MySQL高级知识点（五）

MySQL高级知识点（五）

单表查询优化

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）会提高检索效率

6、使用不等会失效

在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描

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;

对应结果

结论：尽量不要使用不等于

7、使用NULL值要小心

在使用

IS NULL
或者
IS NOT NULL

时，可能会导致索引失效

但是如果允许字段为空，则

• 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;

对应结果

8、模糊查询加右边

要使用模糊查询时，百分号最好加在右边，而且进行模糊查询的字段必须是单值索引

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 '%风%';

对应结果

结论：对索引进行模糊查询时，最好在右边加百分号。必须在左边或左右加百分号时，需要用到覆盖索引来提升查询效率

9、字符串加单引号

当字段为字符串时，查询时必须带上单引号。否则会发生自动的类型转换，从而发生全表扫描

用于查询的表

其中card_id字段为varchar类型，且设置了单值索引

SQL语句

--使用了单引号
EXPLAIN SELECT card_id FROM person WHERE card_id = '1';

--未使用单引号，发生自动类型转换
EXPLAIN SELECT card_id FROM person WHERE card_id = 1;

对应结果

10、尽量不用or查询

如果使用or，可能导致索引失效。所以要减少or的使用，可以使用 union all 或者 union 来替代：

SQL语句

--使用or进行查询
EXPLAIN SELECT * FROM t_emp WHERE age = 90 OR NAME = '风清扬';

对应结果