三.MySQL索引

数据库的索引事为了提高数据查询效率。

索引的常见模型

索引比较常见的模型有：哈希表，有序数组和搜索树。

哈希表

哈希表是一种以k-v存储数据的结构，我们只要输入待查找的key，就可以找到对应的value。哈希表的实现很简单，用哈希函数把key换算成一个地址，然后把value放在这个地址。如果不同的key经过哈希函数计算后得到一个相同的地址，往后拉一个链表即可。

哈希表适用于等值查询的场景，比如Memcached和一些NoSQL。哈希表不适用于区间查询的场景，因为哈希表不是有序的，区间查询时要全部扫描一遍，非常慢。

有序数组

有序数组在等值查询和范围查询的场景下表现都很优秀，使用二分查找，时间复杂度只有O(log(n))。但有序数组不适合更新数据，往中间插入一条数据就需要移动后面索引的记录，成本太高。

有序数组只适合做静态存储引擎。比如要保存的是某城市2017年所有的人口信息，这类不再修改的数据。

搜索树

搜索树的特点是，父节点左子树所有节点的值小于父节点的值，父节点右子树所有节点的值大于父节点的值。按照这个特性进行搜索的话，搜索的时间复杂度是O（log（N））。为了维持查询的时间复杂度是log（N），在更新节点时要保证搜索树是平衡搜索树，更新的时间复杂度也是log（N）。

在实际的数据库引擎中，搜索树一般不用二叉搜索树，都是多叉搜索树。与多叉搜索树相比，二叉搜索树在理论上更快。但是在实际场景中，访问磁盘的速度是很慢的。假设一棵100万节点的平衡二叉树，树高20，一次查询可能需要访问20次磁盘。如果使用的机械磁盘，每访问一次磁盘花费10ms，一次查询耗费了200ms，太慢了。

为了尽可能少的访问磁盘，在实际应用中会使用平衡N叉树。在InnoDB中，N大约是1200。100万条记录在此时只有两层，根节点一般是在内存中的，访问磁盘的次数就更少了。

N叉树在读写性能上的优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎上。

B-树和B+树的区别

• B+树内节点不存储数据，所有数据都存储在也节点，查询时间复杂度固定为logN。B-树内节点存储数据，数据查询时间复杂度不固定，与数据在树中的位置有关，最好为O(1).

• B+树叶节点两两相连可大大增加区间访问性，可使用范围查询等。而B-树每个节点key和data在一起，则无法区间查找。
  

• B+树更适合外部存储。由于内节点无data域，每个节点能索引的范围更大更精确。

• https://www.jianshu.com/p/ace3cd6526c4

InnoDB的索引模型

InnoDB使用了B+树索引模型，数据都存储在B+树中。每一个索引在InnoDB里面对应一颗B+树。

// 假设我们有一张表T，主键列为ID，表中有字段k，并且在k上有索引。
create table T(
id int primary key,
k int not null,
name varchar(16),
index(k)) engine = InnoDB;

从图中可以看出，根据叶子节点的类型，索引可以分为主键索引和非主键索引。

主键索引叶子节点存的是整行数据，在InnoDB中，主键索引也被成为聚簇索引。非主键索引叶子节点里面存的是主键的值，在InnoDB中，非主键索引称为二级索引。

基于主键索引和普通索引查询的区别：

如果语句是select * from T where id = 500，即主键查询的方式，只需要搜索ID搜索树。
如果语句是select * from T where k = 5,即普通搜索的查询方式。首先需要搜索K搜索树，找到ID=500，然后再利用ID，去搜索ID搜索树，这个过程成为回表。

基于非主键搜索树需要多扫描一颗索引树，在实际使用中，应该尽可能使用主键查询。

索引维护

B+树为了维护索引有序性，在插入新值时会进行必要的维护。

在上面的图中，如果插入的值是700，直接在500后面插入一条记录就行。如果插入的是400，需要挪动后面的数据，空出位置。更糟的是，如果R5所在的数据已经满了，根据B+树的算法，这时就需要申请一个新数据页，挪动部分数据过去（页分裂），在这种情况下，性能肯定会下降。除了性能，页分裂还会影响磁盘的利用率。分裂后，两个数据页的整体利用率下降到了50%。

为了避免上面的问题，一些建表规范要求一点要有自增主键。自增主键是指在自增列上定义的主键，在建表语句中一般这么定义：NOT NULL PRIMARY KEY AUTO_INCREMENT.

使用自增主键后，插入记录时可以不指定ID值，数据库在插入记录时会自动指定当前ID最大值+1作为下一条记录的ID。主键自增插入数据。每次插入一条数据都是追加操作，不会涉及数据的移动，也不会触发叶子节点的分裂。

而如果使用业务字段做自增主键，则往往不容易保证有序插入，这样写数据的成本比较高。

重建索引

alter table T drop index(k);
alter table T add index(k);

索引可能因为删除，页分裂等原因，导致数据页有漏洞。重建索引的过程中会创建一个新的索引，把数据按顺序插入。这样数据页的利用效率更高。
重建主键索引

// 不能重建主键索引
// 无论是删除主键，还是创建主键，都会导致整个表被重建
// 重建主键索引的效果相当于alter table T engine = InnoDB
alter table T drop primary key;
alter table T add primary key(id);

覆盖索引

如果要查找的数据在索引树上，不用回表就能查询到结果，这种索引叫覆盖索引。

在上图中，如果执行的语句是select ID from T where k between 3 and 5，这时就只查ID的值，而ID的值已经在索引树上了，因此可以直接提供查询结果，不用回表。也就是说，索引K已经覆盖了我们的查询请求，称为覆盖索引。

覆盖索引可以减少树的搜索次数，显著提高查询性能，所以覆盖索引是一个常用的性能优化手段。如果某个查询非常频繁，建立覆盖索引就是值得的。在实际场景中，需要综合各方面综合考虑。

最左前缀原则

B+树可以利用索引的“最左前缀”，定位记录。

有一个联合索引（name，age）。当查询姓张的人时，SQL查询条件是where name like '张%'，此时利用联合索引(name,age)就可以快速查询出符合条件的记录，不需要新建一个索引（name）。

只要满足“最左前缀”，就可以利用索引加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。在创建联合索引时，如果可以通过调整顺序，可以减少维护的索引数，这个顺序就是需要优先考虑的。

CREATE TABLE `geek` (
    a int(11) not null,
    b int(11) not null,
    c int(11) not null,
    d int(11) not null,
    PRIMARY KEY(a,b)
    key(c)
    key(c,a)
    key(c,b)
)ENGINE=InnoDB;

有一张表geek，上面创建了三个索引©,(c,a),(c,b)，为了支持下面两个查询，(c,a)和(c,b)是否都是必须的？

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

(c,a)不是必须的，可以去掉，(c,b)不能去掉。

索引下推

在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，称为索引下推。

表上有联合索引（name，age）。现在有一个需求，检索出表中“名字第一个是张，而且年龄是10岁的所有男孩”。SQL语句是这样的：

select * f rom tuser where name like '张%' and age = 10 and ismale = 1;

无索引下推的流程是这样的：

索引下推执行流程：