MySql-索引

没有索引的情况

在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

• 以主键为搜索条件

在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

• 以其他列作为搜索条件

对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

在很多页中查找

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：

定位到记录所在的页。
从所在的页内中查找相应的记录。
在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是非常耗时的，这时候就要用到索引了。

InnoDB中的索引方案

聚簇索引

InnoDB是使用页来作为管理存储空间的基本单位，也就是最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。

InnoDB中，目录页索引复用了最基础的页。只不过目录项中的两个列是主键和页号而已，所以他们复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。

同时，InnoDB会将所有的目录项记录都存放到一张叫做聚集索引的B+树中，这棵B+树的叶子节点就是目录项记录，而非叶子节点则是目录项记录的页号。这样，我们就可以通过B+树来快速的定位到目录项记录所在的页，然后再根据目录项记录中的页号找到对应的数据页，最后再在数据页中查找相应的记录。

我们上边介绍的B+树本身就是一个目录，或者说本身就是一个索引。它有两个特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

1. 页内的记录是按照主键的大小顺序排成一个单向链表。

2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建（后边会介绍索引相关的语句），InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

二级索引

上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件时，这时候就需要用到二级索引了。

我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。如下图

这颗B+树与聚类索引的树有以下几方面的不同

1. 页内的记录是按照索引参数的大小顺序排成一个单向链表。

2. 各个存放用户记录的页也是根据页中记录的索引参数大小顺序排成一个双向链表。

3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的索引参数列大小顺序排成一个双向链表。

4. B+树的叶子节点存储的并不是完整的用户记录，而只是索引参数+主键这两个列的值。

5. 目录项记录中不再是主键+页号的搭配，而变成了索引参数列+页号的搭配

因为这个B+树的叶子节点中的记录只存储了索引参数和主键两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍，才能得到完整的用户记录。这个过程也被称为回表。因为需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引（英文名secondary index），或者辅助索引。

为什么不能把用户记录放到叶子节点中

如果把完整的用户记录放到叶子节点是可以不用回表，但是太占地方。相当于于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍。

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引,此时建立的索引叫做联合索引。但是其任然属于二级索引的一种。

每条目录项记录都由c2、c3、页号这三个部分组成，页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。

InnoDB的B+树索引的注意事项

根页面万年不动窝

一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

内节点中目录项记录的唯一性

为了让新插入记录能找到自己在那个页里，我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

• 索引列的值
• 主键值
• 页号

这样我们再插入记录(9, 1, ‘c’)时，由于页3中存储的目录项记录是由c2列 + 主键 + 页号的值构成的，可以先把新记录的c2列的值和页3中各目录项记录的c2列的值作比较，如果c2列的值相同的话，可以接着比较主键值，因为B+树同一层中不同目录项记录的c2列 + 主键的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录，在本例中最后确定新记录应该被插入到页5中。

一个页面最少存储2条记录

我们前边说过一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度杠杠的！这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是个啥效果呢？那就是目录层级非常非常非常多，而且最后的那个存放真实数据的目录中只能存放一条记录。费了半天劲只能存放一条真实的用户记录？逗我呢？所以InnoDB的一个数据页至少可以存放两条记录。

B+树索引的使用

使用的代价

• 空间上的代价

这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那可是很大的一片存储空间呢。

• 时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这还能不给性能拖后腿么？

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

B+树索引适用查找匹配的情况

全值匹配
如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

在查询过程中会按照name、birthday、phone_number的顺序进行查找，因为这三个列都是联合索引的一部分，所以可以快速的定位到目录项记录所在的页，然后再根据页号找到对应的数据页，最后再在数据页中查找相应的记录。

其中,查询的顺序对索引是没有影响的,MySQL有一个叫查询优化器,可以优化查询语句.

匹配左边的列

在搜索中,包含匹配左边的项,就可以使用到这个索引,原因和上面的一样,mysql会按照索引从左到右的顺序进行查找.

匹配范围值

在搜索中,包含匹配范围值,就可以使用到这个索引,原因和上面的一样,mysql会按照索引从左到右的顺序进行查找.

字符串前缀匹配

在搜索中,字符串是按照其的值进行排序,所以可以使用到这个索引进行排序,但是如果使用到了字符串的后缀,就不能使用到这个索引了.

精确匹配某一列并范围匹配另外一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找.

用于排序

使用ORDER BY子句按照某种规则进行排序时,一般情况下，我们只能把记录都加载到内存中(或者文件做暂存)，通过排序算法对其进行排序，然后再返回给客户端。

但是如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤,直接输出排序结果

用于分组

然后针对那些分组进行统计，比如在我们这个查询语句中就是统计每个分组包含的记录条数。如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的B+树中的索引列的顺序是一致的，而我们的B+树索引又是按照索引列排好序的，这不正好么，所以可以直接使用B+树索引进行分组。

使用索引的总结

能不能使用索引关键在于查询的列是否是索引的一部分,同时其排序与索引结构相同.那么就可以使用索引，如果不是索引的一部分，那么就不能使用索引。

回表的代价

回表的代价是非常高的，因为回表的过程需要再次访问数据页，而且还需要再次访问数据页中的目录项记录，这个过程需要进行两次磁盘IO操作，所以回表的代价是非常高的。

在mysql中有查询优化器,查询优化器会事先对表中的记录计算一些统计数据,然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数,需要回表的记录数越多，就越倾向于使用全表扫描,反之倾向于使用二级索引 + 回表的方式.

为了彻底告别回表操作带来的性能损耗，我们最好在查询列表里只包含索引列.

如何建立索引

只为用于搜索、排序或分组的列创建索引

只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引。

考虑列的基数

列的基数指的是某一列中不重复数据的个数,其中基数越大,索引的效果越好.因为当基数小的时候,数的离散程度越小,在排序的时候,需要进行的比较次数越少多,所以效率越低.

索引列的类型尽量小

• 数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东东）

• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

索引字符串值的前缀

原因同上,对于字符串只存储前缀,可以减少索引的大小,从而减少磁盘I/O带来的性能损耗.

让索引列在比较表达式中单独出现

如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

主键插入顺序

当向项中间插入一个页面时候,如果该页面已经存满,此时会发生页面分裂和记录移位,此时就会发生性能损耗.因此最好将主键设置为自动递增的形式

减少冗余索引

给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。