前言

我们在一张 TABLE 上除了丛集索引只能有一个之外，会有多个辅助索引，当在建立辅助索引的时候也常常会包含多个栏位，超过一个以上栏位的索引就是複合索引。

複合索引

★ 前提: 不同版本优化器是有差异的，这个版本会帮你优化语法，另一个版本可能不能帮你优化语法。所以这里会提到一些观念，可能在新一点的 MySQL 版本会发现不是这么回事，所以有时间还是要自己实际测试看看。

直接举个例子来说明：
有5个栏位
PK: Id
辅助索引 : index_name(A,B,C)

-- 建立测试 TABLECREATE TABLE `test_index` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `A` varchar(20) DEFAULT NULL,  `B` varchar(20) DEFAULT NULL,  `C` varchar(20) DEFAULT NULL,  `D` varchar(20) DEFAULT NULL,  PRIMARY KEY (`Id`),  KEY `index_name` (`A`,`B`,`C`)) ENGINE=InnoDB-- 插入资料INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (1, 'a', 'b', 'c', 'd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (2, 'aa', 'bb', 'cc', 'dd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (3, 'aaa', 'bbb', 'ccc', 'ddd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (4, 'a', 'b', 'c', 'd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (5, 'aa', 'bb', 'cc', 'dd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (6, 'aaa', 'bbb', 'ccc', 'ddd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (7, 'a', 'b', 'c', 'd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (8, 'aa', 'bb', 'cc', 'dd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (9, 'aaa', 'bbb', 'ccc', 'ddd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (10, 'a', 'b', 'c', 'd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (11, 'aa', 'bb', 'cc', 'dd');INSERT INTO `test_index`(`Id`, `A`, `B`, `C`, `D`) VALUES (12, 'aaa', 'bbb', 'ccc', 'ddd');

接着我们看几个 SELECT 的语法有没有使用到辅助索引

◎查询1

EXPLAINSELECT *FROM test_indexWHERE A='a' 

图1
从图1可知

type: ref 表示有用到非唯一性索引key: index_name 表示用到我们建立的辅助索引rows: 4 表示扫描了 4行

◎查询2

EXPLAINSELECT *FROM test_indexWHERE  B='b'

图2
从图2可知

type: ALL 表示没有用到索引，全表扫描rows: 12 整张表的资料都扫了

◎查询3

EXPLAINSELECT *FROM test_indexWHERE  A='a' and B='b'

图3
从图3可知

type: ref 表示有用到非唯一性索引key: index_name 表示用到我们建立的辅助索引rows: 4 表示扫描了 4行

小结

针对以上3个查询做的小结

事实上当我们辅助索引建立的是 (A,B,C)，会用到索引的情况为
(A), (A,B), (A,C), (A,B,C)不会用到索引的情况为
(B), (C), (B,C)所以如果我们的 WHERE 条件会用到 (A) 栏位也会用到 (A,B) 栏位，表示我们只要建一个 KEY (A,B) 就好，假如此时还有另一个索引 KEY (A)，那么这个就是多建的，可以删掉这里我们还要了解另一件原理，也就是最左前缀原理

最左前缀(最左匹配)

MySQL 在建立複合索引的时候会符合最左前缀匹配，也就是说在建立此複合索引的 B+树时会依照左到右的顺序来建
举一个简单的例子

CREATE TABLE t (a INT,b INT,PRIMARY KEY (a),KEY idx_a_b (a,b))ENGINE=INNODB

里面假设有一些资料如下图4

图4 参考至书籍 MySQL 技术内幕 InnoDB 存储引擎

基于最左前缀，可以发现会先依照栏位 a 来排，所以此 B+树的顺序可以看到由 1 -> 3如果不看 a 只看 b，会发现 b的数字依序是 1,2,1,4,1,2 这样就没有顺序了所以如果 b 也要有用到索引，那么 a 栏位也要放进去才可以，也就是当 a 栏位相同时，b栏位就有顺序

为什么需要複合索引?

减少硬碟空间占用: 如同前面提到的，假如我们的複合索引(A,B)，搜寻条件有
where A = xxx 和
where A =xxx and B =xxx
那么不用建例两个索引 (A) 和 (A,B)。不要忘记，一个索引就是一个B+树可以使用覆盖索引: 假设有一个语法 SELECT A,B,C FROM table_name WHERE A = xxx and B = xxx ，此时我们有一个複合索引(A,B,C)，表示可以不用回到丛集索引去就可以取得资料。忘记什么是覆盖索引可以回到我的上一篇参考 MySQL 系列文 - 索引的相关知识 - 覆盖索引第二个栏位已排序: 有时候我们用了 A 栏位来当 WHERE 条件，并且 order by B ，例如 SELECT * FROM table_name WHERE A = xxx ORDER BY B 此时选择了複合索引，B 栏位本身就是排序好的，不用另外排序了。

小结

本篇介绍了什么是複合索引，及为什么会需要複合索引，另外也补充了最左前缀原理。这些都关係到当我们要为一张表建立索引时要去思考的部分，所以当我们在建立索引的时候要考虑搜寻的条件、使用的频率，还有不要重複建立多余的索引，这些都是要考虑进去的。希望以上的观念有帮助到大家在建立索引时可能会遇到的问题。

资料库知识相当广泛，文中若有不正确的地方，也烦请各位大神不吝指教，谢谢