良好的逻辑设计和物理设计是高性能的基石.
4.1 选择优化的数据类型
- 更小的通常更好
- 简单就好
- 整型比字符操作代价低
- 应该使用 MySQL 内建的类型来存储日期
- 应该用整形存储 IP 地址
- 整型比字符操作代价低
- 尽量避免 NULL
- 难优化, NULL 的列使得索引, 索引统计和值比较都更复杂
- 占用更多空间
- 把 NULL 列改为 NOT NULL 带来的性能提升比较小
确定数据类型:
- 确定合适的大类型
- 选择具体类型, 相同大类型的不同子类型数据有时也有一些特殊的行为和属性
- TIMESAMP 所使用的空间是 DATETIME 的一半, 而且会根据时区变化
- TIMESAMP 允许的时间范围要小
4.1.1 整数类型
整数 (whole number), -2(N-1) 到 2(N-1)-1, N 是位数:
- TINYINT 8bit
- SMALLINT 16bit
- MEDIUMINT 24bit
- INT 32bit
- BIGINT 64bit
- 整数有 UNSIGNED 属性
- 整数计算一般使用 64bit 的 BIGINT, 即使在 32bit 环境. 一些聚合函数使用 DECIMAL 或 DOUBLE
INT(11)是没有作用的, 只是限制显式字符的个数
4.1.2 实数类型
实数 (real number):
- DECIMAL 可以存储比 BIGINT 还大的整数
- FLOAT 和 DOUBLE 类型迟滞使用标准的浮点运算进行近似计算. 具体如何计算需要研究具体的平台
- DECIMAL 类型用于存储精确的小数. 支持精确计算
- CPU 不支持对 DECIMAL 的直接计算
- CPU 原生支持浮点计算, 比 MySQL 实现的高精度计算快
- 浮点和 DECIMAL 类型都可以指定精度
- 对于 DECIMAL 列, 可以指定小数点前后位数, MySQL 将 DECIMAL 数据打包, 每4个字节存9个数字. (18,9) 将占用9个字节, 前4, 后4, 外加一个小数点
- MySQL 5.0 和更高版本中的 DECIMAL 类型允许最多65个数字, 早期是254个数字
- DECIMAL 只是一种存储格式
- 建议对浮点数只选择类型而不指定精度
- FLOAT 4B
- DOUBLE 8B
4.1.3 字符串类型
很复杂.
VARCHAR 和 CHAR 类型
- 这两种类型的存储与具体的存储引擎有关, 这里讲的是 InnoDB/MyISAM
- 在内存中的存储和磁盘上的存储也可能不一样
VARCHAR:
- 存储变长字符串
- 节省空间
- 如果表使用 ROW_FORMAT=FIXED 创建的话, 会浪费空间
- 使用额外1或2个字节记录字符串长度,
VARCHAR(10)使用11个字节. 当字符串长度超过255字节时, 使用2个字节存储长度 - 使用时考虑最大长度与平均长度
- 适用 VARCHAR 的情况
- 字符串列的最大长度比平均长度大很多
- 列的更新很少
- 使用了 UTF-8 字符集
- 5.0之后, MySQL 在存储和检索时保留末尾空格, 在4.1之前, MySQL会剔除末尾空格
- InnoDB 会把过长的 VARCHAR 存储为 BLOB
CHAR:
- 定长
- 存储时, MySQL 会删除末尾空格
- CHAR 值会根据需要填充空格以方便比较
- 适合存储很短的字符串, 或者所有值的长度接近. 适合存储 MD5
- 对于经常变更的数据, CHAR 比 VARCHAR 更好. 不容易产生碎片
- 填充和截取空格的行为是 MySQL 服务器层进行处理的
BINARY 和 VARBINARY 存储二进制字符串. 存储字节码 (估计就是二进制), 以 \0 填充.
BLOB 和 TEXT 类型
为存储很大的数据而设计的字符串数据类型, 分别采用二进制和字符串方式存储.
- BLOB 是 SMALLBLOB 的同义词
- TEXT 是 SMALLTEXT 的同义词
- InnoDB 在存储太大的 BLOB/TEXT 时, 存储指针, 占用1~4个字节
- BLOB 和 TEXT 之间仅有的不同是 BLOB 存储的是二进制数据, 没有排序规则或字符集, 而 TEXT 有
- 尽量避免使用 BLOB 和 TEXT
使用枚举 (ENUM) 代替字符串类型
- 具体存储的是数字
- 维护 “数字 - 字符串” 的映射关系
- 排序时, 是按照数字排序而不是字符串
- 如果需要顺序, 那么在定义时就按照顺序排列
- 添加或删除枚举必须使用 ALTER TABLE
- 与 VARCHAR 关联慢
4.1.4 日期和时间类型
- MySQL 存储时间的最小粒度是秒, MariaDB 支持微秒
DATETIME
- 能存储1001~9999年, 精度为秒
- 将日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中, 与时区无关, 8B
- 无歧义格式 “2008-01-16 22:37:08”
TIMESTAMP
- 存储了从1970年1月1日午夜格林尼治以来的秒数, 和 UNIX 时间戳相同
- 4B, 只能表示1970年到2038年
- TIMESTAMP 显示的值依赖于时区
- TIMESTAMP 列默认 NOT NULL
- 尽量使用 TIMESTAMP
- 将 TIMESTAMP 存储为整数, 这不会带来任何收益
- 可以使用 BIGINT 类型存储微秒级别的时间戳
4.1.5 位数据类型
从技术上来说都是字符串类型.
BIT
- true/false
- BIT 列最大长度为64
- InnoDB 使用最小长度的整数来存储一个位, 所以不能节省空间
- MSQL 吧 BIT 当作字符串类型
- 在数字上下文检索时, 结果是数字
- 如果想在一个 bit 中存储空间中存储一个 true/false, 另一个方法是创建 CHAR(0) 列, 并且该列可以为 NULL
SET
- 如果使用多个 true/false 值, 那么可以考虑使用 SET
在整数列上进行按位操作
- TINYINT
使用 SET:
使用整数存储:
4.1.6 选择标识符
要考虑的点:
- 外键与被关联的 id 的数据类型要相同
- 类型如何执行和比较, enum 和 set 在内部是整数, 在比较时转为字符串
- 混用不同的数据类型可能会导致性能问题
- 可能会引发隐式类型转换, 导致问题难以发现
- 选择满足范围并且预留增长空间的前提下最小的数据类型
整数类型
- id
- auto_increment
ENUM 和 SET 类型
- 不适合做 id
- 但是如果某张表是固定状态或类型的静态定义表, 其作为 id 是可行的
字符串类型
- 应该避免作为 id
- 空间消耗大
- 导致页分裂, 磁盘随机访问, 碎片
- select 语句慢
- 如果存储 UIID, 则应该移除
-, 更好的方法使用数字
4.1.7 特殊类型数据
- 低于秒级的时间戳
- IPv4, 无符号整数
- INET_ATON()
- INET_NTOA()
4.2 MySQL schema 设计中的陷阱
太多的列
- 服务器层和存储引擎层之间通过行缓冲通信, 从行缓冲解码成行数据的操作的代价是非常高的
太多的关联
- MySQL 的关联操作最多只能有61张表
- 建议不超过12张表
全能的枚举
- 防止过渡使用枚举
变相的枚举
- 不要使用有歧义的 SET
非此发明 (Not Invent Here) 的 NULL
- 可以使用其他值来表示
NULL - MySQL 会在索引中保存
NULL
4.3 范式和反范式
4.3.1 范式的优点和缺点
优点:
缺点:
- 范式化的表需要关联
- 可能使索引策略无效
4.3.2 反范式的优点和缺点
优点:
- 避免关联
- 顺序 I/O
- 使用更有效的索引策略
4.3.3 混用范式化和反范式化
- 触发器
- 缓存衍生值
- 计数
- 未读数
- 冗余排序字段
4.4 缓存表和汇总表
- 在同一张表中保存衍生的冗余数据
- 创建一张完全独立的汇总表或缓存表
- 缓存表可以使用 MyISAM 存储引擎
- 使用其他数据库, Lucene, Sphinx
- 实时维护还是定期重建
- 当重建汇总表和缓存表时, 需要保证可用性
- 影子表
4.4.1 物化视图
预先计算并且存储在磁盘上的表.
应该就是将普通视图所表示的数据导出到物理磁盘上.
Flexviews.
4.4.2 计数器表
- 使用独立的表用于计数
- 如果要提升并发性能, 将计数存储到多行中, 每次
+1操作随机选择一行进行 - 如果不希望表太大, 可以使用定时任务汇总数据到某一行, 然后删除其他行
4.5 加快 ALTER TABLE 操作的速度
注意版本.
不是所有的 ALTER TABLE 都会引起表重建.
- 改变或修改某列的默认值
- 慢操作
ALTER TABLE tableName MODIFY COLUMN ... - 快操作
ALTER TABLE tableName ALTER COLUMN ..., 直接修改.frm文件
- 慢操作
4.5.1 只修改 .frm 文件
4.5.2 快速创建 MyISAM 索引
4.6 总结
简单原则:
若有收获，就点个赞吧
0 人点赞
