你一定要知道的SQL优化技巧

最新推荐文章于 2022-05-22 17:47:04 发布

沸羊羊_

最新推荐文章于 2022-05-22 17:47:04 发布

阅读量3k

点赞数 23

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_42653522/article/details/119294998

版权

MySQL 专栏收录该内容

18 篇文章

订阅专栏

前言

前面的文章介绍过了索引、锁、复制与恢复、日志等 MySQL 的底层原理，本文主要从实战的角度出发，讲解如何合理的设计查询，因为即使查询写的很糟糕，即使表结构再合理，索引再合适，也无法实现高性能。本文会介绍一些查询设计的基本原则，以及一些更深的查询优化的技巧。

在优化查询前先要了解在完成一个查询的过程中，查询需要在哪些地方花费时间，包括网络、CPU计算、生成统计信息和执行计划、锁等待等操作，尤其是向存储引擎检索数据的调用操作，这些调用需要在内存操作，CPU操作和内存不足时导致的 I/O 操作上消耗时间。根据引擎不同，可能还会产生大量的上下文切换以及系统调用。

在每一个消耗大量时间的查询案例中，都会存在一些不必要的额外操作，某些操作被额外的重复了很多次、某些操作执行的太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。

优化查询

只查询需要的数据

在优化前我们要先确保，我们给出的查询范围是否是全部需要的，有些查询性能低下的原因是因为访问的数据太多，而实际使用中又不需要全部数据，这就给 MySQL 服务器带来了额外的负担，并增加网络开销等。

典型案例：

如果你想查询所在电影《Academy Dinosaur》中出现的演员，切忌用以下写法查询：

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

这将会返回三个表的全部数据类，正确的方式应该只取需要的列，例如：select actor.* ...

每次见到select * 都要用怀疑的眼光进行审视，是不是真的需要所有列，很可能并不是必需的，取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的 I/O，内存和CPU开销，实际开发中要禁止使用 select *这样的的写法。

是否扫描了额外的记录

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

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

响应时间

是直观的表示SQL是否是慢SQL的指标，但只是表面上的值。响应时间分为：服务时间和排队时间。服务时间值的是数据库处理这个查询真正花费的时间；排队时间指的是服务器因为等待某种资源而没有真正执行查询的时间，可能是等待I/O操作完成，也可能是等待行锁，等等。我们无法把响应时间细分到到这两个部分上面。

当看到一个查询的响应时间的时候，首先要问问自己，这个响应是否是一个合理的值。实际上可使用“快速上限估计”法来估算查询的响应时间。

扫描行数和返回行数

分析查询时，查看该SQL扫描的行数是非常有帮助的，在一定程度上能说明该查询找到需要的数据的效率高不高。理想情况下，扫描行数和返回行数是相同的，但这种情况往往并不多，让扫描的行数和返回的行数尽量小会提升SQL执行效率。

在 EXPLAIN 语句中的 type 列反映了访问类型，访问类型包括很多种：全表扫描、索引扫描、范围扫描、唯一索引扫描、常熟引用等。这些类型是从慢到快进行排列的。

如果查询没有办法找到合适的访问类型，那么最好的办法通常是增加一个合适的索引，索引会让 SQL 以最高效、扫描行数最少的方式找到需要的记录。

典型案例：

例如，我们从示例数据库查询数据：

SELECT * FROM tb_user WHERE user_code = "17317062260";

此查询将返回1条记录，从 EXPLAIN 的结果来看，MySQL 在索引 idx_tb_user_user_code 上使用了 ref 访问类型来执行查询。

EXPLAIN 的结果显示 MySQL 需要访问 1 行数据，查询优化器认为这种访问类型可以高效的完成查询。那如果没有索引的情况，查询效率会怎么样？

将索引 idx_tb_user_user_code 删除后，访问类型变成了全表扫描（ALL），现在 MySQL 预估要扫描 1431行记录来完成这个查询，这里的 "using where" 是指 MySQL 通过 where 条件来筛选存储引擎返回的记录。

一般 MySQL 使用三种方式应用 where 条件，从好到坏依次为：

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

上面的案例说明了使用索引的重要性，但也不是有了索引就能让扫描的行数等于返回的行数，有很多情况下，如果索引使用不当，就会导致索引失效，关于索引的部分请关注文章：

简单易懂的MySQL覆盖索引、前缀索引、索引下推

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化：

使用索引覆盖原则，这样减少了回表操作，提高SQL执行效率
改变库表结构，例如使用单独的汇总表
重写复杂的查询，让 MySQL 优化器能以更优化的方式执行它（后续讨论）

重构查询

有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好。但也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。下面将介绍如何重构查询。

1、一个复杂查询 or 多个简单查询

设计查询时需要考虑的问题是，是否需要将一个复杂查询分解成多个简单查询。MySQL从设计上让连接和断开都很轻量级，在返回一个小的查询时很高效，在一个千兆网卡也能轻松满足每秒超过2000次的查询，所以运行多个小查询在MySQL服务器性能上不是大问题。

MySQL内部每秒能扫描内存中上百万行数据，相比之下，MySQL 响应给客户端就慢的多。有的时候，将大查询分解为多个小查询是很有必要的；不过，如果一个相对简单的查询能胜任时还写成多个独立查询就是不可取的。

2、切分查询

有时候需要将大查询“分而治之”，每个查询功能一样，只完成一小部分，每次只返回一小部分查询结果。

比如，清除大量数据时，如果一个大的语句一次性完成的话，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小的查询。这种情况下，往往将大查询“分而治之”可以更小的影响 MySQL 性能，还可以减少 MySQL 复制的延迟。

3、分解关联查询

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

select * 
from tag 
join tag_post on tag_post.tag_id=tag.id
join post on tag_post.post=post.id 
where tag.tag='mysql';

可以分解为下面这些查询来代替：

select * from tag where tag='mysql';
select * from tag_post where tag_id = 1234;
select * from post where post.id in (123,456,567,9098,8904);

原本一条查询，这里却变成多条查询，返回的结果又是相同。事实上，用分解关联查询的方式重构查询有如下优势：

让缓存效率更高，例如，上面查询中的 tag 已经被缓存了，那么应用就可以跳过第一个查询，对于 MySQL 查询缓存来说，如果关联中的某个表发生了变化，就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。（MySQL 8 的架构删除了查询缓存）
查询分解后，执行单个查询可以减少锁的竞争
查询本身效率也可能会有所提升。这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多

在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多，比如：当应用能够方便地缓存单个查询的结果的时候、当可以将数据分布到不同的MySQL服务器上的时候、当能够使用IN()的方式代替关联查询的时候、当查询中使用同一个数据表的时候。

4、优化关联子查询

很多时候，MySQL的子查询实现的非常糟糕，最糟糕的一类查询是 where 条件中包含 in() 的子查询语句。下面看一个案例。

我们希望找到 Sakila 数据库中，演员 Penelope Guiness参演过的影片信息，我们用以下方式实现：

select *
from sakila.film
where film_id in (
    select film_id
    from sakila.film_actor
    where actor_id = 1
);

MySQL 会对 in() 列表中的选项有专门的优化策略，一般会认为 MySQL 会先执行子查询返回所包含 actor_id 为1的 film_id，一般来说，in() 列表查询速度很快，所以我们会认为上面的查询会这样执行。

-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

但 MySQL 不是这样做的，MySQL 会将相关的外层表压到子查询中，它认为这样可以更高效率的查找到数据行，也就是说，MySQL 会将查询改写成下面这样：

SELECT * FROM sakila.film WHERE EXISTS (
    SELECT * FROM sakila.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id
);

这时，子查询需要根据 film_id 来关联外部表 film，因为需要 film_id 字段，所以 MySQL 认为无法先执行这个子查询。通过 Explain 可以看到子查询是一个相关子查询DEPENDENT SUBQUERY ，只要见到执行计划中出现 DEPENDENT SUBQUERY 时就要小心了，往往这时的查询效率很糟糕。

EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+---------+--------------------------+
| id | select_type        | tablel     | type    |  possible_keys           |
-----+--------------------+------------+---------+--------------------------+
| 1  | PRIMARY            | film       |  ALL    |  NULL                    |
| 2  | DEPENDENT SUBQUERY | film_actor |  eq_ref | PRIMNARY,idx_fk_film_id  |
+----+--------------------+------------+--------+---------------------------+

为什么糟糕呢？我们看上面的SQL，由于子查询不能独立完成，需要依赖外层查询，所以整个查询可以分为两个步骤：

第一步，根据 select * from sakila.film 查询出所有记录
第二部，根据上一步的结果集中的每一条记录，都将与子查询SQL组成新的查询语句，select * from sakila.film where film_id in ( select film_id from sakila.film_actor where actor_id = 1 );

这样一来，子查询的执行效率受制于外层查询的记录数。如果是一个很小的表，这个查询糟糕的性能可能还不会引起注意，但是如果外层的表是一个非常大的表，那么这个查询的性能会非常糟糕。当然我们很容易用下面的办法来重写这个查询:

SELECT film.* 
FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id) WHERE actor_id = 1;

上述描述的 IN()子查询会被 MySQL 优化成 EXISTS(),指的是 MySQL5.5及以前的版本；而 MySQL5.6 开始会将 IN()子查询优化成 Join 形式。

5、优化UNION

有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL会将两个表都存放到同一个临时表中，然后再取出前20行记录:

(
    select first_name,last_name
    from sakila.actor
    order by last_name
)
union all
(
    select first_name,last_name
    from sakila.customer
    order by last_name
)
limit 20;

这条查询将会把actor中的200条记录和customer表中的599条记录存放在一个临时表中，然后再从临时表中取出前20条。可以通过在UNION 的两个子查询中分别加上一个LIMIT20来减少临时表中的数据:

(
    select first_name,last_name
    from sakila.actor
    order by last_name
    limit 20
)
union all
(
    select first_name,last_name
    from sakila.customer
    order by last_name
    limit 20
)
limit 20;

现在中间的临时表只会包含40条记录了，除了性能考虑之外，在这里还需要注意一点:从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要加上一个全局的ORDER BY和LIMIT操作。

6、索引合并优化

在 MySQL5.0 之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描，但是从 5.1 开始，引入了 Index merge 优先技术，当 where 子句中包含多个复杂条件的时候，MySQL 能访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

MySQL官方API之索引合并优化

7、max() 和 min() 查询优化

select min(user_id) from user;

对于 min() 和 max() 查询，如果 user_id 字段上没有索引，那么 MySQL 会进行一次全表扫描，如果 user_id 是主键，那么理论上，当 MySQL 读到第一个满足条件的记录的时候就是要找的最小值了，因为聚集索引是严格按照字段的大小顺序排列的，所以，对大量字段进行 min() 或者 max() 查询时，可以综合考虑适当添加索引提升查询效率。

8、count() 查询优化

count()可以统计某列值的数量，一般用来统计行数。count()中有好几种写法，可以在其中指定主键列，普通列，数字 1，通配符 *

count(主键id)：InnoDB 会遍历整张表，把每一行id值取出来，返回给server层，server拿到id后，就按行累加。
count(字段)：如果这个字段定义为 not null 时，一行行从记录里读出这个字段，判断不能为null，按行累加；如果这个字段定义允许为null，那么执行时，判断到有可能是null，还要把值取出再判断一下，不是null才累加
count(1)：InnoDB 引擎会遍历整张表，但不取值，server层对于返回的每一行，放一个数字1进去，判断是不可能为空的，按行累加。
count(*)：并不会把全部字段取出，专门做了优化，不取值，count(*)肯定不是null，按行累加

所以，针对实际情况分析：

如果列为主键，count(主键) 效率优于 count(1)
如果列不为主键，count(1)效率优于 count(字段)
如果表中存在主键，count(主键)效率最优
如果表中只有一列，count(*)效率最优
如果表有多列，且不存在主键，则 count(1) 效率优于 count(*)

9、优化 group by 和 distinct

很多时候，MySQL 都是用同样的方法优化这两种查询，MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。

当我们执行 group by 操作在没有合适的索引可用的时候，通常先扫描整个表提取数据并创建一个临时表，然后按照 group by 指定的列进行排序。在这个临时表里面，对于每一个 group 的数据行来说是连续在一起的。完成排序之后，就可以发现所有的 groups，并可以执行聚集函数（aggregate function）。可以看到，在没有使用索引的时候，需要创建临时表和排序。在执行计划中通常可以看到“Using temporary; Using filesort”。

要解决 group by 语句的优化问题，可以先思考一下，执行 group by 为什么需要临时表？group by 的语义是根据指定的列对结果集进行分组，但是由于进行分组的这列是无序的，所以就要有个临时表，来记录并统计结果。那么，如果扫描过程中可以保证出现的数据是有序的，那就不用临时表了。

如果可以确保输入的数据是有序的，那么计算 group by 时，就只需要从左到右，顺序扫描，依次累加，比如数据结构（0,0,1,1,2,2），这个过程如下：

当碰到第一个1时，已经知道累加了X个0，结果集中的第一行就是（0,X）;
当碰到第一个2时，已经知道累加了Y个1，结果集中的第二行就是（1,Y）;

也就是说，到扫描数据结束，就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序。InnoDB 的 B+Tree 索引原本就是有序的，如果通过读取索引完成 group by 操作，那么就可避免创建临时表和排序。

SELECT actor.first_name，actor.last_name,COUNT(*)
FROM sakila.film_actor
INNER J0IN sakila.actor USING(actor_id)
GROUP BY actor.first_name,actor.last_name;

SELECT actor.first_name,actor.last_name,COUNT(*)
FROM sakila.film_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;

以上两种写法，使用 film_actor.actor_id 列分组的效率会比 actor.first_name,actor.last_name 分组的效率高。使用 distinct 也建议对索引列去重。

10、order by 查询优化

因为索引的数据结构是 B+Tree，索引中的数据是按照一定顺序排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作，explain 分析查询时，Extra 显示为 Using index

所有不是索引直接返回排序结果的操作都是 Filesort 排序，也就是说进行了额外的排序操作，explain 分析查询时，Extra 显示为 Using filesort

MySQL 优化的核心原则：尽量减少额外的排序，通过索引直接返回有序数据。

11、limit 查询优化

在系统中需要进行分页操作的时候，我们通常会使用 LIMIT 加上偏移量的办法实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

在偏移量非常大的时候，例如可能是Limit 1000,20 这样的查询，这时MySQL需要查询 1020 条记录然后只返回最后20条，前面1000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。例如下面的查询案例:

SELECT film_id,description 
FROM sakila.film 
ORDER BY title LIMIT 50,5;

如果这个表非常大，可以改写成如下方式：

SELECT film.film_id,film.description
FROM sakila.film
INNER J0IN(
    SELECT film_id 
    FROM sakila.film
    ORDER BY title LIMIT 50,5
) AS lim USING(film_id);