1建表

CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `status` int(1) DEFAULT NULL COMMENT '状态：1未开始，2处理中，3已完成',
  `remark` varchar(64) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2录入数据

#已完成 共 16777216 条
insert into foo (status,remark) values (3,"a");#受影响的行: 1 时间: 0.010s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 1 时间: 0.011s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 2 时间: 0.014s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 4 时间: 0.010s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 8 时间: 0.011s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 16 时间: 0.011s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 32 时间: 0.011s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 64 时间: 0.011s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 128 时间: 0.012s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 256 时间: 0.013s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 512 时间: 0.014s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 1024 时间: 0.015s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 2048 时间: 0.020s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 4096 时间: 0.025s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 8192 时间: 0.043s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 16384 时间: 0.067s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 32768 时间: 0.127s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 65536 时间: 0.238s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 131072 时间: 0.552s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 262144 时间: 1.071s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 524288 时间: 2.106s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 1048576 时间: 4.244s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 2097152 时间: 9.046s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 4194304 时间: 18.645s
insert into foo (status,remark) select status,concat(remark,"a") from foo;#受影响的行: 8388608 时间: 39.254s

#处理中 共 16 条
insert into foo (status,remark) values (2,"b");#受影响的行: 1 时间: 0.010s
insert into foo (status,remark) select status,concat(remark,"b") from foo where status = 2;#受影响的行: 1 时间: 9.113s
insert into foo (status,remark) select status,concat(remark,"b") from foo where status = 2;#受影响的行: 2 时间: 9.062s
insert into foo (status,remark) select status,concat(remark,"b") from foo where status = 2;#受影响的行: 4 时间: 9.125s
insert into foo (status,remark) select status,concat(remark,"b") from foo where status = 2;#受影响的行: 8 时间: 9.136s
#未开始 1
insert into foo (status,remark) values (1,"a");

3测试

select count(*) from foo; #受影响的行: 0 时间: 3.624s
select count(*) from foo where status = 1; #受影响的行: 0 时间: 3.763s
select count(*) from foo where status = 2; #受影响的行: 0 时间: 3.793s
select count(*) from foo where status = 3; #受影响的行: 0 时间: 4.126s

4 添加索引

ALTER TABLE `foo` ADD INDEX `idx_status` (`status`) USING BTREE ; #受影响的行: 0 时间: 23.407s

5再测试

select count(*) from foo; #受影响的行: 0 时间: 3.113s
select count(*) from foo where status = 1; #受影响的行: 0 时间: 0.012s 
select count(*) from foo where status = 2; #受影响的行: 0 时间: 0.010s
select count(*) from foo where status = 3; #受影响的行: 0 时间: 3.591s