一本彻底搞懂MySQL索引优化EXPLAIN百科全书 (8)

file

如何使用模拟查询才能命中索引?

a)like 'xx%' 非前导模糊查询可以命中索引:

EXPLAIN SELECT * FROM staff WHERE name LIKE 'zhang%';

file

b)使用覆盖索引,查询字段必须要建立覆盖索引字段

EXPLAIN SELECT name,work_age FROM staff WHERE name LIKE '%zhang%';

联合索引是 idx_name_work_age_position

file

9、字符串类型不加单引号索引失效

字符串的数据类型一定要将常量值使用单引号,这个在日常开发中要特别注意的,数据类型出现隐式转换的时候不会命中索引。

不加单引号索引失效

EXPLAIN SELECT * FROM staff WHERE name = 1;

file

name=1 类似于在该字段上做了一个函数运算,因此不会走索引的。

加单引号会命中索引:

EXPLAIN SELECT * FROM staff WHERE name = 'zhangsan';

file

10、OR 使用多数情况下索引会失效

EXPLAIN SELECT * FROM staff WHERE OR work_age = 2;

file

尽管 name 和 work_age 是联合索引,但是 work_age 列上并没有建索引,所以使用了 OR 不会走索引。

如果 OR 前后都是联合索引带头大哥 name 字段,那么就会用到索引,如下所示:

file

因 OR 后面的条件列中没有索引,会走全表扫描。存在全表扫描的情况下,就没有必要多一次索引扫描增加IO访问。

可使用覆盖索引查询:

EXPLAIN SELECT name,work_age FROM staff WHERE OR work_age = 2;

file

** OR 后面也使用索引列:**

EXPLAIN SELECT * FROM staff WHERE OR s_name='wangwu';

file

s_name 是唯一索引,name是联合索引第一个字段,两者使用 OR 查询结果 Extra 显示 Using sort_union(idx_name_age_position,idx_s_name); Using where 解释一下。

如果执行计划 Extra 列出现了 Using sort_union(...) 的提示,说明准备使用 Sort-Union 索引合并的方式执行查询。如果出现了 Using intersect(...) 的提示,说明准备使用 Intersect 索引合并方式执行查询,如果出现了 Using union(...) 的提示 ,说明准备使用 Union 索引合并方式执行查询。 括号中 ... 表示需要进行索引合并的索引名称。

使用UNION优化改进:

EXPLAIN SELECT * FROM staff WHERE UNION SELECT * FROM staff WHERE s_name = 'zs';

file

使用 UNION 执行计划中出现了第三条记录,Extra 中出现 Using temporary,说明 MySQL因为不能有效利用索引,建立了内部临时表来执行查询。当你在使用 DISTINCT 、GROUP BY、UNION 等子句中的查询过程中,都有可能会出现该扩展信息。

使用UNION ALL进一步优化:

EXPLAIN SELECT * FROM staff WHERE UNION ALL SELECT * FROM staff WHERE s_name = 'zs';

file

执行结果中不再出现内部临时表,具体用的时候结合实际需求来定是否使用。

11、负向查询条件不能使用索引,可以优化为 IN 查询

负向查询条件包括:!=、<>、NOT IN、NOT EXISTS、NOT LIKE 等。

不会命中索引:

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zyxwsz.html