MySQL数据类型选择性能比对

栏目: 数据库 · Mysql · 发布时间: 5年前

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

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

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

作用:

存储什么数据?

结构:

包含什么列,需要约束吗?

存储:

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

引擎:

使用什么存储引擎呢?

数据筛选:

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

一、构造测试环境

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需要单独的字节来存放字符终止信息。

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性能最佳。

MySQL数据类型选择性能比对

二、基于索引情形比对

下面为过滤条件列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数据类型选择性能比对

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

为了更好比对性能,下面基于索引列进行分组以及聚合运算,可以看出依旧是bigint数据类型性能最佳

MySQL数据类型选择性能比对

四、最终比对结果及结论

最终完整结果图:

MySQL数据类型选择性能比对

结论:

1)满足需求的前提使用更小长度的数据类型(更少磁盘占用,I/O,CPU,memory开销)

2)整型优先原则,使用简单数据类型

3)避免使用NULL字段,NULL字段很难查询优化、的索引需要额外空间、复合索引无效

4)少用text/blob,varchar的性能会比text高很多


以上所述就是小编给大家介绍的《MySQL数据类型选择性能比对》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

数据库索引设计与优化

数据库索引设计与优化

【美】Tapio Lahdenmaki、【美】Michael Leach / 曹怡倩、赵建伟 / 电子工业出版社 / 2015-6 / 79.00元

《数据库索引设计与优化》提供了一种简单、高效、通用的关系型数据库索引设计方法。作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL 运行的CPU 时间及执行时间,帮助读者从原理上理解SQL、表及索引结构、访问方式等对关系型数据库造成的影响,并能够运用量化的方法进行判断和优化,指导关系型数据库的索引设计。 《数据库索......一起来看看 《数据库索引设计与优化》 这本书的介绍吧!

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器

html转js在线工具
html转js在线工具

html转js在线工具