VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
思维模型
想象一本 800 页的书。要找到每一处提到”TCP/IP”的地方,您可以逐页阅读——这就是全表扫描。或者您可以查看书末的索引,找到”TCP/IP — 第 42、107、391 页”,然后直接翻到那些页。数据库索引做的就是这件事。 MySQL 默认的索引结构是 B 树(平衡树)。它让值保持有序,从而支持快速查找(二分查找)、范围扫描(BETWEEN、>、<)和有序输出(ORDER BY)——这一切都不需要读取整个表。
MySQL 如何使用索引
当您运行查询时,MySQL 的优化器会检查是否有索引能缩小它需要读取的行的范围。当您的查询按某个已索引的列进行筛选或排序时,它就会使用索引。EXPLAIN:查看 MySQL 实际在做什么
EXPLAIN 显示 MySQL 计划如何执行查询。key 列告诉您它使用了哪个索引(没有则为 NULL),rows 估计它需要检查多少行。
type: ref 表示 MySQL 使用了索引查找。type: ALL 表示全表扫描——通常正是您想避免的。
复合索引:列顺序很重要
复合索引覆盖多个列。列的顺序决定了该索引何时可用。WHERE user_id = 42— 可以,最左列WHERE user_id = 42 AND status = 'shipped'— 可以,两列都用到WHERE status = 'shipped'— 不可以,跳过了最左列
(user_id, status) 和 (status, user_id) 是用途不同的两个索引。请围绕您最常见的查询模式来设计复合索引。
覆盖索引:只靠索引回答查询
覆盖索引包含查询需要的所有列。MySQL 可以完全从索引回答查询,而不读取实际的行数据——在大表上这是显著的提速。EXPLAIN 显示 Extra: Using index。不需要任何行查找。
何时不该添加索引
索引并不是越多越好。每个索引都有代价:- 写入开销 — 每一条 INSERT、UPDATE 和 DELETE 都必须更新表上的每一个索引
- 存储 — 在写入密集的数据上,索引可能和表本身一样大
- 优化器复杂度 — 索引太多时,优化器有时会选错
- 列的基数高(不同值很多)——
user_id、email、order_id - 查询筛选出表的一小部分
- 基数低——只有 3 个可能值的
status、布尔型的is_active - 表小到全表扫描本来就很快
- 该列写入频繁而很少被查询
主键始终是索引
InnoDB 表围绕主键组织——它不是一个单独的索引,而是行的物理存储顺序。这意味着:- 主键查找是最快的查询
- 主键值连续时,行在物理上相邻
- 随机主键(如 UUID v4)在插入时造成碎片——请参阅 MySQL 中的 UUID
常见的索引错误
在已索引的列上使用函数 — 当您把列包在函数里时,MySQL 无法使用索引:% 开头时,索引帮不上忙:
OR 条件,但这并不可靠。在大表上,UNION 更可预测:
查找缺失的索引
慢查询日志会记录超过时间阈值的查询——这是寻找该给什么建索引的一个好起点:EXPLAIN。留意 type: ALL 和数以百万计的 rows。
总结
- B 树索引让值保持有序,以支持快速查找和范围扫描
- 当您的查询按前导的已索引列进行筛选或排序时,MySQL 会使用索引
- 复合索引的列顺序很重要——围绕您最常见的查询来设计
- 覆盖索引最快——查询完全从索引得到回答
- 索引在写入时有代价;只在读取收益值得的地方添加
- 把列包进函数就等于禁用了它的索引——请改用范围条件
另请参阅
- 阅读 MySQL 的 EXPLAIN — 验证您的索引是否被使用,并理解 MySQL 为什么选择它们
- MySQL 中的覆盖索引 — 不需要任何行查找就能回答查询的索引
- MySQL 慢查询日志 — 找出哪些查询最先需要索引
- MySQL INFORMATION_SCHEMA — 用查询找到索引元数据和未使用的索引
- 为什么我的 MySQL 查询很慢? — 索引所属的更大范围的调优流程
- MySQL 中的分页:Keyset 与 OFFSET — 对大表分页的索引驱动方式