MySQL索引原理深入浅出：B+树结构与索引工作机制-mysql教程-PHP中文网

MySQL索引基于B+树结构，通过将数据组织为有序的层级树形结构，提升查询效率。B+树的所有数据存储在叶子节点，内部节点仅存键值和指针，使得单个磁盘页能容纳更多键值，降低树高，减少I/O次数。叶子节点间通过双向链表连接，支持高效范围查询。相比B树，B+树更适合数据库场景，因其优化了磁盘读写性能和顺序访问效率。MySQL的InnoDB引擎使用聚簇索引，主键索引的叶子节点直接存储行数据，而二级索引则存储主键值，需“回表”获取完整数据。查询优化器根据成本选择最优执行计划，判断是否使用索引、选择哪个索引，并支持覆盖索引、索引合并等策略以提升性能。设计索引时应遵循最左前缀原则，优先在WHERE、JOIN、ORDER BY、GROUP BY等高频列建立索引，选择高选择性、小数据类型的列构建复合索引。避免过度索引，防止写入性能下降和优化器误判。常见误区包括在索引列上使用函数、LIKE以%开头、隐式类型转换、OR条件导致索引失效等，应通过改写SQL或调整设计规避。利用EXPLAIN分析执行计划，定期执行ANALYZE TABLE更新统计信息，确保优化

mysql索引原理深入浅出：b+树结构与索引工作机制

MySQL索引的核心在于它通过一种高效的数据结构——B+树——来组织数据，从而大幅提升了数据库的查询性能。你可以把它想象成一本书的目录，通过目录你可以快速定位到感兴趣的章节，而不需要一页一页地翻找。它不是直接存储数据本身，而是存储了指向数据行的指针，或者在某些情况下，直接存储了数据行的一部分，以此来加速数据的查找、排序和分组操作。

MySQL索引的工作机制，本质上就是利用B+树的特性，将原本需要扫描整个数据表的线性查找，转化为对这棵树的层级遍历。当一条查询语句涉及到索引列时，数据库的查询优化器会尝试通过索引树来快速定位到目标数据所在的物理位置。这种方式极大地减少了磁盘I/O操作，因为相比于随机地读取大量数据页，遍历一棵相对较浅的B+树要高效得多。

MySQL索引的B+树结构究竟长什么样，它为什么比B树更适合数据库？

在我看来，理解MySQL索引，首先要从它的“骨架”——B+树开始。B+树是一种多路平衡查找树，它的设计目标就是为了磁盘存储而优化。想象一下，这棵树有根节点、内部节点和叶子节点。

具体来说，B+树有几个关键特征：

所有数据都存储在叶子节点上。 这与B树不同，B树的内部节点也可以存储数据。在B+树中，内部节点只存储键值（key）和指向子节点的指针。
所有的叶子节点都位于同一层级。 这保证了从根节点到任何一个数据记录的查找路径长度都是相同的，查询性能稳定。
叶子节点之间通过双向链表连接。 这一点非常关键！它使得范围查询（例如
```
WHERE price BETWEEN 100 AND 200
```
登录后复制
）变得极其高效。一旦找到范围的起始点，就可以沿着链表顺序遍历，而无需再次从根节点开始查找。

那么，为什么MySQL，尤其是InnoDB存储引擎，会选择B+树而不是B树呢？我觉得这主要有以下几个考量：

磁盘I/O优化： 数据库操作最昂贵的就是磁盘I/O。B+树的内部节点不存储数据，这意味着单个节点（通常对应一个磁盘页）可以存储更多的键值。节点能存的键值越多，树的层级就越少，从根节点到叶子节点所需的磁盘I/O次数就越少。这对于数据库性能至关重要。
范围查询效率： 正如前面提到的，叶子节点之间的链表结构让范围查询如鱼得水。在B树中，如果要做范围查询，可能需要多次回到父节点或根节点进行查找，效率会大打折扣。
全表扫描的替代： 虽然索引不是用来替代全表扫描的，但在某些情况下，如果索引是覆盖索引（Covering Index），或者需要对索引列进行排序，B+树的叶子节点链表结构也能提供一个有序的、快速的遍历路径。

在我看来，B+树的设计哲学就是为了极致地优化磁盘I/O和范围查询，这完美契合了数据库系统的核心需求。它牺牲了内部节点存储数据的灵活性，换来了更扁平的树结构和更高效的范围扫描能力。

MySQL索引是如何工作的？深入剖析查询优化器如何利用索引。

MySQL索引的工作流程，可以分为“找”和“取”两个阶段，但实际远比这复杂，尤其是查询优化器在其中扮演的角色。

索引查找过程：

单值查找： 比如
```
SELECT * FROM users WHERE id = 123;
```
登录后复制
。

纳米搜索
纳米搜索：360推出的新一代AI搜索引擎

30

查看详情
- 查询优化器首先会根据
```
id
```
  登录后复制
  列上的索引（假设它是主键索引，也就是聚簇索引）从B+树的根节点开始。
- 它会比较查询的键值（123）与当前节点的键值范围，确定应该进入哪个子节点。
- 这个过程会一直重复，直到达到叶子节点。
- 对于聚簇索引，叶子节点直接包含了整行数据，所以数据直接返回。
- 对于非聚簇索引（或称辅助索引、二级索引），叶子节点存储的是索引列的值以及对应行的主键值。此时，需要进行一个“回表”操作：用获取到的主键值，再次去聚簇索引中查找完整的行数据。
范围查找： 比如
```
SELECT * FROM products WHERE price BETWEEN 100 AND 200;
```
登录后复制
。
- 查询优化器会先通过B+树找到范围的起始点（
```
price = 100
```
  登录后复制
  的叶子节点）。
- 然后，它会沿着叶子节点的双向链表，顺序地向后遍历，直到找到范围的结束点（
```
price = 200
```
  登录后复制
  的叶子节点）。
- 在这个遍历过程中，它会收集所有符合条件的记录（或其主键值，如果是辅助索引）。这种链表遍历比多次树查找要高效得多。

查询优化器的角色：

查询优化器就像是数据库的“大脑”，它负责解析SQL语句，然后生成一个执行计划，决定如何高效地检索数据。它利用索引的方式非常智能：

成本估算： 优化器不会盲目地使用索引。它会根据统计信息（例如索引的选择性、表的行数、数据分布等）来估算使用不同索引或不使用索引的成本（主要是I/O和CPU开销）。它会选择成本最低的执行计划。
索引选择： 如果有多个索引都可以用于查询，优化器会选择它认为最有效的那个。例如，一个选择性很高的索引（唯一值多）通常比选择性低的索引更受青睐。
覆盖索引： 这是一个非常重要的概念。如果一个辅助索引包含了查询所需的所有列，那么查询就不需要执行“回表”操作去聚簇索引中查找完整数据。这种索引被称为“覆盖索引”。例如，
```
SELECT name FROM users WHERE age > 25;
```
登录后复制
如果
```
age
```
登录后复制
和
```
name
```
登录后复制
都在同一个辅助索引中，那么这个查询就成了覆盖索引查询，效率会非常高。
索引合并（Index Merge）： 在某些复杂查询中，优化器甚至可以同时使用多个索引，然后将它们的结果进行合并（例如取交集或并集），以优化查询。
EXPLAIN
登录后复制
命令：这是我们了解优化器如何工作的利器。通过
```
EXPLAIN SELECT ...
```
登录后复制
我们可以看到查询计划，包括使用了哪个索引、扫描了多少行、是否进行了回表操作等。这对于调优来说至关重要。

在我看来，查询优化器是一个经验丰富的侦探，它不会放过任何线索（索引），但也不会盲目行动。它会权衡利弊，试图找到最经济、最快速的路径来获取数据。理解它的思考方式，是优化数据库性能的关键。

实践中如何设计和优化MySQL索引，避免常见的“坑”？

索引设计和优化，从来都不是一劳永逸的事情，它需要你对业务场景有深刻的理解，并且要持续地观察和调优。这里我总结了一些实践经验和常见的“坑”。

索引设计原则：

选择合适的列：
- WHERE子句中频繁使用的列： 这是最常见的索引场景。
- JOIN操作的连接列： 在多表连接时，连接列上建立索引能显著提高连接效率。
- ORDER BY和GROUP BY子句中的列： 索引可以帮助避免额外的排序操作。
- 高选择性列： 索引列的唯一值越多，选择性越高，索引效果越好。例如，身份证号比性别更适合做索引。
复合索引（联合索引）与最左前缀原则：
- 如果你经常同时查询多个列，可以考虑建立复合索引，例如
```
INDEX(col1, col2, col3)
```
  登录后复制
  。
- 最左前缀原则是复合索引的灵魂。这意味着，MySQL会从索引的最左边的列开始匹配。
  - ```
  INDEX(a, b, c)
```
  登录后复制
  可以用于
```
  WHERE a = X
```
  登录后复制
  、
```
  WHERE a = X AND b = Y
```
  登录后复制
  、
```
  WHERE a = X AND b = Y AND c = Z
```
  登录后复制
  。
- 但它不能单独用于
```
  WHERE b = Y
```
  登录后复制
  或
```
  WHERE c = Z
```
  登录后复制
  ，也不能用于
```
  WHERE b = Y AND c = Z
```
  登录后复制
  。
- 如果查询条件是
```
  WHERE a = X AND c = Z
```
  登录后复制
  ，那么只有
```
  a
```
  登录后复制
  列会使用索引，
```
  c
```
  登录后复制
  列则会进行过滤。
- 我的经验是，设计复合索引时，把最常用于等值查询或范围查询的列放在前面，选择性高的列也尽量靠前。
避免过度索引：
- 索引不是越多越好。每个索引都会占用磁盘空间，并且在数据写入（INSERT、UPDATE、DELETE）时，数据库需要维护这些索引，这会带来额外的开销。
- 过多的索引还会增加查询优化器选择索引的复杂性，有时甚至可能导致优化器选择错误的索引。
- 经验之谈： 对于不经常更新的表，可以多加一些索引；对于写入频繁的表，则要慎重。
选择合适的数据类型：
- 尽量使用占用空间小、固定长度的数据类型。例如，
```
INT
```
  登录后复制
  通常比
```
VARCHAR(255)
```
  登录后复制
  更适合做索引。
- 短小的索引键值可以使B+树的内部节点存储更多的键值，从而降低树的高度，减少I/O。

常见的“坑”和优化技巧：

在索引列上进行函数操作：
- ```
WHERE YEAR(create_time) = 2023;
```
  登录后复制
  这样的查询会导致索引失效，因为数据库需要对
```
create_time
```
  登录后复制
  列的每一行都执行
```
YEAR()
```
  登录后复制
  函数，才能进行比较。
- 优化： 改为
```
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59';
```
  登录后复制
  。
LIKE '%keyword'
登录后复制
开头模糊匹配：
- ```
WHERE name LIKE '%john%';
```
  登录后复制
  这种以通配符开头的模糊查询会导致索引失效，进行全表扫描。
- 优化： 如果业务允许，尽量使用
```
WHERE name LIKE 'john%';
```
  登录后复制
  （前缀匹配），这样可以利用索引。对于非前缀匹配，可以考虑使用全文索引或搜索引擎技术。
OR
登录后复制
条件：
- 在某些情况下，
```
WHERE col1 = A OR col2 = B;
```
  登录后复制
  可能会导致索引失效。如果
```
col1
```
  登录后复制
  和
```
col2
```
  登录后复制
  都有索引，优化器可能会尝试索引合并，但也可能放弃索引进行全表扫描。
- 优化： 可以尝试使用
```
UNION ALL
```
  登录后复制
  分解为两个独立的查询，或者确保
```
OR
```
  登录后复制
  两边的条件都能高效利用索引。
NULL
登录后复制
值：
- ```
WHERE col IS NULL;
```
  登录后复制
  或
```
WHERE col IS NOT NULL;
```
  登录后复制
  这种查询，索引通常不会被使用（这取决于MySQL的版本和具体的索引类型，但普遍情况是这样）。
- 优化： 如果业务允许，尽量避免在索引列中存储
```
NULL
```
  登录后复制
  值，或者将其替换为某个特定值（例如
```
0
```
  登录后复制
  或空字符串），并为该值建立索引。
隐式类型转换：
- ```
WHERE indexed_col = '123';
```
  登录后复制
  如果
```
indexed_col
```
  登录后复制
  是
```
INT
```
  登录后复制
  类型，字符串
```
'123'
```
  登录后复制
  会被隐式转换为数字，这可能导致索引失效。
- 优化： 确保查询条件的数据类型与列的数据类型一致。
EXPLAIN
登录后复制
是你的朋友：
- 不要猜测，要验证。在进行任何索引优化前，先用
```
EXPLAIN
```
  登录后复制
  分析当前的查询性能，看看索引是否被使用，以及如何使用。
定期分析表（
ANALYZE TABLE
登录后复制
）：
- MySQL的查询优化器依赖于表的统计信息来做决策。当表数据发生大量变化后，统计信息可能过时，导致优化器做出错误的判断。定期运行
```
ANALYZE TABLE your_table;
```
  登录后复制
  可以更新统计信息。