联合索引-最左匹配原则

MySQL存储优化器

老郑头zz

28227人浏览 · 2022-03-28 15:15:22

老郑头zz · 2022-03-28 15:15:22 发布

前言：
面试官：建过索引嘛？
我有一个朋友：见过呀！
面试官：那你知道MySQL的执行计划嘛？
我有一个朋友：啊？！！
面试官：那你肯定知道最左匹配原则吧。
我有一个朋友：额。。。
想必大家面试时都被问到过这样问题，其实mysql的知识点不管是面试作为考点还是工作中使用，都占有很重要的一部分。这次简单介绍一下MySQL的执行优化器，主要说一说联合索引的最左匹配原则。

一、MySQL执行计划（执行优化器）
执行优化器，顾名思义，优化语句的，准确来说是优化查询语句。其实就是在我们写的select语句前加一个Explain关键字。

1.创建student表格，并添加信息。

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gid` int(11) NOT NULL,
  `cid` int(11) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_Gid_Cid_SId` (`gid`,`cid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在这里插入图片描述
2.假设gid,cid,uid,分别对应班级id，课程id，学号id。
1）先不创建索引，使用explain查询。

explain select * from example where name="张三";`

在这里插入图片描述
2）创建gid,cid,uid三列的联合索引后再使用explain查询

 explain select name,gid,cid,uid from example where gid=10010;

在这里插入图片描述
两条语句的查询结果显然不同，第一条语句因为没有创建name字段索引，所以是全表扫描，第二条语句创建了索引，就走了索引列，查询效率更高。

3.explain优化器返回的字段大概有12个字段。下面是各个字段的含义。

在这里插入图片描述

4.type表示对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL — MySQL将遍历全表以找到匹配的行（全表扫描）
index —index与ALL区别为index类型只遍历索引树
range —只检索给定范围的行，使用一个索引来选择行
ref —表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref —类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const —当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
system —system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL —MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

二、最左匹配原则：
最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
1）全列匹配时：
在这里插入图片描述

结论：修改查询列顺序，发现结果一样。是以为MySQL会通过优化器，自动优化索引顺序。

2）连续，匹配最左列时：
在这里插入图片描述
结论：都走了索引。

注：当索引列类型为字符串，where查询条件没有加引号，索引失效。
在这里插入图片描述

3）连续，没有匹配最左列时：

结论：都是全表扫描，没有走索引。
4）不连续匹配时：

在这里插入图片描述

结论：发现走索引列，但是只是走的gid的单列索引，可以通过key_len来确定走的索引列长度。
5）使用like模糊查询，匹配时：
前缀：

在这里插入图片描述
后缀：

在这里插入图片描述
中缀：

在这里插入图片描述
结论：如果gid是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了（修改gid类型为varchar）

6）精确匹配最左列，范围匹配其他列：

在这里插入图片描述
结论：走索引，走的是gid列索引。
7）范围匹配最左列：

在这里插入图片描述
结论：索引生效。
8）范围匹配最左列，范围匹配其他列：

结论：索引生效，只匹配gid列索引。

三、总结：
1）为什么最左匹配：
是因为mysql创建联合索引时，首先会对最左边字段排序，也就是第一个字段，然后再在保证第一个字段有序的情况下，再排序第二个字段，以此类推。

所以联合索引最左列是绝对有序的，其他字段无序。

举个例子：可以把联合索引看成“电话簿”，姓名作为联合索引，姓是第一列，名是第二列，当查找人名时，是先确定这个人姓再根据名确定人。只有名没有姓就查不到。

2）建多个单列索引：
多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！但多个单列索引底层会建立多个 B+索引树，比较占用磁盘空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！

3）创建索引注意事项：

限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
避免在取值朝一个方向增长的字段（例如：日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面。由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。
对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
删除不再使用，或者很少被使用的索引。表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求；
不要在有大量相同取值的字段上，建立索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度；
对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少；
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

4）创建联合索引优点：
1.减少开销。建多个单列索引，每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

2.覆盖索引。对联合索引(Gid,Cid,UId)，如果有如下的sql: select Gid,Cid,UId from student where Gid=1 and Cid=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

3.效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where Gid=1 and Cid=2 and UId=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合Gid=2 and Cid= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升很大。