MySQL 字段类规则

慎重选择数据结构:

• 字段尽可能短
  • 1byte TINYIN
  • 2byte SMALLIN
  • 4byte INT
• 优先数值类型, 避免字符串
  • 索引更加高效, 查询更快, 占用空间更小
  • e.g. ip int unsigned
• 优先定长字符串, 避免变长字符串
  • 更快的索引
• 避免使用ENUM类型 参考
• 避免NULL值
  • 额外的存储空间
  • 对含有NULL列索引需要额外空间
  • 不利于查询优化, 复合索引无效
  • 语义模糊, 业务程序中额外处理负担
  • NOT NULL DEFAULT ...
• 考虑用位运算存储多个开关字段

金额字段处理:

• 和钱相关字段严禁FLOAT类型
• DECIMAL(N,M)
• 要考虑到程序语言对于浮点处理的不精确性, 可以全部用整数运算代替

时间字段:

• DATETIME：8 byte
• TIMESTAMP：4 byte
  • 注意：time_zone 相关
  • 注意：自动初始化特性
  • 读/写时有解析/格式化开销, 很多场合建议直接用INT类型字段替代
• 直接用INT存储格式化的时间：
  • 分 1610250102
  • 时 16102501
  • 天 161025
  • 月 1610
• 存储统一用UTC+0时间, 业务层面处理时区问题

索引类规则

InnoDB表组织形式

• 聚簇索引 / 主键 – 物理存储顺序按照聚簇索引, 按页排列 – 叶节点即数据
• 二级索引 – 指向聚簇索引 – 两次查找

索引优化规则：

• 获取更少的数据行
• 避免点查询
  • 顺序IO 远优于 随机IO
  • 尤其是考虑到数据页缓存效应
• 避免排序

复合索引顺序

create table foo (..., a int, b int, c int);

• Q1: select count(*) from foo where b = 10 and a < 50;
• Q2: select count(*) from foo where b >= 10 and a < 50;
• Q3: select sum(c) from foo where b = 10 and a < 50;
• Q4: select a, count(*) from foo where b = 10 group by a;
• Q5: select b, sum(c) from foo where a = 10 group by b;

索引设计建议：

• 看菜下饭, 根据最核心的查询语句设计
• 不要滥加索引:
  • 更多索引, 不等于更快查询
  • 更多索引, 更慢写入, 更大存储消耗
  • 合理评估字段数据密度和分布情况
• 慎重设计主键索引
  • 根据写入特性设计
    • e.g. 自增ID
  • 最常见范围查找字段
    • e.g. 时间相关字段
• 不要对取值稀疏字段索引
• 复合索引：点查询字段优先, 以最多一个范围查询字段或者排序字段结束
• 线上重点查询语句需要有覆盖索引(covering index)保驾护航
• 字符串字段用前缀索引
• 查询条件不要对索引列做运算

MySQL 其它建议

• 拒绝无效数据 SQL_STRICT_MODE
• 默认 encoding 'utf8mb4' collate 'utf8mb4_bin'
  • MySQL UTF8 不支持超过3字节字符
  • 字符集默认不区分大小写
• 让数据库做它擅长的事情, 避免函数调用, e.g. strftime, md5, …
• 避免 SELECT *
• OR 查询优化
  • a = 1 or a = 2 => a in (1, 2)
  • a = 1 or b = 2 => select ... a = 1 union select ... b = 2
• UNION vs UNION ALL
• 命名建议
  • MySQL特性：字段不区分大小写, 表名/库名区分大小写
  • 字段全部小写
  • 避免保留字

InnoDB实现机制及参数调优

分表 / partition

为什么要(按照时间)分表？

• 每个分区单独一个表, 单独进行索引, 更浅的B+树, 更有效的插入/查询
• 冷热数据分离, 方便删除旧数据（pruning）
  • InnoDB引擎, 单个表数据空间是不会回收的
  • 避免批量删除导致锁表
• 查询谓词优化, 只选择查询范围内的分区

MySQL vs PostgreSQL

MySQL

• 提供特性较少, 简单易上手
• SQL方言比较多
• 插件式的存储引擎：InnoDB, MyISAM, TokuDB, InfoBright, …
• 查询优化偏简单
• 一个线程负责一个连接
• 方便的主从同步

PostgreSQL

• 更加丰富的特性, 学习曲线较陡
• 更加遵循SQL标准
• 只有一种数据存储引擎
• 非常细致的查询优化
• 一个进程负责一个连接
• 缺少同步功能

具体参考这里

数据仓库 / Data Warehouse

区别：

• OLTP vs OLAP
• 单条记录读写, 低延时, 事务性要求 vs 批量写入, 高吞吐, 数据无更新, 聚合查询
• 行式存储 vs 列式存储

数据仓库特点：

• 大数据量, IO是主要瓶颈
• 数据压缩
  • 提升性能：CPU换IO
  • 减少存储成本
• 列式存储
  • 高压缩比
  • 聚合统计数据提前算好
• 集群化结构(Clustering), 水平扩容

例子：

• InfoBright
• Greenplum
• AWS Redshift
• Druid
• Hive over Hadoop