MySQL- SQL执行计划 & 统计SQL执行每阶段的耗时

官方文档

https://dev.mysql.com/doc/

在这里插入图片描述

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

在这里插入图片描述

某些SQL查询为什么慢

要弄清楚这个问题，需要知道MySQL处理SQL请求的过程， 我们来看下

MySQL处理SQL请求的过程

1. 客户端将SQL请求发送给服务器
2. 服务器检查是否在缓存中是否命中该SQL，未命中的话进入下一步
3. 服务器进行SQL解析、预处理，再由优化器生成对应的执行计划
4. 根据执行计划来，调用存储引擎API来查询数据
5. 将结果返回给客户端

查询缓存对SQL性能的影响

• query_cache_type：设置查询缓存是否可用 ， 可选值 ON OFF DEMAND , DEMAND表示只有在查询语句中使用了SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存
• query_cache_size： 设置查询缓存的内存大小 1024的整数倍
• query_cache_limit： 设置查询缓存可用存储的最大大小
• query_cache_wlock_invalidate：设置数据表被锁后是否返回缓存，默认关闭
• query_cache_min_res_unit：设置查询缓存分配的内存块最小单位

对于一个读写很频发的的系统，使用查询缓存很可能会降低查询处理的效率，建议不是用查询缓存，可以将query_cache_type 设置为OFF,query_cache_size 设置为0

SQL预处理及生成执行计划

接着上一步说，查询缓存未启用，或者 未命中查询缓存 ， 服务器进行SQL解析、预处理，再由优化器生成对应的执行计划 。 MySQL会依赖这个执行计划和存储引擎进行交互 .

包括以下过程

• 语法解析： 包含语法等解析校验
• 预处理 : 检查语法是否合法等
• 执行计划： 上面都通过了，会生成执行计划。

造成MySQL生成错误的执行计划的原因

• 存储引擎提供的统计信息不准确
• 执行计划中的估算不等同于实际的执行计划的成本
• MySQL不考虑并发的查询
• MySQL有时候会基于一些特定的规则来生成执行计划
• …

如何确定查询各个阶段所耗费的时间

使用profile

1. set profiling = 1 ; 启用profile , session级别的配置
2. 执行查询
3. show profiles ; 查看每一个查询所消耗的总时间信息
4. show profiles for query N ： 查询每个阶段所消耗的时间 （N为 Query_ID）

当然了还有 查询CPU等信息 的命令

比如 show profile cpu for query 1

演示

查询CPU的使用情况

看到有一个 1 warning ，看看是啥

‘SHOW PROFILE’ is deprecated and will be removed in a future release. Please use Performance Schema instead ， 很明白了，看看官方推荐的 Performance Schema 吧

Performance Schema

5.5引入的 . performance_schema在5.7.x及其以上版本中默认启用(5.6.x及其以下版本默认关闭)，如果要显式启用或关闭时，我们需要使用参数performance_schema=ON|OFF设置

performance_schema可以记录数据库所有线程执行过的SQL， 而上面的profile是session级别的，仅能记录当前session 的。

开启Performance Schema 记录功能

查看耗时的SQL