uncategorized

MySQL查询性能优化（二）

在发现查询效率不高时，首先就需要考虑查询语句的设计是否合理。如下将会介绍一些查询优化技巧，然后在介绍一些MySQL优化器内部的机制，并展示MySQL是如何执行查询的。最后探索查询优化的模式，以帮助MySQL更有效地执行查询。

优化数据访问

查询性能低下的最基本原因是访问的数据太多了。因此大部分的性能低下查询都可以通过减少访问的数据量进行优化。减少数据访问量通常意味着访问了太多的行，但有时也可能是访问了太多的列。在查询时如果仅需要查询结果集中的前某些行，则最简单的方式是在查询语句的最后加上limit。在进行多表关联查询时应尽量避免使用select *，因为它返回表的所有列，但是这些列可能并不都是必须的。除了请求了不需要的数据，还需要查看MySQL是否在扫描额外的记录，其中可以通过扫描行数和返回行数进行衡量。如果发现查询中需要扫描大量的数据但是只返回少数的行，通常可以：

  1. 使用索引覆盖扫描，把所有需要的列都放入索引，这样存储引擎无须回表获取对应行就可以返回结果；
  2. 改变库表结构；
  3. 重写这个复杂的查询，让MySQL优化器能够以更优的方式执行这个查询。

重构查询方式

设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。在传统的实现中总是强调数据库层完成尽可能多的工作，这样的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。但是这样的想法对于MySQL并不适用，MySQL从设计上连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。

分解关联查询：很多高性能的应用都会对关联查询进行分解，简单地说就是对每个表进行一次单表查询，然后将结果在应用程序中进行关联。如下图所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select id from tb_class where Name = '计算机1班';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
mysql> select id from tb_student where classId=1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)
mysql> select * from tb_score where stuId in(1,2);
+----+-----------+-------+-------+
| id | course    | Score | stuId |
+----+-----------+-------+-------+
| 11 | 计算机    |    80 |     1 |
| 12 | 数据库    |    90 |     1 |
| 13 | 计算机    |   100 |     2 |
| 14 | 数据库    |    97 |     2 |
+----+-----------+-------+-------+
4 rows in set (0.01 sec)

那么这么分解的好处又在哪里呢？

  1. 首先是让缓存的效率更高。许多应用程序可以方便的缓存单表查询对应的结果对象。如已经缓存了计算机1班对应的id为1，tb_student表中1班的学生有1号和5号，从而可以从成绩表中查询1号和2号学生的成绩；
  2. 其次查询分解后，执行单个查询可以减少锁竞争；
  3. 再次查询本身效率也会有所提升。如上使用in代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联更加高效；
  4. 最后分解关联查询可以减少冗余记录的查询，在应用层做关联查询时，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。

查询执行的基础

当希望MySQL能够以较高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。如下图展示了向MySQL发送一个请求时MySQL具体的操作过程：
image

  1. 首先服务器接收到一条客户端请求，先检查查询缓存，如果命中缓存，则立刻返回缓存中的数据，否则进入下一阶段；
  2. 服务器进行SQL解析、预处理，再由优化器生成对应的执行计划；
  3. MySQL根据优化器生成的执行计划，调用存储引擎的API执行查询；
  4. 将结果返回给客户端。

第一步是MySQL客户端/服务器通信

二者之间通信协议是“半双工”的，也就是说在某一时刻只能有一方在发送数据。在任何一个时刻MySQL连接都有一个状态，该状态表示MySQL当前的工作，通过SHOW FULL PROCESSLIST命令查询状态。其中状态有Sleep、Query、Locked、Analyzing and statistics、Coping to tmp table、Sorting result、Sending data。

第二步是查寻缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。通常是通过一个对大小写敏感的Hash查找实现。如果命中，那么在返回结果前MySQL会检查一次用户权限，该过程无须解析查询SQL语句。如果未命中，则解析SQL语句。

第三步是查询优化处理

包括解析SQL、预处理、优化SQL执行计划，其中出现任何错误都会终止查询。

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。

查询优化器负责将解析树转化成执行计划，优化器的作用就是找到查询的较优执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本（SHOW STATUS LIKE ‘Last_query_cost’），并选择成本最小的一个。查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。

优化策略分为：静态优化和动态优化。

静态优化可以直接对解析树进行分析，并完成优化。例如，优化器可以通过简单的代数变换将where条件转换成另一种等价形式，静态优化不依赖于特别的数值，如where中带入的常数。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行也不会发生变化，可以认为是一种“编译时优化”。

动态优化是上下文相关的，如where条件中取值、索引条目对应的数据行数等，是一种“运行时优化”。如下是MySQL能够处理的优化类型：

  1. 重新定义关联表的顺序：数据表的关联并不总是按照查询中指定的顺序进行。
  2. 将外连接转化为内连接：并不是OUTER JOIN语句都必须以外连接的方式执行。如where条件、库表结构都可能会让外连接等价于一个内连接；
  3. 使用等价变换：MySQL使用等价变换来规范表达式。如（a10 and b=c；
  4. 优化count、min、max
  5. 覆盖索引扫描：当索引中的列包含所需要的列时，MySQL使用索引返回需要的数据，不需要查询对应的行数据；
  6. 子查询优化：将子查询转化一种效率更高的形式，从而减少多个查询多次对数据的访问；
  7. 提前终止查询：使用limit时，发现已经满足查询需求时，MySQL能够立刻终止查询；
  8. 列表in比较：MySQL中in不等同于多个or条件的子句，因为MySQL首先对in中的数据进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，该时间复杂度为o(logn)，而多个or查询的时间复杂度为o(n)。

当MySQL需要对选择的数据进行排序时，如果无法使用索引进行排序，那么MySQL在数据量小则在内存中进行排序，如果数据量大则需要磁盘进行排序，不过MySQL将这一过程统一称为文件排序（filesort）。

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作，如果内存不够排序，MySQL先对数据进行分块，然后对每个独立的块使用“快速排序”,并将各块排序结果放入磁盘，然后将各个排好序的块进行合并（merge）。

在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序，如果order by子句中的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序，则MySQL的EXPLAIN结果的extra字段就会有“using filesort”。

除此之外的其他情况，MySQL都会先将关联结果放到一个临时表中，然后在所有关联都结束后再进行文件排序，此时的MySQL的EXPLAIN结果的extra字段值为“Using temporary;Using filesort”。

如果查询中有limit的话，limit也会在排序之后应用，所以即使返回较少的数据，临时表和需要排序的数量仍会非常大（MySQL5.6的limit子句在此处已经做了改进）。

第四步是查询执行引擎

MySQL根据执行计划给出的指令逐步执行，在该过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，也就是“Handler API”。MySQL在优化阶段就为每个表创建一个handler实例，优化器根据这些实例的接口获取表的相关信息。

最后一步就是将查询的结果返回给客户端

MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始想客户端逐步返回结果。这样有两个好处：一是服务器端无须存储太多的结果；二是结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，从而是客户端可以在第一时间获得返回的结果。

优化特定类型的查询

一、 优化count查询。
如果指定了列，则查询该列不为null的行数，如果为count(*)则查询总行数。
二、优化关联查询，确保on或者using子句中的列上有索引。确保group by和order by的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化整个过程。
三、优化group by和distinct。MySQL使用同样的方法优化这两类查询，通常是利用索引的顺序性进行优化。但是如果无法使用索引，group by使用两种策略来完成：使用临时表或者文件排序来做分组。
四、优化limit分页，使用延迟关联的方式来优化limit分页；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> explain extended select * from A order by name limit 5;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | A     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 2 warnings (0.01 sec)
mysql> explain extended select * from A inner join(select id from A order by name limit 5) as B using(id);
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref          | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------+------+----------+-------------+
|  1 | PRIMARY     | A          | NULL       | ALL   | PRIMARY       | NULL        | NULL    | NULL         |    3 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>   | <auto_key0> | 4       | index_q.A.id |    2 |   100.00 | Using index |
|  2 | DERIVED     | A          | NULL       | index | NULL          | name        | 37      | NULL         |    3 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------+------+----------+-------------+
3 rows in set, 2 warnings (0.01 sec)

五、优化UNION查询。MySQL通过创建并填充临时表的方式来执行UNION查询，因此需要手工的将where、limit、order by等子句“下推”到UNION的各个子查询中，除非确实需要服务器消除重复的行，否则一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上distinct，从而对临时表的数据做唯一性检查，这样代价非常高。

总结

综上所有的内容可知，创建高性能应用程序要考虑schema、索引、查询语句以及查询优化等问题。理解查询是如何被执行的以及时间都消耗在哪些地方，从而针对耗时大的查询语句进行改进。

分享到： QQ空间 新浪微博 腾讯微博 人人网 微信