本章关注的是MySQL数据库的设计, 主要介绍的是MySQL数据库设计和其他关系型数据库管理系统的区别.
选择优化的数据类型
几个原则: 更小的通常更好, 简单就好, 尽量避免NULL
先选择大类型: 数字, 字符串, 时间. 在选择具体类型.
DATETIME和TIMESTAMP: 都精确到秒. DATETIME与时区无关, TIMESTAMP只使用DATETIME一半的空间, 并会随时区变化, 且具有自动更新的能力. 不过TIMESTAMP时间范围较小.
INTEGER, BOOL, NUMERIC只是别名. SHOW CREATE TABLE检查能看到其基本类型.
整数类型
| 类型 | 存储空间长度 |
|---|---|
| TINYINT | 8 |
| SMALLINT | 16 |
| MEDIUMINT | 24 |
| INT | 32 |
| BIGINT | 64 |
以上类型决定如何在内存和磁盘中存储, 但是计算时一般会使用64位的BIGINT.
MySQL 可以为整数类型指定宽度，如 INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围。这是规定了 MySQL 的一些交互工具用来显示字符的个数。对于存储和计算来说，INT(1) 和INT(20) 是相同的.
实数类型
FLOAT(4个字节), DOUBLE(8个字节)支持存储小数, 支持标准的浮点近似计算(使用的是平台浮点数的具体实现, 计算上没有区别).
DECIMAL不仅支持存储小数, 还支持存储比BIGINT大的整数, 且支持精确计算(5.0或以上版本使用的是MySQL服务器自身的高精度计算, 因此比CPU自身的浮点数计算要慢).
DECIMAL(18,9)意思是小数点左右两边各存9个数字, 最多支持65个数字, 随数字增加, 存储空间上升.
尽量用FLOAT或DOUBLE, 财务数据才用DECIMAL, 可以转化为分用BIGINT.
字符串类型
从MySQL4.1 版本开始 ，CHAR(n), VARCHAR(n) 中的n 指字符长度，不再表示之前版本的字节长度。也就是说在不同字符集下，char类型列的内部存储可能不是定长数据.
也就是说对于多字节字符集编码，CHAR类型不再代表固定长度的字符串, innodb存储引擎在内部将其视为变长字符类型.
VARCHAR
VARCHAR适合存储可变长的字符串, 它比CHAR节省空间. 但由于行是变长的, 在 UPDATE 一个可变长字符串时，如果行所占空间增长，并且在页内没有更多空间存储，在MyISAM会将行拆成不同的片段存储，在 InnoDB 进行页分裂来使行可以放进页内.
VARCHAR需要使用1或者2个额外字节记录字符串的长度：如果列的最大长度小于255字节，则只使用1个字节，否则使用2个字节.
适用场景:
- 字符串最多长度比平均长度大很多;
- 更新很少, 因此碎片不是问题
- 使用UTF8这样的字符集, 每个字符使用了不同的字节数存储
CHAR
CHAR 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。当存储 CHAR 值时，MySQL 会删除所有的末尾空格.
适用场景:
- MD5值
- CHAR(1)存储Y和N
# length()是字节长度
mysql> select length('杨恺');
+------------------+
| length('杨恺') |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
## char_length()是字符长度
mysql> select char_length('杨恺');
+-----------------------+
| char_length('杨恺') |
+-----------------------+
| 2 |
+-----------------------+
1 row in set (0.00 sec)
VARCHAR(5)和VARCHAR(200): 若都可以满足需要, 请使用VARCHAR(5), 因为在MySQL在内存中会使用定长来保存内部值. 最好的策略是只分配真正需要的空间.
BINARY 和 VARBINARY
BINARY 和 VARBINARY 存储二进制字符串。它们用于保存二进制字符串，存储的是字节码.
当需要存储二进制数据，并且希望使用字节码而不是字符进行比较时。字节比较的优势并不仅仅体现在大小写敏感上，在进行比较BINARY字符串时，每次按一个字节，并且根据该字节的数值比较。因此二进制比较比字符比较简单的多，效率也高.
BLOB和TEXT类型
BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储. InnoDB在它们特别大的时候会使用单独的外部存储区域来保存他们，每个值在行里使用1到4个字节存储指针，并且还需要足够的存储空间来保存实际的值.
它们只对每个列的最前 max_sort_length 字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减少 max_sort_length 的配置，或者使用 ORDER BY SUBSTRING(column，length).
枚举值ENUM
枚举值在MySQL中保存为整数, 此外还要存储2个部分(不重复的字符串存储为一个预定的集合, 以及"数字-字符"串映射关系"查找表").
枚举字段是按照内部存储的整数而不是定义的字符串进行排序的，解决方式:
第一种是：按照需要的顺序来定义枚举列。
第二种解决方式是：在查询中使用 FIELD() 函数显式地指定排序顺序，但这会导致 MySQL 无法利用索引消除排序.
优点: 所消耗的存储较小.
缺点: a.字符串列表是固定的, 添加或者删除字符串必须使用ALTER TABLE. 除非可以接受只在列表末尾添加元素, 这样mysql 5.1就可以不用重建这个表. b.由于每个枚举值被存储为整数, 因此查询是必须通过映射表, 因此把CHAR/VARCHAR列与枚举列进行关联可能会比直接两个CHAR/VARCHAR列关联更慢.
时间和日期类型
MySQL最小时间粒度为秒(MariaDB支持微秒)
| 日期和时间类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| DATE | 4 | 1000-01-01 | 9999-12-31 |
| DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| TIMESTAMP | 4 | 19700101080001（1970-01-01 08:00:01) | 2038年某个时刻 |
| TIME | 3 | -838:59:59 | 838:59:59 |
| YEAR | 1 | 1901 | 2155 |
DATETIME
这个类型能保存大的范围值，从 1001 年到 9999 年，精度为秒，它把日期和时间封装到一个格式为 YYYYMMDDHHSSMM 的整数中，与时区无关，它使用了 8 个字节的存储空间。在默认情况下， MySql 以一种可排序的、清楚的格式来显示 DATETIME 值，例如"2012-05-22 22:35:01".
TIMESTAMP
TIMESTAMP显示值也依赖于时区.
在默认情况下，如果插入的行没有设置 TIMESTAMP 列的值， MySql 会把它设置为当前系统时间，在更新的时候，如果没有显示的更新 TIMESTAMP 列的值， MySql 也会以当前时间来自动更新.
如果要存储比秒更小粒度的日期和时间值，可以使用 BIGINT 类型存储微秒级别的时间戳或者使用 DOUBLE 存储秒之后的小数部分, 或使用MariaDB.
时间的计算:
mysql> select '2020-02-12 08:12' - interval 1 hour;
+--------------------------------------+
| '2020-02-12 08:12' - interval 1 hour |
+--------------------------------------+
| 2020-02-12 07:12:00 |
+--------------------------------------+
mysql> select left(now(),14)-interval 23 hour;
+---------------------------------+
| left(now(),14)-interval 23 hour |
+---------------------------------+
| 2020-02-12 08:00:00 |
+---------------------------------+
interval支持小时和分的组合: https://www.mysqltutorial.org/mysql-interval/
mysql> select now() - interval '1:06' hour_minute; --当前时间减1小时6分钟
位数据类型
BIT
BIT(2)存储2个位, 最大64位. 尽量不用该类型.
SET
适合做权限控制:
mysql> create table acl_set (
name varchar(100) not null comment '姓名',
perms set('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') not null comment '权限'
);
mysql> insert into acl_set (name, perms) values ('杨恺', 'CAN_READ,CAN_WRITE'), ('王洋', 'CAN_DELETE');
mysql> select * from where find_in_set('CAN_WRITE', perms); --查询谁拥有读权限
+--------+--------------------+
| name | perms |
+--------+--------------------+
| 杨恺 | CAN_READ,CAN_WRITE |
+--------+--------------------+
也可以用整数TINY来做权限控制(这里使用了存储过程, 也可以使用应用程序来操作位):
mysql> set
@CAN_READ := 1<<0,
@CAN_WRITE := 1<<1,
@CAN_DELETE := 1<<2;
mysql> create table acl (
perms TINYINT UNSIGNED NOT NULL DEFAULT 0
);
mysql> insert into acl (perms) values (@CAN_READ + @CAN_DELETE);
select * from acl where perms & @CAN_READ; --查询谁拥有读权限
+-------+
| perms |
+-------+
| 5 |
+-------+
优点: 存储消耗小.
缺点: 改变列的定义代价高, 需要ALTER TABLE, 也不支持在该列通过索引查找.
选择标识符
为标识列选择适合的数据类型非常重要。一般更有可能用标识列与其他值进行比较，或者通过标识列寻找其他列。标识列也可能在另外表中作为外键使用。所以为标识列选择数据类型时，应该选择跟关联表中对应列一样的类型, 类型间需要精确匹配, 包括UNSIGNED这样的属性.
原则: 在预留未来增长空间的前提下, 选择最小的数据类型.
尽量使用整数类型, 避免使用字符串类型
当心框架自动生成的schema: 会有严重的性能问题, 因此国内大多数都用的mybatis, 而非JPA.
IP地址
尽量使用无符号整数(如bigint unsigned)存储, 而非字符串.
mysql> select
--最好使用无符号整数存储IP
+---------------------------+
| inet_aton('123.23.45.32') |
+---------------------------+
| 2065116448 |
+---------------------------+
mysql> drop temporary table if exists foo1;
mysql> create temporary table foo1 select inet_aton('123.23.45.32');
mysql> desc foo1;
+---------------------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+-----------------+------+-----+---------+-------+
| inet_aton('123.23.45.32') | bigint unsigned | YES | | NULL | NULL |
+---------------------------+-----------------+------+-----+---------+-------+
mysql> select inet_ntoa(2065116448);
+-----------------------+
| inet_ntoa(2065116448) |
+-----------------------+
| 123.23.45.32 |
+-----------------------+
要想得知某个变量或函数查询结果的类型, 新建一张临时表即可:
mysql> drop temporary table if exists foo1;
mysql> create temporary table foo1 select left(now(), 14); -- dirty magic
mysql> desc foo1;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| left(now(), 14) | varchar(14) | YES | | NULL | NULL |
+-----------------+-------------+------+-----+---------+-------+
MySQL schema设计中的陷阱
太多的列, 太多的关联, 全能ENUM, 错误使用的SET, 过多的NULL
关于NULL:
尽量设置列为NOT NULL DEFAULT xxx, 不过也不要走极端, 当确实需要表示未知值时, 也不要害怕使用NULL.
范式和反范式
范式
优点:
- 范式化的更新操作比反范式更快(写密集场景)
- 当数据较好地范式化时，很少有重复数据，只需要修改更少的数据
- 范式化的表更小，可更好地放到内存里，执行操作更快
- 很少冗余数据，检索列表数据时更少需要distinct、group by语句
缺点：
- 查询常需要1次或更多次关联
- 会使一些索引策略无效, 例如范式化可能将列放在不同的表中, 而如果它们在同一个表中本可以属于同一个索引
反范式
优点:
- 所有数据都在一张表中, 可以避免关联
- 及时是全表扫描, 也比关联快, 如果用到索引则更快
混用范式和反范式
在不同表中存储相同的特定列, 缓存一些数据到某列等等
缓存表和汇总表
- 缓存表: 存储那些可以比较简单地从schema其他表获取(但每次获取速度比较慢)数据的表
- 汇总表: 保存使用group by语句聚合数据的表
汇总表的例子: 统计网站之前24小时内发送的消息数, 可以每小时生产一条汇总表的记录. 或者计算最活跃的用户/最长久的标签, 由于经常做update, 因此没办法在其上建索引, 因此查询很慢, 此时就可以用汇总表来保存.
2种表都必须决定是: 实时维护还是定期重建.
以下"影子拷贝"可以保证重建表时的可用性:
mysql> drop table if exists summary_new, summary_old;
mysq> create table summary_new like summary;
-- 重建summary_new表的一系列操作
-- ...
mysql> rename table summary to summary_old, summary_new to summary; -- 原子重命名操作
summary_old表保持老数据, 可用于故障回滚.
物化视图
预先计算并存在磁盘上的表，可通过各种策略刷新和更新，mysql不原生支持，可使用Justin Swanhart工具flexviews实现;
flexviews组成： 变更数据抓取，读取服务器二进制日志且解析相关行的变更; 一系列可以帮助 创建和管理 视图 的定义 的 存储过程; 一些可应用变更到 数据库中的物化视图 的工具
flexviews通过提取对源表的更改，可增量地重新计算物化视图的内容：不需要查询原始数据来更新视图.
- 注: mysql的视图不是物化视图, 而是表查询, 因此效率低下. 物化视图则是一种可以提高性能的方案.
计数器表
方案: 建立一张独立的表存储计数器.
假设有个计数器，只有一行数据，记录网站的点击次数：
CREATE TABLE hit_counter(
cnt int unsigned not null
) ENGINE=InnoDB;
网站的每次点击都会导致对计数器进行更新：
UPDATE hit_counter SET cnt = cnt + 1;
这种操作是原子的, 但是会有全局的行互斥锁, 不适合高并发.
为了更高的并发性，可将计数器保存在多行，每次随机选一行更新，要统计结果时，聚合查询；对表结构进行修改：
CREATE TABLE hit_counter(
slot tinyint unsigned not null primark key,
cnt int unsigned not null
) ENGINE=InnoDB;
然后预先在这张表增加100行数据。现在选择一个随机槽（slot）进行更新, 这样并发度就提高了100倍.
UPDATE hit_counter SET cnt = cnt + 1 where slot =RAND() * 100;
要获取结果，只需统计所有行
SELECT SUM(cnt) FROM hit_counter;
如每隔一段时间重新开始一个新的计数器 如每天一个，对表结构进行修改：
CREATE TABLE daily_hit_counter(
day date not null,
slot tinyint unsigned not null,
cnt int unsigned not null,
primark key(day, slot)
) ENGINE=InnoDB;
这个场景则不能预先生成行了，而用 ON DUPLICATE KEY UPDATE 如果存在则更新操作
INSERT INTO daily_hit_counter(day, slot, cnt)
VALUES(CURRENT_DATE, RAND() * 100, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1
加快alter table 操作的速度
ALTER TABLE的操作的是: 用新的结构创建空表, 从旧表中查出all数据插入新表，删除旧表. 因此花费时间很长, 导致服务终端.
目前成熟的方案有:
- 先在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换
- 影子拷贝: 见上文
修改默认值时alter column比modify column快: 前者只去更改.frm文件而无需涉及表数据.
总结
- 避免过度设计, 例如会导致及其复制查询的schema设计, 或很多列的表的设计.
- 使用小而简单的数据类型, 尽量避免使用NULL值.
- 尽量使用相同数据类型做列的关联.
- 尽量使用整形定义标识列.
- 避免使用已经遗弃的特性, 如指定浮点数的精度, 或整数显示的宽度.
- 不哦滥用ENUM和SET, 避免使用BIT.
