不会吧，不会吧？MySQL 索引最佳实践你不看看

作者爱说话

忙忙碌碌的一周又过去了，这周最大的乐趣就是买了个小音箱，又可以下班的时候开始愉快的开始练琴了，程序员嘛，还是得培养点艺术细菌。哈哈

这周本来没想好写什么东西，刚好周五公司进行了内部技术分享，内容为《MySQL索引讲解 + 最佳实践》，摘出了几条重要，和较常见的和大家一起分享，当然还有很多没有涉及到，关于索引的知识点，就不具体展开讲解，本来想转载一篇文章与大家分享，但是卑微的我还没加上原作者微信，有兴趣的话可以公众号内回复“唯一索引和普通索引”。我将原文地址推荐给你。

现在你可以洗洗手，打开你的 Naviact 和王经理一起动手实践，知识吸收 + 100%

温馨提示：文末附建表语句。

索引前戏

王经理 ?：天苍苍，野茫茫，索引查询帮大忙。

小林小声 bb：经理好像个憨批。

王经理：林步动，你在说什么？

王经理的砖头

小林：没没没，我说经理，好才气！这个诗写的，啧啧啧，真是 ? 牛掰 glass

王经理：算你小子，今天有眼力见。那我考考你，你能概括下索引的优点和缺点嘛？

小林：（淦，又考我） 经理，《高性能 MySQL》一书中总结的 MySQL 的优点是

• 减少查询需要扫描的数据量(加快了查询速度)
• 减少服务器的排序操作和创建临时表的操作(加快了groupby和orderby等操作)
• 将服务器的随机IO变为顺序IO(加快查询速度)

至于缺点嘛，就是索引也是数据，需要存储，因此会带来额外的存储空间占用。其次，在插入，更新和删除操作的同时，需要维护索引，因此会带来额外的时间开销

小美：步动，你今天好帅啊

小林：????

王经理：今天，步动说的很对阿，我再来总结下索引的缺点

• 索引占用磁盘或者内存空间
• 减慢了插入更新操作的速度

但是实际上,在一定数据范围内(索引没有超级多的情况下)，建立索引带来的开销是远远小于它带来的好处的，但是我们仍然要防止索引的滥用。

王经理：林步动，你骄傲啥，快给我从桌子上下来！我们今天的重点不在这里，重点是讲解一下 MySQL 的索引具体在生产中的最佳实践方法。拿起小本本，打开 Naviact，动起来。

最佳实践

前置知识 ：EXPLAIN

---

1，最左前缀法则

EXPLAIN SELECT * FROM `employee_information` WHERE age = 23 AND position = "dev"

WHERE 条件列的，从索引的最左前列开始，且不跳过索引中的列，一旦未以最从左前列开始，索引就失效。意思就是 是兄弟，就来砍我啊，不对。应该是 带头大哥不能死，中间兄弟不能断

由最左前缀原则引申出的索引冗余问题： 根据最左索引原则，我们不难得出，index(a,b)相当于创建了二个索引index(a)、index(a,b)。也可以类推，index(a,b,c)相当于创建了三个索引index(a)、index(a,b)、index(a,b,c)。

那么，不妨多问自己一个为什么？? 索引冗余会带来什么问题？一张表设置几个索引以内最佳？ （可以参考阿里 Java 开发手册，或《高性能的 MySQL》一书）

---

2，不在索引列上做任何操作（计算、函数、（自动 or 手动）类型转换），会导致索引失效而转向全表扫描

`EXPLAIN SELECT * FROM employee_information` WHERE LEFT(name,3) = 'bud'
``

拿该条 SQL 举例说明，我需要一条条取出 name，进行函数操作，然后逐条与限定的条件（bud）进行比较，所以没办法用索引，只能进行全表扫描。

那么，会不会有这么一种情况。在你不经意的时候， MySQL 偷偷的在你的索引列上做了操作？导致我们的索引失效了。

就比如，隐式转换？

---

3，存储引擎不能使用索引中范围条件右边的列（若中间索引列用到了范围（>、<、like等），则后面的所以全失效。 ）

`EXPLAIN SELECT * FROM employee_information` WHERE name = 'budongli' AND age > 23 AND position = "dev"
``

可以看到 type = range，实际上 age 用到了索引（可以观察 key_len），只是说这个索引表达的是范围，导致 position 用不上索引

---

4，尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 SELECT * 语句

EXPLAIN SELECT name FROM `employee_information` WHERE name = 'budongli'

我们在相应的 SELECT 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态。

需要注意的是，当 Using where 和 Using index 同时出现的时候，此时 Using where 只是用来从索引中查找数据，此数据如果不是用来过滤，那么就是用来读取，以避免回表读取数据行。

如果我们只爱使用 SELECT * ，那么完蛋。

Extra 会告诉你，查询的列未被索引覆盖，进而通过 where 条件过滤获取所需数据，

---

5，MySQL 在使用不等于（！= 或者 <>）的时候无法使用索引导致全表扫描，is null 或者 is null not 也不会走索引

EXPLAIN SELECT * FROM `employee_information` WHERE name != 'budongli'

这条和 Shit 一样的 SQL ?，在我是实习生的时候也写过。

尽可能把类似这种判断落在业务层，数据库很累，不给他加压力。保护数据库，从你我做起。

---

6，like 以通配符开头（‘$abc’） MySQL 索引失效会变成全表扫描操作

就像查字典一样，比如要查拼音首字母是ch的字，按顺序翻完以ch开头的页就能有结果，在这个 SQL 语句中我们想去查 名字开头是“budon”，利用索引顺序查就ok了，很快就能查出来了。

EXPLAIN SELECT * FROM `employee_information` WHERE name like 'budon%'

但要是假设你想在字典里查拼音包含an的字，那就只能一页一页翻完整个字典才能知道哪些是需要的，此时就相当于无法走索引，只能走全表扫描。

EXPLAIN SELECT * FROM `employee_information` WHERE name like '%budon%'

---

7，少用 or 或者 in

`EXPLAIN SELECT * FROM employee_information` WHERE name = "budongli" or name = "xiaomei"
``

MySQL 的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划： 在使用 or 或者 in 的时候， MySQL 不一定会使用索引，MySQL 内部优化器会根据每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布等七七八八，九九十十等多个因素去整体评估是否使用索引。有非常多的原因会导致 MySQL 选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）。

归根结底，MySQL 认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但 MySQL 值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

所以说，MySQL 内部优化器有时候也是个笨蛋，很有可能选错。当然如果你觉得走一个固定的索引，这个 SQL 语句会快的飞起，请别客气使用 FORCE INDEX 与优化器进行 Battle

问题时间到了

如果一张表的某个字段即可以建唯一索引，又可以建普通索引，假设你是公司的 DBA，从性能角度考虑来讲，你会如何选择？选择的依据是什么？

（建议小伙伴可以主动去思考思考，动手实践实践，当然公众号内也给你准备了较为详细的分享，你可以在公众号内回复 “唯一索引和普通索引” ，我将与你分享）

建表语句

-- ----------------------------  
-- Table structure for employee_information  
-- ----------------------------  
DROP TABLE IF EXISTS `employee_information`;  
CREATE TABLE `employee_information`  (  
  `id` int(11) NOT NULL,  
  `name` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  
  `age` int(11) NOT NULL,  
  `position` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  PRIMARY KEY (`id`) USING BTREE,  
  INDEX `hybrid_index`(`name`, `age`, `position`) USING BTREE,  
  INDEX `age`(`age`) USING BTREE  
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;  
  
-- ----------------------------  
-- Records of employee_information  
-- ----------------------------  
INSERT INTO `employee_information` VALUES (1, 'budongli', 23, 'dev', '2020-06-12 22:21:24');  
INSERT INTO `employee_information` VALUES (2, 'xiaomei', 22, 'dev', '2020-05-06 22:22:10');  
INSERT INTO `employee_information` VALUES (3, 'jingliwang', 30, 'manager', '2018-06-01 22:22:37');  
  
SET FOREIGN_KEY_CHECKS = 1;