前言
在后端开发的世界里,数据库性能往往是整个系统的瓶颈所在。一个没有经过优化的数据库,在数据量达到百万级甚至千万级时,查询速度会慢得让人无法忍受。而索引,作为数据库性能优化的 "核武器",掌握它的原理和使用技巧,是每一个优秀后端工程师的必备技能。
本文将从索引的本质出发,深入讲解 MySQL 索引的核心实现 B + 树,分析常见的索引类型和使用场景,揭示索引失效的十大经典陷阱,并通过真实的实战案例展示如何进行索引优化。无论你是刚入门的新手,还是有一定经验的开发者,相信这篇文章都能给你带来新的收获。
一、索引的本质:为什么它能让查询变快?
在深入讲解索引之前,我们先思考一个最基本的问题:索引到底是什么?为什么它能让查询速度提升几个数量级?
1.1 没有索引的查询:全表扫描
想象一下,你有一本 1000 页的字典,现在要找 "数据库" 这个词。如果没有目录,你只能从第一页开始一页一页地翻,直到找到这个词为止。这就是数据库中的全表扫描(Full Table Scan)。
在 MySQL 中,当你执行一条没有索引的查询时:
sql
SELECT * FROM users WHERE username = 'zhangsan';
数据库会逐行扫描users表中的每一条记录,比较username字段的值是否等于 'zhangsan'。如果表中有 1000 万条记录,数据库就需要进行 1000 万次比较。在最坏的情况下,目标记录在最后一行,数据库需要扫描整个表才能找到它。
1.2 有索引的查询:二分查找
而如果字典有目录,你只需要在目录中找到 "数" 这个拼音对应的页码,然后直接翻到那一页就可以了。这就是索引的本质:通过建立数据的排序结构,将线性查找转化为二分查找,从而将时间复杂度从 O (n) 降低到 O (log n)。
我们可以用一个简单的数学计算来感受一下这种差距:
- 对于 1000 条记录:全表扫描需要 1000 次比较,二分查找只需要 10 次
- 对于 100 万条记录:全表扫描需要 100 万次比较,二分查找只需要 20 次
- 对于 10 亿条记录:全表扫描需要 10 亿次比较,二分查找只需要 30 次
这就是为什么索引能让查询速度提升几个数量级的根本原因。
1.3 索引的代价:天下没有免费的午餐
虽然索引能极大地提升查询速度,但它并不是没有代价的:
因此,索引不是越多越好,而是要根据实际的查询场景进行合理的设计。
二、MySQL 索引的核心实现:B + 树
MySQL 中最常用的存储引擎是 InnoDB,而 InnoDB 的索引是基于B + 树实现的。要真正理解 MySQL 索引,就必须先理解 B + 树的数据结构。
2.1 为什么是 B + 树,而不是二叉树?
很多人会问:为什么 MySQL 选择 B + 树作为索引的数据结构,而不是二叉搜索树、AVL 树或者红黑树?
答案很简单:磁盘 IO 是数据库性能的最大瓶颈。
数据库的数据是存储在磁盘上的,每次查询都需要从磁盘读取数据到内存中。磁盘 IO 的速度比内存访问慢了几个数量级。因此,数据库索引设计的核心目标就是尽量减少磁盘 IO 的次数。
二叉树的每个节点只有两个子节点,树的高度会随着数据量的增加而迅速增长。对于 100 万条记录,二叉树的高度大约是 20,这意味着一次查询需要 20 次磁盘 IO。而 B + 树是一种多路平衡查找树,每个节点可以有多个子节点,树的高度非常低。对于 100 万条记录,B + 树的高度通常只有 3-4 层,这意味着一次查询只需要 3-4 次磁盘 IO。
2.2 B + 树的结构特点
B + 树是 B 树的一种变体,它具有以下特点:
这种结构带来了很多优势:
- 非叶子节点不存储数据,因此可以存储更多的键值,进一步降低树的高度
- 叶子节点之间的双向链表使得范围查询变得非常高效
- 所有查询都必须到达叶子节点,查询性能稳定
2.3 InnoDB 的聚簇索引与二级索引
InnoDB 的索引分为两种:聚簇索引(Clustered Index)和二级索引(Secondary Index)。
聚簇索引是按照表的主键构建的 B + 树,它的叶子节点直接存储整行数据。也就是说,聚簇索引的叶子节点就是数据页本身。这就是为什么 InnoDB 表必须有主键,而且主键查询是最快的。
二级索引是按照非主键字段构建的 B + 树,它的叶子节点存储的是主键值,而不是整行数据。当我们使用二级索引进行查询时,需要先在二级索引中找到对应的主键值,然后再用这个主键值去聚簇索引中查找整行数据。这个过程叫做回表。
回表操作会增加额外的磁盘 IO,因此在设计索引时,我们应该尽量避免回表。
三、常见索引类型与使用场景
MySQL 支持多种类型的索引,每种索引都有其特定的使用场景。了解这些索引类型的特点,是正确使用索引的前提。
3.1 主键索引(PRIMARY KEY)
主键索引是最特殊也是最重要的索引。它具有以下特点:
- 一个表只能有一个主键索引
- 主键索引的值必须唯一,且不能为 NULL
- InnoDB 会自动为主键创建聚簇索引
最佳实践:
- 尽量使用自增整数作为主键,这样可以保证数据的插入顺序和聚簇索引的顺序一致,避免页分裂
- 不要使用过长的字符串作为主键,因为二级索引的叶子节点会存储主键值,过长的主键会导致二级索引变得很大
3.2 唯一索引(UNIQUE)
唯一索引保证索引列的值不能重复,但可以为 NULL。一个表可以有多个唯一索引。
sql
CREATE UNIQUE INDEX idx_username ON users(username);
使用场景:
- 需要保证字段唯一性的场景,如用户名、手机号、邮箱等
- 唯一索引的查询速度比普通索引略快,因为数据库在找到第一条匹配的记录后就可以停止查找
3.3 普通索引(INDEX)
普通索引是最基本的索引类型,它没有任何限制,只是用来加速查询。
sql
CREATE INDEX idx_email ON users(email);
使用场景:
- 经常出现在 WHERE 子句中的字段
- 经常出现在 ORDER BY 和 GROUP BY 子句中的字段
3.4 复合索引(INDEX)
复合索引是指包含多个字段的索引。复合索引遵循最左前缀原则,即只有当查询条件包含索引的最左字段时,索引才会被使用。
sql
CREATE INDEX idx_name_age ON users(name, age);
对于上面的复合索引,以下查询会使用索引:
sql
SELECT * FROM users WHERE name = 'zhangsan';
SELECT * FROM users WHERE name = 'zhangsan' AND age = 20;
而以下查询不会使用索引:
sql
SELECT * FROM users WHERE age = 20;
最佳实践:
- 将选择性高的字段放在索引的前面
- 尽量使用复合索引代替多个单列索引,因为复合索引可以覆盖更多的查询场景
- 复合索引的字段数不宜过多,一般不超过 5 个
3.5 覆盖索引
覆盖索引是一种特殊的复合索引,它包含了查询所需的所有字段。当使用覆盖索引进行查询时,不需要回表,直接从索引中就能获取所有需要的数据,因此查询速度非常快。
sql
— 创建覆盖索引
CREATE INDEX idx_name_age ON users(name, age);
— 这个查询可以使用覆盖索引,不需要回表
SELECT name, age FROM users WHERE name = 'zhangsan';
覆盖索引是性能优化的利器,在实际开发中应该尽量使用。
四、索引失效的十大经典场景
很多时候,我们明明创建了索引,但查询还是很慢。这通常是因为索引失效了。下面我将总结索引失效的十大经典场景,帮助你避免这些常见的陷阱。
4.1 在索引列上使用函数或表达式
sql
— 索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2023;
— 索引有效
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
4.2 使用不等于操作符(!=、<>)
sql
— 索引失效
SELECT * FROM users WHERE status != 1;
4.3 使用 IS NOT NULL
sql
— 索引失效
SELECT * FROM users WHERE email IS NOT NULL;
4.4 以通配符开头的 LIKE 查询
sql
— 索引失效
SELECT * FROM users WHERE name LIKE '%zhang';
— 索引有效
SELECT * FROM users WHERE name LIKE 'zhang%';
4.5 字符串不加引号
sql
— 索引失效(发生隐式类型转换)
SELECT * FROM users WHERE phone = 13800138000;
— 索引有效
SELECT * FROM users WHERE phone = '13800138000';
4.6 OR 连接的条件中有非索引列
sql
— 索引失效
SELECT * FROM users WHERE name = 'zhangsan' OR address = 'beijing';
4.7 违反最左前缀原则
sql
— 索引失效
SELECT * FROM users WHERE age = 20;
4.8 使用全表扫描比使用索引更快
当查询结果占表中大部分数据时,数据库会选择全表扫描而不是使用索引。因为使用索引需要先扫描索引,然后再回表,这比直接全表扫描还要慢。
4.9 索引列参与计算
sql
— 索引失效
SELECT * FROM users WHERE age + 1 = 21;
— 索引有效
SELECT * FROM users WHERE age = 20;
4.10 使用 NOT IN
sql
— 索引失效
SELECT * FROM users WHERE id NOT IN (1, 2, 3);
五、实战优化案例
下面我将通过几个真实的实战案例,展示如何进行索引优化。
5.1 案例一:慢查询优化
问题:有一个订单表orders,包含以下字段:
- id(主键)
- user_id(用户 ID)
- order_no(订单号)
- create_time(创建时间)
- status(订单状态)
现在有一个查询非常慢:
sql
SELECT * FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time DESC LIMIT 10;
分析:
- 这个查询有三个条件:user_id = 123、status = 1和ORDER BY create_time DESC
- 如果只在user_id上创建索引,那么数据库会先找到所有user_id = 123的记录,然后在内存中对这些记录进行排序,这在数据量很大时会非常慢
优化方案: 创建一个复合索引(user_id, status, create_time):
sql
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
优化效果:
- 数据库可以直接在索引中找到符合条件的记录,并且这些记录已经按照create_time排序好了
- 不需要在内存中进行排序,查询速度提升了 100 倍以上
5.2 案例二:避免回表
问题:有一个用户表users,包含以下字段:
- id(主键)
- username(用户名)
- email(邮箱)
- phone(手机号)
- avatar(头像)
现在有一个查询:
sql
SELECT username, email FROM users WHERE phone = '13800138000';
分析:
- 如果只在phone上创建普通索引,那么数据库需要先在二级索引中找到对应的主键值,然后再回表查询username和email字段
- 回表操作会增加额外的磁盘 IO
优化方案: 创建一个覆盖索引(phone, username, email):
sql
CREATE INDEX idx_phone_username_email ON users(phone, username, email);
优化效果:
- 数据库可以直接从索引中获取所有需要的字段,不需要回表
- 查询速度提升了 10 倍以上
5.3 案例三:分页查询优化
问题:当分页查询的偏移量很大时,查询会变得非常慢:
sql
SELECT * FROM articles ORDER BY id DESC LIMIT 100000, 10;
分析:
- MySQL 需要先扫描 100010 条记录,然后丢弃前 100000 条,只返回最后 10 条
- 这会造成大量的磁盘 IO 和 CPU 消耗
优化方案: 使用 "延迟关联" 技术:
sql
SELECT a.* FROM articles a
JOIN (SELECT id FROM articles ORDER BY id DESC LIMIT 100000, 10) b
ON a.id = b.id;
优化效果:
- 先在索引中找到需要的 10 个 ID,然后再回表查询这 10 条记录
- 查询速度提升了 100 倍以上
六、索引设计的最佳实践
最后,我将总结一些索引设计的最佳实践,帮助你在实际开发中设计出高效的索引。
七、总结
索引是数据库性能优化的核心,掌握索引的原理和使用技巧,是每一个后端工程师的必备技能。本文从索引的本质出发,深入讲解了 MySQL 索引的核心实现 B + 树,分析了常见的索引类型和使用场景,揭示了索引失效的十大经典陷阱,并通过真实的实战案例展示了如何进行索引优化。
希望这篇文章能帮助你更好地理解和使用索引,让你的数据库查询速度更上一层楼。记住,索引优化是一个持续的过程,没有最好的索引,只有最适合的索引。在实际开发中,我们需要根据具体的业务场景和查询模式,不断地调整和优化索引。
如果你觉得这篇文章对你有帮助,欢迎点赞、收藏和转发。如果你有任何问题或建议,也欢迎在评论区留言交流。


