什么是二级索引？什么是覆盖索引？索引下推是什么？什么是索引的最左匹配原则？

约 1106 字大约 4 分钟

2025-02-17

二级索引（Secondary Index）

定义：二级索引（也称为辅助索引）是指在数据库表中，除了主键索引之外的其他索引。它允许用户通过非主键列快速查找数据。

特点：

应用场景：

CREATE INDEX idx_username ON users(username);

覆盖索引（Covering Index）

定义：覆盖索引是指查询中涉及的所有列都可以通过索引直接获取，而无需回表查询数据行。换句话说，索引本身包含了查询所需的所有信息。

特点：

应用场景：

当查询的列完全被索引覆盖时，可以使用覆盖索引。
例如，查询 SELECT username, age FROM users WHERE age > 18，如果有一个索引 CREATE INDEX idx_age_username ON users(age, username)，那么这个查询就可以使用覆盖索引。

CREATE INDEX idx_age_username ON users(age, username);

索引下推（Index Condition Pushdown, ICP）

定义：索引下推是一种查询优化技术，允许数据库引擎在索引扫描阶段就对查询条件进行过滤，而不是等到回表之后再过滤。它通过将查询条件“下推”到存储引擎层，减少回表的次数，从而提高查询性能。

工作原理：

应用场景：

索引下推适用于复杂的查询条件，尤其是涉及多个列的条件。
例如，查询 SELECT * FROM users WHERE age > 18 AND username LIKE 'a%'，如果启用了索引下推，存储引擎可以直接在索引扫描阶段过滤掉不符合 username LIKE 'a%' 的记录。

-- 启用索引下推（MySQL 默认启用）
SELECT * FROM users WHERE age > 18 AND username LIKE 'a%';

索引的最左匹配原则（Left-Most Prefix Principle）

定义：最左匹配原则是指在使用复合索引（多列索引）时，查询条件必须从索引的最左列开始匹配，否则索引无法被完全利用。

特点：

应用场景：

假设有一个复合索引 CREATE INDEX idx_a_b_c ON table(a, b, c)：
- 查询 WHERE a = 1 AND b = 2 可以利用整个索引。
- 查询 WHERE a = 1 也可以利用索引。
- 查询 WHERE b = 2 AND c = 3 无法利用索引。
- 查询 WHERE a = 1 AND b > 2 可以利用索引，但 c 列无法被利用。

CREATE INDEX idx_a_b_c ON table(a, b, c);

-- 可以利用索引
SELECT * FROM table WHERE a = 1 AND b = 2;

-- 无法利用索引
SELECT * FROM table WHERE b = 2 AND c = 3;

总结