摘要

作为程序猿，之前一直没有过多深入了解数据库中单列索引和联合索引的区别和作用，在一个紧张的项目结束后，终于有时间来探究和总结下项目开发过程中很多不明白的事情。

本文主要先从实际出发，测试各种场景下的实际作用效果，在文章的最后将会根据实测结果进行总结。

之前开发过程中一直没有注意数据库中索引的作用，在上一个项目中，有一个自称数据库大佬的人给予的说法是，只需要创建单列索引即可，无需创建联合索引（由于咱不是数据库大佬，咱也不敢随便乱言，于是就按照大佬的说法只添加了单列索引）。项目结束后，一直对这个索引的事情耿耿于怀，想一探究竟，弄明白联合索引和单列索引到底有什么区别和联系，以及它们分别具有什么样的作用。

说到索引，我们首先需要先说下一个名词：执行计划（EXPLAN）。

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈，具体请查看官方文档，如想细看EXPLAN的作用，请参考另一篇文章：。

通过EXPLAIN，我们可以分析出以下结果：

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用
  

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询
  

我们今天就将基于EXPLAN来说明单列索引和联合索引的区别和作用。

一、联合索引

首先创建如下表：

DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '姓名',
  `age` int(3) NOT NULL COMMENT '年龄',
  `address` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '地址',
  `phone` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '联系方式',
  PRIMARY KEY (`id`),
  KEY `idx_colum_1` (`name`,`age`,`phone`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

可以发现，我们在user_info表上创建了一个联合索引，包含三个列：`name`,`age`,`phone`（注意先后顺序）

我们通过SQL来看下索引执行情况。

1、查询条件为name

explain select * from user_info where name = '张三';

结果如下：

通过KEY列可以发现，联合索引有效。

2、查询条件为age

explain select * from user_info where age > 12;

结果如下：

通过KEY列可以发现，联合索引无效。

3、查询条件为phone

explain select * from user_info where phone  = '18222222222';

结果如下：

通过KEY列可以发现，联合索引无效。

4、查询条件为name AND age

explain select * from user_info where  name ='张三' and age >12;

结果如下：

通过KEY列可以发现，联合索引有效。

5、查询条件为age AND name

explain select * from user_info where age >12 and name ='张三';

结果如下：

通过KEY列可以发现，联合索引有效。

6、查询条件为name OR age

explain select * from user_info where age >12 or name ='张三';

结果如下：

通过KEY列可以发现，联合索引无效。

7、查询条件为name AND phone

explain select * from user_info where name ='张三' and phone = '18222222222';

结果如下：

通过KEY列可以发现，联合索引有效。

8、查询条件为age AND phone

explain select * from user_info where age >12 and phone = '18222222222';

结果如下：

通过KEY列可以发现，联合索引无效。

9、查询条件为name AND age AND phone

explain select * from user_info where name='张三' and age > 12 and phone = '18222222222';

结果如下：

通过KEY列可以发现，联合索引有效。

二、单列索引

我们仍旧使用上面的表结构，唯一不同的是将上面的联合索引换成单列索引，如下只保留了一个索引name：

DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '姓名',
  `age` int(3) NOT NULL COMMENT '年龄',
  `address` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '地址',
  `phone` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '联系方式',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

1、查询条件为name AND age

EXPLAIN select * from user_info where NAME = '张三' ;

结果如下：

通过KEY列可以发现，联合索引有效。

2、查询条件为name AND age

EXPLAIN select * from user_info where NAME = '张三' and age >12;

结果如下：

通过KEY列可以发现，联合索引有效。

3、查询条件为age AND name

EXPLAIN select * from user_info where age >12 and NAME = '张三';

结果如下：

通过KEY列可以发现，联合索引有效。

4、查询条件为name OR age

EXPLAIN select * from user_info where NAME = '张三' or age >12;

结果如下：

通过KEY列可以发现，联合索引无效。

5、查询条件不带name

EXPLAIN select * from user_info where age >12;

结果如下：

通过KEY列可以发现，联合索引无效。

三、多个单列索引

仍旧以上面表结构为基础，这次创建两个单列索引，分别为name 和 age

DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '姓名',
  `age` int(3) NOT NULL COMMENT '年龄',
  `address` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '地址',
  `phone` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '联系方式',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

1、查询条件为单个的不再操作（上面二已经做出实践）

略过

2、查询条件为name AND age

EXPLAIN SELECT * FROM user_info where name ='张三' and age >12;

结果如下：

通过KEY列可以发现，联合索引有效但是只用到了name的索引。

3、查询条件为age AND name

EXPLAIN SELECT * FROM user_info where age = 12 and name ='张三';

结果如下：

通过KEY列可以发现，联合索引有效但是只用到了name的索引。（这个现象体现在另一个技术中：index merge（索引合并））

4、查询条件为name or age

EXPLAIN SELECT * FROM user_info where age = 12 or name ='张三';

结果如下：

通过KEY列可以发现，联合索引无效。

四、总结

通过对联合索引和单列索引的分别验证，我们可以总结出以下结论：

1、对于单列索引，可作用到包含该列并且为AND的查询中；

2、联合索引，只有包含联合索引中第一个列的AND查询才会生效

3、多个单列索引进行多列AND查询时，此情况比较复杂

3.1、MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

3.2、如果我们的 where 中有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。索引合并是通过多个range类型的扫描并且合并它们的结果集来检索行的。仅合并来自单个表的索引扫描，而不是跨多个表的索引扫描。合并会产生底层扫描的三种形式：unions（合并）、intersections（交集）、unions-of-intersections（先取交集再合并）

3.3、但是实际情况是index merge 很少使用，为什么呢？与其说是“数据库查询只能用到一个索引”，倒不是说是 和全表扫描/只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是是用一个索引。所以如果经常用多个列进行AND查询，那么最好的方式是使用联合索引。

 收藏 ( 0 )  赞 ( 0 )