MySQL 之 Schema与数据类型优化

Schema与数据类型优化

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计SCHEMA，这往往需要权衡各种因素。如：反范式设计可以加快某些类型的查询，但同时可能会使另一些查询变慢和占用更多的存储空间。可能会导致表的维护成本提高。MYSQL独有的特性和实现细节对性能的影响也很大。

选择优化的数据类型MYSQL的数据类型非常多，选择正确的数据类型对获得高性能至关重要。不管存储哪种数据类型，以下几个简单原则有助于做楚更好的选择：

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

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

尽量避免NULL:
通常情况下最好指定列为NOT NULL，除非整的需要存储NULL的值；如果查询中包含可为NULL的列，对MySQL来说更难优化，因为NULL的列使得索引、索引统计和值都比较复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理；如果计划在列上建立索引，就应该尽量避免设计成可为NULL的列。

整数类型

有两种类型的数字：整数和实数；可以使用：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT等几种整数类型。整数类型可选择UNSIGNED属性，表示不允许为负值。MySQL可以为整数类型指定宽度，但对大多数应用来说是无意义的，对于存储和计算来说，int(1)和int(20)是相同的。

实数类型

实数是带有小数部分的数字，DECIMAL类型用存储精确的小数。

字符串类型

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

VARCHAR：
该类型用于存储可变长字符串，它比定长类型更节省空间，varchar需要使用1或2个额外字节记录字符串的长度。

CHAR：
该类型是定长的，它适合存储很短的字符串或所有值都接近同一个长度的字符串，对于经常变更的数据，CHAR比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。

BLOG和TEXT类型：
BLOG和TEXT都是为了存储很大的数据而设计的字符串数据类型，BLOG采用二进制方式存储，没有排序规则或字符集；TEXT采用字符串方式存储，它有字符集和排序规则。

枚举(EMUN)类型:
有时候可以使用枚举列表代替常用的字符串类型，枚举可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，它会根据列表值的数量压缩到一个或两个字节中。

时间和日期类型

DATETIME：
这个类型能保存大范围的值，精度为秒，使用8个字节的存储空间。

TIMESTAMP：
它和UNIX时间戳相同，只能表示从1970年到2038年，使用4个字节的存储空间。

MySQL schema设计中的陷阱

虽然有一些普遍的好的坏的设计原则，但有一些问题是由MySQL的实现机制导致的，意味着有可能犯一些只在MySQL下发生的特定错误。以下这些有可能会帮助避免这些错误：

太多的列

太多的关联：
MySQL限制了每个关联最多只能有61张表，如果希望查询执行得快且并发性好，单个查询最好在12个表内做关联（个人建议最好控制在5 个表内做关联）

全能的枚举

变相的枚举

非此发明的NULL

范式和反范式

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

范式化的优点与缺点

范式化带来的好处：

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

范式化设计的缺点是通常需要关联，稍微复杂的一些查询语句在符合范式化的schema上可能需要至少一次关联，也许更多；这使得代价昂贵，也可能使一些索引策略无效。

反范式化的优点和缺点

优点：

• 反范式化设计的schema所有数据都在一张表中，可以很好地避免关联
• 单独的表可以使用更有效的索引策略。

反范式化的缺点：
表格内冗余数据比较多，在进行数据删除时可能会导致一些重要信息或有用信息丢失。

建议：
范式化和反范式化的schema各有优劣，在实际应用中经常需要混用。范式是好的，但反范式（大多数情况下会重复数据）有时是必须的，并且能带来好处

加快ALTER TABLE操作速度

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

只修改 .frm 文件：

• 创建一张有相同结构的空表，并进行所需要的修改
• 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开
• 交换 .frm 文件
• 执行UNLOCK TABLES 来释放第二步的读锁

快速创建MyISAM索引：

（未完待续）