注意逻辑设计、物理设计和查询执行，及其相互作用
1 选择优化的数据类型
(1) 选择原则
小
操作更快、占用更少的磁盘、内存和缓存
简单
尽量使用内建类型，如使用整型存储IP地址
非NULL
默认的NULL属性更难优化
(2) 选择顺序
类型
数值、字符串、时间等
长度、范围、精度和空间
如DATETIME和TIMESTAMP都能存储秒精度的时间和日期，但是TIMESTAMP允许的时间范围更小，存储空间只有前者一半，并且会根据时区变化
(3) 数据类型
1) 整数
类别：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT
空间：1B、2B、3B、4B、8B
可以使用UNSIGNED标记仅允许非负值，正数上限增加一倍。
指定宽度用于交互工具显式，不影响存储范围。
2) 浮点数
MySQL会自动选取精度，建议只指定类型，不指定精度
FLOAT、DOUBLE（非精确）
精度由实现平台决定，CPU直接支持，计算更快
FLOAT 4字节, DOUBLE 8字节
DOUBLE作为内部浮点计算类型
DECIMAL（精确）
精度由MySQL实现控制，如DECIMAL(18,9)，小数点后9位数字，总共18位数字
范围可以比BIGINT大，最多允许65个数字
存储空间每4个字节存9个数字，小数点额外占用一个数字
计算时转换为DOUBLE
注意：在范围允许、语义明确和精确计算的场景下，可以使用BIGINT代替DECIMAL，在计算时缩小相应倍数，可减少存储和计算代价。
3) 字符串
可以定义字符集和排序规则，但会影响性能
实现与存储引擎相关
Memory引擎只支持定长字符串
用于Mysql会分配定长内存来保存内部值，声明变长字符串时尽量少
BINARY和VARBINARY存储二进制字符串，类似CHAR和VARCHAR。BINARY使用\0填充
1‘ VARCHAR
变长字符串，适用于长度变化大、更新少(避免长度扩展引起跨页存储)或字符集复杂(可以用不同的字节数表示)的场景
仅使用必要的空间，额外使用1-2字节存储长度
更新时，由于页内没有空间，MyISAM会跨片段存储，InnoDB会分裂页
版本>=5.0，存储和检索时保留末尾空格
InnoDB可以将过长的VARCHAR转换为BLOB
2’ CHAR
定长字符串，使用空格填充，适合长度短（不用存储长度）、更新多的场景
存储和查询时忽略末尾空格
4) BLOB和TEXT
用于长度大的字符串存储
分别有TINY-、SMALL-、MEDIUM-和LONG-等类型，默认为SMALL-
与其他类型不同，MySQL将其作为独立对象处理。长度太大时，InnoDB会使用专门的存储区域存储，利用指针引用。
区别是BLOB没有字符集和排序规则
排序时，使用前max_sort_length字节排序，也可以使用ORDER BY SUSTRING(column, length)动态指定
注意：
使用Memory引擎（不支持该数据类型），或者使用隐式临时表，或者临时表长度超过max_heap_table_size或tmp_table_size时，临时表将转换为MyISAM临时表。建议避免使用该类型或截取部分字符串用于计算。
使用枚举代替字符串
适合不断重复的有限的字符串集合
内部存储为数值，为了避免混淆，建议避免存储数值字面量的字符串
排序使用内部数值。可以使用FIELD()显式指定排序，但无法利用索引消除排序。
优点：
- 关联很快
- 节约存储空间
缺点：
- 元素固定，需要ALERT TABLE变更
- 需要通过查找转换为字符串
5) DATETIME和TIMESTAMP
MySQL支持的最小时间粒度为秒。更小粒度可以使用MariaDB(微秒)或使用数值存储。
DATETIME用8字节存储范围1001年-9999年的时区无关的时间数据。
TIMESTAMP使用4字节存储1970年-2038年的时区相关的时间数据，默认NOT NULL，采用当前时间。
6) 位
BIT最多支持64位。
行为因存储引擎而异。MariaDB按照长度分配空间，其他引擎就近使用整型空间。
在数值上下文中，BIT将转换为数字。由于容易与ASCII码混淆，尽量使用替代方案。
一位时，使用CHAR(0)
多位
SET
缺点：改变列定义需要修改表
整型
缺点：难以操作和理解
(4) 标识符
1) 原则
- 为了关联，需要表之间类型完全匹配
- 在满足需求的前提下，尽量节省空间
2) 选取方案
整型
最好的选择，可以自增
ENUM/SET
不建议，形式固定
MySQL内部使用整型存储，比较时转换为字符串。
字符串
不建议，空间占用大且效率低
MyISAM默认对字符串使用压缩索引
随机字符效率低，因为页分裂和磁盘随机访问
UUID虽然具有一定顺序性，但是效率不如整数。通常使用UNHEX()和HEX()在字符串和BINARY(16)间转换。
注意：自动生成或者对象关系映射系统(ORM)选择的数据类型可能需要优化。
(5) 特殊类型数据
IP地址实际是32位无符号整数，可以使用INET_ATON()和INET_NTOA()相互转换。
2 Schema设计中的陷阱
(1) 列太多
MYSQL使用行缓冲格式在服务器层和存储引擎层间拷贝数据。
服务器层将行缓冲数据解码为数据列，代价高昂。
MyISAM变长结构和InnoDB总是需要转换，代价与列数量相关。
MyISAM定长结构与行缓冲格式兼容，不需要转换。
(2) 关联太多
注意实体-属性-值（EAV）设计模式在MYSQL中不能稳定工作。
MySQL限制最多关联61张表。
建议在12个表内关联。
(3) 全能的枚举
修改枚举定义需要修改表。
防止过度使用枚举。
(4) 变相枚举
有限值、值互斥、值集合不变场景下使用枚举。
列值间互相排斥时，使用枚举替代SET。
(5) 特殊的NULL表示
建议尽量避免使用NULL值。
必要时，需要考量特殊值替换还是直接使用NULL。前者语义不明且容易隐藏问题，后者牺牲效率。
注意：MySQL会在索引中缓存NULL值，而Oracle不会。
3 范式与反范式
范式化数据库中，每个事实数据只出现一次。而反范式数据库中，数据是冗余的。
(1) 范式优缺点
优点
- 更新更快
- 改动更少
- 表更小，可内存执行
- 更少使用DISTINCT或GROUPBY操作
缺点
- 需要关联
- 可能导致索引策略无效
(2) 反范式优缺点
优点
- 避免关联
- 更有效额索引策略，因为数据集中在一张表中
(3) 混用范式和反范式
针对不同的应用场景，可能需要混用范式和反范式。
最常见反范式化数据的是复制和缓存。版本>=5.0可以使用触发器更新缓存。
4 缓存表与汇总表
用于提升性能，适用于读多写少的场景。
更快地读，更慢地写：使用额外索引或数据冗余提升数据读取性能，同时也增加了写入复杂性。
缓存表时直接从其他表中抽取简单数据形成的表。汇总表时经过GROUPBY等数据处理后形成的表。
技巧：
- 为了加速查询，缓存表、汇总表可以使用不同于主表的存储引擎，以便使用不同的索引组合
- 需要决定实时更新还是定期重建。实时更新反映的是最新状态，定期重建节省资源、减少表碎片、有完全顺序的索引组织
- 表格重建时，可以先在影子表上操作，保证表内容可用
(1) 物化视图
物化视图是预先计算的存储在磁盘中的表，通过策略更新。
MySQL并不原生支持物化视图，可以使用Flexviewss工具或者自己实现。
(2) 计数器表
用于计数，存在并发瓶颈
将数据分散存储到多行中，每次随机更新一行，查询时使用聚合查询。
可以使用周期任务周期性合并数据到一行中，减少数据行数。
5 ALERT TABLE提速
通常MySQL修改表是，先创建一个新表，从旧表查询数据并写入到新表中，最后删除旧表。导致服务中断，内存占用大，且操作时间长。
避免服务中断常用技巧：
主备库切换
影子表拷贝
如online schema change、openark toolkit和percona toolkit工具等。
CDC工具实现无锁表结构变更
修改列默认值时，MODIFY COLUMN会引起重建。
由于列默认值存储在.frm文件中，理论上可以只修改.frm文件以避免表重建。如ALTER COLUMN。
(1) 只修改.frm文件
可以不需重建表的操作：
- 移除列的自增属性
- 增、删、改ENUM或SET
[有风险]以下过程通过使用新的.frm替换旧的达到修改表的目的：
1‘ 创建一张相同结构的空表，并应用修改
2’ 执行FLUSH TABLES WITH READ LOCK，关闭所有正在使用的表并禁止打开
3‘ 交换.frm文件
4’ 执行UNLOCK TABLES释放读锁
(2) 快速创建MyISAM索引
1) 关闭非唯一索引
常用技巧：先禁用索引，再载入数据，最后重启索引
在InnoDB中有类似技巧：依据快速在线索引创建功能，先删除所有非唯一索引，再增加新列，最后重新创建删除的索引。
但是禁用索引对唯一索引无效。
2) 替换.frm文件
有风险，需要备份
1‘ 创建一张相同结构的表A，不包含索引
2’ 加载数据到表A中
3‘ 创建一张相同结构的表B，包含索引。(会创建.frm文件和.MYI文件)
4’ 获取读锁并刷新表
5‘ 替换.frm文件和.MYI文件
6’ 使用REPAIR TABLE重建表索引。该操作通过排序创建包含唯一索引在内的所有索引。
参考资料
《高性能MySQL》