Mysql索引失效的情况

MySQL索引的使用能够显著提高查询效率，但在某些情况下，索引可能不会被利用，这就是所谓的索引失效。理解和避免这些情况有助于优化查询性能。以下是一些常见的导致MySQL索引失效的情况分类说明：

1. 查询条件中的索引列被函数或表达式包裹

当在WHERE子句中用到的索引列被函数或计算表达式包裹时，索引可能不会被使用。例如：

• 使用函数转换：SELECT * FROM table WHERE YEAR(dateColumn) = 2021;
• 使用算术运算：SELECT * FROM table WHERE column + 1 = 5;

2. 错误的数据类型隐式转换

如果查询的条件使用了与列数据类型不匹配的类型，MySQL可能会进行隐式的数据类型转换，这可能导致索引失效。举例：

• SELECT * FROM table WHERE intColumn = '123';（intColumn是整型，但条件中使用了字符串）

3. 使用前缀或后缀通配符的LIKE查询

当使用LIKE查询，并且通配符（%或_）在查询字符串的开始位置时，索引将不会被利用：

• SELECT * FROM table WHERE column LIKE '%value';

4. 使用OR条件连接的不同列

当使用OR连接多个条件，且这些条件针对的是不同列时，索引可能不会被有效使用，尤其是当这些列分别有各自的索引时：

• SELECT * FROM table WHERE column1 = 10 OR column2 = 20;

5. ORDER BY和GROUP BY中错误使用索引

• 当你在ORDER BY或GROUP BY子句中使用多个列进行排序或分组时，如果这些列在索引中的顺序与你在查询中使用的顺序不匹配，索引可能不会被有效使用。

6. 复合索引中的列未按索引顺序使用

• 如果一个查询只使用了复合索引的部分列，或者列的使用顺序与索引中的顺序不匹配，那么索引可能不会被最优地使用。

7. 使用!=或<>操作符

• 使用不等于（!=或<>）操作符可能会让索引失效，因为这会促使MySQL扫描整个表来寻找不匹配的行。

8. 索引列参与了运算或字符串连接操作

• 如果WHERE子句中对索引列进行了计算或拼接字符串的操作，那么索引可能不会被使用。

9、Sql in的参数过多导致数据库放弃走索引

10、如何避免索引失效？

• 尽量避免在WHERE子句的索引列上使用函数或运算。
• 确保查询条件中的数据类型与列的数据类型一致。
• 尽量使用索引列进行过滤，并避免使用前缀通配符。
• 考虑使用联合索引，并在查询中按索引顺序使用列。
• 对于复杂的OR条件，可以考虑重写查询或使用UNION来代替。

了解这些导致索引失效的情况，并在实践中避免它们，可以帮助提升MySQL的查询性能。

11、对第2条的补充

数据库字段是int（数字）类型，传入参数是 '123' 字符串类型，因为这个入参可以正确转换为数字，因此它依然可以走索引。我试的版本中，即便是你输入的 'ABC'，依然走了索引。盲猜mysql是按照一定的算法，将字母字符串转换成了数字。字符串转换数字是确定的，也就是说一个给定的字符串在一定的转换逻辑下有一个确定的值，因此就可以走索引。

反过来，数据库字段是字符，入参是数字，这个不会走索引，但是依然可以查询到结果，而且正确。 执行计划是mysql执行了全表扫描，应该是把所有字段上的值转成了数字（至少是需要按照一定的算法一一对比）进行比较，也就是列参与了计算。‘012’=12 ，mysql是考虑到了零前导问题，所以不会简单的把数字转成字符串，进而无法走到索引。

PS：我们学习技术一定要实践，因为这些技术都是人发明的技术，它不像数学定理，物理公式那么确认，有前置条件一定可以推导出后续结果。鄙视一个背书学习IT的人，听到我说隐式转换也会走索引，一脸鄙视的态度，殊不知自己才是个大SB。