mysql(三):InnoDB的索引

一、InnoDB索引的模型:
索引的出现其实是为了提高数据查询的效率，就像书的目录一样。
在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB索引底层是使用B+树。数据都是存储在B+树的叶子节点上。
每一个索引在InnoDB中对应一颗B+树。
根据叶子节点的内容，索引类型分为主键索引(聚簇索引)和非主键索引(二级索引)。
主键索引:叶子节点存的是一整行的数据。
非主键索引:叶子节点存的是主键的值。
所以基于主键索引和普通索引的查询有区别:
主键查询: select * from T where ID=1; 只需要搜索ID这棵B+树。
普通查询: select * from T where k=5; 需要先搜索k的索引树，得到对应主键的值，再去ID索引树再搜索一次，这个过程称为回表。
也就是说，基于非主键索引的查询需要多扫描仪棵索引树。(如果只 select id from T where k=5 则不用回表)。
索引维护:
B+树为了维护索引有序性，在插入新值的时候需要做必要的维护，如果插入所在的数据页满了，就需要将该页分裂成2个数据页。
对应删除数据也会有页的合并。
MySQL的主键递增就不会触发叶子节点的分裂。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。(所以尽量不用业务字段做主键)
适合用业务字段做主键的场景:(KV场景)
1.只有一个索引;
2.该索引必须是唯一索引。
因为没有其他的索引了，不用考虑其他索引的叶子节点大小的问题。
优先考虑"尽量使用主键查询"，可以避免每次查询需要搜索两棵树。
如果在下表执行这个 select * from T where k between 3 and 5 语句，需要执行几次树的搜索操作，会扫描多少行?

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

覆盖索引:(减少树的搜索次数，显著提升查询性能)
如果执行的语句是select ID from T where k between 3 and 5; 这时候只需要查ID的值，k索引树上已经有ID的值了，就不需要回表。
也就是说，在这个查询里面，索引k已经"覆盖"了我们的查询需求，我们称为覆盖索引。

最左前缀原则:(在索引中定位记录)
索引项是按照索引定义里面出现的字段顺序排序的。
比如一个(name,age)的联合索引，
当需求是查到所有名字第一个字是"张"的人，就可以快速定位到第一个符合条件的记录，向后遍历，直到不满足条件为止。
只要满足最左前缀，就可以利用索引来加速检索，最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左N个字符。
在建立联合索引的时候，如果安排索引内的字段顺序?
索引的复用能力。
因为可以支持最左前缀，所以当有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。
如果可以通过调整索引字段的顺序，可以少维护一个索引，那么这个顺序就需要被优先考虑采用。
其次还要考虑空间问题，那个字段太大，就尽量别单独建立索引。
索引下推:
就是在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

唯一索引和普通索引对查询语句和更新语句的性能影响？

查询:
假设执行的语句是 select id from T where k=5；这个查询语句在索引树上查找的过程，先通过B+树搜索到叶子节点，也就是某一个节点(数据页)，可以认为是在数据页通过二分法来定位记录。

· 对于普通索引来说，查找到满足条件的第一个k=5的记录，需要查找下一个记录，直到碰到第一个不满足k=5的记录。
· 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

但是这两种情况带来的性能差距微乎其微，因为InnoDB是按数据页为单位来读写的，也就是说，当需要读一条记录的时候，而是以页为单位将整体读入内存。在InnoDB中，每个数据页的大小默认是16KB.
所以当找到k=5的记录，它所在的数据页已经在内存里了。对于普通索引来说，多做的那一次查找和判断就只需要一次指针寻找和一次计算。如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录才会再去读取下一个数据页。
对于整型字段，一个数据页可以放近千个key，出现上面情况的概率很低。仍可以认为这个操作成本对于CPU来说可以忽略不计。

更新：
更新需要先介绍一下InnoDB的 change buffer(减少读数据的磁盘IO操作)。
当需要更新一个数据页时，如果数据页在内存中就直接更新内存中的数据，如果没有在内存中的话，在不影响数据一致性的前提下， InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。
change buffer在内存中有拷贝，也会被写入到磁盘上。
将change buffer中的操作应用到原数据页上，得到最新结果的过程为merge。除了访问这个数据页会触发merge外，系统还有后台线程会定期merge。在数据库正常关闭(shutdown)的过程中，也会执行merge操作。
将更新操作先记录在change buffer， 减少读磁盘，语句的执行速度会得到明显的提升。而且数据读入内存是需要占用buffer pool的，这样还能够提高内存利用率。
什么条件可以使用 change buffer呢？

对于唯一索引来说，所有更新操作都要先判断这个操作是否违反唯一性约束，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，没必要使用change buffer了。
所以，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存， change buffer的大小可以通过参数 innodb_change_buffer_max_size 来动态设置，这个参数设置为50,表示change buffer 的大小最多只能占用 buffer pool 的50%。

change buffer 的使用场景:
普通索引的所有场景，使用change buffer 都可以起到加速作用吗？
因为merge的时候才是真正进行数据更新的时刻，change buffer 的主要目的只是将记录的变更操作缓存下来，所以在一个数据页做merge之前，change buffer 记录的变更越多，收益就越大。
所以，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。(账单类、日志类)。
反过来说，如果业务的更新模式是写入之后马上会做查询，由于访问这个数据页会马上触发merge操作，这样随机访问IO的次数不会减少，反而增加了 change buffer 的维护代价。

change buffer 和 redo log 的区别
redo log 的WAL 核心机制 也是 尽量减少随机读写，但是主要节省的是随机写磁盘的IO消耗。
change buffer 主要节省的则是随机读磁盘的IO消耗。

change buffer 丢失问题
change buffer 一开始是写内存的，如果这时候机器断电重启，会不会导致 change buffer 丢失呢？

1. change buffer 有一部分在内存，有一部分在 ibdata。做merge 操作，应该会把change buffer 里相应的数据持久化到ibdata。
2. redo log 里面记录了数据页的修改以及 change buffer 新写入的信息，如果断电，持久化的change buffer数据已经给merge，不用恢复。
3. 没有持久化的数据有以下几种情况:
   ① change buffer 写入，redo log 虽然做了fsync 但是没有commit。 bin log 没有 fsync 到磁盘，这部分数据丢失。
   ② change buffer 写入，redo log 写入但没有commit ， bin log 已经 fsync 到磁盘，先从bin log 恢复redo log。再从 redo log 恢复 change buffer。
   ③ change buffer 写入， redo log 和bin log 都已经fsync ，那么直接从 redo log 恢复。
   插入一个新纪录（4,400）的话，InnoDB的处理流程是怎样的？

① 这个记录要更新的目标页在内存中，InnoDB的处理流程如下:
· 对于唯一索引来说，找到3和5之间的位置，判断到有没有冲突，然后插入这个值，语句执行结束。
· 对于普通来说，找到3和5之间的位置，直接插入这个值，语句执行结束。
这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。
② 这个记录要更新的目标页不在内存中，这时，InnoDB的处理流程如下:
· 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束。
· 对于普通索引来说，则是将更新记录在change buffer ，语句执行结束。
将数据页从磁盘读入内存涉及到随机IO的访问，是数据库里成本最高的操作之一。 change buffer 因为减少了随机磁盘访问，所以对于更新性能的提升是很明显的。
PS:

索引选择和实践
普通索引和唯一索引怎么选择？
其实这两类索引在查询能力上是没什么差别的，主要应该考虑的是对更新性能的影响。所以可以尽量选择普通索引。
如果所有的更新操作后面都伴随着对这个记录的查询，那么应该关闭 change buffer 。而在其他情况下， change buffer 都能提升更新性能。
在实际使用中， 普通索引和 change buffer 的配合使用，对于数据量大的表更新优化还是很明显的。

是否使用唯一索引？
① 业务正确性优先，如果业务不能保证数据唯一，或者业务要求数据库来做约束。那么没得选，必须创建唯一索引。
② 在一些"归档库"的场景，可以考虑使用普通索引。比如，线上数据只需要保留半年，然后历史数据保存在归档库中。这时候，
归档数据已经是确保没有唯一键冲突了，要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

MySQL选择索引:
写SQL语句的时候，并没有主动指定使用哪个索引，使用哪个索引是由MySQL来确定的。也就是优化器决定选择索引。

优化器选择的逻辑
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。
当然也不是唯一的判断标准，还会结合是否使用临时表。是否排序等因素进行综合判断。
扫描行数的判断
在开始执行语句之前，只能根据统计信息来估算记录数。
统计信息就是索引的"区分度"，一个索引上不同的值越多，区分度就越好。一个索引上不同的值的个数，我们称之为"基数"(cardinality)。基数越大，索引的区分度越好。
可以通过命令 show index from table;看到索引的基数。
但是基数值也不是特别准确。
索引的基数是怎样统计的
MySQL 是利用采样统计的方法，因为把整张表取出来一行行的统计，虽然可以得到精准的结果，但是代价太高了。
采样统计的时候，InnoDB默认选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以索引的页数，就得到了索引的基数。
数据表是会持续更新的，索引统计信息也不会固定不变。当表变更的数据行数超过1/M的时候，会自动触发重新索引统计。
MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：
· 设置为on的时候，表示统计信息会持久化存储，默认的N是20，M是10.
· 设置为off的时候，表示统计信息只存储在内存中。默认的N是8，M是16.
由于是采样统计，所以不管N是20还是8，基数都容易不准。
优化器有时选择索引或者还是全表扫描会考虑是否需要回表去主键索引上查出整行数据。也就是 select *。
有时优化器会认为直接扫描主键索引更快，但是从执行时间来看，这个选择可能不是最优的。
也可能是统计信息不对，那可以利用命令修正。
analyze table table_name。(重新统计索引信息，也就是扫描行数判断不正确的情况下)
当我们发现explain 的结果预估的 rows值与实际情况差距比较大，可以采用这个命令重新统计。

索引选择异常和处理
① 采用 force index 强行选择某一个索引来执行这个sql语句。 select * from table_name force index(a) …。
MySQL会根据词法解析的结果分析可能可以使用的索引作为候选项，如果force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。
② 对于由于索引统计信息不准确导致的问题，可以用analyze table来解决。
③ 对于其他优化器误判的情况，可以在sql语句使用 force index 强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

给字符串字段加索引

1. 直接创建完整索引，比较占用空间;
2. 创建前缀索引，节省空间，但是会增加查询扫描次数，而且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，解决了字符串本身前缀的区分度不够；
4. 创建hash字段索引，查询性能稳定，有额外的存储和计算CPU消耗，不支持范围查询；

一、如何在邮箱这样的字段上建立合理的索引？

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb; 

由于要使用邮箱登录，所以业务中会出现类似于这样的语句:

mysql> select f1, f2 from SUser where email='xxx';

如果email 这个字段上没有索引，这个语句就只能做全表扫描。
MySQL 是支持前缀索引的，也就是说，可以定义字符串的前一部分作为索引。如果创建索引不指定前缀长度，那么索引就默认包含整个字符串。

mysql> alter table SUser add index index1(email);    //包含了每个记录的整个字符串
或
mysql> alter table SUser add index index2(email(6));   //提取前6个字节存放  （占用空间更小） 可能会增加额外的记录扫描次数

对于这个语句，在两个索引下是怎么执行的:

select id,name,email from SUser where email='zhangssxyz@xxx.com';

使用前缀索引，定义好长度，就可以节省空间，又不用额外增加太多的查询成本。

确定前缀的长度

在建议索引时关注的是区分度，区分度越高越好。越高意味着重复得到键值越少。所以可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

mysql> select count(distinct email) as L from SUser;   //算出这个列上有多少个不同的值

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如5%。然后，在返回的L4~L7中，找出不小于 L * 95%的值，假设这里L6、L7都满足，你就可以选择前缀长度为6。

前缀索引对覆盖索引的影响

1.   select id,email from SUser where email='zhangssxyz@xxx.com';

2.   select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用index1(email整个字符串的索引结构)的话，可以利用覆盖索引，从index1查到结果后直接就返回了，不需要回表。使用index2(email(6)索引结构)，就不得不回表判断email字段的值。
即使将index2定义成email(18)的前缀索引，虽然已经包含了所有的信息，但是InnoDB还是会回表查询。因为系统并不确定前缀缩影的定义是否截断了完整信息。
也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了。这是选择是否使用前缀索引需要考虑的因素。

前缀的区分度不好的情况

比如身份证号，一共18位，前6位是地址码，同一个县的忍身份证前6位一般是相同的。
假设维护的数据库是一个市的公民信息系统，这时候如果对身份证做长度为6的前缀索引的话，索引的区分度就非常低了。可能就需要常见长度为12以上的前缀索引了，才能满足区分度要求。
索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就越低。
前缀的区分度不好的解决办法：
① 使用倒序存储，存储身份证号的时候把它倒过来存。查询的时候，就需要：

mysql> select field_list from t where id_card = reverse('input_id_card_string');

② 使用hash字段，可以在表上多创建一个整数字段，来保存身份证的hash码，在这个字段上创建索引。
然后每次插入新纪录，就用同一个函数得到hash码保存到这个新字段里面，由于hash码可能存在冲突，所以查询语句where 部分要判断id_card的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样索引的长度就变成了4个字节，比原来小了很多。
但是这两种方法都不支持范围查询。hash字段也只能支持等值查询。
这两种方法的区别:

1. 占用的空间来看，倒序存储在主键索引上，不会消耗额外的存储空间，而hash字段需要增加一个字段。当然倒序存储方式使用4个字节的前缀长度应该是不够的，再长一点，这个消耗跟hash字段也差不多了。
2. 在CPU消耗方面，倒序方式每次写和读的时候，都需要调用一次reverse函数。hash字段需要额外调用一次哈希函数，从函数的计算复杂度来看，reverse函数额外消耗的CPU资源会更小些。
3. 从查询效率上看，使用hash字段查询性能相对稳定一些。因为哈希函数算出来的值可能会有冲突，但是概率非常小，可以认为每次查询的平均扫描行数接近1.倒序存储用的前缀索引的方式，相当于会增加扫描行数。