共计 4833 个字符，预计需要花费 13 分钟才能阅读完成。
最近始终忙着解决原来老我的项目遗留的一些 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 不走索引，前面的字段也将不走索引。
对于合乎索引导致索引生效的状况能说的目前就这两种，其实我感觉对于合乎索引来说，重要的是如何建设高效的索引，千万不能说我用到那个字段我就去建设一个独自的索引，不是就能够全局用了嘛。这样是能够，然而这样并没有合乎索引高效，所以为了成为高级的搬砖工，咱们还是要持续学习，如何创立高效的索引。
作者：一个程序员的成长
链接：https://juejin.im/post/686927…
起源：掘金
著作权归作者所有。商业转载请分割作者取得受权，非商业转载请注明出处。
