MySQL慢查询诊断实战：从连接检查到索引优化的DBA排查指南

1. 从SQL1这个编号说起为什么DBA的日常绕不开慢查询很多人看到SQL1这种编号第一反应是这不就是个练习题序号吗。但在实际运维场景里SQL1往往代表着一个DBA团队内部约定的第一优先级排查清单——也就是当数据库出现性能抖动时最先要跑的那几条诊断SQL。这个编号背后其实藏着一套完整的运维方法论不是随便写几条查询语句就完事而是要根据业务特征、数据量级、索引结构来定制一套体检套餐。我接触过不少刚入行的运维同学他们遇到数据库变慢的第一反应是重启服务或者直接找开发改代码。这两种做法都有问题。重启只能暂时缓解根因还在直接找开发改代码你连问题出在哪条SQL上都说不清楚沟通效率极低。真正有效的做法是先用一套标准化的诊断SQL把问题定位到具体语句、具体执行阶段然后再决定是加索引、改写法还是调参数。这篇文章要聊的就是这套SQL1级别的诊断思路。我会从最基础的连接状态检查开始一路讲到执行计划分析、锁等待排查、慢日志挖掘每个环节都配上可以直接复制使用的SQL语句和参数说明。不管你是刚接手MySQL运维的新手还是已经管着几十个实例的老手这套东西都能直接拿去用。文章里涉及的所有语句和参数都是我在实际生产环境中反复验证过的不是从文档里抄来的理论。2. 连接层诊断先搞清楚谁在连、连了多少、卡在哪2.1 当前连接数与最大连接数的比值判断很多人一上来就查SHOW PROCESSLIST这个习惯没错但顺序可以优化。第一步应该先看连接数的整体水位判断是不是连接池被打满了。执行下面这条语句SHOW STATUS LIKE Threads_connected; SHOW STATUS LIKE Threads_running; SHOW VARIABLES LIKE max_connections;Threads_connected是当前建立的连接总数Threads_running是其中正在执行SQL的连接数。这两个值的区别很关键如果Threads_connected很高但Threads_running很低说明大量连接处于空闲状态问题可能出在连接池配置不合理而不是数据库本身扛不住。反过来如果Threads_running接近max_connections那就是真的有大量查询在并发执行需要进一步看这些查询在干什么。我一般会算一个比值Threads_running / max_connections。这个值超过0.6就要警惕了超过0.8基本可以确定是并发压力过大。这时候要么限流要么扩容要么优化SQL减少执行时间。2.2 用PROCESSLIST定位阻塞源头确认连接水位偏高之后下一步就是看具体是哪些连接在捣乱。SHOW FULL PROCESSLIST比SHOW PROCESSLIST多显示一个Info字段的完整内容能看到正在执行的完整SQL语句。但直接看输出会很乱我习惯用下面这条查询来过滤SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC;按time降序排列排在最前面的就是执行时间最长的语句。这里有个经验如果state字段显示Sending data说明查询正在读取和发送结果集通常是全表扫描或者索引效率低如果显示Locked那就是锁等待如果显示Waiting for table metadata lock说明有DDL操作没提交阻塞了后续查询。注意information_schema.processlist在MySQL 5.7和8.0中的字段名略有差异8.0新增了EXECUTION_ENGINE等字段但上面这条查询在两个版本中都能跑。2.3 连接来源分布与异常IP识别有时候问题不是连接数多而是某个应用端异常重连导致连接数暴涨。这时候需要按来源IP聚合看一下SELECT substring_index(host, :, 1) AS client_ip, COUNT(*) AS conn_count FROM information_schema.processlist GROUP BY client_ip ORDER BY conn_count DESC;如果某个IP的连接数明显高于其他而且这些连接的状态大多是Sleep那很可能是该应用端的连接池配置有问题比如maxActive设得太大或者没有正确释放连接。这种情况光在数据库端限流治标不治本得让应用端把连接池参数调合理。3. 执行计划解读EXPLAIN输出里哪些字段真正要命3.1 type字段的优先级排序EXPLAIN是DBA最常用的工具但很多人只看key字段有没有走索引忽略了type字段。实际上type字段决定了访问类型从好到坏依次是systemconsteq_refrefrangeindexALL。看到ALL基本就是全表扫描必须优化看到index是全索引扫描虽然比ALL好一点但如果表数据量大性能依然很差。我遇到过一种情况查询明明走了索引但type是indexrows字段显示扫描了几百万行。原因是他建的索引列顺序不对导致优化器只能做全索引扫描而不是范围扫描。后来把索引列顺序调整了一下type变成range扫描行数降到几千查询时间从3秒降到20毫秒。3.2 rows与filtered的乘积估算真实扫描量rows字段是优化器估算的需要扫描的行数filtered是过滤后剩余行的百分比。这两个值相乘可以粗略估算出实际参与后续join或排序的行数。比如rows10000filtered10那实际有效行数大约是1000。如果这个乘积很大说明要么索引选择性差要么条件写法导致索引失效。有个常见坑在索引列上使用函数或者隐式类型转换会导致索引失效。比如WHERE DATE(create_time) 2024-01-01即使create_time上有索引优化器也不会用因为对列做了函数运算。正确的写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02。3.3 Extra字段里的危险信号Extra字段信息量很大有几个值需要特别关注Using filesort说明需要额外排序如果数据量大且无法利用索引排序性能会很差。Using temporary使用了临时表常见于GROUP BY和DISTINCT操作临时表可能落在磁盘上。Using join bufferjoin时没有走索引使用了连接缓冲区通常意味着被驱动表没有合适的索引。这三个信号同时出现两个以上基本可以判定这条SQL需要重写或者加索引。我一般会优先消除Using temporary因为它对性能的影响最直接。4. 锁与事务那些让查询卡住不动的隐形杀手4.1 用performance_schema查当前锁等待MySQL 5.7之后performance_schema提供了更细粒度的锁信息。下面这条查询可以找出当前正在等待锁的事务SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM performance_schema.data_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id w.blocking_engine_transaction_id JOIN information_schema.innodb_trx r ON r.trx_id w.requesting_engine_transaction_id;这条语句能直接告诉你谁在等锁谁持有锁持有锁的那个事务正在执行什么SQL。定位到阻塞源头之后如果那个事务长时间不提交可以考虑KILL掉对应的线程ID。但KILL之前一定要确认那个事务是不是在跑批量导入或者大事务贸然杀掉可能导致回滚时间很长。4.2 长事务的识别与处理策略长事务是锁等待的常见原因。查长事务的语句SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx WHERE trx_started DATE_SUB(NOW(), INTERVAL 60 SECOND);这里把阈值设为60秒实际可以根据业务容忍度调整。找到长事务之后不要急着杀先看它的trx_query是什么。如果是INSERT ... SELECT这种大批量操作杀掉之后回滚可能比让它跑完还慢。这时候更好的做法是联系开发确认业务影响必要时在业务低峰期再处理。提示innodb_trx表里的trx_query字段可能显示为NULL这通常意味着事务已经执行完SQL但还没提交处于空闲事务状态。这种事务同样会持有锁需要重点关注。4.3 元数据锁的排查路径元数据锁MDL是另一个容易被忽略的阻塞源。当一个会话执行了SELECT但没提交事务另一个会话想对这个表做ALTER TABLE就会发生MDL等待。排查语句SELECT * FROM performance_schema.metadata_locks WHERE lock_status PENDING;如果看到PENDING状态的MDL说明有DDL被阻塞了。这时候需要找到持有MDL的会话通常是某个未提交的事务。处理方式和长事务类似先确认业务影响再决定是否终止。5. 慢查询日志从海量记录里捞出真正有价值的那几条5.1 慢日志关键参数配置慢查询日志是事后分析的主要依据但默认配置往往不够用。我建议至少调整这几个参数SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON; SET GLOBAL min_examined_row_limit 100;long_query_time设为1秒是通用起点如果业务对延迟敏感可以降到0.5秒。log_queries_not_using_indexes会记录所有没走索引的查询这个开关在排查阶段很有用但长期开启会导致日志量过大建议排查完就关掉。min_examined_row_limit设成100可以过滤掉那些扫描行数很少的伪慢查询减少噪音。5.2 用mysqldumpslow做初步聚合慢日志文件可能很大直接看效率太低。mysqldumpslow是MySQL自带的聚合工具常用参数组合mysqldumpslow -s t -t 10 /var/log/mysql/slow.log-s t表示按总执行时间排序-t 10表示只显示前10条。这样能快速找出最耗时的SQL模板。如果想按平均执行时间排序用-s at按执行次数排序用-s c。不过mysqldumpslow的聚合粒度比较粗它会把参数不同的同类SQL归并成一条。如果想看具体某条SQL的完整执行情况还是得去慢日志里搜原始记录。5.3 从慢日志到执行计划的闭环找到慢SQL之后不要直接凭感觉加索引。正确的做法是把慢日志里的SQL拿出来在前面加EXPLAIN跑一遍看执行计划是否符合预期。如果执行计划显示走了索引但依然慢可能是索引区分度不够或者回表次数太多。这时候可以考虑覆盖索引把查询需要的列都加到索引里避免回表。我处理过一个案例一条查询在orders表上走了idx_create_time索引但type是rangerows显示扫描了50万行。后来发现查询条件里还有一个status字段而status的区分度很低优化器估算走idx_create_time更划算。但实际上statuscompleted的数据只占5%如果建一个(status, create_time)的联合索引扫描行数能降到2万左右。调整之后查询时间从1.2秒降到80毫秒。6. 索引设计的取舍不是越多越好也不是越少越好6.1 联合索引的列顺序原则联合索引的列顺序直接决定了它能被哪些查询用到。基本原则是等值条件列在前范围条件列在后。比如查询是WHERE a 1 AND b 2 AND c 3那索引应该建(a, c, b)因为a和c是等值条件b是范围条件。如果建成了(a, b, c)那c的条件就无法利用索引过滤只能作为覆盖索引的列存在。还有一个容易被忽略的点如果查询里只有b 2这个条件那(a, c, b)这个索引是用不上的因为最左前缀原则要求必须从索引的第一列开始匹配。所以建联合索引之前一定要把业务上所有可能的查询模式都列出来找一个覆盖度最高的列顺序。6.2 索引选择性计算方法索引选择性是指不重复的索引值数量与总行数的比值。计算方法SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;选择性越接近1索引效果越好。一般来说选择性低于0.1的列不太适合单独建索引因为扫描行数占比太高优化器可能直接选择全表扫描。但这种列可以放在联合索引的后面作为覆盖索引的一部分。6.3 冗余索引与未使用索引的清理索引不是越多越好每个索引都会增加写入开销和存储空间。查冗余索引可以用sys.schema_redundant_indexes视图MySQL 5.7SELECT * FROM sys.schema_redundant_indexes;查未使用索引用sys.schema_unused_indexesSELECT * FROM sys.schema_unused_indexes;不过要注意schema_unused_indexes只统计了自上次重启以来的使用情况如果实例刚重启不久这个数据参考价值有限。我一般会结合业务周期来看至少观察一个完整的业务周期比如一周再决定是否删除。7. 参数调优的边界哪些可以动哪些最好别碰7.1 InnoDB缓冲池大小的设置逻辑innodb_buffer_pool_size是最重要的参数之一它决定了InnoDB能缓存多少数据和索引。通用建议是设为物理内存的50%到70%。但这不是绝对的如果服务器上还跑了其他吃内存的服务就要相应降低。我见过有人把这个值设成物理内存的90%结果导致系统频繁swap性能反而下降。判断缓冲池是否够用的方法是看命中率SHOW STATUS LIKE Innodb_buffer_pool_read_requests; SHOW STATUS LIKE Innodb_buffer_pool_reads;命中率 (1 - reads / read_requests) * 100%。如果低于99%说明缓冲池偏小可以考虑适当调大。但调整这个参数需要重启实例MySQL 5.7之前或者在线调整MySQL 5.7支持在线调整但会有短暂性能抖动。7.2 连接相关参数的联动关系max_connections、thread_cache_size、back_log这三个参数是联动的。max_connections设得太大会导致每个连接占用的内存累积起来很可观。thread_cache_size设得太小频繁创建销毁线程会增加CPU开销。back_log决定了TCP连接队列的长度如果应用端连接风暴比较频繁可以适当调大。我的经验值是max_connections设为业务峰值连接数的1.5倍thread_cache_size设为max_connections的10%左右back_log不低于max_connections。当然这些都要根据实际监控数据来调整不能照搬。7.3 日志与刷盘策略的性能权衡innodb_flush_log_at_trx_commit和sync_binlog这两个参数直接关系到数据安全性和性能。innodb_flush_log_at_trx_commit1表示每次事务提交都刷盘最安全但性能最差设为2表示写入OS缓存每秒刷一次盘性能好一些但可能丢1秒数据设为0表示交给后台线程刷盘性能最好但安全性最差。sync_binlog同理设为1最安全设为0由系统决定刷盘时机。对于金融类业务这两个参数必须设为1对于日志类或者可容忍少量丢失的业务可以适当放宽。但我要提醒一句不要为了压测好看就把这两个参数改成0生产环境出问题的时候后悔都来不及。8. 日常巡检清单把上面这些串成一套可执行的流程8.1 每日必查的五个指标我把日常巡检浓缩成五个核心指标每天早上花五分钟过一遍Threads_running是否超过max_connections的60%慢查询数量是否比前一天有明显增长Innodb_buffer_pool_reads与read_requests的比值是否低于99%是否有执行时间超过60秒的事务磁盘空间使用率是否超过80%这五个指标覆盖了连接、查询、内存、事务、存储五个维度基本能发现80%的潜在问题。8.2 每周一次的深度检查每周做一次更深入的检查包括用sys.schema_unused_indexes检查未使用索引用mysqldumpslow聚合慢日志看是否有新的慢SQL模板出现检查performance_schema.metadata_locks是否有长期PENDING的记录对比本周和上周的Com_select、Com_insert等计数器看业务量变化趋势这些检查不需要每天做但每周一次能帮你发现那些缓慢累积的问题比如索引逐渐失效、业务量悄悄增长导致的容量瓶颈。8.3 出问题时的排查顺序当收到告警说数据库响应变慢时我建议按这个顺序排查先看Threads_running确认是并发压力还是单条慢查询如果是并发压力看PROCESSLIST里哪些SQL在跑按time排序如果是单条慢查询直接EXPLAIN看执行计划如果执行计划正常但依然慢查锁等待和长事务如果都没有问题查服务器层面的CPU、内存、磁盘IO这个顺序是从数据库内部到外部从整体到局部能最快定位到根因。我见过有人一上来就查服务器监控结果绕了一大圈才发现是某条SQL没走索引。8.4 几个容易踩的坑最后分享几个我在实际运维中踩过的坑第一个坑SHOW PROCESSLIST看到的Time字段是连接空闲时间不是SQL执行时间。如果连接处于Sleep状态Time表示空闲了多久如果正在执行SQLTime才是执行时间。很多人会误判。第二个坑EXPLAIN的rows字段是估算值不是精确值。有时候估算值和实际扫描行数差几个数量级这时候需要用EXPLAIN ANALYZEMySQL 8.0.18来获取实际执行统计。第三个坑KILL掉一个长事务之后回滚过程可能持续很长时间期间数据库性能可能更差。所以KILL之前一定要评估回滚代价最好在业务低峰期操作。第四个坑慢查询日志里的Query_time包含了锁等待时间。如果一条SQL的Query_time很长但Lock_time也长那问题可能不在SQL本身而在锁竞争。这时候优化SQL写法没用得去解决锁冲突。这套东西看起来内容不少但真正用起来之后大部分日常问题都能在十分钟内定位到根因。关键是要养成按流程排查的习惯不要凭感觉瞎猜。我刚开始做运维的时候也喜欢凭经验直接下判断后来发现十次里有三次是错的反而浪费了更多时间。按流程走虽然看起来慢但准确率高长期来看效率更高。