MySql-B+树索引与使用

一、索引

1.查询效率问题

在很多页中查找记录的情况下，不论是根据主键列或者非主键列进行查找，如果不能快速定位到记录所在的页，就只能从第一个页沿着双向链表一直往下找，然后在每一个页中针对主键或非主键分别采用二分法或遍历单向链表中每一条记录，这种方式可能需要遍历所有页以及页中所有记录，极为耗时。所以要快速定位到需要查找的记录所在的索引页，同样需要为索引页建立一个目录，也就是索引。

2.建立索引

建立目录，就需要让记录按照主键值从小到大排序。如果数据较多，在不同的页之间，那么下一个索引页中用户记录的主键值必须大于上一个页中用户记录的主键值。假如页1中的最大主键id是90并且页1中数据已满，此时插入一条id为89的数据，那么就需要分配一个新页2，把id=90的数据移到页2中，把id=89的数据插入到页1，以满足条件。在对页中的记录进行增删改操作的过程中，可能会需要通过记录移动的操作来保证这个状态一直成立，这个操作叫做页分裂，所以正常情况下，**建议数据的主键值由小到大递增，以减少页分裂情况的发生。在保证主键值从小到大排序后，就可以为页建立目录项，目录项只有最小主键值和页编号两个列。

3.InnoDB中的索引

InnoDB复用了存储用户普通记录（record_type=0）的索引页来存储目录项，且有以下特点：

1. record_type=1，代表目录项纪录
2. 数据只有主键值和页的编号两个列
3. 存储目录项记录的页中，主键值最小的目录项记录的min_rec_mask=1

其他情况则与存储用户普通记录（record_type=0）的索引页没有差别。有了目录之后，查询过程就是：

• 确定目录项记录页
• 通过目录项记录页定位到需要查找的记录所在的索引页
• 在页中定位到需要查找的记录

目录所在的页大小也为16KB，如果一个页不够就会再分配一个页；如果目录项记录页也产生了很多，那么就生成一个更高层级的目录。随着表中数据的增加，目录项纪录页也会越来越多，层级也会增加，直到最后成为一颗倒立的树，就是B+树。

在B+树中，每一个的索引页都是树的节点，存储普通记录（record_type=0）的节点都是B+树最底层的节点，也就是叶子节点；存放目录项（record_type=1）的节点称为非叶子节点；B+树最上边的节点称为根节点。

一般情况下，B+树都不会超过4层，那通过主键值去查找某条记录最多只需要做4个页面内的查找，在每个页面内有页目录，所以在页面内可以通过二分法实现快速定位记录，提升查询效率。

3.1 聚簇索引

InnoDB存储引擎会自动创建聚簇索引，并不需要显式创建。聚簇索引就是数据的存储方式，所有的用户记录都存储在了叶子节点。有两个特点：

• 使用记录主键值的大小进行记录和页的排序
• B+树的叶子节点存储的是完整的用户记录，存储了所有列的值（包括隐藏列）

3.2 二级索引

聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。如果想以非主键列作为搜索条件，可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则，这种B+树也被称为二级索引或者辅助索引。为某个列建立索引，其实就是在对应的B+树的记录中使用该列的值进行排序。二级索引与聚簇索引有几处不同：

• 使用记录索引列的大小进行记录和页的排序
• B+树的叶子节点存储的只是索引列+主键这两个列的值，如果想用索引列的值查找到完整的用户记录，必须再根据主键值去聚簇索引中查找，这个过程也被称为回表
• 目录项记录中是索引列+主键+页号，保证目录项记录的唯一性

根据非主键列的值查询一条完整的用户记录需要使用到2棵B+树，因为需要回表。

3.3 联合索引

可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，称为联合索引，本质上也是一个二级索引。

例如根据列1和列2建立联合索引，先把各个记录和页按照列1进行排序，在列1相同的情况下，采用列2进行排序；每条目录项记录都由列1、列2、页号这三个部分组成；叶子节点处的用户记录由列1、列2、主键组成；联合索引只会建立一颗B+树。

4.MyISAM中的索引

MyISAM的索引方案将索引和数据分开存储：将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件；把索引信息另外存储到一个称为索引文件的文件中。

MyISAM中建立的索引相当于全部都是二级索引。

二、索引相关sql

#创建表同时创建索引
CREATE TALBE 表名 (
    ...
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)
#修改表结构时添加索引
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);
#修改表结构时删除索引
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;
#KEY和INDEX是同义词，任意选一个就可以

三、索引的使用

1.索引的代价

索引建的多，就会导致：

• 占用更多的存储空间；
• 在增删改记录的时候就需要去改各个B+树索引，性能就越差；
• 增删改操作可能会对索引列排序造成破坏，导致存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作。

所以，索引并不是越多越好。

2.索引适用的条件

2.1 全值匹配

搜索条件中的列和索引列一致。搜索条件的顺序对查询结果没影响。

2.2 匹配左边的列

搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行。如果想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。

2.3 匹配列前缀

字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，只匹配它的前缀也可以快速定位记录。如果只给出后缀或者中间的某个字符串则无法快速定位记录位置。

2.4 匹配范围值

在使用联合索引进行范围查找时，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到索引。

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

对于同一个联合索引来说，如果左边的列是精确查找，则右边的列可以进行范围查找。

2.6 用于排序

2.7 用于分组

3.回表的代价

两个特点：

• 会使用到两个B+树索引，一个二级索引，一个聚簇索引。
• 访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O。

需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式

3.1 覆盖索引

为了彻底告别回表操作带来的性能损耗，最好在查询列表里只包含索引列，只需要用到索引的查询方式称为索引覆盖。

不鼓励用号作为查询列表，最好把需要查询的列依次标明。*

4.挑选索引

• 只为用于搜索、排序或分组的列创建索引
• 最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好
• 索引列的类型尽量小
• 只对字符串的前几个字符进行索引，也就是说在二级索引的记录中只保留字符串前几个字符
• 让索引列在比较表达式中单独出现。如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。
• 让插入的记录的主键值依次递增，避免页面分裂和记录移位导致的性能损耗。
• 避免冗余和重复索引