索引失效的情况和解决方法

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM user WHERE name LIKE '%用%'
​
-- 优化方式：尽量在字段后面使用模糊查询
SELECT * FROM user WHERE name LIKE '用%'

如果需求是要在前面使用模糊查询，使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()

2. 尽量避免使用in 和not in，会导致引擎走全表扫描

SELECT * FROM user WHERE id IN (2,3)
​
-- 优化方式：如果是连续数值，可以用between代替
SELECT * FROM user WHERE id BETWEEN 2 AND 3

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描

select * from user WHERE id = 1 or id = 3
​
-- 优化方式：可以用union（且）代替or
select * from user WHERE id = 1
UNION
select * from user WHERE id = 3

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描

select * from user WHERE age is null 
​
-- 优化方式：可以给字段添加默认值0，对0值进行判断
select * from user WHERE age = 0

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

可以将表达式、函数操作移动到等号右侧 ( 不建议这样写会造成运行效率降低 )

-- 全表扫描
SELECT * FROM user WHERE age/10 = 9
-- 走索引
SELECT * FROM user WHERE age = 10*9

6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描

SELECT name, age, pwd FROM user and 1=1
​
-- 优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and
SELECT name, age, pwd FROM user WHERE 1 and 1

7. 查询条件不能用 < > 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。要去java进行判断，不能对数据库进行操作

8. where条件仅包含复合索引非前置列

如下：复合（联合）索引包含a1，b2，c3三列，但SQL语句没有包含索引前置列"a1"，按照MySQL联合索引的最左匹配原则，不会走联合索引

-- 创建联合索引
create index user_index on user (name,age,pwd)
-- 这种方式是不会走索引的
select name,age,pwd from user where age=123 and pwd=''

9. 隐式类型转换造成不使用索引

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select name from table where name = 123;