Skip to main content

索引

索引是关系型数据库中用来提升查询性能的一种数据结构。关系型数据库中的索引就像一本书的目录，我们可以想象一下，如果要从一本书中找出某个知识点，但是这本书没有目录，这将是多么可怕的事情！我们估计得一篇一篇的翻下去，才能确定这个知识点到底在什么位置。

tdNT1Y

创建索引虽然会带来存储空间上的开销，就像一本书的目录会占用一部分篇幅一样，但是在牺牲空间后换来的查询时间的减少也是非常显著的。

MySQL 数据库中所有数据类型的列都可以被索引。对于 MySQL 8.0 版本的 InnoDB 存储引擎来说，它支持三种类型的索引，分别是 B+ 树索引、全文索引和 R 树索引。这里，我们只介绍使用得最为广泛的 B+ 树索引。使用 B+ 树的原因非常简单，因为它是目前在基于磁盘进行海量数据存储和排序上最有效率的数据结构。

B+ 树​

B+ 树是一棵平衡树，树的高度通常为 3 或 4，但是却可以保存从百万级到十亿级的数据，而从这些数据里面查询一条数据，只需要 3 次或 4 次 I/O 操作，固态硬盘每次 I/O 操作差不多 1ms。

B+ 树由根节点、中间节点和叶子节点构成，其中叶子节点用来保存排序后的数据。由于记录在索引上是排序过的，因此在一个叶子节点内查找数据时可以使用二分查找，这种查找方式效率非常的高。

当数据很少的时候，B+ 树只有一个根节点，即只有一个页，该页既是根节点也是叶子节点，数据也就保存在根节点上。

JPZ1Wl

随着记录越来越多，B+ 树会发生分裂，根节点不再保存数据，而是提供了访问下一层节点的指针，帮助快速确定数据在哪个叶子节点上。

PaOx2i

3 层高度，数据也是放在叶子节点，中间节点用来存放地址：

GKRL9k

每个节点是 16k 大小的存储空间，大小是固定的：

SXp4i4

假设主键用的是 bigint，它占 8 字节，地址占用 6 个字节，16*1024/(8+6)=1100 即根节点大约可以分出 1100 个分支，第二层每个节点再分出 1100 个分支，即叶节点个数为 1100*1100=121w。

假设每个叶节点中每条记录大约占用 5000 字节，16*1024/5000=32 即每个叶节点可以存储大约 32 条记录，3 层 B+树总记录数约为 3872w 条，这是理想情况，有可能每条记录不止 5000 字节，或者每个节点的空间利用率没有达到 100%，即 16k 没有占满，假设空间利用率在 60%，大约有 1000w 条记录，差不多 3 层 B+树可以支持百万级数据。

tip

B+树适合用来存储海量数据，是非常有效率的一种存储结构：

  • 树（层次结构）
  • 平衡树
  • 3-4 层（3-4 次 I/O 操作，固态硬盘即 5ms 内可以拿到数据）

了解的数据库索引底层的 B+树基本原理，那我们如何知道写的查询语句速度快不快呢？抛开直观感觉，如何从科学的角度来判别呢？这就需要用到数据库的执行计划。

执行计划 EXPLAIN​

EXPLAIN SELECT * FROM student WHERE name = '项少龙';
Zf9b5r
EXPLAIN SELECT * FROM student WHERE id = 3755;
hVZbMV
  • select_type: 查询的类型
    • SIMPLE: 简单 SELECT，不需要使用 UNION 操作或子查询，无疑是性能最好的。
    • PRIMARY: 如果查询包含子查询，最外层的 SELECT 被标记为 PRIMARY。
    • UNION: UNION 操作中第二个或后面的 SELECT 语句。
    • SUBQUERY: 子查询中的第一个 SELECT。
    • DERIVED: 派生表的 SELECT 子查询。
  • type: 最关键，MySQL 在表中找到满足条件的行的方式，也称为访问类型，以下顺序就是查询性能从坏到好:
    • ALL: 全表扫描，性能最差的，它代表的全表扫描是指要扫描表中的每一行才能找到匹配的行。
    • index: 索引全扫描，只遍历索引树，基本上也不能容忍和接受。
    • range: 索引范围扫描，一般查询优化至少要到 range。
    • ref: 非唯一索引扫描。
    • eq_ref: 唯一索引扫描。
    • const/system : 常量级查询。
    • NULL: 不需要访问表或索引，比如 SELECT 'x' 很少用。
  • possible_keys: MySQL 可以选择的索引，但是有可能不会使用。
  • key: MySQL 真正使用的索引，如果为 NULL 就表示没有使用索引。
  • key_len: 使用的索引的⻓度，在不影响查询的情况下肯定是⻓度越短越好。
  • rows: 执行查询需要扫描的行数，这是一个预估值。
  • extra: 关于查询额外的信息。
    • Using index: 只使用索引的信息而不需要进一步查表来获取更多的信息。
    • Using filesort: MySQL 无法利用索引完成排序操作。
    • Using temporary: MySQL 需要使用临时表来存储结果集，常用于分组和排序。
    • Impossible where: where 子句会导致没有符合条件的行。
    • Distinct: MySQL 发现第一个匹配行后，停止为当前的行组合搜索更多的行。
    • Using where: 查询的列未被索引覆盖，筛选条件并不是索引的前导列。
tip

主键上默认有索引，MySQL 的 InnoDB 引擎用的是索引组织表结构，整张表的数据就是放在主键索引这棵 B+ 树上的。

思考：虽然用主键 id 去查一个学生比用名字去查效率要高，但现实情况就是，一般都是用名字去查询，比如在购物网站上买东西时候，输入的不是商品编号是商品名称，那该怎样用名字去查的时候不是全表扫描呢？

创建索引​

CREATE INDEX idx_student_name ON student (name);

然后在看下之前 SQL 查询语句的执行计划：

EXPLAIN SELECT * FROM student WHERE name = '项少龙';
AR4BPe

删除索引​

DROP INDEX idx_student_name ON student;

或者：

ALTER TABLE student DROP INDEX idx_student_name;

索引失效​

EXPLAIN SELECT * FROM student WHERE name LIKE '项%';
9ikTbm
EXPLAIN SELECT * FROM student WHERE name REGEXP '项.*';
ZEECIv
tip

一般不写正则表达式，可能会导致查询变得很慢。

EXPLAIN SELECT * FROM student WHERE name LIKE '%项%';
GXHpWg
EXPLAIN SELECT * FROM student WHERE name != '项少龙';
Xu3z42
tip

查询的时候尽量使用正向条件，减少使用反向条件。

前缀索引​

CREATE INDEX idx_student_name ON student (name(1));

表示在 name 上对前 1 个字符做索引，此时我们再执行一下：

EXPLAIN SELECT * FROM student WHERE name = '项少⻰';
enTOAA

MySQL 先把姓”项“的找到，然后再从这两个中找到哪个是”项少⻰“，这种前缀索引会节省一点空间，浪费一点时间，具体需要在实际业务中进行权衡，比如 name 是一个很长的 VARCHAR，就没有必要给所有的字符都建立索引。

聚集索引​

主键上的索引我们称之为聚集索引（Clustered Index）或者叫主键索引，对于索引组织表这种存储结构，索引就是整张表。

一张表的聚集索引只有一个，而且只能在主键上，这也侧面说明，一张表只能有一个主键，要是有两个聚集索引，相当于把整张表在磁盘上存了两次，完全没有必要。

二级索引​

开发者自己手动建立的索引都是二级索引或者叫非聚集索引，之前我们通过 name 去查找学生的其他字段时候，其实是先通过名字找到对应的主键，再通过主键索引查找到对应记录的其他字段，这个过程也称作“回表”。

kRI9fp

思考：回表也是要花时间的，那如果不想花这个时间怎么办？

复合索引​

如果查找某个学生并不是为了获得所有字段信息，而是某个字段，比如 “address”， 这时我们就可以建立复合索引(MySQL 5.7 开始支持)，即基于多个字段创建索引。用好复合索引实现索引覆盖可以减少不必要的排序和回表操作，这样就会让查询的性能成倍的提升。

CREATE INDEX idx_student_name ON student (name, address);

这样再去查找 “address” 的时候，就不需要回表，直接在二级索引中即可直接拿到 “address”。

SELECT name, address FROM student WHERE name = '项少⻰';
EXPLAIN SELECT name, address FROM student WHERE name = '项少⻰' AND address = '四川成都';
VzCPxo

AND 前后字段顺序无所谓，索引都是有效的：

EXPLAIN SELECT name, address FROM student WHERE address = '四川成都' AND name = '项少⻰';

如果只查 “address” 呢？

EXPLAIN SELECT name, address FROM student WHERE address = '四川成都';
mPBvXj

这个时候是索引全扫描，其实索引已然失效，达不到应有作用，相当于做全表扫描。

tip

使用复合索引时候，遵循最左匹配原则，可以使用 (name, address) 两个字段一起查，顺序无所谓，或者只使用第一个字段查，但不能只使用第二个字段查。

如果 AND 换成 OR 呢？

EXPLAIN SELECT name, address FROM student WHERE name = '项少⻰' OR address = '四川成都';
HCX8cI

OR 相当于使用了 SELECT name, address FROM student WHERE address = '四川成都';，所以最终还是索引全扫描，达不到索引应有效果。

索引排序​

建有索引的字段其实是排好序的，基于之前的复合索引 (name, address)，看一个例子：

EXPLAIN SELECT name, address FROM student WHERE name = '项少⻰' ORDER BY address;
yOmcNE

Using index 代表直接使用的是索引排序，再看下如果按照 “birth” 排序会是什么结果：

EXPLAIN SELECT name, address FROM student WHERE name = '项少⻰' ORDER BY birth;
HgDNge

Using filesort 使用的是外部排序，此时就要额外多一次排序操作，查询性能是要有所降低的。

tip

查询时候排序的列，最好能够被索引覆盖。

使用好复合索引，能够大幅优化查询性能，也是我们实际中使用比较多的。

参考资料​

  1. 面向数据分析师的 SQL 教程：创建和使用索引, by 骆昊