MySQL【sql性能分析+sql调优】

本文整理自尚硅谷MySQL数据库教程天花板

sql性能分析

使用last_query_cost

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

show status like 'last_query_cost'查询出最近一条sql查询了多少数据页

show status like ‘last_query_cost’;
±----------------±----------+
| Variable_name | Value |
±----------------±----------+
| Last_query_cost | 45.221160 |
±----------------±----------+
1 row in set (0.00 sec)

通过开启mysql的慢查询日志，让mysql记录超时的sql

[sql慢查询日志][https://juejin.cn/post/6970550749528866852]

set global slow_query_log=‘ON’; 开启mysql的慢查询
|
show variables like ‘%long_query_time%’; 查询慢sql执行秒数阈值
|
set global long_query_time = 1; 设置秒数阈值
|
show variables like ‘%slow%’; 查询与慢查询有关的配置
|
SHOW GLOBAL STATUS LIKE ‘%Slow_queries%’; 查看当前系统中有多少条慢查询sql

从上图可以看到slow_query_log_file，是存放慢sql的日志，使用下一个mysqldumpslow工具时就是对该日志进行分析。

慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow 。
|
mysqldumpslow 命令的具体参数如下：

-a: 不将数字抽象成N，字符串抽象成S

-s: 是表示按照何种方式排序：

• c: 访问次数

• l: 锁定时间

• r: 返回记录

• t: 查询时间

• al:平均锁定时间

• ar:平均返回记录数

• at:平均查询时间 （默认方式）

• ac:平均查询次数

-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；
|
例如：（在未登录mysql时使用该语句）

#得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

查看 SQL 执行成本：SHOW PROFILE

set profiling = ‘ON’; 开启profiling工具

show profiles 可以查看历史sql语句

show profile ALL for query 2 查询第二条sql语句所有开销信息

show profile的常用查询参数： ① ALL：显示所有的开销信息。 ② BLOCK IO：显示块IO开销。 ③ CONTEXT SWITCHES：上下文切换开 销。 ④ CPU：显示CPU开销信息。 ⑤ IPC：显示发送和接收开销信息。 ⑥ MEMORY：显示内存开销信 息。 ⑦ PAGE FAULTS：显示页面错误开销信息。 ⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。 ⑨ SWAPS：显示交换次数开销信息。

通过explain分析工具查看sql的执行计划

explain 各列作用

1. table 表示该条sql中查询了哪些表，MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表该表的表名（有时不是真实的表名字，可能是简称）。

2. id：查询的id值，例如子查询中可能会查两张表，就会存在两个id

   id如果相同，可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

   关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

3. type：其各个级别： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL

   1. system 如果存储引擎是MyISAM或Memory，表中只有一条记录，查询表中的count(*)，能够达到该级别；
   2. const 表示通过索引一次就找到，根据主键或唯一二级索引与常数进行等值匹配时，能够达到该级别；
   3. eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。连接查询中select * from s1 inner join s2 on s1.id = s2.ids2表的查询能达到该级别；
   4. ref 非唯一性索引扫描；通过普通二级索引进行等值查询能达到该级别；
   5. ref_or_null 当普通二级索引列值可为null时，在ref级别基础上加上is null查询达到该级别；
   6. index_merge 根据多个索引列进行or查询能够达到；
   7. unique subquery 是针对在一些包含in子查询的查询语句中，如果查询优化器决定将in子查询转换为exist子查询，而且子查询可以使用到主键进行等值匹配的话，可以达到该级别；
   8. range 只检索给定范围的行，使用一级索引或二级索引进行 < > between in查询能达到该级别；
   9. index 全表扫描，index与all区别为index只遍历索引树，通常比all快，因为索引文件比数据文件小很多。

4. possible_keys 可能会用到的索引

5. key 实际用到的索引

6. key_len 实际用到的索引长度，单位(字节数)，检查是否充分使用索引，值越大越好；例如varchar(11)字段，长度为11*3+1+2=36，其中的1表示列值为null，+2表示可变长字符；

7. ref 当使用索引列等值查询时，ref为常数const或某个列；

8. rows 预估需要读取的记录条数，值越小越好；

9. filtered 经过搜索条件过滤后，剩余记录的百分比（filtered与rows一起看，经过搜索条件筛选后的结果不一定是最终结果）

   如果单表查询中使用索引，那么计算时要估计出满足除使用到索引的搜索条件外的其他搜索条件的记录有多少条；例如：

   explain select name from demo_user_dyx where name like '广%' and age = 10;

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	demo_user_dyx	NULL	range	idx_name	idx_name	51	NULL	1630	10	Using index condition; Using where

   对于单表查询，这个filtered无太大意义，我们更关注连接查询时驱动表对应的执行计划记录的filtered值，它决定被驱动表要执行多少次(rows*filtered)，例如：

   EXPLAIN SELECT * FROM demo_user_dyx t1 INNER JOIN demo_user_dyx t2 ON t1.id = t2.id WHERE t1.name like '广%';

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	t1	NULL	range	PRIMARY,idx_name	idx_name	51	NULL	1630	100	Using index condition
   1	SIMPLE	t2	NULL	eq_ref	PRIMARY	PRIMARY	8	test.t1.id	1	100	NULL

10. Extra 常见的一些属性：

    Using where：SQL使用了where条件过滤数据

    Using index：使用了索引覆盖

    Using index condition：使用了索引下推

    Using filesort：得到所需结果集，需要对所有记录进行文件排序，没有利用到索引的顺序性

    Using temporary：需要建立临时表(temporary table)来暂存中间结果；该级别并不是一个好的征兆，维护临时表需要较大成本

sql调优

有哪些维度可以进行调优？

1. 索引失效、未充分利用索引 - 建立、修改索引
2. 关联查询包含太多join - SQL优化
3. 服务器调优及各个参数设置（缓存、线程数等）- 调整my.cnf
4. 数据过多 - 分库分表

sql查询优化的方式有很多，但可分为 物理查询优化 和 逻辑查询优化

• 物理查询优化通过索引和表连接方式进行优化
• 逻辑查询优化通过换一种查询写法（新写一个逻辑相同但效率更高的sql）来提升查询效率

索引失效的11种情况

1. 不遵守左前缀法则

2. 主键插入顺序并不遵循递增

3. 计算、函数、类型转换(自动或手动)导致索引失效

4. 类型转换导致索引失效

5. 范围条件右边的列索引失效

   例如存在联合索引 index(mobile_age_name)explain select name from demo_user_dyx where mobile like '155%' and age > 10 and name like '郝%';实际中并不会用到联合索引种的name字段，在sql中改变条件的顺序无法解决，只能修改联合索引的列顺序来解决，将能够等值查询的列放在前面；（注：<= >= between in不会导致索引失效）（如果where 条件这样写：where age = 10 and mobile = 'XXX'，这种情况可以走索引，因为mysql会优化顺序，找age后面有没有mobile）

6. 不等于(!= 或者<>)可能导致索引失效（不一定失效，如果当表数据量小，还是能够使用上索引）

7. 联合索引中，使用范围查询可以导致下一个索引失效

8. is null可以使用索引，is not null无法使用索引

9. like以通配符%开头索引失效

10. or 前后存在非索引的列，索引失效

11. 数据库和表的字符集统一使用utf8mb4，不同字符集进行比较前需要进行转换，会造成索引失效。

连接查询时mysql为我们做的优化

1. 在inner join查询时（select * from t1 inner join t2 on t1.col = t2.col 如果两个表的col列都没有索引，t1为驱动表，t2为被驱动表），查询优化器能够决定某个表为被驱动表；如果t2表的col没有索引，t1表有索引，优化器会选择t1作为被驱动表（理由：例两表都是20条数据，两表都无索引，则搜索次数是20*20；如果被驱动表加上了索引，就不需全表查询了，则查询次数是20*1）；

2. 针对内连接，两个表的连接条件列都存在索引，查询优化器会选择小表作为驱动表；

3. 不仅仅是内连接查询优化器会帮我们优化，在外连接时优化器也会帮我们优化；

4. 针对左、右连接查询，查询优化器会根据表大小（根据sql的where条件筛选，筛选后结果集小的为小表）与连接列是否存在索引，选择驱动表与被驱动表；

   例如explain select * from b left join a on a.f1 = b.f1 where a.f1 = 12;a表存在索引idx_f1，查询优化器会选择a表为驱动表，b表为被驱动表；

JOIN语句原理

1. simple nested-loop join 简单嵌套循环连接，例如两个表都没有索引，进行连接查询select * from t1 inner join t2 on t1.col = t2.col ，效率是很低的；

2. index nested-loop join 索引嵌套循环连接，select * from t1 inner join t2 on t1.col = t2.col在此查询基础上，对t2表的col加上索引，读取t2表的记录数就等于t1表的记录数

3. block nested-loop join 块嵌套循环连接，有时没有用到被驱动表的索引导致全表扫描，该方式引入了join buffer缓冲区，将驱动表中相关数据列放入该缓存中，再全表扫描被驱动表，被驱动表中的每一条记录与缓存中的所有记录进行匹配（内存中操作），将SNLJ中的join比较次数降为了1次，降低了被驱动表的IO访问次数；
   
   
4. 整体效率：INLJ > BNLJ > SNLJ

JOIN调优

1. 用小结果集驱动大结果集（本质减少外层循环的数据数量）在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各 个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
2. 为被驱动表的条件新增索引
3. 增大join buffer的大小
4. 减少驱动表不必要的字段查询

子查询优化

子查询的执行效率不高。

1. 执行子查询时，MySQL需要为内层查询语句的查询结果建立临时表 ，然后外层查询语句从临时表中查询记录。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引 ，所以查询性能会受到一定的影响。
3. 使用连接查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询要快 ;
4. 尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

order by 排序优化

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句避免使用 FileSort 排序 。
2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。(也要遵守联合索引的最左匹配原则)
3. 无法使用 Index 时，需要对 FileSort 方式进行调优。
4. 有时使用where条件过滤时，也可能导致order by的索引失效，但不代表不使用索引一定就效率低。

举例：

INDEX a_b_c(a,b,c)

order by 能使用索引最左前缀

• ORDER BY a

• ORDER BY a,b

• ORDER BY a,b,c

• ORDER BY a DESC,b DESC,c DESC

如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引

• WHERE a = const ORDER BY b,c

• WHERE a = const AND b = const ORDER BY c

• WHERE a = const ORDER BY b,c

• WHERE a = const AND b > const ORDER BY b,c

不能使用索引进行排序

• ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
• WHERE g = const ORDER BY b,c /丢失a索引/
• WHERE a = const ORDER BY c /丢失b索引/
• WHERE a = const ORDER BY a,d /d不是索引的一部分/
• WHERE a in (…) ORDER BY b,c /对于排序来说，多个相等条件也是范围查询/

GROUP BY优化

1. group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
2. group by 先排序再分组，遵照索引建的最佳左前缀法则
3. 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
4. where效率高于having，能写在where限定的条件就不要写在having中了
5. 减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
6. 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

优化分页查询

• 优化思路一，在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

  EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;

• 优化思路二，该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询

  EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

覆盖索引

不再赘述

索引条件下推 ICP (索引下推)(index condition pushdown)

ICP是MySQL5.6中的新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

使用联合索引举例，例如表demo_user_dyx存在联合索引idx_mobile_age_name，查询explain select * from demo_user_dyx where mobile like '155%' and age > 10 and name like '%郝%';这条语句explain结果为：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	demo_user_dyx	NULL	range	idx_mobile_age_name	idx_mobile_age_name	38	NULL	43812	3.7	Using index condition

Extra中的 Using index condition 就表示使用了ICP，我们看到查询语句中name字段使用了全模糊匹配，全模糊看上去不会使用到联合索引中的name列，但由于ICP，实际上是使用到了；使用了ICP后，联合索引中的mobile age name三列都被利用；如果不使用ICP，则联合索引中的name列不会被利用，造成先回表查询，再来根据name字段过滤；

好处：根据上面例子，ICP可以减少回表查询的次数；ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例；

ICP的使用条件：

1. 只能用于二级索引(secondary index)
2. explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
3. 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录 到server端做where过滤。
4. ICP可以用于MyISAM和InnnoDB存储引擎
5. MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
6. 当SQL使用覆盖索引时，不支持ICP优化方法。

其他的查询优化策略

普通索引 vs 唯一索引

从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？选择普通索引，依据是出于更新索引的时候普通索引效率比唯一索引效率更高（前提是InnoDB存储引擎）；

在查询的操作中两种索引几乎没有效率差别；但在更新索引过程中，普通索引能够利用change buffer，而唯一索引不能使用；

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话， 在不影响数据一致性的前提下， InooDB会将这些更新操作缓存在change buffer中 ，这样就不需要从磁 盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了 访问这个数据页 会触 发merge外，系统有 后台线程会定期 merge。在 数据库正常关闭（shutdown） 的过程中，也会执行merge 操作。

如果能够将更新操作先记录在change buffer， 减少读磁盘 ，语句的执行速度会得到明显的提升。而且， 数据读入内存是需要占用 buffer pool 的，所以这种方式还能够 避免占用内存 ，提高内存利用率。 唯一索引的更新就不能使用change buffer ，实际上也只有普通索引可以使用。

EXISTS 和 IN 的区分

两个sql例子，什么情况下使用IN或EXISTS，取决于表的大小，遵循小表驱动大表的原则；

select * from A where cc in (select cc from B) 如果B表小，选择使用IN

select * from A exists (select cc from B where B.cc = A.cc) 如果A表小，使用EXISTS

COUNT(*)与COUNT(具体字段)效率

在MySQL中统计数据表的行数，可以使用三种方式：SELECT COUNT(*)、SELECT COUNT(1)和select count(具体字段)，使用这三者之间的查询效率是怎样的?

前提：要统计的是某个字段的非空数据行数；

1. COUNT(*)和COUNT(1)都是对所有结果进行COUNTCOUNT(*)和COUNT(1)本质上并没有区别(二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的)。

2. 如果是MyISAM存储引擎，统计数据表的行数只需要0(1)的复杂度，这是因为每张MylSAM的数据表都有一个meta信息存储了row_count值，而一致性则由表级锁来保证。

   如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，维护一个row_count变量，因此需要采用扫描全表，是0(n)的复杂度，进行循环＋计数的方式来完成统计

3. 在InnoDB引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，消耗的时间明显会大于二级索引。对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。如果有多个二级索引，会使用key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

避免使用select *

• select *不会使用索引覆盖
• mysql解析过程中，会将*转换为所有列名，也会消耗资源