mysql中字段类型及索引的重要性

背景

大家都知道数据库表字段设计得是否合理，对查询速度的快慢至关重要，下面做个简单的测试，看下差距有多大

开始

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、结论
合理的字段类型不论对 查询速度 或是 数据存储 都至关重要