MySQL的一些索引类型和回表查询的优化

聚集索引与非聚集索引

聚集索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询

主键索引与普通索引

当我们在列上建索引的时候，我们分为两种，当我们在主键建立索引时，我们称之为主键索引；当我们在其他列建立索引的时候，我们称之为普通索引

1. 如果一个主键被定义了，那么这个主键就是作为聚集索引，即叶子节点存储的是全部列数据
2. 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
3. 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6字节的列，该列的值会随着数据的插入自增
4. 自增主键会把数据自动向后插入，避免了插入过程中的聚集索引排序问题。聚集索引的排序，必然会带来大范围的数据的物理移动，这里面带来的磁盘IO性能损耗是非常大的，而如果聚集索引上的值可以改动的话，那么也会触发物理磁盘上的移动，于是就可能出现page分裂，表碎片横生，所以不应该修改聚集索引

回表查询

回表查询就是如果通过普通索引查询，我们会通过这个普通索引的列值找到该记录的主键值（也就是说普通索引的叶子节点里面存的不是某一个记录的所有列的值，存的是它的主键值，如果每个普通索引像聚集索引一样叶子节点存储所有列的值，那么会造成大量的空间浪费）然后拿着这个主键值来到主键索引再查询一遍，这个就叫回表查询，就是普通索引查询
示意图：

我们应该再int数据类型建立索引还是再char类型建立索引

如果char类型占用的字节比较小，可以考虑在char上建立索引，否则尽量在int上建立索引，因为在B+树上，建立索引的列的值是作为B+树节点的key的，key越大，一个节点的元素数量就会越少，B+树深度就会越深，IO次数就会越多

回表查询的优化

如下情况是不会回表查询的
他select的是id，id是主键，普通索引的叶子节点的value就是存放的是主键，所以不需要回表

如果在某种特定的情况之下，我们可以使用联合索引（覆盖索引）来避免回表查询
如果有如下表：

联合索引的创建：

当索引键好了之后，他的B+树大概是如下的样子：

我们在建联合索引的时候注意：
第一列索引是有序的，第二列索引是无序的，因为我们建立索引的时候，我们是先判断第一列的大小，当第一列大小一样的时候，再判断第二列的大小的。
当我们执行如下查询语句时，是不会出现回表查询的，因为我们可以在这个索引里面找到我们要的列

这里我们需要注意一些索引失效（一般指联合索引）的情况，当我们执行下面这条查询语句的时候时，MySQL是不会走这个联合索引的：因为age是第二列索引，他是无序的

我们可以认为覆盖索引是一种概念，联合索引是覆盖索引的一种实现
联合索引：指索引中包含多个列。
覆盖索引：指的是从索引中可以得到所有想要查询的列。
比如select id，age from user where name = ‘a’ and age = 12。
联合索引是说的是where后面的部分，即查询条件；覆盖索引是说的select后面的部分，即查询列。