MySQL调优之查询优化

实际工作中，有时候打开一个页面响应时间非常慢，这背后通常牵涉到SQL语句查询慢的问题。

前面我们提到很多数据库结构设计，建索引等来视图提高MySQL的性能。但是如果我们实际业务场景中，SQL查询语句写的不合适，索引建的再好可能也达不到预期的高性能。

因此，我们很有必要对查询进行分析，我写的查询为什么慢，该怎么样对查询进行优化。

查询慢的原因

一般情况下，查询可以看成按如下顺序执行任务：由客户端向服务端发起查询请求，然后在服务器端进行解析，生成执行计划，执行，最后将结果返回给客户端。

在完成上述查询任务的时候，查询需要在不同的地方话费相应的时间，比如网络，CPU计算，生成统计信息和执行计划，锁等待等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作，CPU操作和内存不足时导致的IO操作上消耗时间。

不同的存储引擎，还会产生大量的上下文切换以及系统调用。

总之，查询慢的原因无外乎以下几点：

• 网络
• CPU
• IO
• 上下文切换
• 系统调用
• 生成统计信息和执行计划
• 锁等待时间

优化数据访问

查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化。

对于低效查询，我们可以作如下分析：

1. 确认应用程序是否在检索大量超过需要的数据。一旦确认查询了大量的数据，说明访问了太多的行，也可能是访问了太多的列。
2. 确认MySQL服务器层是否在分析大量超过需要的数据行

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

查询不需要的记录

我们常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果再进行计算，在日常的开发习惯中，经常是先用select语句查询大量的结果，然后获取前面的N行后关闭结果集。

优化方式是在查询后面添加limit。

多表关联时返回全部列

还是用sakila数据库。

现在想查询所有在电影Academy Dinosaur中出演的演员，涉及actor，film_actor以及film三个表。

上来我就这样写：

select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';

返回了三个表的所有列。

其实人家只是想看所有演员就行了，没必要查出来那么多列，改成这样：

mysql> select actor.* from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE     | 2006-02-15 04:34:33 |
|       20 | LUCILLE    | TRACY     | 2006-02-15 04:34:33 |
|       30 | SANDRA     | PECK      | 2006-02-15 04:34:33 |
|       40 | JOHNNY     | CAGE      | 2006-02-15 04:34:33 |
|       53 | MENA       | TEMPLE    | 2006-02-15 04:34:33 |
|      108 | WARREN     | NOLTE     | 2006-02-15 04:34:33 |
|      162 | OPRAH      | KILMER    | 2006-02-15 04:34:33 |
|      188 | ROCK       | DUKAKIS   | 2006-02-15 04:34:33 |
|      198 | MARY       | KEITEL    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
10 rows in set (0.00 sec)

只返回四列就可以了。

用show status like 'last_query_cost';分别查一下执行时间，第二个查询的时间少于第一种。

总是取出全部列

在公司的实际需求中，禁止使用select *，虽然这种方式能够简化开发，但是会影响查询的性能，所以尽量不要使用。

取出全部列，会让MySQL的优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的IO，内存和CPU消耗。

重复查询相同的数据

如果需要不断的重复执行相同的查询，且每次返回完全相同的数据，基于这样的应用场景，我们可以将这部分数据缓存起来，这样的话能够提高查询效率

执行过程的优化

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前会检查用户权限，如果权限没有问题，那么MySQL会跳过所有的阶段，就直接从缓存中拿到结果并返回给客户端。

查询缓存这东西MySQL 8版本已经废弃不用了。缓存有大名鼎鼎的redis供我们使用。

查询优化处理

MySQL查询完缓存之后会经过以下几个步骤：解析SQL、预处理、优化SQL执行计划，这几个步骤出现任何的错误，都可能会终止查询。

语法解析器和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗解析树，MySQL解析器将使用MySQL语法规则验证和解析查询。

例如验证使用了错误的关键字或者顺序是否正确等等，预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等等。

查询优化器

当语法树没有问题之后，相应的要由优化器将其转成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器的最主要目的就是要选择最有效的执行计划。

MySQL使用的是基于成本的优化器，在优化的时候会尝试预测一个查询使用某种查询计划时候的成本，并选择其中成本最小的一个。

经过一些列的统计信息

例如

mysql> select count(*) from film_actor;
+----------+
| count(*) |
+----------+
|     5462 |
+----------+
1 row in set (0.03 sec)

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1104.399000 |
+-----------------+-------------+
1 row in set (0.00 sec)

可以看到这条查询语句大概需要做1104个数据页才能找到对应的数据，这是经过一系列的统计信息计算来的：

• 每个表或者索引的页面个数
• 索引的基数
• 索引和数据行的长度
• 索引的分布情况

MySQL会选择错误的执行计划？

很多情况下，MySQL会选择错误的执行计划，因为：

• 统计信息不准确

InnoDB因为其mvcc的架构，并不能维护一个数据表的行数的精确统计信息，MySQL给出的一般是预估的统计。

• 执行计划的成本估算不等同于实际执行的成本

有时候某个执行计划虽然需要读取更多的页面，但是他的成本却更小，因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小。

MySQL层面并不知道哪些页面在内存中，哪些在磁盘，所以查询之际执行过程中到底需要多少次IO是无法得知的。

• MySQL的最优可能跟你想的不一样

MySQL的优化是基于成本模型的优化，但是有可能不是最快的优化。

• MySQL不考虑其他并发执行的查询
• MySQL不会考虑不受其控制的操作成本

执行存储过程或者用户自定义函数的成本。

优化器的优化策略

静态优化

直接对解析树进行分析，并完成优化。

动态优化

动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关。

MySQL对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估。

优化器的优化类型

重新定义关联表的顺序

数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序是优化器很重要的功能。

将外连接转化成内连接，内连接的效率要高于外连接

使用等价变换规则，MySQL可以使用一些等价变化来简化并规划表达式。

优化count(),min(),max()

索引和列是否可以为空通常可以帮助MySQL优化这类表达式。

例如，要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较。

预估并转化为常数表达式，当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行处理

比如这个，就把film.film_id=1转化成了常数来执行，提升了效率：

mysql> explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: const
         rows: 10
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

索引覆盖扫描，当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引

之前的MySQL优化文章，有覆盖索引的例子，此处不在举例了。

子查询优化

MySQL在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问，例如将经常查询的数据放入到缓存中。

等值传播

如果两个列的值通过等式关联，那么MySQL能够把其中一个列的where条件传递到另一个上。

看这个例子

select film.film_id from film inner join film_actor 
using(film_id) where film.film_id > 500;

select film.film_id from film inner join film_actor 
using(film_id) where film.film_id > 500 and film_actor.film_id > 500;

这两个查询完全是等效的，就是因为等值传播。

注：这里只是普及一下等值传播的概念，实际工作中不会有人用第二种写法吧？

关联查询（join）

MySQL的关联查询很重要，但其实关联查询执行的策略比较简单：

MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一张表中循环取出单条数据，然后再嵌套到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。

然后根据各个表匹配的行，返回查询中需要的各个列。

MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行之后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，以此类推迭代执行。

整体的思路如此，但是要注意实际的执行过程中有多个变种形式。

join的实现方式原理

• Simple Nested-Loop Join，Simple NLJ
  

r为驱动表，s为匹配表。从r中分别取出每一个记录去匹配s表的列，然后再合并数据，对s表进行r表的行数次访问，对数据库的开销比较大。

• Index Nested-Loop Join，Index NLJ
  

这个要求非驱动表s上有索引，可以通过索引来减少比较，加速查询。

在查询时，驱动表r会根据关联字段的索引进行查找，当在索引上找到符合的值时，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。

如果非驱动表s的关联建是主键的话，性能会非常高，而如果不是主键，要进行多次徽标查询，先关联索引，然后根据二级索引的主键进行回表操作，性能上比索引是主键要慢。

• Block Nested-Loop Join，Block NLJ

如果join的关联表有索引，会采取Index Nested-Loop Join，但如果join列没有索引，就会采用Block Nested-Loop Join。

由图可以看到中间有个join buffer缓冲区，它是将驱动表r的所有join相关的列都先缓存到join buffer中，然后批量与匹配表s进行匹配，将Simple NLJ的多次比较合并为一次，降低了非驱动表s的访问频率。

默认情况下join_buffer_size为256K，在查找的时候MySQL会将所有的列缓存到join buffer中，包括select的列，而不是仅仅缓存关联的列。

再一个有N个join关联SQL当中会在之行时候分配N-1个join buffer。

（1）Join Buffer会缓存所有参与查询的列而不是只有Join的列。

（2）可以通过调整join_buffer_size缓存大小

（3）join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。

（4）使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。

案例：查看不同的顺序执行方式对查询性能的影响

explain select film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);

show status like 'last_query_cost'; 

explain select straight_join film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);

show status like 'last_query_cost'; 

可以看到，按照第一种方式，MySQL自己的join查询顺序为actor->film_actor->film，过滤的行最大为200；

而按照straight_join指定join的查询顺序（一般我们预想的也是这个顺序）是这样的：film->film_actor->actor，这样先查询了film的1000行记录，数据量比第一种方式大，耗时也长一些。

排序优化

无论如何排序都是一个成本很高的操作，所以从性能的角度出发，应该尽可能避免排序或者尽可能避免对大量数据进行排序。

推荐使用利用索引进行排序，但是当不能使用索引的时候，MySQL就需要自己进行排序，如果数据量小则在内存中进行，如果数据量大就需要使用磁盘，MySQL中称之为filesort。

如果需要排序的数据量小于排序缓冲区(show variables like ‘%sort_buffer_size%’????，MySQL使用内存进行快速排序操作；

如果内存不够排序，那么MySQL就会先将树分块，对每个独立的块使用快速排序进行排序，并将各个块的排序结果存放再磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。

优化特定类型的查询

优化count()

count()是特殊的函数，有两种不同的作用，一种是某个列值的数量，也可以统计行数。

MyISAM存储引擎的count函数比较快吗？它的快是有前提条件的，只有没有任何where条件的count(*)才是比较快的。

使用近似值

在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值。

在很多OLAP的应用中，需要计算某一个列值的基数，有一个计算近似值的算法叫hyperloglog。

更复杂的优化

一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统。

优化关联查询

• 确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序

当表A和表B使用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表的相应列上创建索引。

• 确保任何的group by和order by中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程

优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替。

优化limit分页

优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列。

在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的order by 的子句，如果这种方式有索引的帮助，效率通常不错，否则的话需要进行大量的文件排序操作。

还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。

要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能。

我们来比较一下这两个查询：

mysql> explain select film_id,description from film order by title limit 50,5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

Using filesort，rows是1000，再来看一下达到同样目的的另一个SQL：

mysql> explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 55
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: film
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: lim.film_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: film
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_title
      key_len: 514
          ref: NULL
         rows: 55
     filtered: 100.00
        Extra: Using index

使用的是Using index，而且rows只有50多行，效率明显有提升。

优化union查询

MySQL总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。

经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化。

注：除非确实需要服务器消除重复的行，否则一定要使用union all，如果没有all关键字，MySQL会在查询的时候给临时表加上distinct关键字，这个操作的代价很高。

推荐使用用户自定义变量

自定义变量的使用

mysql> set @one :=1;

mysql> set @min_actor :=(select min(actor_id) from actor);

mysql> select @one;
+------+
| @one |
+------+
|    1 |
+------+

mysql> select @min_actor;
+------------+
| @min_actor |
+------------+
|          1 |
+------------+

mysql> set @last_week :=current_date-interval 1 week;

mysql> select @last_week;
+------------+
| @last_week |
+------------+
| 2020-08-11 |
+------------+

注：自定义变量只对当前会话有效，当退出客户端，再连上MySQL，就没有这些定义的变量了。

自定义变量的限制

• 无法使用查询缓存
• 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句
• 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信
• 不能显式地声明自定义变量地类型
• MySQL优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行
• 赋值符号:=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号
• 使用未定义变量不会产生任何语法错误

自定义变量的使用案例

案例来自《高性能MySQL（第三版）》

优化排名语句

在给一个变量赋值的同时使用这个变量

mysql> set @rownum:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;
+----------+--------+
| actor_id | rownum |
+----------+--------+
|       58 |      1 |
|       92 |      2 |
|      182 |      3 |
|      118 |      4 |
|      145 |      5 |
|      194 |      6 |
|       76 |      7 |
|      112 |      8 |
|       67 |      9 |
|      190 |     10 |
+----------+--------+
10 rows in set (0.00 sec)

查询获取演过最多电影的前10名演员，然后根据出演电影次数做一个排名

mysql> select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;
+----------+-----+
| actor_id | cnt |
+----------+-----+
|      107 |  42 |
|      102 |  41 |
|      198 |  40 |
|      181 |  39 |
|       23 |  37 |
|       81 |  36 |
|      144 |  35 |
|       37 |  35 |
|      106 |  35 |
|       60 |  35 |
+----------+-----+
10 rows in set (0.00 sec)

避免重新查询刚刚更新的数据

当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么

方式1：

update t1 set  lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;

方式2，使用变量：

update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;

方式2看起来也是两个查询，需要两次网络来回，但是它无需访问任何数据表，所以会快很多。

确定取值的顺序

在赋值和读取变量的时候可能是在查询的不同阶段

例如，在select子句中进行赋值，然后在where子句中读取变量，则可能变量取值并非预期的值。

eg.1

mysql> set @rownum:=0;

mysql> select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;
+----------+------+
| actor_id | cnt  |
+----------+------+
|       58 |    1 |
|       92 |    2 |
+----------+------+

因为where和select在查询的不同阶段执行，所以看到查询到两条记录，这不符合预期

eg.2

mysql> set @rownum:=0;

mysql> select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name;

当引入了order by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的。

解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：

mysql> set @rownum:=0;

mysql> select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;
+----------+------+
| actor_id | cnt  |
+----------+------+
|       58 |    1 |
+----------+------+

参考资料

• 《高性能MySQL（第3版）》
• MySQL官方网站 https://dev.mysql.com/doc/refman/5.7/en/

推荐阅读

• 捅破窗户纸-入门MySQL调优之性能监控
• 结合案例说明MySQL的数据类型如何优化
• 将优化考虑在最前面-MySQL数据库设计优化：范式与反范式，主键，字符集，存储引擎
• MySQL优化必备之执行计划explain，索引基本知识，索引数据结构推演
• MySQL通过索引优化-这里可能有你不知道的索引优化细节（一）
• MySQL通过索引优化-这里可能有你不知道的索引优化细节（二）

熬夜不易，且行且珍惜！