本章關(guān)注的是MySQL數(shù)據(jù)庫的設(shè)計, 主要介紹的是MySQL數(shù)據(jù)庫設(shè)計和其他關(guān)系型數(shù)據(jù)庫管理系統(tǒng)的區(qū)別.

選擇優(yōu)化的數(shù)據(jù)類型

幾個原則: 更小的通常更好, 簡單就好, 盡量避免NULL

先選擇大類型: 數(shù)字, 字符串, 時間. 在選擇具體類型.

DATETIME和TIMESTAMP: 都精確到秒. DATETIME與時區(qū)無關(guān), TIMESTAMP只使用DATETIME一半的空間, 并會隨時區(qū)變化, 且具有自動更新的能力. 不過TIMESTAMP時間范圍較小.

INTEGER, BOOL, NUMERIC只是別名. SHOW CREATE TABLE檢查能看到其基本類型.

整數(shù)類型

以上類型決定如何在內(nèi)存和磁盤中存儲, 但是計算時一般會使用64位的BIGINT.
MySQL 可以為整數(shù)類型指定寬度，如 INT(11)，對大多數(shù)應(yīng)用這是沒有意義的：它不會限制值的合法范圍。這是規(guī)定了 MySQL 的一些交互工具用來顯示字符的個數(shù)。對于存儲和計算來說，INT(1) 和INT(20) 是相同的.

實(shí)數(shù)類型

FLOAT(4個字節(jié)), DOUBLE(8個字節(jié))支持存儲小數(shù), 支持標(biāo)準(zhǔn)的浮點(diǎn)近似計算(使用的是平臺浮點(diǎn)數(shù)的具體實(shí)現(xiàn), 計算上沒有區(qū)別).
DECIMAL不僅支持存儲小數(shù), 還支持存儲比BIGINT大的整數(shù), 且支持精確計算(5.0或以上版本使用的是MySQL服務(wù)器自身的高精度計算, 因此比CPU自身的浮點(diǎn)數(shù)計算要慢).
DECIMAL(18,9)意思是小數(shù)點(diǎn)左右兩邊各存9個數(shù)字, 最多支持65個數(shù)字, 隨數(shù)字增加, 存儲空間上升.
盡量用FLOAT或DOUBLE, 財務(wù)數(shù)據(jù)才用DECIMAL, 可以轉(zhuǎn)化為分用BIGINT.

字符串類型

從MySQL4.1 版本開始 ，CHAR(n), VARCHAR(n) 中的n 指字符長度，不再表示之前版本的字節(jié)長度。也就是說在不同字符集下，char類型列的內(nèi)部存儲可能不是定長數(shù)據(jù).
也就是說對于多字節(jié)字符集編碼，CHAR類型不再代表固定長度的字符串, innodb存儲引擎在內(nèi)部將其視為變長字符類型.

VARCHAR

VARCHAR適合存儲可變長的字符串, 它比CHAR節(jié)省空間. 但由于行是變長的, 在 UPDATE 一個可變長字符串時，如果行所占空間增長，并且在頁內(nèi)沒有更多空間存儲，在MyISAM會將行拆成不同的片段存儲，在 InnoDB 進(jìn)行頁分裂來使行可以放進(jìn)頁內(nèi).
VARCHAR需要使用1或者2個額外字節(jié)記錄字符串的長度：如果列的最大長度小于255字節(jié)，則只使用1個字節(jié)，否則使用2個字節(jié).
適用場景:

• 字符串最多長度比平均長度大很多;
• 更新很少, 因此碎片不是問題
• 使用UTF8這樣的字符集, 每個字符使用了不同的字節(jié)數(shù)存儲

CHAR

CHAR 類型是定長的，MySQL 總是根據(jù)定義的字符串長度分配足夠的空間。當(dāng)存儲 CHAR 值時，MySQL 會刪除所有的末尾空格.

適用場景:

• MD5值
• CHAR(1)存儲Y和N

# length()是字節(jié)長度
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在內(nèi)存中會使用定長來保存內(nèi)部值. 最好的策略是只分配真正需要的空間.

BINARY 和 VARBINARY

BINARY 和 VARBINARY 存儲二進(jìn)制字符串。它們用于保存二進(jìn)制字符串，存儲的是字節(jié)碼.

當(dāng)需要存儲二進(jìn)制數(shù)據(jù)，并且希望使用字節(jié)碼而不是字符進(jìn)行比較時。字節(jié)比較的優(yōu)勢并不僅僅體現(xiàn)在大小寫敏感上，在進(jìn)行比較BINARY字符串時，每次按一個字節(jié)，并且根據(jù)該字節(jié)的數(shù)值比較。因此二進(jìn)制比較比字符比較簡單的多，效率也高.

BLOB和TEXT類型

BLOB 和 TEXT 都是為存儲很大的數(shù)據(jù)而設(shè)計的字符串?dāng)?shù)據(jù)類型，分別采用二進(jìn)制和字符方式存儲. InnoDB在它們特別大的時候會使用單獨(dú)的外部存儲區(qū)域來保存他們，每個值在行里使用1到4個字節(jié)存儲指針，并且還需要足夠的存儲空間來保存實(shí)際的值.

它們只對每個列的最前 max_sort_length 字節(jié)而不是整個字符串做排序。如果只需要排序前面一小部分字符，則可以減少 max_sort_length 的配置，或者使用 ORDER BY SUBSTRING(column，length).

枚舉值ENUM

枚舉值在MySQL中保存為整數(shù), 此外還要存儲2個部分(不重復(fù)的字符串存儲為一個預(yù)定的集合, 以及"數(shù)字-字符"串映射關(guān)系"查找表").

枚舉字段是按照內(nèi)部存儲的整數(shù)而不是定義的字符串進(jìn)行排序的，解決方式:
第一種是：按照需要的順序來定義枚舉列。
第二種解決方式是：在查詢中使用 FIELD() 函數(shù)顯式地指定排序順序，但這會導(dǎo)致 MySQL 無法利用索引消除排序.

優(yōu)點(diǎn): 所消耗的存儲較小.
缺點(diǎn): a.字符串列表是固定的, 添加或者刪除字符串必須使用ALTER TABLE. 除非可以接受只在列表末尾添加元素, 這樣mysql 5.1就可以不用重建這個表. b.由于每個枚舉值被存儲為整數(shù), 因此查詢是必須通過映射表, 因此把CHAR/VARCHAR列與枚舉列進(jìn)行關(guān)聯(lián)可能會比直接兩個CHAR/VARCHAR列關(guān)聯(lián)更慢.

時間和日期類型

MySQL最小時間粒度為秒(MariaDB支持微秒)

DATETIME

這個類型能保存大的范圍值，從 1001 年到 9999 年，精度為秒，它把日期和時間封裝到一個格式為 YYYYMMDDHHSSMM 的整數(shù)中，與時區(qū)無關(guān)，它使用了 8 個字節(jié)的存儲空間。在默認(rèn)情況下， MySql 以一種可排序的、清楚的格式來顯示 DATETIME 值，例如"2012-05-22 22:35:01".

TIMESTAMP

TIMESTAMP顯示值也依賴于時區(qū).
在默認(rèn)情況下，如果插入的行沒有設(shè)置 TIMESTAMP 列的值， MySql 會把它設(shè)置為當(dāng)前系統(tǒng)時間，在更新的時候，如果沒有顯示的更新 TIMESTAMP 列的值， MySql 也會以當(dāng)前時間來自動更新.

如果要存儲比秒更小粒度的日期和時間值，可以使用 BIGINT 類型存儲微秒級別的時間戳或者使用 DOUBLE 存儲秒之后的小數(shù)部分, 或使用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;  --當(dāng)前時間減1小時6分鐘

位數(shù)據(jù)類型

BIT

BIT(2)存儲2個位, 最大64位. 盡量不用該類型.

SET

適合做權(quán)限控制:

mysql> create table acl_set (
name varchar(100) not null  comment '姓名',
perms set('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') not null comment '權(quán)限'
);

mysql> insert into acl_set (name, perms) values ('楊愷', 'CAN_READ,CAN_WRITE'), ('王洋', 'CAN_DELETE');
mysql> select * from where find_in_set('CAN_WRITE', perms);  --查詢誰擁有讀權(quán)限

+--------+--------------------+
| name   | perms              |
+--------+--------------------+
| 楊愷   | CAN_READ,CAN_WRITE |
+--------+--------------------+

也可以用整數(shù)TINY來做權(quán)限控制(這里使用了存儲過程, 也可以使用應(yīng)用程序來操作位):

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; --查詢誰擁有讀權(quán)限

+-------+
| perms |
+-------+
|     5 |
+-------+

優(yōu)點(diǎn): 存儲消耗小.
缺點(diǎn): 改變列的定義代價高, 需要ALTER TABLE, 也不支持在該列通過索引查找.

選擇標(biāo)識符

為標(biāo)識列選擇適合的數(shù)據(jù)類型非常重要。一般更有可能用標(biāo)識列與其他值進(jìn)行比較，或者通過標(biāo)識列尋找其他列。標(biāo)識列也可能在另外表中作為外鍵使用。所以為標(biāo)識列選擇數(shù)據(jù)類型時，應(yīng)該選擇跟關(guān)聯(lián)表中對應(yīng)列一樣的類型, 類型間需要精確匹配, 包括UNSIGNED這樣的屬性.

原則: 在預(yù)留未來增長空間的前提下, 選擇最小的數(shù)據(jù)類型.
盡量使用整數(shù)類型, 避免使用字符串類型

當(dāng)心框架自動生成的schema: 會有嚴(yán)重的性能問題, 因此國內(nèi)大多數(shù)都用的mybatis, 而非JPA.

IP地址

盡量使用無符號整數(shù)(如bigint unsigned)存儲, 而非字符串.

mysql> select 
 --最好使用無符號整數(shù)存儲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          |
+-----------------------+

要想得知某個變量或函數(shù)查詢結(jié)果的類型, 新建一張臨時表即可:

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設(shè)計中的陷阱

太多的列, 太多的關(guān)聯(lián), 全能ENUM, 錯誤使用的SET, 過多的NULL

關(guān)于NULL:
盡量設(shè)置列為NOT NULL DEFAULT xxx, 不過也不要走極端, 當(dāng)確實(shí)需要表示未知值時, 也不要害怕使用NULL.

范式和反范式

范式

優(yōu)點(diǎn):

• 范式化的更新操作比反范式更快(寫密集場景)
• 當(dāng)數(shù)據(jù)較好地范式化時，很少有重復(fù)數(shù)據(jù)，只需要修改更少的數(shù)據(jù)
• 范式化的表更小，可更好地放到內(nèi)存里，執(zhí)行操作更快
• 很少冗余數(shù)據(jù)，檢索列表數(shù)據(jù)時更少需要distinct、group by語句

缺點(diǎn)：

• 查詢常需要1次或更多次關(guān)聯(lián)
• 會使一些索引策略無效, 例如范式化可能將列放在不同的表中, 而如果它們在同一個表中本可以屬于同一個索引

反范式

優(yōu)點(diǎn):

• 所有數(shù)據(jù)都在一張表中, 可以避免關(guān)聯(lián)
• 及時是全表掃描, 也比關(guān)聯(lián)快, 如果用到索引則更快

混用范式和反范式

在不同表中存儲相同的特定列, 緩存一些數(shù)據(jù)到某列等等

緩存表和匯總表

• 緩存表: 存儲那些可以比較簡單地從schema其他表獲取(但每次獲取速度比較慢)數(shù)據(jù)的表
• 匯總表: 保存使用group by語句聚合數(shù)據(jù)的表

匯總表的例子: 統(tǒng)計網(wǎng)站之前24小時內(nèi)發(fā)送的消息數(shù), 可以每小時生產(chǎn)一條匯總表的記錄. 或者計算最活躍的用戶/最長久的標(biāo)簽, 由于經(jīng)常做update, 因此沒辦法在其上建索引, 因此查詢很慢, 此時就可以用匯總表來保存.

2種表都必須決定是: 實(shí)時維護(hù)還是定期重建.

以下"影子拷貝"可以保證重建表時的可用性:

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表保持老數(shù)據(jù), 可用于故障回滾.

物化視圖

預(yù)先計算并存在磁盤上的表，可通過各種策略刷新和更新，mysql不原生支持，可使用Justin Swanhart工具flexviews實(shí)現(xiàn);

flexviews組成： 變更數(shù)據(jù)抓取，讀取服務(wù)器二進(jìn)制日志且解析相關(guān)行的變更; 一系列可以幫助 創(chuàng)建和管理 視圖 的定義 的 存儲過程; 一些可應(yīng)用變更到 數(shù)據(jù)庫中的物化視圖 的工具
flexviews通過提取對源表的更改，可增量地重新計算物化視圖的內(nèi)容：不需要查詢原始數(shù)據(jù)來更新視圖.

• 注: mysql的視圖不是物化視圖, 而是表查詢, 因此效率低下. 物化視圖則是一種可以提高性能的方案.

計數(shù)器表

方案: 建立一張獨(dú)立的表存儲計數(shù)器.

假設(shè)有個計數(shù)器，只有一行數(shù)據(jù)，記錄網(wǎng)站的點(diǎn)擊次數(shù)：

CREATE TABLE hit_counter(
    cnt int unsigned not null
) ENGINE=InnoDB;

網(wǎng)站的每次點(diǎn)擊都會導(dǎo)致對計數(shù)器進(jìn)行更新：

UPDATE hit_counter SET cnt = cnt + 1;  

這種操作是原子的, 但是會有全局的行互斥鎖, 不適合高并發(fā).

為了更高的并發(fā)性，可將計數(shù)器保存在多行，每次隨機(jī)選一行更新，要統(tǒng)計結(jié)果時，聚合查詢；對表結(jié)構(gòu)進(jìn)行修改：

CREATE TABLE hit_counter(
    slot tinyint unsigned not null primark key,
    cnt int unsigned not null
) ENGINE=InnoDB;

然后預(yù)先在這張表增加100行數(shù)據(jù)?，F(xiàn)在選擇一個隨機(jī)槽（slot）進(jìn)行更新, 這樣并發(fā)度就提高了100倍.

UPDATE hit_counter SET cnt = cnt + 1 where slot =RAND() * 100;

要獲取結(jié)果，只需統(tǒng)計所有行

SELECT SUM(cnt) FROM hit_counter;

如每隔一段時間重新開始一個新的計數(shù)器 如每天一個，對表結(jié)構(gòu)進(jìn)行修改：

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;

這個場景則不能預(yù)先生成行了，而用 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的操作的是: 用新的結(jié)構(gòu)創(chuàng)建空表, 從舊表中查出all數(shù)據(jù)插入新表，刪除舊表. 因此花費(fèi)時間很長, 導(dǎo)致服務(wù)終端.

目前成熟的方案有:

1. 先在一臺不提供服務(wù)的機(jī)器上執(zhí)行alter table操作，然后和提供服務(wù)的主庫進(jìn)行切換
2. 影子拷貝: 見上文

修改默認(rèn)值時alter column比modify column快: 前者只去更改.frm文件而無需涉及表數(shù)據(jù).

總結(jié)

1. 避免過度設(shè)計, 例如會導(dǎo)致及其復(fù)制查詢的schema設(shè)計, 或很多列的表的設(shè)計.
2. 使用小而簡單的數(shù)據(jù)類型, 盡量避免使用NULL值.
3. 盡量使用相同數(shù)據(jù)類型做列的關(guān)聯(lián).
4. 盡量使用整形定義標(biāo)識列.
5. 避免使用已經(jīng)遺棄的特性, 如指定浮點(diǎn)數(shù)的精度, 或整數(shù)顯示的寬度.
6. 不哦濫用ENUM和SET, 避免使用BIT.