4.运算符导致索引失效针对不走索引列的情况分析

背景剖析

明天领导在查询报表时,发觉非常慢,于是引起一系列关于sql优化的工作,最终发觉是剖析朋友在进行多表关联时进行不等值关联导致全表扫描,且使用外键无索引导致1W条数据表和20W数据表关联时执行平缓。并且在MySQL中,并不是你完善了索引,但是你在SQL中使用到了该列,MySQL就肯定会使用到这些索引的,有一些情况很可能在你不知不觉中,你就“成功的避免了”MySQL的所有索引。

mysql什么查询情况不走索引呢?

1.用!=或则造成索引失效

2.类型不一致引起的索引失效

3.函数引起的索引失效如:SELECT*FROMuserWHEREDATE(create_time)=‘2020-09-03’;假如使用函数在索引列,这是不走索引的。

4.运算符造成的索引失效SELECT*FROMuserWHEREage-1=20;假如你对列进行了(+,-,*,/,!),这么都将不会走索引。

5.OR造成的索引失效SELECT*FROMuserWHEREname=‘张三’ORheight=‘175’;OR造成索引是在特定情况下的,并不是所有的OR都是使索引失效,假如OR联接的是同一个数组或则联接的两个数组都有索引,这么索引不会失效,反之索引失效。

6.模糊搜索造成的索引失效SELECT*FROMuserWHEREnameLIKE‘%冰’;当%置于匹配数组前是不走索引的,置于前面才能走索引。

7.NOTIN、NOTEXISTS造成索引失效

针对不走索引的情况详尽剖析

1、索引列参与估算,不走索引

SELECT `username` FROM `t_user` WHERE age=20;-- 会使用索引
SELECT `username` FROM `t_user` WHERE age+10=30;-- 不会使用索引!!因为所有索引列参与了计算
SELECT `username` FROM `t_user` WHERE age=30-10;-- 会使用索引

2、索引列使用函数,可能不走索引

-- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT username FROM t_user WHERE concat(username,'1') = 'admin1'; 
-- 会使用索引
SELECT username FROM t_user WHERE username = concat('admin','1'); 

3、索引列使用like句子,可能不走索引

SELECT * FROM USER WHERE username LIKE 'mysql测试%'   --走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试'   --不走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试%'  --不走索引

4、数据类型隐式转换,字符串列与数字直接比较,不走索引

-- stock_code字符串类型带索引
SELECT * FROM `stock_data` WHERE stock_code = '600538'  --走索引
SELECT * FROM `stock_data` WHERE stock_code = 600538  --不走索引

5、尽量避开OR操作sql 查不到数据 返回0sql 查不到数据 返回0,只要有一个数组没有索引,改句子就不走索引,不走索引!

-- stock_code带索引,open不带索引
SELECT * FROM `stock_data` WHERE `stock_code` = '600538' OR `open` = 6.62  -- 不走索引
-- stock_code带索引,up_down_pre带索引
SELECT * FROM `stock_data` WHERE `stock_code` = '600538' OR `up_down_pre` = 5.1  -- 走索引

6、whereid!=2或则whereid2,不走索引!

SELECT * FROM t_user WHERE username  'mysql测试'

7、isnull,isnotnull也难以使用索引,不走索引!

SELECT * FROM t_user WHERE username IS NULL -- 不走索引
SELECT * FROM t_user WHERE username IS NOT NULL -- 不走索引

8、索引列使用in句子,可能不走索引

-- stock_code数据类型为varchar
SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538')  -- 走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538','688663','688280')  -- 走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN (大量数据)  -- 不走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN (600538)  -- 不走索引

对索引失效情况的一些建议,仅供你们有则改之无则加勉

1.没有查询条件,或则查询条件没有构建索引在业务数据库中,非常是数据量比较大的表。

建议:

1换成有索引的列作为查询条件

2或则将查询频繁的列构建索引

2.查询结果集是原表中的大部份数据,应当是25%以上

查询的结果集,超过了总量行数25%,优化器认为就没有必要走索引了。

建议:

1假如业务准许,可以使用limit控制。

2结合业务判定,有没有更好的方法。假如没有更好的改写方案

3尽量不要在mysql储存这个数据了。放在redis上面。

3.索引本身失效,统计数据不真实

索引有自我维护的能力,对于表内容变化比较频繁的情况下,有可能会出现索引失效。

建议:

备份表数据,删掉重建相关表

4.查询条件使用函数在索引列上,或则对索引列进行运算,运算包括(+,-,*,/,!等)

建议:降低在mysql中使用加减乘除等估算运算。

5.隐式转换造成索引失效.这一点应该导致注重.也是开发中常常会犯的错误.

索引建立的字段为varchar();
select * from stu where name =111';走索引
select * from stu where name = 111;不走索引

建议

与研制协商,句子查询符合规范。

6.,notin不走索引(辅助索引)

建议:尽量不要用以上方法进行查询,或则选择有索引列为筛选条件。单独的>,

© 版权声明
THE END
喜欢就支持一下吧
点赞106 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片