如何避免使用mysql in_如何防止 MySQL 索引失效？

以下文章来源于一个程序员的成长 ，作者 bingfeng

作者 | bingfeng

来源 | 一个程序员的成长(ID:xiaozaibuluo)

头图 | CSDN 下载自视觉中国

最近一直忙着处理原来老项目遗留的一些 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 字段建立了索引，但是如果 != 或者 <> 这种都会导致索引失效，进行全表扫描，所以如果数据量大的话，谨慎使用

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

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

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

SELECT * FROM `user` WHERE height= 175;

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

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

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

少年们，切记，切记。

3、函数导致的索引失效

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

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

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

SELECT * FROM `user` WHERE age - 1 = 20;

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

5、OR 引起的索引失效

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

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

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

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

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

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 全部是不走索引的。我之前就有误解(丢人了 ...)。

8、IS NULL 不走索引，IS NOT NULL 走索引

SELECT * FROM `user` WHERE address IS NULL

不走索引。

SELECT * FROM `user` WHERE address IS NOT NULL;

走索引。

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

符合索引

1、最左匹配原则

EXPLAIN SELECT * FROM `user` WHERE sex = ' 男 ';

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

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

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

注意：

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 不走索引，后面的字段也将不走索引。

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

更多精彩推荐

Linus Torvalds 回应，Debian 项目曾讨论永久禁止他出席会议！

点分享

点点赞

点在看