1. MySQL索引失效的典型场景剖析
作为数据库性能优化的核心手段，索引的正确使用直接影响查询效率。但在实际工作中，我们经常会遇到"明明加了索引却还是慢"的诡异现象。根据我处理过的数百个生产案例，以下五种场景最为常见且最具迷惑性：
1.1 隐式类型转换导致的索引失效
当查询条件的数据类型与索引字段定义类型不一致时，MySQL会进行隐式类型转换，导致索引失效。例如定义user_id为varchar类型却用数字查询：
-- 表结构 CREATE TABLE orders ( id INT PRIMARY KEY, user_id VARCHAR(20), INDEX idx_user_id (user_id) ); -- 错误查询（索引失效） SELECT * FROM orders WHERE user_id = 10086; -- 正确查询（使用索引） SELECT * FROM orders WHERE user_id = '10086';注意：所有字符类型的字段在条件中必须用引号包裹，特别是手机号、身份证号等数字形式的字符串。
1.2 函数操作导致的索引失效
对索引字段使用函数会使优化器无法使用索引。常见场景包括日期处理、字符串截取等：
-- 表结构 CREATE TABLE logs ( id INT PRIMARY KEY, create_time DATETIME, INDEX idx_create_time (create_time) ); -- 错误查询（索引失效） SELECT * FROM logs WHERE DATE(create_time) = '2023-01-01'; -- 正确查询（使用索引） SELECT * FROM logs WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2023-01-02 00:00:00';1.3 前导模糊查询问题
LIKE查询以通配符开头会导致索引失效，这是B+树索引结构的固有特性：
-- 表结构 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), INDEX idx_name (name) ); -- 错误查询（索引失效） SELECT * FROM products WHERE name LIKE '%手机%'; -- 优化方案1：使用全文索引 ALTER TABLE products ADD FULLTEXT INDEX ft_name (name); SELECT * FROM products WHERE MATCH(name) AGAINST('手机'); -- 优化方案2：使用覆盖索引+后置模糊 SELECT id FROM products WHERE name LIKE '小米%';1.4 不符合最左前缀原则
联合索引必须遵循最左前缀匹配原则，否则会出现索引断点：
-- 表结构 CREATE TABLE employees ( id INT PRIMARY KEY, dept_id INT, position VARCHAR(50), salary DECIMAL(10,2), INDEX idx_dept_position (dept_id, position) ); -- 有效使用索引的查询 SELECT * FROM employees WHERE dept_id = 3 AND position = '工程师'; SELECT * FROM employees WHERE dept_id = 3; -- 索引失效的查询 SELECT * FROM employees WHERE position = '工程师';1.5 OR条件使用不当
OR条件可能导致索引失效，特别是当OR两边的条件字段不同时：
-- 表结构 CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50), phone VARCHAR(20), INDEX idx_username (username), INDEX idx_phone (phone) ); -- 索引失效的查询 SELECT * FROM users WHERE username = 'admin' OR phone = '13800138000'; -- 优化方案：使用UNION ALL SELECT * FROM users WHERE username = 'admin' UNION ALL SELECT * FROM users WHERE phone = '13800138000' AND username != 'admin';2. 索引失效的诊断方法论
2.1 EXPLAIN命令深度解读
EXPLAIN是诊断索引问题的瑞士军刀，关键字段解析：
| 字段 | 含义 | 理想值 |
|---|---|---|
| type | 访问类型 | const/eq_ref/ref/range |
| key | 实际使用的索引 | 显示索引名称 |
| rows | 预估扫描行数 | 与实际数据量正相关 |
| Extra | 额外信息 | Using index(覆盖索引) |
典型问题模式：
type=ALL：全表扫描key=NULL：未使用索引Extra=Using filesort：需要额外排序
2.2 性能模式监控
MySQL 5.7+的性能模式提供更细粒度的监控：
-- 开启性能监控 UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements%'; -- 查看慢查询统计 SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;2.3 索引使用统计
通过sys库查看索引使用情况：
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_database';3. 高级优化策略
3.1 索引跳跃扫描(MySQL 8.0+)
MySQL 8.0引入的Index Skip Scan特性可以突破最左前缀限制：
-- 表结构 CREATE TABLE orders ( id INT PRIMARY KEY, gender ENUM('M','F'), register_date DATE, INDEX idx_gender_date (gender, register_date) ); -- MySQL 8.0+可以部分使用索引 SELECT * FROM orders WHERE register_date > '2023-01-01';3.2 降序索引优化
MySQL 8.0支持真正的降序索引，优化ORDER BY ... DESC场景：
-- 传统索引 CREATE INDEX idx_score ON students(score); -- 降序索引(MySQL 8.0+) CREATE INDEX idx_score_desc ON students(score DESC); -- 查询优化 SELECT * FROM students ORDER BY score DESC LIMIT 100;3.3 函数索引(MySQL 8.0+)
通过函数索引解决计算字段的查询问题：
-- 创建函数索引 CREATE INDEX idx_name_lower ON employees((LOWER(name))); -- 使用函数索引查询 SELECT * FROM employees WHERE LOWER(name) = 'john';4. 生产环境实战案例
4.1 电商订单查询优化
原始查询（执行时间2.8s）：
SELECT * FROM orders WHERE DATE_FORMAT(create_time,'%Y-%m') = '2023-01' ORDER BY amount DESC;优化方案：
- 添加计算列和函数索引
- 使用覆盖索引减少回表
-- 添加计算列 ALTER TABLE orders ADD COLUMN create_month VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(create_time,'%Y-%m')) STORED; -- 创建复合索引 CREATE INDEX idx_month_amount ON orders(create_month, amount DESC, id); -- 优化后查询（执行时间0.02s） SELECT id, user_id, amount FROM orders FORCE INDEX(idx_month_amount) WHERE create_month = '2023-01' ORDER BY amount DESC;4.2 社交平台Feed流优化
原始分页查询（随着offset增大性能急剧下降）：
SELECT * FROM posts WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10 OFFSET 10000;优化方案：使用游标分页
-- 第一页 SELECT * FROM posts WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10; -- 后续页（假设上一页最后一条create_time为'2023-01-01 12:00:00'） SELECT * FROM posts WHERE user_id = 123 AND create_time < '2023-01-01 12:00:00' ORDER BY create_time DESC LIMIT 10;5. 索引维护与管理
5.1 索引碎片整理
定期检查并优化索引碎片：
-- 查看碎片率 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'; -- 优化表 ALTER TABLE your_table ENGINE=InnoDB;5.2 索引使用监控
建立索引使用监控机制：
-- 创建监控表 CREATE TABLE index_usage_monitor ( id INT AUTO_INCREMENT PRIMARY KEY, table_name VARCHAR(64), index_name VARCHAR(64), select_count BIGINT DEFAULT 0, last_updated TIMESTAMP ); -- 定期更新统计 INSERT INTO index_usage_monitor (table_name, index_name, select_count) SELECT object_schema, object_name, count_read FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL ON DUPLICATE KEY UPDATE select_count = VALUES(select_count), last_updated = CURRENT_TIMESTAMP;5.3 索引生命周期管理
制定索引管理规范：
- 新索引上线前必须通过EXPLAIN验证
- 设置3个月观察期，收集使用数据
- 建立季度评审机制，清理无用索引
- 重大业务变更时重新评估索引策略
经验法则：单表索引数量不超过5个，联合索引字段不超过3个。超过这个阈值就需要考虑业务拆分或架构调整。
