深入理解MySQL索引、索引失效场景

大家好、我是星。
今天我们来聊一下MySQL中的索引，以及索引失效的场景。

那么这个技术点应该说是咱们Java程序员必须掌握的哈，今天我将会从什么是索引，索引底层数据结构，以及索引失效的场景来依次展开。咱们今天说的索引是基于InnoDB存储引擎下的。

1.啥是索引呢？

简单说索引就是一种帮助快速定位数据，减少IO次数的数据结构，在InnoDB下是B+树。

2.索引数据结构？

InnoDB存储引擎选择B+树作为索引结构是经过多方面权衡的结果的。

咱先来说说B+树和其他数据结构的对比

1.和二叉搜索树对比

二叉搜索树它存在的问题主要有这么几点：
1.树的高度高，存储百万数据大概需要20层
2.每次查找需要很多次的磁盘I/O，所以性能差
3.退化为链表的时候性能极差

2.和B树对比

和B树最大的区别就是B树的非叶子节点上是存了数据的，换句话说就是B树的每一个节点都存储数据，而B＋树只在叶子节点存储数据，这会导致多方面的问题，那么我待会说B＋树的优点的时候，其实也就相当于把B树的缺点说了。

3.和hash表对比

hash索引的优点就是查找快，查找是O（1）的时间复杂度，但缺点很明显，它不支持范围查询、不支持排序、不支持模糊匹配、会存在hash冲突的问题。

4.和红黑树/AVL树对比

这类型树缺点就是树太高了，导致IO次数太多

5.调表

调表优点就是简单，而且支持范围查询，但是空间开销大，磁盘存储效率低，查询不如B＋树稳定。

3.使用B＋树的优势

相比于上面说的几种数据结构，bB＋树是有很多优势的。

1.磁盘I/O效率高

这点是非常关键的，首先就是咱说的，B＋树是一个“矮胖”的结构，那么如果咱假设一个数据页的大小是16KB的情况下，假设主键大小是8字节（BIGINT了这个相当于是），指针大小是6字节，那么每个节点可以存储的键值大概就是16KB / (8 + 6) 大概是1170个键值对。

咱们再来计算一下3层B＋树的容量：
根节点：1170个指针
第二层：1170 * 1170 = 1360000多个指针（咱就取个大概值了），所以第三层就有1360000个节点（注意一下，第三层是用来放数据了，而第一层第二层没有存数据行所以可以存大量的键值对，这也就是为啥第三层可以有那么多节点的原因）
第三层（叶子层）：咱假设每行1KB，所以每个叶子节点存储16行，那么总共的数据量就是 1360000 * 16 大概是21900000行，所以你可以想象一下，你只需要三层就能存储2100多万条数据，而且优点还不止于此，我们继续看。

2.范围查询效率极高

咱举一个sql
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

那么B+树的执行过程是啥样的呢：
1.找到age=20的叶子节点，你只需要3次IO就找到了
2.沿着叶子节点的链表向右遍历
3.直到age>30停止

这个过程中，叶子节点是双向链表，所以顺序访问高效，而且无需回溯上层节点（是不是B树就做不到这一点了）而且适合分页查询。

3.查询性能稳定

B+树所有数据都在叶子节点
任何查询都需要从根走到叶子（3层）
对比B树：
B树：数据可能在根节点（1次I/O）
B树：数据可能在叶子节点（3次I/O）
B树：查询性能不稳定
B+树保证了O(logN)的稳定性能

4.非常适合数据库场景

像一些全表扫描呀，排序呀，覆盖索引呀，那B＋树都是首选。

5.节点利用率高

B+树节点填充率更高
原因：
非叶节点不存数据，可存更多键值
叶子节点存数据，空间利用率高
对比B树：
B树节点需要预留空间给数据，导致：
节点分裂更频繁
树的高度增加

6.更适合现代硬件

叶子节点的顺序存储充分利用了顺序IO，而且可以预测性的读取相邻节点，对缓存也友好

4.索引失效的场景

1.违背最左前缀原则

因为当你使用联合索引去查询的时候，那首先会根据第一个字段进行排序，如果第一个字段相等了那就第二个字段排序……依次类推，所以这个场景是非常容易理解的，你跳过了第一个字段直接查询后一个字段，那就可能会导致没用到索引或者索引半失效。

2.对索引列进行运算或者函数操作

这里我上网给你截一段代码出来，写得非常好

-- ✗ 失效：使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- ✓ 优化：使用范围查询
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

-- ✗ 失效：进行运算
SELECT * FROM users WHERE age + 1 = 26;
-- ✓ 优化：运算移到右边
SELECT * FROM users WHERE age = 25;

-- ✗ 失效：隐式类型转换（实际是函数转换）
SELECT * FROM users WHERE phone = 13800138000;  -- phone是varchar类型
-- ✓ 优化：保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

3.使用范围查询导致后面列失效

给你一段代码

-- 索引: INDEX idx_age_city_salary (age, city, salary)

-- ✗ city和salary索引失效
SELECT * FROM users WHERE age > 20 AND city = '北京' AND salary > 10000;

-- ✓ 使用覆盖索引可以避免全表扫描
SELECT age, city, salary FROM users 
WHERE age > 20 AND city = '北京' AND salary > 10000;

4. 使用OR条件（除非所有列都有索引）

-- ✗ 失效：即使name和age都有单独索引
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- ✓ 优化1：使用UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

-- ✓ 优化2：使用IN（对于同一列）
SELECT * FROM users WHERE name IN ('张三', '李四');

5. 使用不等于（!= 或 <>）

-- ✗ 失效：大多数情况
SELECT * FROM users WHERE status != 1;

-- ✓ 优化：使用覆盖索引
SELECT id, status FROM users WHERE status != 1;

6. 使用IS NULL或IS NOT NULL

-- 情况取决于数据分布
-- ✗ 如果NULL值很多，可能失效
SELECT * FROM users WHERE name IS NULL;

-- ✓ 如果NOT NULL值很少，可能使用索引
SELECT * FROM users WHERE name IS NOT NULL;

7. LIKE以通配符开头

-- ✗ 失效：前导通配符
SELECT * FROM users WHERE name LIKE '%张三%';
SELECT * FROM users WHERE name LIKE '%张三';

-- ✓ 生效：后缀通配符
SELECT * FROM users WHERE name LIKE '张三%';

-- ✓ 优化：使用全文索引
ALTER TABLE users ADD FULLTEXT ft_name (name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张三');

8. 数据量小或区分度低

-- ✗ 失效：数据量太少（通常<表行数20%）
-- MySQL认为全表扫描更快

-- ✗ 失效：区分度太低（如性别字段）
SELECT * FROM users WHERE gender = '男';  -- 假设50%数据

-- ✓ 优化：结合其他条件
SELECT * FROM users WHERE gender = '男' AND age = 25;

9. JOIN条件类型不一致

-- ✗ 失效：类型不一致
SELECT * FROM t1 
JOIN t2 ON t1.varchar_col = t2.int_col;  -- 隐式转换

-- ✗ 失效：字符集不一致
-- t1.utf8_col与t2.utf8mb4_col连接

5.总结

今天呢就主要介绍了索引结构，以及它的一些优点，以及索引失效的场景，我个人觉得还是挺深刻的，那么像索引优化呀，我可能会在后面几期去出。

好的，那么今天的内容到这里就结束了，如果对您有帮助，可以点个赞支持一下，咱们下期再见。