【架构实战】SQL调优实战：从执行计划到索引优化

一、为什么需要SQL调优在应用开发中SQL性能直接影响系统响应速度慢SQL的影响页面加载缓慢用户体验差数据库CPU使用率飙升连接池耗尽应用不可用甚至引发连锁故障调优的目标查询时间从秒级降到毫秒级减少数据库资源消耗提升系统吞吐量二、执行计划分析1. EXPLAIN使用-- 基本分析EXPLAINSELECT*FROMordersWHEREuser_id1001;-- 详细分析MySQL 8.0EXPLAINANALYZESELECT*FROMordersWHEREuser_id1001;返回字段说明字段说明id查询编号select_type查询类型table涉及的表partitions涉及的分区type访问类型重要possible_keys可用的索引key实际使用的索引key_len索引长度ref索引列的引用rows预计扫描行数重要filtered过滤比例Extra额外信息重要2. 访问类型type值说明性能ALL全表扫描最差index索引全扫描较差range索引范围扫描一般ref索引等值查询较好eq_ref唯一索引查询较好const常量查询最好3. Extra信息信息说明Using filesort需要额外排序Using temporary使用临时表Using index覆盖索引Using index condition索引下推Using where使用WHERE过滤三、索引优化1. 索引设计原则1. 区分度高的列放在前面 2. 复合索引遵循最左前缀原则 3. 不要在索引列上做函数运算 4. 尽量使用覆盖索引 5. 避免索引失效2. 索引示例-- 用户表索引设计CREATETABLEusers(idBIGINTPRIMARYKEYAUTO_INCREMENT,phoneVARCHAR(20),emailVARCHAR(100),statusTINYINT,create_timeTIMESTAMP,-- 手机号查询高频率INDEXidx_phone(phone),-- 邮箱查询INDEXidx_email(email),-- 复合索引状态创建时间按状态筛选后按时间排序INDEXidx_status_time(status,create_time),-- 复合索引查询某个状态的最新用户INDEXidx_status_create(status,create_timeDESC));-- 订单表索引设计CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(32),user_idBIGINT,shop_idBIGINT,order_statusTINYINT,order_amountDECIMAL(12,2),order_timeTIMESTAMP,pay_timeTIMESTAMP,-- 订单号唯一索引UNIQUEINDEXuk_order_no(order_no),-- 用户订单列表最常用INDEXidx_user_time(user_id,order_timeDESC),-- 商家订单列表INDEXidx_shop_time(shop_id,order_timeDESC),-- 状态查询INDEXidx_status(order_status),-- 复合索引商家状态时间INDEXidx_shop_status_time(shop_id,order_status,order_timeDESC));3. 索引失效场景-- ❌ 索引列做运算SELECT*FROMusersWHEREYEAR(create_time)2024;-- ✅ 正确写法SELECT*FROMusersWHEREcreate_time2024-01-01ANDcreate_time2025-01-01;-- ❌ 使用函数SELECT*FROMusersWHERELOWER(phone)13800138000;-- ✅ 正确写法SELECT*FROMusersWHEREphone13800138000;-- ❌ 类型转换SELECT*FROMordersWHEREorder_no12345;-- ✅ 正确写法SELECT*FROMordersWHEREorder_no12345;-- ❌ 前缀模糊查询SELECT*FROMusersWHEREphoneLIKE%138;-- ✅ 正确写法后缀模糊查询仍可以用索引SELECT*FROMusersWHEREphoneLIKE138%;四、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),(A003);3. 避免深度分页-- ❌ 深度分页SELECT*FROMordersORDERBYidLIMIT1000000,10;-- ✅ 方式1游标分页SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT10;-- ✅ 方式2子查询SELECT*FROM(SELECTidFROMordersORDERBYidLIMIT1000000,10)tJOINordersONt.idorders.id;-- ✅ 方式3记录总数先查IDSELECT*FROMordersWHEREidIN(SELECTidFROMordersORDERBYidLIMIT1000000,10);4. 预计算-- ❌ 每次统计SELECTCOUNT(*)FROMordersWHEREorder_date2024-01-15;-- ✅ 预计算表CREATETABLEdaily_order_stats(stat_dateDATEPRIMARYKEY,order_countINT,order_amountDECIMAL(14,2));-- 定时更新统计数据INSERTINTOdaily_order_statsSELECTorder_date,COUNT(*),SUM(order_amount)FROMordersWHEREorder_date2024-01-14GROUPBYorder_date;五、慢查询诊断1. 开启慢查询日志-- 查看配置SHOWVARIABLESLIKEslow_query_log%;SHOWVARIABLESLIKElong_query_time%;-- 开启慢查询SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 1秒-- 查看慢查询SHOWGLOBALSTATUSLIKESlow_queries;2. 分析慢查询-- 查看最近的慢查询SELECT*FROMmysql.slow_logORDERBYstart_timeDESCLIMIT10;-- 使用mysqldumpslow分析mysqldumpslow-s t-t10/var/log/mysql/slow.log3. 诊断脚本-- 查看最慢的查询SELECTquery,count(*)asexecutions,avg(sec)asavg_sec,max(sec)asmax_sec,sum(sec)astotal_secFROM(SELECTSUBSTRING(SQL_TEXT,1,100)asquery,TIME_TO_SEC(EXEC_TIME)assecFROMmysql.slow_query_log)tGROUPBYqueryORDERBYtotal_secDESCLIMIT10;六、实战案例案例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只扫描20行Extra: Using index condition案例2统计查询优化原始SQLSELECTDATE(order_time)asdate,COUNT(*)asorder_count,SUM(order_amount)astotal_amountFROMordersWHEREorder_time2024-01-01GROUPBYDATE(order_time);问题在GROUP BY上使用函数导致索引失效优化方案-- 方案1避免函数ALTERTABLEordersADDINDEXidx_order_time(order_time);-- 方案2预计算表CREATETABLEdaily_stats(stat_dateDATEPRIMARYKEY,order_countINT,order_amountDECIMAL(14,2));-- 定时任务每天0点计算前一天数据INSERTINTOdaily_statsSELECTDATE(order_time)asstat_date,COUNT(*)asorder_count,SUM(order_amount)asorder_amountFROMordersWHEREorder_timeNOW()-INTERVAL1DAYGROUPBYDATE(order_time);-- 查询预计算表SELECT*FROMdaily_statsWHEREstat_date2024-01-01;七、总结SQL调优是提升数据库性能的核心执行计划分析查询如何执行索引优化创建合适的索引SQL重构避免性能陷阱慢查询监控及时发现问题最佳实践优先使用索引避免全表扫描避免在索引列上使用函数用EXPLAIN分析每条慢SQL定期维护索引重建、删除冗余个人观点仅供参考