Mysql - 索引失效的原因

1）、like查询以“%”开头(非要写在最左边，就使用覆盖索引；如果开头、结果都有“%”，也不会使用索引，走的是全表扫描);

2）、or语句前后没有同时使用索引【可使用 union 代替or】；

3）、组合索引中不是使用第一列索引；（不符合最左匹配原则）

4）、字符串类型的字段没有使用引号引起来;【查询where条件数据类型不匹配也无法使用索引，字符串与数字比较不使用索引，因为正则表达式不使用索引，如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描】

5）、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效；(数据量少于300w)

6）、在索引字段上使用“is null”，“is not null” ，“<>”，“!=”等；

SELECT id FROM table WHERE num != 0      – 不能使用索引

SELECT id FROM table WHERE num is null   – 不能使用索引

7）、对索引字段进行计算操作、使用函数; 【sql中使用to_days()、date()、date_sub()等函数时会导致索引失效，其原因是：使用部分MySQL的内部函数时会导致索引失效】

select * from doc where YEAR(create_time) <= ‘2016’; – 不能使用索引
select * from doc where create_time<= ‘2016-01-01’; – 可以使用索引
select * from order where date < = CURDATE()； – 不能使用索引
select * from order where date < = ‘2018-01-2412:00:00’; – 可以使用索引
select id from t where substring(name,1,3)=’abc’ – 不能使用索引
select id from t where name like ‘abc%’ – 可以使用索引
select id from t where num/2=100 – 不能使用索引
select id from t where num=100*2 – 可以使用索引

8）、索引散列值（重复多）不适合建索引，例：性别、状态等字段不适合。

9）、两列数据做比较，即便两列都创建了索引，索引也会失效

select * from t_user where id > age;

10）、查询的参数值不同也可能导致索引失效【索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描】

Mysql - 哪些字段适合建立索引