# 选择优化的数据类型
# 存储类型选择原则
- 使用最小数据类型,占用磁盘、内存和 CPU 缓存更少,需要更少的 CPU 周期,所以更快
- 使用更简单的数据类型,需要更少的 CPU 周期
- 尽量避免 NULL,会使得索引、索引统计和值比较更复杂
# 整数类型
- TINYINT,8 位
- SMALLINT,16 位
- MEDIUMINT,24 位
- INT,32 位
- BIGINT,64 位
可以使用 UNSIGNED 标识,标识不允许负值,会使正数的上限提高一倍
# 实数类型
- FLOAT,32 位,不精确计算
- DOUBLE,64 位,不精确计算
- DECIMAL,每 32 位存 9 个数字,小数点 8 位,最多 65 个数字,精确计算,计算代价高
需要精确计算时,为避免 DECIMAL 计算代价高的问题,可使用 BIGINT 类型,乘以相应的倍数即可
# 字符串类型
- VARCHAR,可变长,需要使用额外 1 或 2 个字节记录长度,会产生碎片
- CHAR,定长,空格填充多余字节,检索时去掉填充空格,不需要额外字节记录长度,不会产生碎片
- BINARY,定长,采用 \0 填充,检索时不会去掉填充值
- VARBINARY,可变长
# BLOB 和 TEXT 类型
- BLOB,二进制存储
- TINYBLOB
- SMALLBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
- TEXT,字符串存储
- TINYTEXT
- SMALLTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
排序只对列的最前 max_sort_length 字节做排序
# ENUM 类型
枚举值在标定义时定义,修改时需要修改字段的定义;查询时可能需要关联两张表,会导致查询变慢,如果只是依赖枚举数字,如两个表根据枚举字段关联,则不需要关联枚举表,此时查询会比使用字符串快。
# 日期和时间类型
- DATETIME,范围从 1001 到 9999 年,精度为秒,存储的是 YYYYMMDDHHMMSS 格式的整数,与时区无关,8 个字节
- TIMESTAMP,存储时间戳,范围从 1970 到 2038,与时区相关,4 个字节,值默认不为空
# 位数据类型
- BIT,最长 64 位,存储的是字符
- SET,需要在字段定义时定义 set 元素
# 选择标识符
标识符不仅要考虑存储,还要考虑计算和比较。要尽量用整数。
# schema 设计中的陷阱
- 太多的列
- 太多的关联
- 全能的枚举
- 变相的枚举,使用 set,但是每行只设计一个值
- 非此发明的 NULL,使用特殊字符代替 NULL 会引发很多问题,无法区分真假值
# 范式和反范式
# 范式的优点
- 更新操作更快
- 只需要修改更少的数据
- 表更小,可以放在内存,执行操作会更快
- 更少需要 DISTINCT 或 GROUP BY
# 范式的缺点
- 需要关联,有些索引不能生效
# 反范式的优点
- 避免关联,避免随机 IO,可以使用索引
# 反范式的缺点
就是范式的优点
# 缓存表和汇总表
缓存表标识可以简单从其他表获取数据的表,汇总表标识使用 GROUP BY 聚合数据的表。
# 物化视图
预先计算并且存储在磁盘上的表,可以通过各种各样的策略刷新和更新。
# 计数器表
只是手动创建的普通表,可通过一些技巧支持并发
# 加快 alter table 的速度
需要创建一个空表,从旧表中查询所有数据插入新表,然后删除旧表。有些列的修改不需要新建表,可以通过 alter column 修改列,他只会修改 .frm 文件。
有两种方式可以加快速度:
- 在从库执行,然后主从切换
- 影子拷贝,创建一个和源表无关的新表,然后通过重命名和删除表交换两张表
# 只修改 .frm 文件
- 移除一个列的 AUTO_INCREMENT 属性
- 增加、移除或更改 ENUM 和 SET 常量