[MySQL] schema 與資料類型優化

選擇優化的資料類型

原則:

• 越小越好
• 因為會佔用最小的磁碟、記憶體與CPU快取。
• 越簡單越好
• 操作整數比操作字串代價低，因為字串排序規則
• ex:
• 儲存ip應該用 unsigned int - 4 bytes, 用 varchar 會用到 15 bytes
• 儲存時間應該用 mysql 內建的時間類型
• DATETIME, TIMESTAMP可以儲存相同資料：時間日期，精確到秒
• TIMESTAMP 只用 DATETIME 一半的儲存空間，根據時區自動變化
• TIMESTAMP 允許的時間範圍比 DATETIME 小很多
• 避免 NULL
• 在 innodb 上有單獨的 bit 儲存 null 值，但在 myisam，但還是盡量避免

整數類型

• tinyint - 8 bits
• smallint - 16 bits
• mediumint - 24 bits
• int - 32 bits
• bigint - 64 bits
• 如果是有正負數的，會因為 1 bits 會用來記錄正負，故範圍為 -2^(n-1) ~ 2^(n-1)
• unsigned 的範圍則是 -2^n ~ 2^n
• MySQL 可以為整數指定寬度，例如 INT(11)，其實沒什麼意義，11只是表示與 MySQL 相關工具(client 端的 cli) 用來顯示的長度，對於儲存與計算來說，INT(1)與INT(20)是一樣的。

實數類型

decimal(M,N) - M: 最大位數, N: 小數點後有幾位
ex: decimal(18, 9) => 9位數字.9位數字，總共使用 9 個 bytes

float => 4 bytes

double => 8 bytes

decimal的計算雖然比較精確，但需要額外，但需要額外的空間與計算開銷，資料量較大時，可以用 bigint 代替 decimal，假設需要精準到萬分之一，則可以將數字*1百萬，將結果存在 bigint 內，可以同時避免福點數計算不精準與 decimal 計算代價太高。

字串類型

VARCHAR

row_format=fix，這樣就都會固定長度，沒意義

varchar會需要額外1~2 bytes記錄長度,

假設用 latin1 charset, varchar(10) => 11 bytes, varchar(1000) => 1002 bytes

varchar 適合使用情境

儲存資料的字串長度比平均字串長度大很多，或是像使用了 utf8 這種複雜的 charset，每個字都用不同的 bytes 進行儲存

InnoDB會把過長的 varchar 轉成 blob

CHAR

適合儲存很短的字串或是所有字串的長度接近同個值，在儲存效率上也比 VARCHAR 好。

ex: 用 CHAR(1) 存 Y or N 只需要用1個 byte，但 varchar 會用到2個 bytes

BINARY 與 VARBINARY

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they store binary strings rather than nonbinary strings. That is, they store byte strings rather than character strings. This means they have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in the values.

binary 跟 varbinary 存的是二進制的值, 所以 binary(10) => 固定 10 bytes

二進制資料的優勢在大小與 case sensitive 上，由於是根據由於是根據 byte 上的數值進行比較，所以會比比較字串的行為來的快

BINARY 如果有設定幾位，如果長度不足，MySQL 會補 \0 補到需要的長度

BLOB 與 TEXT

BLOB 與 TEXT 專門設計來儲存很大的字串，BLOB 為二進制，TEXT 則是字串

TEXT 就是 SMALLTEXT

BLOG 就是 SMALLBLOB

TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT

TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB

當 BLOB or TEXT 太大時，MySQL 會用外部儲存區域來存值，此時在資料內需要 1~4 bytes 去存放外部儲存區域的指針

MySQL 對於 BLOB, TEXT 的排序跟其他形態不同，它只對每列最前 max_sort_length bytes 去排序，而不是整個字串的值去排序，如果只需要排序前面一小部分的字串，可以減少 max_sort_length 的設定，或著使用 ORDER BY SUBSTRING(column, length)

MySQL 不能將 BLOB, TEXT內的值字串進行索引。

如果 explain 的 extra 包含 Using Temporary => 這個查詢用了隱式臨時表

使用 ENUM 代替字串類型

有時候可以把常用且不常變動的字串定義成 enum，在 MySQL 內部, 會將 enum 存為存為整數，並且在 table 的 .frm 這個檔案中存著"數字-enum字串" mapping 關係的查找表，且 enum 的順序是當初在建立的時候就定義好了，也就是按照當初建立時，內部整數表的大小去排序。

ENUM關聯VARCHAR或是CHAR會很慢，只有ENUM跟ENUM做關聯才會快

日期與時間類型

DATETIME

可以從西元1001年存到9999年，精準到秒。與時區無關，用 8 bytes 的儲存空間。

TIMESTAMP

西元1970年存到2038年，與時區相關，用 4 bytes 的儲存空間。

預設為 NOT NULL。

如果需要儲存如果需要儲存比秒更小粒度，可以用 BIGINT 儲存微秒，或是用 DOUBLE 儲存

秒之後的小數點！(MariaDB 支援到微秒)

Bit-Packed 資料類型

Bit 在 Memory or InnoDB 是用能夠容納指定 bits 最小的整數類型，所以根本不能節省儲存空間。

MySQL 把 bit 視為字串類型，不是數字類型。當你取出 BIT(1) 的值，你會取得一個字串，但這個字串是二進位的 0 or 1，不是 ASCII CODE 的 0 or 1。

舉例來說，假設你把 b'00111001' (也就是10進位的57)，存到BIT(8)並搜尋出來，你會拿到是字串 code 57，也就是 ASCII code 的 9

假設想做一個權限的表，可以用 bit operation 這種方式來實作

選擇 ID 的類型

如果已經選定好 id 的型態，在與這個 id 的所有關聯中，關聯的欄位必須跟這個 id 一模一樣。

ex: 如果這個 id 有 unsigned, 那關聯用的欄位也要有 unsigned. 不然會造成效能的問題。

在可以滿足值的範圍需求，且保留未來增長空間的情況下，應選擇最小的資料類型。

ex: state_id 儲存

如果可以，盡量不要用字串當成 id ，因為很耗空間外，通常還比數字類型的 ID 慢。

尤其是 myisam 使用字串當 id 要小心，因為預設會字串壓縮索引，會導致效能下降大約6倍！

如果用 UUID 當成 id, 比較好的做法是用 unhex() 轉換 UUID 的值轉成 16 bytes 的數字，並存在 BINARY(16) 中，如果要查詢的時候，再使用 hex() 格式化 16 進位。

特殊數據類型

IPV4通常會用VARCHAR(15)來存，但 ipv4 本來就是 32 bits unsigned integer, 所以其實可以直接用 unsigned integer 去存，MySQL 有 inet_aton(), inet_ntoa() 來轉換。

MySQL Schema 設計中的陷阱

1. 太多的欄位
MySQL engine API 是透過複製在伺服器與MySQL engine的 row buffer格式，伺服器再將 row buffer 轉成我們在用的 columns。但是從 row buffer 轉成 column 需要蠻高的成本。

MyISAM 的 fixed row 格式剛好跟伺服器的 row 格式一樣，所以不需要轉換。
但是 MyISAM 的 vairable row 格式與 InnoDB 的 row 格式就都需要轉換。
轉換的代價是看有多少 column 而定。
假如說有張表有幾百個 columns, 即便你只有用到其中幾個，還是會消耗許多CPU資源。

2. 太多 join

MySQL有個限制 - 一個 join 最多只能有 61 個 table. 即便少於 61 個, 解析與優化查詢 (planning and optimizing the query)也會成為 MySQL 的問題！

加快 ALTER TABLE 的速度

1. 先在一台不提供服務的機器上執行 ALTER TABLE 的操作，然後跟提供服務的 master 進行切換。
2. shadow copy: 把舊表的結構拿來建立一張新表，透過 rename 與 drop 來交換兩張表

3. 有些工具可以做到上述第二點
 - fb 維護的 online schema change

 - Shlomi Noach’s openark toolkit

 - Percona toolkit

MySQL而言，不是所有 ALTER TABLE 都會讓表重新建立。
可能假設我要修改一個欄位的預設值，
ex:
ALTER TABLE sakila.film -> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

用 SHOW STATUS; 看這個語法執行的行為，會發現這個語法做了1000次讀與1000次寫入。
MODIFY COLUMN 會拷貝整張表到一張新表 (慢)
ALTER COLUMN 則是直接修改 .frm 這個檔案 (快)