【架构实战】MySQL慢查询诊断与优化方法论

一、慢查询概述MySQL慢查询是数据库性能问题的常见原因慢查询定义查询时间超过指定阈值默认1秒全表扫描缺少索引慢查询的影响页面响应变慢数据库CPU使用率高连接池耗尽二、慢查询开启与配置1. 开启慢查询日志-- 查看慢查询配置SHOWVARIABLESLIKEslow_query_log%;SHOWVARIABLESLIKElong_query_time%;-- 开启慢查询SETGLOBALslow_query_logON;SETGLOBALslow_query_log_file/var/log/mysql/slow.log;SETGLOBALlong_query_time1;-- 1秒-- 开启记录未使用索引的查询SETGLOBALlog_queries_not_using_indexesON;2. 配置文件方式[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1 # 记录管理语句 log_slow_admin_statements 1 log_slow_slave_statements 1三、慢查询分析工具1. mysqldumpslow# 查看最慢的10条查询mysqldumpslow-st-t10/var/log/mysql/slow.log# 查看最频繁的10条查询mysqldumpslow-sc-t10/var/log/mysql/slow.log# 按时间排序查看mysqldumpslow-st-t10-gorder/var/log/mysql/slow.log2. pt-query-digest# 安装yuminstallpercona-toolkit# 分析慢查询pt-query-digest /var/log/mysql/slow.log# 输出到文件pt-query-digest slow.logreport.txt# 分析最近24小时的查询pt-query-digest--since24h slow.log四、EXPLAIN分析1. EXPLAIN使用EXPLAINSELECT*FROMordersWHEREuser_id1001;EXPLAINFORMATJSONSELECT*FROMordersWHEREuser_id1001;2. 关键字段解读字段说明优化目标type访问类型至少rangekey使用的索引非NULLrows扫描行数越少越好Extra额外信息避免filesort3. 常见问题type: ALL -- 全表扫描需要优化 key: NULL -- 未使用索引需要创建 rows: 100000 -- 扫描行数过多需要优化 Extra: Using filesort -- 需要排序需要优化五、索引优化1. 索引设计原则1. WHERE条件字段建索引 2. 复合索引最左前缀 3. 区分度高的放前面 4. 避免索引失效2. 索引失效场景-- ❌ 索引列使用函数WHEREYEAR(order_time)2024-- ✅ 正确写法WHEREorder_time2024-01-01-- ❌ 前导模糊查询WHEREnameLIKE%zhang-- ✅ 正确写法WHEREnameLIKEzhang%-- ❌ 类型不匹配WHEREorder_id12345-- order_id是INT-- ✅ 正确写法WHEREorder_id123453. 索引选择-- 查看表索引SHOWINDEXFROMorders;-- 查看索引选择性SELECTCOUNT(DISTINCTcolumn)/COUNT(*)ASselectivityFROMtable_name;-- 查看冗余索引SELECTa.TABLE_SCHEMA,a.TABLE_NAME,a.INDEX_NAME,b.INDEX_NAMEASredundant_indexFROMSTATISTICSaJOINSTATISTICSbONa.TABLE_SCHEMAb.TABLE_SCHEMAANDa.TABLE_NAMEb.TABLE_NAMEANDa.SEQ_IN_INDEXb.SEQ_IN_INDEXWHEREa.INDEX_NAMEb.INDEX_NAME;六、SQL优化技巧1. 避免SELECT *-- ❌SELECT*FROMordersWHEREorder_id1;-- ✅SELECTorder_id,order_no,order_amountFROMordersWHEREorder_id1;2. 批量操作-- ❌ 循环插入INSERTINTOorders(order_no)VALUES(A001);INSERTINTOorders(order_no)VALUES(A002);-- ✅ 批量插入INSERTINTOorders(order_no)VALUES(A001),(A002);3. 分页优化-- ❌ 深度分页SELECT*FROMordersLIMIT1000000,10;-- ✅ 游标分页SELECT*FROMordersWHEREid1000000LIMIT10;-- ✅ 子查询SELECT*FROMordersWHEREidIN(SELECTidFROMordersORDERBYidLIMIT1000000,10);4. JOIN优化-- ✅ 小表驱动大表SELECT*FROMorders oINNERJOINusers uONo.user_idu.idWHEREu.status1;-- ✅ 确保JOIN字段有索引ALTERTABLEordersADDINDEXidx_user_id(user_id);ALTERTABLEusersADDINDEXidx_id(id);七、慢查询优化案例案例1订单列表优化原始SQLSELECT*FROMordersWHEREuser_id1001ORDERBYcreate_timeDESCLIMIT0,20;分析type: ALL全表扫描rows: 1000000扫描100万行Extra: Using filesort优化-- 添加复合索引ALTERTABLEordersADDINDEXidx_user_time(user_id,create_timeDESC);结果type: ref索引查询rows: 20案例2统计查询优化原始SQLSELECTDATE(create_time)ASdate,COUNT(*)FROMordersWHEREcreate_time2024-01-01GROUPBYDATE(create_time);问题在列上使用函数导致索引失效优化-- 预计算表CREATETABLEdaily_stats(stat_dateDATEPRIMARYKEY,order_countINT);-- 定时任务计算INSERTINTOdaily_statsSELECTDATE(create_time),COUNT(*)FROMordersWHEREcreate_timeDATE_SUB(CURDATE(),INTERVAL1DAY)GROUPBYDATE(create_time);-- 查询预计算表SELECT*FROMdaily_statsWHEREstat_date2024-01-01;八、监控与告警1. 慢查询监控-- 查看最近慢查询SELECT*FROMmysql.slow_logORDERBYstart_timeDESCLIMIT10;-- 统计慢查询数量SELECTCOUNT(*)FROMmysql.slow_logWHEREstart_timeDATE_SUB(NOW(),INTERVAL1HOUR);2. Prometheus监控-job_name:mysqlstatic_configs:-targets:[localhost:9104]九、总结MySQL慢查询优化方法论开启慢查询日志发现慢SQLEXPLAIN分析定位问题索引优化创建合适索引SQL重构避免性能陷阱最佳实践定期分析慢查询日志监控慢查询数量建立索引规范避免深度分页个人观点仅供参考