-- 对于所有本应该走索引的，当mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描
-- 1. =走索引，!=、<>也走索引
explain select id2 from user where varchar_short4 = '0'; -- = 范围小，肯定走索引。ref。key_len=11
explain select id2 from user where varchar_short4 = '0000'; -- = 范围大，肯定走索引。ref
explain select id2 from user where varchar_short4 != '0000'; -- != 范围小， 走索引。range
explain select id2 from user where varchar_short4 != '0'; -- != 范围大， 不走索引。
-- 2. <、>、>=、<= 和 between、not between均走索引
explain select id2 from user where varchar_short4 <= '0'; -- <= 范围小， 走索引。range
explain select id2 from user where varchar_short4 <= '0000'; -- <= 范围大，不走索引。
explain select id2 from user where varchar_short4 between 'a' and 'z'; -- between 范围小， 走索引。range
explain select id2 from user where varchar_short4 between '0' and 'z'; -- between 范围大，不走索引。
explain select id2 from user where varchar_short4 not between '0' and 'z'; -- not between 范围小， 走索引。range
explain select id2 from user where varchar_short4 not between 'a' and 'z'; -- not between 范围大，不走索引。
-- 3.in走索引，not in走索引，in 1个值时同=。in 1个变量，自动转为=。
explain select id2 from user where varchar_short4 in ('0'); -- in 范围小，肯定走索引。ref
explain select id2 from user where varchar_short4 in ('0000'); -- in 范围大，肯定走索引。ref
explain select id2 from user where varchar_short4 in ('0','1'); -- in 范围小， 走索引。range
explain select id2 from user where varchar_short4 in ('0000','1'); -- in 范围大， 不走索引。
explain select id2 from user where varchar_short4 not in ('0000'); -- not in 范围小， 走索引。range
explain select id2 from user where varchar_short4 not in ('0'); -- not in 范围大， 不走索引。
-- 4.exists走索引，not exists不走索引【需细化】
explain select id2 from user o where exists (select 1 from user where id =o.id and varchar_short4 ='0'); -- exists范围小， 走索引。ref
explain select id2 from user o where exists (select * from user where varchar_short4 =o.varchar_short4 and varchar_short4 ='0'); -- exists范围小， 走索引。ref
explain select id2 from user o where exists (select * from user where varchar_short4 =o.varchar_short4 and varchar_short4 ='0000'); -- exists范围大， 不走索引。
explain select id2 from user o where not exists (select id2 from user where varchar_short4 =o.varchar_short4 and varchar_short4 ='0000'); -- not exists肯定不走索引。
-- 5.null,同=
explain select id2 from user where varchar_short6 is null; -- is null 范围小， 走索引。ref
explain select id2 from user where varchar_short5 is null; -- is null 范围大， 走索引。ref
explain select id2 from user where varchar_short5 is not null; -- is not null 范围小， 走索引。range，
explain select id2 from user where varchar_short6 is not null; -- is not null 范围大，不走索引。
-- like
explain select id2 from user where varchar_short4 like 'a'; -- like前缀 范围小， 走索引。range
explain select id2 from user where varchar_short4 like '0000'; -- like前缀 范围大，不走索引。
explain select id2 from user where varchar_short4 like 'a%'; -- like前缀 范围小， 走索引。range
explain select id2 from user where varchar_short4 like '0000%'; -- like前缀 范围大，不走索引。
explain select id2 from user where varchar_short4 like '%a'; -- like后缀 范围小，不走索引。
explain select id2 from user where varchar_short4 not like '0000%'; -- like前缀 范围小，不走索引。
-- distinct
explain select distinct varchar_short4 from user; -- 范围小，走索引。range
explain select distinct varchar_mid2 from user; -- 范围大，走索引。range
explain select distinct varchar_short3 from user;
-- order by
explain select id2 from user order by varchar_short4 limit 1000; -- 走索引。index
explain select id2 from user order by varchar_short3 limit 1000;
-- group by
explain select varchar_short4 from user group by varchar_short4 limit 1000; -- 走索引。range
explain select varchar_short3 from user group by varchar_short3 limit 1000;
-- having。having是group by后，属于聚合函数，肯定不走索引
explain select varchar_short2 from user group by varchar_short2 having varchar_short2 = '00'; -- 不走索引
explain select varchar_short1 from user group by varchar_short1 having varchar_short1 = '00';
-- and/or
explain select id2 from user where varchar_short4 = '0' and varchar_short5 = '0'; -- 走索引。index_merge 。key_len=11,11
explain select id2 from user where varchar_short4 = '0' or varchar_short5 = '0'; -- 走索引。index_merge 。key_len=11,11
-- 类型隐式转换
explain select id2 from user where varchar_short4 = '11'; -- 无类型转换， 走索引
explain select id2 from user where varchar_short4 = 11 ; -- 有类型转换，不走索引。对于varchar，查的数据也是错的
explain select id2 from user where id = '11'; -- 无类型转换， 走索引
explain select id2 from user where id = 11; -- 无类型转换， 走索引
-- 函数、运算、两列比较
explain select id2 from user where lower(varchar_short4) = '00'; -- 函数，不走索引
explain select id2 from user where varchar_short4 || '0' = '00'; -- 函数，不走索引
explain select id2 from user where id + 1 = 100; -- 运算，不走索引
explain select id2 from user where id = 100 - 1; -- 不运算，走索引
explain select id2 from user where varchar_short4 = varchar_short5; -- 两列比较，不走索引
-- 联合索引。最左匹配原则。与where 后面的字段顺序无关，sql优化器会自动调整。其它情况与单字段索引类type、失效原则一致
explain select id2 from user2 where varchar_short7 = '0' and varchar_short8 = '0' and varchar_short9 = '0'; -- 走索引,key_len=33
explain select id2 from user2 where varchar_short9 = '0' and varchar_short8 = '0' and varchar_short7 = '0'; -- 走索引,key_len=33
explain select id2 from user2 where varchar_short9 = '0' and varchar_short8 = '0' and varchar_short7 = '0'; -- 走索引,key_len=33
explain select id2 from user2 where varchar_short7 = '0' and varchar_short8 = '0'; -- 走索引,key_len=22
explain select id2 from user2 where varchar_short7 = '0' and varchar_short9 = '0'; -- 走索引,key_len=11
explain select id2 from user2 where varchar_short7 = '0'; -- 走索引,key_len=11
explain select id2 from user2 where varchar_short8 = '0' and varchar_short9 = '0'; -- 不走索引
explain select id2 from user2 where varchar_short8 = '0'; -- 不走索引
explain select id2 from user2 where varchar_short9 = '0'; -- 不走索引
explain select id2 from user2 where varchar_short7 in( '0','1'); -- 走索引,key_len=11
explain select id2 from user2 where varchar_short7 in( '0','1') and varchar_short8 = '0' and varchar_short9 = '0';-- 走索引,key_len=33
explain select id2 from user2 where varchar_short7 > '1'; -- 走索引,key_len=11
explain select id2 from user2 where varchar_short7 > '1' and varchar_short8 = '0' and varchar_short9 = '0';-- 走索引,key_len=33
explain select id2 from user2 where varchar_short7 like '1%' -- 走索引,key_len=11
explain select id2 from user2 where varchar_short7 like '1%' and varchar_short8 = '0' and varchar_short9 = '0'; -- 走索引,key_len=33
explain select id2 from user2 where varchar_short7 = '0' or varchar_short8 = '0'; -- 不走索引
explain select id2 from user ;
explain select * from user where bigint2 = 1 limit 1;
explain select distinct id2 from user where id2 > '1';
explain select id2 from user limit 100,1;
select * from user;
explain select id2 from user where id2 = '1'; -- =范围小，肯定走索引。const
explain select id2 from user where id2 != '1'; -- =范围小，肯定走索引。const