一、索引的种类
1、hash类型：查询速度快，但是由于hash之后是无序性的，所以只能做等值查询，无法进行大于、小于之类的方位查询。
2、B+树类型：在B树的基础上演变而来的，B树非叶子节点也会存储数据，B+树非叶子节点只存储索引之间地址，只有叶子节点会存储数据，从而使得非叶子节点可以存储更多的记录，降低树的高度。
二、个人对索引的理解（主要是B+树结构以及InnoDb引擎）
1、添加索引就是把每条记录对应的字段进行排序之后按顺序查找，查询好比一个老师要查询学号为10的学生的试卷，如果每次找都把试卷全部翻一遍，那就太费劲了，何不把试卷先按照学号从小到大或者从大到小叠放整齐，然后根据学号的顺序去查找呢，这样是不是效率提交了很多，索引的原理也是一样的，把每条记录的要加索引的字段，按照优先级排序好保存起来，并且同事把当前记录的主键以及字段的值也保存起来，查询的时候如果能命中索引，就按照顺序去找到对应索引，再找到主键ID从而找到相应的记录。
2、如下图，code与val字段先后顺序添加组合索引，
（添加索引前）
（图1）
（添加索引后）
（图2）
三、执行计划explain/desc
1、执行计划得到如下结果，主要说一下id、table、type、key、extra、rows项，优化主要根据这几项
执行SQL：desc select * from b_user where id=1
（图3）
2、id查询序列号，决定数据表的执行顺序，id大先执行，id一样，从上往下执行。
执行SQL：desc select * from b_user where id in (select user_id from b_user_role)
（图4）
上面的结果是 b_user_role的id最大限制性、b_user与生成临时表<subquery2>的id一样，但是<subquery2>在上面所以优先执行，b_user最后执行。
3、table是数据表，包括产生的临时表（如上面的<subquery2>），普通的表如果有别名就显示别名。
4、type索引的联接类型
- system:表仅有一行(=系统表)。这是const联接类型的一个特例
- const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次
- eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行,这可能是最好的联接类型,除了const类型。
- ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取
- ref_or_:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行
- index_merge:该联接类型表示使用了索引合并优化方法
- unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高
- index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
- range:只检索给定范围的行,使用一个索引来选择行
- index:该联接类型与ALL相同,只有索引树被扫描就能拿到想要字段。这通常比ALL快,因为索引文件通常比数据文件小（索引覆盖）。
- ALL:对于每个来自于先前的表的行组合,进行完整的表扫描，说明查询就需要优化了。
- 一般来说，得保证查询至少达到range级别，最好能达到ref。
5、key 显示MySQL实际决定使用的键(索引)。如果没有命中索引，是NULL
6、rows 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
7、extra该列包含MySQL解决查询的详细信息
- Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
- Select tables optimized away MySQL根本没有遍历表或索引就返回数据了，表示已经优化到不能再优化了
- Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
- range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
- Using filesort:就是排序没有用到索引，MySQL需要额外的排序动作,以找出如何按排序顺序检索行，说明查询就需要优化了。
- Using index:从只使用索引树中的信息而不需要进回表查询。
- Using temporary:为了解决查询或者排序,MySQL需要创建一个临时表来容纳结果，说明查询就需要优化了。
- Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
- Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
- Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表
四、索引命中与失效
1、由于大多数情况下每个表只能使用一个索引，索引推荐尽量使用组合索引，按照字段的查询频率添加索引，为了满足用户的不同查询维度，可以按照不同的顺序创建多个不同的索引。
2、索引的命中
- 查看执行计划type字段使用了什么联接类型，如果是All表示没有命中，如果不是All也不代表一定命中了索引，比如left join，两个字段字符集不一致的情况下，可能出现type为ref但是key为null，这种情况也是没有命中索引相当于All，索引需要通过type和key字段来确定是否有命中索引，并且命中了哪一个索引。
3、索引的失效以及原因
- 最佳左匹配原则：很多人经常会搞混，以为是在SQL的where条件的and查询条件字段放置的先后顺序，其实不是的，是指多个字段添加索引的先后顺序，简单理解如下。
例如（图2），从左到右顺序是code再到val，索引先按照code排序c1 - c4，再按照val排序，在code字段相同的情况下排序val，val并不是全局有序的，只是在code确定的条件下有序的，所以当code 没有查询条件的时候，val也是无序的，需要全表扫描，就是说，左边的字段索引生效右边的字段索引才有可能生效，否则右边的索引肯定不生效。
- 索引字段不能使用函数，否则索引会失效
- 遇到范围查询，后面的索引也会失效（例如：code和val添加组合索引，查询条件为code in （）and val = x），code和val从左到右添加索引，以（图2）为例简单列举下面几种情况
- code >= c3 and val = 1：通过code条件大于等于c3可以得到
（图5）
这三条记录，这时候再去根据val查找，这时val的顺序是4、1、6，所以就必须要扫描全部的在code过滤条件下的val，所以val字段的索引失效了，可能有些同学会认为，为什么不把val也排序 呢，因为你这个是组合索引，前面还有code字段优先级比较高，需要在code相同的情况下才能排序val，而不是全局排序
- code like "c%"：%字符就是匹配任何的值，也是一种范围，所以可以匹配到code以c开头的值，这个时候是可以用到索引的。
- code like "%c%"：%字符也是范围，由于不确定c的前面是什么值，所以是包含c的所有记录都要查找出来，所以要，查找所有的记录，所以失效
- 由上面的举例可以知道组合索引，从左到右，当有一个字段碰到 in、大于、小于、like、between等类型的查询，其后面的字段索引都会失效
- 字段重复比例高的情况下，索引也会失效，走全表查询：比如 性别只有男、女这两种
- 查询数据量占总数的比例高的情况下也会失效（主键索引除外，因为其他的索引需要回表的话，也是通过主键索引去查找的，回表太多耗费性能，还不如全表扫描，减少回表，所以如果直接主键查询的话，占比高也无所谓），比如：下例，num有索引，
（图6）
- 执行 desc SELECT * FROM `b_test` where num > 3 查询出来的数据占比太高了，索引失效
（图7）
- 执行 desc SELECT * FROM `b_test` where num > 12 查询出来的占比低，索引生效 type = range 类型的
（图8）
五、order by 与 group by （要理解一点是，我们添加索引之后，存储的数据结构已经固定了，是我们的数据去按照已存在的数据结构里面去排序查，而不是吧数据拿出来再去排序）
1、order by 排序使用索引的情况
- 执行SQL：desc SELECT * FROM `b_test` where name = 'a6' order by num desc （name和num字段依次创建索引），可以看到extra没有出现 Using filesort 排序也命中索引
（图9）
- 执行SQL：desc SELECT * FROM `b_test` where name > 'a6' order by num desc （name和num字段依次创建索引），出现了 Using filesort 并且 type = range 标识name命中了所以，但是是范围类型的，导致num排序无法命中索引，原因也很简单，因为num的排序，是在name固定的前提下的，name都是范围了，标识num也是无序的了，所以在索引中无法按顺序获取，只能根据name获取出所有的数据到内存中再进行排序
（图10）
- 执行SQL：desc SELECT * FROM `b_test` order by num desc （name和num字段依次创建索引），由于name没有查询条件无法确定，所以num也是无序的，无法使用索引
（图11）
group by 聚合
- group by 的聚合一般都是配合sum(求和)、avg（求平均）等方法的出现
- 与order by 不同的点是，where条件中存在或者不存在，单独给group by 字段添加索引也能生效
- 执行SQL：desc SELECT sum(del_flag) FROM `b_test` GROUP BY num （num字段有索引） 也能命中索引
（图12）
- 执行SQL：desc SELECT sum(del_flag) FROM `b_test` where name in ("a1","a2") GROUP BY num （num字段有索引） 也能命中索引
（图13）
- group by 如果添加组合索引，其原理和order by 几乎一致
六、索引覆盖
- 索引覆盖就比较简单了，由于innoDB保存索引的时候，会把当前字段的值也会保存进去，所以如果你查询的字段在一个组合索引里面，那么他会直接扫描索引的值，不用再回表从而提高效率。
- 执行SQL：desc SELECT num FROM `b_test` where name in ("a1","a2") （name与num有索引），命中了name字段的索引，所以符合索引覆盖，extra 出现了Using index
- 执行SQL：desc SELECT num FROM `b_test` （name与num有索引），符合索引覆盖，extra 出现了Using index
- 执行SQL：desc SELECT num FROM `b_test` where name like "%a%" or num > 1 （name与num有索引），查询条件之一索引里面的字段，但是索引失效了，也符合，extra 出现了Using index，有时候被用来解决like查询效率低的问题
七、索引下推
- 索引下推是MySQL 5.6 及以上版本上推出的，把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断，这样能有效减少回表。
- 执行SQL：desc SELECT * FROM `b_test` where name like "a%" and num =6 （name和num添加组合索引），这个sql按道理来说，命中name索引后面就是范围了，num索引失效。
- 不用索引下推的执行过程
第一步：利用索引找出name带'a'的全部行
第二步：再根据这四条索引数据中的 id 值，逐一进行回表扫描，从聚簇索引中找到相应的行数据，将找到的行数据返回给 server 层。
第三步：在server层判断num = 6,进行筛选，最终只留下 a5用户的数据信息
- 使用索引下推的执行过程
第一步：利用索引找出name带'a'的全部行
第二步：根据 num = 6 这个条件，对上一步查询的得到的索引数据进行判断筛选，最终只留下 a5用户的数据信息。（直接操作索引，不进行回表提高效率）
第三步：将符合条件的索引对应的 id 进行回表扫描，最终将找到的行数据返回给 server 层，无需在进行过滤。
