一、索引失效的底层原理
索引本质上是通过B+树结构实现的快速定位机制。当查询条件无法有效利用B+树的排序特征时，MySQL优化器会放弃使用索引。此时引擎需要执行全表扫描（Full Table Scan），查询效率会呈指数级下降。例如在500万数据表中，索引查询可能仅需0.01秒，而全表扫描可能需要超过10秒。
二、索引失效的典型场景及解决方案
（以下示例均基于MySQL 8.0版本）
1. 违反最左前缀原则
-- 创建复合索引
ALTER TABLE user ADD INDEX idx_name_age (name, age);
-- 失效场景：跳过name字段查询age
SELECT * FROM user WHERE age = 25;
-- 有效查询：保持最左字段存在
SELECT * FROM user WHERE name = '张三' AND age = 25;
-- 优化方案：调整查询条件顺序或创建单列索引
ALTER TABLE user ADD INDEX idx_age (age);
2. 隐式类型转换
-- 创建索引
ALTER TABLE product ADD INDEX idx_code (product_code);
-- 失效场景：数字类型字段使用字符串查询（product_code为INT类型）
SELECT * FROM product WHERE product_code = '10086';
-- 有效查询：保持类型一致
SELECT * FROM product WHERE product_code = 10086;
-- 特殊案例：datetime字段
SELECT * FROM orders WHERE create_time = '2023-08-01'; -- 有效
SELECT * FROM orders WHERE create_time = '2023-8-1'; -- 可能失效
3. 索引列参与运算
-- 创建索引
ALTER TABLE employee ADD INDEX idx_salary (salary);
-- 失效场景：对索引字段进行计算
SELECT * FROM employee WHERE salary * 1.1 > 10000;
-- 优化方案：重构查询条件
SELECT * FROM employee WHERE salary > 10000 / 1.1;
-- 时间运算案例
SELECT * FROM logs WHERE DATE(create_time) = '2023-08-01'; -- 失效
SELECT * FROM logs WHERE create_time BETWEEN '2023-08-01 00:00:00' AND '2023-08-01 23:59:59'; -- 有效
4. 使用函数操作
-- 创建索引
ALTER TABLE article ADD INDEX idx_title (title);
-- 失效场景：使用字符串函数
SELECT * FROM article WHERE LEFT(title, 3) = 'MySQL';
-- 优化方案：使用前缀索引
ALTER TABLE article ADD INDEX idx_title_prefix (title(3));
SELECT * FROM article WHERE title LIKE 'MySQL%';
5. 不同字符集转换
-- 创建表时指定字符集
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(50) CHARACTER SET utf8mb4,
company VARCHAR(50) CHARACTER SET latin1
) CHARSET=utf8mb4;
-- 创建索引
ALTER TABLE user ADD INDEX idx_company (company);
-- 失效场景：不同字符集比较
SELECT * FROM user WHERE name = company;
-- 解决方案：统一字符集
ALTER TABLE user MODIFY company VARCHAR(50) CHARACTER SET utf8mb4;
6. OR连接非索引列
-- 创建单列索引
ALTER TABLE orders ADD INDEX idx_status (status);
-- 失效场景：OR连接非索引列
SELECT * FROM orders WHERE status = 1 OR total_amount > 1000;
-- 优化方案：拆分查询或创建复合索引
SELECT * FROM orders WHERE status = 1
UNION
SELECT * FROM orders WHERE total_amount > 1000;
-- 或创建覆盖索引
ALTER TABLE orders ADD INDEX idx_status_amount (status, total_amount);
7. 索引选择性过低
-- 查看索引选择性
SELECT
COUNT(DISTINCT gender)/COUNT(*) AS selectivity
FROM user;
-- 当选择性低于30%时，优化器可能放弃索引
SELECT * FROM user WHERE gender = 'male';
-- 解决方案：创建复合索引
ALTER TABLE user ADD INDEX idx_gender_age (gender, age);
8. 联合索引顺序错位
-- 创建复合索引
ALTER TABLE sales ADD INDEX idx_region_year (region, year);
-- 失效场景：错误的使用顺序
SELECT * FROM sales WHERE year = 2023 AND region = '华东';
-- 有效查询：保持索引字段顺序
SELECT * FROM sales WHERE region = '华东' AND year = 2023;
-- 执行计划验证
EXPLAIN SELECT * FROM sales WHERE year = 2023 AND region = '华东';
9. 范围查询阻断后续索引
-- 创建复合索引
ALTER TABLE products ADD INDEX idx_price_stock (price, stock);
-- 失效场景：范围查询阻断后续字段
SELECT * FROM products WHERE price > 100 AND stock = 50;
-- 优化方案：调整索引顺序
ALTER TABLE products ADD INDEX idx_stock_price (stock, price);
10. 索引覆盖不足
-- 创建单列索引
ALTER TABLE articles ADD INDEX idx_category (category);
-- 需要回表查询
SELECT title, content FROM articles WHERE category = '技术';
-- 优化方案：创建覆盖索引
ALTER TABLE articles ADD INDEX idx_category_title (category, title);
11. 索引统计信息过时
-- 手动更新统计信息
ANALYZE TABLE user;
-- 查看索引统计
SHOW INDEX FROM user;
-- 自动更新配置（MySQL 8.0默认开启）
SET GLOBAL innodb_stats_auto_recalc = 1;
12. 索引碎片化严重
-- 查看索引碎片率
SELECT
TABLE_NAME,
INDEX_NAME,
ROUND(data_free/(data_length+index_length)*100,2) AS frag_ratio
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db';
-- 优化索引碎片
ALTER TABLE orders ENGINE=InnoDB;
OPTIMIZE TABLE orders;
三、关联技术：执行计划解析
EXPLAIN SELECT * FROM user WHERE name LIKE '张%' AND age > 25;
-- 关键字段解读：
1. type：const > eq_ref > ref > range > index > ALL
2. key：实际使用的索引
3. rows：预估扫描行数
4. Extra：Using index（覆盖索引）、Using where（回表查询）
四、应用场景与技术选型
应用场景：
- 电商系统的商品搜索
- 金融系统的交易记录查询
- 日志分析系统的数据筛选
- 用户管理系统的分页查询
技术优缺点：
- 优点：查询速度提升100-1000倍，降低服务器负载
- 缺点：增加写操作开销，占用额外存储空间
注意事项：
- 定期使用
SHOW INDEX检查索引状态 - 避免在更新频繁的字段创建过多索引
- 使用
FORCE INDEX需谨慎，可能影响版本兼容性 - 注意NULL值对索引选择性的影响
五、总结与最佳实践
- 遵循"三星索引"原则：等值条件在前，范围查询在后，覆盖查询字段
- 使用
EXPLAIN验证执行计划 - 定期进行索引健康检查
- 平衡读写性能，避免过度索引
- 结合业务场景设计针对性索引