一文揭秘MySQL索引失效原因与优化方案

目录

• 一、索引：数据库世界的超级目录
• 二、索引罢工的五大罪状（失效场景）
• 1.最左匹配原则
• 2.隐式转换
• 3.函数计算
• 4.范围查询阻断连锁反应
• 5.OR引发的危机
• 三、原理深潜：B+树为何罢工
• 四、避坑指南：四大生存法则
• 五、最佳实践：索引优化军规
• 六、面试考点区
• 七、终极总结：与索引和平共处原则

索引小剧场：某日，程序员小明发现SQL查询突然从0.1秒暴增到5秒。索引委屈巴巴：“主人，不是我不干活，是你老给我穿小鞋啊！”

一、索引：数据库世界的超级目录

索引如同图书馆的图书目录：

• 聚簇索引：书架按编号排序（数据即索引）
• 二级索引：独立目录卡片（需回表查询）
• B+树结构：多叉平衡树，3层可存2000万数据（假设每页16KB）

// Java中创建索引示例（Spring Data JPA）
@Entity
@Table(indexes = @Index(columnList = "username,email", name = "idx_user_identity"))
public class User {
    @Id
    private Long id;
    private String username; // 索引列
    private String email;    // 索引列
    private Integer age;
    // Getter/Setter省略
}

二、索引罢工的五大罪状（失效场景）

1.最左匹配原则

-- 创建联合索引
CREATE INDEX idx_soldier ON army(squad, team, soldier);

-- 有效查询 ✅
SELECT * FROM army WHERE squad = 'A'; 
SELECT * FROM army WHERE squad = 'A' AND team = 2;

-- 索引罢工 ❌
SELECT * FROM army WHERE team = 2;        -- 跳过squad
SELECT * FROM army WHERE soldier = 'Tom'; -- 跳过头两列

原理：联合索引如电话簿，必须先按省→市→姓名查找，跳级查询无效

2.隐式转换

// Java代码中常见的类型错误
@Query("SELECT u FROM User u WHERE u.username = :name") // username是varchar
User findByUsername(@Param("name") Integer name); // 传入Integer类型！

执行SQL：

SELECT * FROM user WHERE username = 100; 
-- 类型转换导致：username列索引失效！

原理：mysql被迫对索引列做类型转换（CAST），如同要求目录同时支持字android母和数字排序

3.函数计算

-- 生日字段有索引
SELECT * FROM user WHERE YEAR(birthday) = 1990;  -- 索引失效 ❌

-- 优化方案 ✅
SELECT * FROM user 
WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31';

血泪案例：某电商平台因DATE(create_time)查询导致CPU飙升90%

4.范围查询阻断连锁反应

CREATE INDEX idx_sales ON orders(region, amount, product);

-- 索引仅用到 region 和 amount ❌
SELECT * FROM orders 
WHERE region = 'East' 
  AND amount > 1000 
  AND product = 'Phone';

破解方案：调整索引顺序为(region, product, amount)

5.OR引发的危机

-- 即使name和age都有独立索引
SELECT * FROM user WHERE name = 'John' OR age = 3php0;
-- MySQL通常选择全表扫描！

优化方案：改用UNION

SELECT * FROM user WHERE name = 'John'
UNION ALL
SELECT * FROM user WHERE age = 30;

三、原理深潜：B+树为何罢工

当发生索引失效时：

• 优化器计算使用索引的成本
• 若预计android扫描超过30%数据（默认阈值）
• 选择全表扫描作为“更优方案”

冷知识：FORCE INDEX可强制使用索引，但如同用枪逼工人干活，慎用！

四、避坑指南：四大生存法则

前缀索引策略

ALTER TABLE article ADD INDEX idx_title(title(10));

对长文本取前N个字符（需保证区分度>90%）

覆盖索引护盾

-- 建立覆盖索引
CREATE INDEX idx_covering ON orders(user_id, status, amount);

-- 查询只需索引列
SELECT user_id, status FROM orders WHERE user_id = 1001;

索引下推（ICP）

MySQL 5.6+ 黑科技：

在存储引擎层提前过滤数据

索引散兵清理

-- 每月检查无用索引
SELECT * FROM sys.schema_unused_indexes;

五、最佳实践：索引优化军规

// 分页优化Java实现
public Page<User> getUsers(Long lastId, int limit) {
    String sql = "SELECT * FROM user WHERE id > ? ORDER BY id ASC LIMIT ?";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(), lastId, limit);
}

六、面试考点区

问题1：varchar字段传int参数为何索引失效？

答：触发隐式转换→索引列计算→B+树失效→全表扫描

问题2：如何判断索引选择性？

答：SELECT COUNT(DISTINCT col)/COUNT(*) FROM table

结果>0.2适合建索引

问题3：EXPLAIN中哪些信号危险？

• type: ALL（核爆级）
• Extra: Using filesort（排序灾难）
• rows: 1000000（预估扫描行数）

七、终极总结：与索引和平共处原则

设计阶段

• 优先整数字段索引
• 联合索引遵循ASC排序原则

开发阶段

// MyBATis防类型事故
@Param("userId") Long userId // 而非Integer

运维阶段

-- 每月执行
ANALYZE TABLE orders; 
OPTIMIZE TABLE critical_data;

最后忠告：索引不是银弹！200万数据以下，精心设计的索引比分布式更有效；500万以上，考虑分库分表+索引的组合拳。

附录：索引健康检查清单

- [ ] 所有SQL都通过EXPLAIN验证

- [ ] 联合索引列顺序符合查询模式

- [ ] 避免在WHERE子句中使用函数

- [ ] 定期清理冗余索引（工具：pt-duplicate-key-checker）

- [ ] 为慢查询设置监控（>0.5秒报警）

到此这篇关于一文揭秘MySQL索引失效原因与优化方案的文章就介绍到这了,更多相关MySQL索引失效内容请搜索编android程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！