MySQL索引失效原因详解与优化技巧

日期：2024-12-20 12:30:08 作者：创始人浏览：0

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

1.如果对列使用函数，则该列的索引将不起作用。
****

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

2、对列进行操作（+、-、*、/、!等），该列的索引将不起作用。

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

select*fromtestwhereid=10;//正确的写法；

3.LIKEinsome情况下操作，该列上的索引将不起作用。

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

4、某些情况下，如果使用反向操作，则该列的索引不会工作。

例如：字段名<>2;

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

5、WHERE中使用OR时，如果某一列没有索引，那么其他列的索引就不起作用。
****6.隐式转换会导致索引失败。
应该认真对待这一点。
这也是开发中经常犯的错误。
****

因为表的字段t_number定义为varchar2(20)，但是在查询时，该字段是通过where条件以number类型传递给Oracle的，这样会导致索引失败。

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

select*fromtestwheret_number='13333333333';//正确的写法；7、使用notin、notexist等语句时。
****8.当变量使用times变量，表字段使用date变量时。
或者相反的情况。
****9.当B树索引isnull不失效时，使用isnotnull时也会失效。
位图索引isnull和isnotnull将无效。
10、联合索引isnotnull只要索引列被创建（排名不分先后）就会失效。
****

Innull必须与索引的第一列一起使用。
当索引的第一个位置条件为isnull时，其他索引列可以为isnull（但必须是所有列都满足isnull时。
），或者=一个值；

当索引的第一个位置为isnull时=avalue，其他索引列可以是任何条件（包括isnull=avalue），以上两种情况索引都会无效。
其他情况下也不会失败。

作者：程序园子

MySQLNULL值处理详细说明

在讨论null之前，我们先看一下下面的示例表数据：

然后我们要查找名称不是“chen”的所有其他行记录。
我们可以这样写：

但是结果似乎并没有达到我们的预期。
未找到ID为1的记录。
为什么找不到他？

NULL值对于初学者来说很容易混淆。
人们常常认为null和空字符串是相同的，但实际上两者代表的是“缺失的未知值”，而字符串''是一个明显不同的特定值。

例如：

第一条插入指令表示手机号码未知，缺少未知电话号码。
第二条语句表示手机号码是安全的，他的手机号码是空字符串

要进行与null相关的比较，可以使用isnull,isnotnull,<=>,isinMySQLnull(）用于测试和比较。
您不能使用=、<、<>来比较和测试空值。
一个值与零比较，一个函数运算的结果为空

下面的三个例子可以说明问题：

但是，凡事都有一个例外：当使用“distinct”时"、"groupby"和"orderby"不同的空值被认为是相同的进行计算。

所以如果我们回顾一下原来的例子：如果我们的Where条件是name!='chen'，它将与id=1的行进行比较，所以测试：'chen'!='chen'测试结果是0，这是错误的，所以没有找到行ID=1。
接下来再测试一下：'zhang'!='chen'测试结果为1，为true，因此可以找到行ID=2。
接下来再次测试：NULL!='chen'测试结果为NULL，这是错误的，所以没有找到rowID=3。
所以最终的结果是只找到了id=2行的数据。
那么如果我们想要实现我们的目标呢？

推荐第一种方法。
由于MySQL具有搜索空列的查询优化：

而向列添加函数的第二种类型的查询不起作用。

其他说明

MySQL优化查询6：高级函数exists;rollup;cube;field等|数据分析笔记

高级MySQL查询技巧：persist、rollup、block、field等《数据分析手册》讨论了MySQL中的几种高级查询功能，包括存在、不存在、rollup、块、字段以及相关的聚合操作。
首先，exists和notexists用于替换返回布尔值的exist子查询，有效利用索引，提高查询效率，而notexists则过滤掉不存在的记录条件。
使用exists时，一定要注意Where子句和From子句之间的表连接。
Rollup和Cube用于小组活动。
Rollup可以一次性聚合不同组的汇总数据，而Cube会循环遍历所有可能的组组合。
group_concat用于合并特定字段的值，例如显示每个学生的所有课程ID。
字段函数提供了排序时自定义排序的灵活性，例如优先级排序。
COALESCE、IF、IFNULL、ISNULL和NULLIF函数为处理空值和条件判断提供了高效的方法。
最后，本文涵盖了许多其他相关主题，例如聚合函数的使用，以及解决特定需求时的提示和注意事项。
如果你对这些内容感兴趣，可以访问我的github：Zhouxiaonnan/machine-learning-notesandcode或者关注我的学习MySQL系列文章：从基础到优化，尽在一个地方。
同时，我还有一系列关于分析职业改变数据和实施机器学习模型的教程。
欢迎加入我们的学习社区，共同进步。
写于成都，2020年10月7日

标签： MySQL索引失效 NULL值处理