MySQL实战45讲 索引（1）

MySQL索引总结

索引的作用

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样，一本500页的树，如果想快速找到其中的某一个知识点，在不借助目录的情况下，是需要找很长时间的，同样，对于数据库的表而言，索引就是他的目录

索引的常见模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种，这里也就引入了索引模型的概念，可以用于提高读写效率的数据结构很多，这里主要有三种常见的，也比较简单的数据结构，他们分别是哈希表，有序数组和搜索数。

哈希表

哈希表是一种以key-val存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。
不可避免的，多个key值经过哈希函数的换算，会出现同一个值的情况，处理这种情况的一种方法就是拉出一个链表。
假设，你现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这是对应的哈希索引的示意图如下所示：

.

图中，User2和User4根据身份证号算出来的值都是N，但没关系，后面还跟了一个链表，假设，这时候你要查询ID_card_n2对应的名字是什么，处理步骤就是，首先，将id_card_n2通过哈希函数算出N，然后，按顺序遍历，找到User2.
四个ID_card_n的值不是递增的，这样做的好处是增加新的User时速度会很快，只需要往后追加，但是缺点时因为不是有序的，所以hash索引做区间查询的速度时很慢的。加入我们要找身份证号在x到y的所有用户，我们就必须全表扫描一遍了。
所以哈希表这种结构适用于只有等职查询的场景，比如Mencaached及其他的一些NoSQL引擎。

有序数组

有序数组在等值查询和范围查询场景中的性能都非常优秀，对于上面这个根据身份证号查名字的例子，如果我们使用有序数组来实现的话，示意图如下：

这里我们假设身份证号没有重复，那么这个数组就是按照身份证号的递增顺序保存的，这时候如果我们要查某一个身份证号对应的名字，只需要用二分法就可以快速得到，这个时间复杂度为O(log(N))。同时很显然，这个索引支持范围查询，如果要查身份证号在x到y区间的User，可以先用二分法找到x，如果x不存在，就找到大于x的第一个User，然后向右遍历，知道查到第一个大于y的身份证号，推出循环。
有序数组在查询的表现十分优越，但是，在需要更新数据的时候，往中间插入一个记录就必须挪动后面所有的记录，成本太高。所以有序数组索引只适用于静态存储引擎，比如我们保存的时2017年某个城市的所有人口信息，这类不会再修改的数据。

二叉树

二叉搜索树的特点是，每个节点的左儿子小于父节点，父节点有小于右儿子。这样如果我们要查ID_card_n2的话，按照图中搜索的顺序就是按照UserA->UserC->UserF->User2这个路径得到，这个时间复杂度是O(log(N))。

树可以有二叉，也可以有多叉，多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增，二叉树的搜索效率是最高的，但是实际上大多数数据库的存储并不使用二叉树，其原因是索引不止存在内存中，还要写在磁盘上。假如有一颗100万节点的平衡二叉树，树高20，一次查询可能需要访问20个数据，在机械硬盘时代，从磁盘随机读一个数据块需要10ms左右的寻址时间，也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一行可能需要200ms，为了让一个查询尽量少的读磁盘，就必须让查询或称访问尽量少的数据块，那么，我们就不应该使用二叉树，而实要使用N叉树，这里N叉取决与数据块的大小。
在InnoDB的整数字段索引为例，这个N差不多是1200.这棵树是4的时候，就可以存1200的3次方个值，这就已经17亿了，考虑到树根的数据块总是在内存中，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘，其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

InnoDB的索引模型

简介

在InnoDB中，表都是根据主键的顺序以索引的形式存放的，这种存放方式的表称为索引组织表，有因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存放在B+树中的，每个索引在InnoDB里面对应一颗b+树。
假设我们有一个主键为ID的表，表中有字段k，并在k上有索引
这个表的建表语句为

create table T(
    id int primary key,
    k int not null,
    name varchar(16),
    index(k) engine=InnoDB;
)

假设表中的数据如下

	ID	k
1	100	1
2	200	2
3	300	3
4	500	5
5	600	6

那么因为表中有两个索引，所以两棵索引树的示意图如下

我们可以看到，根据叶子节点的内容，索引分为主键索引和非主键索引。

主键索引与非主键索引

主键索引的叶子节点存的是整行数据，在InnoDB中主键索引也被称为聚簇索引，非主键索引的叶子节点内容是主键的值，在InnoDB里，非主键索引也被称为二级索引。
那么基于主键索引和普通索引有什么区别呢？

• 如果语句是 select * from T where ID = 500 ,即主键查询方式，则只需要搜索ID这颗B+树；
• 如果语句是 select * from T where k = 5 ,即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次，这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一次索引树，因此，我们再应用中应该尽量避免使用主键查询。

索引维护

B+树为了维护索引的有序性，再插入新值的时候，需要做必要的维护，以上面这个图为例，如果插入新值的行ID值为700，只需要再R5的记录后面插入一个新记录，如果新插入的ID值为400，就比较麻烦了，需要逻辑上挪动后面的数据，空出位置。更糟糕的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂，再这种情况下，性能自然会收到影响。
除了性能外，页分裂操作还影响数据页的利用率，原本放在一个页的数据，现在分到两个页中，整体空间利用率大约降低50%。当然有页分裂就有页合并过程，当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。

自增主键的选择

自增主键是指再自增列上定义的主键，再建表语句中一般是这么定义的，not null primary key auto_increment
插入新记录的时候可以不指定ID的值，系统会获得当前ID最大值加1作为下一条记录的ID值。也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景，每次插入一条新记录，都是追加操作，都不涉及挪动其他记录，也不会除法叶子节点的分裂。而又业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
存储空间的角度来看，假设我们的表中缺失有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢。
由于每个非主键索引的叶子节点上都是主键的值，如果用身份证号做主键，那么每个二级索引的叶子节点占用月20个字节，如果用整形做主键，则只要四个字节，如果是长整型，只需要8个字节。
因此，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。因此自增主键往往是合理的选择。
当一些业务逻辑的场景需求是这样的时候

1. 只有一个索引
2. 该索引必须是唯一索引

即KV场景，在这种场景下，我们尽量使用K做主键。

扫描流程解析

对于下面的表

create table T(
    ID int primary key,
    k int not null default 0,
    s varchar(16) not null default '',
    index k(k))
    engine=InnoDB;
)

该表中的数据的索引示意图如下：

对于SQL查询语句 select * from T where k between 3 and 5;
现在，我们一起来看看这条SQL查询语句的执行流程：

1. 在k索引树上找到k=3的记录，取得ID=300；
2. 在ID索引树查找到ID=300对应的R3；
3. 在k索引树取下一个值k=5，取得ID=500；
4. 再回到ID索引树查到ID=500对应的R4；
5. 在k索引树去下一个k=6，不满足条件，循环结束

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到整个查询过程读了k索引树的3条记录，回表了两次。由于查询结果所需的数据只在主键索引上又，所以不得不回表，那么，有没有可能经过索引优化，避免回表过程呢？

覆盖索引

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

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
下面我们来看一个有关覆盖索引的详细例子，在一个市民信息表上，是否有必要将身份证号的名字建立联合索引？

这个市民表的定义如下

create table tuser(
    id int(11) not null,
    id_card varchar(32),
    name varchar(32) default null,
    age int(11) default null,
    ismale tinyint(1) default null,
    primary key(id),
    key id_card (id_card),
    key name_age (name,age)
) engine=InnoDB;

我们知道，身份证号是市民的唯一标识，也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了，而在建立一个联合索引，是不是浪费空间。
现在有一个高频需求，要根据市民的身份证号查询他的姓名和年龄，这个联合索引就有意义，它可以在这个高频请求上用到覆盖索引，不子啊需要回表查整行记录，减少语句的执行时间。

最左前缀原则

我们还根据上面的例子(name,age)这个联合索引来分析。

索引项是按照索引定义里面出现的字段顺序排序的。
当我们的逻辑需求是查到所有名字是张三的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果，如果要差的时所有名字第一个字时张的人，这是也能够用上这个索引，查找第一个符合条件的记录时ID3，然后向后遍历，直到不满足条件为止。
不只是索引的全部定义，只要是满足最左前缀，就可以利用索引来加速检索，这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字段。

索引下推

上一段我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录，这是，哪些不符合最左前缀的部分会怎么样呢？
我们以上述联合索引(name,age)为例，如果现在有一个需求，检索出表中名字第一个字是张，而且年龄是10岁的所有男孩。那么，SQL语句是这么写的：

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

你已经知道了前缀索引规则，所以这个语句在搜索索引的时候，只能用"张",找到第一个满足条件的记录ID3，当然，这还不错，总比全表扫描要好。
然后呢？
在MySQL5.6之前，只能从ID3开始一个个回表，到主键索引上找出数据行，在对比字段值。而MySQL5.6引入的索引下推优化，可以在索引遍历的过程中，对索引包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
下图是5.6之前的策略，下下图是5.6之后的策略。

如何选择普通索引和唯一索引

例如，我们在上述的例子中的查询语句 select id from T where k = 5.这个查询语句在索引树上查找的过程，首先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录

• 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，知道碰到第一个不满足k=5条件的记录。
• 对于唯一索引来说，由于索引具有唯一性，查找到第一个满足条件的记录后，就会停止继续检查。

这个不同带来的性能差距是微乎其微的，因为InnoDB是按照数据页为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘中读出来，而是以页为单位，将其整体读入内存，在InnoDB中，每个数据页的大小默认是16KB。因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了，那么，对于普通索引来说，要多做的那一次，查找和判断下一条记录的操作，就只需要一次指针和一次计算。当然，如果k=5的记录刚好是这个数据页的最后一个记录，那么要去下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些，但是，我们之前计算过，对于整形字段，一个数据页可以放近千个key，因此出现这种情况的概率会很低，所以，我们计算平均性能差异时，仍可能认为这个操作成本对于现在的CPU来说可以忽略不计。

change buffer 简介

当我们需要更新一个数据页的时候，如果数据页在内存中就直接更新，如果这个数据页还没有在内存中，在不影响数据一执行的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
changebuffer实际上时可以持久化的数据，它在内存中有拷贝，也会被写入到磁盘上。
将changebuffer中的操作应用到原数据页，得到最新结果的过程称为purge，除了访问这个数据页会除法purge外，系统后台线程会定期purge。在数据库正常关闭的过程中，也会执行purge操作。
显然，如果能够将更新操作先记录在changebuffer，减少读磁盘，语句的执行速度会得到明显的提升，而却，数据读入内存需要占用buffer poll的，所以这种方式还能避免占用内存，提高内存利用率。

change buffer用途

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反了唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这不许要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。
因此唯一索引的更新就不能使用change buffer了，实际上只有普通索引操可以使用。change buffer用的是buffer poll中的内存，因此不能无限增大。change buffer的大小，可以通过参数 innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，标识change buffer的大小最多只能占用buffer pool的50%。

语句的更新过程

如果我们在这张表中插入一个新记录(4,400)的话，InnoDB的处理流程：
第一种情况，如果这个要更新的目标也在内存中，这是，InnoDB的处理流程如下：

• 对于唯一索引来说，找到3和5之间的位置，判断冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

性能的差距很小。
第二种情况，这个记录要更新的目标页不在内存中。这是，InnoDB的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到内有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

将数据从磁盘读入内存设计随机IO的访问，是数据库里面成本最高的操作之一，change buffer因此减少了随机磁盘访问，所以对更新性能的提升是非常明显的。

change buffer的使用场景

普通索引的所有场景，使用change buffer都可以起到加速作用吗？
因为purge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动坐缓存下来，所以在一个数据页做purge之前，change buffer记录的变更越多，受益就越大，因此changebuffer适用于写多读少的情况。