MySQL索引失效分析及解决方案

1、使用!= 或者 <> 

SELECT * FROM user WHERE name != 'andrew';

解决办法

SELECT id FROM user WHERE name != 'andrew';

在MySQL中，不等号在主键字段和唯一索引字段中会走索引，在普通索引的字段上不会走索引。如果想让普通索引生效，查询主键就可以了。这里涉及到回表及索引原理的问题，在这里就不做过多介绍了。

2、字段类型不同

当查询条件中的列进行了隐式类型转换时 MySQL 无法使用索引进行优化。例如，列是字符串类型，但查询条件使用了数值类型，或者列是数值类型，但查询条件使用了字符串类型。

解决办法

查询字段的类型与值类型保持一致。

3、索引列使用函数

SELECT * FROM user WHERE DATE(create_at) = '2020-09-03';

如果使用函数在索引列，这是不走索引的。

解决办法

SELECT * FROM user WHERE create_at BETWEEN STR_TO_DATE('2023-06-25 00:00:00','%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2023-06-26 23:59:59','%Y-%m-%d %H:%i:%s');

SELECT * FROM user WHERE create_at BETWEEN '2023-06-25 00:00:00' AND '2023-06-26 23:59:59';

SELECT * FROM user WHERE create_at>= '2023-06-25 00:00:00' AND create_at< '2023-06-26 23:59:59';

4、索引列使用运算符

SELECT * FROM user WHERE age - 1 = 20;

如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。

解决办法

用 age = 19 查询不香吗？干嘛非要多些一举？

5、使用OR关键字

OR 是日常使用最多的操作关键字了，但使用不当，也会导致索引失效。

SELECT * FROM user WHERE name = 'andrew' OR age = 28

解决办法

SELECT * FROM user WHERE name = 'andrew'
UNION ALL 
SELECT a.* FROM user WHERE age = 28

6、Like 左匹配

SELECT * FROM user WHERE name LIKE '%andrew';

当 % 放在匹配字段前是不走索引的，放在后面才会走索引。

解决办法

SELECT * FROM user WHERE name LIKE 'andrew%';

7、NOT IN、NOT EXISTS导致索引失效

SELECT * FROM user WHERE id NOT IN (48,49,51)

解决办法

SELECT id FROM user WHERE id NOT IN (48,49,51)

8、使用 *（星）查询

SELECT * FROM user

使用 * (星) 查询可能使用最多的，很多时候明明符合走索引的条件，却不走索引。所以在使用过程中尽量少使用或不使用 *（星）查询。

9、不满足最左匹配原则

 该索引字段的顺序是：name、age、code

SELECT * FROM USER WHERE code=1 AND age=12

如果在使用组合索引时，没注意最左前缀原则，导致索引失效。

10、最优选择

有时候明明符合走索引的条件，却不走索引。是因为 MySQL 会在选择索引的时候进行优化，如果 MySQL 认为全表扫描比走索引+回表效率高， 那么他会选择全表扫描。

为了测试方便查询时使用的 *（星），在《阿里巴巴开发手册》中有明确规定，查询sql中禁止使用select * 。使用 *（星）查询，走的是全表扫描，没有用到任何索引，查询效率是非常低的。