数据库索引

Owen大约 15 分钟

索引是为了加速对表中数据行的索引而创建的一种分散的存储结构。索引时针对表而建立的，他是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，指向对应的数据行，以便加速检索物理数据。常见的索引结构有：B树、B+ 树和 Hash。

索引的作用相当于文件目录。打个比方：我们在查字典的时候，如果没有目录，那么我们就只能一页一页的去找我们需要查的那个字，速度会很慢。如果有了目录，我们只需要先去目录里面查找字的位置，然后直接翻到那一页就行了。

索引的优缺点

优点

使用索引可以大大加快数据的检索速度（其实是减少了检索的数据量），这也是创建索引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
加快表与表之间的连接。
减少分组排序的时间。

缺点

创建索引和维护索引需要耗费大量的时间成本。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 的执行效率。
索引也需要使用物理文件存储，所以会耗费一定的存储空间。

在我们的实际应用中，使用索引一定能提高查询性能吗？

大多数情况下，索引查询都是比全表扫描要快的。如果数据库的数据量不大，那么使用索引所带来的速度提升就不那么明显了，反倒是加大维护成本。

索引的底层数据结构

Hash 表 & B+ 树

哈希表是键值对的集合，通过键（key）即可快速取出对应的值（value），因此哈希表可以快速检索数据（时间复杂度进阶 O(1)）。

为何能够通过 key 快速取出 value 呢？原因在于哈希算法（散列算法）。通过哈希算法，我们可以快速的找到 value 对应的 index，找到了 index 也就可以找到对应的 value。

hash = hashfunc(key)
index = hash % array_size

但是呢，哈希算法有一个问题 - 哈希冲突，也就是说多个不同的 key 经哈希计算之后得到的index 可能是相同的。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中，当发生哈希冲突的时候，会将相同的 index 对应的 value 用链表的方式存储。就比如 JDK 1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的；不过在 JDK 1.8 以后 HashMap 为了解决链表过长带来的性能问题而采用了 红黑树。

为了减少 Hash 冲突的发生，一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。

既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？

Hash 冲突问题：我们上面也提过 Hash 冲突，不过对于数据库来说这并不是最大的缺点；
Hash 索引不支持顺序和范围查询：Hash 索引不支持顺序和范围查询是最大的缺点，如果我们需要对表中的数据进行排序或者进行范围查询，那么 Hash 索引就不可行了。
试想一种情况：
```
SELECT * FROM tb1 WHERE id < 500;
```
在这种范围查询中，直接遍历 id 比 500 小的叶子节点就够了；但是对于 hash 索引就没有那么简单了，上面我们说过，hash 索引是通过 hash 算法来定位对应的值的，在这种情况下 hash 索引应如何去定位 id（key）对应的值（value）呢？难道将 1 - 499 的数据，每个都进行一次 hash 计算吗？显然这是不成立的，这也就是它作为索引最大的缺陷。

B 树 & B+ 树

B 树也称 B- 树，全称为 多路平衡查询树，B+ 树是 B 树的一种变体。B 树和 B+ 树中的 B 是 Balanced（平衡）的意思。

目前大部分数据库以及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树 & B+ 树两者有何异同

B 树的所有节点即存放键（key）也存放数据（data），而 B+ 树只有叶子节点存放 key 和 data，其它内节点只存放 key。
B 树的叶子节点都是独立的；B+ 树的叶子节点有一条引用链指向与它相邻的叶子节点；
B 树的检索过程相当于对范围内的每个节点的关键字做 二分查找，可能还没有到达叶子节点，检索就结束了。而 B+ 树的检索效率就很稳定了，任何查询都是从根节点到叶子节点的过程，叶子节点的顺序检索是很明显的。
在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。（下面的内容整理自《Java 工程师修炼之道》）
MyISAM 引擎中，B+Tree 叶子节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 key 存在，则取出其 data 域的值，然后以 data 域的值作为地址读取相应的数据记录。这被称为 非聚簇索引。
InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶子节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为 聚簇索引（聚集索引），而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可去除数据；在根据辅助索引查找时，则需要选出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

索引类型

主键索引（Primary Key）

数据表的主键列使用的就是 主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动间检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引（辅助索引）

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

唯一索引（Unique Key）：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了确保该属性的数据在表中的唯一性，而不是为了查询效率。
普通索引（Index）：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
前缀索引（Prefix）：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符串创建索引，相比普通索引建立的数据更小，因为只取前几个字符串。
全文索引（Full Text）：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。MySQL 5.6 之前只有 MyISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

二级索引

聚集索引与非聚集索引

聚集索引

聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd 文件就包含了该表的索引和数据，对于 InnoDB 引擎来说，该表的索引（B+Tree）的 每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

聚集索引的缺点

聚集索引的查询速度非常的快，因为整个 B+Tree 本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点

依赖于有序的数据：因为 B+Tree 是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
更新代价大：如果对索引列的树被修改时，那么对应的索引也将会被修改，何况聚集索引的叶子节点还存放着数据，修改代价肯定是比较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引

非聚集索引即索引结构和数据分开存放的索引。

二级索引属于非聚集索引。

MyISAM 引擎的表的 .MYI 文件包含了表的索引，该表的索引（B+Tree）的每个非叶子节点存储索引，叶子节点存储索引和索引对应数据的指针，只想 .MYD 文件的数据。

非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点存放的就是主键，可以根据主键回表查询数据。

非聚集索引的优点

更新代价比聚集索引要小。非聚集索引的更新代价就没有聚集索引那么大了，因为非聚集索引的叶子节点是不存放数据的。

非聚集索引的缺点

跟聚集索引一样，非聚集索引也依赖于有序的数据
可能会二次查询（回表）：这应该是非聚集索引最大的缺点了。当查询索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

这是 MySQL 的表的文件截图：

聚集索引和非聚集索引:

非聚集索引一定回表查询吗（覆盖索引）？

非聚集索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

SELECT name FROM table WHERE name='guang19';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MyISAM 也是这样，虽然 MyISAM 的主键索引确实需要回表，因为它的主键索引的叶子节点存放的是指针。但是如果 SQL 查询的就是主键呢？

SELECT id FROM table WHERE id=1;

主键索引本身的 key 就是主键，查到直接返回就行了。这种情况就称之为 覆盖索引。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引。在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键 + 列值，最终还是需要 ”回表“ 的，也就是要先通过主键再查找一次，这样就会比较慢。覆盖索引就是待查询的列和索引是对应的，不需要做回表操作。

提示

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引就可以直接查询到数据，而无需回表查询。对于主键查询，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查询到主键；对于普通索引，如果一条 SQL 需要查询的是 name，name 字段刚好有索引，那么直接根据这个索引就可以查到数据，也无需回表。

覆盖索引：

创建索引的注意事项

选择合适的字段创建索引：
- 不为 null 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0、1、true、false 这样语义较为清晰的短值或段字符串作为替代。
- 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
- 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
- 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询的时间。
- 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表之间的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
被频繁更新的字段应该慎重简历索引。
虽然索引能带来查询上的高效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就不应该在该字段上建立索引。
尽可能的考虑建立联合索引而不是单列索引覆盖索引。
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+Tree。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量之后，索引所占用的磁盘空间也会很大，并且在修改索引时，耗费的时间也是比较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大的磁盘空间，并且修改数据的操作效率也会得到提升。
注意避免冗余索引。
冗余索引指的是索引的功能相同，能够命中索引（a，b）肯定能命中索引（a），那么索引（a）就是冗余索引。如（name，city）和（name）这两个索引就冗余了，因为能够命中前者的查询肯定能够命中后者。在大多数情况下，都应该尽量扩展已有的索引而不是创建新的索引。
考虑在字符串类型的字段上使用前缀索引代替普通索引。
前缀索引仅限与使用在字符串类型的字段上，前缀索引较普通索引占用的存储空间更小，所以在字符串类型的字段上添加索引时，可以考虑使用前缀索引来代替普通索引。

使用索引的一些建议

对于中、大型表索引是非常有效的，但是对于特大型表的话索引的维护开销会很大，不适合建索引；
避免 where 子句中对字段施加函数，这会造成无法命中索引；
在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键；
删除长期未使用的索引，无用索引的存在会造成不必要的性能损耗，MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexs 视图来查询那些索引从未被使用过；
在使用 limit offset 查询缓慢时，可以借助索引来提高性能。

MYSQL 如何为表字段添加索引？

1.添加 PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

2.添加 UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` );

3.添加 INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` );

4.添加 FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`);

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );

摘自 JavaGuide 点击此处查看原文