7.联合索引（最左前缀原则）

1）什么是联合索引？

联合索引指的是对一张表上的多个列进行索引。也就是说，表上多个列加起来组成一个索引，供快速查询使用。

2）如何使用联合索引？

首先，给表添加索引
创建表时给表添加：

CREATE TABLE t(
    a int,
    b int,
    primary key(a),
    key idx_a_b(a,b)
)
1
2
3
4
5
6

创建表后给表添加：

CREATE TABLE t(
    a int,
    b int,
    primary key(a)
)
1
2
3
4
5

给表添加索引语句：

ALTER TABLE t ADD INDEX indx_a_b(a,b);
1

然后进行使用该联合索引进行查询，在此之前，我们需要学习联合索引的最左前缀原则。
最左前缀原则规定了联合索引在何种查询中才能生效，规则如下：
如果想使用联合索引，联合索引的最左边的列必须作为过滤条件，否则联合索引不会生效。
这也就是说，联合索引其实拥有单列索引的作用。
下面我们给上表t插入数据，学习联合索引如何正确使用：

insert into t(a,b) values(1,4);
insert into t(a,b) values(2,5);
insert into t(a,b) values(3,6);
1
2
3

最左前缀原则实验：

3）联合索引使用场景

那么联合索引的优势在哪里呢？

首先，我们需要看一下联合索引内部的结果：从本质上说，联合索引还是一个B+树，不过联合索引的键值数量不是1，
而是大于等于2. 我们一个两列联合索引假定两个键值分别为key1,key2，则其B+树结构如下图：

从上图中我们可以看到，通过叶子节点可以逻辑上顺序地读出，所有数据：
(1,2) （1,2） (2,1) (2,4) (3,1) (3,2).
因此，对于查询

SELECT * FROM TABLE WHERE key1=xxx and key2=xxx 显然是可以使用（key1,key2）这个联合索引的。


对于单个列key1的查询 `SELECT * FROM TABLE WHERE key1=xxx` 根据最左前缀原则也是可以使用联合索引的。
1
2
3
4

但是：对于单个列key2的查询时不可以使用B+树索引的：

SELECT * FROM TABLE WHERE key2=xxx;
1

我们查看上图B+树可以看到叶子节点上的key2的值为1，2，1，4，1，2,这显然不是顺序的，故对于key2列的查询是不能使用联合索引的。

综上，我们可以总结出联合索引的一个优势：当两个列的组合是唯一值时，联合索引是个不错的选择。

联合索引的另一个好处就是可以对第二个键值进行排序

例如，在很多时候我们需要查询某个用户的购物情况，并按照时间排序。这时使用联合索引（userid,buy_date）可以减少一次排序操作，因为这个索引本身在叶子节点上已经排序了。
实例：
首先创建一张表

CREATE TABLE buy_log(
    userid int unsigned not null,
    buy_date DATE
)
1
2
3
4

然后给表加上一个两个索引：

ALTER TABLE buy_log ADD KEY (userid);
ALTER TABLE buy_log ADD KEY(userid,buy_date);
1
2

最后向表中插入数据：

INSERT INTO buy_log (userid,buy_date) VALUES (1,'2021-01-01');
INSERT INTO buy_log (userid,buy_date) VALUES (2,'2021-01-01');
INSERT INTO buy_log (userid,buy_date) VALUES (3,'2021-01-01');
INSERT INTO buy_log (userid,buy_date) VALUES (1,'2021-02-01');
INSERT INTO buy_log (userid,buy_date) VALUES (3,'2021-02-01');
INSERT INTO buy_log (userid,buy_date) VALUES (1,'2021-03-01');
1
2
3
4
5
6

这时联合索引的B+树结构如下：

本例中创建了两个索引来进行比较，一个是userid的单列索引，一个是userid和buy_date的联合索引。
如果只对userid进行查询，例如：

EXPLAIN SELECT * FROM buy_log WHERE userid=2;
1

可以看到，优化器最终选择的是userid，从这里可以看出，查询一行数据时，当单列和索引和联合索引发生冲突时，优先选择单列索引。

接着，假定要取出userid为1的最近3次的购买记录：

EXPLAIN SELECT * FROM buy_log WHERE userid=1 ORDER BY buy_date DESC LIMIT 3;
1

从这里可以看到优化器选择了联合索引，下面我们看下这个查询的结果：

上面说到联合索引的一个优势就是：可以对第二个键值进行排序
所以下面语句在不使用ORDER BY额外排序的基础上一样可以得到排序结果：

EXPLAIN SELECT * FROM buy_log WHERE userid=1 DESC LIMIT 3;
1

这就是使用联合索引的一个巨大的优势！！！

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦