一篇文章了解MySQL联合索引

置顶 hanxiaozhang2018

已于 2024-01-17 21:27:34 修改

阅读量2.4k

点赞数 2

分类专栏： MySQL Java面试文章标签： mysql 数据库 sql 后端

于 2020-03-15 10:05:29 首次发布

本文链接：https://blog.csdn.net/huantai3334/article/details/104854797

版权

Java面试同时被 2 个专栏收录

22 篇文章 0 订阅

订阅专栏

MySQL

9 篇文章 0 订阅

订阅专栏

一、什么叫联合索引？

由两列或以上列组成的索引成为联合索引，联合索引又叫复合索引。

准备材料：

MySQL数据库版本：5.7.17

User表结构：

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别：0：男，1：女',
  `age` tinyint(4) DEFAULT NULL COMMENT '年龄',
  `mobile` varchar(20) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`),
) ENGINE=MEMORY AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

二、联合索引什么时候回失效？

估计大家都知道联合索引遵循最左匹配索引的原则，其实这句话只是说对一半，在某些环境下，确实遵循此原则。但是切勿囫囵吞枣，以偏概全。下面，我就说一下，我对这块的一些见解。

如果让联合索引遵循最左匹配索引的原则，首先保证当前表的联合索引使用的方法不是Hash索引，如果使用Hash索引方法，只能全键查找记录。所以，联合索引遵循最左匹配索引的原则，必须使用B-Tree索引方法。其次，如果联合索引使用B-Tree索引方法后，MySQL会使用优化器，对于你的SQL进行优化，你觉得不可能遵循最左匹配原则的where条件，可能已经优化成可以使用最左匹配原则使用联合索引的执行计划。

1.证明Hash索引方法，只能全键查找记录：

通过上面的相关概念，我们可以知道MySQL存储引擎中只有Memory引擎支持Hsah索引方法，所以我们要表修改成Memory引擎。

1.1修改存储引擎语句：

alter table `user` ENGINE = MEMORY;

1.2验证修改后表的存储引擎：

1.3添加Hash索引类型的联合索引：

ALTER TABLE `user`  ADD INDEX `jointIndex` (`name`, `sex`, `age`) USING HASH;

1.4查询联合索引：

1.5使用EXPLAIN命令验证：

使用全键查询：

测试一：

EXPLAIN SELECT * FROM `user`  WHERE sex=1 AND age=20 AND NAME='小张';

结果：

Explain命令：keys列显示使用了我们定义jointIndex索引，证明Hash索引全键查询可以使用索引。

不使用全键查询：

测试一：

EXPLAIN SELECT * FROM `user`  WHERE NAME='小张';

结果：

Explain命令：type类显示类型为ALL，代表全表扫描；keys列显示null，没有用到索引。

测试二：

EXPLAIN SELECT * FROM `user`  WHERE   NAME='小张' AND sex=1;

结果：

Explain命令：type类显示类型为ALL，代表全表扫描；keys列显示null，没有用到索引。

测试三：

EXPLAIN SELECT * FROM `user`  WHERE sex=1 AND age=20;

结果：

Explain命令：type类显示类型为ALL，代表全表扫描；keys列显示null，没有用到索引。

结论：

通过以上三个测试，可以证明使用Hash索引方法，不使用全键查询时，不管尊不遵循最左匹配原则都无法使用联合索引。

2.B-Tree索引方法，最左索引匹配原则生效与失效：

在测试B-Tree时，我们要把MySQL的存储引擎切换为InnoDB，把索引方法修改成B-Tree索引，操作方法与《1.证明Hash索引方法，只能全键查找记录》中的方法类似，这里就不错过多演示。

上面的相关概念中说到MySQL优化器，在我们执行SQL时，会对SQL进行优化，选择成本最低的执行路径去执行，我们这里就会用到这个知识点，我会通过一个全键查询来简单说明一下。然后我们再演示，最左索引匹配原则生效与失效。

2.1全键查询时MySQL优化器的优化：

我们通过一下三个测试语句测试全键查询，打乱where条件顺序，是否使用联合索引：

测试语句：

EXPLAIN SELECT * FROM `user`  WHERE sex=1 AND age=20 AND NAME='小张'; 
EXPLAIN SELECT * FROM `user`  WHERE age=20 AND sex=1  AND NAME='小张'; 
EXPLAIN SELECT * FROM `user`  WHERE age=20 AND NAME='小张' AND sex=1;

结果：

通过结果我们可以看出，乱序where条件，最后都使用类联合索引，这都是MySQL优化器的功劳

2.2 最左索引匹配原则生效与失效：

通过2.1，我们知道MySQL都会查询进行优化，在使用最左索引匹配原则的时候，也会优化。接下来我们会通过单where条件、两个where条件进行验证最左索引匹配原则生效与失效。

单where条件：

测试语句：

EXPLAIN SELECT * FROM `user`  WHERE NAME='小张';  // 1
EXPLAIN SELECT * FROM `user`  WHERE  sex=1;  // 2
EXPLAIN SELECT * FROM `user`  WHERE age=20;  // 3

结果：

通过结果发现，只有1使用联合索引。

两个where条件：

测试语句：

EXPLAIN SELECT * FROM `user`  WHERE  NAME='小张' AND sex=1;    // 1
EXPLAIN SELECT * FROM `user`  WHERE sex=1 AND  NAME='小张';    // 2
EXPLAIN SELECT * FROM `user`  WHERE  NAME='小张' AND age=20;   // 3
EXPLAIN SELECT * FROM `user`  WHERE sex=1 AND age=20;          // 4

结果：

通过1和2，可以看出MySQL优化器对SQL进行优化，两个都使用了最左匹配原则，通过3，我们可以看出它也使用了最左匹配原则，发现他只有一列使用了索引，通过绿框可以看出来。通过4，可以看出它没有使用索引。