回顾一下以前写过的Mysql索引相关理论知识，
1，一般我们建表都会有一个主键ID，mysql会根据该ID生成聚集索引(如果没有主键，也会有一个隐藏的ID用来做索引)，该索引会生成一颗B+树，节点的键为该ID，所有叶节点上会挂上对应的数据
2，辅助索引同聚集索引一样会建一颗B+树，但是所有叶节点挂的是该数据的主键ID，所以一般使用辅助索引会再在聚集索引上查一遍
3，可以用多个键建立联合辅助索引，根据建索引时各个字段的先后顺序，按最左原则使用
今天来验证一把到底什么时候用了，怎么用的索引
随便找了张测试表，一共5个索引，1个主键id，1个唯一索引sm_id，1个组合索引login_name+password，2个普通索引real_name,phone
一，没有搜索条件的时候
-
explain select * from TB_USER
type:All表示进行完整的全表扫描，这里应该不需要解释
image.png explain select * from TB_USER order by identity_no;
explain select * from TB_USER order by phone
explain select * from TB_USER order by login_name,password
-
explain select * from TB_USER order by sm_id
用普通字段排序，无论是否带有索引，Extra多了一个Using filesort，表示同样走了全表扫描，但是多了一次外部排序，即根据排序算法在当前thread的内存中排序，排序算法以后再说，这里应该清楚取数据的方式是一样的，但把数据取到内存中后做了一次排序，效率显然低于前面那次
image.png -
explain select * from TB_USER order by id;
这里type:index表示通过走索引树的方式获得数据,一般来说走索引会比直接全表扫描更快
image.png总结上面，在没有查询条件的时候，用order by 主键可以提高效率。
二，搜索单列
- explain select id from TB_USER
Using index表示：从只使用索引树中的信息而不需要进一步回表操作 因为id是聚集索引所有节点的键，所以只要搜索该树结构，无需找出对应叶节点的具体数据。
image.png - explain select sm_id from TB_USER
这里走了smIdx索引，原因同上（一般也叫覆盖索引）
image.png
同样： - explain select sql_no_cache login_name,password from TB_USER
- explain select phone from TB_USER
总结上面：没有查询条件的前提下，当查询字段被索引覆盖的时候，会选择走该索引
- explain select password from TB_USER where login_name = '123'
根据组合索引原则，走了该覆盖索引
image.png
- explain select login_name from TB_USER where password = '123'
这里还不清楚，可能是优化器选择了索引
image.png
三，搜索单列+Order By
- explain select id from TB_USER order by id;
- explain select id from TB_USER order by phone;
都走了相应的索引，且没有回表，因为ID就是叶节点对应的值
image.png
- explain select id from TB_USER order by login_name,password;
- explain select id from TB_USER order by login_name;
- explain select id from TB_USER order by password;
这里比较特殊，因为password是索引loginPassIdx第二个键，所以走完索引后还需一次排序
image.png
四，带搜索条件
-
explain select * from TB_USER where phone = '123'
走索引
image.png -
explain select * from TB_USER where phone > '123'
这里可以用(possible_keys)索引，但是可能由于数据量的原因没有使用，最终走的全表，这个是由查询优化器定的
image.png -
explain select * from TB_USER where phone > '123' and sm_id = '11'
多个查询条件都带索引时，优化器会选择最合适的一个
image.png -
explain select * from TB_USER where phone > '123' and sm_id like '%11'
前缀的%不能使用索引
image.png -
explain select * from TB_USER where password = '11'
根据最左前缀匹配原则，password无法使用索引
image.png -
explain select * from TB_USER where login_name = '123' order by phone
先走loginPassIdx的索引，再做一次排序
image.png -
explain select * from TB_USER where login_name = '123' order by password
比较上面那条，这里走的用联合索引，减少一次排序
image.png
�总结：
1，首先根据查询条件选择最优的索引执行。
2，当查询结果为主键或为该索引的组成部分时，可以直接使用索引树上的键，即使用覆盖索引。
3，组合索引根据最左前缀匹配原则，查询条件中必须出现该组合索引的第一项。
4，like不能做前缀匹配'%XXX',可以做后缀'XXX%',后缀匹配可以转换成range查询
5，可以将order by 中字段和查询条件做成联合索引减少一次内存排序。
