# MySql

导致MySQL索引失效的几种常见写法

2021-07-29 11:19:00
45
```
  • 原文作者: 佚名
  • 原文链接: https://database.51cto.com/art/202012/634063.htm
最近一直忙着处理原来老项目遗留的一些SQL优化问题,由于当初表的设计以及字段设计的问题,随着业务的增长,出现了大量的慢SQL,导致MySQL的CPU资源飙升,基于此,给大家简单分享下这些比较使用的易于学习和使用的经验。

这次的话简单说下如何防止你的索引失效。

再说之前我先根据我最近的经验说下我对索引的看法,我觉得并不是所以的表都需要去建立索引,对于一些业务数据,可能量比较大了,查询数据已经有了一点压力,那么最简单、快速的办法就是建立合适的索引,但是有些业务可能表里就没多少数据,或者表的使用频率非常不高的情况下是没必要必须要去做索引的。就像我们有些表,2年了可能就10来条数据,有索引和没索引性能方面差不多多少。

索引只是我们优化业务的一种方式,千万为了为了建索引而去建索引。

下面是我此次测试使用的一张表结构以及一些测试数据

CREATE TABLE user ( id INT ( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT, create_time datetime NOT NULL, name VARCHAR ( 5 ) NOT NULL, age TINYINT ( 2 ) UNSIGNED ZEROFILL NOT NULL, sex CHAR ( 1 ) NOT NULL, mobile CHAR ( 12 ) NOT NULL DEFAULT '', address CHAR ( 120 ) DEFAULT NULL, height VARCHAR ( 10 ) DEFAULT NULL, PRIMARY KEY ( id ), KEY idx_createtime ( create_time ) USING BTREE, KEY idx_name_age_sex ( name, sex, age ) USING BTREE, KEY idx_ height ( height ) USING BTREE, KEY idx_address ( address ) USING BTREE, KEY idx_age ( age ) USING BTREE ) ENGINE = INNODB AUTO_INCREMENT = 261 DEFAULT CHARSET = utf8;

INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 1, '2019-09-02 10:17:47', '冰峰', 22, '男', '1', '陕西省咸阳市彬县', '175' ); INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 2, '2020-09-02 10:17:47', '松子', 13, '女', '1', NULL, '180' ); INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 3, '2020-09-02 10:17:48', '蚕豆', 20, '女', '1', NULL, '180' ); INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 4, '2020-09-02 10:17:47', '冰峰', 20, '男', '17765010977', '陕西省西安市', '155' ); INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 255, '2020-09-02 10:17:47', '竹笋', 22, '男', '我测试下可以储存几个中文', NULL, '180' ); INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 256, '2020-09-03 10:17:47', '冰峰', 21, '女', '', NULL, '167' ); INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 257, '2020-09-02 10:17:47', '小红', 20, '', '', NULL, '180' ); INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 258, '2020-09-02 10:17:47', '小鹏', 20, '', '', NULL, '188' ); INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 259, '2020-09-02 10:17:47', '张三', 20, '', '', NULL, '180' ); INSERT INTO bingfeng.user ( id, create_time, name, age, sex, mobile, address, height ) VALUES ( 260, '2020-09-02 10:17:47', '李四', 22, '', '', NULL, '165' );


**单个索引**

**1、使用!= 或者 <> 导致索引失效**

SELECT * FROM user WHERE name != '冰峰';


我们给name字段建立了索引,但是如果!= 或者 <> 这种都会导致索引失效,进行全表扫描,所以如果数据量大的话,谨慎使用

[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/1.png)](https://s2.51cto.com/oss/202012/08/8e6ca871444fb7af338d9273d89e17d8.png)

可以通过分析SQL看到,type类型是ALL,扫描了10行数据,进行了全表扫描。<>也是同样的结果。

**2、类型不一致导致的索引失效**

在说这个之前,一定要说一下设计表字段的时候,千万、一定、必须要保持字段类型的一致性,啥意思?比如user表的id是int自增,到了用户的账户表user_id这个字段,一定、必须也是int类型,千万不要写成varchar、char什么的骚操作。

SELECT * FROM user WHERE height= 175;


这个SQL诸位一定要看清楚,height表字段类型是varchar,但是我查询的时候使用了数字类型,因为这个中间存在一个隐式的类型转换,所以就会导致索引失效,进行全表扫描。

[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/2.png)](https://s3.51cto.com/oss/202012/08/79b661f0ac19d82e7979c690b34bfbf4.png)

现在明白我为啥说设计字段的时候一定要保持类型的一致性了不,如果你不保证一致性,一个int一个varchar,在进行多表联合查询(eg: 1 = '1')必然走不了索引。

遇到这样的表,里面有几千万数据,改又不能改,那种痛可能你们暂时还体会。

少年们,切记,切记。

**3、函数导致的索引失效**

SELECT * FROM user WHERE DATE(create_time) = '2020-09-03';


如果你的索引字段使用了索引,对不起,他是真的不走索引的。

[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/3.png)](https://oss.120120.top/typecho/2021/07/mysql/0729/3.png)

**4、运算符导致的索引失效**

SELECT * FROM user WHERE age - 1 = 20;


如果你对列进行了(+,-,*,/,!), 那么都将不会走索引。

[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/4.png)](https://oss.120120.top/typecho/2021/07/mysql/0729/4.png)

**5、OR引起的索引失效**

SELECT * FROM user WHERE name = '张三' OR height = '175';


OR导致索引是在特定情况下的,并不是所有的OR都是使索引失效,如果OR连接的是同一个字段,那么索引不会失效,反之索引失效。

[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/5.png)](https://oss.120120.top/typecho/2021/07/mysql/0729/5.png)

**6、模糊搜索导致的索引失效**

SELECT * FROM user WHERE name LIKE '%冰';


这个我相信大家都明白,模糊搜索如果你前缀也进行模糊搜索,那么不会走索引。

[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/6.png)](https://oss.120120.top/typecho/2021/07/mysql/0729/6.png)

**7、NOT IN、NOT EXISTS导致索引失效**

SELECT s.* FROM user s WHERE NOT EXISTS (SELECT * FROM user u WHERE u.name = s.name AND u.name = '冰峰')
SELECT * FROM user WHERE name NOT IN ('冰峰');


这两种用法,也将使索引失效。但是NOT IN 还是走索引的,千万不要误解为 IN 全部是不走索引的。我之前就有误解(丢人了...)。

[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/7.png)](https://oss.120120.top/typecho/2021/07/mysql/0729/7.png)

**8、IS NULL不走索引,IS NOT NULL走索引**

SELECT * FROM user WHERE address IS NULL


[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/8.png)](https://oss.120120.top/typecho/2021/07/mysql/0729/8.png)

不走索引。

SELECT * FROM user WHERE address IS NOT NULL;


[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/9.png)](https://oss.120120.top/typecho/2021/07/mysql/0729/9.png)

走索引。

根据这个情况,建议大家这设计字段的时候,如果没有必要的要求必须为NULL,那么最好给个默认值空字符串,这可以解决很多后续的麻烦(有深刻的体验<体验=教训>)。

**符合索引**

**1、最左匹配原则**

EXPLAIN SELECT * FROM user WHERE sex = '男';
EXPLAIN SELECT * FROM user WHERE name = '冰峰' AND sex = '男';


**测试之前,删除其他的单列索引。**

啥叫最左匹配原则,就是对于符合索引来说,它的一个索引的顺序是从左往右依次进行比较的,像第二个查询语句,name走索引,接下来回去找age,结果条件中没有age那么后面的sex也将不走索引。

[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/10.png)](https://oss.120120.top/typecho/2021/07/mysql/0729/10.png)

注意:

SELECT * FROM user WHERE sex = '男' AND age = 22 AND name = '冰峰';


可能有些搬砖工可能跟我最开始有个误解,我们的索引顺序明明是name、sex、age,你现在的查询顺序是sex、age、name,这肯定不走索引啊,你要是自己没测试过,也有这种不成熟的想法,那跟我一样还是太年轻了,它其实跟顺序是没有任何关系的,因为mysql的底层会帮我们做一个优化,它会把你的SQL优化为它认为一个效率最高的样子进行执行。所以千万不要有这种误解。

**2、如果使用了!=会导致后面的索引全部失效**

SELECT * FROM user WHERE sex = '男' AND name != '冰峰' AND age = 22;


我们在name字段使用了 != ,由于name字段是最左边的一个字段,根据最左匹配原则,如果name不走索引,后面的字段也将不走索引。

[![img](https://oss.120120.top/typecho/2021/07/mysql/0729/11.png)](https://oss.120120.top/typecho/2021/07/mysql/0729/11.png)

关于符合索引导致索引失效的情况能说的目前就这两种,其实我觉得对于符合索引来说,重要的是如何建立高效的索引,千万不能说我用到那个字段我就去建立一个单独的索引,不是就可以全局用了嘛。这样是可以,但是这样并没有符合索引高效,所以为了成为高级的搬砖工,我们还是要继续学习,如何创建高效的索引。
最后编辑于 2024-10-31 14:05:38