const

通过聚簇索引 或者 唯一索引来查询 ，这里的二级索引必须是唯一索引才能是const，如果只是一个普通索引，在执行计划里叫做ref

  • 案例 ``sql CREATE TABLEsys_user(idvarchar(128) NOT NULL ',user_namevarchar(16) NOT NULL ,real_namechar(16) DEFAULT NULL ,passwordvarchar(255) NOT NULL ,phonevarchar(11) DEFAULT NULL,sexint(11) NOT NULL ,saltvarchar(45) NOT NULLemailvarchar(100) DEFAULT NULL ,avatarvarchar(255) DEFAULT NULL ,statusint(11) NULL DEFAULT NULL ,birthdaydatetime(0) NULL DEFAULT NULL ', -- 聚簇索引 PRIMARY KEY (id) USING BTREE, -- 唯一索引 UNIQUE INDEXindex_phone(phone) USING BTREE, -- 联合索引 INDEXindex_user_status(user_name,phone`) USING BTREE ) ENGINE = InnoDB CHARACTER ROW_FORMAT = Compact;

```

  • 通过唯一索引查

EXPLAIN SELECT * FROM sys_user WHERE phone='15106196276' ;
image.png

  • 通过聚簇索引查

EXPLAIN SELECT * FROM sys_user WHERE id ='1390219005465006082';
image.png

ref

通过普通二级索引，列等值才是ref，无论是单列还是多列（多列的话，按照最左原则顺序），查询速度也较快，先根据索引去筛选，再回源。等值查询。
有个例外，如果用 IS NULL这种语法的话，即使where字段是主键或者唯一索引，还是只能走ref方式。但是如果是针对一个二级索引同时比较了一个值还有限定了IS NULL，那么此时在执行计划里就叫做ref or null

  • 通过普通索引查

EXPLAIN SELECT * FROM sys_user WHERE user_name ='songhongwei';
image.png

  • 通过主键/唯一索引带 IS NULL语法

EXPLAIN SELECT * FROM sys_user WHERE phone is null;
image.png

ref_or_null

查询的时候，带上了 or 字段 is null，is not null，这种，则还要把null的值查出来，再回源，等值查询

  • 索引 + is null

EXPLAIN SELECT * FROM sys_user WHERE phone='15106196276' or phone is null;
image.png

  • 普通二级索引+ is null

这里把user_name的约束改为可为Null了
EXPLAIN SELECT * FROM sys_user WHERE user_name ='songhongwei'or user_name is null;
image.png

小结

const：代表使用的是唯一索引或者是主键的聚簇索引的等值查询
ref：代表使用的是二级普通索引等值查询或者是唯一索引/主键 is null / is not null
ref_or_null：代表使用的是索引等值查询+ 索引 or is null / is not null

range

利用索引作了范围查询

  • 唯一索引/普通索引范围查询

EXPLAIN SELECT * FROM sys_user WHERE phone>'18855209959';
image.png
EXPLAIN SELECT * FROM sys_user WHERE user_name >'songhongwei'
image.png

index

遍历二级索引，就可以拿到数据，不需要回表，简单的说就是这种只要遍历二级索引就可以拿到想要的数据，而不需要回表到聚簇索引的访问方式，就叫做index访问方式，也即，查询的字段信息全部包含在联合索引中，即使过滤条件不是按照最左原则顺序等值。

  • 案例

给sys_user添加联合索引user_name, phone, sex, salt，根据sex查询user_name, sex, phone字段信息
EXPLAIN SELECT user_name, sex, phone FROM sys_user WHERE sex=1;
image.png

  • 详解

假设有一个表，里面完整的字段联合索引是KEY(x1,x2,x3)，现在写一个SQL语句是select x1,x2,x3 from table where x2=xxx，因为x2不是联合索引的最左侧的那个字段，所以这个SQL是没办法直接从联合索引的索引树的根节点开始二分查找，快速一层一层跳转的，但是这个SQL里要查的几个字段，就是联合索引里的几个字段
所以针对这种SQL，在实际查询的时候，就会直接遍历KEY(x1,x2,x3)这个联合索引的索引树的叶子节点，所以此时针对这个SQL，会直接遍历KEY(x1,x2,x3)索引树的叶子节点的那些页，一个接一个的遍历，然后找到 x2=xxx 的那个数据，就把里面的x1，x2，x3三个字段的值直接提取出来

  • 回顾

聚簇索引的叶子节点放的是完整的数据页，里面包含完整的一行一行的数据，联合索引的叶子节点放的也是页，但是页里每一行就是索引字段和主键的值

all

全表扫描的查询方式称为all，即扫描聚簇索引的所有叶子节点，也就是一个表里一行一行数据去扫描

总结

SQL执行的方式const、ref、range，都是性能最好的方式，说明在底层直接基于某个索引树快速查找了数据了，但有的时候可能在用了索引之后，还会在回表到聚簇索引里查完整数据，接着根据其他条件来过滤。
然后index方式其实是扫描二级索引的意思，就是说不通过索引树的根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，这种速度还是比较慢的，尽量还是别出现这种情况。当然index方式怎么也比all方式好一些
all就是直接全表扫描了，也就是直接扫描聚簇索引的叶子节点，那是相当的慢，index虽然扫描的是二级索引的叶子节点，但是起码二级索引的叶子节点数据量比较小，相对all要快一些。
可能一个SQL里用多个索引，意思就是可能对多个索引树进行查找，接着用intersection交集、union并集的方式来进行合并，此时可能给在执行计划里也会看到这些字样，其实他就是告诉你，他查找了多个索引，做了一些结果集的交集或者是并集，而且这种方式也不一定是会发生的。