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

基础原则：程序员，不是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多万数据的时候，数据库的操作情况

查询

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

然后 取出10条数据

select * from user_test limit 10;

速度非常快，0.00秒 （400万+的数据量）

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; 

查询速度非常快

查询

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万+的数据量）

没有索引

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

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

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

增加索引之后

增加索引之后，耗时：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万+的数据量）

查询

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

查询

增加索引前查询count

select count(user_id) from user_test;

耗时 56.99秒

增加索引之后查询 count

查询

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万数据量级

查询

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

查询

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万数据量级

查询

第二次查询情况

查询

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

查询

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

增加索引之后：

增加索引

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

比较

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

结果

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

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