MySQL性能优化--优化数据库结构之优化数据类型

 

By：授客  QQ：1033553122

 

优化数字数据(Numeric Data)

l   对于唯一ID或其它可用字符串或数字表示的值，选择用数字列好过用字符串列。因为相比对应的字符串，可使用更少的字节存储大数字，同时，转换并比较数字速度更快且消耗更少的内存。

参考连接：http://dev.mysql.com/doc/refman/5.7/en/optimize-numeric.html

优化字符和字符串类型(Character and String Type)

l   当不需要语言特定的比对功能时，使用二进制比对(binary collation)命令对来获取更快的比较和排序操作。在特定查询中使用BINARY 操作符以使用二进制比对。

eg:

mysql> SELECT 'a' = 'A';

-> 1

mysql> SELECT BINARY 'a' = 'A';

-> 0

mysql> SELECT 'a' = 'a ';

-> 1

mysql> SELECT BINARY 'a' = 'a ';

-> 0

l   当需要比较不同列的值时，尽可能为那些列定义相同的字符集和比对方法，避免执行查询时进行字符串转换。

l   对于小于8KB的列值，使用二进制VARCHAR,而非BLOB，GROPU BY和ORDER BY语句会生成临时表，如果原始表没包含任何BLOB列，那么这些临时表可使用MEMORY 存储引擎。

l   如果表包含字符串列，如名字和地址，但是许多查询不检索那些列，可考虑把这些字符串列拆分到一个单独的表，必要时使用携带外键的join查询。当MySQL检索来自某记录行的任意值时，它会读取包含该记录行(也可能还有其它相邻行)所有列的数据块。保持每个记录行尽可能的小，仅含最频繁使用的列，这样允许在每个数据块中放入更多的记录行。这样紧凑的表，可减少大部分查询带来的磁盘I/O和内存使用。

l   当在InnoDB表中使用随机生成的值作为主键时，如果可能的话，使用一个“升序值”(ascending value)如当前日期和时间作为其前缀。当连续的主键值在物理上连续存储时，可以加快InnoDB的插入和检索速度。

参考连接：

http://dev.mysql.com/doc/refman/5.7/en/optimize-character.html

优化BLOB

l   当存储一个包含文本数据的大二进制对象，考虑先压缩。当整个表都被InnoDB或MyISAM压缩过时不能使用该技术。

l   对于包含多列的表，为减少查询内存占用，不使用BLOB列的话可考虑把BLOB列拆分到单独的表，并在需要时使用join方式引用。

l   由于检索和展示BLOB值的性能要求和其它数据类型不一样，可以考虑把特定于BLOB的表放在不同的存储设备，甚至是一个单独的数据库实例。例如，检索一个BLOB可能需要一个大的顺序读磁盘(sequential disk read),相比SSD 设备，更适合传统的硬盘驱动。

l   与其直接比较长文本字符串的相等性，可在某个单独的列中存储长文本所在列列值的哈希，并为存储哈希值的列建立索引，查询的时候测试哈希是否相等(使用MD5()、CRC32()函数生成哈希值)。由于哈希函数会为不同输入生成重复结果，所以查询中还需要增加语句blob_column=long_string_value以防止错误的匹配。针对哈希值的更小，更容易的索引扫描有助于提高性能。

参考连接：

http://dev.mysql.com/doc/refman/5.7/en/optimize-blob.html

使用PROCEDURE ANALYSE

ANALYSE([max_elements[,max_memory]])

ANALYSE（）检测来自查询的结果并返回分析结果，为每列的数据类型给出可能帮助减小表的优化建议。使用方法如下，直接把语句拼接到查询末尾：

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])

eg:

SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);

可选参数：

l   max_elements：默认256，指定每列中，ANALYSE()关注的不重复值的最大数量。ANALYSE()用于检测优化数据类型是否应该为ENUM,如果有多余max_elements个补重复值，则ENUM不为建议类型。

l   max_memory：默认为8192，当ANALYSE()试图查找所有不重复值时，为每列分配的最大内存量。

注意：PROCEDURE ANALYSE()不能在UNION语句中使用。

参考连接：

http://dev.mysql.com/doc/refman/5.7/en/procedure-analyse.html