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

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

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

其次，如果查询涉及到复合索引，只有当索引的第一列作为查询条件时才会使用该索引。
其他列上的条件将导致索引失败。

另外，如果一个查询喜欢以百分号（%）开头，即使有索引，MySQL通常也不会用它来进行搜索，因为%匹配会导致全表扫描。

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

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

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

1.在列上使用函数时，列索引不起作用。
****

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

2.对列进行操作(+,-,​​,/,!等)。
列索引不起作用。

示例：select*fromtestwhereid-1=9;//格式不正确；

select*fromtestwhereid=10;//正确格式；

3.LIKEthis以多种方式对此建立索引列将不起作用。

示例：字段名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无效，isnotnull无效。
10.创建索引列时，联接索引isnotnull被禁用（乱序）。
****

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

如果索引的第一个位置是=avalue，那么其他索引列可以是任意条件（包括isnull=avalue），并且该索引有两个条件使其无效。
在其他情况下它不会失败。

作者：程序本子

MySQL索引失效的场景

要验证MySQL索引失败场景，请使用所描述的执行计划来分析特定案例。
在SQL前添加explain关键字，注意KEY列显示实际使用的索引，NULL表示未使用。
请注意包含关键字段的列类型：ALL、INDEX、BTREE、HASH、NESTED_LOOP、JOIN等。
创建表并测试数据，包括主键、学号、姓名、年龄、性别、家庭住址等字段。
表中有3个索引，包括职称、学号、姓名、年龄的组合索引。
索引失败条件1：没有最左匹配。
从最左边的字段开始的查询可以使用共享索引，否则不能。
最左匹配原则适用于sn、name、age等字段，但sn+name组合无效。
索引失败案例2：模糊查询错误。
使用like时，只有第一种方法可以使用索引。
模糊查询需要精确匹配，例如通配符，但不遵循索引列的顺序。
索引失败案例3：列操作。
如果索引列使用加、减、乘、除等运算，索引将无效。
索引失败案例4：使用函数。
使用ifnull等MySQL函数查询列将导致索引失败。
索引失败条件5：转换类型。
如果索引列上有类型转换，则不会使用该索引。
例如，当查询string类型的地址和int类型的值时，索引将无效。
索引失败案例6：使用isnotnull。
isnotnull会导致索引失败，而isnull则正常。
总结了常见的索引失败场景，包括非最左匹配、二义性查询错误、列操作、函数使用、类型转换和isnotnull。