SQL优化：MySQL索引失效的各种情况总结

1、当对列使用函数时，该列的索引不起作用。
****

例如：substring(字段名,1,2)='xxx'

2.对列进行运算(+,-,*,/,!等)。
该列的索引不起作用。

例如：select*fromtestwhereid-1=9;//拼写错误；

select*fromtestwhereid=10;//拼写正确；

3.LIKE在某些情况下的操作是对该列执行索引将不起作用。

例如：字段名LIKECONCAT('%','2014-08-13','%');

4、某些情况下，使用反向操作时不显示该列的索引去工作。

例如：字段名<>2;

参考资料：http://www.cx1314.cn/forum-64-1.html

5、WHERE中使用OR时如果某列没有索引，其他列的索引将不起作用。
****6.隐式转换会导致索引错误。
这也是开发中经常犯的错误。
****

由于表的t_number字段定义为varchar2(20)，但查询时该字段通过Where条件以数字类型传递给Oracle，导致索引失败。

例如：select*fromtestwheret_number=13333333333;//拼写错误；

select*fromtestwheret_number='13333333333';//拼写正确；7、使用notin、notexist等语句时。
****8.如果变量使用时间变量，表字段使用日期变量，或者相反的情况。
****9.如果B树索引isnull不无效，则使用isnotnull时无效。
位图索引isnull和isnotnull无效。
10、共享索引isnotnull只要索引列被创建（不分先后）就无效。
****

Innull必须与索引的第一列一起使用。
如果索引的第一个位置条件为isnull，则其他索引列可以为isnull（但如果所有列都满足isnull，则必须如此）。
or=avalue;

如果索引的第一个位置=avalue，则其他索引列可以是任何情况（包括isnull=avalue），并且索引在上述两种情况下无效情况下它不会失败。

作者：元子程序

Mysql索引会失效的几种情况分析

索引并不总是保证查询效率，并且在某些情况下可能会降低性能。
以下是导致索引失败的一些常见情况。

第一，如果你的查询条件包含“或”操作，即使你可以在其中一个条件中使用索引，但也可能无法在整个索引中使用它。
发挥作用。
为了确保在“or”查询中使用索引，建议为每个or条件列添加单独的索引。

那么，如果查询中包含复合索引，则只有将索引的第一列作为查询条件时才会使用该索引。
由于其他列的情况，索引失败。

此外，如果like查询以百分号（%）开头，MySQL通常不会使用索引进行搜索，即使有索引，因为%匹配会导致扫描整个表。

对于字符串类型列，如果查询条件中的数据没有加引号，MySQL可能会忽略索引，因此确保数据被正确引用很重要。

最后，如果MySQL确定扫描整个表比使用索引更快，它会覆盖索引选择前者。
索引使用情况可以通过查看`showstatuslike'Handler_read%'`中的两个指标`handler_read_key`和`handler_read_rnd_next`来评估。
“handler_read_key”的值越大，使用索引的查询就越多，这可能会导致“handler_read_rnd_next”的值越大，查询效率越低。

mysql索引失效的情况有哪些

MySQL中的索引失败意味着查询操作没有充分利用预期的索引，导致对整个表进行全扫描，这会显着降低查询性能。
以下是一些常见的索引失败场景：不公平使用：索引在相等查询上表现最佳，而非相等情况可能会导致索引失败。
OR运算符：当使用OR连接多个条件时，不能使用索引，因为MySQL通常为每个条件创建一个单独的索引。
LIKE语句的前缀搜索：使用LIKE进行匹配时，如果开始使用通配符%，MySQL可能会选择扫描全表而不是使用索引。
类型不匹配：如果查询和索引之间存在类型不匹配，则该索引不能在查询中使用。
函数或表达式：索引通常不支持函数或复杂表达式，如果它们包含在查询中，索引可能会失败。
全列匹配问题：如果查询中使用了表的所有列，则无法使用索引，因为MySQL需要全表扫描。
复合索引的选择性问题：复合索引的使用取决于查询的列顺序和选择性。
不正确的排序可能会导致索引失败。
隐式类型转换：在某些情况下，MySQL可能会进行隐式类型转换，导致查询不使用索引。
NULL值查询：NULL值在索引中专门处理。
当查询NULL时，索引可能无法优化查询过程。
错误的索引统计信息：索引统计信息用于做出最优决策。
如果信息不正确，可能会导致索引选择错误。
强制索引：在某些情况下，即使有更好的替代方案，SQL查询也可能会强制使用特定索引，从而导致索引无效。
查询优化器决策：MySQL的查询优化器负责选择最有效的查询计划，其决策会影响索引的使用。

索引失效的情况和解释

索引并不是一直生效的，比如以下几种情况会导致索引失败：1.如果条件中有or，即使有带索引的条件也不会被使用（这就是为什么你应该尽量少用or）注意：要使用or，如果想让索引生效，只需在条件or中的每一列添加一个索引即可2.对于多列索引，如果不使用第一部分，不使用索引以%开头的查询4.如果列是String类型，则必须在使用引号来引用大小写中的数据，否则不会使用索引5.如果MySQL估计使用全表扫描比使用索引更快，那么除了索引showstatuslike'Handler_read%'之外，不会使用索引；可以关注：Handler_read_key：值越大，使用索引的查询越无效。
------------------1.如果WHERE子句的查询用例中存在不等号（WHEREcolumn！=...），则MYSQL将无法使用索引2。
同样，如果WHERE子句的查询用例中使用了函数（例如:WHEREDAY(column)=..)，那么MYSQL只能使用索引3.在JOIN操作中(需要从多个数据表中提取数据时)，MYSQL只能使用索引的时候主键和外键的数据类型相同。
否则，即使创建了索引，也不会被使用4、如果在WHERE子句的查询情况下使用LIKE和REGEXP比较运算符，MYSQL只能在搜索模板的第一个字符不是a的情况下才能使用索引通配符。
例如查询条件为LIKE'%abc'，MYSQL就会使用索引；如果查询条件是LIKE'%abc'，MYSQL将不会使用索引。
5.在ORDERBY操作中，只有当排序条件不是查询条件的表达式时，MYSQL才使用索引。
然而，在涉及多个数据表的查询中，即使有索引，这些索引对加速ORDERBY操作的作用也很小。
6、如果一个数据列包含很多重复值，即使为其创建索引，也不会产生很好的效果。
例如，如果某个数据列包含“0/1”或“Y/N”等值，则无需为其创建索引。