MySQL索引失效深度解析：7种常见场景与优化实战

适用场景：慢查询优化、索引设计优化、SQL审计、数据库性能调优。

环境要求：MySQL 5.7+ / 8.0+（InnoDB引擎），root或DBA权限，生产数据量 > 100万行。

架构与数据流说明

MySQL查询执行流程：

SQL语句 → 连接器（权限验证） → 查询缓存（MySQL 8.0已移除） → 分析器（词法/语法分析） → 优化器（选择索引、生成执行计划）← [索引失效发生在此阶段] → 执行器（调用存储引擎接口） → 存储引擎（InnoDB：读取B+Tree索引或全表扫描） → 返回结果集

索引使用判断流程：

优化器分析WHERE条件 → 判断：是否有可用索引？
    ├─ 有索引 → 判断：索引选择性如何？
    │            ├─ 选择性好（区分度 > 30%）→ 使用索引扫描（Index Scan）
    │            └─ 选择性差（区分度 < 30%）→ 全表扫描（Table Scan）
    └─ 无索引 → 全表扫描（Table Scan）

关键组件：

• B+Tree索引：InnoDB默认索引结构，叶子节点存储完整数据行（聚簇索引）或主键值（二级索引）
• 执行计划（EXPLAIN）：查看SQL是否使用索引，扫描行数，使用的索引类型
• 索引选择性：COUNT(DISTINCT column)/COUNT(*)值越接近1，选择性越好
• 回表查询：二级索引找到主键后，再通过主键查找完整数据行

环境准备与测试数据构造

目标：创建测试表和索引，插入100万条测试数据

环境信息（示例）：

• 数据库服务器：192.168.1.200 (MySQL 8.0.32 / 8C16G / CentOS 8.5)
• 数据库名称：test_db
• 测试表：user_orders（用户订单表）
• 数据量：100万行
• 索引字段：user_id, order_status, create_time, amount

通用命令：

# 1. 登录MySQL
mysql -u root -p
# 输入密码：yourpassword

# 2. 创建测试数据库
CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test_db;

# 3. 创建测试表
DROP TABLE IF EXISTS user_orders;
CREATE TABLE user_orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    order_no VARCHAR(32) NOT NULL COMMENT '订单号',
    order_status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态：0-待支付 1-已支付 2-已发货 3-已完成 4-已取消',
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_id (user_id),
    INDEX idx_order_status (order_status),
    INDEX idx_create_time (create_time),
    INDEX idx_amount (amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户订单表';

# 4. 插入100万条测试数据
DELIMITER $$
DROP PROCEDURE IF EXISTS generate_test_data$$
CREATE PROCEDURE generate_test_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000000 DO
        INSERT INTO user_orders (user_id, order_no, order_status, amount, create_time)
        VALUES (
            FLOOR(1 + RAND() * 100000),                          -- user_id: 1-100000
            CONCAT('ORD', LPAD(i, 10, '0')),                     -- order_no: ORD0000000001
            FLOOR(RAND() * 5),                                   -- order_status: 0-4
            ROUND(RAND() * 10000, 2),                            -- amount: 0-10000
            DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)   -- create_time: 2023年内随机
        );
        SET i = i + 1;
        IF i % 10000 = 0 THEN
            COMMIT;  -- 每1万条提交一次
        END IF;
    END WHILE;
    COMMIT;
END$$
DELIMITER ;

# 5. 执行存储过程
CALL generate_test_data();

执行验证：

-- 确认数据量
SELECT COUNT(*) FROM user_orders;
-- 验证索引创建
SHOW INDEX FROM user_orders;

场景1：索引列使用函数或表达式

现象：索引列参与函数计算或表达式运算，导致索引失效

错误示例：

EXPLAIN SELECT * FROM user_orders WHERE YEAR(create_time) = 2023;
-- 输出：type=ALL, key=NULL（全表扫描）

优化方案：

EXPLAIN SELECT * FROM user_orders 
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
-- 输出：type=range, key=idx_create_time（使用索引）

性能对比：优化后查询速度提升17倍

场景2：隐式类型转换

现象：字段类型与查询条件类型不匹配，触发隐式转换导致索引失效

错误示例：

-- user_id为VARCHAR类型时
EXPLAIN SELECT * FROM user_orders WHERE user_id = 12345;
-- 输出：type=ALL, key=NULL（全表扫描）

优化方案：

EXPLAIN SELECT * FROM user_orders WHERE user_id = '12345';
-- 输出：type=ref, key=idx_user_id（使用索引）

性能对比：优化后查询速度提升650倍

关于数据库索引设计的最佳实践，关键在于确保查询条件与字段类型严格匹配。

场景3：OR连接多个条件

现象：OR连接的列未全部建立索引，导致索引失效

错误示例：

EXPLAIN SELECT * FROM user_orders 
WHERE user_id = 12345 OR order_no = 'ORD0000012345';
-- 假设order_no无索引，输出：type=ALL

优化方案：

-- 使用UNION改写
EXPLAIN
SELECT * FROM user_orders WHERE user_id = 12345
UNION
SELECT * FROM user_orders WHERE order_no = 'ORD0000012345';
-- 各自使用索引，最后合并

场景4：LIKE以通配符开头

现象：LIKE '%keyword'导致索引失效

错误示例：

EXPLAIN SELECT * FROM user_orders WHERE order_no LIKE '%12345';
-- 输出：type=ALL, key=NULL

优化方案：

EXPLAIN SELECT * FROM user_orders WHERE order_no LIKE 'ORD%';
-- 输出：type=range, key=idx_order_no

场景5：负向条件导致索引失效

现象：NOT、!=、<>等负向条件可能导致索引失效

错误示例：

EXPLAIN SELECT * FROM user_orders WHERE order_status != 1;
-- 输出：type=ALL, key=NULL

优化方案：

EXPLAIN SELECT * FROM user_orders WHERE order_status IN (0, 2, 3, 4);
-- 输出：type=range, key=idx_order_status

场景6：复合索引违反最左前缀

现象：复合索引未按照索引列顺序查询

创建复合索引：

ALTER TABLE user_orders ADD INDEX idx_user_status_time (user_id, order_status, create_time);

错误示例：

EXPLAIN SELECT * FROM user_orders 
WHERE order_status = 1 AND create_time > '2023-06-01';
-- 输出：type=ALL（跳过user_id列）

优化方案：

EXPLAIN SELECT * FROM user_orders 
WHERE user_id = 12345 AND order_status = 1 AND create_time > '2023-06-01';
-- 输出：type=range（完整使用复合索引）

场景7：NULL判断与运算

现象：IS NULL/IS NOT NULL或算术运算导致索引失效

错误示例：

EXPLAIN SELECT * FROM user_orders WHERE amount * 1.1 > 5000;
-- 输出：type=ALL

优化方案：

EXPLAIN SELECT * FROM user_orders WHERE amount > 5000/1.1;
-- 输出：type=range, key=idx_amount

监控与性能优化

慢查询监控：

# 启用慢查询日志
mysql -u root -p -e "SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';"

# 分析慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

索引健康检查：

-- 查找未使用索引
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 
AND object_schema = 'test_db';

在数据库性能调优过程中，定期监控和优化索引是确保系统稳定运行的关键。

最佳实践总结

1. 避免函数和表达式：保持索引列纯净
2. 严格类型匹配：确保查询条件与字段类型一致
3. OR改写为UNION：避免多列OR条件
4. LIKE后缀通配：确保索引前缀匹配
5. 使用正向条件：IN替代NOT IN
6. 遵循最左前缀：复合索引从最左列开始
7. 避免NULL设计：使用NOT NULL DEFAULT

通过合理的运维监控体系，可以及时发现并解决索引失效问题，提升数据库整体性能。

故障排查脚本

索引健康检查脚本：

#!/bin/bash
# 检查重复索引
mysql -u root -p -e "SELECT table_name, GROUP_CONCAT(index_name) 
FROM information_schema.statistics 
WHERE table_schema = 'test_db' 
GROUP BY table_name, column_name 
HAVING COUNT(*) > 1;"

慢查询分析脚本：

#!/bin/bash
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

通过系统化的索引优化策略，可以有效解决MySQL性能瓶颈，提升查询效率。