mysql优化-索引失效场景

1. 索引字段不独立

• 索引字段进行了表达式计算
• 事先计算好表达式的值，再传过来，避免在SQL where条件=的左侧做计算

select *
from employees
where emp_no + 1 = 10003;

select *
from employees
where emp_no = 10002;

索引字段是函数的参数

预先计算好结果，再传过来，在where条件的左侧，不要使用函数；或者使用等价的SQL

select *
from employees
where SUBSTRING(first_name, 1, 3) = 'Geo';

select *
from employees
where first_name like 'Geo%';

2. 使用了左模糊

尽量避免使用左模糊，如果避免不了，可以考虑使用搜索引擎去解决

select *
from employees
where first_name like '%Geo%';

select *
from employees
where first_name like 'Geo%';

3. 使用OR查询的部分字段没有索引

分别为first_name以及last_name字段创建索引

union 替代 or

select *
from employees
where first_name = 'Georgi'
or last_name = 'Georgi';

4. 字符串条件未使用''引起来

规范地编写SQL

select *
from dept_emp
where dept_no = 3;

select *
from dept_emp
where dept_no = '3'

5. 不符合最左前缀原则的查询

存index(last_name, first_name)

explain select *
from employees
where first_name = 'Facello';

调整索引的顺序，变成index(first_name,last_name)/index(first_name)

Mysql 8 之后会进行跳表扫描

6. 索引字段建议添加NOT NULL约束

• 单列索引无法储null值，复合索引无法储全为null的值
• 查询时，采用is null条件时，不能利用到索引，只能全表扫描

官方建议尽量把字段定义为NOT NULL

MySQL :: MySQL 8.0 Reference Manual :: 10.4.1 Optimizing Data Size

select *
from `foodie-shop-dev`.users
where mobile is null;

把索引字段设置成NOT NULL，甚至可以把所有字段都设置成NOT NULL并为字段设置默认值

7. 隐式转换导致索引失效

select emp.*, d.dept_name
from employees emp
left join dept_emp de
on emp.emp_no = de.emp_no
left join departments d
on de.dept_no = d.dept_no
where de.emp_no = '100001';

在创建表的时候尽量规范一点，比如统一用int，或者bigint

8. 使用不等于的时候无法使用索引会导致全表扫描

9. 不能使用索引中范围条件右边的列 范围之后失效

10. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select

11. asc和desc混用