MySQL 索引

发表于

在 MySQL 中，索引是在存储引擎层而不是 Server 层实现的，所以没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。MySQL 支持的索引类型有 B-Tree 索引、哈希索引、全文索引等，默认的 InnoDB 存储引擎实现的是 B-Tree 索引。

索引模型

CREATE TABLE person (
    id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(32) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(32) NOT NULL,
    INDEX idx_name_age (name, age)
) engine=InnoDB;
INSERT INTO person(name, age, city) VALUES ('Alice', 14, 'Beijing'), ('Bob', 15, 'Shenzhen'), ('Eve', 9, 'Shanghai');

在 InnoDB 中，每个索引对应一棵 B+ 树，主键索引的叶子节点存放完整的数据行（如上面的 id、name、age 和 city），即表数据是存储在主键索引上的，这种存储方式称为索引组织表（Index Organized Table），主键索引也被称为聚簇索引（Clutered Index）。非主键索引的叶子节点存储的是主键的值，所以非主键索引也被称为二级索引（Secondary Index）。

所以在主键索引和非主键索引（普通索引）上面的查询会有一些区别，例如：

SELECT * FROM person WHERE id = 1;

这个查询只需要扫描主键索引树即可获取全部数据，而下面的语句：

SELECT * FROM person WHERE name = 'Alice';

需要先扫描 idx_name_age 索引树，获取符合条件的 id 值，然后再拿这个 id 值扫描主键索引树获取完整的数据行，这个过程叫做回表。也就是说，基于非主键索引的查询需要多扫描一棵索引树，所以在实际应用中应该尽量使用主键查询。

覆盖索引

上面基于 idx_name_age 索引的查询，之所以需要回表，是因为这个索引上面没有 city 列的信息，如果只是查询 id、 name 或 age 列，应该就不需要回表了，从下面的执行计划就可以验证我们的猜测（Extra 的值是 Using index）：

EXPLAIN SELECT id, name, age FROM person WHERE name = 'Alice';

id            | 1
select_type   | SIMPLE
table         | person
partitions    | <null>
type          | ref
possible_keys | idx_name_age
key           | idx_name_age
key_len       | 130
ref           | const
rows          | 1
filtered      | 100.0
Extra         | Using index

在上面的查询中，idx_name_age 索引覆盖了查询需求，就称 idx_name_age 索引为覆盖索引（Covering Index）。由于覆盖索引可以减少树搜索的次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀

B+ 树这种索引结构可以利用索引 key 的「最左前缀」来定位记录，如下面的查询：

EXPLAIN SELECT * FROM person WHERE name LIKE 'A%';

id            | 1
select_type   | SIMPLE
table         | person
partitions    | <null>
type          | range
possible_keys | idx_name_age
key           | idx_name_age
key_len       | 130
ref           | <null>
rows          | 1
filtered      | 100.0
Extra         | Using index condition

可以看到，执行计划中的 key 的值是 idx_name_age， type 值是 range，说明这个查询是一个使用 idx_name_age 索引的范围查询。由于「最左前缀原则」，如果只按照 age 查询理论上应该是不能利用索引的，如下面的查询：

EXPLAIN SELECT * FROM person WHERE age = 14;

id            | 1
select_type   | SIMPLE
table         | person
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 3
filtered      | 33.33
Extra         | Using where

从执行计划可以看出，其实是进行了全表扫描，没有用到索引，验证了「最左前缀原则」。

索引下推

前面 LIKE 查询的执行计划中，Extra 的值是 Using index condition，这表示查询使用了索引下推（Index Condition Pushdown）优化。这个查询是需要回表的，索引下推是指：在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。LIKE 查询的例子不是特别显著，来看下面的查询：

EXPLAIN SELECT * FROM person WHERE name = 'Alice' and age > 10;

id            | 1
select_type   | SIMPLE
table         | person
partitions    | <null>
type          | range
possible_keys | idx_name_age
key           | idx_name_age
key_len       | 134
ref           | <null>
rows          | 1
filtered      | 100.0
Extra         | Using index condition

查询过程中，不满足 WHERE 条件的记录直接被过滤掉，不需要回表。

独立的列

如果查询中的列不是独立的，那么 MySQL 就不会使用索引，所谓「独立的列」是指索引列不能是表达式的一部分，也不能是函数的参数。下面的查询就使用了非独立的列，从执行计划中可以看出 MySQL 没有使用索引：

EXPLAIN SELECT * FROM person WHERE id + 1 = 2;

id            | 1
select_type   | SIMPLE
table         | person
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 3
filtered      | 100.0
Extra         | Using where
0%