2020 年 6 月 22 日 Schema 与数据类型优化
良好的逻辑设计和物理设计师高性能的基石，应该根据系统将要执行的查询语句来设计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设计中的陷阱
太多的列：从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。
太多的关联：当个查询，最好在12个表以内做关联
使用过多的枚举
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设计应该保持：
尽量避免过度设计
使用小而简单的合适数据类型，除非真实数据需要，尽可能的避免使用NULL
尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中出现的列
注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存
尽量使用整型定义标识列
小心使用ENUM和SET
还未添加个人签名 2020.05.18 加入
还未添加个人简介
评论