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

优化数字数据(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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券