MYSQL联合索引原理之最左匹配原则

2021-11-13 01:56:55

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

示例：

CREATE TABLE `student` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  `Gid` int(11) unsigned DEFAULT NULL COMMENT '年级id',
  `Cid` int(11) unsigned DEFAULT NULL COMMENT '班级id',
  `SId` int(11) unsigned DEFAULT NULL COMMENT '学号',
  `Name` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

对列Gid、列Cid和列Sid建一个联合索引：

create unique index uni_Gid_Cid_SId on student(Gid,Cid,SId);

联合索引 uni_Gid_Cid_SId 实际建立了(Gid)、(Gid,Cid)、(Gid,SId)、(Gid,Cid,SId)四个索引。

插入模拟数据：

INSERT INTO `student` (`Gid`, `Cid`, `SId`, `Name`) VALUES (floor(rand() * rand() *rand() * 1000000000) , floor(rand() *  rand() *rand() * 1000000000) , floor(rand() * rand() * rand() *1000000000) , rand());

查询示例：

SELECT * FROM student WHERE Gid=16236196 AND Cid=8143382 AND Name='0.76727119';

EXPLAIN SELECT * FROM student WHERE Gid=16236196 AND Cid=8143382 AND Name='0.76727119';

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(Gid,Cid)进行数据匹配。

索引的字段可以是任意顺序的，如：

SELECT * FROM student WHERE Gid=16236196 AND Cid=8143382;
SELECT * FROM student WHERE Cid=8143382 AND Gid=16236196 ;

联合索引 uni_Gid_Cid_SId 还支持的查询条件有：

select * from table where Gid = 1;

select * from table where Gid = 1 and  Cid =1;

select * from table where Gid = 1 and  Cid=1 and SId= 1;

select * from table where Gid= 1 and SId= 1;

关于最后一个的结构 Gid和SId 也会走索引 uni_Gid_Cid_SId 的原理是：

　　b+树的数据项是复合的数据结构，比如(Gid,Cid,SId)的时候，b+数是按照从左到右的顺序来建立搜索树的。

　　比如当(111,222,333)这样的数据来检索的时候，b+树会优先比较 Gid 来确定下一步的所搜方向，如果 Gid 相同再依次比较 Cid 和 SId，最后得到检索的数据；

　　但当(222,333)这样的没有 Gid 的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候 Gid 就是第一个比较因子，必须要先根据 Gid 来搜索才能知道下一步去哪里查询。

　　比如当(111,333)这样的数据来检索时，b+树可以用 Gid 来指定搜索方向，但下一个字段 Cid 的缺失，所以只能把 Gid 等于 111 的数据都找到，然后再匹配 SId 是 333 的数据了，这个是非常重要的性质，即索引的最左匹配特性。

为什么要使用联合索引：

减少开销。

　　建一个联合索引(Gid,Cid,SId)，实际相当于建了(Gid)、(Gid,Cid)、(Gid,SId)、(Gid,Cid,SId)四个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。

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

效率高。

　　索引列越多，通过索引筛选出的数据越少。

　　有1000W条数据的表，有如下SQL：SELECT * FROM TABLE WHERE Gid=1 AND Cid=2 AND SId=3，假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合Gid=2 and Cid= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

缺点。

　　联合索引越多，索引列越多，则创建的索引越多，索引都是存储在磁盘里的，通过索引算法 (Btree代表索引算法使用二叉树的形式来做索引的) 来查找数据，的确可以极大的提高查询效率，但是与此同时增删改的同时，需要更新索引，同样是需要花时间的，并且索引所占的磁盘空间也不小。

建议。

　　单表尽可能不要超过一个联合索引，单个联合索引不超过3个字段。

码农公寓

相关文章