Schema 与数据类型优化--读书笔记

MySQL

1、择优数据类型原则

更小的通常更好

更小的数据类型通常更快，因为她们占用更少的磁盘，内存和CPU缓存，处理需要的cpu周期也更少

简单就好

简单的数据类型操作通常需要更少的cpu周期

尽量避免NULL

• 可为NULL是列的默认属性，查询中包含可为null的列，对Mysql来说难优化，也使得索引，索引统计和值比较都更复杂，可为NULL的列会使用更多的存储空间，当可为NULL的列被索引时，每个索引需要一个额外的字节。
• 如果计划在列上创建索引，应尽量避免设计为可为NULL的列。

example

• DATETIME和TIMESTAMP列都可以存储相同类型的数据：时间和日期，精确到秒，然而TIMESTAMP只使用DATETIME一半的存储空间，而且会根据时区变化，具有特殊的自动更新的能力。但另一方面TIMESTAMP允许的时间范围要小的多。

2、整数类型和实数类型

整数类型

• TINYINT, SMALLINT,MEDIUMINT, INT, BIGINT,分别使用8、16、24、32、64位存储空间，存储的值的范围是 -2(n-1) 到 2(n-1) -1，n是存储空间的位数。
• 整数类型有可选的UNSINGED属性，表示允许负值，大致使正数的上线提高一倍。
• Mysql可以为整数类型指定宽度，对大多数应用是没有意义的，他不会限制值的合法范围，只是规定mysql交互工具显示字符的个数。
• 有符号和无符号类型使用相同的存储空间。

实数类型

• 实数是带有小数部分的数字，支持精确类型和不精确类型
• float和double支持标准的浮点运算进行近似运算，decimal支持精确计算，但cpu不支持decimal计算，是由mysql自身实现的，cpu支持原生浮点运算，所以浮点运算明显更快。
• decimal只是一种存储格式，在计算中decimal会转换为double类型。
• 浮点类型存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节，double使用8个字节,mysql使用double作为内部浮点计算类型.

3、字符串类型

varchar类型

• varchar类型用于存储可变长字符串，比定长更省空间，因为它仅使用必要的空间。当mysql表使用ROW_FORMAT=FIXED创建，每一行都是定长。
• varchar需要1或2个额外的字节记录字符串的长度，列的最大长度<=255个字节，使用1个字节,否则使用2个。 Example: varchar(100) 需要101个字节存储， varchar(300)需要302个字节存储空间。
• varchar节省了存储空间，对性能有帮助，但由于行是变长的，在update时可能使行变得比原来长，这就导致要做额外的工作。
• varchar合适：字符串列最大长度比平均长度大很多; 列的更新很少，避免碎片化;使用了复杂的字符集，每个字符都使用不同的字节数存储;

char类型

• char类型是定长的: char的值会根据需要采用空格进行补充方便比较.
• 当存储char的值时，mysql
• char 适合存储很短的字符串，或者所有值都接近同一个长度。 比如密码MD5的值
• 对于经常变更的数据char比varchar更好，因为char类型不易产生碎片。
• 对于非常短的列,char比varchar在存储空间上更有效率; 比如: char(1)来存储只需要一个字节，但是varchar(1)却需要两个字节，还有一个记录长度的额外字节。

binary 和varbinary

• 二进制字符串存储的是字节码而不是字符
• Mysql比较Binary字符串，每次按照一个字节，并且根据该字节的数值进行比较，因此二进制比较比字符比较简单而且快。

BLOB和TEXT类型

• BLOB和TEXT都是为了存储很大的数据设计的字符串类型，分别用二进制和字符存储。
• MySQL把每个BLOB和TEXT值当作一个独立的对象处理.当值太大时，InnoDB会使用专门的外部存储区域存储，此时每个值在行内需要1～4个字节存储一个指针，然后在外部存储实际的值。
• BLOB和TEXT的区别是BLOB存储的是二进制数据，没有排序规则和字符集，而TEXT类型有。
• MySQL不能把BLOB和TEXT全部长度的字符串进行索引，也不能使用这些索引消除排序。

4、枚举(ENUM)类型

• 枚举列可以把一些不重复的字符串存储成一个预定义的集合。
• MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。
• MySQL在内部会把每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串"的映射关系。
• 枚举字段是按照内部存储的整数而非定义字符串进行排序的,一种绕过限制的方式是按照需要的顺序定义枚举列，在查询中使用FIELD()函数显式指定排序顺序，但这会导致MySQL无法使用索引消除排序.
• 枚举最不好的地方是字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。
• 特定情况下，把CHAR/VARCHAR列与枚举列进行关联会比直接关联CHAR/VARCHAR列更慢。
• 转换列为枚举类型，根据show table status命令输出结果中Data_length列的值，把这两列转换为枚举可以表的大小缩小.

5、日期和时间类型

DATETIME

• 这个类型能保存大范围的值,从1001到9999年,精度为秒.他把日期和时间封装到YYYYMMDDHHMMSS的整数中,与时区无关.
• 使用8个字节的存储空间.
• 默认情况下，MySQL以一种可排序的，无歧义的格式显示DATETIME的值.

TIMESTAMP

• TIMESTAMP保存从1970年1月1日以来的秒数，它和UNIX时间戳相同.
• TIMESTAMP使用4个字节的存储空间.
• 范围比较少，表示1970~2038年的时间.
• FROM_UNIXTIME()把UNIX时间戳转换为日期, UNIX_TIMESTAMP()日期转为UNIX时间戳.
• TIMESTAMP显示的值依赖于时区.如果在多个时区存储访问数据TIMESTAMP和DATETIME的行为很不一样,前者提供的值与时区有关，后者保留文本表示的日期和时间。
• 可以配置任何TIMESTAMP列的插入和更新行为，TIMESTAMP列默认为NOT NULL.
• 一般尽量使用TIMESTAMP，因为它比DATETIME空间效率更高.
• 用整数存储Unix时间戳不会带来任何收益，而且不方便处理，一般不建议这么做。

6、位数据类型

BIT

• MySQL把BIT当作字符串类型,而不是数字类型.
• 当检索BIT(1)的值时，结果是包含二进制的0或1的字符串，而不是ASCII码的0或1，然而在数字上下文的场景中检索，结果将是位字符串转换为数字。
• 如果想再一个bit的存储空间中存储一个ture/false的值，另一个方法创建一个可以为空的char(0)的列，可以保存空值(NULL)或者长度为0的字符串(空字符串);

SET

• 如果需要保存很多true/false的值，可以考虑合并这些列到一个set数据类型,它在MySQL内部以一系列打包的位集合来表示，可以有效的利用存储空间,而且MySQL有像FIND_IN_SET() 和 FILED() 这样的函数，方便在查询中使用.
• 缺点是改变列价值比较高，需要alter table,一般来说，无法在set列上通过索引查找。
• 整数位上进行按位操作,使用一个整数包装一系列的位。

7、选择标识符

• 为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型.
• MySQL在内部使用整数存储ENUM和SET类型，在做比较操作时转换为字符串.
• 一旦选定一种类型，要确保所有关联表中都使用同样的类型，，混用不同的数据类型可能会导致性能问题。
• 在可以满足值的范围需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。
• 整数通常是标识列最好的选择，因为他们很快而且可以使用AUTO_INCREMENT.
• 对于标识列来说，应尽量避免使用ENUN和SET类型，它们适合存储固定信息.
• 如果可能，应该尽量避免使用字符串类型作为标识列，因为他们很耗空间，并且通常比数字类型慢。
• 尤其是MyISAM表中使用字符串作为标识列，MyISAM默认对字符串使用压缩索引，会导致查询慢很多。
• 如果使用InnoDB存储引擎，不能在数据类型不完全匹配的情况下创建外键，否则报错。
• 由MD5(),SHA1()等产生的随机字符串会任意分布在很大的空间内，会导致insert和一些select语句变得很慢.
• 因为插入值会随机写到索引不同位置，所以使得insert语句很慢，而且会导致页分裂，磁盘随机访问，对于聚簇存储引擎产生索引碎片。
• select语句变得更慢是因为逻辑上相邻的行分布在磁盘和内存的不同地方。
• 随机值会导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。
• IPv4地址实际上是32为无符号的整数，不是字符串，用小数点将地址分成四段的表示方法只是为了阅读方便,所以应该用无符号地址存储IP地址,这样可以节省空间, MySQL提供 INET_ATON() 和 INET_NTOA() 函数进行转换。

8、MySQL schema设计陷阱

• MySQL的存储引擎API工作时需要在服务器层和存储引擎层通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列.
• 从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构与服务器层行结构正好匹配，无需转换; 然而，MyISAM变长行结构和InnoDB的行结构则总需要转换，转换代价依赖于列的数量。
• MySQL限制每个关联操作最多只能有61张表，一个粗略的经验法则，如果希望查询执行的快速且并发性好，单个查询最好在12个表以内做关联。
• 可以配置MySQL的SQL_MODE来禁止不可能的日期，对于新应用这是个非常好的时间经验，它不会让创建的数据库里充满不可能的值。

9、范式和反范式

范式

• 范式化的更新操作通常比反范式要快
• 数据较好的范式化时，就只有很少or没有重复数据，所以只需要修改更少的数据
• 范式化的表通常更小，可以更好的放在内存，所以执行操作会更快
• 很少多余的数据，所以检索列表数据时更少需要使用distinct或者group by数据;
• 范式化设计的schema缺点是通常需要关联。代价昂贵，可能使一些索引策略无效。

反范式

• 反范式化的schema所有数据都在一张表中，可以很好避免关联;
• 当数据比内存大时可能比关联查询要快，因为这样避免了随机I/O;
• 单独的表也能使用更有效的索引策略。

混用

• 最常见的反范式化数据的方法是复制或着缓存，在不同的表中存储相同的特定列。
• 另一个从父表冗余一些数据到子表的理由是排序的需要。
• 缓存衍生值，比如要显示每个用户发了多少消息，可以在user表添加num字段，当用户发送新消息时更新这个字段；

10、缓存表和汇总表

• 有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。
• 缓存表 表示存储那些可以比较简单的从schema其他表获取数据的表(比如逻辑上的冗余数据)
• 汇总表 保存的是使用group by语句聚合数据的表[数据不是逻辑冗余的]
• 缓存表对优化搜索和检索查询语句很有效，这些查询语句经常需要特殊的表和索引结构；
• 缓存表可以使用不同的存储引擎，比如主表是InnoDB，用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，并且可以全文索引。

物化视图

MySQL并不原生支持物化视图，使用开源工具Flexviews实现物化视图.由如下部分组成

• 变更数据抓取功能,读取服务器的二进制日志并解析相关行的变更
• 一系列帮助创建和管理视图的定义的存储过程
• 一些可以应用变更到数据库中的物化视图的工具

• 计算增量数据比从源表中读取数据的效率要高的多

计数器表

• 应用在表中保存计数器，则可能在更新计数器的时候碰到并发的问题。
• 创建一张独立的表存储计数器通常是个好主意，这样可以使计数器小且快，并且使用独立的表可以帮助避免查询缓存失效。
• 要获得高并发的更新性能，可以吧计数器存在多行中，每次随机更新一行，比如预先增加100行数据，每次选择一个随机槽(slot)更新，使用sum获取统计结果

11、加快ALTER TABLE操作的速度

• MySQL的alter table操作的性能对大表来说是个大问题。
• MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表查处所有数据插入新表，然后删除旧表。
• 一般而言，大部分的alter table操作将导致MySQL服务中断。
• 先在不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换
• 影子拷贝的技巧用要求的表结构创建一张和源表无关的新标，然后通过重命名和删表操作交换两张表。
• alter table 的alter column，modify column，change column语句修改列操作是不一样的。

快速创建MyISAM索引

• 为了高效的载入数据到MyISAM表中，一般是先禁用索引，载入数据，再重启索引，这是因为构建索引的工作被延迟到数据完全载入以后，这个时候意境可以通过排序来构建索引，这样做就快很多，而且使得索引数的碎片更少，更紧凑。

mysql> alter table load_data disable keys;
--load data
mysql> alter table load_data enable keys;

• 然而这个方法对唯一索引无效,因为disable keys只对非唯一索引有效。
• MyISAM在内存中构造唯一索引，并为载入的每一行检查唯一性，一旦索引大小超过内存，载入操作会变得越来越慢。

12、总结

良好的schema设计是普遍适用的，MySQL有他自己的实现细节要注意，尽可能保持任何东西小而简单总是好的。

• 尽量避免过度设计，比如会导致复杂查询schema设计or很多列的表设计
• 使用小而简单的合适数据类型，除非真实数据模型需要，否则尽量避免使用NULL值
• 尽量使用相同数据类型存储相似or相关的值，尤其是关联查询使用列
• 注意可变长字符串, 他在临时表or排序时可能导致按最大长度分配内存
• 尽量使用整型定义标识列
• 避免使用MySQL已经遗弃的特性，例如指定浮点数精度，显示整数宽度
• 小心使用ENUM和SET，尽量避免使用BIT
• 范式是好的，但反范式有时也是必需的，比如数据排序，分组，统计时