MySQL DB Schema 设计原则

在工作上常会有自己设计架构的时候，因此纪录一下一些心得。

因应资料量庞大，许多 Schema 在设计时，必须考量到 DB 资源的使用，以达到最有效的利用。

以下就用这一张可以再调整的资料表为例：

CREATE TABLE `mainartist_raw` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `territory_code` char(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `language_code` char(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `script_code` char(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title_text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'artist role name',
  `status` bigint(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` int(10) NOT NULL,
  `updated_at` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `btree__title_text` (`title_text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED

流水号的设计

MySQL 中常用的整数数字型态有 tinyint、smallint、int、bigint
* tinyint(1 byte)：-128 到 127
* smallint(2 bytes)：-2^15 (-32768) 到 2^15-1 (32767)
* int(4 bytes)：-2^31 (-2147483648) 到 2^31-1 (2147483647)
* bigint(8 bytes)：-2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807)

不同的型态存在资料库的容量大小会不同，因此在设计上需要先估算此张表写入的资料量大小，再决定使用什么型态，而不是一昧的使用最大型态。
以纪录 album 来说，可能就需要用到 bigint ，但如果只是纪录某种 type ，也许 tinyint 就已足够。

以范例来说，status 可以改成：

`status` tinyint(4) DEFAULT 0,

Notice：如果只是流水号，一定不会用到负数，可以利用 unsigned 属性增加流水号的正数区间。

字串的设计

字串储存使用的是 char 或 varchar 的型态，两者差异是 char 使用固定长度空间来储存，如果字串长度不足，还是会自动存满；而 varchar 可以允许可变长度的字串，存入字串后会再使用 1 byte 来标识字串的长度。

存入字串	Char(5)	Varchar(5)
‘te’	5 bytes	3 bytes
‘tes’	5 bytes	4 bytes
‘kkbox’	5 bytes	6 bytes

名字相关不确定长度的资讯，可以使用 varchar ，并预估可能需要的长度：

`title_text` varchar(200) NOT NULL COMMENT 'artist role name',

确定长度的字串，例如 script_code 就可以使用 char 来存。

Notice：VARCHAR 超过 255 的话, 标识字串长度需要用到 2 bytes。

Notice：储存大小会根据编码决定，utf8 编码要 * 3，utf8mb4 要 * 4。

COLLATE 设定不要写在 column

CREATE TABLE 时就应该设定 COLLATE=utf8mb4_unicode_ci，不需要再针对 column 做 COLLATE 的设定。
主要原因是当未来 DBA 需要协助调整 COLLATE 设定时，还要再进 column 改一次，会增加调整的成本。

Notice：除非栏位有特殊编码需求，不然统一都设定在 Table。

DEFAULT ”

除了 Laravel ORM Model 会使用到的 updated_at 可以 DEFAULT NULL 之外，其他栏位如果非必填，建议都是以预设空字串或 0 为主。

`language_code` char(4) DEFAULT '',
`status` tinyint(4) DEFAULT 0,

原因是 InnoDB 会把需要的空间画进去 data page (16K) 里面，但是 Null 只是一个 flag ，当 NULL 被写入值的时候，需要把整笔记录搬一个新的位置，会造成 Data fragmentation。

建立索引（Index）

MySQL 中，索引都是以 B+Tree 的方式储存，这种结构可以在查询时针对键值快速找出资料。
因此，如果 Table 中会被拿来当搜寻依据的特定栏位，都需要加上索引（Index）。

提升索引（Index）效率的资料型态：
* 小：能用 varchar(5) 就不要用 varchar(255)
* 简单：Int > Char > Varchar > Text
* 尽量不用 NULL

根据 B+Tree 的原理，索引（Index）设计的长度也会影响搜寻的速度及建立的空间大小，如果遇到字串需要设定索引（Index），必须思考多少长度的索引（Index）范围可以含盖到最多资料。
以 album_meta_name_raw 的 title_text (其实就是 album_name) 为例，取前 20 个字就可以涵盖到大部份的名字。

DBA 的测试资料：

mysql> SELECT count(distinct(title_text)) AS n_unique, count(distinct(LEFT(title_text, 100))) AS n100,
    -> count(distinct(LEFT(title_text, 20))) AS n20, count(distinct(LEFT(title_text, 15))) AS n15,
    -> count(distinct(LEFT(title_text, 12))) AS n12, count(distinct(LEFT(title_text, 10))) AS n10,
    -> count(distinct(LEFT(title_text, 5))) AS n5 FROM song_meta_name_raw;
+----------+----------+----------+----------+---------+---------+---------+
| n_unique | n100     | n20      | n15      | n12     | n10     | n5      |
+----------+----------+----------+----------+---------+---------+---------+
| 16699739 | 16684589 | 13906368 | 11993071 | 9727458 | 7555408 | 1476038 |
+----------+----------+----------+----------+---------+---------+---------+
1 row in set (27 min 1.61 sec)

根据上面测试资料显示，title_text 的索引（Index）设在 20 是一个在效益上相对平衡的数字，根据范例 title_text 可以修改成：

KEY `btree__title_text` (`title_text`(20))

MySQL DB Schema 设计原则 – 佛祖球球