Day15-MySQL修炼之道之研发规范和查询优化

一、前言

本章将为读者解读一份研发规范。为了更好地协同工作和确保所开发的应用尽可能的稳定、高效，建立一套数据库相关的研发规范是很有必要的，虽然研发规范的确立和推广是一项很耗时的工作，但所取得的收益也是长久的，它可以让研发人员更高效地使用数据库，可以让新的研发人员尽快融入研发体系，还可以极大地减少DBA和研发团队、测试团队的沟通成本。

如果DBA需要建立研发规范，建议和研发团队一起沟通确定，因为标准的实行和落地，需要考虑到现有的一些框架、语言和习惯，而旧有的力量往往是强大的。不同的人背景不一样，看待事务的标准也不一样，自然就会有理解上的不一致，我们应该尽可能地求得最大的共识。以下将列举下一些研发规范，主要包括命名约定、索引、表设计、SQL语句、升级/部署脚本规范、数据架构建议这几个部分，以供读者参考。这些规范中有些并不是绝对要遵循的，需要依据现实情况进行权衡取舍。

7.1　命名约定

1. 严格的统一风格.
2. 避免使用缩写或代码来命名。
3. ·数据库、表都用小写。
4. ·索引的命名以idx_为前缀。
5. ·命名少于25个字符。
6. ·不要使用保留字。
7. ·同一个字段在不同的表中也应是相同的类型或长度。
8.  同一个数据库下有不同的应用模块，则可以考虑对表名用不同的前缀标识。
9. ·备份表时加上时间标识.(xxx表_201705081409) 说明这个表是在2017年05月08日14点09分所备份。）

    10.新建库必须提供库名，库的命名规则必须契合所属业务的特点，新建库必须说明需要授权的用户，若要新建用户，则必须提供用户名，用户命名规则要契合业务。

7.2　索引

1. ·建议索引中的字段数量不要超过5个。
2. ·单张表的索引数量建议控制在5个以内。
3. ·唯一键和主键不要重复。
4. ·索引字段的顺序需要考虑字段唯一值的个数，选择性高的字段一般放在前面。
5. ·ORDER BY、GROUP BY、DISTINCT的字段需要放在复合索引的后面，也就是说，复合索引的前面部分用于等值查询，后面的部分用于排序。
6. ·使用EXPLAIN判断SQL语句是否合理使用了索引，尽量避免Extra列出现Using File Sort，Using Temporary。
7. ·UPDATE、DELETE语句需要根据WHERE条件添加索引。
8. ·建议不要使用“like%value”的形式，因为MySQL仅支持最左前缀索引。
9. ·对长度过长的VARCHAR字段（比如网页地址）建立索引时，需要增加散列字段，对VARCHAR使用散列算法时，散列后的字段最好是整型，然后对该字段建立索引。
10. ·存储域名地址时，可以考虑采用反向存储的方法，比如把news.sohu.com存储为com.sohu.news，方便在其上构建索引和进行统计。
11. ·合理地创建复合索引，复合索引(a,b,c)可以用于“where a=?”、“where a=?and b=?”、“where a=?and b=?and c=?”等形式，但对于“where a=?”的查询，可能会比仅仅在a列上创建单列索引查询要慢，因此需要在空间和效率上达成平衡。
12. ·合理地利用覆盖索引。由于覆盖索引一般常驻于内存中，因此可以大大提高查询速度。
13. ·把范围条件放到复合索引的最后，WHERE条件中的范围条件（BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。

7.3　表设计

• ·如果没有特殊的情况，建议选择InnoDB引擎。
• ·每个表都应该有主键，可选择自增字段，或者整型字段。使用UNSIGNED整型可以增加取值的范围。例外的情况是，一些应用会频繁地基于某些字段进行检索，设计人员可能会认为这些字段/字段组合更适合做主键，因为它们更自然、更高效。
• ·尽量将字段设置成NOT NULL。如果没有特殊的理由，建议将字段定义为NOT NULL。如果将字段设置成一个空字符串或设置成0值并没有什么不同，都不会影响到应用逻辑，那么就可以将这个字段设置为NOT NULL。NULL值的存储需要额外的空间，且会导致比较运算更为复杂，这会使优化器更难以优化SQL。当然，是否设置为NULL更应取决于你的业务逻辑，如果你确实需要，那么就设置它允许NULL值，NULL值虽然会导致比较运算更加复杂，但这比因为定义了NOT NULL默认值而导致应用逻辑出现异常要好。

• ·使用更短小的列，比如短整型。整型列的执行速度往往更快

• ·考虑使用垂直分区。比如，我们可以把大字段或使用不频繁的字段分离到另外的表中，这样做可以减少表的大小，让表执行得更快。我们还可以把一个频繁更新的字段放到另外的表中，因为频繁更新的字段会导致MySQL Query Cache里相关的结果集频繁失效，可能会影响性能。需要留意的一点是，垂直分区的目的是为了优化性能，但如果将字段分离了到分离表后，又经常需要建立连接，那可能就会得不偿失了，所以，我们要确保分离的表不会经常进行连接，这时，用程序进行连接是一个可以考虑的办法。
• ·存储精确浮点数时必须使用DECIMAL替代FLOAT和DOUBLE。
• ·建议使用UNSIGNED类型存储非负值。
• ·建议使用INT UNSIGNED存储IPV4。可以使用INET_ATON()、INET_NTOA()函数进行转换，PHP里也有类似的函数如ip2long()、long2ip()。
• ·整形定义中不添加显示长度的值，比如使用INT，而不是INT(4)。

• ·建议不要使用ENUM类型。
• ·尽可能不要使用TEXT、BLOB类型。
• ·在VARCHAR(N)中，N表示的是字符数而不是字节数，比如VARCHAR(255)，最大可存储255个汉字。需要根据实际的宽度来选择N。此外，N应尽可能地小，因为在MySQL的一个表中，所有的VARCHAR字段的最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存（对于这一点，MySQL 5.7后有了改进）。

• ·字符集建议选择UTF-8。
• ·存储年时使用YEAR类型。
• ·存储日期时使用DATE类型。
• ·存储时间时（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用的是4字节，DATETIME使用的是8个字节。
• ·不要在数据库中使用VARBINARY或BLOB存储图片及文件等。MySQL并不适合大量存储这种类型的文件。
• ·JOIN（连接）字段在不同表中的类型和命名要一致。
• ·如果变更表结构可能会影响性能，则需要通知DBA审核。

7.4　SQL语句

执行一些大的DELETE、UPDATE、INSERT操作时要慎重，特别是对于业务繁忙的系统，要尽量避免对线上业务产生影响。长时间的锁表，可能会导致线上部分查询被阻塞，甚至导致Web应用服务器宕机。解决的方案是，尽可能早地释放资源，尽可能把大操作切割为小的操作，比如使用LIMIT子句限制每次操作的记录数，也可以利用一些日期字段，基于更小粒度的时间范围进行操作。

我们也可以基于自增字段ID分批分段删除数据，如下的例子，是一个定时删除线上数据的脚本，interval变量用于设置每次循环删除的记录数，i变量用于控制循环的次数。由于在删除记录的同时，可能也插入了记录，因此设置为最后一次删除的记录数小于500（$delRow-le 500）时，退出循环。

interval=200000
i=1
while [ $i -lt 100 ]
do
delRow=`mysql  db_name  2>>$logFile <<EOF
set @minMid=(select min(id) from table_name) ;
delete from table_name where id < @minMid + $interval + 500 and date_time <‘2014-10-10 00:00:00’;
select ROW_COUNT();
EOF`
if [ $? -ne 0 ] ; then
echo“delete table_name  failed”| tee -a $logFile
exit 1
fi
echo“$i round: delete $delRow rows”
if [ $delRow -le 500 ] ; then
break
fi
sleep 1
i=$(($i + 1 ))
done

• 不要使用ORDER BY RAND()。
• 避免使用SELECT*语句，SELECT语句只用于获取需要的字段。
• 使用预编译语句（prepared statement），可以提高性能并且防范SQL注入攻击。
• 分割大操作。
• SQL语句中IN包含的值不应过多，建议少于100。
• 一般情况下在UPDATE、DELETE语句中不要使用LIMIT。
• WHERE条件语句中必须使用合适的类型，避免MySQL进行隐式类型转化。
• INSERT语句必须显式地指明字段名称，不要使用INSERT INTO table()。
• 避免在SQL语句中进行数学运算或函数运算，避免将业务逻辑和数据存储耦合在一起。
• INSERT语句如果使用批量提交（如INSERT INTO table VALUES(),(),()……），那么VALUES的个数不应过多。一次性提交过多的记录，会导致线上I/O紧张，出现慢查询。
• ·避免使用存储过程、触发器、函数等，这些特性会将业务逻辑和数据库耦合在一起，并且MySQL的存储过程、触发器和函数中可能会存在一些Bug。
• ·应尽量避免使用连接（JOIN），连接的表也不宜过多。
• ·应使用合理的SQL语句以减少与数据库的交互次数。
• ·建议使用合理的分页技术以提高操作的效率。
• ·如果性能没有问题，则只在主库上执行后台查询或统计功能。如果必须在从库上执行大的查询，那么应该先通知DBA增加专门用于生产查询的从库。

7.5　SQL脚本

• ·SQL脚本必须去除^M符号。Windows系统中，每行的结尾是“<回车><换行>”，即“\r\n”；Mac系统里，每行的结尾是“<回车>”，即'\r'。Unix/Linux系统里，每行的结尾是换行CR，即“\n”。三个系统行的结尾各不相同，这会导致的一个直接后果是，Unix/Mac系统下的文件在Windows里打开时，所有的文字会变成一行；而Windows里的文件在Unix/Mac下打开，在每行的结尾可能会多出一个^M符号。而在SQL脚本中，必须要将此符号去除。

• ·对于存储过程或触发器，升级脚本里应该正确设置分隔符（DELIMITER）。
• ·对于函数，需要确认DETERMINISTIC。
• ·如果没有特殊需要，应该一律使用InnoDB引擎和utf8字符集。升级脚本应尽量做到方便回滚、可重复执行。

• 必须保证注释的有效性（注：MySQL注释可以使用“--”、“#”或“/**/”，其中“--”后面跟内容时一定要有空格，由于“--”这种注释方法经常导致出错，建议统一使用“#”进行注释）。
• 对一个表的表结构的变更，应合并为一条SQL实现。
• SQL文件必须是UTF-8无BOM格式的文件。对于存在非英文字符的升级文件，可以用file命令确认它是否为一个UTF-8编码的文件 。
•  例如：[linux1]$ file upgrade.sql

upgrade.sql: UTF-8 Unicode text, with very long lines
需要留意的是，英语字母的utf8编码和ASCII编码是一样的。对于一个全英文字母的文件，file命令不会指明这是一个UTF-8编码的文件。file命令对于GBK等字符集可能也会识别不佳。
对于开发和测试环境，建议制订严格的规范，让大家都使用UTF-8编码的文件。可以使用enca、iconv等命令批量转换文件。
iconv的命令格式为：iconv-f encoding-t encoding inputfile
iconv-l可列出所支持的字符集。
如下命令将转换GBK字符集的aaa.txt文件为utf8字符集的bbb.txt。
iconv -fgbk  -t utf-8 aaa.txt > bbb.txt
一些编辑工具可以轻易地转换文件格式，图7-1展示了notepad++转换编码的菜单项。

• ·一些初始化数据的操作，也可以用mysqldump导出测试/开发环境数据，然后提交给DBA升级生产环境数据库。mysqldump可以保持最佳的兼容性。而其他的客户端工具导出的文件则可能存在一些异常或不兼容的情况。
• ·导出导入数据时需要注意MySQL Server和客户端工具的版本。
• 由于一般软件都是向后兼容的，因此在高低版本间导出导入数据时，如果大版

本是一致的，比如，都是MySQL 5.1，一般是不会有什么问题的。但如果大版本不一致，则可能存在兼容性的问题，如从MySQL 5.0导入到5.1，或者从MySQL 5.1导入到5.0，请尽量遵循以下原则。

从MySQL Server低版本导入数据到MySQL Server高版本时，应该直接以高版本的mysqldump导出，然后导入高版本的MySQL Server中，当然，以低版本的mysqldump导出可能也行。

从MySQL Server高版本导入数据到MySQL Server低版本，应该以低版本的mysqldump导出，然后再导入低版本的MySQL Server。

7.6　数据架构的建议

·每张表的数据量控制在5000万以下。

·推荐使用CRC32求余（或者类似的算术算法）进行分表，表名后缀使用数字，数字必须从0开始并等宽，比如散100张表，后缀则是从00-99。

·使用时间分表，表名后缀必须使用固定的格式，比如按日分表为user_20110101。

7.7　开发环境、测试环境的配置参数建议

假设我们统一字符集为utf8，统一默认引擎为InnoDB，那么建议默认的配置文件my.cnf如下，这份配置文件没有进行关注性能方面的调整，大家可以对照自己的环境修改或增加适当的参数。

[client]
port            = 3306
socket          = / tmp/mysql.sock
default-character-set = utf8
[mysqld]
character-set-server = utf8
port            = 3306
socket          = /tmp/mysql.sock
user    = mysql
skip-external-locking
max_connections=3000
max_connect_errors=3000
thread_cache_size = 300
skip-name-resolve
server-id       = 1
binlog_format=mixed
expire-logs-days = 8
sync_binlog=60
innodb_log_file_size = 256M
default-storage-engine=innodb
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set = utf8

7.8　数据规划表

数据库是一项比较紧缺的资源，往往需要进行数据规划和资源申请。表7-1是一个申请资源的范本表，可以作为研发团队提交给DBA进行申请资源之用。由于互联网业务的变化可能会很快，往往难以准确地估计数据量和业务量的增长速度，所以，对于这两项可以要求不必非常准确，但最好不要有数量级的估算错误，你规划得越准确，后续的运维成本就越低，调整的代价就越小。

其中，峰值事务增比幅度=最高峰值事务/平均事务。

对于长查询事务，因为数据库不是很擅长同时处理批量大事务和实时短事务，因此对于线上的繁忙生产系统，一般是不允许有很多长查询存在的，以免影响线上业务。如果有统计类的分析业务，则建议尽早规划，将统计数据分离到其他的数据库实例。

关于数据文件的大小，建议使用真实的数据进行估算。如输入30万条数据，然后使用如下查询验证数据大小。

select sum(data_length+index_length) from information_schema.tables where table_schema='db_name' and table_name='table_name';

由以上的结果可以估算出100万数据的大小。

7.9　其他规范

·批量导入、导出数据时DBA需要进行审查，并在执行过程中观察服务。

·批量更新数据时，如执行UPDATE、DELETE操作，DBA也要进行审查，并在执行过程中观察服务。

·产品出现非数据库平台运维导致的问题和故障时，请及时通知DBA，以便于维护服务的稳定性。

·业务部门推广活动，请提前通知DBA进行服务和访问评估。

·如果业务部门出现人为误操作而导致数据丢失，则需要恢复数据，请在第一时间通知DBA，并提供数据丢失的准确时间，误操作语句等重要线索。

小结

规范的根本目的是为了帮助开发、释放人的潜力，提高生产力，而不是约束人，让人失去发挥的空间。标准的建设任重而道远，在制定的过程中，前期宜宽不宜紧，逐渐收集信息，提高规范的适应性，最终是可以达到一个平衡的。友好的规范既能保证运维的安全、便捷，也能让研发、测试团队的工作更加高效。它还应该是一个知识的集聚地，让接触规范的人尽快变得训练有素。

第6章　查询优化

查询优化是研发人员比较关注也是疑问较多的领域。本章首先为读者介绍常用的优化策略、MySQL的优化器、连接机制，然后介绍各种语句的优化，在阅读本章之前，需要先对EXPLAIN命令，索引知识有必要的了解。

研发人员应该掌握并且熟悉优化技巧，某种意义上，因为研发人员熟悉业务逻辑，因此应该比DBA更加擅长于对SQL的优化。现实中，各种技术之间的界限变得越来越模糊，不同背景的IT从业人员之间的交流也越来越频繁，本书将属于优化的大部分内容都放在开发篇，是因为优化的重心将会越来越向前推移到研发团队，DBA也需要了解开发，需要融入整个研发体系中去。

6.1　基础知识

6.1.1　查询优化的常用策略

一般常用的查询优化策略有优化数据访问、重写SQL、重新设计表、添加索引4种。下面将分别介绍这4种优化策略。

（1）优化数据访问

应该尽量减少对数据的访问。一般有如下两个需要考虑的地方：应用程序应减少对数据库的数据访问，数据库应减少实际扫描的记录数。

例如，如果应用程序可以缓存数据，就可以不需要从数据库中直接读取数据。

例如，如果应用程序只需要几个列的数据，就没有必要把所有列的数据全部读取出来，应该尽可能地避免“SELECT*FROM table_name”这样的语句。

例如，有时我们在慢查询日志里会看到Rows_examined这一项的值很高，而实际上，并不需要扫描大量的数据，这种情况下添加索引或增加筛选条件都可以极大地减少记录扫描的行数。

类似的例子还有很多，这里就不一一列举了。

（2）重写SQL

由于复杂查询严重降低了并发性，因此为了让程序更适于扩展，我们可以把复杂的查询分解为多个简单的查询。一般来说多个简单查询的总成本是小于一个复杂查询的。

对于需要进行大量数据的操作，可以分批执行，以减少对生产系统产生的影响，从而缓解复制超时。

由于MySQL连接（JOIN）严重降低了并发性，对于高并发，高性能的服务，应该尽量避免连接太多表，如果可能，对于一些严重影响性能的SQL，建议程序在应用层就实现部分连接的功能。这样的好处是：可以更方便、更高效地缓存数据，方便迁移表到另外的机器，扩展性也更好。

（3）重新设计库表

有些情况下，我们即使是重写SQL或添加索引也是解决不了问题的，这个时候可能要考虑更改表结构的设计。比如，可以增加一个缓存表，暂存统计数据，或者可以增加冗余列，以减少连接。优化的主要方向是进行反范式设计，反范式的设计请参考4.1节。

（4）添加索引

生产环境中的性能问题，可能80%的都是索引的问题，所以优化好索引，就已经有了一个好的开始。索引的具体优化，请参考3.5节。

6.1.2　优化器介绍

查询优化器的任务是发现执行SQL查询的最佳方案。“好”方案和“坏”方案之间性能的差别可能会很大。大多数查询优化器，包括MySQL的查询优化器，总是或多或少地在所有可能的查询评估方案中搜索最佳方案。不同版本优化器的优化算法可能也会不同，随着MySQL版本的进化，优化器也变得越来越强大和智能，去除了一些限制，改进了一些算法。本书主要关注的是MySQL 5.1版本的优化方式，MySQL 5.5、5.6、5.7版本目前都已经有了GA版本，相关的改进，建议大家参考官方文档。如果不确定优化器的优化方式，可以使用EXPLAIN语句验证之。

1.优化器的不足

MySQL优化器也有很多不足之处，它不一定能保证选择的执行计划就是最优的。

• ·数据的统计信息有可能是错误的，对于复杂的查询，数据库可能会执行错误的执行计划，从而导致严重的性能问题。
• ·MySQL优化器的优化是基于简单的成本评估进行的，总是会选择成本更小的执行计划，其对成本衡量的标准是读取的随机块的数量，但是，本质上成本往往包括了诸多因素，CPU、内存、数据是否在缓存中，都是需要考虑到的因素，这样往往会导致MySQL计算得出的成本最小的执行计划不一定是响应最快的。

• ·优化器不会考虑并发的情况，而实际的数据库执行，并发处理则是复杂的，资源的争用可能会导致性能问题。
• ·一些商业数据库在执行的过程中会对各种优化结果的执行情况进行统计评估，以便自动改进后续的执行优化状况，而MySQL目前还没有这些功能

2.优化器加提示

有时我们需要告诉优化器，让它按我们的意图生成执行计划，但是，加提示（hint）的方式不到万不得已，建议不要使用。一般来说，复杂的SQL走了错误的执行计划的时候才可能需要使用到提示，我们应该尽量让MySQL的优化器去决定执行计划。否则，将会增加MySQL的维护成本，你也可能需要更多的额外工作。随着时间的演变，我们选择的提示所依据的外部条件很可能已经发生了变化，比如说数据量、数据分布发生了变化，如果你仍然使用旧的提示，可能会导致MySQL承担过多的工作。而且，在MySQL升级了新版本后，你也应用不了新的优化技术。

 比较常用的加提示的方式有如下6种。

（1）使用索引（USE INDEX）

USE INDEX(index_list)将告诉MySQL使用我们指定的索引去检索记录。index_list是索引名列表，以逗号分隔。注意，我们这里设置的是索引名或索引名列表，而不是索引基于的字段名，主键名为PRIMARY。可以使用SHOW INDEX FROM table_name命令显示表上的索引名。

下面的例子表示建议使用索引名为col1_index或col2_index的索引检索表。

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;

（2）不使用索引（IGNORE INDEX）

 IGNORE INDEX(index_list)将建议MySQL不使用指定的索引。如果我们用EXPLAIN命令查看执行计划，发现走了错误的索引，那么可以使用IGNORE INDEX来避免继续使用错误的索引。如下的例子表示建议MySQL不使用索引名为col3_index的索引。

SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;

（3）强制使用索引（FORCE INDEX）

      有时我们使用USE INDEX指定了索引，但MySQL优化器仍然选择不使用我们指定的索引，这时可以考虑使用FORCE INDEX提示。

      注意，USE INDEX、IGNORE INDEX和FORCE INDEX这些提示方式只会影响MySQL在表中检索记录或连接要使用的索引，它们并不会影响ORDER BY或GROUP BY子句对于索引的选择。

（4）不使用查询缓冲（SQL_NO_CACHE）

SQL_NO_CACHE提示MySQL对指定的查询关闭查询缓冲机制。有时为了验证一条SQL语句实际执行的时间，我们可以临时加上SQL_NO_CACHE，以免被查询缓冲给误导了。对于一些不期望被缓存的SQL，比如夜间的报表查询，可以通过设置SQL_NO_CACHE来让MySQL查询缓冲更高效地工作。

（5）使用查询缓冲（SQL_CACHE）

有时我们将查询缓冲设置为显式模式（explicit mode，query_cache_type=2），也就是说，除非指明了SQL需要缓存，否则MySQL是不考虑缓存它的，我们使用SQL_CACHE来指定哪些查询需要被缓存。

（6）STRAIGHT_JOIN

        这个提示将告诉MySQL按照FROM子句描述的表的顺序进行连接。如果用EXP

LAIN命令进行检查，确认了MySQL没有按照最优的顺序进行表的连接，那就可以使用这个提示，告诉MySQL按照我们指定的顺序进行连接。不建议自己指定连接顺序，可以尝试重写SQL，看看MySQL是否能够选择更好的执行计划，也可以尝试分析表（运行ANALYZE TABLE命令）以更新索引统计信息，STRAIGHT_JOIN应该是最万不得已时才做的选择。

6.1.3　MySQL的连接机制

MySQL中的JOIN（连接）这个术语泛指一切查询，而不是传统术语中的定义：“两个表之间的JOIN”。MySQL的查询优化器最重要的部分就是连接优化器，由它来决定多个表连接的次序。其他的查询语句都相应地向JOIN靠拢：单表查询将被当作JOIN的特例，子查询也将被尽量转换为JOIN查询。

MySQL一般使用的是“Nested Loop Join”，即嵌套连接。图6-1是《High Performance MySQL》一书中对嵌套连接的说明图，col3为连接列，连接两个表tbl1、tbl2的步骤类似如图6-1所示。

如图6-1所示，嵌套连接遍历tbl1表，对于tbl1表中的每一行记录，都将去tbl2表中探测，看是否有满足条件的记录。上述执行步骤，可以简单地描述成如下语句，实际上，MySQL对如下算法做了一些改进。

For each tuple r in tbl1 do

For each tuple s in tbl2 do

If r and s satisfy the join condition

Then output the tuple <r,s>

我们称外部的tbl1表为驱动表或外部表，内部的tbl2表为内部表。这种算法的成本与外部表行数乘以内部表行数的乘积是成正比例的。如果嵌套的层次比较多，也就是说连接了很多表，那么成本将是昂贵的。如果两个表进行连接，MySQL优化器一般会选择更小的表或更小子集（满足查询条件的记录行数少）的表作为驱动表。为什么要这么做呢？由上面的代码可知，随着驱动表（外部表）行数的增加，成本会增加得很快，选择更小的外部表或更小子集的外部表，是为了尽量减少嵌套连接的循环次数，而且，内部表一般在连接列有索引，索引一般常驻于内存中，这样可以保证很快完成连接。

因此，MySQL应该尽量避免连接太多表。在现实的生产环境中，这个问题很普遍，研发人员往往低估了连接太多表所带来的负面影响。

6.2　各种语句优化

6.2.1　连接的优化

由于连接的成本比较高，因此对于高并发的应用，应该尽量减少有连接的查询，连接的表的个数不能太多，连接的表建议控制在4个以内。互联网应用比较常见的一种情况是，在数据量比较小的时候，连接的开销不大，这个时候一般不会有性能问题，但当数据量变大之后，连接的低效率问题就暴露出来了，成为整个系统的瓶颈所在。所以对于数据库应用的设计，最好在早期就确定未来可能会影响性能的一些查询，进行反范式设计减少连接的表，或者考虑在应用层进行连接。

优化连接的一些要点如下。

1）ON、USING子句中的列确认有索引。如果优化器选择了连接的顺序为B、A，那么我们只需要在A表的列上创建索引即可。例如，对于查询“SELECT B.*,A.*FROM B JOIN A ON B.col1=A.col2;”语句MySQL会全表扫描B表，对B表的每一行记录探测A表的记录（利用A表col2列上的索引）。

2）最好是能转化为INNER JOIN，LEFT JOIN的成本比INNER JOIN高很多。

3）使用EXPLAIN检查连接，留意EXPLAIN输出的rows列，如果rows列太高，比如几千，上万，那么就需要考虑是否索引不佳或连接表的顺序不当。

4）反范式设计，这样可以减少连接表的个数，加快存取数据的速度。

5）考虑在应用层实现连接。

对于一些复杂的连接查询，更值得推荐的做法是将它分解为几个简单的查询，可以先执行查询以获得一个较小的结果集，然后再遍历此结果集，最后根据一定的条件去获取完整的数据，这样做往往是更高效的，因为我们把数据分离了，更不容易发生变化，更方便缓存数据，数据也可以按照设计的需要从缓存或数据库中进行获取。例如，对于如下的查询：

SELECT a.* FROM a WHERE a.id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17);

如果id=1~15的记录已经被存储在缓存（如Memcached）中了，那么我们只需要到数据库查询“SELECT a.*FROM a WHERE a.id=16”和“SELECT a.*FROM a WHERE a.id=17”了。而且，把IN列表分解为等值查找，往往可以提高性能。

6）一些应用可能需要访问不同的数据库实例，这种情况下，在应用层实现连接将是更好的选择。

6.2.2　GROUP BY、DISTINCT、ORDER BY语句优化

GROUP BY、DISTINCT、ORDER BY这几类子句比较类似，GROUP BY默认也是要进行ORDER BY排序的，笔者在本书中把它们归为一类，优化的思路也是类似的。可以考虑的优化方式如下。

• 尽量对较少的行进行排序。
• 如果连接了多张表，ORDER BY的列应该属于连接顺序的第一张表。
• 利用索引排序，如果不能利用索引排序，那么EXPLAIN查询语句将会看到有filesort。
• GROUP BY、ORDER BY语句参考的列应该尽量在一个表中，如果不在同一个表中，那么可以考虑冗余一些列，或者合并表。
• 需要保证索引列和ORDER BY的列相同，且各列均按相同的方向进行排序。
• 增加sort_buffer_size。
• sort_buffer_size是为每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP BY操作。但是，这是为每个客户端分配的缓冲区，因此不要将全局变量设置为较大的值，因为每个需要排序的连接都会分配sort_buffer_size大小的内存。
• ·增加read_rnd_buffer_size。
• 当按照排序后的顺序读取行时，通过该缓冲区读取行，从而避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是，这是为每个客户端分配的缓冲区，因此你不应将全局变量设置为较大的值。相反，只用为需要运行大查询的客户端更改会话变量即可。
• ·改变tmpdir变量指向基于内存的文件系统或其他更快的磁盘。
• 如果MySQL服务器正作为复制从服务器被使用，那么不应将“--tmpdir”设置为指向基于内存的文件系统的目录，或者当服务器主机重启时将要被清空的目录。因为，对于复制从服务器，需要在机器重启时仍然保留一些临时文件，以便能够复制临时表或执行LOAD DATA INFILE操作。如果在服务器重启时丢失了临时文件目录下的文件，那么复制将会失败。
• ·指定ORDER BY NULL。

默认情况下，MySQL将排序所有GROUP BY的查询，如果想要避免排序结果

所产生的消耗，可以指定ORDER BY NULL。例如：

SELECT count(*) cnt, cluster_id FROM stat GROUP BY cluster_id ORDER BY NULL LIMIT 10;

·优化GROUP BY WITH ROLLUP。

GROUP BY WITH ROLLUP可以方便地获得整体分组的聚合信息（super aggregation），但如果存在性能问题，可以考虑在应用层实现这个功能，这样往往会更高效，伸缩性也更佳。

·使用非GROUP BY的列来代替GROUP BY的列。

比如，原来是“GROUP BY xx_name,yy_name”，如果GROUP BY xx_id可以得到一样的结果，那么使用GROUP BY xx_id也是可行的。

·可以考虑使用Sphinx等产品来优化GROUP BY语句，一般来说，它可以有更好的可扩展性和更佳的性能。

6.2.3　优化子查询

由于子查询的可读性比较好，所以有些研发人员习惯于编写子查询，特别是刚接触数据库编程的新手。但子查询往往也是性能杀手，在生产环境中，子查询是最常见的导致性能问题的症结所在。

对于数据库来说，在绝大部分情况下，连接会比子查询更快。使用连接的方式，MySQL优化器一般可以生成更佳的执行计划，可以预先装载数据，更高效地处理查询。而子查询往往需要运行重复的查询，子查询生成的临时表上也没有索引，因此效率会更低。

一些商业数据库已经可以智能地识别子查询，转化子查询为连接查询，或者转化连接为子查询。这种情况下，编写子查询也许是更好的方式，毕竟更符合人的思考方式，也能避免因为重复记录的匹配导致连接结果集的异常。但MySQL对于子查询的优化一直不佳，就目前的研发实践来说，子查询应尽量改写成JOIN的写法。如果我们不能确定是否要使用连接的方式，那么可以使用EXPLAIN语法查看语句具体的执行计划。

如下是一个带子查询的语句。
SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROM t2);
普通的子查询一般都可以转化为连接的方式。上面的例子可以转化为如下的写法。
SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;
如下的两个查询是等价的。
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);
还可以改写成如下LEFT JOIN的形式。
SELECT table1.*
FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

下面再举一些例子。
把子句从子查询的外部转移到内部。例如，把此查询：
SELECT * FROM t1
WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);
转化成如下的写法：
SELECT * FROM t1
WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);
将此查询：
SELECT (SELECT column1 FROM t1) + 5 FROM t2;
转化成如下的写法：
SELECT (SELECT column1 + 5 FROM t1) FROM t2;

将此查询：
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
AND t2.column2=t1.column2);
转化成如下的写法，使用行子查询来代替关联子查询：
SELECT * FROM t1
WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);
对于只返回一行的无关联子查询，IN的速度慢于“＝”。
将此查询：
SELECT * FROM t1 WHERE t1.col_name
IN (SELECT a FROM t2 WHERE b = some_const);

转化成如下的写法：
SELECT * FROM t1 WHERE t1.col_name
= (SELECT a FROM t2 WHERE b = some_const);
MySQL优化器这些年来一直都在改进，MySQL后续版本对于子查询也有了更多改进。读者可以参考如下链接：
http://dev.MySQL.com/doc/refman/5.6/en/subquery-optimization.html
http://dev.MySQL.com/doc/refman/5.7/en/subquery-optimization.html

6.2.4　优化limit子句

Web应用经常需要对查询的结果进行分页，分页算法经常需要用到“LIMIT offset,row_count ORDER BY col_id”之类的语句。一旦offset的值很大，效率就会很差，因为MySQL必须检索大量的记录（offset+row_count），然后丢弃大部分记录。

可供考虑的优化办法有如下4点。

1）限制页数，只显示前几页，超过了一定的页数后，直接显示“更多（more）”，一般来说，对于N页之后的结果，用户一般不会关心。

2）要避免设置offset值，也就是避免丢弃记录。

例如以下的例子，按照id排序（id列上有索引），通过增加一个定位的列“id>990”，可以避免设置offset的值。

SELECT id, name, address, phone
FROM customers
WHERE id > 990
ORDER BY id LIMIT 10;

也可以使用条件限制要排序的结果集，如可以这样使用。

WHERE date_time BETWEEN '2014-04-01 00:00:00' AND '2014-04-02 00:00:00' ORDER BY id

对条件值可以进行估算，对于几百上千页的检索，往往不需要很精确。也可以专门增加冗余的列来定位记录，比如如下的查询，有一个page列，指定记录所在的页，代价是在修改数据的时候需要维护这个列的数据，如下面的查询。

SELECT id, name, address, phone
FROM customers
WHERE page = 100
ORDER BY name;

3）使用Sphinx。

4）使用INNER JOIN。

以下的例子中，先按照索引排序获取到id值，然后再使用JOIN补充其他列的数据。customers表的主键列是id列，name列上有索引，由于“SELECT id FROM customers…”可以用到覆盖索引，所以效率尚可。

SELECT id, name, address, phone
FROM customers
INNER JOIN (
SELECT id
FROM customers
ORDER BY name
LIMIT 999,10)
AS my_results USING(id);

6.2.5　优化IN列表

对于IN列表，MySQL会排序IN列表里的值，并使用二分查找（Binary Search）的方式去定位数据。

把IN子句改写成OR的形式并不能提高性能。以笔者个人的经验，IN列表不宜过长，最好不要超过200。对于高并发的业务，小于几十为佳。

如果能够将其转化为多个等于的查询，那么这种方式会更优。例如如下这个查询。

SELECT * FROM table_a WHERE id IN (SELECT id FROM table_b);

我们可以先查询SELECT id FROM table_b，然后把获取到的id值，逐个地和“SELECT*FROM table_a”进行拼接，转化为“SELECT id FROM table_a WHERE

id=?”的形式。这个操作用程序来实现其实是很容易的。

6.2.6　优化UNION

UNION语句默认是移除重复记录的，需要用到排序操作，如果结果集很大，成本将会很高，所以，建议尽量使用UNION ALL语句。对于UNION多个分表的场景，应尽可能地在数据库分表的时候，就确定各个分表的数据是唯一的，这样就无须使用UNION来去除重复的记录了。

另外，查询语句外层的WHERE条件，并不会应用到每个单独的UNION子句内，所以，应在每一个UNION子句中添加上WHERE条件，从而尽可能地限制检索的记录数。

6.2.7　优化带有BLOB、TEXT类型字段的查询

由于MySQL的内存临时表不支持BLOB、TEXT类型，如果包含BLOB或TEXT类型列的查询需要用到临时表，就会使用基于磁盘的临时表，性能将会急剧降低。所以，编写查询语句时，如果没有必要包含BLOB、TEXT列，就不要写入查询条件。

规避BLOB、TEXT列的办法有如下两种。

1）使用SUBSTRING()函数。

2）设置MySQL变量tmpdir，把临时表存放在基于内存的文件系统中。如Linux下的tmpfs。可以设置多个临时表的路径（用分号分隔），MySQL将使用轮询的方式。

优化的办法有如下3种。

1）如果必须使用，可以考虑拆分表，把BLOB、TEXT字段分离到单独的表。

2）如果有许多大字段，可以考虑合并这些字段到一个字段，存储一个大的200KB比存储20个10KB更高效。

3）考虑使用COMPRESS，或者在应用层进行压缩，再存储到BLOB字段中。

注意

如果BLOB列很大，可能需要增大innodb_log_file_size（MySQL错误日志内可能会提示事务日志小了）。

6.2.8　filesort的优化

有时我们使用EXPLAIN工具，可以看到查询计划的输出中的Extra列有filesort。filesort往往意味着你没有利用到索引进行排序。filesort的字面意思可能会导致混淆，它和文件排序没有任何关系，可以理解为不能利用索引实现排序。

排序一个带JOIN（连接）的查询，如果ORDER BY子句参考的是JOIN顺序里的第一张表的列且不能利用索引进行排序，那么MySQL会对这个表进行文件排序（filesort），EXPLAIN输出中的Extra列就有filesort。如果排序的列来自于其他的表，且需要临时文件来帮助排序，那么EXPLAIN输出的Extra列就有“Using temporary;Using filesort”字样。对于MySQL 5.1，如果有LIMIT子句，那么是在filesort之后执行LIMIT的，这样做效率可能会很差，因为需要排序过多的记录。

1.两种filesort算法

MySQL有两种filesort算法：two-pass和single-pass。

（1）two-pass

这是旧的算法。列长度之和超过max_length_for_sort_data字节时就使用这个算法，其原理是：先按照WHERE筛选条件读取数据行，并存储每行的排序字段和行指针到排序缓冲（sort buffer）。如果排序缓冲大小不够，就在内存中运行一个快速排序（quick sort）操作，把排序结果存储到一个临时文件里，用一个指针指向这个已经排序好了的块。然后继续读取数据，直到所有行都读取完毕为止。这是第一次读取记录。

然后合并如上的临时文件，进行排序。

然后依据排序结果再去读取所需要的数据，读入行缓冲（row buffer，由read_rnd_buffer_size参数设定其大小）。这是第二次读取记录。

以上第一次读取记录时，可以按照索引排序或表扫描，可以做到顺序读取。但第二次读取记录时，虽然排序字段是有序的，行缓冲里存储的行指针是有序的，但所指向的物理记录需要随机读，所以这个算法可能会带来很多随机读，从而导致效率不佳。

优点：排序的数据量较小，一般在内存中即可完成。

缺点：需要读取记录两次，第二次读取时，可能会产生许多随机I/O，成本可能会比较高。

（2）single-pass

MySQL一般使用这种算法。其原理是：按筛选条件，把SQL中涉及的字段全部读入排序缓冲中，然后依据排序字段进行排序，如果排序缓冲不够，则会将临时排序结果写入到一个临时文件中，最后合并临时排序文件，直接返回已经排序好的结果集。

优点：不需要读取记录两次，相对于two-pass，可以减少I/O开销。

缺点：由于要读入所有字段，排序缓冲可能不够，需要额外的临时文件协助进行排序，导致增加额外的I/O成本。

2.相关参数的设置和优化

相关参数如下。

max_length_for_sort_data：如果各列长度之和（包括选择列、排序列）超过了max_length_for_sort_data字节，那么就使用two-pass算法。如果排序BLOB、TEXT字段，使用的也是two-pass算法，那么这个值设置得太高会导致系统I/O上升，CPU下降，建议不要将max_length_for_sort_data设置得太高。

max_sort_length：如果排序BLOB、TEXT字段，则仅排序前max_sort_length个字节.

可以考虑的优化方向如下。

·加大sort_buffer_size。

一般情况下使用默认的single-pass算法即可。可以考虑加大sort_buffer_size以减少I/O。

需要留意的是字段长度之和不要超过max_length_for_sort_data，只查询所需要的列，注意列的类型、长度。MySQL目前读取和计算列的长度是按照定义的最大的度进行的，所以在设计表结构的时候，不要将VARCHAR类型的字段设置得过大，虽然对于VARCHAR类型来说，在物理磁盘中的实际存储可以做到紧凑，但在排序的时候，是会分配最大定义的长度的，有时排序阶段所产生的临时文件甚至比原始表还要大。MySQL 5.7版本在这方面做了一些优化，有兴趣的同学可以参考http://dev.MySQL.com/doc/refman/5.7/en/order-by-optimization.html

·对于two-pass算法，可以考虑增大read_rnd_buffer_size，但由于这个全局变量是对所有连接都生效的，因此建议只在会话级别进行设置，以加速一些特殊的大操作。

·在操作系统层面，优化临时文件的读写。

6.2.9　优化SQL_CALC_FOUND_ROWS

建议不要使用SQL_CALC_FOUND_ROWS这个提示，虽然它可以让开发过程变得简单一些，但并没有减少数据库所做的事情。例如以下这个查询。

SELECT SQL_CALC_FOUND_ROWS  col_name FROM table_name  where ... LIMIT N

它使用LIMIT子句限制了返回记录数，但实际上数据库仍然需要扫描大量记录以找到符合查询条件的所有记录。这是一个成本昂贵的操作。如果实在需要使用的话，建议使用独立的语句SELECT COUNT（*），这样做将会更高效。

一些统计值，如果可以缓存的话，那么缓存之更好。现实应用中，有时并没有必要显示记录的总数，或者不要求精确性，这时我们应该尽量减少这种消耗资源的查询。

6.2.10　优化临时表

如果不能利用索引排序，那么我们在MySQL中可能需要创建一个临时表用于排序。MySQL的临时表分为“内存临时表”和“磁盘临时表”，其中，内存临时表使用MySQL的MEMORY存储引擎。磁盘临时表使用MySQL的MyISAM存储引擎；一般情况下，MySQL会先创建内存临时表，但当内存临时表超过配置参数指定的值后，MySQL会将内存临时表导出到磁盘临时表。

触发以下条件，会创建临时表。

·ORDER BY子句和GROUP BY子句引用的列不一样。

·在连接查询中，ORDER BY或GROUP BY使用的列不是连接顺序中的第一个表。

·ORDER BY中使用了DISTINCT关键字。

通过EXPLAIN的Extra列可以查看是否用到了临时表：“Using temporary”表示使用了临时表。

如果查询创建了临时表（in-memory table）来排序或检索结果集，分配的内存大于tmp_table_size与max_heap_table_size参数之间的最小值，那么内存临时表就会转换为磁盘临时表（on-disk table），MySQL会在磁盘上创建磁盘临时表，这样会可能导致I/O瓶颈，进而影响性能。

·tmp_table_size：指定系统创建的内存临时表的最大大小。

·max_heap_table_size：指定用户创建的内存表的最大大小。

SHOW FULL PROCESSLIST命令输出的state列为“Converting heap to MyISAM”时表明临时表大于我们所设置的参数值，此时将会产生磁盘临时表，但是数据库执行查询往往很快，“Converting heap to MyISAM”这个状态不一定能及时被看到，我们需要关注Created_tmp_tables和Created_tmp_disk_tables这两个变量的变化。由于MySQL慢查询日志里没有使用临时表的信息，这就给我们诊断性能问题带来了一些不便，第三方的版本如Percona Server，在慢查询里可以有更详细的信息，将会记录临时表使用的情况，从而有助于我们诊断和调优。

如下情况也可能会导致使用到磁盘临时表。

·表中有BLOB或TEXT字段。

·使用UNION或UNION ALL时，SELECT子句中包含了大于512字节的列。

使用临时表一般意味着性能会比较低，特别是使用磁盘临时表时，性能将会更慢，因此我们在实际应用中应该尽量避免临时表的使用。

常见的避免临时表的方法有如下3点。

·创建索引：在ORDER BY或GROUP BY的列上创建索引。

·分拆长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件，因此设计表的时候，可以考虑将这些列分离到另外一张表中。

·不需要用DISTINCT时就没必要用DISTINCT，能用UNION ALL就不要用UNION。

6.3　OLAP业务优化

由于MySQL对于复杂SQL的优化不佳，所以对于一些OLAP的应用需要格外小心，在前期就做好一些针对性的设计，以尽量避免数据量剧增后碰到性能问题。关于SQL查询、索引优化，这里就不再赘述了。下面主要说下OLAP类型的业务需要

考虑的一些要点。

（1）使用冗余数据

有时最好的办法是在表中保存冗余的数据，虽然这些冗余数据有时也可以由其他的列推断得出。冗余数据可以让查询执行得更快。比如，我们可以增加一个专门的计数表或计数字段，实时更新计数信息。比如，大表之间的连接操作很耗时，增加冗余字段则可以有效地减少连接的表的个数。

（2）计算复用，使用缓存表

我们可以使用缓存表存储一些结果，这里所说的“缓存表”，意思是这些值在逻辑上是冗余的，可以从原始表中获取到，但显然从原始表中获取数据更慢。

（3）预计算

预先对一些常用的大查询生成汇总表。我们需要有这样一个意识，如果你需要处理大量数据，一般需要昂贵的计算成本。所以预计算往往是值得考虑的好方法。我们可以把查询结果存储到独立的汇总表中，或者可以把相关联的表的一些字段存放在一个独立的新表中，基于这个新的汇总表去做统计。

当我们使用缓存表和汇总表时，我们要做出决定：是实时更新数据还是定期更新，这依赖于你的应用。

当我们实时或定期重建缓存表、汇总表的时候，我们需要数据在操作的时间范围内仍然可用。我们可以采用一种“影子表”的方法，即建立一个临时表，在建立好之后，通过原子性地重命名表的操作，实现切换。

如下是重命名表，实现表切换的一个例子。

mysql> DROP TABLE IF EXISTS my_summary_new, my_summary_old;
mysql> CREATE TABLE my_summary_new LIKE my_summary;
mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;

我们保留my_summary_old表，用于以后进行回滚，可以一直保留到下次操作。

以上的方式，增加了写操作和维护的工作，但要想获得更高的性能，往往是需要付出一定代价的。通过这些方法可以极大地加速读操作，虽然要承担写操作更慢的代价。

（4）统计框架的改善

需要将一个复杂的查询任务放在一个SQL查询中来完成，往往会导致性能问题，使用这种方式最常见的原因是你正在使用一个编程框架或一个可视化组件库直接和数据源相连，然后在程序里直接展示数据，简单的商务智能和报表工具都属于这一分类。

一些报表框架，如果表设计不佳，那么随着数据量的增加，数据库将越来越力不从心，难以适应复杂的查询。组件或报表工具通常假设单个查询仅仅只用来完成一个简单的任务。但它鼓励你去设计更庞大的查询来生成报告中的所有数据。如果你使用某个这样的报表程序，就可能被迫去写一个复杂的SQL查询，而没有机会写代码操作结果集。

如果报表需求太复杂，不能用单个SQL查询来完成，那么更好的方案可能就是生成多个报表、增加一些限制条件。有时我们想从多个维度进行各种组合得出报表，但是，表的设计往往限制了这种可能性，反而会导致复杂的查询，甚至导致发布查询后，长时间无法得到响应。报表研发人员可以和用户沟通，限制一些查询的

使用，引导用户培养一些能够更快查询数据的习惯，让用户能够自己综合分析一些报表而不是完全借助计算机系统。如果你的老板不喜欢这样的解决方案，那么要提醒他报表的复杂度和生成报表所花的时间是成正比的。

小结

一般MySQL的优化有两个方向，一个是让SQL语句执行得更快，一个是让SQL语句做更少的事。比如，我们可以升级硬件让SQL跑得更快。或者，我们可以把小批量数据的排序交由应用程序去执行，MySQL不做排序计算。类似的方法有很多，但基本不外乎这两个方向。

MySQL的查询优化器比较简单，没有商业数据库那么强大和智能，我们应该理解MySQL的优化器限制，按优化器能理解的方式编写SQL。对于大流量的业务，应该尽量保持MySQL查询的简单性，以保证尽可能地支持更高的并发。现实中，对于数据库流量很大的业务，数据库往往已经退化为一个存储数据的容器，只利用它最高效的核心的特性。

来源于： MySQL DBA修炼之道

作者：陈晓勇

打赏

微信扫一扫，打赏作者吧～