Schema与数据类型优化
发布日期:2021-10-25 03:44:49 浏览次数:0 分类:技术文章

良好的逻辑设计和物理设计是高性能的基石,应该根据系统将要执行的查询数据来设计schema,这往往需要权衡各种因素。

MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。

  • 更小的通常更好 一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更少的磁盘,内存和缓存。并且处理时需要的CPU周期也更少。
  • 简单就好 简单数据类型的操作通常需要更少的CPU周期。(应该使用MySQL内建的类型而不是字符串来存储日期和时间,应该用整型来存储IP地址)
  • 尽量避免NULL 如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更加复杂。可为NULL的列会使用更多的存储空间,在MySQL里需要特殊处理。通常把NULL列改为NOT NULL带来的性能提升比较小,所以没有必要首先在现有Schema中查找并修改掉这种情况,除非确定会导致问题。但是如果计划在列上建立索引,就应该尽量避免设计成可为NULL的列。

在为列选择数据类型时,第一步需要确定合适的大类型。下一步是选择具体类型,很多MySQL的数据类型可以存储相同类型的数据,只是存储的长度和范围不一样、允许的精度不同,或者需要的物理空间不同。
例如DATETIME和TIMESTAMP列都可以存储相同类型的数据:时间和日期,精确到秒。但是TIMESTAMP只是用DATETIME一般的存储空间,并且会根据时区进行变化,具有特殊的自动更新能力。但是TIMESTAMP允许的时间范围要小得多。

MySQL为了兼容性支持很多数据类型的别名。

MySQL中有两种类型的数字:整数实数
如果存储整数,可以使用:TINYINT SMALLINT MEDIUMINT INT BIGINT,分别对应8,16,24,32,64位存储空间。 整数类型可以携带UNSIGNED属性,有符号数和无符号数使用相同的存储空间,并且有相同的性能。
MySQL既支持精确类型,也支持不精确类型。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数。由于CPU不支持对DECIMAL的直接计算,MySQL服务器自身实现了DECIMAL的高精度计算。
浮点和DECIMAL类型都可以指定精度。对于DEMICAL列,可以指定小数点前后所允许的最大位数。但会影响列的空间消耗。MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每四个字节存九个数字,小数点本身占一个字节)。
MySQL5.0和更高版本中的DECIMAL类型允许最多65个数字。

字符串

MySQL支持多种字符串类型,每种类型还有很多变种。

VARCHAR和CHAR类型是两种最主要的字符串类型,根据存储引擎的具体实现,这两种字符串类型在磁盘中和内存中的存储方式也不同。

VARCHAR类型用于存储可变长度字符串,是一种最常见的字符串数据类型。它比定长类型更节省空间,因为它仅仅使用必要的空间(但如果MySQL表使用ROW_FORMAT=FIXED的话,每一行都定长,很浪费空间),VARCHAR需要使用1到2个额外字节记录字符串长度,如果列的最大长度小虎或等于255,则使用1字节来表示,否则使用2字节来表示。
VARCHAR节省了存储空间,所以对性能有一定的帮助。但是,由于行是变长的,在UPDATE时可能使得行变得比原来长,这就导致需要额外的工作。如果一个行占用的空间增长,并且在页内没有更多的存储空间,在这种情况下,不同的存储引擎的处理方式是不一样的。MyISAM会将行拆成不同的片段存储,InnoDB则需要分裂页来使行可以放进页内。
下面情况下使用VARCHAR是合适的:字符串列的最大长度比平均长度大很多;列更新的很少(碎片不是问题);使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。

最好的策略是只分配真正需要的空间。

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。实际上是隶属于两个完全不同的数据类型。MYSQL会将此二者特殊处理,InnoDB会使用专门的外部存储区域来进行存储,Memory引擎不支持Blob和Text类型,所以,如果使用Blob或者Text并且需要使用隐式临时表,将不得不使用MyISAM磁盘临时表。有一个技巧:在所有用到Blob字段的地方都使用SubString(column, length)将其转化为字符串,这样就可以使用内存表了。

当一些字符串固定出现,可以将这些字符串统一替换为枚举类型,MySQL在存储枚举类型会非常紧凑,其内部将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。但在扩充枚举类型的可选值的时候,则需要进行Alter Table。

日期和时间

MySQL能存储的最小时间单位粒度为秒(但可以使用微秒级粒度进行临时运算)

MySQL提供两种日期类型:DATETIME和TIMESTAMP。

DATETIME

该类型的保存范围为1001年到9999年,精度为秒,它将日期和时间封装为YYYYMMDDHHMMSS的整数中,与时区无关,使用8字节存储。

TIMESTAMP

该类型保存从1970年1月1日起(格林尼治时间)以来的秒数(UNIX时间戳)TIMESTAMP只使用了4字节来进行存储,只能保存19700年到2038年之间的时间。
MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期,同样地,提供了UNIX_TIMESTAMP()函数将日期转换为时间戳。 TIMESTAMP显示的值依赖于时区。

TIMESTAMP和DATETIME没有特殊属性,默认情况下,插入的TIMESTAMP的值为当前时间。

位数据

MySQL有少数集中存储类型使用紧凑的位存储数据。这些位类型,不管底层存储格式和处理方式如何,从技术上来说都是字符串类型。

BIT

在MySQL5.0以前,BIT和TINYINT是同义词,5.0以后则是两个完全不同的类型。可以在BIT列存储一个或多个TRUE/FALSE值。BIT列的最大长度为64位。
BIT存储因存储引擎而异。MyISAM会将打包存储所有的BIT列,因此占用的空间较小,而InnoDB和Memory则将每个BIT以最小整数存储。
MySQL会把BIT当成字符串类型,而不是数字类型,其结果是包含二进制0或者1的字符串,而不是ASCII码‘0’和‘1’。 检索BIT列,会根据检索环境动态地将结果转换为字符串类型或者数字类型。

如果想在一个BIT的存储空间存储一个TRUE/FALSE值,另一个方法是创建一个可以为空的CHAR(0)列,该列可以保存NULL或者长度为0的字符串。

SET

如果需要保存多个TRUE/FALSE,可以考虑合并这些列到SET数据类型,它在MySQL内部是以一系列到爆的位的集合来表示的。其主要的缺点是改变列的定义代价较高(ALTER TABLE)。而且也无法在SET列上进行检索查询。

标识符

标识列在查询过程中是非常有用的。当选择标识列的类型时,不仅仅需要考虑存储类型,还需要考虑MySQL对这种类型怎么执行计算和比较。例如MySQL在内部使用整数存储ENUM和SET,然后在比较的时候转换为字符串。

其中,整数类型是标识符的最好的选择,而ENUM和SET就是一种非常糟糕的选择。ENUM和SET列适合存储固定信息。 如果可能,尽量避免使用字符串类型作为标识列,因为它非常消耗空间,比数字类型慢。

对于完全随机的字符串也需要注意,如MD5,SHA1,或者UUID产生的字符串,这些函数生成的新值会任意分布在很大的空间内,这样会导致SELECT或者INSERT语句很慢。

MySQL schema设计中的陷阱

  1. 太多的列。MySQL存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后uzai服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。
  2. 太多的关联。MySQL限制了每个关联操作最多只能61个关联表,如果希望查询执行得快,并且并发性好,单个查询最好在12个表以内做关联。
  3. 枚举
  4. NULL。不要过分遵循“不适用NULL”的准则。

转载于:https://www.cnblogs.com/gscienty/p/6567352.html

上一篇:关于this的用法,网上看到一个非常好的判断方法。
下一篇:窗体美化

关于作者

    白红宇是个全栈工程师,前端vue,小程序,app开发到后端框架设计,数据库设计,环境部署上线运维。

最新文章