索引的使用

索引的本质

索引就是帮助数据库管理系统高效获取数据的数据结构。

思考？

索引用得好，可以将 SQL 查询的效率提升 10 倍甚至更多。但索引是万能的吗？既然索引可以提升效率，只要创建索引不就好了吗？

实际上以下情况，创建索引反而会降低效率。

1. 数据量比较小，比如行数小于1000
2. 在数据重复大的字段上添加索引

索引类型

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引和全文索引。按照物理实现方式，索引可以分为 2 种：聚集索引和非聚集索引。我们也把非聚集索引称为二级索引或者辅助索引。除了业务逻辑和物理实现方式，索引还可以按照字段个数进行划分，分成单一索引和联合索引。

普通索引

普通索引是基础的索引，没有任何约束，主要用于提高查询效率。

唯一索引

唯一索引就是在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。

主键索引

主键索引在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里最多只有一个主键索引。

全文索引

全文索引用的不多，MySQL 自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。

其实前三种索引（普通索引、唯一索引和主键索引）都是一类索引，只不过对数据的约束性逐渐提升。在一张数据表中只能有一个主键索引，这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。但可以有多个普通索引或者多个唯一索引。

聚集索引

InnoDB 的数据是按照主键顺序存放的，而聚集索引就是按照每张表的主键构造一颗 B+ 树，它的叶子节点存放的是整行数据。

InnoDB 的主键一定是聚集索引。如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，也有可能是 row id。

由于实际的数据页只能按照一颗 B+ 树进行排序，因此每张表只能有一个聚集索引（TokuDB 引擎除外）。查询优化器倾向于采用聚集索引，因为聚集索引能够在 B+ 树索引的叶子节点上直接找到数据。

聚集索引对于主键的排序查找和范围查找速度非常快。

非聚集索引

InnoDB 存储引擎辅助索引的叶子节点并不会放整行数据，而存放的是键值和主键 ID。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引树查找到对应记录的主键，然后通过主键索引来找到对应的行数据。

我们能看出辅助索引的查询比主键查询多扫描一颗索引树，所以，我们应该尽量使用主键做为条件进行查询。

思考：什么是回表？它是如何引起的？

因为二级索引（辅助索引）并不存储我们想要SELECT的具体数据，因此是通过二级索引想找到主键，然后再通过主键找到具体的数据。这种方式也称之为回表，是因为索引没有对SELECT查询的字段进行完全覆盖。

区别

聚集索引与非聚集索引的原理不同，在使用上也有一些区别：
非聚集索引不会影响数据表的物理存储顺序。一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

思考：为何使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低呢 ？

这是因为聚集索引是面向读取的设计，因为我们的数据会按照聚集索引的大小顺序写入到磁盘，因此聚集索引会存在存储顺序的问题。而我们更新，插入的内容往往都是随机的，这时如果我们还是用聚集索引，所有的记录就需要重新进行排序并重新写入到磁盘中，所以效率相比于非聚集索引可能会降低。
而非聚集索引只是存储索引，我们只需要更新这个索引即可，不需要对所有的记录重新排序。

单一索引和联合索引

索引列为一列时为单一索引；多个列组合在一起创建的索引叫做联合索引。
这里需要说明的是联合索引存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。比如举例的 (x, y, z)，如果查询条件是 WHERE x=1 AND y=2 AND z=3，就可以匹配上联合索引；如果查询条件是 WHERE y=2，就无法匹配上联合索引。

索引的原理： B+树结构

B+ 树索引就是基于B+ 树发展而来的。在数据库中，B+ 树的高度一般都在 2 ~ 4 层，所以查找某一行数据最多只需要 2 到 4 次 IO。而没索引的情况，需要逐行扫描，明显效率低很多，这也就是为什么添加索引能提高查询速度。

B+ 树索引并不能找到一个给定键值的具体行，B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到缓冲池（buffer pool）中，在内存中通过二分查找法进行查找，得到需要的数据。

InnoDB 中 B+ 树索引分为聚集索引和辅助索引。

思考：B+ 树和 B 树在构造和查询性能上有什么差异呢？

B+ 树的中间节点并不直接存储数据。

1. B+树的查询效率更加稳定：
   由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

2. B+树的磁盘读写代价更低：
   B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

3、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

为什么 MySQL 使用 B+ 树