Mysql索引原理、聚簇索引和非聚簇索引、回表

我们都知道索引的作用是增加查询速度，大部分系统都是读多写少，索引对查询的帮助是非常大的。特别是数据量越大的时候，索引对应查询性能的影响非常关键。就好比一本字典，如果没有目录，想要找一个字的话就要一页一页的查找，非常耗时，通过字典里面的目录就可以直接定位到数据在哪一页，这样查找效率就非常高。索引就类似字典里面的目录。

一、索引的底层数据结构

1、Hash（哈希表）
哈希算法也叫散列算法，就是把任意的key通过哈希函数转变为固定长度的地址。
2、B+树
Mysql默认使用的就是B+树，B+树是一种平衡查找树。在B+树中，所有记录节点都是按照键值大小按照顺序存放在同一层叶子节点上，各个叶子节点用指针进行连接，每个叶子节点保存了相邻的节点的指针。

浅蓝色为一个磁盘块，深蓝色为数据项，黄色为指针。
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

二、聚簇索引和非聚簇索引

1、聚簇索引是按照表的主键构建的有顺序的索引，叶子节点存储了整行的字段信息，非聚簇索引存储的是索引本身和主键值。（ InnoDB引擎里面表的主键就是聚簇索引，如果没有指定主键，系统会默认生成隐式的主键）；
2、聚簇索引一个表只能有一个，非聚簇索引可以有多个；
3、聚簇索引是按照顺序保存的，所以要按照顺序插入，否则可能会引起页分裂，影响性能。

三、Mysql索引分类

1、主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引；
2、单值索引：即一个索引只包含单个列，一个表可以有多个单列索引；
3、唯一索引：索引列的值必须唯一，但允许有空值；
4、复合索引：即一个索引包含多个列，由多个字段组合而成；

四、什么是回表

回表的产生是查询的时候使用到了非聚簇索引，而非聚簇索引只存储了数据行的主键，通过非聚簇索引找到主键后，再通过主键去找到最后的数据，应该尽量避免回表。但是如果查询的字段就是非聚簇索引本身，比如mobile为索引，sql里面只查询了mobile字段，这个时候就直接返回，不用回表了。

五 、InnoDB和MyISAM的区别

项目	InnoDB	MyISAM
事务	支持	不支持
行锁	支持	不支持
表锁	支持	支持
外键	支持	不支持
计数器	不支持	支持
聚簇索引	支持	不支持

六 、索引最左匹配原则

建立如下组合索引(a,b,c)，索引会从最左边开始匹配，a,b,c、a、a,b、a,c都可以命中索引，b,c、b,a,c、c都无法命中索引。
因为B+树是从左到右建立索引树的，只能先匹配到最左边的索引字段才知道下一步去哪个范围查询，否则都不知道下一步，只能全文检索，无法命中索引。

七 、Explain执行计划

可以通过explain来查看sql的执行计划等详细信息，对于SQL调优有很大帮助

字段	描述
id	查询标识符
select_type	查询类型
table	输出的表
partitions	匹配的分区
type	联接类型，表示SQL语句的好坏，从好到差：system>const>eq_ref>ref>range>index>ALL
possible_keys	可供选择的索引
key	实际选择的索引，如为NULL，则表示未使用索引
key_len	表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度
ref	与索引比较的列
rows	估计要读取的行数
filtered	百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例
Extra	附加信息