MySQL 8.4 索引实战：3步排查慢查询，从全表扫描到索引覆盖

MySQL 8.4 索引实战3步排查慢查询从全表扫描到索引覆盖当线上数据库突然出现性能瓶颈时开发团队常常陷入被动应对的困境。上周我们遇到一个典型案例某核心业务接口响应时间从200ms骤增至8秒最终定位到是一条未合理使用索引的SQL语句。本文将分享一套经过实战检验的索引优化方法论通过三个关键步骤快速定位和解决慢查询问题。1. 慢查询识别与问题定位在开始优化之前我们需要准确识别问题SQL。MySQL提供了多种性能分析工具但最直接有效的是开启慢查询日志。以下是在MySQL 8.4中的配置方法-- 临时开启慢查询日志(重启失效) SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的查询记录 SET GLOBAL log_queries_not_using_indexes ON; -- 记录未使用索引的查询 -- 永久生效配置(my.cnf) [mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1假设我们通过日志发现以下问题SQLSELECT user_id, order_no, amount FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-06-30 AND status PAID ORDER BY amount DESC LIMIT 1000;这个查询在百万级数据表中执行需要6.8秒明显不符合业务预期。接下来我们需要分析其执行计划EXPLAIN FORMATJSON SELECT user_id, order_no, amount FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-06-30 AND status PAID ORDER BY amount DESC LIMIT 1000;执行计划关键指标解读指标当前值理想值问题诊断typeALLrange全表扫描rows985,6321000扫描行数过多ExtraUsing filesortUsing index需要额外排序2. 索引设计与优化策略针对已识别的性能瓶颈我们需要设计合适的索引方案。MySQL 8.4引入了多项索引优化特性以下是针对该场景的索引设计过程2.1 现有索引分析首先检查表的现有索引结构SHOW INDEX FROM orders;假设当前索引情况如下Key_nameColumn_nameIndex_typeCardinalityPRIMARYidBTREE1,200,000idx_useruser_idBTREE850,000显然缺少对create_time和status字段的索引支持。2.2 复合索引设计原则根据查询条件我们需要创建包含create_time和status的复合索引。在MySQL 8.4中索引列顺序应遵循以下原则高区分度优先将区分度高的列放在前面范围查询靠后范围查询条件应放在索引最后覆盖索引原则尽量让索引包含所有查询字段计算字段区分度SELECT COUNT(DISTINCT status)/COUNT(*) AS status_selectivity, COUNT(DISTINCT create_time)/COUNT(*) AS time_selectivity FROM orders;结果显示create_time区分度(0.92)高于status(0.15)因此索引顺序应为(status, create_time)。2.3 创建优化索引ALTER TABLE orders ADD INDEX idx_status_time_amount(status, create_time, amount);这个索引设计考虑到了等值条件status放在最前范围条件create_time放在中间排序字段amount包含在索引中实现覆盖3. 优化效果验证与调优创建索引后我们需要验证优化效果并进一步调优。3.1 执行计划对比优化前的执行计划{ query_block: { cost_info: { query_cost: 9862.45 }, table: { access_type: ALL, rows_examined_per_scan: 985632, rows_produced_per_join: 32854, filtered: 3.33, cost_info: { read_cost: 9532.65, eval_cost: 329.80, prefix_cost: 9862.45, data_read_per_join: 4M }, used_columns: [...], attached_condition: ... } } }优化后的执行计划{ query_block: { cost_info: { query_cost: 356.82 }, table: { access_type: range, possible_keys: [idx_status_time_amount], key: idx_status_time_amount, used_key_parts: [status,create_time,amount], rows_examined_per_scan: 2876, rows_produced_per_join: 1000, filtered: 100.00, using_index: true, cost_info: { read_cost: 256.82, eval_cost: 100.00, prefix_cost: 356.82, data_read_per_join: 240K } } } }关键指标对比指标优化前优化后提升幅度查询成本9862.45356.8227.6倍扫描行数985,6322,876342倍数据读取量4MB240KB17倍3.2 实际性能测试使用MySQL自带的压力测试工具验证-- 测试查询性能 BENCHMARK(1000, ( SELECT user_id, order_no, amount FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-06-30 AND status PAID ORDER BY amount DESC LIMIT 1000 ));测试结果对比场景平均耗时QPS无索引6.8s0.15有索引0.12s83.33.3 索引优化决策树针对不同查询场景的索引选择策略开始 │ ├── 是否等值查询? → 是 → 将等值条件列放在索引最前 │ │ │ ├── 是否有范围查询? → 是 → 范围列放在等值列后 │ │ │ │ │ ├── 是否需要排序? → 是 → 排序字段加入索引 │ │ │ │ │ └── 是否覆盖查询? → 是 → 包含所有select字段 │ │ │ └── 是否多列等值? → 是 → 按区分度从高到低排列 │ └── 是否纯排序查询? → 是 → 创建(排序字段)索引4. 高级优化技巧与注意事项4.1 索引跳跃扫描(MySQL 8.0)当复合索引前导列区分度很低时可以利用跳跃扫描特性-- 前导列gender只有M/F两种值 ALTER TABLE users ADD INDEX idx_gender_age(gender, age); -- 即使不指定gender也能使用索引 SELECT * FROM users WHERE age 30;4.2 降序索引优化对于大量DESC排序的场景MySQL 8.0支持降序索引ALTER TABLE orders ADD INDEX idx_time_desc(create_time DESC, amount);4.3 索引维护建议定期检查索引使用情况-- 查找冗余索引 SELECT s.table_name, s.index_name, GROUP_CONCAT(s.column_name ORDER BY s.seq_in_index) AS index_columns, COUNT(*) AS col_count FROM information_schema.statistics s JOIN information_schema.tables t ON s.table_schema t.table_schema AND s.table_name t.table_name WHERE s.table_schema DATABASE() GROUP BY s.table_name, s.index_name HAVING COUNT(*) 1 ORDER BY s.table_name, s.index_name;4.4 索引使用陷阱需要注意的常见问题隐式类型转换WHERE status 1 (status是varchar类型)函数操作索引列WHERE DATE(create_time) 2024-01-01前导模糊查询WHERE order_no LIKE %123OR条件不当使用WHERE status PAID OR amount 1000在实际项目中我们曾遇到一个典型案例某核心表创建了十余个单列索引导致写入性能下降80%。通过合并为合适的复合索引不仅解决了查询性能问题还将写入耗时从1200ms降低到300ms。