在关系型 数据库 建表期间, 我们需要考虑很多很多的事项. 诸如表存储什么数据, 列上使用的数据类型, 选择什么样的存储引擎等等. 本文主要介绍针对表上列使用三种不同的数据类型来进行对比, 以观察选择不同数据类型时, 对于性能造成的影响.
一, 建表时需要考虑的事项
作用:
存储什么数据?
结构:
包含什么列, 需要约束吗?
存储:
每一列使用什么数据类型? 需要索引吗?
引擎:
使用什么存储引擎呢?
数据筛选:
哪些列被频繁用作过滤条件? 增删改查频率?
一, 构造测试环境
- CREATE TABLE `tb_char` (
- `uid` int(11) NOT NULL AUTO_INCREMENT,
- `mobile` char(11) DEFAULT NULL,
- `passwd` varchar(50) DEFAULT NULL,
- `name` varchar(50) DEFAULT NULL,
- `sex` tinyint DEFAULT NULL,
- `birthday` datetime DEFAULT NULL,
- `updated_time` datetime DEFAULT NULL,
- PRIMARY KEY (`uid`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
使用三个表相同 mobile 列使用三种不同数据类型, 分别为:
- Char(11)
- Varchar(11)
- Bigint(11)
对应表名分别为:
- tb_char
- tb_varchar
- tb_bigint
三张表总记录数 100W,
测试环境:
- (root@localhost) [tempdb]> show variables like 'version';
- +---------------+------------+
- | Variable_name | Value |
- +---------------+------------+
- | version | 5.7.23-log |
- +---------------+------------+
- select 'Leshami' author,'http://blog.csdn.net/leshami' Blog;
- +---------+------------------------------+
- | author | Blog |
- +---------+------------------------------+
- | Leshami | http://blog.csdn.net/leshami |
- +---------+------------------------------+
二, 基于无索引情形比对
表上统计信息, MySQL 统计信息不是很准确, 如下图, 基于 bigint 数据类型占用的磁盘你空间与 char 类型占用磁盘空间等同. varchar 变长要大一些. 因为 varchar 需要单独的字节来存放字符终止信息.
-- 下面执行以下三条 SQL 语句比对性能
- SELECT *
- FROM tb_char
- WHERE mobile = '17998335908';
- SELECT *
- FROM tb_varchar
- WHERE mobile = '17998335908';
- SELECT *
- FROM tb_bigint
- WHERE mobile = 17998335908;
每条 SQL 总计执行 10 次, 观察每条好用的平均时间, bigint 性能最佳.
二, 基于索引情形比对
下面为过滤条件列 mobile 添加索引列, 观察性能表现
- CREATE INDEX uk_mobile ON tb_char(mobile);
- CREATE INDEX uk_mobile ON tb_varchar(mobile);
- CREATE INDEX uk_mobile ON tb_bigint(mobile);
如下图所示, 索引占用磁盘开销是 bigint 数据类型最小, 基于索引查询的时间, 三者相当
三, 基于索引列分组聚合情形比对
为了更好比对性能, 下面基于索引列进行分组以及聚合运算, 可以看出依旧是 bigint 数据类型性能最佳
四, 最终比对结果及结论
最终完整结果图:
结论:
1) 满足需求的前提使用更小长度的数据类型 (更少磁盘占用, I/O,CPU,memory 开销)
2) 整型优先原则, 使用简单数据类型
3) 避免使用 NULL 字段, NULL 字段很难查询优化, 的索引需要额外空间, 复合索引无效
4) 少用 text/blob,varchar 的性能会比 text 高很多
来源: http://www.linuxidc.com/Linux/2018-11/155505.htm