mysql单列索引和多列索引_mysql 多个单列索引创建的效率问题

有开发同事询问，使用一个语句建立3个单列索引，效率是否比3个单独创建语句的效率高。官方文档上并未做出直接回答，但是考虑到innodb索引机制，理论上效率应该是会高的，下面用实验来验证理论。

实验环境 mysql 5.7 innod，数据量千万级

use test;

create table test.t2 (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,id2 int,id3 int,id4 int )

构造数据：

delimiter $$

create procedure test.pCreateNums(cnt int)

begin

declare s int default 1;

while s<=cnt do

insert into test.t2(id2,id3,id4) values(s,s,s);

set s=s+1;

end while;

end $$

delimiter ;

call test.pCreateNums(10000)

--这样构造数据太慢，存储过程其实是一条语句一条语句进行执行，解析效率低下。插入数据很慢。

然后使用t2 自身填充:

insert into test.t2(id2,id3,id4) select id2,id3,id4 from test.t2;

将t2填充到130万数据

一次性在3列上创建索引：

ALTER TABLE test.t2 ADD INDEX idx2 ( id2 ), ADD INDEX idx3( id3),ADD INDEX idx4( id4);

--耗时为16秒

use test;

drop index idx2 on t2;

drop index idx3 on t2;

drop index idx4 on t2;

然后创建一个索引的时间为：

create index idx2 on t2 ( id2);

create index idx3 on t2 ( id3);

create index idx4 on t2 ( id4);

--耗时约为6秒

所以 建立3个索引和一个索引的速度差别不大。节省了两秒？ 还是说130万行的数据量太小？

---下面再次翻倍 t2表

insert into test.t2(id2,id3,id4) select id2,id3,id4 from test.t2;

将数据量翻倍到1000万

3个索引一起构建的语句耗时约为：157秒

单个索引耗时约为：72秒

3*72 = 216 秒

节约时间为： 216-157 = 60秒

--也就是说在1千万的数据级别下，一个索引减少耗时20秒。

---同理推断，在1亿数据量下，一个索引减少耗时200秒，

理论上 多个索引创建会少扫描主键，在数据量低于亿级别的时候实际上区别不大。

因为在线DDL 的支持，索引的创建支持in-place的方式，所以不会拷贝表数据，在一个sql 语句和拆分多个执行，其实性能差距不大。唯一区别是每次创建的时候读取主键构造索引的那部分。上面测试的时候每次都清理了缓存，如果考虑数据缓存的话，性能上应该没什么差距。