多个单列索引和联合索引的区别

MySql版本为:5.7.14

联合索引测试

创建一张10w数据的测试表:

CREATE TABLE `10w` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1` int(11) NOT NULL DEFAULT '0',
  `t2` varchar(255) NOT NULL DEFAULT '',
  `t3` int(11) NOT NULL DEFAULT '0',
  `t4` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `index_3` (`t1`,`t2`,`t3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

依据结构,我们为t1,t2,t3三个字段添加了联合索引!

1、查询条件为t1时

mysql> explain select * from 10w where t1=1000;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | 10w   | NULL       | ref  | index_3       | index_3 | 4       | const |    1 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

联合索引有效

2、查询条件为t2时

mysql> explain select * from 10w where t2='a1000';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | 10w   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100039 |       10 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

联合索引无效

3、查询条件为t3时

mysql> explain select * from 10w where t3=1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | 10w   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100039 |       10 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

联合索引无效

4、查询条件为t1 and t2时

mysql> explain select * from 10w where t1=1000 and t2='a1000';
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | 10w   | NULL       | ref  | index_3       | index_3 | 771     | const,const |    1 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+

联合索引有效

5、查询条件为t2 and t1时

mysql> explain select * from 10w where t2='a1000' and t1=1000;
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | 10w   | NULL       | ref  | index_3       | index_3 | 771     | const,const |    1 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+

在4的基础上调换了查询条件的顺序,发现联合索引依旧有效

6、查询条件为t1 or t2时

mysql> explain select * from 10w where t1=1000 or t2='a1000';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | 10w   | NULL       | ALL  | index_3       | NULL | NULL    | NULL | 100039 |       10 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

把 and 换成 or,发现联合所索引无效

7、查询条件为t1 and t3时

mysql> explain select * from 10w where t1=1000 and t3=1000;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | 10w   | NULL       | ref  | index_3       | index_3 | 4       | const |    1 |       10 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+

这两个条件分别位于联合索引位置的第一和第三,测试联合索引依旧有效

8、查询条件为t2 and t3时

mysql> explain select * from 10w where t2='a1000' and t3=1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | 10w   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100039 |        1 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

这两个条件分别位于联合索引位置的第二和第三,发现联合索引无效

9、查询条件为t1 and t2 and t3时

mysql> explain select * from 10w where t1=1000 and t2='a1000' and t3=1000;
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | 10w   | NULL       | ref  | index_3       | index_3 | 775     | const,const,const |    1 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+

所有条件一起查询,联合索引有效

单列索引测试

将上面三个联合索引字段改成单个单列索引

1、查询条件为t1 and t2 and t3时

mysql> explain select * from 10w where t1=1000 and t2='a1000' and t3=1000;
+----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | 10w   | NULL       | ref  | t1,t2,t3      | t1  | 4       | const |    1 |        5 | Using where |
+----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+

我们发现三个单列索引只有 t1 有效(位置为查询条件第一个),其他两个都没有用上

2、查询条件为t2 and t3时

mysql> explain select * from 10w where t2='a1000' and t3=1000;
+----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | 10w   | NULL       | ref  | t2,t3         | t2  | 767     | const |    1 |        5 | Using where |
+----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+

我们发现此处两个查询条件只有 t2 有效(位置也为查询条件第一个),后面的无效

3、查询条件为t1 or t2时

mysql> explain select * from 10w where t1=1000 or t2='a1000';
+----+-------------+-------+------------+-------------+---------------+-------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key   | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+-------------+---------------+-------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | 10w   | NULL       | index_merge | t1,t2         | t1,t2 | 4,767   | NULL |    2 |      100 | Using union(t1,t2); Using where |
+----+-------------+-------+------------+-------------+---------------+-------+---------+------+------+----------+---------------------------------+

这次把 and 换成 or,发现两个查询条件都用上索引了

结论

通俗理解
利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用两个单独的索引。
复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处。
所以说创建复合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。

重点
多个单列索引在多条件查询时只会生效第一个索引!所以多条件联合查询时最好建联合索引!

最左前缀原则
顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建联合索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。这样的话扩展性较好,比如 userid 经常需要作为查询条件,而 mobile 不常常用,则需要把 userid 放在联合索引的第一位置,即最左边

思考
同时存在联合索引和单列索引(字段有重复的),这个时候查询mysql会怎么用索引呢?

这个涉及到mysql本身的查询优化器策略了,当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引;mysql执行优化器会对其进行优化,当不考虑索引时,where条件顺序对效率没有影响,真正有影响的是是否用到了索引!

联合索引本质

当创建(a,b,c)联合索引时,相当于创建了(a)单列索引,(a,b)联合索引以及(a,b,c)联合索引 想要索引生效的话,只能使用 a和a,b和a,b,c三种组合;当然,我们上面测试过,a,c组合也可以,但实际上只用到了a的索引,c并没有用到! 注:这个可以结合上边的 通俗理解 来思考!

其他知识点

1、需要加索引的字段,要在where条件中 2、数据量少的字段不需要加索引;因为建索引有一定开销,如果数据量小则没必要建索引(速度反而慢) 3、如果where条件中是OR关系,加索引不起作用 4、联合索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。

Last Updated:
Contributors: huangyanfu