数据库索引是如何工作的？
如果你曾查询过一个数据库，那么即使你当时没有意识到，也很可能已经使用过索引了。数据库索引用于通过创建辅助数据结构来加速读取查询，使扫描更快。本文将详细介绍数据库索引的工作原理，并特别关注 MySQL，这款深受许多人喜爱的数据库软件。
索引加速查询
索引本质上是一种加速读取查询的方式，尤其是带有过滤条件（例如 WHERE）的查询。它是数据库引擎中一种存在于关联表之外的额外数据结构，指向你尝试查询的数据。
为了避免常见的图书馆类比，我们假设一个极端的场景：你在 MySQL 数据库中有一个包含所有用户的表。你正在为你的社交应用添加功能，使用户可以搜索和过滤其他用户，这意味着将有查询在生产环境中运行，扫描整个用户表。更糟的是，你的应用相当成功，用户达到了几十万量级！
然而，不幸的是，现在对用户表进行 SELECT 操作性能不是很好。基于 UI 输入应用的过滤条件（如用户所在位置、账户类型、最近活动时间等数据库中的列），需要扫描整个用户表，平均得耗时 O(N/2)。你的查询需要5-6秒才能运行完毕，这对于内部数据分析可能还算可接受，但远远不能满足顺滑的用户体验需求。
为了解决这个问题，你可以在用户表的“最近活动时间（most recent activity）”列上创建一个索引（通过 CREATE INDEX ）。在后台，MySQL 会创建一个新的伪“表”，其中包含了两个列：一个是“最近活动时间”的值，一个是指向用户表中记录的指针。神奇之处在于，这个“表”会被排序并存储为二叉树，按“最近活动时间”列的值排序。结果是，查询效率提高到了 O(Log(n))，运行时间仅需一秒或更短。
这就是索引的基本原理。如果你知道将会反复运行特定的查询，并且担心读取性能，那么创建一个（或几个）索引可以显著提升查询速度。
不过这只是简单版本。在幕后还发生了很多其他操作，而索引过多可能甚至会降低查询性能。
数据库索引的幕后工作原理
索引并不是魔法，它是一种包含指向特定数据库记录的指针的数据结构。在没有索引的情况下，数据库中的数据通常以堆形式存储，基本上是一堆无序的行。这实际上可以在微软 SQL Server 和 Azure SQL 数据库中设置和切换。
在实际操作中，数据很少是完全没有排序的。通常，你会使用某种主键（Primary Key）进行排序——在 MySQL 中，主键可以与索引相同。例如，使用一个自动递增的整数作为主键。但数据当然只能按一个列排序，这会限制排序的“二叉效率”，只能针对你查询过滤的那个单一的、有序的列。索引基本上是一种让表可以按多列排序的方式，从而获得多列上过滤的二叉搜索效率。
当你在某个列上创建索引时，其实是在创建一个包含两列的新表：一个是被索引列的值，另一个是指向记录存储位置的指针。虽然索引的长度与原表相同，但宽度可能会显著变短，因此需要更少的磁盘块进行存储和遍历。MySQL 中的指针通常很小，通常少于 5 字节。著名的 Stack Overflow帖子 提供了详细的存储块数计算，对于更深入了解此原理的人可能会有帮助。
如果你未创建任何索引，那么当前的数据库中可能已经由系统生成了一些索引。你可以通过以下命令查看特定表上的索引：
SHOW INDEX FROM table_name FROM db_name;
此外，运行 EXPLAIN 语句时，也可以看到查询计划中用到的索引信息。这是 MySQL 文档中可能的 EXPLAIN 输出值的表格：注意 possible_keys 和 key 两列，均与索引选择有关。
| Column | JSON name | Definition |
| id | select_id | The SELECT identifier |
| select_type | None | The SELECT type |
| table | table_name | The table for the output row |
| partitions | partitions | The matching partitions |
| type | access_type | The join type |
| possible_keys | possible_keys | The possible indexes to choose |
| key | key | The index actually chosen |
| key_len | key_length | The length of the chosen key |
| ref | ref | The columns compared to the index |
| rows | rows | Estimate of rows to be examined |
| filtered | filtered | Percentage of rows filtered by table condition |
| Extra | None | Additional information |
这种 EXPLAIN 的使用方式在创建索引时也很有帮助，可以用于调试验证新索引是否按预期工作。
索引的类型
我们主要讨论了唯一索引（Unique Index），但它实际上只是众多类型中的一种。以下是一些常见的索引类型：
唯一索引（Unique Index）
唯一索引保证列中没有两个相同的非空值，同时对列进行排序，使查询可以利用二叉搜索实现 O(Log(n)) 效率。另外，主键（Primary Key）也是一种特殊的唯一索引。
文本索引（Text Index）
使用 FULLTEXT 创建文本索引，适用于 MySQL 的 CHAR、VARCHAR 或 TEXT 数据类型。MySQL 的文本索引提供了类似现代文本解析功能，包括自然语言搜索、布尔搜索，以及查询扩展搜索。
MySQL 中创建索引
创建索引的语法非常标准，例如：
CREATE INDEX index_name ON table_name (column_name);
以下是自定义索引的选项和示例：
CREATE INDEX users_most_recent_activity ON users ({most_recent_activity} DESC) COMMENT 'for querying by most recent activity';
你可以自定义索引类型、排序方式、算法以及锁定策略等。更多内容可参考 MySQL 文档中关于索引的章节。
关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台
除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接
本文链接：http://choupangxia.cn/2025/09/07/how-do-database-indexes-work/
