MySQL数据类型选择性能比对详解

日期：2020-06-02 栏目：程序人生浏览：次

在关系型数据库建表期间，我们需要考虑很多很多的事项。诸如表存储什么数据，列上使用的数据类型，选择什么样的存储引擎等等。本文主要介绍针对表上列使用三种不同的数据类型来进行对比，以观察选择不同数据类型时，对于性能造成的影响。

一、建表时需要考虑的事项

作用：

存储什么数据？

结构：

包含什么列，需要约束吗？

存储：

每一列使用什么数据类型？需要索引吗？

引擎：

使用什么存储引擎呢？

数据筛选：

哪些列被频繁用作过滤条件？增删改查频率?

一、构造测试环境 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 | | +---------+------------------------------+ 二、基于无索引情形比对

表上统计信息，MySQL统计信息不是很准确，如下图，基于bigint数据类型占用的磁盘你空间与char类型占用磁盘空间等同。varchar变长要大一些。因为varchar需要单独的字节来存放字符终止信息。

MySQL数据类型选择性能比对详解

-- 下面执行以下三条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数据类型最小，基于索引查询的时间，三者相当

MySQL数据类型选择性能比对详解