MySQL索引优化实战从执行计划解读到慢查询根治数据库性能问题就像房间里的大象——所有人都知道它存在却常常选择视而不见。直到某天凌晨三点值班电话突然响起你才意识到那个被忽略的索引问题已经演变成了生产事故。这不是危言耸听根据2023年数据库性能报告超过67%的生产环境性能问题都源于不当的索引设计。1. 索引失效的七宗罪那些年我们踩过的坑在成都某电商平台的黑色星期五大促中一个本该承载百万级QPS的商品搜索接口突然响应时间突破5秒。技术团队紧急排查后发现问题出在一个看似简单的查询上SELECT * FROM products WHERE category_id 1024 AND status ON_SALE ORDER BY price DESC LIMIT 50;这个查询在测试环境运行良好却在生产环境成了性能杀手。根本原因在于开发者在category_id和status字段上建立了独立的单列索引而MySQL优化器最终选择了category_id索引导致需要扫描12万行数据并进行昂贵的filesort操作。1.1 联合索引的致命误区最典型的索引误用场景包括该用联合索引时用了多个单列索引联合索引的字段顺序与查询条件不匹配在索引列上使用函数或计算以刚才的电商查询为例正确的索引应该是ALTER TABLE products ADD INDEX idx_cat_status_price (category_id, status, price);这个联合索引能同时满足WHERE条件过滤和ORDER BY排序需求执行计划会显示Using index而非可怕的Using filesort。1.2 索引选择性陷阱索引选择性是指索引中不同值的数量与表中记录总数的比值。有个容易忽视的真相低选择性的索引可能比全表扫描更糟糕。比如在性别字段上建索引就是个经典反模式字段不同值数量总记录数选择性是否适合索引gender21,000,0000.0002%user_id1,000,0001,000,000100%mobile950,0001,000,00095%经验法则选择性低于10%的字段通常不适合单独建立索引但可以作为联合索引的后缀字段2. Explain执行计划深度解码Explain不是占卜工具而是数据库优化器的思想报告。某金融系统曾有个查询耗时8秒执行计划却显示type: index看起来使用了索引。但细看rows列显示扫描了50万行——这实际上相当于全索引扫描。2.1 关键指标的四维分析执行计划中真正需要关注的四个维度访问类型typesystemconsteq_refrefrangeindexALL至少要达到range级别索引使用情况key_len计算实际使用的索引长度与联合索引设计对比可发现字段截断问题额外信息ExtraUsing index覆盖索引Using temporary需要临时表Using filesort需要额外排序扫描行数rows与实际返回行数对比突然增长可能预示索引失效2.2 执行计划实战案例分析这个看似简单的查询EXPLAIN SELECT user_name FROM users WHERE register_time 2023-01-01 AND age BETWEEN 18 AND 30;得到的执行计划idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEusersrangeidx_reg_ageidx_reg_age615420Using where这个结果告诉我们使用了idx_reg_age索引的range扫描索引长度6字节可能只用了register_time字段仍需扫描1.5万行数据潜在优化方向调整索引字段顺序或创建更适合的联合索引3. 慢查询日志的黄金组合拳某社交平台通过慢查询日志发现夜间批量任务中有个UPDATE语句平均执行4.2秒UPDATE user_activities SET last_active NOW() WHERE user_id IN ( SELECT user_id FROM vip_users WHERE expiration_date CURDATE() );3.1 慢日志配置的进阶技巧在my.cnf中加入这些配置slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1 log_throttle_queries_not_using_indexes 10 min_examined_row_limit 100警告生产环境开启log_queries_not_using_indexes可能导致日志暴涨建议配合log_throttle_queries_not_using_indexes使用3.2 慢日志分析三板斧pt-query-digest工具pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt关键指标排序按Query_time排序找最耗时的按Rows_examined排序找扫描行数多的按出现次数排序找高频查询执行时间分布分析是否总在特定时间段出现是否与定时任务相关是否伴随锁等待4. 索引优化实战手册杭州某物流系统曾有个分页查询随着页数增加响应时间呈指数增长SELECT * FROM waybills WHERE warehouse_id 5 AND create_time 2023-06-01 ORDER BY waybill_no DESC LIMIT 10000, 20;4.1 分页查询的终极解决方案传统优化方案是使用延迟关联SELECT * FROM waybills INNER JOIN ( SELECT id FROM waybills WHERE warehouse_id 5 AND create_time 2023-06-01 ORDER BY waybill_no DESC LIMIT 10000, 20 ) AS tmp USING(id);但更优雅的方式是使用游标分页SELECT * FROM waybills WHERE warehouse_id 5 AND create_time 2023-06-01 AND waybill_no WAYBILL_20230630_99999 ORDER BY waybill_no DESC LIMIT 20;4.2 索引设计检查清单在创建新索引前先回答这些问题这个查询的执行频率是多少WHERE条件中最具选择性的字段是什么ORDER BY和GROUP BY使用了哪些字段查询返回的字段能否被索引覆盖表的数据量和增长趋势如何复合索引黄金法则等值条件字段优先范围条件字段次之排序字段放在最后确保索引最左前缀匹配5. 特殊场景的索引策略在物联网(IoT)领域我们经常需要处理时间序列数据。某智能家居平台的海量设备状态记录表就遇到了这样的查询难题SELECT device_id, MAX(temperature) FROM device_metrics WHERE metric_time BETWEEN 2023-07-01 AND 2023-07-02 GROUP BY device_id;5.1 时间序列数据的索引魔法针对这类场景推荐使用时间分区复合索引ALTER TABLE device_metrics PARTITION BY RANGE (UNIX_TIMESTAMP(metric_time)) ( PARTITION p202307 VALUES LESS THAN (UNIX_TIMESTAMP(2023-08-01)), PARTITION p202308 VALUES LESS THAN (UNIX_TIMESTAMP(2023-09-01)) ); ALTER TABLE device_metrics ADD INDEX idx_device_metric_time (device_id, metric_time);这种组合能实现分区裁剪减少扫描范围索引覆盖GROUP BY和WHERE条件避免全表扫描5.2 JSON字段的索引技巧随着MySQL对JSON支持越来越完善很多团队开始大量使用JSON字段。某内容管理系统在JSON数组上建立函数索引的案例值得学习ALTER TABLE articles ADD INDEX idx_tag_ids ((CAST(tag_ids-$[*] AS CHAR(32) ARRAY))), ALGORITHMINPLACE;查询时使用MEMBER OF操作符SELECT * FROM articles WHERE 1024 MEMBER OF(tag_ids-$[*]);6. 监控与持续优化索引不是一劳永逸的解决方案。某SaaS平台每月新增百万用户后原本高效的索引逐渐变成了性能瓶颈。他们建立了这样的监控体系索引使用率监控SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star 0 ORDER BY object_schema, object_name;索引冗余检测SELECT table_name, index_name, seq_in_index, column_name FROM information_schema.statistics WHERE table_schema your_db ORDER BY table_name, index_name, seq_in_index;索引碎片化检查SELECT table_name, index_name, ROUND(stat_value * innodb_page_size / 1024 / 1024, 2) AS size_mb, stat_description FROM mysql.innodb_index_stats WHERE database_name your_db AND stat_name size;这套监控方案帮助他们每月节省了30%的数据库存储空间同时查询性能提升了15-20%。记住索引优化是持续过程需要定期review和调整。
别再乱用索引了！MySQL索引设计实战：从Explain执行计划到慢查询优化
MySQL索引优化实战从执行计划解读到慢查询根治数据库性能问题就像房间里的大象——所有人都知道它存在却常常选择视而不见。直到某天凌晨三点值班电话突然响起你才意识到那个被忽略的索引问题已经演变成了生产事故。这不是危言耸听根据2023年数据库性能报告超过67%的生产环境性能问题都源于不当的索引设计。1. 索引失效的七宗罪那些年我们踩过的坑在成都某电商平台的黑色星期五大促中一个本该承载百万级QPS的商品搜索接口突然响应时间突破5秒。技术团队紧急排查后发现问题出在一个看似简单的查询上SELECT * FROM products WHERE category_id 1024 AND status ON_SALE ORDER BY price DESC LIMIT 50;这个查询在测试环境运行良好却在生产环境成了性能杀手。根本原因在于开发者在category_id和status字段上建立了独立的单列索引而MySQL优化器最终选择了category_id索引导致需要扫描12万行数据并进行昂贵的filesort操作。1.1 联合索引的致命误区最典型的索引误用场景包括该用联合索引时用了多个单列索引联合索引的字段顺序与查询条件不匹配在索引列上使用函数或计算以刚才的电商查询为例正确的索引应该是ALTER TABLE products ADD INDEX idx_cat_status_price (category_id, status, price);这个联合索引能同时满足WHERE条件过滤和ORDER BY排序需求执行计划会显示Using index而非可怕的Using filesort。1.2 索引选择性陷阱索引选择性是指索引中不同值的数量与表中记录总数的比值。有个容易忽视的真相低选择性的索引可能比全表扫描更糟糕。比如在性别字段上建索引就是个经典反模式字段不同值数量总记录数选择性是否适合索引gender21,000,0000.0002%user_id1,000,0001,000,000100%mobile950,0001,000,00095%经验法则选择性低于10%的字段通常不适合单独建立索引但可以作为联合索引的后缀字段2. Explain执行计划深度解码Explain不是占卜工具而是数据库优化器的思想报告。某金融系统曾有个查询耗时8秒执行计划却显示type: index看起来使用了索引。但细看rows列显示扫描了50万行——这实际上相当于全索引扫描。2.1 关键指标的四维分析执行计划中真正需要关注的四个维度访问类型typesystemconsteq_refrefrangeindexALL至少要达到range级别索引使用情况key_len计算实际使用的索引长度与联合索引设计对比可发现字段截断问题额外信息ExtraUsing index覆盖索引Using temporary需要临时表Using filesort需要额外排序扫描行数rows与实际返回行数对比突然增长可能预示索引失效2.2 执行计划实战案例分析这个看似简单的查询EXPLAIN SELECT user_name FROM users WHERE register_time 2023-01-01 AND age BETWEEN 18 AND 30;得到的执行计划idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEusersrangeidx_reg_ageidx_reg_age615420Using where这个结果告诉我们使用了idx_reg_age索引的range扫描索引长度6字节可能只用了register_time字段仍需扫描1.5万行数据潜在优化方向调整索引字段顺序或创建更适合的联合索引3. 慢查询日志的黄金组合拳某社交平台通过慢查询日志发现夜间批量任务中有个UPDATE语句平均执行4.2秒UPDATE user_activities SET last_active NOW() WHERE user_id IN ( SELECT user_id FROM vip_users WHERE expiration_date CURDATE() );3.1 慢日志配置的进阶技巧在my.cnf中加入这些配置slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1 log_throttle_queries_not_using_indexes 10 min_examined_row_limit 100警告生产环境开启log_queries_not_using_indexes可能导致日志暴涨建议配合log_throttle_queries_not_using_indexes使用3.2 慢日志分析三板斧pt-query-digest工具pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt关键指标排序按Query_time排序找最耗时的按Rows_examined排序找扫描行数多的按出现次数排序找高频查询执行时间分布分析是否总在特定时间段出现是否与定时任务相关是否伴随锁等待4. 索引优化实战手册杭州某物流系统曾有个分页查询随着页数增加响应时间呈指数增长SELECT * FROM waybills WHERE warehouse_id 5 AND create_time 2023-06-01 ORDER BY waybill_no DESC LIMIT 10000, 20;4.1 分页查询的终极解决方案传统优化方案是使用延迟关联SELECT * FROM waybills INNER JOIN ( SELECT id FROM waybills WHERE warehouse_id 5 AND create_time 2023-06-01 ORDER BY waybill_no DESC LIMIT 10000, 20 ) AS tmp USING(id);但更优雅的方式是使用游标分页SELECT * FROM waybills WHERE warehouse_id 5 AND create_time 2023-06-01 AND waybill_no WAYBILL_20230630_99999 ORDER BY waybill_no DESC LIMIT 20;4.2 索引设计检查清单在创建新索引前先回答这些问题这个查询的执行频率是多少WHERE条件中最具选择性的字段是什么ORDER BY和GROUP BY使用了哪些字段查询返回的字段能否被索引覆盖表的数据量和增长趋势如何复合索引黄金法则等值条件字段优先范围条件字段次之排序字段放在最后确保索引最左前缀匹配5. 特殊场景的索引策略在物联网(IoT)领域我们经常需要处理时间序列数据。某智能家居平台的海量设备状态记录表就遇到了这样的查询难题SELECT device_id, MAX(temperature) FROM device_metrics WHERE metric_time BETWEEN 2023-07-01 AND 2023-07-02 GROUP BY device_id;5.1 时间序列数据的索引魔法针对这类场景推荐使用时间分区复合索引ALTER TABLE device_metrics PARTITION BY RANGE (UNIX_TIMESTAMP(metric_time)) ( PARTITION p202307 VALUES LESS THAN (UNIX_TIMESTAMP(2023-08-01)), PARTITION p202308 VALUES LESS THAN (UNIX_TIMESTAMP(2023-09-01)) ); ALTER TABLE device_metrics ADD INDEX idx_device_metric_time (device_id, metric_time);这种组合能实现分区裁剪减少扫描范围索引覆盖GROUP BY和WHERE条件避免全表扫描5.2 JSON字段的索引技巧随着MySQL对JSON支持越来越完善很多团队开始大量使用JSON字段。某内容管理系统在JSON数组上建立函数索引的案例值得学习ALTER TABLE articles ADD INDEX idx_tag_ids ((CAST(tag_ids-$[*] AS CHAR(32) ARRAY))), ALGORITHMINPLACE;查询时使用MEMBER OF操作符SELECT * FROM articles WHERE 1024 MEMBER OF(tag_ids-$[*]);6. 监控与持续优化索引不是一劳永逸的解决方案。某SaaS平台每月新增百万用户后原本高效的索引逐渐变成了性能瓶颈。他们建立了这样的监控体系索引使用率监控SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star 0 ORDER BY object_schema, object_name;索引冗余检测SELECT table_name, index_name, seq_in_index, column_name FROM information_schema.statistics WHERE table_schema your_db ORDER BY table_name, index_name, seq_in_index;索引碎片化检查SELECT table_name, index_name, ROUND(stat_value * innodb_page_size / 1024 / 1024, 2) AS size_mb, stat_description FROM mysql.innodb_index_stats WHERE database_name your_db AND stat_name size;这套监控方案帮助他们每月节省了30%的数据库存储空间同时查询性能提升了15-20%。记住索引优化是持续过程需要定期review和调整。
