MySQL数据类型选择性能比对

逃离我推掉我的手 2022-04-15 07:48 250阅读 0赞

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

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

作用：  
   存储什么数据？  
结构：  
   包含什么列，需要约束吗？  
存储：  
   每一列使用什么数据类型？需要索引吗？  
引擎：  
   使用什么存储引擎呢？  
数据筛选：  
   哪些列被频繁用作过滤条件？增删改查频率?

### 一、构造测试环境 ###

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需要单独的字节来存放字符终止信息。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JvYmluc29uXzA2MTI_size_16_color_FFFFFF_t_70]

-- 下面执行以下三条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性能最佳。  
![在这里插入图片描述][20181123073947372.png]

### 二、基于索引情形比对 ###

下面为过滤条件列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数据类型最小，基于索引查询的时间，三者相当  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JvYmluc29uXzA2MTI_size_16_color_FFFFFF_t_70 1]

### 三、基于索引列分组聚合情形比对 ###

为了更好比对性能，下面基于索引列进行分组以及聚合运算，可以看出依旧是bigint数据类型性能最佳  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JvYmluc29uXzA2MTI_size_16_color_FFFFFF_t_70 2]

### 四、最终比对结果及结论 ###

最终完整结果图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JvYmluc29uXzA2MTI_size_16_color_FFFFFF_t_70 3]  
结论：  
1）满足需求的前提使用更小长度的数据类型(更少磁盘占用，I/O，CPU，memory开销)  
2）整型优先原则，使用简单数据类型  
3）避免使用NULL字段，NULL字段很难查询优化、的索引需要额外空间、复合索引无效  
4）少用text/blob，varchar的性能会比text高很多

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JvYmluc29uXzA2MTI_size_16_color_FFFFFF_t_70]: /images/20220415/0cd46693b2784c799f5416a8827a57d5.png
[20181123073947372.png]: /images/20220415/f32d9ccd93d24db1988a2233de136fc7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JvYmluc29uXzA2MTI_size_16_color_FFFFFF_t_70 1]: /images/20220415/45e0719782c04459a69a78a9df180e39.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JvYmluc29uXzA2MTI_size_16_color_FFFFFF_t_70 2]: /images/20220415/d6f7680631d848b2aa687a6ca7fac26c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3JvYmluc29uXzA2MTI_size_16_color_FFFFFF_t_70 3]: /images/20220415/c2761d7a52914b6a96bd4d99833e8378.png