2020 年 6 月 22 日 Schema 与数据类型优化

发布于: 2020 年 06 月 22 日

良好的逻辑设计和物理设计师高性能的基石,应该根据系统将要执行的查询语句来设计schema。

本章内容主要是为接下来的两个章节做铺垫。在这三章中,我们将讨论逻辑设计,物理设计和查询执行,以及它们之间的相互作用

1、选择优化的数据类型

通常在选择数据类型的时候,应该遵循以下几个原则:

1、更小的通常更好,一般情况下,应该尽量使用可以正确存储数据的最小数据类型。

2、简单就好,简单数据类型的操作通常需要更少的CPU周期

3、尽量避免NULL。

4、在为列选择数据类型的时候,第一步需要确定合适的大类型:数字,字符串,时间等。下一步是选择具体类型。

1、整数类型

有两种类型的数字:整数和实数。如果存储整数,可以使用这几种整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。

有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。

2、实数类型

实数是带有小数部分的数字,然而它们不只是为了存储小数部分;也可以使用DECIMAL存储比BIGINT还大的整数。MySQL支持精确类型,也支持不精确类型。

Float和Double类型支持使用标准的浮点型运算进行近似计算

DECIMAL类型用于存储精确的小数。

浮点类型在存储同样范围的值时,通常比Decimal使用更少的空间,FLoat使用4个字节存储,DOUBLE占用8个字节

3、字符串类型

VARCHAR和CHAR是两种最主要的字符串类型。

VARCHAR类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型更节省空间,因为它仅使用必要的空间。Innodb则更灵活,它可以把过长的VARCHAR存储为BLOB。

CHAR类型是定长的:MySQL总是根据定义的长度分配足够的空间。当存储CHAR值时,MySQL会删除所有的末尾空格,CHAR会根据需要采用空格进行填充以方便比较。

比较:对于经常变更的数据,CHAR比VARCHAR更好,因为定长的CHAR不容易产生碎片,CHAR比VARCHAR在存储空间上也更有效率。

BLOB和TEXT:

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。

实际上他们分别属于不同的数据类型家族:

字符类型是:TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT

对应的二进制类型:TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。

使用枚举代替字符串类型

枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在内部会将每个值在列表中存储为整数,并且在表的.frm文件中保存“数字-字符串”映射关系的查找表。

注意:枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。

枚举最不好的地方时是,字符串列表是固定的,添加或删除字符串必须使用ALTER TABLE

4、日期和时间类型

MySQL提供了两种相似的日期类型:DATETIME和TIMESTAMP

DATETIME:

这个类型能保存大范围的值,从1001年到9999年,精度为秒,他把日期和时间封装到格式为“YYYYMMDDHHMMSS”的整数中,与时区无关,使用8个字节存储空间

TIMESTAMP:

TIMESTAMP类型保存了从1971年1月1日午夜以来的秒数,它和UNIX时间戳相同,TIMESTAMP只使用了4个字节的存储空间

TIMESTAMP的值是依赖时区的,MYSQL服务器、操作系统,以及客户端连接都有时区设置

5、位数据类型

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

BIT:

可以使用BIT列在一列中存储一个或多个true/false值.

BIT(1)定义一个包含单位的字段,BIT(2)存储2个位,依此类推。BIT列的最大长度是64个位

SET:

如果需要保存很多true/false值,可以考虑合并这些列到一个SET数据类型,它在MySQL内部是以一系列打包的位的集合来表示的。

6、选择标识符

一般来说更有可能用标识列与其他值进行比较,或者通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用,所以为标识列选择数据类型时,应该选择跟关联表中的对应列一样的数据类型。

当选择标识列的类型时,不仅仅需要考虑存储类型,还需要考虑MySQL对这种类型怎么执行计算和比较。

混用不同的数据类型可能导致性能问题,即使没有性能影响,在比较操作时隐式类型转换也可能导致很难发现的错误。

常见的小技巧:

整数类型

整数通常是标识列最好的选择,因为它们可以很快,并且可以使用AUTO_INCREAMENT

ENUM和SET类型:

对于标识列来说,ENUM和SET类型通常是一个糟糕的选择,尽量不要使用ENUM和SET类型

字符串类型

如果可能尽量避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢。

7、特殊类型数据

人们经常用VarChar(15)来存储IP地址,然而他么实际上是32位无符号整数,不是字符串。应该用无符号整数存储IP地址。MySQL提供了INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

2、MySQL schema设计中的陷阱

  1. 太多的列:从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。

  2. 太多的关联:当个查询,最好在12个表以内做关联

  3. 使用过多的枚举

3、范式和反范式

在范式化的数据库中,每个事实数据会出现并且只出现一次。相反,在反范式化的数据库中,信息是冗余的,可能存储在多个地方

1、范式的优点和缺点

优点:

  • 范式化的更新操作通常比反范式化要快

  • 很少的数据重复

  • 范式化的表更小,可以更好的放在内存里,所以执行操作快

缺点;

  • 通常需要关联

  • 可能使索引策略无效

2、反范式的优点和缺点

优点:

  • 因为所有的数据都在一张表中,可以很好的避免关联

缺点:存在重复数据

3、混用范式化和反范式化

最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同特定的列。

反范式化的典型特征就是空间换时间

4、缓存表和汇总表

有时提升性能最好的方法就是在同一张表中保存衍生的冗余数据。然而有时也需要创建一张完全独立的汇总表或缓存表

缓存表:表示存储那些可以比较简单的从schema其他表获取数据的表

汇总表:保存的是GROUP BY语句聚合数据 的表

在使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建。哪个更好的依赖于程序,但是定期重建并不只是节省资源,也可以保持表不会有很多碎片。

1、物化视图

(了解即可)物化视图实际上是预先计算并且存储在磁盘上的表,可以通过各种各样的策略刷新和更新

5、加快ALTER TABLE 操作的速度

MySQL中的Alter TABLE操作的性能对大表来说是一个大问题。

MySQL执行大部分修改表结构操作的方法是用一个新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表。

对于常见的场景,使用的技巧有两种:一种是先在一台不提供服务的机器上执行ALTER TABLE操作,然后和提供服务的主库进行切换 。另一种是创建一张和源表无关的新表,然后通过重命名和删除操作交换两张表

1、只修改.frm文件

不是所有的Alter Table语句都会引起表重建

下面这些操作有可能不需要重建表:

1、移除(不是增加)一个列的AUTO_INCREMENT属性

2、增加、移除、或更改ENUM和SET常量。

基本的技术是为想要的表结构创建一个新的.frm文件,然后用它替换掉已经存在的那张表的.frm文件,像下面这样:

1、创建一张具有相同结构的空表,并进行所需要的修改

2、执行FLUSH TABLES WITH READ LOCK,这将会关闭所有正在使用的表,并且禁止任何表被打开

3、交换.frm文件

4、执行UNLOCK TABLE 来释放第2步的读锁。

2、快速创建MyISAM索引

6、总结

良好的schema设计应该保持:

  1. 尽量避免过度设计

  2. 使用小而简单的合适数据类型,除非真实数据需要,尽可能的避免使用NULL

  3. 尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中出现的列

  4. 注意可变长字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存

  5. 尽量使用整型定义标识列

  6. 小心使用ENUM和SET

用户头像

瑞克与莫迪

关注

还未添加个人签名 2020.05.18 加入

还未添加个人简介

评论

发布
暂无评论
2020年6月22日   Schema与数据类型优化