1. MySQL索引失效现象解析为什么你的查询不走索引第一次遇到索引失效问题时我正处理一个用户订单查询接口的性能问题。明明在order_id字段上建立了BTREE索引EXPLAIN结果却显示全表扫描。当时百思不得其解——这完全违背了我对索引工作原理的认知。经过反复排查最终发现是隐式类型转换导致的索引失效。这个经历让我意识到索引失效远比想象中常见。索引失效的本质是优化器认为全表扫描比使用索引更高效。当出现以下情况时MySQL可能放弃使用索引预估扫描行数超过表记录的30%经验值需要回表的记录数过多索引统计信息不准确存在更优的访问路径重要提示索引失效不一定是坏事优化器会根据成本模型选择最优执行计划。我们需要区分合理的失效和意外的失效。2. 索引失效12大场景深度剖析2.1 隐式类型转换陷阱最常见却最易被忽视的问题。当查询条件的数据类型与列定义不匹配时MySQL会进行隐式转换-- user_id是varchar类型但用数字查询 SELECT * FROM users WHERE user_id 10086;这个查询会导致对user_id列所有值执行CAST(AS SIGNED)转换比较转换后的值与10086索引因函数操作完全失效解决方案保持查询条件与列类型严格一致使用EXPLAIN检查type列是否出现ALL对JSON字段使用-操作符避免隐式转换2.2 最左前缀原则违反联合索引(a,b,c)相当于创建了三个索引(a)(a,b)(a,b,c)以下查询无法充分利用索引-- 缺失最左列a SELECT * FROM table WHERE b 1 AND c 2; -- 跳过了中间列b SELECT * FROM table WHERE a 1 AND c 2;实战技巧使用ALTER TABLE ADD INDEX idx_a_c(a,c)创建跳跃扫描索引MySQL 8.0支持Index Skip Scan但性能可能不如专用索引2.3 索引列参与运算在索引列上使用函数或运算会导致失效-- 错误示例 SELECT * FROM orders WHERE YEAR(create_time) 2023; SELECT * FROM products WHERE price 10 100;优化方案-- 正确写法 SELECT * FROM orders WHERE create_time BETWEEN 2023-01-01 AND 2023-12-31; -- 对计算条件重构 SELECT * FROM products WHERE price 90;2.4 OR条件使用不当单独的OR条件会导致索引失效-- 全表扫描 SELECT * FROM users WHERE age 18 OR name 张三;解决方案改用UNION ALLSELECT * FROM users WHERE age 18 UNION ALL SELECT * FROM users WHERE name 张三 AND age ! 18;对OR条件分别创建索引2.5 范围查询阻断索引范围查询会阻断后续索引列的使用-- 只能用到idx(a,b)c失效 SELECT * FROM table WHERE a 1 AND b 10 AND c 2;优化策略调整索引顺序为(a,c,b)使用IN代替范围查询SELECT * FROM table WHERE a 1 AND c 2 AND b IN (11,12,13...);2.6 使用NOT、!、操作符否定条件通常无法使用索引-- 全表扫描 SELECT * FROM products WHERE status ! active;替代方案-- 改为正向查询 SELECT * FROM products WHERE status IN (pending,deleted);2.7 LIKE通配符前置前导通配符导致索引失效-- 无法使用索引 SELECT * FROM articles WHERE title LIKE %优化;解决方案使用全文索引(FULLTEXT)考虑使用Elasticsearch等专业搜索引擎不得已时可使用反转存储技巧-- 存储时反转字符串 UPDATE articles SET title_reverse REVERSE(title); -- 查询时 SELECT * FROM articles WHERE title_reverse LIKE 化优%;2.8 索引选择性过低当索引列不同值很少时优化器可能放弃索引-- 性别列只有M,F两个值 SELECT * FROM users WHERE gender M;处理方案添加更细粒度的条件如时间范围使用复合索引提高选择性考虑使用位图索引如通过tags字段2.9 索引统计信息过时执行计划依赖的统计信息可能不准确-- 更新统计信息 ANALYZE TABLE users;最佳实践大表更新后手动执行ANALYZE TABLEMySQL 8.0支持直方图统计监控information_schema.STATISTICS中的CARDINALITY2.10 使用IS NULL条件-- 可能无法使用索引 SELECT * FROM customers WHERE phone IS NULL;优化方法设置列为NOT NULL DEFAULT 使用特殊值代替NULLMySQL 8.0支持索引NULL值2.11 多表JOIN顺序不当关联查询时驱动表选择错误-- 小表应作为驱动表 SELECT * FROM large_table l JOIN small_table s ON l.id s.id;优化技巧使用STRAIGHT_JOIN强制连接顺序调整JOIN顺序让过滤性强的表先执行使用EXPLAIN FORMATJSON分析连接成本2.12 索引合并策略失效当优化器错误选择index_merge时-- 可能比全表扫描更慢 SELECT * FROM table WHERE a 1 OR b 2;应对措施使用optimizer_switch关闭index_mergeSET optimizer_switchindex_mergeoff;创建更合适的复合索引3. 索引诊断与优化实战3.1 诊断工具三件套EXPLAIN详解type列从优到差依次为system const eq_ref ref range index ALLExtra列重点关注Using index # 覆盖索引 Using filesort # 需要额外排序 Using temporary # 使用临时表性能Schema-- 查看索引使用情况 SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;慢查询日志# my.cnf配置 slow_query_log ON long_query_time 1 log_queries_not_using_indexes ON3.2 索引优化黄金法则三星索引原则一星WHERE条件匹配索引最左前缀二星ORDER BY/GROUP BY使用索引排序三星SELECT列被索引覆盖索引设计 checklist为WHERE、JOIN、ORDER BY字段建索引选择性高的列在前避免过度索引每个索引增加写开销定期使用pt-index-usage工具审计索引维护策略-- 重建索引InnoDB ALTER TABLE orders ENGINEInnoDB; -- 在线DDLMySQL 5.6 ALTER TABLE orders ALGORITHMINPLACE, LOCKNONE, ADD INDEX idx_new(col);4. 高级场景与疑难解答4.1 函数索引解决方案MySQL 8.0支持函数索引-- 创建函数索引 ALTER TABLE orders ADD INDEX idx_ym((DATE_FORMAT(create_time,%Y-%m))); -- 查询使用 SELECT * FROM orders WHERE DATE_FORMAT(create_time,%Y-%m) 2023-01;4.2 不可见索引与降级索引不可见索引测试索引影响-- 设置为不可见 ALTER TABLE orders ALTER INDEX idx_name INVISIBLE; -- 优化器忽略但保持更新降级索引MySQL 8.0-- 将B-Tree索引转为倒排索引 ALTER TABLE products ADD INDEX idx_desc(description) USING INVISIBLE;4.3 分区表索引策略分区表的索引分为全局索引整个分区表本地索引每个分区单独最佳实践-- 创建分区表时定义索引 CREATE TABLE sales ( id INT, sale_date DATE, amount DECIMAL(10,2), INDEX idx_date (sale_date) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022) );4.4 索引跳跃扫描优化MySQL 8.0的新特性-- 即使没有最左列也能使用索引 SELECT * FROM employees WHERE last_name Smith; -- 可能使用INDEX SKIP SCAN限制条件前导列的不同值较少如性别查询需要过滤性强的后续列5. 真实案例复盘5.1 电商平台订单查询优化问题现象订单查询页响应时间从200ms突增到2s查询条件WHERE user_id? AND status IN (1,2,3) ORDER BY create_time DESC排查过程EXPLAIN显示使用了idx_user单列索引而非idx_user_status复合索引发现status字段新增了多个枚举值索引选择性从0.3降到0.1解决方案-- 创建更合适的索引 ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time); -- 强制指定索引 SELECT * FROM orders FORCE INDEX(idx_user_status_time) WHERE user_id123 AND status IN (1,2,3) ORDER BY create_time DESC;5.2 社交平台Feed流性能问题场景特点千万级数据表查询WHERE user_id IN (...) AND create_time ?原有索引(user_id, create_time)优化方案使用延迟关联SELECT t.* FROM feed t JOIN ( SELECT id FROM feed WHERE user_id IN (1,2,3) AND create_time 2023-01-01 ORDER BY create_time DESC LIMIT 1000 ) tmp ON t.id tmp.id;添加(create_time, user_id)倒排索引效果查询时间从1200ms降至80msIO消耗减少90%6. 索引监控与维护体系6.1 监控指标清单指标名称监控方式健康阈值索引使用频率performance_schema50%查询使用索引大小增长information_schema.TABLES每周增长5%索引碎片率SHOW TABLE STATUS20%索引查询效率慢查询日志扫描行数1万6.2 自动化维护脚本#!/bin/bash # 自动索引维护脚本 # 1. 分析索引使用情况 mysql -e SELECT * FROM sys.schema_unused_indexes # 2. 重建碎片化索引 mysql -e SELECT CONCAT(ALTER TABLE ,TABLE_SCHEMA,.,TABLE_NAME, ENGINEInnoDB;) FROM information_schema.TABLES WHERE DATA_FREE 100*1024*1024 | mysql # 3. 更新统计信息 mysql -e ANALYZE TABLE ${TABLES}6.3 索引生命周期管理设计阶段使用pt-index-usage预测索引使用进行EXPLAIN验证上线阶段先设置为INVISIBLE测试灰度发布观察性能运行阶段每月审计索引使用率删除冗余索引下线阶段设置INVISIBLE观察两周确认无影响后删除
