mysql 验证状态字段加索引的必要性
假设某状态有：1未开始，2处理中，3已完成
随时间变化3越来越多，1和2则始终维系在少量
测试步骤：
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
6 对比及结论
对于分布不均匀的枚举字段建立索引是有必要的，可以极大提升数据量小的状态查询时间（未开始和处理中视为热数据，已完成视为冷数据）
