1、可能导致索引失效的场景

  • 索引列不独立。独立是指列不能是表达式的一部分，也不能是函数的参数
  • 使用了左模糊
  • 使用OR查询的部分字段没有索引
  • 字符串条件未使用’’引起来
  • 不符合最左前缀原则的查询
  • null查询
  • 隐式转换导致索引失效

1.1、索引列不独立

（1）索引字段进行了表达式计算

EXPLAIN SELECT * from employees where emp_no + 1 = 10002
image.png
解决方案：
事先计算好表达式的值，避免在SQL where条件的左侧做计算
EXPLAIN SELECT * from employees where emp_no = 10001
image.png

（2）索引字段是函数的参数

create index fir_name_idx on employees(first_name)
EXPLAIN SELECT * from employees where SUBSTRING(first_name,1,3) = 'Geo'
image.png
解决方案：
预先计算好结果再传过来，不要再where条件的左侧使用函数，或者使用等价的sql

等价sql:
EXPLAIN SELECT * from employees where first_name like 'Geo%'
image.png

1.2、使用了左模糊

EXPLAIN SELECT * from employees where first_name like '%Geo'

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

1.3、使用OR查询的部分字段没有索引

EXPLAIN SELECT * from employees where first_name like 'Geo%' or last_name like '%As'
first_name有索引而last_name无索引
image.png
解决方案：额外为or的其他字段添加索引

1.4、字符串条件未使用’’引起来

EXPLAIN SELECT * from dept_emp where dept_no = 3
解决方案：规范的编写sql

1.5、不符合最左前缀原则的查询

前置条件

  1. drop INDEX fir_name_idx on employees
  2. create index name_idx on employees(first_name,last_name)

EXPLAIN SELECT * from employees where last_name like '%As'
image.png
解决方案：调整索引顺序，或者单独创建索引

1.6、null查询

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

单列索引无法存储null值，复合索引无法存储全为null的值，查询时采用 is null条件时，不能利用索引，只能全表扫描
**
解决方案：把索引字段设置为NOT NULL

1.7、隐式转换

EXPLAIN select * from employees emp left join dept_emp de on emp.emp_no = de.emp_no

employees表emp_no字段和dept_emp表emp_no字段类型不一致

解决方案：相同字段类型一致