MySQL索引

问题引入

1. 查询时条件字段加索引为什么比不加索引快？
2. 为什么MySQL建议使用自增主键而非UUID主键？
3. 什么是二级索引？
4. 为什么建立了联合索引但只匹配索引后边的列就无法使用索引？

没有索引的查找

  例子：SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

以主键为搜索条件

  定位到记录所在的页（如果数据只有一页，则不需要此步骤），在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

以其他列作为搜索条件

  从第一页开始查找，每页中从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。

使用索引之后的查找

索引背后的设计

1. 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。在对页中的记录进行增删改操作的过程中，必须通过一些诸如记录移动的操作来始终保证这个状态一直成立，当页满的时候，就会产生页分裂。
2. 数据存储在页中，为所有的页建立目录项（目录其实就是索引了）。
3. 目录项的存储：复用之前存储用户记录的数据页来存储目录项，每个目录项记录只存储主键值和对应的页号，页满则进行页分裂。InnoDB使用记录头信息里的record_type属性来区分普通的用户记录和目录项记录

record_type 值含义：
0：普通的用户记录
1：目录项记录
2：最小记录
3：最大记录

4. 表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢？其实也简单，为这些存储目录项记录的页再生成一个更高级的目录。
   
5. 上述其实组成了传说中的B+树，实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。

聚簇索引

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

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

  二是B+树的叶子节点存储的是完整的用户记录（包括隐藏列）。

  InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

查找过程如下：
1. 确定目录项记录页。
2. 通过目录项记录页确定用户记录真实所在的页。
3. 在真实存储用户记录的页中定位到具体的记录。

二级索引

  按照其他条件（非主键）建立的B+树（索引）就是二级索引。
  使用记录索引列的大小进行记录和页的排序，这包括三个方面的含义：

1.页内的记录是按照索引列的大小顺序排成一个单向链表。
2.各个存放用户记录的页也是根据页中记录的索引列大小顺序排成一个双向链表。
3.存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的索引列大小顺序排成一个双向链表。

  两个特点：
  一是B+树的叶子节点存储的并不是完整的用户记录，而只是索引列+主键这两个列的值。
  二是目录项记录中不再是主键+页号的搭配，而变成了索引列+页号的搭配。

查找过程如下：
1. 确定目录项记录页
2. 通过目录项记录页确定用户记录真实所在的页。
3. 在真实存储用户记录的页中定位到具体的记录。
4. 但是这个B+树的叶子节点中的记录只存储了索引列和主键两个列，所以我们必须**再根据主键值去聚簇索引中再查找一遍完整的用户记录**。这个过程叫做**回表**。

  因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引（英文名secondary index），或者辅助索引。

联合索引

  按照索引列1和索引列2的大小进行排序，这个包含两层含义：

1.先把各个记录和页按照索引列1进行排序。
2.在记录的索引列1相同的情况下，再用索引列2进行排序。

注意：
  每条目录项记录都由索引列1、索引列2、主键（主键为了保证唯一性）、页号这几个部分组成，各条记录先按照索引列1的值进行排序，如果记录的索引列1相同，则按照索引列2的值进行排序。
  B+树叶子节点处的用户记录由索引列1、索引列2和主键组成。

B+树的形成过程

1. 每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
2. 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
3. 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

MyISAM中的索引

  索引和数据分开存储：

1. 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。MyISAM记录也需要记录头信息来存储一些额外数据。
2. 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！所以MyISAM中建立的索引相当于全部都是二级索引！
3. 如果有需要的话，我们也可以对其它的列分别建立索引或者建立联合索引，原理和InnoDB中的索引差不多，不过在叶子节点处存储的是相应的列 + 行号。这些索引也全部都是二级索引。

问题解答

1. 查询时条件字段加索引为什么比不加索引快？因为加了索引以后相当于有了一个目录，就像字典目录一样，当然很快。
2. 为什么MySQL建议使用自增主键而非UUID主键？因为B+树每个节点内的记录都是按照索引列的值从小到大的顺序形成的单链表，自增主键都是插入链表末尾，而UUID主键需要比较记录的主键值，容易产生记录移动以及页分裂现象，导致性能降低。
3. 什么是二级索引？按照其他条件（非主键）建立的索引就是二级索引，二级索引的叶子节点包含的用户记录由索引列和主键组成，所以使用二级索引查询需要找到主键值之后再到聚簇索引中查找完整的用户记录，也就是回表。
4. 为什么建立了联合索引但只匹配索引后边的列就无法使用索引？B+树的每个节点之间以及节点内记录都是先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。所以如果只匹配索引后边的列，就无法使用索引。

参考资料

  MySQL 是怎样运行的：从根儿上理解 MySQL