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

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

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

5、OR引起的索引失效

`SELECT * FROM user WHERE name` = '张三' OR height = '175';
复制代码``

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

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

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

`SELECT * FROM user WHERE name` LIKE '%冰';
复制代码``

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

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

注意：

`SELECT * FROM user WHERE sex = '男' AND age = 22 AND name` = '冰峰';
复制代码``

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