MYSQL教程:数据列类型与查询效率

数据列类型与查询效率

选用适当的数据列类型有助于提高查询命令的执行速度，下面是几点关于如何选择合适数据列类型的建议：

尽量选用尺寸较小的数据列。这样能节约磁盘空间和加快查询速度。如果较短的数据列上建有索引，则索引的处理速度会进一步提高。

针对数据列类型，尽量选择最适用的数据表类型。如固定长度数据列在MyISAM或ISAM数据表中的速度是最快的，所以在这样数据表中尽量使用char类型而不是varchar类型来保存字符串数据。对于InnoDB数据表类型，由于varchar类型可有效减少占用空间，从而减少磁盘I/O，所以使用varchar类型是有利的。对于BDB类型数据表，使用定长和不定长列类型的区别就不大，可任选一种。

尽量把数据列声明为NOT NULL，以节约存储空间和加快处理速度。

对于取值范围有限的数据列，考虑使用ENUM数据列类型。ENUM数据列类型在MySQL中的处理速度是很快。

使用PROCEDURE ANALYSE()语句来分析数据表，它会对数据列的声明提出建议，我们可根据建议进行修改。

select * from table_name PROCEDURE ANALYSE();

select * from table_name PROCEDURE ANALYSE(16,256); #(16,256)含义是：如果某列的不同取值在16个以上或长度超过256字节，就不提出使用ENUM的建议。

用OPTIMIZE TABLE语句对容易出现碎片的数据表进行整理。包含可变长数据列的数据表都会产生碎片，从而占用多余的磁盘空间和影响查询速度。所以要定期运行OPTIMIZE TABLE语句以防止数据表查询性能降低。但该语句只对MyISAM数据表有效。对各种数据表通用的碎片整理方法是这样的：先用工具程序mysqldump导出数据表，再删除数据表后重建，如：

$ mysqldump --opt db_name table_name > dump.sql

$ mysql db_name < dump.sql

把非结构化和变化大的数据放在BLOB数据列里，定期用OPTIMIZE TABLE命令优化。

人为地给数据表增加一个数据列，以充当索引。做法是这样的，先根据数据表里的其它数据列计算出一个散列值，并保存在一个数据列里，然后通过搜索散列值来检索数据行。注意，该技巧只适用于精确匹配型查询。散列值在大于，小于等的操作中不起作用。散列值可以MD5()(适用于3.23及以上版本)，SHA1()(适用于4.0.1及以上版本)，CRC32()(适用于4.1及以上版本)等函数生成。使用散列值支检索BLOB和TEXT值的做法比直接检索BLOB和TEXT本身的做法快。

尽量避免对大尺寸的BLOB值进行检索。如果要检索都应该通过它的上面提到散列值先进行筛选。而不应该盲目地在网络中传送大量BLOB值。

如果把BLOB值剥离到另外一个数据表里去，可实现数据表中其它数据列转变成固定长度数据列的话。就即可减少数据表碎片，又可使在原始表中的select *查询不会把大尺寸的BLOB值不必要地通过网络传送。