二级索引查询注意事项(2)--单表访问方法（三十七)

前面说了explain参数的type代表访问数据库的方法，如果用主键和二级索引，测试快的const方法，若用普通索引，则是ref，还有ref_or_null，range是代表区间查询，若用index则代表查询联合索引的非左边索引，后是all。

注意事项

我们先回忆一下二级索引+回表的查询方法：

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

这个sql里有两个条件，key1 = ‘abc’和key2 > 1000，优化器会根据single_table的数据来判断用哪个条件来作为二级索引查询，因为回表的数量越少，性能越高，可以用ref查询或者range查询，一般来说，固定常量都比范围查询的回表效率更高，也不一定，也可能ref固定常量值特别多，这里我们默认就用idx_key1来查询二级索引b+树。

先在key1的索引b+树叶子节点找到对应的id，。

回表阶段，之后再用b+树的id来查询聚簇索引的叶子节点，查询key2>1000范围的数据。

这里需要注意的是，idx_key1的b+树叶子节点里存的只有索引列和主键，索引步骤1里不会查询条件key2>1000,这个条件在步骤2里才开始范围查询。

明确range访问方法使用范围

对于b+树索引来说，只要索引列和常量使用=,<=>,<>,=,<,>，is null，is not null，between,!=，like就会产生区间。

这里特意强调一下or和and的区别：

cond1 and cond2：只有当1和2都为true，整个表达式才是true。

cond1 or cond2：只要有一个为true，这个表达式都是true。

所有搜索条件都可以使用某个索取的情况

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

上面这个sql里面有两个条件，key2 > 100和key2>200,因为需要取他们的交集，所以应该区key2>200，所以我们这里只需要吧key2>200的索引回表查询就好。

SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

如果是or呢，这时候需要取他们的并集，所以我们只需要吧key2>100的索引进行回表查询就好。

有的搜索条件无法使用索引情况

SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';

注意这里的key2是可以索引查询，但common_field无法进行索引查询，所以key2次查询的时候，在二级索引列是没有数据的，他的查询过程是先在key2的索引叶子节点找到数据，然后回表在查询common_field的数据，这时候步骤1查询索引时，会吧语句优化成：

SELECT * FROM single_table WHERE key2 > 100 AND TRUE;

因为后面的在查询索引b+树的时候不会使用到，在后面回表时候在用条件进行过滤，优化之后就是，

SELECT * FROM single_table WHERE key2 > 100;

再来看第二种情况：

SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

当这种情况下先优化成：

SELECT * FROM single_table WHERE key2 > 100 or TRUE;

在优化成

SELECT * FROM single_table WHERE TRUE;

所以，在or语句有的条件无法使用索引的情况下，百分百会使用全表查询的，因为后面的数据都需要，而后面的是没有索引的条件。

复杂情况下索引如何查询

举个例子：

SELECT * FROM single_table WHERE

(key1 > 'xyz' AND key2 = 748 ) OR

(key1 < 'abc' AND key1 > 'lmn') OR

(key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;

这里面有两个索引，key1和key2，common_field没有索引，所以这里如果先按二级索引b+树查询可以分为两种情况：

采用key1为二级索引查询：这时候其他条件在key1的b+树索引叶子节点都是没有数据的，回表才会去过滤，因为like的百分比在前面，也是无法走索引的，所以吧sql优化之后就是

(key1 > 'xyz' AND true ) OR

(key1 < 'abc' AND key1 > 'lmn') OR

(true AND key1 > 'zzz' AND (true OR true)) ;

这时候再优化一下就是：

(key1 > 'xyz') OR

(key1 < 'abc' AND key1 > 'lmn') OR

(key1 > 'zzz') ;

因为key1 < ‘abc’ and key1 >’lmn’永远为false，所以这时候继续优化就是：

(key1 > 'xyz') OR (key1 > 'zzz') ;

这时候区他们的并集，所以查询索引b+树其实只需要查询key1>xyz的数据进行回表。

采用key2为索引进行查询索引b+树：

(true AND key2 = 748 ) OR

(true AND key1 > true) OR

(true AND true AND (key2 < 8000 OR true)) ;

这里优化之后就是：

（Key2 = 748）or true

继续优化就是where true，这时候就是直接走全表查询，所以这种情况下会走key1的b+树索引。

文章来源：知乎平台 原文地址：https://zhuanlan.zhihu.com/p/409983529