SQL语句
--建立符合索引(age, deptId, name) CREATE INDEX idx_emp_ade ON t_emp(age, deptId, NAME); --查找 EXPLAIN SELECT empno FROM t_emp WHERE age = 90; EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1; EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND name = '风清扬'; --和上一条SQL语句中WHERE后字段的顺序不同,但是不影响查询结果 EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1 AND name = '风清扬' AND age = 90;
对应结果
可以看到,复合索引都被用到了,并且SQL中查询字段的顺序,跟使用索引中字段的顺序,没有关系。优化器会在不影响 SQL 执行结果的前提下,自动地优化
结论:全职匹配我最爱指的是,查询的字段按照顺序在索引中都可以匹配到
SQL语句
--先删除之前创建的单值索引 DROP INDEX idx_dept_id ON t_emp; --查询,未按照最佳左前缀法则 EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1; EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1 AND name = '风清扬'; --查询,部分按照最佳左前缀法则(age字段和复合索引匹配,但name没有) EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND name = '风清扬'; --查询,完全按照最佳左前缀法则 EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1; EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND name = '风清扬';
对应结果
可以看到,查询字段与索引字段顺序的不同会导致,索引无法充分使用,甚至索引失效
原因:使用复合索引,需要遵循最佳左前缀法则,即如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
结论:过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用
不在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换),可能会导致索引失效而转向全表扫描
SQL语句
--直接查询 EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND NAME = '风清扬'; --使用MySQL函数查询 EXPLAIN SELECT empno FROM t_emp WHERE LEFT(age,2) = 90 AND deptId = 1 AND name = '风清扬';
对应结果
可以看出,当age字段使用了left函数以后,导致索引完全失效
结论:等号左边无计算
SQL语句
--范围查询 EXPLAIN SELECT empno FROM t_emp WHERE age > 50 AND deptId = 1 AND name = '风清扬'; EXPLAIN SELECT empno FROM t_emp WHERE age = 50 AND deptId > 1 AND NAME = '风清扬'; --未使用范围查询 EXPLAIN SELECT empno FROM t_emp WHERE age = 50 AND deptId = 1 AND name = '风清扬';
对应结果
可以看出,当对age字段使用范围查询后,使得范围后面的索引失效了
建议:将可能做范围查询的字段的索引顺序放在最后
结论:使用范围查询后,如果范围内的记录过多,会导致索引失效,因为从自定义索引映射到主键索引需要耗费太多的时间,反而不如全表扫描来得快
SQL语句
--查询所有字段 EXPLAIN SELECT * FROM t_dept WHERE id = 1; --查询索引字段 EXPLAIN SELECT id FROM t_dept WHERE id = 1;
对应结果
结论:使用覆盖索引(Using index)会提高检索效率
在使用不等于(!= 或者<>)时,有时会无法使用索引会导致全表扫描
SQL语句
--SQL语句中有不等于 EXPLAIN SELECT * FROM t_emp WHERE age != 90; EXPLAIN SELECT * FROM t_emp WHERE age <> 90; --SQL语句中没有不等于 EXPLAIN SELECT * FROM t_emp WHERE age = 90;
对应结果
结论:尽量不要使用不等于
在使用
IS NULL 或者 IS NOT NULL
时,可能会导致索引失效
但是如果允许字段为空,则
SQL语句
EXPLAIN SELECT * FROM t_emp WHERE age IS NULL; EXPLAIN SELECT * FROM t_emp WHERE age IS NOT NULL;
对应结果
要使用模糊查询时,百分号最好加在右边,而且进行模糊查询的字段必须是单值索引
SQL语句
--创建单值索引 CREATE INDEX idx_emp_name ON t_emp(NAME); --进行模糊查询 EXPLAIN SELECT * FROM t_emp WHERE name LIKE '%风'; EXPLAIN SELECT * FROM t_emp WHERE name LIKE '风%'; EXPLAIN SELECT * FROM t_emp WHERE name LIKE '%风%';
对应结果
可以看出,对索引使用模糊查询时,只有当百分号在右边,索引为单值索引且模糊查询语句在最右边时,索引才会生效
其他情况均失效了
但是有时必须使用其他类型的模糊查询,这时就需要用覆盖索引来解决索引失效的问题
SQL语句
EXPLAIN SELECT name FROM t_emp WHERE name LIKE '%风'; EXPLAIN SELECT name FROM t_emp WHERE name LIKE '风%'; EXPLAIN SELECT NAME FROM t_emp WHERE name LIKE '%风%';
对应结果
结论:对索引进行模糊查询时,最好在右边加百分号。必须在左边或左右加百分号时,需要用到覆盖索引来提升查询效率
当字段为字符串时,查询时必须带上单引号。否则会发生自动的类型转换,从而发生全表扫描
用于查询的表
其中card_id字段为varchar类型,且设置了单值索引
SQL语句
--使用了单引号 EXPLAIN SELECT card_id FROM person WHERE card_id = '1'; --未使用单引号,发生自动类型转换 EXPLAIN SELECT card_id FROM person WHERE card_id = 1;
对应结果
如果使用or,可能导致索引失效。所以要减少or的使用,可以使用 union all 或者 union 来替代:
SQL语句
--使用or进行查询 EXPLAIN SELECT * FROM t_emp WHERE age = 90 OR NAME = '风清扬';
对应结果