MySQL之索引

MySQL索引

前言：本文主要梳理整合了包括：索引的概念、作用、优缺点，索引的底层数据结构（Hash表、B树、B+树），索引的类型（主键索引、辅助索引、聚集索引、非聚集索引、覆盖索引），联合索引的最左匹配原则，以及创建索引的注意事项。具体内容如下所示。

索引的概念

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。如果没有索引，一般来说执行查询的时候需要遍历整张表。 （索引的作用就相当于我们平时用的目录，通过目录快速定位到查找目标的位置。如果没有目录我们只能一页一页翻，速度十分慢）

• 索引的核心原理:就是把无序的数据变成有序的查询

索引的优缺点

优点 ：

• 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

• 创建和维护索引需要耗费许多时间。当我们对表中的数据进行增删改查时，如果数据有索引，那么索引也需要动态修改，就会降低SQL的执行效率。
• 索引需要使用物理文件存储，也会耗费一定的空间。

使用索引不一定能提高查询性能

大多数情况下，通过索引查询是要比全表扫描快的。但是如果数据量不大的话，使用索引也不一定能带来很大提升。

索引的底层数据结构

索引可以通过Hash表、B树、B+树来实现。

Hash表

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

能通过key快速取出value的原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。但是，哈希算法可能产生Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如在JDK1.8以前，hashMap就是通过链地址法来解决哈希冲突的。

具体实现如下图所示：

MySQL 没有使用其作为索引的数据结构的原因

Hash表实现索引的缺点：

• Hash索引不支持顺序查询和范围查询，（像like ‘xxx%’ 这样部分的模糊查询其本质也是范围查询），因为原先是有序的键值，经过哈希算法后，可能变得不连续了，所以导致不支持。
• 会产生Hash冲突。
• 哈希索引也不支持多列联合索引的最左匹配规则。

tip：但如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；前提 键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

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+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

B+Tree在MyISAM 引擎和 InnoDB 引擎的实现方式的区别

mysql5.5之前都采用myisan作为默认引擎，5.6开始采用Innodb。

• 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会自动检查表中是否含有唯一索引且不允许有null值的字段，若有，则选该字段为默认主键，否则InnoDB会自动创建一个6Byte的自增主键。

二级索引(辅助索引)

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

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

• 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

  当索引是很长的字符序列时，这个索引将会很占内存，而且会很慢，这时候就会用到前缀索引了。

• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字，是目前搜索引擎数据库使用的一种技术。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引（inverted index/也叫作反向索引）实现，它记录着关键词到其所在文档的映射。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

• 组合索引(联合索引): 即将数据库表中的多个字段联合起来作为一个组合索引。

聚集索引与非聚集索引

聚集索引（聚簇索引）

聚集索引将数据存储与索引放到了一起、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。一张表只允许存在一个聚簇索引，因为真实数据的物理顺序只能有一种。一般情况下主键就是默认的聚簇索引。

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

聚集索引的优点：

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

聚集索引的缺点：

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

非聚集索引（辅助索引）

非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚簇索引。

非聚集索引的叶子节点并不存放数据，而是存放对应的主键的值，根据主键再回表（在主索引中）查数据。

非聚集索引的优点：

• 更新代价比聚集索引小，也是因为非聚集所以的叶子节点是不存放数据的。

非聚集索引：

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

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

• 非聚集索引不一定回表查询。可能查询的字段正好建立了索引。

比如，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

 SELECT name FROM table WHERE name='guang19';

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

• 即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表， 因为它的主键索引的叶子节点存放的是指针。但是如果查询的就是主键，主键索引本身的 key 就是主键，查到返回就行了。这种情况也就是我们下面说的覆盖索引了。
  如下情况：

SELECT id FROM table WHERE id=1;

覆盖索引

• 定义：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。（如上面的例子）

最左前缀匹配原则

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会从左一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

最左匹配原则与联合索引的索引存储结构和检索方式是有关系的。

比如：组合索引idx_abc(a,b,c)，在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无 序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况 下小范围内递增有序。

可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。

此外，比如一个简单的查询，查询字段从左到右依次为a、b，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a 列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。也就造成索引失效，导致全表扫描。

创建索引的注意事项

1.选择合适的字段创建索引：

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.不建议在频繁更新的字段上建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)，在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

6.非必要不要进行 JOIN 查询，如果要进行 JOIN 查询，被 JOIN 的字段必须类型相同，并建立索引。

因为 JOIN 字段类型不一致的话会导致全表扫描。

Tip：以上为个人借鉴各种资料的梳理总结，如有侵犯，请联系删除。