【高性能MySQL】第四章 Schema与数据类型优化

第四章 Schema与数据类型优化

¶引言

高性能⬅️良好的逻辑设计和物理设计
根据系统将要执行的查询语句来设计schema

1. 反范式的设计➡️加快/减慢某些类型的查询
2. 添加计数表和汇总表➡️优化查询，但同时也加大维护成本

推荐的学习数据库设计基础知识的书：
《Beginning Database Design》 Apress出版 Clare Churcher

¶4.1 选择优化的数据类型

¶选择原则

¶更小的

• 尽量使用可以正确存储数据的#最小数据类型#
  • 占用更少的磁盘、内存和CPU缓存
  • 处理时需要的CPU周期也更少

¶简单的

• 简单数据类型的操作➡️需要更少的CPU周期
  • 举例：整型比字符操作代价更低
    • 字符集和校对规则（排序规则）使字符比整型更复杂
  • 具体例子1:应该使用MySQL内建的类型来存储日期和时间
    • 而不是字符串
  • 具体例子2:应该使用整型存储IP地址

¶尽量避免使用NULL

• 可为NULL是列的默认属性

  • 通常情况下：指定列为NOT NULL

• 可为NULL的列：

  • 会使用更多的存储空间
  • 在MySQL中也需要特殊处理
  • 被索引时，每个索引记录需要一个额外字节

• 如果计划在列上建索引，尽量避免设计成可为NULL的列

¶小总结：为列做数据类型选择的步骤

1. 确定合适的大类型

数字、字符串、时间等

2. 选择具体类型

很多MySQL的数据类型可以存储相同类型的数据，
但是：
存储的长度和范围不一样
允许的精度不同
需要的物理空间（磁盘和内存空间）不同

¶具体举例：DATETIME和TIMESTAMP

• 都可以存储相同类型数据：时间和日期，精确到秒
• TIMESTAMP
  • 只使用DATETIME一半的存储空间，且可以根据时区变化，具有特 殊的自动更新能力
  • 允许的时间范围更小
  • 有时候特殊能力会成为障碍

¶4.1.1 整数类型

• 两种类型的数字：整数和实数

¶整数

¶几种整数类型

• TINYINT 8位
• SMALLINT 16位
• MEDIUMINT 24位
• INT 32位
• BIGINT 64位
• 可以存储的值的范围：-2^(N-1)到2^(N-1)-1 N为存储空间的位数

¶可选：UNSIGNED属性

• 不允许负值

• 可以使正数的上限提高一倍

• 举例：

  • TINYINT UNSIGNED可以存储的范围：0～255
  • TINYINT：-128～127

• 有符号和无符号类型

  • ➡️使用相同的存储空间
  • ➡️具有相同的性能
  • ➡️根据实际情况选择合适的类型

¶4.1.2 实数类型

• 实数：带有小数部分的数字
• 实数不仅是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数
• MySQL既支持精确类型，也支持不精确类型

¶FLOAT、DOUBLE、DECIMAL类型

• FLOAT、DOUBLE支持使用标准的浮点运算进行近似计算

• DECIMAL用处：存储精确的小数

• 浮点和DECIMAL类型都可以指定精度

  • DECIMAL列：可以指定小数点前后所允许的最大位数

  会影响列的空间消耗

• 浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间

  • FLOAT使用4个字节，DOUBLE占用8个字节
  • DOUBLE有更高的精度和更大的范围
  • 用户可选择的只是存储类型
  • MySQL使用DOUBLE作为内部浮点运算的类型

• 建议1：尽量只在对小数进行精确计算时才使用DECIMAL

例如：存储财务数据

• 建议2：在数据量比较大的时候，考虑使用BIGINT代替DECIMAL

接上例：将需要存储的货币单位根据小数的位数乘以相应的倍数

⬇️
假设：要存储财务数据精确到万分之一分
➡️可以把所有金额乘以一百万，然后将结果存储在BIGINT里
➡️可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题

¶4.1.3 字符串类型

MySQL支持多种字符串类型，每种类型还有很多变种

¶VARCHAR和CHAR类型

两种最主要的字符串类型

¶VARCHAR

• 用于存储可变长字符串

• 是最常见的字符串数据类型

• 比定长类型更省空间

• 需要使用1或2个额外字节记录字符串的长度

  • 如果列的最大长度小于或等于255字节，则只使用1个字节表示
  • 否则使用2个字节

• VARCHAR节省了存储空间，有助于提高性能

• 适合使用VARCHAR的情况：

  • 字符串列的最大长度比平均长度大很多

    有个别特别长的字符串

  • 列的更新很少

扩展1：在5.0以上版本，MySQL在存储和检索时会保留末尾空格
扩展2：InnoDB会把过长的VARCHAR存储为BLOB

¶CHAR

• 定长字符串

• 根据定义的字符串长度分配足够的空间

• 存储CHAR值时，MySQL会删除所有的末尾空格

• CHAR值会根据需要采用空格进行填充以方便比较

• 适合使用CHAR的情况：

  • 存储很短的字符串

  • 所有值都接近同一个长度

    举例：CHAR非常适合存储密码的 MD5 值→一个定长的值

  • 经常变更的数据

    定长的CHAR不容易产生碎片

  • 非常短的列，CHAR比VARCHAR在存储空间上更有效率

    举例：CHAR（1）来存储只有Y和N的值

¶BINARY和VARBINARY

• 存储的是二进制字符串

• 二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符

• 填充：MySQL填充BINARY采用的是\0（零字节）而不是空格，检索时也不会去掉填充值

• 使用场景：需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较

• 二进制比较的优势：

  • 大小写敏感
  • 比字符比较简单，速度更快

¶定义变量时分配空间

• 建议：使用更短的列
  • 更长的列会消耗更多的内存

¶BLOB和TEXT类型

都是为了存储很大的数据而设计的字符串数据类型

• BLOB→二进制方式存储

• TEXT→字符方式存储

• 字符类型

  • TINYTEXT
  • SMALLTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

• 对应的二进制类型：

  • TINYBLOB
  • SMALLBLOB
  • BLOB
  • MEDIUMBLOB
  • LONGBLOB

• MySQL的处理：

  • 把每个BLOB和TEXT值当作一个独立的对象处理

• 当BLOB和TEXT值太大：InnoDB会使用专门的“外部”存储区域来进行存储

  • 每个值在行内需要1~4个字节存储一个指针，然后再外部存储区域存储实际的值

• BLOB和TEXT家族之间仅有的不同是：

  • BLOB类型存储二进制数据

    没有排序规则或字符集

  • TEXT类型有字符集和排序规则

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

• 枚举列可以把一些不重复的字符串存储成一个预定义的集合
• MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中
• MySQL在内部会将每个值在列表中的位置保存为整数，并在表的.frm文件中保存“数字-字符串”映射关系的“查找表”

CREATE TABLE enum_test(
	e ENUM('fish', 'apple', 'dog') NOT NULL
);
INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');

• 以上三行数据实际存储为整数而不是字符串
• 可以通过在数据上下文环境检索看到这个双重属性：

• 建议：尽量避免使用数字作为ENUM枚举常量

  这种双重性很容易导致混乱

• 绕过这种限制的方式：

  • 方法1：按照需要的顺序来定义枚举列

  • 方法2：在查询中使用FIELD()函数显式地指定排序顺序

    这种方法会导致MySQL无法利用索引消除排序

• 枚举不好的地方：

  • 字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE
  • 所以对于未来可能会改变的字符串，不建议使用枚举
  • （除非能接受在列表末尾添加元素）

• MySQL把每个枚举值保存为整数，且必须进行查找才能转换为字符串，所以枚举有一些开销

¶4.1.4 日期和时间类型

• MySQL可以使用许多类型来保存日期和时间值

  举例：YEAR、DATE

• MySQL还提供两种相似的日期类型：DATETIME和TIMESTAMP

¶DATETIME

• DATETIME能保存大范围的值，从1001年到9999年，精度为秒

• DATETIME把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间

• 默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值

  例如：“2008-01-16 22:37:08

  这是ANSI标准定义的日期和时间表示方法

¶TIMESTAMP

• TIMESTAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数

• 和UNIX时间戳相同

• TIMESTAMP只使用4个字节的存储空间

• TIMESTAMP只能表示从1970年到2038年，范围比DATETIME小很多

• MySQL提供了FROM_UNIXITME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳

• TIMESTAMP显示的值也依赖于时区

  MySQL服务器、操作系统以及客户端连接都有时区设置

  • 举例：存储值为0的TIMESTAMP在美国东部时区显示为"1969-12-31 19:00:00"，与格林尼治时间差5个小时

¶小总结

• 如果在多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将很不一样

  • TIMESTAMP提供的值和时区有关系
  • DATETIME保留文本表示的日期和时间

• TIMESTAMP有DATETIME没有的特殊属性

  • 插入时如果没有指定第一个TIMESTAMP的值，则MySQL设置这个列的值为当前时间

    TIMESTAMP的行为规则比较复杂，

    应该验证数据库的行为是需要的

    一个好的方式：修改完TIMESTAMP列后用SHOW CREATE TABLE命令检查输出

  • 在插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值

  • TIMESTAMP默认为NOT NULL，这一点和其他数据类型不一样

• 除了特殊行为，通常也尽量使用TIMESTAMP，因为它比DATETIME空间效率更高

¶小扩展：需要存储比秒更小粒度的日期和时间值时

• MySQL目前没有提供合适的数据类型

• 可以使用自己的存储格式：

  方法1：可以使用BIGINT类型存储微秒级别的时间戳

  方法2：使用DOUBLE存储秒之后的小数部分

  方法3：使用MariaDB代替MySQL

¶4.1.5 位数据类型

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

¶BIT

• 可以使用BIT列在一列中存储一个或多个true/false值

• BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位

• BIT列的最大长度是64个位

• MySQL把BIT当作字符串类型，而不是数字类型

• 但是建议：大部分应用谨慎使用BIT类型

• 如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的CHAR(0)列

  • 该列可以保存空值（NULL）或者长度为零的字符串（空字符串）

¶SET

• 如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型

• SET在MySQL内部是以一系列打包的位的集合来表示

  这样可以有效利用存储空间

• MySQL有像FIND_IN_SET()和FIELD()这样的函数，方便地在查询中使用

• 主要缺点：

  • 改变列的定义的代价较高：需要ALTER TABLE，对于大表来说是非常昂贵的操作

• 一般来说，无法在SET列上通过索引查找

¶在整数列上进行按位操作

• 一种替代SET的方式是使用一个整数包装一系列的位

  举例：可以把8个位包装到一个TINYINT中，并且按位操作来使用

  可以在应用中为每个位定义名称常量来简化工作

• 比起SET的好处：

  • 可以不使用ALTER TABLE改变字段代表的“枚举”值

• 缺点：

  • 查询语句更难写
  • 且查询语句更难理解

• 包装位的应用举例：

这里使用MySQL变量来定义值，

也可以在代码里使用常量来代替

¶4.1.6 选择标识符（identifier）

¶如何选择标识列（identifier column）的数据类型

• 一般更有可能使用标识列与其他值进行比较（举例，在关联操作中），或者通过标识列寻找其他列
• 标识列也可能在另外的表中作为外键使用
• ∴为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型

1. 不仅要考虑存储类型，还要考虑MySQL对这种类型怎么执行计算和比较

举例：

MySQL在内部使用整数存储ENUM和SET类型，

然后在做比较操作时转换为字符串

2. 一旦选定了一种类型，要确保在所有关联表中都使用相同的类型，类型之间需要精确匹配，包括像UNSIGNED这样的属性

• 混用不同数据类型可能导致性能问题
• 在比较操作时隐式类型转换也可能导致难以发现的错误

3. 在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择**最小的数据类型**

¶技巧1：对于整数类型

• 整数通常是标识列的最好的选择
• ∵很快+可以使用AUTO_INCREMENT

¶技巧2： ENUM 和 SET 类型

• 对于标识列是一个糟糕的选择

• ENUM 和 SET 列适合存储固定信息

  举例：有序的状态、产品类型、人的性别

¶技巧3：字符串类型

• 建议：避免使用字符串类型作为标识列

  • 原因：
    • 字符串很耗空间
    • 通常比数字类型慢
  • 尤其是：MyISAM默认对字符串使用压缩索引，会导致查询慢很多（最多下降6倍性能）

• 对于完全“随机”的字符串也要多加注意

  例如：MD5()、SHA1()、UUID()产生的字符串

• 这些函数生成的新值会任意分布在很大的空间内，会导致INSERT以及一些SELECT语句变得很慢

  • 因为插入值会随机地写到索引的不同位置，所以使得INSERT语句更慢
    • 会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片
  • SELECT语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方
  • 随机值导致缓存对所有类型的查询语句效果都很差
    • 因为会使得缓存赖以工作的访问局部性原理失效

• 举例：如果存储UUID值

  应该移除“-”符号

  更好的做法：用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中；检索时可以通过HEX()函数来格式化为十六进制格式

¶4.1.7 特殊类型数据

• 例1：低于秒级精度的时间戳

• 例2：IPv4地址

  人们经常使用VARCHAR(15)列来存储IP地址，然而IPv4地址实际上是32位无符号整数，不是字符串

  用小数点将地址分为四段的表示方法只是为了阅读容易

  应该用**无符号整数存储IP地址**

  MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换

¶4.2 MySQL schema 设计中的陷阱

¶1. 太多的列

¶2. 太多的关联

• 所谓的“实体-属性-值”（EAV）设计模式是一个常见的糟糕设计模式
• MySQL限制了每个关联操作最多只能有61张表，但是EAVES数据库需要许多自关联
• 在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题
• 一个经验法则：如果希望查询执行的快速且并发性好，单个查询最好在12个表以内做关联

¶3. 全能的枚举

• 建议：防止过度使用枚举

• 举例：

¶4. 变相的枚举

• 枚举（ENUM）列允许在列中存储一组定义值中的单个值
• 集合（SET）列则允许在列中存储一组定义值中的一个或多个值 → 可能比较容易导致混乱

¶5. 非此发明（Not Invent Here）的 NULL

• 之前的建议：尽可能避免使用NULL

• 即使需要存储一个事实上的“空值”到表中，也不一定非要使用NULL，也许可以使用以下类型作为代替：

  • 0
  • 某个特殊值
  • 空字符串

• 但是在一些场景中，使用NULL可能会比某个神奇常数更好：

¶4.3 范式和反范式

¶引入和举例说明

• 对于任何给定的数据通常都有很多种表示方法

  完全的范式化

  完全的反范式化

  两者的折中

• 在范式化的数据库中，每个事实数据会出现并且只出现也一次

• 在反范式化的数据库中，信息是冗余的，可能会存储在多个地方

¶4.3.1 范式的优点和缺点

• 需要提高性能时，经常被建议：对schema进行范式化设计，尤其是写密集的场景

¶范式化的优点

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

¶范式化的缺点

• 通常**需要关联**

• 稍微复杂一些的查询语句在符合范式的schema 上都可能需要至少一次关联

  代价昂贵，也可能使一些索引策略无效

• 例如：范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引

¶4.3.2 反范式的优点和缺点

¶反范式的优点

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

• 如果不需要关联表，则对于大部分查询，最差的情况：（即使表没有使用索引）全表扫描

  • 当数据比内存大时，这可能比关联要快得多
  • ∵这样避免了随机I/O

  全表扫描基本上是顺序I/O，但不是100%，跟引擎的实现有关

• 单独的表也能使用更有效的索引策略

¶反范式的缺点

• 关联

  • 需要在一个索引中又排序又过滤

• 如果采用反范式化组织数据，将两张表的字段合并一下，并且增加一个索引（account_type，published），就可以不通过关联写出这个查询，this is高效：

SELECT message_text, user_name
FROM user_messages
WHERE account_type = 'premium'
ORDER BY published DESC
LIMIT 10;

¶4.3.3 混用范式化和反范式化

• 在实际应用中经常需要混用
  • 可能使用部分范式化的schema、缓存表，以及其他技巧
  • 最常见的反范式化数据的方法：复制或缓存，在不同的表中存储相同的特定列

• 从父表冗余一些数据到子表→是**排序**的需要
• 缓存衍生值→useful

¶4.4 缓存表和汇总表

• 有时候提示性能最好的方法：在同一张表中保存衍生的冗余数据

• 有时也需要创建一张**完全独立的汇总表或缓存表（特别是为了满足检索**的需求时）

• 术语【“缓存表”】

  来表示存储那些可以比较简单地从schema其它表获取（但是每次获取的速度比较慢）数据的表（例如：逻辑上冗余的数据）

• 术语【“汇总表” / “累积表”(Roll-Up Table)】

  保存使用GROUP BY语句聚合数据的表（例如：数据不是逻辑上冗余的）

¶汇总表

• 建立汇总表的最关键原因：

  • 不严格的计数
  • 通过小范围查询填满间隙的严格计数

  都比计算messages表的所有行有效的多

• 实时计算统计值是很昂贵的操作

  • 要么扫描表中的大部分数据
  • 要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般会对UPDATE操作有影响
    • 一般不希望创建这样的索引

• 【计算最活跃的用户】或【计算最常见的“标签”】是这种操作的典型例子

¶缓存表

• 对优化搜索和检索查询语句很有效

  • 这些查询语句经常需要特殊的表和索引结构
  • 跟普通OLTP操作用的表有些区别

• 举例：可能会需要很多不同的索引组合来加速各种类型的查询

  • 这些矛盾的需求：有时需要创建一张只包含主表中部分列的缓存表

• 一个有用的技巧：对缓存表使用不同的存储引擎

• 例如：

  • 如果主表使用InnoDB，
  • 用MyISAM作为缓存表的引擎可以得到更小的索引占用空间
  • 并且可以做全文搜索

• 有时甚至把整个表导出MySQL，插入到专门的搜索系统中获得更高的搜索效率

  • 例如：Lucene或Sphinx搜索引擎

¶使用汇总表和缓存表时

• 必须决定：

  • 是实时维护数据还是定期重建
  • 哪个更好依赖于应用程序

• 定期重建→

  • 不仅节省资源
  • 也可以保持表不会有很多碎片
  • 有完全顺序组织的索引（更加高效）

¶重建汇总表和缓存表时

• 需要保证数据在操作时依然可用

• 使用方法：影子表

¶影子表

• 指的是在一张真实表“背后”创建的表
• 当完成建表操作后，可以通过一个原子的重命名操作切换影子表和原表

¶4.4.1 物化视图

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

¶物化视图的组成

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

¶Flexviews

• Flexviews通过提取对源表的更改，可以增量地重新计算物化视图的内容
• 意味着不需要通过查询原始数据来更新视图

举例：

如果创建了一张汇总表用于计算每个分组的行数，此后增加了一行数据到源表中

Flexviews简单地给相应的组的行数加一即可

• 同样的计数对其他的聚合函数也有效

  例如SUM() 和 AVG()

• 使用Flexviews的好处：

  • 基于行的二进制日志包含行更新前后的镜像
  • ∴Flexviews不仅可以获得每行的新值，还可以不需要查找源表就能知道每行数据的旧版本
  • 计算增量数据比从源表中读取数据的效率更高

• 概略：

  先写一个SELECT语句描述想从已经存在的数据库中得到的数据，可能包含关联和聚合（GROUP BY）
  
  Flexviews有一个辅助工具可以转换SQL语句到Flexviews的API调用
  
  Flexviews会监控数据库的变更并且转换后用于更新存储物化视图的表
  
  现在应用可以简单地查询物化视图来代替查询需要检索的表

• Flexviews有不错的 SQL 覆盖范围，包括一些棘手的表达式

  • 这一点有利于创建基于复杂SQL表达式的视图

• 可以用基于物化视图的简单、快速的查询替换原来复杂的查询

¶4.4.2 计数器表

• 如果应用在表中保存计数器，则在更新计数器时可能遇到并发问题

• 计数器表可以用来缓存一个用户的朋友数、文件下载次数等

• 创建一张独立的表存储计数器

  • →good idea
  • →可以使计数器表小而快
  • 使用独立的表可以帮助避免查询缓存失效

• 第一个案例：网站点击次数计数器

• 第二个案例：每隔一段时间开始一个新的计数器

• Tip：更快地读，更慢地写

¶4.5 加快 ALTER TABLE 操作的速度

• MySQL的ALTER TABLE操作的性能对大表来说是个大问题

• MySQL执行大部分修改表结构操作的方法：

  • 用新的结构创建一个空表，
  • 从旧表中查出所有数据插入新表，
  • 然后删除旧表

  问题：

  如果内存不足而表又很大+有很多索引

  →花费时间长

  大表的ALTER TABLE要花费数小时甚至数天才能完成

• 大部分ALTER TABLE操作将导致MySQL服务中断

• 对于常见的场景，有两种技巧：

• 第一种：先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换

• 第二种：“影子拷贝”：用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表

• 有一些可以帮助完成影子拷贝的工具：

  • Facebook数据库运维团队的“online schema change”工具

    https://lauchpad.net/mysqlatfacebook

  • Shlomi Noach的openark toolkit

    http://code.openark.org/

  • Percona Toolkit

    http://www.percona.com/software

• 不是所有的ALTER TABLE操作都会引起表重建，有两种方法可以改变或删除一个列的默认值（一块一慢）

¶4.5.1 只修改.frm文件

• 只修改.frm文件来修改列的默认值的速度很快

• （以下这本书说不是官方方法的方法）

¶可能不需要重建表的操作

• 移除（no添加）一个列的AUTO_INCREMENT属性
• 增加、移除或更改ENUM和SET常量
  • 如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字符串

¶基本的技术

• 为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件

• 具体操作步骤：

1. 创建一张有相同结构的空表，并进行所需要的修改（如增加ENUM常量）

2. 执行FLUSH TABLES WITH READ LOCK

   这将会关闭所有正在使用的表，并且禁止任何表被打开

3. 交换.frm文件

4. 执行UNLOCK TABLES来释放第2步的读锁

¶具体案例操作

¶4.5.2 快速创建MyISAM索引

¶步骤说明

• 为了高效地载入数据到MyISAM表中，一个常用技巧：
  • 先禁用索引、载入数据，
  • 然后重新启用索引<

> ALTER TABLE test.load_data DISABLE KEYS;
-- load the data
> ALTER TABLE test.load_data ENABLE KEYS;

• 技巧的原理：

  • 构建索引的工作被延迟到数据完全载入以后
  • 这时已经可以通过排序来构建索引
  • 这样载入数据更快，且使得索引树的碎片更少、更紧凑

• 以上方法对于唯一索引无效

  • ∵DISABLE KEYS只对非唯一索引有效

• MyISAM会在内存中构造唯一索引，并且为载入的每一行检查唯一性

  • 一旦索引的大小超过了有效内存大小，载入操作就会变得越来越慢

• 在现代版本的InnoDB中的类似技巧：

  依赖于InnoDB的快速在线索引创建功能

  • 先删除所有的非唯一索引，
  • 然后增加新的列，
  • 最后重新创建删除掉的索引
  • Percona Server可以自动完成这些操作步骤

• 也可以：用ALTER TABLE的骇客方法来加速这个操作

  需要多做一些工作并承担一定风险

¶具体操作步骤

1. 用需要的表结构创建一张表，但是不包括索引

¶4.6 总结