下面通过一个完整的实战案例从发现问题、配置日志、分析原因、索引优化到效果验证完整展示慢查询的处理流程。 1. 场景与准备电商系统订单表orders数据量约500万行用户频繁查询自己的订单并按创建时间倒序展示。表结构CREATETABLEorders(idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,user_idBIGINTUNSIGNEDNOTNULL,statusENUM(pending,paid,shipped,completed,cancelled)NOTNULL,create_timeDATETIMENOTNULL,amountDECIMAL(10,2)NOTNULL,product_nameVARCHAR(200)NOTNULL)ENGINEInnoDBDEFAULTCHARSETutf8mb4;初始索引仅有主键-- 无任何二级索引数据分布用户数10万每个用户平均 50 笔订单高频查询某用户按时间倒序取最近 20 笔⚙️ 2. 慢查询日志配置为捕获执行超过0.3秒的查询开启慢查询日志并写入文件同时记录详细字段。SETGLOBALslow_query_logON;SETGLOBALlong_query_time0.3;SETGLOBALlog_outputFILE;SETGLOBALslow_query_log_file/var/log/mysql/slow.log;SETGLOBALlog_slow_extraON;SETGLOBALmin_examined_row_limit500;SETGLOBALlog_queries_not_using_indexesOFF;-- 本例先关闭专注慢查询配置文件/etc/my.cnf中持久化[mysqld] slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 0.3 log_output FILE log_slow_extra ON min_examined_row_limit 500 log_queries_not_using_indexes OFF 3. 慢查询复现应用程序频繁执行以下 SQL分页查询用户订单SELECTid,status,create_time,amount,product_nameFROMordersWHEREuser_id10086ORDERBYcreate_timeDESCLIMIT20;由于表上只有主键索引user_id无索引且需要按create_time倒序排序MySQL 只能全表扫描。在 MySQL 客户端模拟执行多次运行以产生足够慢日志SELECT...FROMordersWHEREuser_id10086ORDERBYcreate_timeDESCLIMIT20;执行时间通常在2~8秒远超 0.3秒。 4. 慢查询日志内容查看慢日志尾部tail-n20/var/log/mysql/slow.log得到类似输出# Time: 2025-06-27T10:20:33.123456Z # UserHost: app_user[app_user] db1 [10.0.0.5] # Thread_id: 128 Schema: ecommerce QC_hit: No # Query_time: 5.234567 Lock_time: 0.000112 Rows_sent: 20 Rows_examined: 5000000 # Rows_affected: 0 Bytes_sent: 2048 SET timestamp1719476433; SELECT id, status, create_time, amount, product_name FROM orders WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;关键发现Query_time: 5.23秒严重超标。Rows_examined: 5000000扫描全表所有行而Rows_sent: 20只返回20行比例极差。没有合适的索引导致全表扫描 文件排序。 5. 分析与诊断使用EXPLAIN查看执行计划EXPLAINSELECTid,status,create_time,amount,product_nameFROMordersWHEREuser_id10086ORDERBYcreate_timeDESCLIMIT20;输出idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEordersALLNULLNULLNULLNULL4980000Using where; Using filesort解读type: ALL全表扫描。key: NULL未使用任何索引。Extra: Using filesort需要额外排序因为未使用索引顺序。rows估计扫描近500万行。问题根因缺少包含user_id和create_time的联合索引。 6. 优化措施创建联合索引索引设计需满足用user_id等值过滤。按create_time DESC排序以消除 filesort。查询列id, status, create_time, amount, product_name尽量覆盖避免回表。权衡磁盘空间和写入负载后创建覆盖索引读远大于写可接受稍大索引ALTERTABLEordersADDINDEXidx_usr_time_cover(user_id,create_timeDESC,status,amount,product_name);列顺序解释user_id等值条件放在最前。create_time DESC范围排序紧接其后利用降序索引特性MySQL 8.0。status, amount, product_name为覆盖列避免回表。如果 MySQL 版本低于 8.0降序索引不支持可创建(user_id, create_time)即可查询中的DESC仍可利用索引反向扫描8.0 之前虽不支持真正降序索引但升序索引可反向扫描用于ORDER BY ... DESC。执行创建语句ALTERTABLEordersADDINDEXidx_usr_time_cover(user_id,create_timeDESC,status,amount,product_name);在线操作可能耗时几分钟根据表大小评估窗口期。 7. 验证优化效果再次执行原查询SELECTid,status,create_time,amount,product_nameFROMordersWHEREuser_id10086ORDERBYcreate_timeDESCLIMIT20;执行时间降至0.01秒左右。使用EXPLAIN验证新执行计划EXPLAINSELECTid,status,create_time,amount,product_nameFROMordersWHEREuser_id10086ORDERBYcreate_timeDESCLIMIT20;输出idtabletypekeykey_lenrefrowsExtra1ordersrefidx_usr_time_cover8const50Using where; Using indextype: ref索引等值查找。key: idx_usr_time_cover使用了新索引。Extra: Using index覆盖索引无需回表。rows估算仅扫描该用户的50行实际直接定位并顺序读取。再无Using filesort排序直接利用索引顺序。 8. 效果对比与总结指标优化前优化后执行时间5.23 秒0.01 秒扫描行数500 万 (全表)~50 (仅用户订单)ExtraUsing where; Using filesortUsing where; Using index索引使用无idx_usr_time_cover(覆盖)排序文件排序索引顺序无额外排序回表—无覆盖索引通过开启慢查询日志并正确配置成功捕获了这条典型的全表扫描慢 SQL。借助EXPLAIN分析执行计划找到了问题根因——缺少合适的联合索引。最终通过设计覆盖索引将查询性能提升了500倍以上从数秒降至毫秒级极大提升了用户体验并降低数据库负载。 9. 慢查询优化的通用闭环配置合理设置long_query_time、log_slow_extra、轮转策略。捕获收集慢查询日志。分析使用mysqldumpslow/pt-query-digest汇总EXPLAIN逐条诊断。优化添加/调整索引、改写 SQL、调整库表结构。验证再次EXPLAIN确认计划并观察执行时间。监控持续关注慢查询数量形成预防性优化文化。
