读书笔记2—高性能MySQL（第3版）—第4章—Schema与数据类型优化

1、选择优化的数据类型

1.1、更小的通常的更好

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

1.2、简单就好

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

1.3、尽量避免NULL

很多表都包含可为NULL（空值）的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好制定列为NOT NULL，除非真的需要存储NULL值。

2、数据类型

2.1、整数类型

有两种类型的数字：整数（whole number）和实数（real number）。如果存储整数，可以使用这几种整数类型：TINYINT，SAMLLINT，MEDIUMINT，INT，BIGINT。分别使用8,16,24,32,64位存储空间。

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255，而TINYINT的存储范围是-128~127。

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

2.2、实数类型

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

2.3、VARCHAR类型

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。

2.4、CHAR类型

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格（在MySQL4.1和更老版本中VARCHAR也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区别只是在存储格式上）。CHAR值会根据需要采用空格进行填充以方便比较。

2.5、BLOB和TEXT类型

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

2.6、DATETIME类型

这个类型能保存大范围的值，从1001到9999年，精度为妙。它把日期和实践封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字符的存储空间。默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值，例如“200-01-16 22：37:08”。这是ANIS标准定义的日期和时间表示方法。

2.7、TIMESTAMP类型

TIMESTAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字符的存储空间，因此它的范围比DATETIME小得多：只能表示从1970年到2038年。MySQL提供了FROM_UNIXTIME（）函数把UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP（）函数把日期转换为UNIX时间戳。

3、MySQL schema设计中的陷阱

• 太多的列
• 太多的关联
• 全能的枚举
• 变相的枚举

4、范式

4.1、范式的优点和缺点

• 范式化的更新操作通常比反范式化要快
• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快
• 很少有多余的数据，意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句

4.2、反范式的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联

如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O。

5、缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然后，有时也需要创建一张完全独立的汇总表或缓存表（特别是为满足检索的需求时）。如果能容许少量的脏数据，这是非常好的方法，但是有时确实没有选择的余地（例如，需要避免负责、昂贵的实时更新操作）。

6、物化视图

许多数据库管理系统（例如Oracle或者微软SQL Server）都提供了一个被称作物化视图的功能。物化视图实际上是预先计算并且存储在磁盘上的表，可通过各种各样的策略刷新和更新。MySQL并不原生支持物化视图。然而，使用Justin Swanhart的开源工具Flexviews，也可以自己实现物化视图。Flexviews比完全自己实现的解决方案更要惊喜，并且提供了很多不错的功能使得可以更简单地创建和维护物化视图。它由下面这些部分组成：

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

7、计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快，还可以帮助避免查询缓存失效。

8、加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用心的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。对以上情况一般有三种方法：

• 先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换
• “影子拷贝”：用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表
• 只修改 .frm文件

9、总结

良好的schema设计原则是普遍使用的，但MySQL有它自己的实现细节要注意。概括来说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人应该也同样会喜欢简单地原则：

• 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值
• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存
• 尽量使用整型定义标识列
• 避免使用MySQL已经遗弃的特性，例如制定浮点数的精度，或者整数的显示宽度。
• 小心使用ENUM和SET，虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱，最好避免使用BIT
• 范式是好的，但是反范式有时也是必须的，并且能带来好处