对于刚入行的程序猿来说，如何优化MySQL查询，是必须跨过的坎。网上有很多关于SQL优化的博文，但大多是片段和结论。这里，我摘抄了《高性能MySQL》一书的内容，从全局的角度将MySQL查询优化的思路和要点进行串通，希望能帮助大家有一个系统性的认知。如果希望深入学习请阅读此书籍，并在实际开发中反复思考佐证。

一、MySQL基本架构

1、基本架构组成

Server层：包括连接器、查询缓存、分析器、优化器、执行器等。涵盖了mysql大多数核心服务功能，以及所有内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎：负责数据的存储和提取，插件式架构模式。

2、连接器

连接器负责跟客户建立连接、获取权限、维持和管理连接。用户连接里的权限，都依赖于登陆时用户名、密码通过后，在权限表里查出的用户权限，也就意味着，即使管理员账号对用户权限做了修改，也不会影响已经存在连接的权限。

客户如果长时间没有动静，连接器就会自动将它断开，这个时间由参数wait_timeout控制，默认8小时。当用户全部使用长连接后，你可能会发现，由些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的，这些资源在连接断开时才释放。

解决方案：1.定期断开长连接。或者程序里判断执行一个占用内存的大查询后，断开连接，之后查询再重连。2.MySQL5.7以上版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源，这个过程不需要重连和重做权限认证。

3、查询缓存

连接建立后，执行逻辑就会来到第二步：查询缓存。之前执行过的语句及其结果会以key-value的方式缓存在内存中，key是查询语句，value是查询结果。

查询缓存的失效非常频繁，只要对一个表的更新，这个表上所有的查询缓存都会被清空，所以静态表（比如系统配置表）适合查询缓存（二级缓存？），对于更新压力大的数据库来说，缓存命中率会非常低。

可以将参数query_cache_type设置成demand，这样对默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以使用SQL_CACHE显示指定。Select SQL_CACHE * from ……

需要注意的是Mysql8.0版本直接将查询缓存整块功能删除掉了。

4、分析器

分析器会先做词法分析，MySQL需要识别出你输入的SQL字符串代表什么。Selcet识别出来是查询语句，字符串T识别成表名T…，判断表是否存在，字段是否存在。然后做语法分析，根据语法规则，判断你输入的语法是否满足MySQL语法，相当于java的编译器。

5、优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在语句有多表关联的时候，决定各表的连接顺序。也就是MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就可以进入执行器阶段。

6、执行器

开始执行的时候，要判断一下你对这个表T有没有执行查询的权限。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

二、查询性能优化

1、为什么查询速度会慢

通常来说，查询的生命周期大致可以分为以下顺序：从客户端，到服务器，然后在服务器上进行解析，优化后生成执行计划，执行，并返回结果给客户端。其中执行可以认为是整个生命周期最重要的阶段，这其中包含了大量为了检索数据到存储引擎的调用，以及调用后的数据处理，包括排序和分组。

在完成这些任务的时候，查询需要在不同的地方花费时间,包括网络、CPU计算，生成统计信息和执行计划、锁等待等操作，尤其是向底层存储引擎检索数据的调用操作。优化和查询的目的就是减少和消除这些操作所花费的时间。

2、慢查询的基础：优化数据访问

查询性能低下的最基本原因是访问的数据太多，大部分性能低下的查询可以通过减少访问的数据量的方式进行优化：

1. 确认程序是否在检索大量超过需要的数据，这通常意味着访问太多行，但有时也可能是访问太多列。
2. 确认MySQL服务器层是否在分析大量超过需求的数据。

2.1 是否向数据库请求了不需要的数据

（1）查询不需要的记录

例如查询select查询大量的结果，然后获取前面N条结果后关闭结果集。分页查询使用逻辑分页也是一样，查询所有数据，只返给页面所需N条记录。最简单有效的方法就是在这样的查询中使用Limit。

（2）多表关联时返回全部列

“select * from..”总是取出全部列，会让优化器无法完成索引覆盖扫描这列优化，还会为服务器带来额外的I\O，内存和CPU消耗。因此一些DBA严格禁止SELECT * 的写法。但是在许多实际开发中，查询返回超过需要的数据也不总是坏事，因为这种有点浪费数据库资源的方式可以简化开发，提高相同代码片段的复用性（一组数据结果可供多个接口使用）。获取并缓存所有列的查询，相比多个独立的只获取部分列的查询可能更有好处。

（3）重复查询相同的数据。

在程序中很容易出现这样的逻辑错误——不断执行相同的查询，并返回相同数据。例如，博客中用户评论功能中，需要查询用户头像，那么用户多次评论的时候，可能会反复查询头像数据。比较好的方案是，在初次查询的时候就将头像数据缓存起来。

2.2 MySQL是否在扫描额外记录

在确定查询只返回需要的数据后，接下来应该看看为了需要的结果是是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销三个指标如下：

• 响应时间
• 扫描行数
• 返回行数

这三个指标都会记录在MySQL慢日志中。

（1）响应时间

响应时间是两部分之和：服务时间和排队时间（一般常见的是等待I/0操作、行锁等等）。响应时间可能是单个查询的问题，也可能是服务器问题等等。

（2）扫描的行数和访问类型

MySQL有好几种访问方式可以查找并返回一行结果。有些方式可能需要扫描很多行才会返回一行结果，也有些访问方式可能无须扫描就返回结果。

在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种，从全表扫描，索引扫描，范围扫描、唯一索引查询、常数引用等。

一般MySQL可以用如下三种方式应用where条件，从好到坏依次是：

• 在索引中使用where条件来过滤不匹配的记录，这是在存储引擎完成的。
• 使用索引覆盖扫描（在extra列中出现了Using Index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成，但无需再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（在extra列中出现了Using Where）,这在MySQL服务器层完成，MySQL需要先从数据表中读出记录然后过滤。

所以好的索引可以让查询使用适合的访问类型，尽可能地只扫描需要的数据行。如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试以下的方式去优化它：

• 使用索引覆盖扫描，把所有需要用的列都放在索引中（比如多字段的聚合索引），这样存储引擎无须回表获取对应的行就能返回结果。
• 改变库表结构，例如使用单独的汇总表。
• 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

例子：

Collection_info 表有100万条数据,id为主键，collection_id，java中查询语句collection_id中和collect_time由参数传递

1
2
3
4
1Select id,device_id,device_name,collection_id,collection_type,collection_name,collection_value,collect_time 

2from collection_info 

3where collection_id=352 and (collect_time between CONCAT('2018-12-01',' 00:00:00') and CONCAT ('2018-12-01',' 23:59:59')) 

4

这么一条sql语句执行时间：3.992s

EXPLAIN查看执行计划

虽然type达到ref级别，但扫描行rows16350，这是由于单索引collecion_id选择性（基数）太差，也就是重复数多。extra使用Useing where。

优化：

对collection_id，collect_time两字段使用组合索引（where 条件求交，即and，使用组合索引往往优于单索引，但要注意索引顺序）。另一方面因为B-TREE索引，collect_time的范围查询只需要找出首点数据，就能连续性导出范围数据。

1
2
1Alter table collection_info add index idx_collection_id_time (collection_id，collect_time)

2

EXPLAIN查看执行计划

很明显扫描行数得到了很大优化。

执行时间：0.061s

后期，我又将collection_id，collect_time调整为primary key 即主键，查询速度为0.020s，这是因为innodb引擎默认使用主键来建立聚簇索引（聚集单页数据），原来以id为主键时，相同collecion_id的数据存放在不同的块中，最糟糕的情况是每一条查询可能都会导致一次I/O。后期优化后，也就是同collecion_id的相关的数据都紧邻存放在同一个块中，减少I/O次数，提高了I/O效率。另一方面，聚簇索引页节点直接存放数据，普通索引页节点存放指针，还得再走一次聚簇索引。

3、重构查询方式

3.1 一个复杂的查询还是多个简单的查询

在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的原因在于以前总是认为，网络通信、查询解析和优化是一件代价很高的事情。

但是这对MySQL并不适用。MySQL连接和断开都很轻量级，在返回一个小的查询结果集方面很高效。现在的网络速度比以前快很多，无论是带宽还是延迟。

在一个通用的服务器上，MySQL能运行每秒超过10万条查询，即使1000兆网卡，也能轻松满足每秒超过2000次查询。MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多。在其他条件相同的情况下，使用尽可能少的查询当然更好，但有时将一个大查询分解成多个小查询也是有必要的。

3.2 切分查询

将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

删除旧数据就是一个很好的例子，如果一个大的语句一次性完成的话，则可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻碍很多小的但重要的查询。将一个大的delete语句切分成多个较小的查询语句可以尽可能小的影响MySQL性能，同时还可以减少复制的延迟，例如每个月需要运行一次下面的查询。

DELET FROM message where create < DATE_SUB(NOW(),INTERVAL 3 MONTH)

那么我们可以用类似下面的办法来完成工作：

1
2
3
4
5
6
7
1rows_affected=0

2do{

3    rows_affected=do_query(

4        DELET FROM message where create < DATE_SUB(NOW(),INTERVAL 3 MONTH) limit 10000

5    )

6} while rows_affected > 0

7

3.3 分解关联查询

可以对每个表进行一次单表查询，然后将结果在应用程序中关联。

好处：

• 让缓存效率更高。许多应用程序可以方便的缓存单表查询的结果集，另外，关联中的某个表发生了变化，那么就无法使用查询缓存了，拆分后，单表之间不受影响。
• 执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库拆分，更容易做到高性能和扩展性。
• 查询效率本身可能会有所提升。
• 可减少冗余记录的查询。在应用层做关联查询，意味着对某条记录应用只需要查询一次，而在数据库中做关联查询可能需要重复地访问一部分数据。
• 更进一步，这样做相当于在应用层实现了哈希关联，而不是MySQL的嵌套循环关联。

4、查询执行的基础

当希望MySQL能以更的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。下图为一条查询语句的执行路径：

4.1 MySQL客户端/通信协议

MySQL客户端和服务器之间的通信协议是半双工的，在任何一个时刻，要么由客户端向服务器发送数据，要么由服务器向客服端发送数据，这两个动作不能同时发生。这种协议让MySQL通信简单快速，但也带来诸如没法进行流量控制等限制。

客户端用一个单独的数据包将查询传递给服务器，这也是为什么当一个查询语句很长时，参数max_allowed_packet就特别重要了，比如批量查询时一次性传递N条的SQL组合语句。

相反地一般服务器返回给客户端的数据很多，由多个数包组成，客户端必须完整地接收返回结果，而不能简单地只取前面几条结果，然后让服务器终止数据发送，这也是在必要的时候一定要在查询中加上limit的原因（limit可终止查询）。

4.2 查询缓存

一个查询执行过程中，MySQL会优先检查这个查询是否命中查询缓存中的数据，这个检查是通过一个对大小写敏感的哈希查找来实现的。如果命中缓存，再检查用户权限。

4.3 查询优化处理

Mysql能够处理的优化类型：

• 重定义关联表的顺序
• 将外连接转换成内连接
• 使用等价变换规则
• 优化count()、max()和min()
• 预估并转化为常数表达式
• 覆盖索引扫描
• 子查询优化
• 提前终止查询
• 等值传播
• 列表in()的比较

(1)MySQL如何执行关联查询

MySQL关联执行的策略很简单：对任何关联查询都执行循环嵌套关联操作，即MySQL先从一张表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配行，依次下去，直到找到所有表中的匹配行（类似菜单迭代的过程），按照这样的方式查找第一表记录，再嵌套查询下一个关联表，然后再回溯到上一个表。

请看下面的例子：

用伪代码表示：

MySQL再from子句中遇到子查询时，先执行子查询并将结果集放到一个临时表中，然后进行循环嵌套关联查询。注意临时表是没有任何索引的，所以能用表关联提升查询效率时，就代替子查询。

对于Union查询，MySQL会将一系列单个查询放入一张临时表，然后再重新读出临时表的数据来完成查询操作。

（2）关联顺序优化

MySQL会选择适合的关联顺序（通常小表驱动），来让查询执行成本尽可能低，不过如果有超过n多个表，优化器不可能逐一评估每一中关联的成本，则会使用贪婪搜索方式。

（3）排序优化

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免大数据排序（索引排序另当别论）。当不能使用索引生成排序结果时，MySQL需要自己进行排序，如果数据量小于排序缓冲区，使用内存快速排序，如果内存不够排序，MySQL会先将数据分块，对每个独立的块使用快速排序，并将各个块排序结果存放在磁盘上，最后将各个块合并返回排序结果。

MySQL在进行文件排序的时候，对没一个排序记录都会分配一个足够长的定长空间来存放，那排序时所需要的临时存储空间往往比磁盘上的表大很多。

MySQL5.6对limit的order by 做了改进。当只需要返回部分结果时，例如使用了limit子句，MySQL不再对所有结果进行排序，而是根据实际情况，抛弃不满足条件的结果，然后再进行排序。

5、查询执行引擎

执行阶段，MySQL只是简单地根据执行计划（一种数据结构）给出的指令逐步执行。在执行过程中有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口称为“handler API”，查询中的每一个表由一个handler实例表示。

实际上MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口获取表的相关信息，包括表的所有列，索引统计信息（不同的促存储引擎统计方式不同），等等。

是在表里面有多个索引的时候，决定使用哪个索引；或者在语句有多表关联的时候，决定各表的连接顺序。也就是MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就可以进入执行器阶段。但底层TCP可能对MySQL的封包缓存一部分后批量传送。

6、返回结果给客户端

MySQL将结果返回给客户端是一个增量，逐步返回的过程。例如前面的关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果，MySQL就开始向客户端逐步返回结果集了。这样服务器就无需为存储太过结果而消耗大量内存，客户端也能在第一时间获得返回结果。

开始执行的时候，要判断一下你对这个表T有没有执行查询的权限。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。