SQL优化实战：索引与查询性能提升指南

1. SQL优化实战指南从理论到落地刚入行那会儿我最怕听到的就是这个查询太慢了优化一下。面对一个执行时间超过30秒的SQL我常常手足无措——加索引改写法还是调整参数经过多年实战我发现SQL优化不是玄学而是有章可循的工程实践。今天我就分享几个在工作中反复验证有效的优化技巧每个都附带可直接运行的案例。2. 理解SQL执行原理优化前的必修课2.1 执行计划优化师的X光片EXPLAIN SELECT * FROM orders WHERE user_id 100 AND status completed;这个简单的命令会揭示数据库如何处理你的查询。关键要看type列从最优到最差依次是 system const eq_ref ref range index ALLkey列实际使用的索引rows列预估扫描行数Extra列Using filesort、Using temporary 都是危险信号经验MySQL 8.0 建议使用EXPLAIN ANALYZE获取实际执行数据2.2 索引的底层实现B树索引就像图书馆的目录系统主键索引是完整的图书目录聚集索引二级索引就像作者索引需要回表查询联合索引遵循最左前缀原则像电话簿按姓名排序-- 失效的索引使用 SELECT * FROM users WHERE YEAR(create_time) 2023; -- 应改为 SELECT * FROM users WHERE create_time BETWEEN 2023-01-01 AND 2023-12-31;3. 高频优化场景实战3.1 索引优化经典案例场景订单分页查询慢-- 原始SQL执行2.4s SELECT * FROM orders WHERE user_id 100 ORDER BY create_time DESC LIMIT 10 OFFSET 10000; -- 优化方案0.02s ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time); -- 更优写法 SELECT * FROM orders WHERE user_id 100 AND id last_seen_id -- 记住上一页最后ID ORDER BY create_time DESC LIMIT 10;避坑指南避免在索引列上使用函数分页查询避免大OFFSET联合索引字段顺序查询条件顺序排序字段3.2 改写查询的魔法案例统计每月订单量-- 原始全表扫描 SELECT DATE_FORMAT(create_time,%Y-%m), COUNT(*) FROM orders GROUP BY DATE_FORMAT(create_time,%Y-%m); -- 优化索引扫描 SELECT YEAR(create_time) AS y, MONTH(create_time) AS m, COUNT(*) FROM orders WHERE create_time 2020-01-01 GROUP BY y, m;经验能用WHERE过滤的就不要HAVING多表连接时先过滤再关联子查询尽量改写成JOIN4. 高级优化技巧4.1 隐式类型转换陷阱-- user_id是varchar类型但传入数字导致索引失效 SELECT * FROM users WHERE user_id 100; -- 解决方案 SELECT * FROM users WHERE user_id 100;4.2 临时表优化当看到Extra出现Using temporary时-- 原始产生临时表 SELECT DISTINCT department FROM employees; -- 优化利用索引避免临时表 SELECT department FROM employees GROUP BY department;5. 实战问题排查手册问题索引存在但查询仍然慢排查步骤确认执行计划确实使用了索引检查索引区分度SELECT COUNT(DISTINCT column)/COUNT(*) FROM table检查回表代价EXPLAIN FORMATJSON查看cost_info案例-- 看似用了索引实际效果差 SELECT * FROM products WHERE category LIKE 电子%; -- 优化方案 ALTER TABLE products ADD INDEX idx_category_name (category, name); SELECT name,price FROM products WHERE category LIKE 电子%;6. 性能优化工具箱6.1 必备监控命令-- 查看慢查询 SHOW VARIABLES LIKE slow_query_log%; -- 当前连接状态 SHOW PROCESSLIST; -- 索引统计信息 SHOW INDEX FROM orders;6.2 参数调优参考# my.cnf关键参数 innodb_buffer_pool_size 12G # 内存的50-70% innodb_log_file_size 4G sort_buffer_size 4M join_buffer_size 4M7. 真实案例解析电商系统优化实例原始查询执行8秒SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status shipped AND o.create_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY o.amount DESC LIMIT 100;优化步骤添加联合索引(status, create_time, amount)改写为延迟关联SELECT o.*, u.name FROM ( SELECT id FROM orders WHERE status shipped AND create_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY amount DESC LIMIT 100 ) tmp JOIN orders o ON tmp.id o.id JOIN users u ON o.user_id u.id;优化后执行时间0.15秒8. 保持优化思维我习惯在每个迭代周期预留20%时间做性能优化。最有效的优化往往来自业务逻辑调整如分批处理替代实时计算数据模型改进如适度反范式化架构层面解决如引入缓存记住最好的优化是不需要优化。在设计阶段就考虑主键用自增INT而非UUID避免过度分表为未来6个月的数据量预留索引