MySQL 索引

MySQL 索引

何为索引？

索引是存储引擎为提高数据表的查询速度而提供的一张目录。索引是保存在文件系统中的。InnoDB通过查询语句中的where子句中的字段以及条件判断是否可以从直接从索引中找到结果。

为什么需要索引？

数据库的表数据是存在文件中的，数据库文件是以数据页的形式存在的（相当于数组，一页相当于数组中的一项），一页4kB，或者16kB，其中保存一些数据，文件中保存多个页。

这是一种分治的设计，把一个大文件分解成一个个小页。这样做的目的是当拿偏移量来文件中查询的话，如果是一个大文件的话还相当于是全量IO的去找（相当于链表查找），但是分成一个个连续的页的话，那么通过页号和数据在页中的偏移量就可以快速的找到数据。

如果没有索引，

数据库的查询就是要到文件中这些页挨个的去找，等于还是一个全量查找，全表进行扫描。

有索引的话

假设用用户表的name做索引，首先得为索引也建一个文件。索引文件也是有很多页的，一页里面有一些数据。不过索引里面存的就不是表数据而是对应字段的每行的值以及其在数据文件中的页的偏移量。

下面如果我们要查 name='张三’的记录

select * from user where name='张三'

步骤就是这样的，先拿‘张三’到索引文件里面找，找到对应文件偏移量再拿这个偏移量回表到数据表文件中拿实际的那一行的所有数据。
那就有一个问题，在索引里面查还是全量查找呀！根本效率提升不了多少。

所以索引文件再也不能使用这种顺序的数据结构，而是采用查找效率更高的数据结构，例如B+树。

这样的话从索引文件里面查找到条件对应在数据文件中偏移量的效率就很高了。然后到数据文件中根据偏移量直接读数据的效率也很高。这也就是为什么要用索引了！

MySQL索引结构

索引可以使用Hash表，B树，和B+树这些数据结构

首先为什么不推荐使用Hash表作为MySQL索引的数据结构呢？
除了Hash表会产生哈希冲突外，还有一个更大的问题就是，Hash表索引无法快速处理连续的范围条件查询。例如sql语句 select * from t where id>500
因为hash表索引建立好以后对应的值通过哈希算法就不一定还会按照顺序了，想要这种连续的范围查询还得把所有的行都扫描一遍！

使用二叉排序树

优点：查询效率变成了O(logn)
问题：当保存顺序数据时，例如递增的数据，那么节点总是会构造到二叉树的右节点，导致二叉排序树变成了一根链表，查询效率并没有提升！

使用平衡二叉树

优点：通过调整平衡因子使得二叉树不会变成一根链表
问题：树的深度太深，由于平衡二叉树只有左右两个子树，当数据量大了，二叉树的深度同样也很大，查询效率并没有提升多少！

使用B树，也叫B-树

多路平衡二叉树，限制了树的深度不会太深

问题：
1. 每个节点都是即包含key又包含value，一个节点包含的数据量太大，加载同样的数据量就需要多执行几次内存加载；
2. 查询效率不稳定，由于最终结果可能在叶子节点也有可能在其中的某个节点，所以不同的查询时间差别可能会很大；
3. 对于数据表范围遍历的效率并没有提升。
问题2和3，其实都是因为B树中每个节点中即包含了key也包含了value，导致最终查找到的结果所在的层数不是固定的，以及数据表中每一行的数据映射到B树中所在的层数也不是固定的，导致不同的查询效率不稳定，以及对于数据表的范围查询效率也不是很高，因为同一个范围内的数据可能不在同一层。

使用B+树

和B树类似，不同点在于

1. B+数的每个非叶子节点上只有key的范围，并没有实际value的范围。实际的数据全部在叶子节点上；
2. 所有的叶子节点都在同一层（B树也是所有叶子都在同一层但是不一定叶子就是最后结果），并且既包含key又包含value（非聚簇索引的value就是主键的值，聚簇索引的value就是这一行其他所有属性的值（这里指的是InnoDB））；
3. 叶子节点之间通过指针相连保证连续。

优势：
1. 内存从磁盘加载数据的效率更高（只有叶子才实际包含value）
2. 索引查询的效率更加稳定（数据都在同一层）
数据表范围查询的效率也得到了提升（叶子节点之间通过指针相连保证顺序性）

MyISAM和InnoDB的索引结构都是使用了B+树，不同点在于：
MyISAM索引文件和数据文件是分离的，索引叶子节点中的data位置保存的是数据文件对应数据的地址偏移量。通过索引找到对应data以后再去数据文件中找对应真实的数据。

在InnoDB中索引即数据，也就是说索引节点中data存的就是实际的数据，其中聚簇（主键）索引保存的是整行数据，二级索引data保存的是该行主键的值（通常非聚簇索引需要回表（拿到主键再从主键索引中查找））。

索引类型

主键索引

数据表的主键列使用的就是主键索引，设置了主键就有主键索引
一张表只能有一个主键，并且主键不能为null，不允许重复
在InnoDB中，如果一张表没有主键，那么InnoDB会先检查是否有唯一索引，且不允许为null的字段，如果有，则选择这个字段作为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。
主键索引data存的就是对应数据行的数据

二级索引

二级索引是使用主键索引服务的，即二级索引的叶子节点的data存的是主键的值。
唯一索引，普通索引，前缀索引（只针对字符串的，使用字符串的部分前缀）等索引都属于二级索引

聚集索引

聚集索引是索引结构与数据存放在一起的索引，主键索引就属于聚集索引。就是叶子节点中的data 放的是实际的一行数据。

聚集索引的优点
通过索引定位到叶子节点也就意味着找到了数据

缺点
一、依赖有序的数据：因为B+树的叶子节点是有序排列的，如果聚集索引对应的字段并不是有序的，那就需要创建插入节点的时候进行排序。这也是为什么主键索引推荐设置为自增的原因！
二、更新代价大：如果对索引列的数据修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改的代价肯定较大，所以对于主键索引来说，主键一般是不可以被修改的。

非聚集索引

非聚集索引就是索引结果和数据分开存放的索引，二级索引属于非聚集索引。
非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

优点
更新代价更小

缺点
一、一样，非聚集节点也依赖于有序的数据，这点是由B+树结构决定的
二、由于非聚集索引叶子节点数据部分存放的是主键的值，那么查到以后可能还需要通过这个主键再回到数据文件（聚集索引）或者表中查询（回表）

问题：非聚集索引一定要回表查询吗？
答案：不一定

 select username from user where username='张三'

这种通过username建立的索引查询username的值，那么直接返回查到了叶子节点key即可

覆盖索引

这个是根据查询语句决定的，前面的类型都可能是覆盖索引。

上述说的，如果一个索引包含（覆盖了）所有需要查询的字段的值，就称这个索引叫覆盖索引。
我们知道在InnoDB中，如果不是主键索引，查询完以后往往都是需要“回表”，即通过主键再查一次。覆盖索引则是查出来的节点的key和要查属性列是对应的，不需要回表操作！

例如，通过主键索引查询主键，那么这个主键索引就可以被叫做覆盖索引；再比如上面的通过username的索引查询username。那么这个username的普通索引就也可以别叫做覆盖索引。

联合索引

使用表中的多个字段共同创建的索引就叫联合索引，也叫组合索引或符合复合索引。使用非聚集索引的话，推荐使用联合索引。

联合索引的最左前缀匹配原则

最左前缀匹配原则是指，在使用联合索引时，MySQL会根据联合索引中的字段顺序，从左到右依次到查询条件中取匹配，如果查询条件中存在于联合索引中最左侧相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如>,<,between,以%开头的like等条件，才会停止匹配。

举个例子：例如在user表中创建联合索引，使用的字段以及顺序是 name, age, sex
下列查询应用到索引的情况分别为

select * from user where name='xx' and age=10 and sex='1'  //用到了索引

这个匹配顺序是，先拿name到where条件进行匹配，匹配了第一个过滤了一批数据，然后继续用age到where后面的条件进行匹配，同样匹配到age，再过滤一批数据，最后再拿sex到后面条件继续匹配，再过滤一批数据。

select * from user where name='xx' and sex='1' // 用到了索引

这个匹配顺序是，先name匹配到索引，过滤掉一批数据，再拿age到剩下的条件中匹配，一直没匹配到。那么剩下的条件就是没走索引的通过name得到的数据回数据表里面再根据剩下的条件查询。

select * from user where age=10 and sex='1'  // 没用到索引

这个一直拿name到条件中匹配，一直没匹配上，所以这条查询语句是没有走索引的。

select * from user where sex='1' and name='xx' // 用到了索引

只有条件后面的name使用了索引过滤了一批数据，前面的sex没有走索引

select * from user where age > 18 and name='xx' // 没有用到索引

前面的范围查询停止了匹配，导致后面的name也没有走索引

所以我们在使用联合索引时，可以将区分度高的字段放在最左边，这样就可以过滤更多的数据。
例如上面的例子中，联合索引 name, age, sex的效果比联合索引 sex, age, name的效果好！
因为name放在最左边可以优先匹配，通过name可以过滤掉绝大多数数据，而sex只能过滤掉一半

索引下推

索引下推是MySQL5.6版本中提供的一项索引优化功能，可以在非聚集索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
就是一项优化技术。
不使用索引下推的话，先通过索引获取到数据（回了表的），然后再根据条件判断这个数据是否可用，不可用就不要！
使用了索引下推的话，先看索引中存的数据是否满足查询条件，如果满足再回表查询数据行，不满足就直接跳过，看下一个。减少了回表的次数！