MySql索引实例

回顾一下以前写过的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

image.png

一，没有搜索条件的时候

• 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

image.png image.png

总结上面：没有查询条件的前提下，当查询字段被索引覆盖的时候，会选择走该索引

---

• 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

image.png

• explain select id from TB_USER order by login_name,password;
• explain select id from TB_USER order by login_name;

image.png

• 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 中字段和查询条件做成联合索引减少一次内存排序。