您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

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

时间:2021-12-08 10:13:28  来源:  作者:吴彬的分享

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

基础原则:程序员,不是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究竟用哪个?

结果

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

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



Tags:Mysql数据库   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  Tags: Mysql数据库  点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  Tags: Mysql数据库  点击:(16)  评论:(0)  加入收藏
1. 介绍1.1 介绍今天开始我们来学习Java操作MySQL数据库的技巧,Java操作MySQL是借助JdbcTemplate这个对象来实现的。JdbcTemplate是一个多数据库集中解决方案,而我们今天只讲...【详细内容】
2021-11-05  Tags: Mysql数据库  点击:(30)  评论:(0)  加入收藏
Mysql数据库安装与操作之一 以下操作是在Linux-xshell环境下进行的(不懂的,大家可以留言) 一.安装 1. 安装数据库 wget -i -c http://dev.mysql.com/get/mysql57-community-rele...【详细内容】
2021-09-01  Tags: Mysql数据库  点击:(62)  评论:(0)  加入收藏
之前管理mysql数据库,我是远程VNC登陆到服务器,然后在服务器上通过mysql command line client进行查询操作等,偶然前几天,发现可以不远程登陆服务器,本地CMD可以直接登陆,后研究了...【详细内容】
2021-08-17  Tags: Mysql数据库  点击:(53)  评论:(0)  加入收藏
【目标】本文介绍了MySQL数据库命令规范、数据库基本设计规范、数据库字段设计规范、索引设计规范、常见索引列建议、如何选择索引列的顺序、以及数据库SQL开发规范等。一...【详细内容】
2021-07-08  Tags: Mysql数据库  点击:(61)  评论:(0)  加入收藏
大型网站为了软解大量的并发访问,除了在网站实现分布式负载均衡,远远不够。到了数据业务层、数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器来处理如此多的数据库...【详细内容】
2021-06-09  Tags: Mysql数据库  点击:(77)  评论:(0)  加入收藏
2014年6月,Red Hat公司发行的Linux社区版CentOS7.0,因甲骨文公司收购了MySQL后,有将MySQL闭源的潜在风险,为避开这个风险,CentOS7已将MySQL数据库软件从默认的程序列表中移除,用ma...【详细内容】
2021-04-15  Tags: Mysql数据库  点击:(194)  评论:(0)  加入收藏
生活中,你我一定都看到过这种「xx元爆改出租屋」,「爆改小汽车」之类的文章,做为IT人,折腾的劲头一点也不差。软件开发过程中,你是否有时候,会拿着业务提供的一个个CSV或者JSON的...【详细内容】
2021-01-06  Tags: Mysql数据库  点击:(159)  评论:(0)  加入收藏
在企业的Liunx运维中,经常需要应用到各种shell脚本,比如Mysql数据库备份Shell脚本和Mysql主从同步监控Shell脚本等等常用企业运维shell脚本。。下面分享两例,希望对大家有帮助...【详细内容】
2020-12-18  Tags: Mysql数据库  点击:(195)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(19)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条