如何使用模拟查询才能命中索引?
a)like 'xx%' 非前导模糊查询可以命中索引:
EXPLAIN SELECT * FROM staff WHERE name LIKE 'zhang%';
b)使用覆盖索引,查询字段必须要建立覆盖索引字段
EXPLAIN SELECT name,work_age FROM staff WHERE name LIKE '%zhang%';
联合索引是 idx_name_work_age_position
9、字符串类型不加单引号索引失效
字符串的数据类型一定要将常量值使用单引号,这个在日常开发中要特别注意的,数据类型出现隐式转换的时候不会命中索引。
不加单引号索引失效
EXPLAIN SELECT * FROM staff WHERE name = 1;
name=1 类似于在该字段上做了一个函数运算,因此不会走索引的。
加单引号会命中索引:
EXPLAIN SELECT * FROM staff WHERE name = 'zhangsan';
10、OR 使用多数情况下索引会失效
EXPLAIN SELECT * FROM staff WHERE OR work_age = 2;
尽管 name 和 work_age 是联合索引,但是 work_age 列上并没有建索引,所以使用了 OR 不会走索引。
如果 OR 前后都是联合索引带头大哥 name 字段,那么就会用到索引,如下所示:
因 OR 后面的条件列中没有索引,会走全表扫描。存在全表扫描的情况下,就没有必要多一次索引扫描增加IO访问。
可使用覆盖索引查询:
EXPLAIN SELECT name,work_age FROM staff WHERE OR work_age = 2;
** OR 后面也使用索引列:**
EXPLAIN SELECT * FROM staff WHERE OR s_name='wangwu';
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';
使用 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';
执行结果中不再出现内部临时表,具体用的时候结合实际需求来定是否使用。
11、负向查询条件不能使用索引,可以优化为 IN 查询
负向查询条件包括:!=、<>、NOT IN、NOT EXISTS、NOT LIKE 等。
不会命中索引: