MySQL 索引原理与优化

索引的基本概念

我在实际开发中经常使用 MySQL 的索引来提升查询速度。MySQL 的索引在 InnoDB 存储引擎中通过 B+树实现，会在磁盘上构建有序数据结构，查询时能够通过多级节点快速定位目标数据行。在我看来，这种树结构的存储方式使得查找效率随着数据量的增长仍能保持在较高水平。

索引的分类

主键索引

我在为表设置主键时会自动获得一棵主键索引树的支持。主键索引的叶子节点存储整行数据，因此通过主键查询可以在一次索引查找中直接获取完整行数据。
例如在一个订单表中创建主键索引

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  user_id BIGINT,
  product_id BIGINT,
  order_date DATETIME
) ENGINE=InnoDB;

查询特定 order_id 的订单时，InnoDB 会通过主键索引树快速找到对应行数据。

SELECT * FROM orders WHERE order_id = 12345;

二级索引

我在为非主键列创建索引时获得的是二级索引。二级索引的叶子节点存储的是对应行的主键值。通过二级索引查询数据时通常需要先找到匹配行的主键值，再回主键索引树中检索出完整的数据行。这被称为回表查询。
例如为 user_id 列创建二级索引

CREATE INDEX idx_user_id ON orders(user_id);

当我查询 user_id 对应的订单时

SELECT * FROM orders WHERE user_id = 6789;

数据库会先在 idx_user_id 索引中找到匹配的主键值，再回表获取完整行数据。

联合索引

我常在多列组合查询条件下创建联合索引，例如在需要按 user_id 与 order_date 频繁组合查询时。联合索引会按照定义顺序存储。遵循最左前缀原则，查询条件必须使用联合索引最左边的列，否则无法使用该索引。但如果查询条件中包含了最左边的列，即使中间的列没有使用，也可以使用该索引的最左边和最右边的列。
例如为( user_id, order_date )创建联合索引

CREATE INDEX idx_user_date ON orders(user_id, order_date);

查询时如果我使用

SELECT * FROM orders WHERE user_id = 6789 AND order_date > '2024-01-01';

会利用联合索引快速定位数据。如果只使用 order_date 作为条件则不会利用此联合索引。

唯一索引

我在需要确保某列数据唯一性时会使用唯一索引。唯一索引允许 NULL 值，但非 NULL 值必须唯一。例如在用户信息表中为 email 列创建唯一索引确保每个邮箱只对应一位用户。

CREATE UNIQUE INDEX idx_email ON users(email);

索引优化原则

选择合适的列建立索引

我会优先为区分度高的列建立索引。例如 user_id 在用户量较大时区分度高，检索效果明显。如果为性别这种区分度低的列建索引查询效果并不佳，索引树会增加存储与维护成本。

控制索引数量

我在实际维护中发现过多的索引会使 INSERT、UPDATE、DELETE 操作成本上升，因此我倾向于保持单表索引数量在合理范围内，通常不超过五个。

避免重复索引

我在优化时会检查现有索引，若已经有(user_id, order_date)的联合索引则不需要再次为 user_id 单独建索引，这样可以减少存储和维护开销。

利用覆盖索引

我尽可能让查询使用覆盖索引，这样当查询的列全部包含在索引中时，无需再回表查询。在查询中多加考虑实际业务字段的组合从而形成合适的覆盖索引是我常用的技巧。

索引失效场景

使用函数或运算

我在检索中如果对索引列使用函数或进行计算，会导致索引无法使用。例如

SELECT * FROM orders WHERE YEAR(order_date) = 2024;

这里对 order_date 使用 YEAR 函数会使索引失效。为避免这一问题，我倾向于对查询条件进行改写，例如

SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

这样查询可以充分利用索引。

实战检索与优化

我在优化索引时常用 EXPLAIN 分析 SQL 执行计划，通过查看 type、key、rows 等字段了解索引使用情况。如果发现查询未使用预期的索引，我会考虑是否存在函数调用、查询条件顺序、索引列区分度不足等问题。

EXPLAIN SELECT * FROM orders WHERE user_id = 6789 AND order_date > '2024-01-01';

通过这种方式，我能快速定位索引问题并有针对性地进行优化。