Mysql索引-索引原理、聚集索引、覆盖索引

mysql索引

索引就是来帮助mysql高效获取数据的数据结构。索引数据结构的有四种：二叉树、红黑树、hash表、b树、b+树。

二叉树作为索引缺点：如果为一组有序的数据，那么二叉树实际存储更像链表，就是二叉树单边存储了。

红黑树：红黑树本质上也是一颗二叉树，就是一个节点最多只有两个子节点，当存储的数据多的时候，红黑树的深度就会变得很深，不利于查询。

hash表，根据查找值做hash运算得到的一个值，因为key是经过hash运算的，hash函数处理后的结果无序，所以用hash表做不到范围查询。

b树和B+树：b树的每个节点除了存储key而且还存储了Data数据，而B+树只有叶子节点才会存储data，所以同一节点内存下，B+树存储的key会比B树存储的要多，树的高度也就会变矮。这样的查询效率会变高。data是我们索引所在行的磁盘文件地址。并且B数相邻的叶子节点之间没有指针进行连接，所以他的范围查找性能也比较低。

1、索引存储原理

我们在查找某个索引时，会把当前查找的索引节点全部加载到内存进行比对，接着又会加载下一个索引节点到内存进行比对，直到在叶子节点找到目标索引。

在mysql中，一个索引节点设置为16kb，不能太大，太大会消耗内存资源。那么16kB的索引节点可以存储多少个索引呢？说建立的索引以bingint为例，一个bigint作用8Byte，每个索引隔壁都会有一个指针指向下一个索引节点，这个指针大小为6byte。所以16KB可以存储16KB/(8+6)byte=1170，假设索引树高度为3，第二层第三层全部存满，到第二次存满可以存1170*1170个索引，最后一层为叶子节点，因为存储data，所以一个索引+data大小为1KB。那么16KB可以存储16个索引。所以第三层存满的索引数量为1170*1170*16。差不多为2千多万个索引。

所以几千万的数据根据索引进行查找的话，可能就建立几次磁盘IO进行查找，而不走索引就得全表扫描，一个数据一个数据进行对比。

2、存储引擎

2.1、MyISAM存储引擎

表的存储后的索引文件和数据文件是分开的。索引使用B+树。其中一个MyISAM表在磁盘中的文件有.frm\.MYD\.MYI三个文件。数据存储在MYD文件，索引存储在MYI文件。MyISAM不支持事务。例如MyISAM表查找一条记录的流程如下：

2.2InnoDB存储引擎

InnoDB表文件在磁盘中存储为。frm文件和。idb文件。idb文件存储数据和索引。所以innodb数据和索引存储在一起。InnoDB支持事务。

 

3、聚集索引和非聚集索引

什么是聚集索引，就是这个索引树的叶子节点包含了完整的数据记录，其中主键索引的叶子节点就是包含了完整的数据记录，所以主键索引也是一个聚集索引。

什么是非聚集索引，索引树的叶子节点没有包含完整的数据记录，例如MyISAM表，索引和数据分开存储的，叶子节点索引只记录了完整数据的地址。

其中聚集索引的效率更高。

那么InnoDB的表索引都是聚集索引吗？不是的，有一些普通索引，也是二级索引，在有主键索引的情况下，他的叶子节点存储的不是完整的数据记录，而是该索引对应的主键值。然后根据主键值根据主键索引找到完整的数据记录。二级索引先找到主键值，再根据主键值找到完整数据的这个过程叫做回表，所以聚餐索引查询不用回表，二级索引查询需要回表。

既然回表要用到主键索引，那么InnoDB的表在否一定要建立主键索引呢？首先优先查找表中是否有不重复的列，有的话就根据这一列创建聚集索引，没有的话mysql会自动生成一个聚集索引列，列的值是从1开始自增，此时二级索引的叶节点存储的date为对应的聚集索引列的值。总结：一张表有且仅有一个聚集索引，可以有多个二级索引。

既然mysql会默认建立聚集索引，那么我们在将表的时候是否就不用建立主键索引了？不。因为mysql去找值不重复的列和自动维护一个自增列都会浪费资源，给mysql增加负担。

既然要自动建立主键索引，那么要用什么字段来建立主键索引？用整型的自增列。首先整型方便进行数值的对比，其二一个整型索引的占用内存较小，可以节约资源，其三自增主键，要是为不用自增列，那么新增索引会导致索引树频繁的分裂，效率较低。

4、覆盖索引

 

例如InnoDB存储引擎，因为我们索引树只有主键索引里面叶子节点才会存储完整数据，而普通索引的叶子节点存储的是对应记录的主键值，所以通过普通索引查找完整数据时需要回表，但是通过普通索引找主键值时就不用回表，因为就在普通索引的叶子节点上。查询字段在索引树上，这个索引叫做覆盖索引。

5、联合索引

底层结构：例如abc列构成联合索引，那么在构建树时，先对比a，a相等再对比b，b相等再对比c。所以通过查询a、ab、abc都可以利用到联合索引，但是bc、ac、b、c都不能用到联合索引，因为在查找索引时必须要有第一个索引列的判断结果才能判断第二个索引列，也就是说b在a确定的条件下才会有序，c在ab确定条件下才有序，这也叫最左前缀原则。例如下图就是一个联合索引的B+树结构。

索引下推：在mysql5.6前没有索引下推，如果查询一条语句用了联合索引where name like “陈%” and age=20，mysql会通过把满足name like “陈%”的所有记录找出来，再去判断 age=20这个条件，所以回表查询的次数会变多。mysql5.6后使用索引下推，会同时使用name和age判断，找到数据，这样的回表的次数就会变少，提高查询效率。