记一次MySql单列索引和联合索引的使用区别

2019独角兽企业重金招聘Python工程师标准>>>

情况是这样，有一张表，建立了一个组合索引，比如:userId，userType，orgId这三个字段组合，顺序也是这样的，然后写sql的时候这样写的：

 select * from user where userType=0 and userId=1;

 同事说这样写SQL的效率会有影响，建立联合索引的时候字段是什么顺序就要按照顺序来，所以要把后面的where条件改为 userId=1 and userType=0才可以，以前还真没有注意过，这次自己亲自试验了一下。

建立两张表，其实表结构一样，只不过表名和索引类型不一样

CREATE TABLE `gift` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  `name` varchar(50) DEFAULT NULL COMMENT '道具名称',
  `description` varchar(300) DEFAULT NULL COMMENT '道具功能简介',
  `status` tinyint(1) DEFAULT NULL COMMENT '道具状态,1:启用中，0:',
  `scene_type` int(11) DEFAULT NULL COMMENT '场景类型，0:直播场景,1:回放场景',
  `price` float DEFAULT NULL COMMENT '价格',
  `integration` float DEFAULT NULL COMMENT '道具积分',
  `charge_status` int(11) DEFAULT NULL COMMENT '收费状态（0:免费,1:收费,2:折扣）',
  `rank` int(11) DEFAULT NULL COMMENT '排序,比如有新道具，需要优先进行推荐',
  `app_id` int(11) DEFAULT NULL COMMENT '应用的标识',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` int(11) DEFAULT NULL COMMENT '创建人ID',
  `data` varchar(1000) DEFAULT NULL COMMENT '预留字段，用来存放logo，效果图等信息，JSON串形式',
  PRIMARY KEY (`id`),
  KEY `idx` (`name`,`status`,`scene_type`,`app_id`)
) ENGINE=InnoDB AUTO_INCREMENT=786663 DEFAULT CHARSET=utf8;

 

第一张表gift和索引为联合索引，如图：

第二张表gift2为单列索引，如图：

 

下面开始进行测试：

相同的SQL分别查询两张表，使用EXPLAIN解析一下SQL

EXPLAIN select * from gift where `name`='道具' and scene_type=1;
EXPLAIN select * from gift2 where `name`='道具' and scene_type=1;

     显示的结果为两条SQL都会使用到索引，这我就不上图了。

然后只查询其中的某列，但是这个列已经创建索引

EXPLAIN select `name`,`status` from gift where `name`='道具' and scene_type=1;
EXPLAIN select `name`,`status` from gift2 where `name`='道具' and scene_type=1;

    显示的结果为两条SQL也都使用了索引。

继续查询没有创建索引的列，这里rank字段并没有创建索引

EXPLAIN select `name`,`status`,rank from gift where `name`='道具' and scene_type=1;
EXPLAIN select `name`,`status`,rank from gift2 where `name`='道具' and scene_type=1;

     显示的结果为两条SQL也都使用了索引。

接下来把SQL调整一下，name字段都建立了索引，下面把where条件里的name条件去掉

EXPLAIN select `name`,`status` from gift where scene_type=1;
EXPLAIN select `name`,`status` from gift2 where scene_type=1;

     显示的结果为两条SQL也都使用了索引。

还是上面这条SQL，把rank列再加上去，再查看下效果

EXPLAIN select `name`,`status`,rank from gift where scene_type=1;
EXPLAIN select `name`,`status`,rank from gift2 where scene_type=1;

    这个时候比较奇怪的事情就出来，第一条SQL根本没有用到索引，第二条SQL还和以前一样，同样使用到了索引。

其实在联合索引上会有一个mysql索引最左匹配原则，但是如果联合索引的第一个列不在where条件语句中，并且所查询的列其中有的是没有建立索引的，那么这个联合索引就是无效的，具体为什么会这样我也还没有整明白（囧），不过以后再写SQL也会注意一下这方面的问题，而且公司DBA也建议如果使用联合索引，那么where条件也要尽量根据联合索引的顺序来，如果不按照顺序来，索引也同样会用到，但是在执行前，SQL优化器也会将条件调整为联合索引的顺序，既然可以直接避免这种情况，就没必要再让SQL优化器去处理，毕竟处理也是有开销的。

转载于:https://my.oschina.net/857359351/blog/658668