1. 为什么MySQL索引会失效从B树说起我至今记得第一次在生产环境遇到索引失效的场景——那是一个用户订单查询接口明明在测试环境跑得飞快上线后却频繁超时。当时执行EXPLAIN看到Using filesort和Using temporary时整个人都懵了。后来花了三天时间才搞明白原来是我们团队在user_id和create_time字段上建的联合索引被一个看似无害的ORDER BY status彻底废掉了。1.1 B树索引的物理结构MySQL的InnoDB引擎采用B树作为索引数据结构这棵树的每个节点都是16KB的页page。主键索引的叶子节点存储完整行数据聚簇索引二级索引的叶子节点则存储主键值。当执行SELECT * FROM orders WHERE user_id123时先在idx_user二级索引树找到user_id123的所有主键ID再用这些ID回表Bookmark Lookup到主键索引获取完整数据这个过程中最关键的代价在于磁盘I/O次数。根据我们的压测数据在SSD存储上单次随机I/O延迟约0.1ms顺序扫描速度约500MB/s内存中比较操作仅需纳秒级1.2 索引失效的六大经典场景通过分析200生产案例我总结出这些高频踩坑点最左前缀原则违反联合索引(a,b,c)相当于同时建立了(a)、(a,b)、(a,b,c)三个索引。但以下查询用不到索引WHERE b1 AND c2 -- 缺少最左列a WHERE a1 AND c2 -- 跳过了b列隐式类型转换当字段类型为varchar而查询用数字比较时WHERE mobile13800138000 -- mobile是varchar类型实际执行的是CAST(mobile AS signed)13800138000导致全表扫描索引列参与运算WHERE YEAR(create_time)2023 -- 应该用范围查询 WHERE id1100 -- 直接改写成id99OR条件未全覆盖WHERE a1 OR b2 -- 只有单列索引时优化方案是改用UNION ALLSELECT * FROM t WHERE a1 UNION ALL SELECT * FROM t WHERE b2 AND (a!1 OR a IS NULL)LIKE左模糊匹配WHERE name LIKE %张% -- 全表扫描 WHERE name LIKE 张% -- 可以用索引统计信息过时当表数据量变化超过10%时InnoDB会重新采样统计信息。在以下情况需要手动执行ANALYZE TABLE orders;提示通过SHOW INDEX FROM table查看Cardinality值如果明显低于实际唯一值数量说明统计信息不准确。2. 执行计划深度解读从EXPLAIN到Optimizer Trace2.1 EXPLAIN输出全字段解析这是我们在千万级订单表上的真实执行计划EXPLAIN SELECT * FROM orders WHERE user_id123 AND statuspaid ORDER BY create_time DESC LIMIT 10;输出结果idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEordersrefidx_user,idx_statusidx_user8const,const325Using filesort关键字段解读type访问类型性能从优到劣system const eq_ref ref range index ALL本例ref表示用非唯一索引扫描key_len8字节 4字节(user_id int) 1字节(nullable) 3字节(status varchar(10) utf8mb4)ExtraUsing filesort需要额外排序Using temporary使用临时表Using index覆盖索引2.2 Optimizer Trace实战当EXPLAIN无法解释优化器行为时可以开启SET optimizer_traceenabledon; SELECT * FROM orders WHERE ...; SELECT * FROM information_schema.optimizer_trace;输出会包含候选索引列表及选择理由代价估算公式与参数最终决策树我们曾用此工具发现一个有趣现象当IN()列表超过200个值时优化器会错误估算代价转而选择全表扫描。3. 千万级数据下的索引重构实战3.1 案例背景某电商平台的订单表面临性能问题数据量6500万行查询QPS峰值1200平均响应时间780ms现有索引INDEX idx_user (user_id), INDEX idx_status (status), INDEX idx_ctime (create_time)3.2 索引重构方案通过慢查询日志分析我们识别出TOP 5查询模式用户订单分页查询45%SELECT * FROM orders WHERE user_id? AND status IN (paid,shipped) ORDER BY create_time DESC LIMIT 0,10物流状态批量查询30%SELECT order_id,logistics_status FROM orders WHERE order_id IN (?,?,...)新索引设计-- 替换原有三个单列索引 ALTER TABLE orders DROP INDEX idx_user, DROP INDEX idx_status, DROP INDEX idx_ctime, ADD INDEX idx_user_status_ctime (user_id, status, create_time), ADD INDEX idx_cover_order (order_id, logistics_status);3.3 效果对比指标重构前重构后提升平均响应时间780ms32ms24xCPU使用率85%23%73%↓磁盘IOPS450012003.7x↓4. 高级优化技巧与避坑指南4.1 ICP索引条件下推Index Condition Pushdown特性允许在存储引擎层过滤数据-- 5.6版本前将所有user_id123的数据取到server层再过滤status -- 5.6版本后在存储引擎层就完成status过滤 SELECT * FROM orders WHERE user_id123 AND status LIKE %pending%;可通过SET optimizer_switchindex_condition_pushdownoff对比测试。4.2 MRR多范围读取对于范围查询优化器会先收集主键值并排序再批量回表-- 启用MRR SET optimizer_switchmrron,mrr_cost_basedoff; EXPLAIN SELECT * FROM orders WHERE user_id BETWEEN 100 AND 200;在HDD磁盘上效果尤为明显我们的测试显示IOPS降低60%。4.3 隐式排序陷阱MySQL会对GROUP BY、DISTINCT隐式排序可能导致临时表-- 隐式排序 SELECT DISTINCT status FROM orders; -- 优化方案 SELECT status FROM orders GROUP BY status ORDER BY NULL;4.4 分区表索引策略对于按range分区的亿级数据表索引设计要特殊考虑CREATE TABLE big_table ( id BIGINT, create_time DATETIME, PRIMARY KEY (id, create_time) -- 分区键必须包含在主键中 ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS(2023-02-01)), PARTITION p202302 VALUES LESS THAN (TO_DAYS(2023-03-01)) );在最近的数据迁移项目中通过合理设计分区键使查询性能提升8倍。
