MySQL索引

前言

通过这篇文章你将收获：
索引的实现、索引类型、索引使用原则、索引维护、主键的选择、重建索引

索引的常见数据结构

1).hash表

hash表使用key-value键值对来存储数据,

使用hash函数对原数据进行处理,找到数据对应的位置;

相同的hash值后再后面加入一个链表;

好处:增加新数据很快

缺点:哈希表这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎;查找区间范围数据效率低。

2)有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀,有序数组索引只适用于静态存储引擎，比如你要保存的是2017年某个城市的所有人口信息，这类不会再修改的数据.

缺点:更新很慢,每插入一条新数据,需要把后面的数据往后移动

3)树

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树.

因为索引不止存在内存中，还要写到磁盘上。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小。

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

innodb引擎的索引实现

MySQL数据库innodb引擎使用BTREE,实际实现为B+树。即:数据全存在叶子节点上，其余节点用来确定范围(索引。将所有的叶子节点用指针串起，这样遍历叶子节点就能获得全部数据，能更迅速的支持范围查找。

为什么不用哈希：

哈希虽然能够提供 O(1) 的单数据行操作性能，但是对于范围查询和排序却无法很好地支持，最终导致全表扫描；

为什么不用B树：

B 树能够在非叶节点中存储数据，但是这也导致在查询连续数据时可能会带来更多的随机 I/O，而 B+ 树的所有叶节点可以通过指针相互连接，能够减少顺序遍历时产生的额外随机 I/O；

为什么不用数组：

数组只要涉及到插入删除操作就会很慢,每插入一条新数据,需要把后面的数据往后移动；每删除一条数据，需要把面的数据依次前移。

在MySQL中，索引是在存储引擎层实现的

在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

InnoDB 的索引模型 B+树

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。每一个索引在InnoDB里面对应一棵B+树。

由于InnoDB是索引组织表，一般情况下会建议创建一个自增主键，这样非主键索引占用的空间最小。但事无绝对，也有KV,使用业务逻辑字段做主键的应用场景。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

聚簇索引

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

二级索引

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？(回表)

  • 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
  • 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。
  • 当select k from T where k>5,当select后的字段为索引字段或主键时,无需回表;(覆盖索引).

也就是说，基于非主键索引的查询可能会需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询;或者返回值在索引字段中.

覆盖索引

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

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

结论(最左N个字段或字符)

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

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

分析

用（name，age）这个联合索引来分析。

img

图2 （name，age）索引示意图

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“张”的人，你的SQL语句的条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。

建立联合索引相关问题

在建立联合索引的时候，如何安排索引内的字段顺序。

这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

如果既有联合查询，又有基于a、b各自的查询呢？

查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。

这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name字段是比age字段大的 ，那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引。

索引条件下推(V5.6)

详细介绍:https://www.cnblogs.com/zengkefu/p/5684101.html

总结

1.“索引条件下推”，称为 Index Condition Pushdown (ICP)，这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”，注意我们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种方式。

根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引下推。

2.MySQLV5.6后才有索引条件下推

  • 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
  • 关闭索引下推可以使用如下命令，配置文件的修改不再讲述了，毕竟这么优秀的功能干嘛关闭呢： 
　　set optimizer_switch='index_condition_pushdown=off';

实例分析:

那些不符合最左前缀的部分，会怎么样呢？

我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么，SQL语句是这么写的：

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

你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录ID3。当然，这还不错，总比全表扫描要好。

然后呢？

当然是判断其他条件是否满足。

在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。

而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

图3和图4，是这两个过程的执行流程图。

img

图3 无索引下推执行流程

img

图4 索引下推执行流程

在图3和4这两个图里面，每一个虚线箭头表示回表一次。

图3中，在(name,age)索引里面我特意去掉了age的值，这个过程InnoDB并不会去看age的值，只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此，需要回表4次。

图4跟图3的区别是，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。在我们的这个例子中，只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。

索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。

普通插入操作

如果插入新行在最后面，则只需要在记录后面插入一个新记录。

如果新插入的ID值在中间，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

插入时的页分裂

而更糟的情况是，如果所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

自增主键的选择(MySQL建议)

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

MySQL建议使用自增ID作为主键,并提供auto_increment 来实现对id的递增,一个表只能有一个字段被auto_increment修饰,在插入时不指定id值会自动+1操作;被修饰的字段必须被指定为主键.

性能方面:

插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。

也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

存储空间:

除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

KV场景适合用业务字段直接做主键

还是有的。比如，有些业务的场景需求是这样的：

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

你一定看出来了，这就是典型的KV场景。

由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

uuid,雪花id(random),与自增ID的效率比较

分析:

uuid的插入效率垫底，因为uuid相对顺序的自增id来说是毫无规律可言的,新行的值不一定要比之前的主键的值要大,所以innodb无法做到总是把新行插入到索引的最后,而是需要为新行寻找新的合适的位置从而来分配新的空间。

这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：

①写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO

②因为写入是乱序的,innodb不得不频繁的做页分裂操作,以便为新的行分配空间,页分裂导致移动大量的数据，一次插入最少需要修改三个页以上

③由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片

在把随机值（uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后,有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。

结论：

使用innodb应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行

使用自增id的缺点

那么使用自增的id就完全没有坏处了吗？并不是，自增id也会存在以下几点问题：

①别人一旦爬取你的数据库,就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况

②对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争

③Auto_Increment锁机制会造成自增锁的抢夺,有一定的性能损失

重建索引

为什么要重建索引

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

如何重建索引

非主键索引,删除后再新增

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

主键索引,alter table T engine=InnoDB

重建普通索引k删除后新增的做法是合理的，可以达到省空间的目的。

重建主键如果使用删除后新增不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。