「这是我参与2022首次更文挑战的第25天，活动详情查看：2022首次更文挑战」

单表查询优化

1、全值匹配很快捷

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 执行结果的前提下，自动地优化

结论：全职匹配我最爱指的是，查询的字段按照顺序在索引中都可以匹配到

2、最佳左前缀法则

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 = '风清扬';

对应结果

可以看到，查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效

原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用

3、索引列上不计算

不在索引列上做任何操作（计算、函数、(自动 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函数以后，导致索引完全失效

结论：等号左边无计算

4、范围之后全失效

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字段使用范围查询后，使得范围后面的索引失效了

建议：将可能做范围查询的字段的索引顺序放在最后

结论：使用范围查询后，如果范围内的记录过多，会导致索引失效，因为从自定义索引映射到主键索引需要耗费太多的时间，反而不如全表扫描来得快

5、覆盖索引多使用

SQL语句

--查询所有字段
EXPLAIN SELECT * FROM t_dept WHERE id = 1;

--查询索引字段
EXPLAIN SELECT id FROM t_dept WHERE id = 1;

对应结果

结论：使用覆盖索引（Using index）会提高检索效率