背景
大家都知道数据库表字段设计得是否合理,对查询速度的快慢至关重要,下面做个简单的测试,看下差距有多大
开始
1、在数据库中新建两个表 test1(不合理的表) 和 test2(合理表),两张表的 send_time 字段的类型不一样
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`send_time` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`send_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2、新建存储过程,导入10 000 000 条测试数据(运行过程可能需要几分钟),运行结束后再将数据导入表test2保证两张表的数据一致
BEGIN
DECLARE i INT DEFAULT 0;
START TRANSACTION;
WHILE i<10000000 DO
INSERT INTO test1(send_time) VALUES(from_unixtime(1541302365+FLOOR(rand()*154130236),"%Y-%m-%d %H:%i:%s"));
SET i=i+1;
END WHILE;
COMMIT;
END
3、查看表信息可得:
结论:varchar 表字段占用存储空间 是 datetime 表的三倍左右
4、查询速度比较
SELECT * FROM test1 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
SELECT * FROM test2 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
结果比较
test1 test2
2.653s 1.833s
2.650s 1.866s
5、给表添加索引(执行过程需要几分钟),并查询速度比较
-- 添加索引
ALTER TABLE `test1` ADD INDEX `n_sendtime` (`send_time`) ;
ALTER TABLE `test2` ADD INDEX `n_sendtime` (`send_time`) ;
-- 查询速度比较:
SELECT * FROM test1 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
SELECT * FROM test2 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
结果比较
test1 test2
0.084s 0.048s
0.062s 0.046s
0.062s 0.048s
0.066s 0.047s
4、结论
合理的字段类型不论对 查询速度 或是 数据存储 都至关重要