mysql索引理论这一篇就够了

首先给上一张脑图，包含索引基本的知识点。以下内容针对mysql索引。

常见模型InnoDB索引维护覆盖索引最左前缀原则索引下推

常见模型

索引常见的模型，或者叫数据结构，有3种，哈希表、有序数组和搜索树。

哈希表（key-value）顾名思义，通过哈希函数，将key换算成一个确定的位置，将值value存入该位置。但是数据多了难免会遇到不同的key得到相同的位置，这时这个位置衍生出一个链表，存放这几个key对应的value。增加数据的时候，往链表后面加数据即可。由于key是无序的，所以针对key做区间查询的时候速度就比较慢。常用场景是等值查询。

有序数组，作为查询条件的列是有序（比如递增）的，假如我们查id=10的数据，而索引id是递增的，那我们通过二分法能快速定位到id=10的位置；假如我们查id为10~15的数据，那定位到id=10的位置，向后遍历即可。等值查询和区间查询都很快，但是增删数据需要搬动该数据后面的所有数据，开销比较大。有序数组比较适合静态存储引擎，即数据不会变化的情况。

树，是查询效率和增删效率都比较高的数据结构。典型的有二叉树，N叉树。由于索引数据是存在磁盘上的，查找数据时从根节点向下查找，每向下深入一层需要耗时比较长，所以树高越小，查询时间越短。同样多的数据，N叉树要比二叉树的树高要小。其中比较优秀的是B+树，也是作为mysql的一种索引结构的，为InnoDB使用。

InnoDB

我们建表时指定了主键，就是一个索引，但是实际业务中，我们还会建立其他索引，比如身份证号码。这两者有区别。索引列主键的是主键索引，其他的是非主键索引。主键索引中，key是主键，value是该行数据；在非主键索引中，key是索引列（比如身份证号码），value是对应的主键值。

比如一个表有id（主键）和身份证号码，及其他字段。一行数据（id=10，身份证号码=aaa，地址=中国），根据id=10查找地址，由于value就是该行数据，所有信息在value中；如果根据身份证号码=aaa查找地址，value是主键id=10，根据id=10再去找该行数据。这就是回表。

也就是根据非主键索引查找，会调用主键索引，增加查询时间。我们要尽量避免非主键索引。

索引维护

B+树的为了保证有序性，需要不断维护。可以想象一下，一个索引存在n个数据页上，前n-1个数据页的空间都用完了，此时在要加入一条新数据在前面的数据页中，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程叫页分裂。同理要删除一条数据，数据页需要合并。分裂和合并，性能就会受影响。

业务字段做主键，不容易保证有序插入，从存储空间来看一般也比整型占据的空间大。所以推荐使用自增主键。

覆盖索引

如果我们根据身份证号码，查找id，及select id from T where 身份证号码='aaa'要比select * from T where 身份证号码='aaa'要快。一方面是id比*更明确要查询的内容，需要数据库返回的数据较少；另一方面，索引身份证号码的值就是id，不需要回表。也就是这个通过身份证号码的查询覆盖了要查询的结果。类似的这种情况就是覆盖索引。

覆盖索引还有另外的形式，比如联合索引，例如（姓名，年龄）的联合索引。需要查询姓名为“张三”的人的年龄，索引数据中包含了年龄，不需要回表。

最左前缀原则

比如，查询姓“张”的人，也可以用到（姓名，年龄）的索引。
 两个原则

1. 通过调整顺序，可以少维护一个索引
   有个高频请求，根据a查b，那可以维护一个（a,b）的索引，让高频请求可以用这个索引，同时这个所以还能支持根据a查c

2. 空间
   比如需要d、e的联合索引，又需要单独根据d或e进行查询（d字段比e字段占用空间大），如果有（d,e）索引，只需要（e）索引即可，（d）索引无须建立。

索引下推

索引下推优化是mysql5.6开始引入的，即索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

比如，维护有（姓名，年龄）的联合索引，姓“张”的记录有100条，其中超过30岁的有20条。当需要查询姓“张”且年龄>30岁的人的记录，①在引入索引下推优化之前，根据like '张%' 找到100个id之后，做一次回表，判断这100条数据的年龄；②索引下推优化后，根据like '张%' 和年龄>30找到20个id，仅需对这20个id做回表。