专栏首页01二进制聊一聊数据库(MySQL)设计中的数据类型优化

聊一聊数据库(MySQL)设计中的数据类型优化

良好的逻辑设计和物理设计是高性能的基石,在进行数据库设计时,我们应该要考虑到未来将会执行的查询语句,这就需要对各种因素进行权衡。本文将会聊一聊数据库(MySQL)设计中有关数据类型优化的一些内容。以下内容总结自《高性能 MysQL》。

选择优化的数据类型

原则

我们知道 MySQL 支持多种数据类型,通常情况下,很多数据类型都可以完成相同的工作,选择正确的数据类型对于高性能至关重要。在明确需要优化的数据类型前,我们需要先掌握几个原则,这些原则有助于我们作出更好的选择。

  • 选择相对轻量的数据类型

这里的轻量指的是,在一般情况下,应该使用可以正确存储数据的最小数据类型。例如一个列中的最大值为 2020,那就没必要选择 INT 以及更大的数据类型。

  • 简单就好

简单的数据类型通常意味着处理时需要更少的 CPU 周期。例如,整型比字符操作代价更低;存储日期/时间应该用内置的数据类型而不是字符串;存储 IP 地址时应该用整型而不是字符串。

  • 避免 NULL

通常情况下,应该设置列为 NOT NULL。因为 NULL 会使某个列的索引、值等数据的处理变得复杂。而且可为 NULL 的列会使用更多的存储空间。

针对具体的数据类型

整数类型

对于整数,可选的数据类型有 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT。分别使用 8、16,24,32,64 位存储空间。同时可选的属性有UNSIGNED,表示不允许有负值,这可以让正数的上限提高一倍。例如 TINYINT 的存储范围是 -128~127,TINYINT UNSIGNED 的存储范围是 0~255

MySQL 可以为整数类型制定宽度,例如 INT(11),但是并不会限制数值的合法范围,只会控制某些交互工具用来显示字符的个数,对于存储和计算来说,INT(1)和 INT(20)是相同的。即括号中的数字只是用于控制显示的字符数,和实际可以存储的字符数无关。

实数类型

实数是带有小数部分的数字,MySQL 中使用 DECIMAL 类型用于存储精确的小数,但是 CPU 不支持对 DECIMAL 的直接计算,因此 MySQL 服务器自身实现了 DECIMAL 的高精度计算。

与此同时 CPU 支持原生浮点计算,因此浮点数的运算速度相对 DECIMAL 会更快。浮点分为两种:FLOAT 和 DOUBLE。由于 DOUBLE 相对于 FLOAT 有更高的精度和更大的范围,MySQL 使用 DOUBLE 作为内部浮点计算的类型。

因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用 DECIMAL,例如财务数据。数据量较大时,也可以考虑使用 BIGINT 代替 DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型

常用的是 VARCHAR 和 CHAR

VARCHAR

  • 存储可变长字符串
  • 比定长类型省空间,越短的字符串使用空间越少
  • 使用 1 或 2 个额外字节记录字符串的长度。列的最大长度小于或等于 255 字节,使用 1 个字节表示,否则使用 2 个字节表示

CHAR

  • 存储定长字符串
  • 对于经常变更的数据,使用 CHAR 存储不易产生碎片

因此二者的使用场景也很明显:

VARCHAR 适合字符串列的最大长度比平均长度大很多,同时列的更新很少,以及列中的字符串使用复杂的字符集,每个字符都使用不同的字节数进行存储(UTF-8)。

CHAR 适合存储很短的字符串,或者所有值都接近同一个长度,例如存储密码的 MD5 值,或者用 CHAR(1)存储只有 Y 和 N 的值,因为 CHAR(1)需要一个字节,VARCHAR(1)需要两个字节(需要一个记录长度的额外字节)。

日期和时间

DATETIME

  • 可以保存从 1001 年到 9999 年,精度为秒
  • 将日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中,与时区无关。
  • 使用 8 个字节的存储空间

TIMESTAMP

  • 保存了从 1970 年 1 月 1 日午夜以来的秒数
  • 只使用 4 个字节存储,因此范围会小很多(最多表示从 1970 年到 2038 年)
  • 和时区有关

因为 TIMESTAMP 是用 4 个字节存储,因此最多只能保存到 2038 年,这一点也造成了非常著名的2038 年问题。

选择标识符

标识符是用于标识列与其他值进行比较(例如关联操作中,通过标识列寻找其他列),标识列在选择数据类型时,应该跟关联表中的对应列一样的类型。

整数类型通常是最好的选择,效率高且可以自增长(例如主键)。如果可以,尽量避免使用字符串作为标识列,消耗空间,且查询速度慢。

特殊数据

有些特殊的数据需要用一些数据类型专门存储,例如存储 IP 地址应该用无符号整数,因为 IP 地址本质上是 32 位无符号数,并不是字符串,用小数点将地址分成四段只是方便阅读。

总结

想要提高 MySQL 的效率,可以做的功课非常多,数据库的数据类型优化也只是其中很小的一点,本文也只是挑出了常用的数据类型进行介绍。有兴趣的可以仔细阅读《高性能 MySQL》这本书,你可以在微信公众号「01 二进制」后台回复「高性能 MySQL」获取本书。

大道至简,尽可能将事情保持简单总是好的,MySQL 喜欢简单,希望使用数据库的你也会喜欢简单。

本文分享自微信公众号 - 01二进制(gh_d1999add1857),作者:雇个城管打天下

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-01-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 利用flutter_downloader插件在Flutter中实现文件下载

    之前有做一个工具集的微信小程序「开挂Lite」,但是由于小程序自身限制,没有办法实现下载文件的功能,只能把下载链接解析出来。而且受限于微信平台,小程序的审核是一...

    出其东门
  • Leetcode题解——22 括号生成

    首先我们需要明白的一点是,在此题中,动态规划的思想类似于数学归纳法,当知道所有 i<n 的情况时,我们可以通过某种算法算出="" i="n" 的情况。="" 本...

    出其东门
  • 防止 Git 泄漏的 5 种最佳做法

    之前看过几个新闻,说是因为程序员的疏忽,将公司服务器的密钥上传到 GitHub 上,导致公司数据丢失,造成了很严重的影响,恰巧最近看到一篇英文博客有介绍如何防止...

    出其东门
  • Android 和 Webview 如何相互 sayHello(一)

    在移动时代 Web 的开发方式逐渐从 PC 适配时代转向 Hybird 的 Webview。以前,我们只需要了解一下 PC Chrome 提供的几个操作行为,比...

    villainhr
  • java学习笔记(基础篇)—==与equals的区别

    ​ 用于基本数据类型相互比较. 比较二者的值是否相等 ​ 用于引用数据类型相互比较. 比较二者地址是否相等 ​ 不能用于基本数据类型与引用型比较

    chlinlearn
  • 重磅!中国量子计算机诞生,创世界纪录

    镁客网
  • 关于图片或者文件在数据库的存储方式归纳

    http://www.cnblogs.com/wangtao_20/p/3440570.html

    bear_fish
  • 通过欧拉计划学Rust编程(第73题)

    由于研究Libra等数字货币编程技术的需要,学习了一段时间的Rust编程,一不小心刷题上瘾。

    申龙斌
  • day3 python函数

    自定义函数: def myfun(args1,args2): 函数体 return value

    py3study
  • 『开发技术』Python中文分词工具SnowNLP教程

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    小宋是呢

扫码关注云+社区

领取腾讯云代金券