1. SQL优化实战索引策略与Explain分析的深度解析刚处理完一个生产环境的慢查询问题查询响应时间从12秒降到0.2秒。这让我想起五年前第一次面对SQL优化时的茫然——当时连执行计划都看不懂现在却能通过索引策略和Explain分析快速定位瓶颈。今天就把这些年积累的实战经验系统梳理出来特别要分享那些官方文档不会告诉你的野路子技巧。SQL优化本质上是在解决数据库的沟通效率问题。就像快递员送包裹索引是导航地图执行计划是配送路线而Explain就是路线规划说明书。当查询变慢时我们需要通过索引策略调整地图精度通过Explain分析找出绕路路段。下面我会用电商、社交、物联网三个典型场景的案例拆解索引设计的思维过程和Explain的深度解读方法。1.1 为什么优化总从索引开始去年双十一压测时我们有个商品搜索接口在1000QPS时CPU直接打满。检查发现这个LIKE查询竟然全表扫描了2000万行数据SELECT * FROM products WHERE name LIKE %智能% AND status 1 ORDER BY sales DESC当时紧急加了(status, sales)的复合索引但效果甚微。后来改成(status, name, sales)的索引性能提升80%。这里有个关键认知索引不仅是加速查询的工具更是改变执行路径的开关。通过索引我们其实是在告诉优化器数据在这条路上走更快。关键认知索引顺序必须匹配查询的筛选漏斗——把过滤性最强的条件放最左。上例中status1能过滤掉70%数据比name的模糊匹配更高效。2.1 Explain执行计划的黑盒破解很多人看Explain只关注type列是不是index这就像看病只量体温。去年我们有个订单查询出现诡异现象EXPLAIN SELECT * FROM orders WHERE user_id 10086 AND create_time 2023-01-01显示用了(user_id, create_time)索引但实际扫描行数却是50万。原来是因为该用户是测试账号历史订单占比极高索引第二列的范围查询导致后续索引失效这种情况需要索引跳跃扫描技巧ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);通过引入低基数的status字段如已支付/未支付让范围查询落在索引第三列。这是B树索引的特性决定的——就像查字典时不能先按第2个字母检索。2.1.1 执行计划中的隐藏信号这几个关键指标90%的人会忽略filtered列显示条件过滤的实际效率Using index condition是否用到索引下推Using filesort的真实代价内存排序还是磁盘临时表去年我们通过监控Using filesort的sort_buffer_size使用情况发现一个分页查询竟然用了800MB排序内存。后来通过optimizer_switch调整了优先使用索引排序的策略。3.1 复合索引设计的黄金法则在社交平台的feed流场景中我们设计过这样一个索引ALTER TABLE posts ADD INDEX idx_geo_tag_time ( geo_hash_prefix, tag_id, is_del, create_time DESC );这个设计包含三个层级策略空间维度用geo_hash前缀快速定位同城内容内容维度按标签二次过滤时间维度保证新内容优先特别注意is_del这个看似多余的字段——实际能过滤掉30%的已删除内容。这种索引包含查询的设计避免了回表操作带来的随机IO。3.1.1 索引维护的实战技巧有个容易踩的坑线上直接添加大表索引导致锁表。我们现在的标准操作流程先在从库用ALGORITHMINPLACE测试添加耗时使用pt-online-schema-change工具在业务低峰期分批创建特别是文本索引去年一个VARCHAR(255)字段的全文索引在2000万数据量下创建耗时从4小时优化到40分钟关键就是调整了innodb_sort_buffer_size参数。4.1 Explain的进阶玩法大多数教程只教基础执行计划解读但实战中我们需要关注4.1.1 代价估算的准确性验证通过EXPLAIN FORMATJSON可以获取更详细的成本计算{ query_cost: 1023.76, cost_info: { eval_cost: 200.00, io_cost: 823.76 } }曾经有个查询优化器误判了JOIN顺序导致选择了比实际慢5倍的执行计划。通过optimizer_trace功能我们发现是因为统计信息过期手动执行ANALYZE TABLE后解决了问题。4.1.2 索引合并的陷阱看到Using union(idx_a,idx_b)别高兴太早——这可能是设计缺陷的信号。我们遇到过一个案例SELECT * FROM users WHERE mobile 13800138000 OR email adminexample.com优化器选择了索引合并但实际性能还不如全表扫描。最终解决方案是建立(mobile,email)的复合索引业务层拆分成两个查询UNION ALL5.1 特殊场景的优化策略5.1.1 分页查询的终极方案深分页是经典难题。我们对比过三种方案常规分页LIMIT 10000,20问题需要先读取10020行再丢弃延迟关联SELECT * FROM users u JOIN (SELECT id FROM users WHERE status1 ORDER BY id LIMIT 10000,20) tmp ON u.id tmp.id优势内层查询只需走索引游标分页SELECT * FROM users WHERE status1 AND id 上次最后ID ORDER BY id LIMIT 20适合无限滚动场景实测在1000万数据量下方案3比方案1快300倍。5.1.2 JSON数据的高效查询随着MySQL 8.0的JSON支持增强我们总结出这些技巧对高频查询的JSON路径建立虚拟列索引使用JSON_CONTAINS替代LIKE %value%多值查询时MEMBER OF()比JSON_OVERLAPS更高效有个物联网项目设备上报的JSON数据经过优化后查询速度从1200ms降到80ms。6.1 监控与持续优化我们团队现在使用这套监控体系慢查询实时捕获通过pt-query-digest分析模式变化索引使用统计定期检查sys.schema_unused_indexes执行计划基线用optimizer_use_plan_baselines防止计划回退上个月刚通过这个体系发现一个新增索引完全未被使用及时进行了清理。这里有个经验值单表索引数超过5个就需要警惕特别是存在冗余索引时。最后分享一个真实案例某核心接口TP99从800ms降到90ms的完整过程。通过EXPLAIN发现虽然走了索引但需要回表查8个字段。解决方案是创建覆盖索引(a,b,c)包含所有查询字段使用FORCE INDEX临时锁定执行计划重构业务代码减少查询字段数这个案例让我深刻认识到优化不是一次性的工作而是需要建立持续监控、快速响应的完整机制。 SQL索引优化与Explain执行计划实战解析
1. SQL优化实战索引策略与Explain分析的深度解析刚处理完一个生产环境的慢查询问题查询响应时间从12秒降到0.2秒。这让我想起五年前第一次面对SQL优化时的茫然——当时连执行计划都看不懂现在却能通过索引策略和Explain分析快速定位瓶颈。今天就把这些年积累的实战经验系统梳理出来特别要分享那些官方文档不会告诉你的野路子技巧。SQL优化本质上是在解决数据库的沟通效率问题。就像快递员送包裹索引是导航地图执行计划是配送路线而Explain就是路线规划说明书。当查询变慢时我们需要通过索引策略调整地图精度通过Explain分析找出绕路路段。下面我会用电商、社交、物联网三个典型场景的案例拆解索引设计的思维过程和Explain的深度解读方法。1.1 为什么优化总从索引开始去年双十一压测时我们有个商品搜索接口在1000QPS时CPU直接打满。检查发现这个LIKE查询竟然全表扫描了2000万行数据SELECT * FROM products WHERE name LIKE %智能% AND status 1 ORDER BY sales DESC当时紧急加了(status, sales)的复合索引但效果甚微。后来改成(status, name, sales)的索引性能提升80%。这里有个关键认知索引不仅是加速查询的工具更是改变执行路径的开关。通过索引我们其实是在告诉优化器数据在这条路上走更快。关键认知索引顺序必须匹配查询的筛选漏斗——把过滤性最强的条件放最左。上例中status1能过滤掉70%数据比name的模糊匹配更高效。2.1 Explain执行计划的黑盒破解很多人看Explain只关注type列是不是index这就像看病只量体温。去年我们有个订单查询出现诡异现象EXPLAIN SELECT * FROM orders WHERE user_id 10086 AND create_time 2023-01-01显示用了(user_id, create_time)索引但实际扫描行数却是50万。原来是因为该用户是测试账号历史订单占比极高索引第二列的范围查询导致后续索引失效这种情况需要索引跳跃扫描技巧ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);通过引入低基数的status字段如已支付/未支付让范围查询落在索引第三列。这是B树索引的特性决定的——就像查字典时不能先按第2个字母检索。2.1.1 执行计划中的隐藏信号这几个关键指标90%的人会忽略filtered列显示条件过滤的实际效率Using index condition是否用到索引下推Using filesort的真实代价内存排序还是磁盘临时表去年我们通过监控Using filesort的sort_buffer_size使用情况发现一个分页查询竟然用了800MB排序内存。后来通过optimizer_switch调整了优先使用索引排序的策略。3.1 复合索引设计的黄金法则在社交平台的feed流场景中我们设计过这样一个索引ALTER TABLE posts ADD INDEX idx_geo_tag_time ( geo_hash_prefix, tag_id, is_del, create_time DESC );这个设计包含三个层级策略空间维度用geo_hash前缀快速定位同城内容内容维度按标签二次过滤时间维度保证新内容优先特别注意is_del这个看似多余的字段——实际能过滤掉30%的已删除内容。这种索引包含查询的设计避免了回表操作带来的随机IO。3.1.1 索引维护的实战技巧有个容易踩的坑线上直接添加大表索引导致锁表。我们现在的标准操作流程先在从库用ALGORITHMINPLACE测试添加耗时使用pt-online-schema-change工具在业务低峰期分批创建特别是文本索引去年一个VARCHAR(255)字段的全文索引在2000万数据量下创建耗时从4小时优化到40分钟关键就是调整了innodb_sort_buffer_size参数。4.1 Explain的进阶玩法大多数教程只教基础执行计划解读但实战中我们需要关注4.1.1 代价估算的准确性验证通过EXPLAIN FORMATJSON可以获取更详细的成本计算{ query_cost: 1023.76, cost_info: { eval_cost: 200.00, io_cost: 823.76 } }曾经有个查询优化器误判了JOIN顺序导致选择了比实际慢5倍的执行计划。通过optimizer_trace功能我们发现是因为统计信息过期手动执行ANALYZE TABLE后解决了问题。4.1.2 索引合并的陷阱看到Using union(idx_a,idx_b)别高兴太早——这可能是设计缺陷的信号。我们遇到过一个案例SELECT * FROM users WHERE mobile 13800138000 OR email adminexample.com优化器选择了索引合并但实际性能还不如全表扫描。最终解决方案是建立(mobile,email)的复合索引业务层拆分成两个查询UNION ALL5.1 特殊场景的优化策略5.1.1 分页查询的终极方案深分页是经典难题。我们对比过三种方案常规分页LIMIT 10000,20问题需要先读取10020行再丢弃延迟关联SELECT * FROM users u JOIN (SELECT id FROM users WHERE status1 ORDER BY id LIMIT 10000,20) tmp ON u.id tmp.id优势内层查询只需走索引游标分页SELECT * FROM users WHERE status1 AND id 上次最后ID ORDER BY id LIMIT 20适合无限滚动场景实测在1000万数据量下方案3比方案1快300倍。5.1.2 JSON数据的高效查询随着MySQL 8.0的JSON支持增强我们总结出这些技巧对高频查询的JSON路径建立虚拟列索引使用JSON_CONTAINS替代LIKE %value%多值查询时MEMBER OF()比JSON_OVERLAPS更高效有个物联网项目设备上报的JSON数据经过优化后查询速度从1200ms降到80ms。6.1 监控与持续优化我们团队现在使用这套监控体系慢查询实时捕获通过pt-query-digest分析模式变化索引使用统计定期检查sys.schema_unused_indexes执行计划基线用optimizer_use_plan_baselines防止计划回退上个月刚通过这个体系发现一个新增索引完全未被使用及时进行了清理。这里有个经验值单表索引数超过5个就需要警惕特别是存在冗余索引时。最后分享一个真实案例某核心接口TP99从800ms降到90ms的完整过程。通过EXPLAIN发现虽然走了索引但需要回表查8个字段。解决方案是创建覆盖索引(a,b,c)包含所有查询字段使用FORCE INDEX临时锁定执行计划重构业务代码减少查询字段数这个案例让我深刻认识到优化不是一次性的工作而是需要建立持续监控、快速响应的完整机制。