前面说了explain参数的type代表访问数据库的方法,如果用主键和唯一二级索引,测试最快的const方法,若用普通索引,则是ref,还有ref_or_null,range是代表区间查询,若用index则代表查询联合索引的非最左边索引,最后是all。
访问方法access method---单表访问方法(三十六)
我们先回忆一下二级索引+回表的查询方法:
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里才开始范围查询。
对于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+树索引。