<返回更多

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

2021-12-08    吴彬的分享
加入收藏

测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使用方式效率最高。

基础原则:程序员,不是DB ,所以能用程序逻辑解决的问题,就不要使用DB来解决问题,除非程序逻辑做不到,必须使用数据库来解决的问题。比如分库分表,比如数据唯一性,这些能在程序里面完成处理的,就不要使用数据库来做,这样万一我们程序出问题了,数据库还可以兜底,如果直接使用数据库来做,数据库出问题了,那用什么兜底呢???

测试环境:4核心8G内存 普通机械硬盘,内网环境(不考虑网速问题)

操作系统:centos linux release 7.9.2009 (Core) 64位

软件:MySQL Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrApper

注意:表的字符设置为 utf8mb4 ,引擎为:InnoDB

如果不是以上情况,可能不合适本文测试的内容。

晚点将会把生成数据的代码发出来,分享给想要测试的小伙伴,如果感觉有用,请关注并转发,谢谢!

先说结论:

各方面效率最高的是varchar ,居然是 varchar 这 。。。 。。。难道是因为使用utf8mb4 数据集的原因,目前尚未对此进行测试。

那么表的使用结构就非常明了了。

主键自增使用 int 就可以了。其他大部分字段均可以使用 varchar ,单个表格数据量尽量控制在 500万的数据以内,500万的数据备份可能就有4G 左右了。所以不适用bigint,考虑到数据量大的,可以在程序逻辑分表,因为是程序员不是DB,所以尽量少DB操作,程序业务逻辑处理。

电话号码切记不要使用 bigint 原因是因为会有 010 7532 1456 这样的格式,那么前面的 0 就不会被记录在数据库,那么逻辑需要判断,会增加代码量。比价大小也可以用 varchar 需要在逻辑增加一步判断即可。

不要使用tinyint 做属性,比如 1代表正常,2代表待审核,3代表审核通过,4代表删除

直接使用 varchar 记录 正常,待审核,审核通过,删除

这样,当以后增加属性的时候可以直接增加,而且当紧急维护的时候可以直接知道属性含义,可以直接操作库。

如果数据库记录的都是 0 1 2 3 4 5 6 7 8 9 这样的数字,那么现在看到的是 15 请问代表什么含义,那13呢?这样要查代码,非常麻烦。尽量不要给自己找麻烦。

以下三个字段必须要有,时间相关的记录数据变化时间,这个由mysql自己控制,避免程序逻辑问题。最后一个是标志位删除。

CREATE TABLE `XXXXX` (
`XXXXX_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'XXXXX的ID',
`XXXXX ` varchar(XXX) DEFAULT NULL COMMENT '任意数据',
`XXXXX` varchar(XXX) DEFAULT NULL COMMENT '任意数据',

… …

`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`tb_status` varchar(10) DEFAULT '正常' COMMENT '状态:正常,正常;删除,删除;',
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

另外,不建议做数据库基本的唯一属性。可以在程序逻辑处理。原则尽量简化,能省就省。

以下为测试内容,用来证明上面的结论的,仅作参考。数据量 400万到1500万+ 。

首先,制作表格:

CREATE TABLE `user_test` (
`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`char_email` char(200) DEFAULT NULL COMMENT 'char格式的邮箱',
`varchar_email` varchar(200) DEFAULT NULL COMMENT 'varchar格式的邮箱',
`char_name` char(200) DEFAULT NULL COMMENT 'char格式的名字',
`varchar_name` varchar(200) DEFAULT NULL COMMENT 'varchar格式的名字',
`varchar_content` varchar(800) DEFAULT NULL COMMENT 'varchar格式的文字内容',
`tinyint_type` tinyint(4) DEFAULT NULL COMMENT '用tinyint表示的属性',
`char_type` char(20) DEFAULT NULL COMMENT '用char格式表示的属性',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`tb_status` char(50) DEFAULT '正常' COMMENT '状态:正常,正常;删除,删除;',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注意只有主键,没有索引的情况下。

然后用程序插入数据,先测试看插入 400多万数据的时候,数据库的操作情况

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

因为只有主键,并没有做索引,根据主键count耗时 3.53秒

然后 取出10条数据

select * from user_test limit 10;

速度非常快,0.00秒 (400万+的数据量)

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

400万数据查询

select * from user_test where char_email='zyzonovuvaadcd@126.com' limit 10;

char 格式的数据查询,耗时 7.39秒 (400万+的数据量)

select * from user_test where varchar_email='zyzonovuvaadcd@126.com' limit 10;

varchar 格式的数据查询,耗时 5.52 秒 (400万+的数据量)

select * from user_test where user_id > 78921 limit 10;

这个查询可以用于分页操作,数据量大的时候可以用

耗时 0.07 秒,速度也很快。使用主键进行判断查询很快。(400万+的数据量)

select * from user_test where user_id > 79021 limit 2; 

查询速度非常快

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

select * from user_test where varchar_name='行文可抒怀畅志,扬己志于天下,左登峰也喜此道,但他心性阴暗,不喜成群为伍,喧哗宣讲。' limit 12;

varchar 查询 耗时24.34 秒(400万+的数据量)

 

select * from user_test where char_name='行文可抒怀畅志,扬己志于天下,左登峰也喜此道,但他心性阴暗,不喜成群为伍,喧哗宣讲。' limit 12;

char 查询 耗时 24.09 秒 (400万+的数据量)

将表的数据量升级到 1513万,继续测试。

select count(user_id) from user_test;

耗时 50.32 秒,虽然是主键但是因为没加索引,速度很慢。(1500万+的数据量)

select count(char_type) from user_test where char_type='del';

char type 查询 耗时 57.27 秒 ,并非主键,并且没有索引。(1500万+的数据量)

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

没有索引

增加索引之后,耗时:1秒,和tinyint差别不大。

select count(tinyint_type) from user_test where tinyint_type='7';

tinyint type 查询 耗时 56.68秒 并非主键,并且没有索引。(1500万+的数据量)

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

增加索引之后

增加索引之后,耗时:0.69秒。(1500万+的数据量)

 

select * from user_test where tinyint_type=7 limit 3;

耗时:0.07秒,带索引,(1500万+的数据量)

select * from user_test where char_type='del' limit 3;

耗时:0.1秒,带索引,(1500万+的数据量)

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

这个测试证明,使用tinyint 或者 char 用来表示 属性,在没有索引的情况下,差别非常小。

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

增加索引前查询count

select count(user_id) from user_test;

耗时 56.99秒

增加索引之后查询 count

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

select count(user_id) from user_test;

耗时 3.24秒 ,速度提升非常多啊。

但是依然有3.24秒,速度依然不理想啊,查询了下解决方案,不用主键做索引,

使用 count(*) 居然比使用 count(主键快) 这个你敢信???经过反复测试,发现都差不多的。

那最终的count的效率的解决方案是什么呢?答案是如果数据量比较大,可以使用一个计数列,通过逻辑处理,不过如果增删改查比较多就不要用这样的方式,可以通过逻辑处理的方式进行处理,比如入库队列,redis 缓存处理等等。

但是如果业务逻辑OK的情况下,单表数据量控制在 500万以内更加合适。

以此表为例子,当数据在300万的时候,备份的sql文件是2G左右大小。500万+ 的数据库备份文件会更大一些,这样的话,还要考虑备份的时间和效率问题。切记一定要做读写分离,并且在从库上做数据备份操作。

结论:tinyint 和 char 的效率非常接近,但是在维护和逻辑理解上,char更加直观,建议使用char 又因为 varchar 的查询效率高于 char 所以,都用 varchar吧。

使用bigint ,tinyint 还是使用char,varchar ?

测试表结构:

CREATE TABLE `test_big_char` (
`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '测试的ID',
`bigint_num` bigint(20) DEFAULT NULL COMMENT 'bigint数据',
`char_num` char(20) DEFAULT NULL COMMENT 'char数据',
`varchar_num` varchar(20) DEFAULT NULL COMMENT 'varchar数据',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`tb_status` char(50) DEFAULT '正常' COMMENT '状态:正常,正常;删除,删除;',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

 

select count(*) from test_big_char;

耗时1.3秒,400万数据量级

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

根据主键进行简单查询,速度非常快。400万数据量级

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

select * from test_big_char where bigint_num = 6616351006811;

耗时:2.4秒 ,400万数据量级

增加索引之后,耗时:0.01秒

切换其他值查询,耗时:0.00秒,带索引,400万数据量级

select * from test_big_char where char_num = '6616351006811';

耗时:2.87秒 ,400万数据量级

增加索引之后,耗时:0.00秒

select * from test_big_char where varchar_num = '6616351006811';

耗时:2.28秒 ,400万数据量级

增加索引之后,耗时:0.00秒

切换其他值进行查询,耗时:0.00秒,带索引,400万数据量级

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

第二次查询情况

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

第三次查询,因为 char 查询最慢,所以之做 bigint 和 varchar 的比较:

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

查询

在没有索引的情况下,varchar和bigint 的查询效率几乎相同 ,400万数据量级。

增加索引之后:

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

增加索引

那么接下来测试 大于,小于 的各种情况,实际测试 char和varchar 得到的结果不准确,需要加其他限定条件,但就数据库查询而言,varchar的查询效率最高。

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

比较

为了避免执行先后顺序问题,再执行一次顺序不一样的。

Mysql数据库tinyint,int,bigint,char,varchar究竟用哪个?

结果

还测试了许多其他的内容,就不再一一记录了,感兴趣可以自己操作一遍。

晚点将会把生成数据的代码发出来,分享给想要测试的小伙伴,如果感觉有用,请关注并转发,谢谢!

声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多资讯 >>>