一、有效的类型选择
1、使你的数据尽可能的小
最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小。这能给出巨大的改进,因为磁盘读入较快并且通常也用较少的主存储器。如果在更小
的列上做索引,索引也占据较少的资源。
你能用下面的技术使表的性能更好并且使存储空间最小:
(1)尽可能地使用最有效的类型。MySQL有很多节省磁盘空间和内存的专业化类型。
(2)如果可能使表更小,使用较小的整数类型。例如MEDIUMINT经常比INT好一些。
(3)如果可能,声明列为NOT NULL。它使任何事情更快而且你为每列节省一位。注意如果在你的应用程序中你确实需要NULL,你应该毫无疑问使用它,只是避免缺省地在所有
列上有它。
2、使用定长列、而非可变长列
这条准则对被经常修改,从而容易产生碎片的表来说特别重要。例如,应该选择CHAR 列而不选择VARCHAR 列。所要权衡的是使用定长列时,表所占用的空间更多,但如果
能够承担这种空间的耗费,使用定长行将比使用可变长的行处理快得多。
3、将列定义为NOT NULL
这样处理更快,所需空间更少。而且有时还能简化查询,因为不需要检查是否存在特例NULL。
4、考虑使用ENUM列
如果有一个只含有限数目的特定值的列,那么应该考虑将其转换为ENUM 列。ENUM 列的值可以更快地处理,因为它们在内部是以数值表示的。
二、特殊类型BLOB和TEXT
1、BLOB和TEXT优点
用 BLOB 存储应用程序中包装或未包装的数据,有可能使原来需要几个检索操作才能完成的数据检索得以在单个检索操作中完成。而且还对存储标准表结构不易表示的数据
或随时间变化的数据有帮助。
2、BLOB和TEXT缺点
另一方面,BLOB 值也有自己的固有问题,特别是在进行大量的DELETE 或UPDATE 操作时更是如此。删除BLOB 会在表中留下一个大空白,在以后将需用一个记录或可能是不
同大小的多个记录来填充。
除非有必要,否则应避免检索较大的BLOB 或TEXT 值。例如,除非肯定WHERE 子句能够将结果恰好限制在所想要的行上,否则SELECT * 查询不是一个好办法。这样做可能
会将非常大的BLOB 值无目的地从网络上拖过来。这是存储在另一列中的BLOB 标识信息很有用的另一种情形。可以搜索该列以确定想要的行,然后从限定的行中检索BLOB 值。
3、必要的准则
(1)对容易产生碎片的表使用OPTIMIZE TABLE
大量进行修改的表,特别是那些含有可变长列的表,容易产生碎片。碎片不好,因为它在存储表的磁盘块中产生不使用的空间。随着时间的增长,必须读取更多的块才能取
到有效的行,从而降低了性能。任意具有可变长行的表都存在这个问题,但这个问题对BLOB 列更为突出,因为它们尺寸的变化非常大。经常使用OPTIMIZE TABLE 有助于保持性
能不下降。
(2)使用多列缩影
多列索引列有时很有用。一种技术是根据其他列建立一个散列值,并将其存储在一个独立的列中,然后可通过搜索散列值找到行。这只对精确匹配的查询有效。(散列值对
具有诸如“<”或“>=”这样的操作符的范围搜索没有用处)。在MySQL 3.23版及以上版本中,散列值可利用MD5( ) 函数产生。
(3)将BLOB值隔离在一个独立的表中
在某些情况下,将BLOB 列从表中移出放入另一个副表可能具有一定的意义,条件是移出BLOB 列后可将表转换为定长行格式。这样会减少主表中的碎片,而且能利用定长行
的性能优势。
三、使用ANALYSE过程检查表列
ANALYSE([max elements,[max memory]])
它检验来自你的查询的结果并返回结果的分析。
max elements(缺省256)是analyse将注意的每列不同值的最大数量。这被ANALYSE用来检查最佳的列类型是否应该是ENUM类型。
max memory(缺省8192)是在analyse尝试寻找所有不同值的时候应该分配给每列的最大内存量。
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])
mysql>SELECT * FROM student PROCEDURE ANALYSE();
mysql>SELECT * FROM student PROCEDURE ANALYSE(16,256);
相应输出中有一列是关于表中每列的最佳列类型的建议。第二个例子要求PROCEDURE ANALYSE( ) 不要建议含有多于16 个值或取多于256 字节的ENUM 类型(可根据需要更
改这些值)。如果没有这样的限制,输出可能会很长;ENUM 的定义也会很难阅读。
根据 PROCEDURE ANALYSE( ) 的输出,会发现可以对表进行更改以利用更有效的类型。如果希望更改值类型,使用ALTER TABLE 语句即可。