【MySQL | 第五篇】 MySQL 性能分析：如何查询慢 SQL

一、前言在面试和项目开发中慢 SQL 是比较常见的问题。如果一条 SQL 执行时间过长轻则导致接口响应变慢重则导致请求超时。要是大量慢 SQL 同时出现还可能把数据库连接池占满最终影响整个系统的正常访问。今天我们就来回答并深入学习这个问题怎么把慢 SQL 找出来。二、查看 SQL 执行频次SQL 执行频次指的是某类 SQL 语句在数据库中被执行的次数。比如在一个系统中select执行得特别多而insert、update相对较少那么我们在做性能优化时就可以优先关注查询语句。因为这类 SQL 执行频率高优化之后的收益也更明显。在 MySQL 中可以通过下面的命令查看常见 SQL 的执行频次showglobalstatuslikeCom_______;这里的global表示查看全局统计信息(要注意global重启后失效)如果只想查看当前会话的统计信息也可以写成showsessionstatuslikeCom_______;Com_______中有 7 个下划线。这里的下划线是like语句中的通配符用来匹配Com_select、Com_insert、Com_update这类状态变量。常见字段如下– 事务相关com_begin 执行 begin 操作的次数 com_commit 执行 commit 操作的次数 com_rollback 执行 rollback 操作的次数– dml 操作最常用 com_select 执行 select 操作的次数 com_insert 执行 insert 操作的次数 com_update 执行 update 操作的次数 com_delete 执行delete 操作的次数– ddl 操作 com_create_table 执行 create table 的次数 com_drop_table 执行 drop table 的次数 com_alter_table 执行 alter table 的次数– 表维护 com_repair 执行 repair table 的次数 com_optimize 执行 optimize table 的次数– 权限操作com_grant 执行 grant 的次数com_revoke 执行 revoke 的次数– show 相关com_show_databases 执行 show databases 的次数com_show_tables 执行 show tables 的次数com_show_status 执行 show status 的次数com_show_variables 执行 show variables 的次数com_show_processlist 执行 show processlist 的次数– binlogcom_binlog 执行 binlog 操作的次数– 其他com_change_db 执行 use database 的次数com_kill 执行 kill 的次数例如我们一次查看所有常用的包括提交删除插入事务,查询,更新的频率通过这一步我们可以先大概判断系统的 SQL 访问特点到底是查询多还是写入多后续优化时也会更有方向。三、开启慢查询日志如果说执行频次是帮我们判断“哪类 SQL 执行得多”那么慢查询日志就是帮我们找到“哪些 SQL 执行得慢”。MySQL 的慢查询日志会记录执行时间超过指定阈值的 SQL 语句。这个阈值由long_query_time参数控制单位是秒默认值通常是 10 秒。1. 查看慢查询日志是否开启先查看慢查询日志的开关状态ON为开启OFF为关闭showvariableslikeslow_query_log;2. 临时开启慢查询日志可以通过下面的命令临时开启慢查询日志setglobalslow_query_logon;也可以写成setglobalslow_query_log1;需要注意的是这种方式是运行时修改MySQL 重启之后可能会失效。如果希望长期生效需要写到 MySQL 配置文件中。3. 设置慢查询时间阈值查看当前慢查询时间阈值showvariableslikelong_query_time;例如我们希望 SQL 执行时间超过 2 秒就被记录下来可以设置setgloballong_query_time2;如果只想在当前会话中测试也可以使用setsessionlong_query_time2;这里要注意一点slow_query_log是控制慢查询日志是否开启而long_query_time是控制超过多少秒才算慢查询。两个参数的作用不一样。4. 查看慢查询日志文件位置这个时候可能会有一个问题慢查询日志开启之后生成的日志文件在哪里更直接的方式是查看slow_query_log_fileshowvariableslikeslow_query_log_file;如果想查看 MySQL 的数据目录也可以使用selectdatadir;查询结果就是 MySQL 的数据目录地址。慢查询日志文件一般会在这个目录下文件名中通常会带有slow.log。5. 测试慢查询日志是否生效为了测试慢查询日志是否真的生效可以执行一条耗时 SQLselectsleep(3);如果我们把long_query_time设置成了 2 秒那么这条 SQL 执行 3 秒就会被记录到慢查询日志中。然后找到后缀为slow.log的文件打开之后就可以看到慢查询记录。慢查询日志中比较重要的信息一般包括字段含义Query_timeSQL 实际执行耗时Lock_time等待锁的时间Rows_sent返回给客户端的行数Rows_examined扫描过的行数SQL 语句具体被记录下来的慢 SQL其中我们最需要关注的是Query_time和Rows_examined。如果Query_time很长说明 SQL 执行时间确实比较久。如果Rows_examined很大说明 MySQL 为了得到结果扫描了大量数据这种情况通常就需要继续用explain分析执行计划。四、使用 show profile 查看 SQL 耗时慢查询日志可以帮助我们找到慢 SQL但如果我们想继续看一条 SQL 在执行过程中具体耗时在哪里可以使用show profile。先查看 profiling 是否开启selectprofiling;如果结果为0说明默认是关闭的。可以通过下面的命令开启setprofiling1;然后执行需要分析的 SQL再查看 SQL 执行记录showprofiles;如果想查看某一条 SQL 的详细耗时阶段可以继续执行showprofileforquery1;这里的1是show profiles结果中的Query_ID。show profile更适合学习阶段观察 SQL 执行过程。在实际项目或生产环境中更推荐结合慢查询日志、explain、监控工具以及 Performance Schema 一起分析。五、使用 explain 查看执行计划找到慢 SQL 之后下一步就要分析它为什么慢。这时最常用的工具就是explain。它可以告诉我们 MySQL 准备怎么执行这条 SQL比如有没有走索引、预计扫描多少行、访问类型是什么等。基本语法如下explainselect字段列表from表名where条件;例如explainselect*fromempwhereusernameTom;1. explain 的输出格式在 MySQL 8.0 中explain支持多种输出格式比如传统表格格式、树形格式和 JSON 格式。如果你的环境中看到的是树形结果类似下面这样explainformattreeselect字段列表from表名where条件;树形格式更适合看执行步骤之间的层级关系。如果想看到我们平时更常见的表格字段可以使用传统格式explainformattraditionalselect字段列表from表名where条件;也可以使用 JSON 格式查看更完整的信息explainformatjsonselect字段列表from表名where条件;对于初学阶段来说先掌握formattraditional的表格字段就够用了。2. explain 常见字段说明explain formattraditional 的结果中常见字段如下字段含义重点怎么看id查询中select的编号id相同通常从上往下看id不同一般值越大越先执行select_type查询类型常见有SIMPLE、PRIMARY、SUBQUERY、UNION等table当前访问的表表示这一行执行计划分析的是哪张表partitions匹配到的分区没有使用分区表时通常为NULLtype访问类型非常重要用来判断访问表的方式好不好possible_keys可能使用的索引优化器认为这条 SQL 可能用到哪些索引key实际使用的索引如果为NULL说明没有使用索引key_len使用索引的长度表示 MySQL 实际使用了索引中的多少字节ref索引比较对象表示索引列和哪个列或常量进行比较rows预计扫描行数估算值越大通常说明扫描数据越多filtered条件过滤百分比要结合rows一起看不能单独判断好坏Extra额外信息会显示Using where、Using index、Using filesort等补充信息3. type 字段怎么看在explain中type是非常关键的字段它表示 MySQL 访问表的方式。常见访问类型可以简单按下面顺序理解system const eq_ref ref range index ALL一般来说越靠左性能越好越靠右性能越差。type含义说明system系统表或只有一行数据的表这种情况比较少见const通过主键或唯一索引精确匹配一行性能很好eq_ref多表连接时通过主键或唯一索引匹配唯一一行常见于关联查询ref使用普通索引进行查询可能匹配多行range使用索引进行范围查询例如、、BETWEEN、INindex扫描整个索引树比全表扫描好一些但仍然可能扫描很多数据ALL全表扫描如果数据量大就需要重点关注这里还可能看到NULL。NULL一般表示查询不需要访问表比如直接执行explainselect1;这种情况比较特殊不需要放到常规的性能优劣顺序里理解。4. Extra 字段怎么看Extra字段是执行计划中的补充信息也很值得关注。Extra 信息含义是否需要关注Using whereMySQL 在存储引擎取出数据后还需要根据where条件过滤正常情况结合rows一起看Using index使用了覆盖索引不需要回表查询通常是比较好的情况Using temporary使用了临时表需要关注常见于group by、order byUsing filesortMySQL 需要额外排序需要关注可能和索引设计有关其中Using temporary和Using filesort在数据量比较大时要重点分析因为它们可能会带来额外的内存或磁盘开销。5. 分析 explain 时的思路看explain时不需要一上来就把所有字段背下来可以先抓住几个重点看key是否为NULL如果key是NULL说明这条 SQL 没有使用索引需要继续分析查询条件、索引设计以及字段类型是否匹配。看type是否为ALL如果type是ALL说明 MySQL 做了全表扫描。小表问题不大但如果是大表就很可能是慢 SQL 的原因。看rows是否过大rows表示 MySQL 预计要扫描多少行。它是估算值不一定完全准确但可以用来判断扫描范围是否过大。看Extra中是否出现Using temporary或Using filesort如果出现这两个信息说明 SQL 可能发生了临时表或额外排序需要重点检查order by、group by以及相关索引。看possible_keys和key是否一致possible_keys表示可能使用的索引key表示最终实际使用的索引。如果possible_keys有值但key是NULL说明优化器最终没有选择索引这时就要继续分析条件写法、数据量、索引区分度等问题。六、总结这一篇主要学习了如何查询慢 SQL而不是直接进入 SQL 优化。整体流程可以总结为先用show status查看 SQL 执行频次判断系统中哪类 SQL 最常见再开启慢查询日志通过 slow_query_log 和 long_query_time找到真正执行慢的 SQL如果想看单条 SQL 的耗时阶段可以使用show profile最后使用explain查看执行计划重点关注type、key、rows和Extra今天我们把慢SQL查询学习完毕,下篇我们将会讲到索引与SQL优化,这基本是相关联的也是面试的重点内容