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

一、为什么需要SQL调优

在应用开发中，SQL性能直接影响系统响应速度：

慢SQL的影响：

• 页面加载缓慢，用户体验差
• 数据库CPU使用率飙升
• 连接池耗尽，应用不可用
• 甚至引发连锁故障

调优的目标：

• 查询时间从秒级降到毫秒级
• 减少数据库资源消耗
• 提升系统吞吐量

二、执行计划分析

1. EXPLAIN使用

-- 基本分析EXPLAINSELECT*FROMordersWHEREuser_id=1001;-- 详细分析（MySQL 8.0+）EXPLAINANALYZESELECT*FROMordersWHEREuser_id=1001;

返回字段说明：

2. 访问类型

3. Extra信息

三、索引优化

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_time>='2024-01-01'ANDcreate_time<'2025-01-01';-- ❌ 使用函数SELECT*FROMusersWHERELOWER(phone)='13800138000';-- ✅ 正确写法SELECT*FROMusersWHEREphone='13800138000';-- ❌ 类型转换SELECT*FROMordersWHEREorder_no=12345;-- ✅ 正确写法SELECT*FROMordersWHEREorder_no='12345';-- ❌ 前缀模糊查询SELECT*FROMusersWHEREphoneLIKE'%138';-- ✅ 正确写法（后缀模糊查询仍可以用索引）SELECT*FROMusersWHEREphoneLIKE'138%';

四、SQL优化技巧

1. 避免SELECT *

-- ❌ 查询所有列SELECT*FROMordersWHEREorder_id=1;-- ✅ 只查询需要的列SELECTorder_id,order_no,order_amountFROMordersWHEREorder_id=1;

2. 批量操作

-- ❌ 循环插入INSERTINTOorders(order_no)VALUES('A001');INSERTINTOorders(order_no)VALUES('A002');-- ✅ 批量插入INSERTINTOorders(order_no)VALUES('A001'),('A002'),('A003');

3. 避免深度分页

-- ❌ 深度分页SELECT*FROMordersORDERBYidLIMIT1000000,10;-- ✅ 方式1：游标分页SELECT*FROMordersWHEREid>1000000ORDERBYidLIMIT10;-- ✅ 方式2：子查询SELECT*FROM(SELECTidFROMordersORDERBYidLIMIT1000000,10)tJOINordersONt.id=orders.id;-- ✅ 方式3：记录总数（先查ID）SELECT*FROMordersWHEREidIN(SELECTidFROMordersORDERBYidLIMIT1000000,10);

4. 预计算

-- ❌ 每次统计SELECTCOUNT(*)FROMordersWHEREorder_date='2024-01-15';-- ✅ 预计算表CREATETABLEdaily_order_stats(stat_dateDATEPRIMARYKEY,order_countINT,order_amountDECIMAL(14,2));-- 定时更新统计数据INSERTINTOdaily_order_statsSELECTorder_date,COUNT(*),SUM(order_amount)FROMordersWHEREorder_date='2024-01-14'GROUPBYorder_date;

五、慢查询诊断

1. 开启慢查询日志

-- 查看配置SHOWVARIABLESLIKE'slow_query_log%';SHOWVARIABLESLIKE'long_query_time%';-- 开启慢查询SETGLOBALslow_query_log='ON';SETGLOBALlong_query_time=1;-- 1秒-- 查看慢查询SHOWGLOBALSTATUSLIKE'Slow_queries';

2. 分析慢查询

-- 查看最近的慢查询SELECT*FROMmysql.slow_logORDERBYstart_timeDESCLIMIT10;-- 使用mysqldumpslow分析mysqldumpslow-s t-t10/var/log/mysql/slow.log

3. 诊断脚本

-- 查看最慢的查询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：订单列表优化

原始SQL：

SELECT*FROMordersWHEREuser_id=1001ORDERBYcreate_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：统计查询优化

原始SQL：

SELECTDATE(order_time)asdate,COUNT(*)asorder_count,SUM(order_amount)astotal_amountFROMordersWHEREorder_time>='2024-01-01'GROUPBYDATE(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_time>=NOW()-INTERVAL1DAYGROUPBYDATE(order_time);-- 查询预计算表SELECT*FROMdaily_statsWHEREstat_date>='2024-01-01';

七、总结

SQL调优是提升数据库性能的核心：

• 执行计划：分析查询如何执行
• 索引优化：创建合适的索引
• SQL重构：避免性能陷阱
• 慢查询监控：及时发现问题

最佳实践：

1. 优先使用索引，避免全表扫描
2. 避免在索引列上使用函数
3. 用EXPLAIN分析每条慢SQL
4. 定期维护索引（重建、删除冗余）

个人观点，仅供参考