日期：2023年 09月 02日

標籤： MySQL DataBase

摘要：效能議題

第一部分：Mysql官方結論

Step 1：官方結論重點

以下是Mysql官網utf8mb3與utf8mb4說明連結
看重點框選的地方，翻譯成中文大意就是：
Mysql官方建議使用utf8mb4，捨棄utf8mb3
並且在未來Mysql 8以後的版本預設用utf8mb4

Step 2：探討官方結論-優缺點

下表是兩者的優缺點
如果要開發多國語言、Emoji、Mysql版本大於8.0.28、未來擴展性等等，都確定用utf8mb4叫好

Step 3：探討官方結論-提出問題

官方雖然建議用utf8mb4，但可以發現utf8mb3有空間優勢
utf8mb3 占用字符集為 1-3 字節表示一個字
utf8mb4 占用字符集為 1-4 字節表示一個字
因此我們可以判斷 utf8mb4 一定比 utf8mb3 多花空間，問題是用utf8mb3可以省多少呢?

第二部分：占用空間比較

Step 1：空間比較前言

空間使用率的比較，我們建立4張表，2種對照
對照組1：表內有30000筆[文字]資料的情況下，各自占用多少空間

對照組2：表內有30000筆[沒有文字]的情況下，各自占用多少空間

Step 2：有文字表比較-建表

建立以下兩張表
utf8mb3帶文字：

CREATE TABLE `UseUtf8mb3` (
  `UseUtf8mb3Id` INT NOT NULL AUTO_INCREMENT,  
  `JunkMessage` VARCHAR(1000) NOT NULL COMMENT '',
  PRIMARY KEY (`UseUtf8mb3Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3 COMMENT='填充訊息utf8mb3表';

utf8mb4帶文字：

CREATE TABLE `UseUtf8mb4` (
  `UseUtf8mb4Id` INT NOT NULL AUTO_INCREMENT,
  `JunkMessage` VARCHAR(1000) NOT NULL COMMENT '',
  PRIMARY KEY (`UseUtf8mb4Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='填充訊息utf8mb4表';

Step 3：有文字表比較-塞資料代碼

以下是C#代碼，我們產生500個字的變數，重複塞入30000萬筆資料到2張表

//1. Class Model
public class useUtf8Class
{
    public string JunkMessage { get; set; }
}

//2. 用Dapper 塞資料到 Mysql
public void AddJunkMessages()
{
    var datas = new List<useUtf8Class>();
    string randomString = GenerateRandomString(500);
    //3萬筆
    for (int index = 0; index < 30000; index++)
    {              
        datas.Add(new useUtf8Class() { JunkMessage = randomString });
    }
    var sql = $@"
INSERT UseUtf8mb3(JunkMessage)
VALUE(@JunkMessage);

INSERT UseUtf8mb4(JunkMessage)
VALUE(@JunkMessage);
";
    _unitOfWork.Master.Execute(sql, datas);
}
";

Step 4：有文字表比較-資料表內容

塞入資料後 UseUtf8mb3 的內容如下：

塞入資料後 UseUtf8mb4 的內容如下：

Step 5：有文字表比較-比較空間

UseUtf8mb3 表使用空間為：8.52MB

SELECT ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'UseUtf8mb3 使用量 (MB)'       
FROM information_schema.TABLES
WHERE table_name = 'UseUtf8mb3'

UseUtf8mb4 表使用空間為：14.52MB

SELECT ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'UseUtf8mb4 使用量 (MB)'       
FROM information_schema.TABLES
WHERE table_name = 'UseUtf8mb4'

Step 6：有文字表比較-結論

如果表 “都只有字串” 的情況下，空間節省效率為 表使用空間為：8.52 / 14.52 約為58%
但實務上很多資料表的欄位大多都不會只有字串

Step 7：無文字表比較-建表

這次考量 “不用文字” 的狀況下，會有多少空間差異，文字欄位改用Int保存，建立以下兩張表
utf8mb3 無文字欄位：

CREATE TABLE `UseUtf8mb3_INT` (
  `UseUtf8mb3Id` INT NOT NULL AUTO_INCREMENT,  
  `JunkInt` INT NOT NULL COMMENT '',
  PRIMARY KEY (`UseUtf8mb3Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3 COMMENT='填充訊息utf8mb3只有INT表';

utf8mb4 無文字欄位：

CREATE TABLE `UseUtf8mb4_INT` (
  `UseUtf8mb4Id` INT NOT NULL AUTO_INCREMENT,
  `JunkInt` INT NOT NULL COMMENT '',
  PRIMARY KEY (`UseUtf8mb4Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='填充訊息utf8mb4只有INT表';

Step 8：無文字表比較-塞資料代碼

以下是C#代碼，我們產生數值都是1234567的變數，重複塞入30000萬筆資料

//1. Class Model
public class useUtf8Class
{
    public int JunkInt { get; set; }
}

//2. 用Dapper 塞資料到 Mysql
public void AddJunkMessages()
{
    var datas = new List<useUtf8Class>();
    //3萬筆
    for (int index = 0; index < 30000; index++)
    {              
        datas.Add(new useUtf8Class() { JunkInt = 1234567});
    }
    var sql = $@"
INSERT UseUtf8mb3_INT(JunkInt)
VALUE(@JunkInt);

INSERT UseUtf8mb4_INT(JunkInt)
VALUE(@JunkInt);
";
    _unitOfWork.Master.Execute(sql, datas);
}
";

Step 9：無文字表比較-資料表內容

塞入資料後 UseUtf8mb3_INT 的內容如下：

塞入資料後 UseUtf8mb4_INT 的內容如下：

Step 10：無文字表比較-比較空間

UseUtf8mb3_INT 表使用空間為： 3.52MB

SELECT ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'UseUtf8mb3 使用量 (MB)'       
FROM information_schema.TABLES
WHERE table_name = 'UseUtf8mb3_INT'

UseUtf8mb4_INT 表使用空間為： 3.52MB

SELECT ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'UseUtf8mb4 使用量 (MB)'       
FROM information_schema.TABLES
WHERE table_name = 'UseUtf8mb4_INT'

Step 11：無文字表比較-結論

如果表”沒有文字”的情況下，空間節省效率為 0% => 表使用空間為：3.52 / 3.52 = 1

第三部分：成本價值比較

Step 1：計算方式

因此決定能壓縮多少空間就看char、varchar等欄位，其他可以不計
假設資料庫全部都是utf8mb4，改為全都是utf8mb3可以解省的空間簡單計算如下：
使用空間值 * [(文字欄位) / 找出資料庫所有欄位] * (1 - 0.58) = 節省的空間

Step 2：示範計算 - SQL查詢

我們有一個資料庫，是專門存放報表資料的，
下達以下SQL語法，得出以下數值

SELECT SUM(CASE WHEN DATA_TYPE IN ('varchar', 'char', 'text') THEN 1 ELSE 0 END) AS text_columns,
       SUM(CASE WHEN DATA_TYPE NOT IN ('varchar', 'char', 'text') THEN 1 ELSE 0 END) AS non_text_columns
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = 'Report'

Step 3：示範計算 - 節省空間比例

為了便於計算我們將數值調整如下：
依照 => [(文字欄位) / 找出資料庫所有欄位]
計算出 => (2000 / 32000) => 0.0625%
這個範例資料庫如果使用utf8mb3 可以省下6%的空間

Step 4：示範計算 - 1G資料節省空間

計算方式，假設資料庫使用空間為 1000MB ， 在範例報表庫可以得到節省26.25MB
一個全都是utf8mb4 的報表資料庫，全部轉為utf8mb3，在1G的資料庫中我們可以省26.25MB
※計算值 1000 * 0.0625 * 0.42 = 26.25 MB

使用空間值 * [(文字欄位) / 找出資料庫所有欄位] * (1 - 0.58) = 節省的空間

Step 5：示範計算 - 硬碟價格

目前一顆SSD 1TB的硬碟為1200元，每1MB約 0.12元
因此26.5MB 價格約 3.18元
※findprice 網站，價格由低至高

Step 6：示範計算 - 勞工薪資

目前中華民國勞工薪資最低所得為176/HR NT，月薪26400元
因此如果軟體工程師時薪176，至少要能省1460MB的空間，才有將utf8mb4優化為utf8mb3的價值

Step 7：示範計算 - 價格對照

基於硬碟、人力成本來考量，如果今天公司想要將資料庫所有表utf8mb4優化為utf8mb3
如下圖(範例報表庫換算)：
※最低工資時薪換算：表示請員工做這件事的成本，超過這個時間就表示賠錢了

Step 8：結論 - 請用utf8mb4

我的範例資料庫總共只用了11.18 MB左右，全部預設都utf8mb4就好了。
如果還每張表判斷是否用utf8mb3，必要的用utf8mb4節省空間，根本浪費時間，浪費金錢
優化這個庫將所有table優化為 utf8mb3 執行這項工作，超過10秒就賠錢
※除非海量數據以京為單位、或者大多只使用”文字欄位”的資料庫。
※Mysql官方的建議，已經符合未來趨勢(語系增加、空間低廉)，請用utf8mb4

第四部分：Unicode的發展近況

Unicode官網
最近一次更新的版本是2022/09/13 15.0版本
大意是說：新增幾個語言、支援新的表情符號，有持續加新的碼
如果系統要走進國際化(翻譯多國語言)，建議聽從官方建議使用utf8mb4，捨棄utf8mb3