MySQL之Schema与数据类型优化

关注

原创最新推荐文章于 2026-02-07 14:35:05 发布 391 阅读 AI 写同款· GEO 优化 ›

MySQL之Schema设计与数据类型优化：避坑指南与实践大家好！在技术学习和实践的道路上，我一直期望能和大家共同进步，分享经验。今天，咱们聚焦于MySQL的Schema设计与数据类型优化，这两个方面对于构建高效的数据库系统至关重要。希望通过这篇文章，能帮助大家深入理解相关知识，在实际应用中避免常见错误，提升MySQL数据库的性能和稳定性。阅读详情

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema，这往往需要权衡各种因素。如，反范式的设计可以加快某些类型的查询，但同时可能使另一种的查询变慢。比如，添加计数表和汇总表是一种很好的优化查询的方式，但这些表的维护成本可能会很高。MySQL独有的特性和实现细节对性能的影响也很大。

选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对获取高性能至关重要。可以参照以下原则：

更小的通常更好
一般情况下，应该选择可以正确存储的最小数据类型。如只需要存0~200，tinyint unsigned更好。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
但是需要确保没有低估需要存储的值的范围，因为在schema中多个地方增加数据类型的范围时一个非常耗时和痛苦的操作。
简单就好
简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比整型比较更复杂。
应该使用MySQL内建的数据类型来存储日期和时间（如：date、time、datetime）。应该用整型来存储IP地址。
尽量避免NULL
通常情况下最好指定列为NOT NULL，除非确实需要存储NULL值。
如果存储中包含NULL的列，对MySQL来说更难优化，因为NULL的列使得索引、索引统计和值比较都更复杂。NULL的列会使用更多的存储空间，在MySQL中也需要特殊处理。当NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM中可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以调优时没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是如果计划在列上建立索引，则应该尽量避免设计成可为NULL的列。
InnoDB中使用单独的位（bit）来存储NULL值，所以对于稀疏数据（很多值为NULL，只有少数行的列为非NULL值）有很好的空间效率。

数据类型

在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等。下一步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。
例如，DATETIME和TIMESAMP列都可以存储相同类型的数据：时间和日期，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为障碍。
MySQL为了兼容性支持很多别名，例如INTEGER、BOOL，以及NUMERIC。它们都只是别名。这些别名可能令人不解，但不会影响性能。如果建表时采用数据类型的别名，然后用SHOW CREATE TABLE检查，会发现MySQL报告的是基本类型，而不是别名。

整数类型

整数（whole number）和实数（real number）。如果存储整数，可以使用这几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间。它们可以存储的值的范围从−2 （N−1）到2 （N−1） −1，其中N是存储空间的位数。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。
有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。
你的选择决定MySQL是怎么在内存和磁盘中保存数据的。然而，整数计算一般使用64位的BIGINT整数，即使在32位环境也是如此。（一些聚合函数是例外，它们使用DECIMAL或DOUBLE进行计算）。
MySQL可以为整数类型指定宽度，例如INT（11），对大多数应用这是没有意
义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT（1）INT（20）是相同的。

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。浮点运算的实现和具体平台的浮点数的实现有关。
DECIMAL类型用于存储精确的小数。
因为CPU不支持对DECIMAL的直接计算，所以MySQL 5.0以及更高版本中，MySQL服务器自身实现DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。
浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL 5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。MySQL 5.0和更高版本中的DECIMAL类型允许最多65个数字。
有多种方法可以指定浮点列所需要的精度，这会使得MySQL悄悄选择不同的数据类型，或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类型，不指定精度。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型；MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型

MySQL支持很多的字符串类型，每种类型还有很多变种。从MySQL4.1开始，每个字符串列可以定义自己的字符集和排序规则，或者说校对规则（collation）。
VARCHAR和CHAR是两种最重要的字符串类型。很难精确的解释这些值是怎么存储在磁盘和内存中的，这和存储引擎的具体实现有关。存储引擎存储CHAR或VARCHAR值的方式在内存和磁盘上可能不一样，所有MySQL服务器从存储引擎中读出的值可能需要转换为另一种格式。

VARCHAR

VARCHAR用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（如，越短的字符串使用越少的空间）。但是如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。
VARCHAR使用1或2个额外字节记录字符串的长度：如果列的最大长度<=则使用1个字节表示，否则使用2个字节表示。假如采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间，VARCHAR(1000)的列则需要1002个字节，因为需要2个字节存储长度信息。
VARCHAR节省了存储空间，所有对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且页内没有更多的空间可以存储，这种情况下，不同的存储引擎处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。其他一些存储引擎也许并不在原数据位置更新数据。
以下情况使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所有碎片不是问题；使用了UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
在5.0或更高版本，MySQL在存储或检索时会保留末尾空格。
InnoDB则更灵活，它可以把过长的VARCHAR存储为BLOB。

CHAR

CHAR类型时定长的：MySQL总数根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这个一个定长的值。对应经常变更的数据，CHAR比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对应非常短的类，CHAR比VARCHAR在存储空间上更有效率。例如CHAR(1)用来存储只有Y和N的值，采用单字节字符集（字符串长度定义是字符数，而不是字节数。多字节字符集会需要更多的空间存储单个字符。）只需要一个字节，但是VARCHAR(1)则需要两个字节，因为还有一个用来记录字节长度的额外字节。
数据如果存储取决于存储引擎，并非所有的存储引擎都会按照相同的方式处理定长和变长字符串。不过，填充和截取空格的行为在不同的存储引擎都是一样的，因为这是在MySQL服务器层进行处理的。
与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制存储的是字节码而不是字符。填充也不一样，MySQL填充BINARY采用的是\0（零字节）而不是空格，检索时也不会去掉填充值。（如果在检索时需要保持值不变，需要特别小心BINARY类型，MySQL会用\0将其填充到需要的长度）。用来存储二进制数据，并且希望MySQL使用字节码而不是字符串进行比较。二进制比较的优势不仅仅体现在大小写敏感上。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比字符串比较简单很多，所有也就很快。
最好的策略是只分配真正需要的空间。VARCHAR(5)和VARCHAR(100)存储’hello’的空间开销是一样的，但使用短的列事实证明有很大的优势。更长的列通常会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据设计的字符串数据类型，分别采用二进制和字符串方式存储。
实际上，它们分布属于不同的数据类型家族：字符串类型是TINYTEXT，SMALLTEXT，TEXT，MEDIUTEXT，LONGTEXT；对应的二进制类型是TINYBLOB，SMALLBLOB，BLOB，MEDIUBLOB，LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。
和其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的”外部“存储区域来进行存储，此时需要在行内用1~4个字节存储一个指针，然后在外部存储区域存储实际的值。
BLOB和TEXT家族仅有的不同是BLOB存储的是二进制数据，没有排序规则和字符集，而TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减少max_sort_length的配置。或使用ORDER BY SUBSTRING(column, length)。
MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。
磁盘临时表和文件排序
因为Memory引擎不支持BLOB和TEXT类型，所以，如果查询使用BLOB或TEXT列并且需要使用隐式临时表，将不得不使用MyISAM磁盘临时表，即使只有几行数据也是如此。这会导致严重的性能开销。即使配置MySQL将临时表存储在内存块设备上（RAM Disk）,依然需要很多昂贵的系统调用。

最好的的解决方法是尽量避免使用BLOB和TEXT类型。如果实在无法避免，有一个技巧是在所有用到BLOB字段的地方都使用SUBSTRING(column, length)将列值转换为字符串（在ORDER BY子句中也适用），这样就可以使用内存历史表了。但是要确保截取的子字符串足够短，不会使临时表的大小哦超过max_heap_table_size或tmp_table_size，超过之后MySQL会将内存临时表转换为MyISAM磁盘临时表。

如果EXPLAIN执行计划的Extra列包含”Using temporary“，则说明这个查询使用了隐式临时表。

使用枚举（ENUM）代替字符串类型

有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存”数字-字符串“映射关系的”查找表“。
ENUM实际存储的值为整数，而不是字符串。这是个双重属性。建议尽量不要将数字作为ENUN枚举常量，这种双重属性很容易引起混乱。如ENUM(‘1’,‘2’,‘3’)。
ENUM枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。一种绕过这种限制的方式是按照需要的顺序来定义枚举列。另外也可以在查询中使用FIELD()函数显式地指定排序顺序，但这会导致MySQL无法利用索引消除排序。如果定义时就是按照字母的顺序，就没必要这么做了。
枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。因此，对应一系列未来可能会改变的字符串，使用枚举不是一个好主意。除非能接受只在末尾添加元素，这样在Mysql5.1中就可以不用重建整个表来完成修改。
由于MySQL将枚举值保存为整数，并且必须查找才能转换为字符串，所有枚举列有一些开销。通常枚举的列表比较小。在特定情况下，把CHAR/VARCHAR列与枚举列进行关联可能比直接关联CHAR/VARCHAR列更慢。
通过测试可知，当ENUM和ENUM关联时很快，但将VARCHAR和ENUM关联则变得很慢。这是一个通用的设计实践，在”查找表“时，采用整数主键而避免采用基于字符串的值进行关联。
将VARCHAR列换行为ENUM列还有一个好处。根据SHOW TABLE STATUS命令输出结果中Data_length列的值，把VARCHAR转换为ENUM可以让表的大小缩小1/3。这很可能节省I/O。同样，转换后的主键也只有原来的一般大小了。InnoDB表中，减少主键大小会使非主键索引也变得更小。

MySQL USING用法

mysql> select tb1.* from tb1 inner join tb2 on tb1.id=tb2.id;
等同于
mysql> select tb1.* from tb1 inner join tb2 USING(id);

USING等同于连接中的ON，但a表和b表必须有相同的列。

日期和时间类型

MySQL可以使用很多类型来保存日期和时间，例如YEAR和DATE。MySQL能存储的最小时间粒度为秒（MariaDB支持微妙级别的时间类型）。但是MySQL也可以使用微秒级的粒度进行临时运算。
大部分时间类型都没有替代品，因此没有什么最佳选择的问题。

DATETIME

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

TIMESTAMP

TIMESTAMP类型保存了从1970年1月1日午夜（格里尼治标准时间）以来的秒数，它和UNIX时间戳相同。TIMESTAMP使用4个字节的存储空间，因此它的范围比DATETIME小得多：只能表示从1970年到2038年。
MySQL提过了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提过了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。
TIMESTAMP的存储格式在各个版本都是一样的。MySQL 4.1已经更新的版本按照DATETIME的方式格式化TIMESTAMP的值。
TIMESTAMP显示的值也依赖于时区。MySQL服务器、操作系统、以及客户端连接都有时区设置。如果在多个时区存储和访问数据，TIMESTAMP和DATETIME的行为将很不一样。前者提过的值和时区有关系，厚泽则保留文本表示的日期和时间。
默认情况下，如果插入没有指定第一个TIMESTAMP列的值，MySQL会设置这个列的值为当前时间。在插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值（除非在UPDATE语句中明确指定了值）。TIMESTAMP列默认为NOT NULL。
除了特殊行为之外，通常也该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。有时候人们会将Unix时间截存储为整数值，但这不会带来任何收益。用整数保存时间截的格式通常不方便处理，所以我们不推荐这样做。
如果需要存储比秒更小粒度的日期和时间值怎么办？MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUBLE存储秒之后的小数部分。这两种方式都可以，或者也可以使用MariaDB替代MySQL。

位数据类型

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

BIT

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位，依次类推。BIT列的最大长度是64位。
BIT的行为因存储引擎而异。MyISAM会打包存储所有的BIT列，所有17个单独的BIT列只需要17个位存储（假如没有NULL的列），这样MyISAM只使用3个字节就能存储17个BIT列。其他存储引擎如InnoDB，为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。
MySQL把BIT当作字符串类型，而不是数字类型。当检索BIT(1)的值时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的“0”或“1”。然而，在数字上下文的场景中，结果将是字符串转换成的数字。这相当令人费解，所以我们应该谨慎使用BIT类型。对于大部分应用，最好避免使用这种类型。
如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值NULL或长度为零的字符串（空字符串）。

SET

如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间，并且MySQL有像FIND_IN_SET()和FIELD()这样的函数，可以方便的使用。
它的缺点是改变列的定义的代价较高：需要ALTER TABLE，这对大表来说是非常昂贵的操作。也无法在SET列上通过索引查找。

FIND_IN_SET()

参考：https://www.cnblogs.com/xiaoxi/p/5889486.html

在整数列上按位操作

一种替代SET的方式视使用一个整数包装一系列的位。例如，可以把8个位包装到一个TINYINT中，并且按位操作来使用。

选择标识符（identifier）

为标识列（identifier column）选择合适的数据类型非常重要。一般来说，标识列更可能与其他值进行比较（如在关联操作中），或者通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用。所有在为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型。
在相关的表中使用相同的数据类型是个好主意，因为这些列很可能在关联中使用。
在选择标识列时，不仅仅要考虑存储类型，还需要考虑MySQL怎么对这种类型执行计算和比较。例如，MySQL在内部使用整数存储ENUM和SET类型，然后在比较操作时转换为字符串。
一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。类型直接需要精确匹配，包括向UNSIGNED这样的属性。混用不同数据类型可能会导致性能问题，即使没有性能影响，在比较操作隐式类型转换也可能导致很难发现的错误。
在满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。
下面是一下小技巧

整数类型
整数类型通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。
ENUM和SET类型
对应标识列来说，ENUM和SET通常是一个糟糕的选择。尽管对只包含固定的状态或者类型的“定义表”来说可能是没有问题的。ENUM和SET适合存储固定信息，例如有序的状态、产品类型、人的性别。
字符串类型
应该避免字符串类型作为标识列，因为它们很消耗空间，而且通常比数字类型慢。尤其是在MyISAM表中默认对字符串使用压缩索引，这会导致查询慢的多。

对应完全“随机”的字符串需要多加注意，例如MD5()、SHA1()、或者UUID()产生的字符串。这些函数产生的值会任意分布在很大的空间内，这会导致INSERT及SELECT语句变得很慢（另一方面，对一些有很多写的特别大的表，这种伪随机值实际上可以帮助消除热点）：
- 因为插入值会随机地写到索引的不同位置，所有使得INSERT语句更慢。这回导致页分裂、磁盘随机访问、以及对于聚簇存储引擎产生聚簇索引碎片。
- SELECT语句会变得很慢，因为逻辑上相邻的行会分布到磁盘和内存的不同地方。
- 随机值导致缓存对所有类型的查询语句效果都很差，因为这会使得缓存赖以工作的访问局部性原理失效。
- 如果存储UUID值，应该移除-符号，更好的做法是，用UNDEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。
- UUID()生成的值和SHA1()函数生成的值有不同的特征：UUID值虽然分布不均匀，但还是有一定的顺序。尽管如此，但函数不如递增的整数好用。

当心自动生成的schema

写的很烂的schema迁移程序，或者自动生成schema的程序，都会导致严重的性能问题。
对象关系映射（ORM）系统是另一种常见的性能噩梦。建议在用性能交换开发者的效率之前要仔细考虑，并且总是在真实大小的数据集上做测试，这样就不会太晚才发现性能问题。

特殊的数据类型

IP4地址实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的标识方式只是为了让人们容易阅读。所有应该使用无符号整数存储IP地址。MySQL提过了INET_ATON()和INET_NTOA()函数在IP地址和整数之间转换。

MySQL schema设计中的陷阱

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

太多的列
MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换为行数据结构的代码是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。然而，MyISAM的变长行结构和InnoDB的行结构总是需要转换。转换的代价依赖与列的数量。如果有数千个字段，必须意识到服务器的性能运行特征会有一些不同。
太多的关联
所谓的“实体-属性-值”（EAV）设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱的工作。MySQL限制了每个关联操作最多只能有61张表。但是EAV数据库需要很多自关联。一个粗略的经验法则是，如果希望查询执行得快且并发性好，单个查询最好在12个表以内做关联。
全能的枚举
注意防止过度的使用枚举（ENUM）。在MySQL中，当需要在枚举列表中增加一个新的选项时就要做一次ALTER TABLE操作。在MySQL 5.1以及更新版本中，如果不是在列表的末尾增加值，则一样需要ALTER TABLE, ALTER TABLE是一个阻塞操作。
变相的枚举
枚举（ENUM）列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。当需要单个值时，应该使用枚举来代替SET集合列。
NULL
可以使用0、某个特殊值或者空字符串作为代替NULL值。
但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。

CREATE TABLE ...(
  dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'

伪造的全0值可能会导致很多问题（可以配置MySQL的SQL_MODE来禁止不可能的日期）
MySQL会在索引中存储NULL值，而Oracle则不会。

范式和反范式

对应任何给定的数据通常会有很多种表示方法，从完全的范式到完全的反范式化，以及两者的折中。在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

范式的优点和缺点

当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是个好建议。范式化通常可以带来以下好处：

范式化的更新操作通常比反范式化要快。
当数据较好地范式化时，就只有很少或者没有重复数据，所有只需要修改更少的数据。
范式化的表通常更小，可以更好的放在内存里，所有执行操作会更快。
很少有冗余数据意味着检索列表数据时更少的需要DISTINCT或者GROUP BY语句。
范式化的设计的缺点是通常需要关联。稍微复杂一点的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不仅代价昂贵，也可能使一些索引策略无效。

反范式的优点和缺点

因为所有数据都在一张表中，所有可以很好的避免关联。
如果不需要关联表，则对大部分查询最差的情况（表没有索引，是全表扫描）。当数据比内存大时，这可能比关联要快得多，因为避免了随机I/O。
单独的表也能使用更有效的索引。

混用范式化和反范式化

实际上，在实际应用中经常需要混用范式化和反范式化，可能使用部分范式化的schema、缓存表，以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL5.0和更新的版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。
另一个从父表冗余一些数据到子表的理由是排序的需要。
缓存衍生值也是有用的。

缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时候也需要创建一张完全独立的汇总表和缓存表（特别是为满足检索的需求时）。如果能容许少量的脏数据，这是非常好的方法，但是有时确实没有选择的余地（例如，需要避免复杂、昂贵的实时更新操作）。
我们用”缓存表“表示存储那些可能比较简单地从其他表获取（但是每次获取的速度比较慢）数据的表（例如，逻辑上冗余的数据）。”汇总表“则保存的是使用GROUP BY语句聚合数据的表（例如，数据不是逻辑上冗余的）。
不管使用那种方法-不严格的技术或通过小范围查询填满间隙的严格计数，都比实时计算统计值有效得多。这是建立汇总表的最关键原因。
缓存表对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构，跟普通OLTP操作用的表有些区别。例如，可能会需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求优势需要创建一张只包含著表中部分列的缓存表。一个有用的技巧是对缓存表使用不同的存储引擎。例如，如果主表使用InnoDB，用MyISAM作为缓存表的引擎将会得到更小的所有占用空间，并且可以做全文索引。优势甚至想把整个表导出MySQL，插入到专门的搜索系统中获得更高的搜索效率，如Lucene或者SPhinx搜索引擎。
在使用缓存表和汇总表时，必须决定时实时维护数据还是定期重建。但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引。

物化视图

物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。然而，使用Justin Swanhart的开源工具Flexviews，也可以自己实现物化视图。

计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。
问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的
互斥锁（mutex）。这会使得这些事务只能串行执行。要获得更高的并发更新性能，也可以将计数器保存在多行中，每次随机选择一行进行更新。如果希望减少表的行数，以避免表变得太大，可以写一个周期执行的任务，合并所有结果到0号槽，并且删除所有其他的槽。
为了提升读查询的速度，经常会需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法会增加写查询的负担，也需要额外的维护任务。

加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方式是用新的结构创建一个空表，从旧表中查出所有数据插入到新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验，ALTERTABLE操作需要花费数个小时甚至数天才能完成。
MySQL 5.1以及更新版本包含一些类型的“在线”操作的支持，这些功能不需要在整个操作过程中锁表。最近版本的InnoDB也支持通过排序来建索引，这使得建索引更快并且有一个紧凑的索引布局。
一般而言，大部分ALTER TABLE操作将导致MySQL服务中断。对常见的场景，能使用的技巧只有两种：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通重命名和删表操作交换两张表。
不是所有的ALTER TABLE都会引起表重建。有两种方法可以改变或删除一个列的默认值（一种很慢，一种很快）：

mysql> ALTER TABLE sakila.film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

理论上MySQL可以跳过创建新表的步骤。列的默认值实际上是保存在表的.frm文件中的，所有可以直接修改.frm文件而不需要改变表本身。然而MySQL还没有采用这种优化的方法，所有的MODIFY COLUMN操作都讲导致表重建。

另一种方法是通过ALTER COLUMN操作来改变列的默认值：

mysql> ALTER TABLE sakila.film ALTER COLUMN rental_duration SET DEFAULT 5;

这个语句会直接修改.frm文件而不涉及表数据。所以，这个操作是非常快的。

只修改.frm文件

下面的操作有可能是不需要重建表的:

移除（不是增加）一个列的AUTO_INCREMENT属性。
增加、移除，或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字串值。
基本的技术是为想要的结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件

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

总结

尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间）。
使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值。
尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
尽量使用整型定义标识列。
避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT。
范式是好的，但是反范式（大多数情况下意味着重复数据）有时也是必需的，并且能带来好处。
最后，ALTER TABLE是让人痛苦的操作，因为在大部分情况下，它都会锁表并且重建整张表。我们展示了一些特殊的场景可以使用骇客方法；但是对大部分场景，必须使用其他更常规的方法，例如在备机执行ALTER并在完成后把它切换为主库。

参考:《高性能MySQL》

收录于