最近因需求改動新增了一些資料庫表，但是在定義表結構時，具體列屬性的選擇有些不知其所以然，索引的新增也有遺漏和不規範的地方，所以我打算為建立一個高效能表的過程以實戰的形式寫一個專題，以此來學習和鞏固這些知識。

1. 實戰

我使用的 MySQL 版本是 5.7，建表 DDL 語句如下所示：根據需求建立介面呼叫紀錄檔資料庫表，請大家瀏覽具體欄位的屬性資訊，它們有不少能夠優化的點。

CREATE TABLE `service_log` (
  `id` bigint(100) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `service_type` int(10) DEFAULT NULL COMMENT '介面型別',
  `service_name` varchar(30) DEFAULT NULL COMMENT '介面名稱',
  `service_method` varchar(10) DEFAULT NULL COMMENT '介面方式',
  `serial_no` int(10) DEFAULT NULL COMMENT '訊息序號',
  `service_caller` varchar(15) DEFAULT NULL COMMENT '呼叫方',
  `service_receiver` varchar(15) DEFAULT NULL COMMENT '接收方',
  `status` int(3) DEFAULT '10' COMMENT '狀態 10-成功 20-異常',
  `error_message` varchar(200) DEFAULT NULL COMMENT '異常資訊',
  `message` text DEFAULT NULL COMMENT '報文內容',
  `create_user` varchar(50) DEFAULT NULL COMMENT '建立者',
  `create_time` datetime NOT NULL COMMENT '建立時間',
  `update_user` varchar(50) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime NOT NULL COMMENT '更新時間',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '刪除標誌',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '時間戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='介面呼叫紀錄檔';

我會在下文中將其中包含的問題和可以進行優化的地方一一進行解釋，主要參考的書目是《高效能MySQL 第四版》，也希望大家有精力去看原書。

2. 優化和改進

慷慨不是明智的

一般來說，要儘量使用能夠正確儲存和表示資料的最小資料型別，更小的資料型別通常更快，因為它們佔用的磁碟、記憶體和CPU快取的空間更少，並且處理時需要的CPU週期也更少。但是，這也要確保沒有低估需要儲存的值的範圍，否則會因入庫失敗而造成資料丟失，而且表結構修改的流程審批也很麻煩。

我們以表中id和message列為例來說：

id為主鍵列，它使用的是整數型別 BIGINT(64位元)，除此之外還有 TINYINT(8位元)、SMALLINT(16位元)、MEDIUMINT(24位元) 和 INT(32位元)，可以儲存的取值範圍是從 -2(N - 1)到 2(N - 1)- 1，所以 BIGINT 型別值的最大值是9223372036854775808(19位數)。

顯然，主鍵定義100位寬度是有些「無腦的」，而且也是沒有意義的：因為它不會限制值的合法範圍，即使是定義了 BIGINT(100) 也沒辦法儲存寬度為100的數位，實際上定義 BIGINT(1) 和 BIGINT(20) 的儲存空間是相同的，寬度的定義只是規定了 MySQL 的一些互動工具（MySQL命令列使用者端）用來顯示字元的個數。

整數型別有可選的UNSIGNED 屬性，它表示不允許負值，這大約能使正整數的上限提高一倍。例如 TINYINT UNSIGNED 可以儲存的值範圍是 0 ~ 255，而 TINYINT 的值的儲存範圍是 -128 ~ 127。我們的ID列是從0開始遞增的，所以可以選用這個屬性。

那麼，我們應該對id列的定義如下所示：

`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵'

message列儲存的是介面互動報文內容，定義的型別是 TEXT，它還有一些相關的型別，具體如下（L代表字串的位元組長度，數位表示儲存字串位元組長度的位元組數）：

若報文內容中每個字元只佔用1位元組的話，那麼 TEXT 型別能最多儲存大約 65535 個字元，而實際上報文內容遠遠達不到這個長度，而且 TEXT 型別是為了儲存很大的資料而設計的字串資料型別。

我們可以將其調整成 VARCHAR 型別，並根據實際的報文長度都不超過 1000 來指定它的字元數為 1000，避免發生因報文長度過長而無法儲存資料的情況。通常情況下MySQL會在內容分配固定大小的記憶體來儲存值，我們這樣做節省了儲存空間，對效能也有幫助。

message的更改後的定義如下所示：

`message` varchar(1000) DEFAULT NULL COMMENT '報文內容'

VARCHAR 型別也需要額外使用 1 或 2 位元組來記錄字串位元組的長度：如果列的最大長度小於或等於 255 位元組，則只使用 1 位元組來表示；否則使用 2 位元組來表示。

MySQL 字串長度定義的不是位元組數，而是字元數。像 UTF-8 這樣複雜的字元集可能需要多個位元組來儲存一個字元。

更小的通常更好

MySQL 總是為 CHAR 型別分配所定義長度的空間，所以它是固定長度的，它相比於 VARCHAR 在面對經常修改的資料時表現更好，因為固定長度的列不容易出現記憶體碎片，而且對於 CHAR(1) 這種非常短的列，它要比 VARCHAR(1) 更高效，因為前者只佔用 1 個位元組的空間，後者佔用 2 個位元組（其中 1 位元組記錄長度）。

CHAR 型別適合儲存非常短的字串或者所有值長度都幾乎相同的字串，不過需要注意的是，MySQL 會將所有尾隨的空格移除。

service_method欄位實際上儲存的是介面協定，無非是 HTTP 和 TCP 這兩種，我們可以將其定義修改為如下所示：

`service_method` char(4) DEFAULT NULL COMMENT '介面方式'

但是實際上，整型資料比字元資料的比較操作代價更低，如果在允許改變欄位型別的情況下，我們將其修改為 TINYINT 型別，通過定義列舉值來表示不同的協定效率會更高。

`service_method` tinyint DEFAULT NULL COMMENT '介面方式 1-HTTP 2-TCP'

service_caller和service_receiver欄位也是一樣的道理，這些值都是固定的列舉，最初應該也定義成 TINYINT 的形式，如下

`service_caller` tinyint DEFAULT NULL COMMENT '呼叫方',
`service_receiver` tinyint DEFAULT NULL COMMENT '接收方'

service_type欄位中儲存的是對應介面的編碼值，它們都是寬度為 4 的整型資料，最大值不會超過 9999，所以根據它的取值範圍將其修改為 SMALLINT 型別會更合適，如下

`service_type` smallint DEFAULT NULL COMMENT '介面型別'

service_name欄位介面名稱最長也不會超過15個字元，所以我們將它的 VARCHAR 定義字元長度修改一下：

`service_name` varchar(15) DEFAULT NULL COMMENT '介面名稱'

status欄位只有 10 和 20 兩種值，相比於 INT，使用 TINYINT 更合適一些

`status` tinyint DEFAULT 10 COMMENT '狀態 10-成功 20-異常'

DATETIME 和 TIMESTAMP

這兩種型別非常相似，對於大多數系統來說，這兩種型別都可以，不過它們也有所不同。

DATETIME 可以儲存的日期範圍更大，從 1000 年到 9999 年，精度為 1 微秒，非小數部分佔用 5 個位元組的儲存空間，小數部分根據精度大小佔用 0 ~ 3 個位元組，並且它與時區無關。預設情況下，MySQL 以 yyyy-MM-dd HH:mm:ss 的格式顯示時間，如果需要指定精度，可以以datetime(6)的形式定義。

TIMESTAMP 型別儲存的是自 1970 年 1 月 1 日格林尼治標準時間以來的秒數（精度也為 1 微秒），非小數部分佔用 4 個位元組的儲存空間，小數部分與 DATETIME 型別佔用空間規則一致，所以它的取值範圍相比於 DATETIME 要小，只能表示從 1970 年到 2038 年 1 月 19 日的時間範圍。而且該型別與MySQL服務指定的時區相關，這就使得在查詢日期時，會將時間戳轉換為所在時區的時間後再顯示，所以不同地區看到的同一時間戳的實際時間展示是不一樣的。

MySQL 可以使用 FROM_UNIXTIME() 函數將 UNIX 時間戳轉換成日期，使用 UNIX_TIMESTAMP() 函數將日期轉換為 UNIX 時間戳。

使用 DATETIME 型別還是使用 TIMESTAMP 型別需要考慮以下問題：

儲存空間對我們來說重要嗎？
需要支援前後多大時間範圍的日期和時間？
儲存的日期資料有精度要求嗎？
是在MySQL中處理時區還是在程式碼中處理時區？

拿我們的應用來說，DATETIME 型別會更合適一些：

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
`ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '時間戳'

如果想要對時間戳進行記錄，可以考慮使用 BIGINT 型別，它不會遇到 2038 年的問題。

避免使用 NULL

通常情況下，最好指定列為 NOT NULL，除非明確的需要儲存為 NULL 值。可為 NULL 的列會使用更多的儲存空間，在 MySQL 中需要特殊的處理；查詢中包含可為 NULL 的列對 MySQL 來說更難優化，因為可為 NULL 的列使得索引、索引統計和值的比較更為複雜。

MySQL 預設的行格式為 DYNAMIC，它會在每行資料中記錄額外資訊，其中就包括對 NULL 值列表的記錄，如果我們所有的列都為 NOT NULL 的話，那麼這部分額外資訊是不需要記錄的。

瞭解：COMPRESSED 行格式與 DYNAMIC 不同的是，它會對儲存資料的頁進行壓縮以節省空間；COMPACT 行格式與 DYNAMIC 和 COMPRESSED 不同的是在對溢位列的處理上，COMPACT 會儲存溢位列的部分資料，剩餘的資料使用其他資料頁儲存，並記錄下儲存這些資料頁的指標，DYNAMIC 和 COMPRESSED 則是將該列所有資料都儲存在其他資料頁中，在該列資料處只儲存對應溢位頁的地址。

但是實際上將列的定義修改為 NOT NULL 帶來的效能提升並不明顯，所以並不會將這種優化作為首選，而是在表結構初始化時考慮到這一點。

修改好，最終初始化表結構的 DDL 語句如下：

CREATE TABLE `service_log` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `service_type` smallint NOT NULL DEFAULT -1 COMMENT '介面型別',
  `service_name` varchar(30) DEFAULT '' COMMENT '介面名稱',
  `service_method` tinyint NOT NULL DEFAULT -1 COMMENT '介面方式 1-HTTP 2-TCP',
  `serial_no` int DEFAULT -1 COMMENT '訊息序號',
  `service_caller` tinyint DEFAULT -1 COMMENT '呼叫方',
  `service_receiver` tinyint DEFAULT -1 COMMENT '接收方',
  `status` tinyint DEFAULT 10 COMMENT '狀態 10-成功 20-異常',
  `error_message` varchar(200) DEFAULT '' COMMENT '異常資訊',
  `message` varchar(1000) DEFAULT '' COMMENT '報文內容',
  `create_user` varchar(50) DEFAULT '' COMMENT '建立者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_user` varchar(50) DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '刪除標誌',
  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '時間戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='介面呼叫紀錄檔';

TINYINT 表示 Boolean 型別

需要注意，Boolean 型別的值在 MySQL 中是通過 TINYINT 來對映的，如果在資料庫中該值為 0，那麼對映到 Java 物件中為 False，如下所示：

實數型別

實數型別因為在該表結構中使用不到我們沒有介紹，所以在這裡進行補充。

MySQL 既支援精確計算的型別（DECIMAL），也支援近似計算的浮點型別（FLOAT 和 DOUBLE）。

FLOAT 使用 4 個位元組的儲存空間，DOUBLE 使用 8 個位元組的儲存空間，可以指定列的精度，但是通常情況下建議只指定資料型別，而不指定精度，否則 MySQL 會根據精度自行進行舍入，而且它們還會受到平臺或實現依賴性的影響。

我們看下邊這個例子：

CREATE TABLE `real_number` (
  `f1` float(7, 4) NOT NULL,
  `f2` float NOT NULL,
  `d1` double(7, 4) NOT NULL,
  `d2` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='實數';

# 插入資料
INSERT into real_number values (
    3.1415926535,
    3.1415926535,
    3.1415926535,
    3.1415926535
);

# 查詢結果
select * from real_number;

f1	f2	d1	d2
3.1416	3.14159	3.1416	3.1415926535

根據結果值我們可以發現，指定了精度的浮點型別進行了舍入，沒有指定精度的 FLOAT 型別預設保留了小數點後 5 位小數，自行的舍入可能會引起混淆。

通常情況下，我們為了保證最大限度的實現可移植性，需要儲存近似數位資料值的程式碼應該使用 FLOAT 或 DOUBLE，而不指定精度或位數。

還有一種情況需要注意，如果我們要插入超過指定精度的整數範圍，會導致資料入庫失敗，如下：

# 指定 f1 列整數寬度為 4，實際定義允許的最大寬度為 3
INSERT into real_number values (
3210.1415926535,
3.1415926535,
3.1415926535,
3.1415926535
);

# 結果
SQL 錯誤 [1264] [22001]: Data truncation: Out of range value for column 'f1' at row 1

如果沒有指定精度範圍，那麼則會對小數部分進行壓縮，精度變小，而不是提示入庫失敗，如下：

# f2 列插入該值，檢視結果
INSERT into real_number values (
3.1415926535,
3210.1415926535,
3.1415926535,
3.1415926535
);

f1	f2	d1	d2
3.1416	3210.14	3.1416	3.1415926535

DECIMAL 與 FLOAT 和 DOUBLE 不同，在進行精確的小數計算時，需要指定它的精度，否則預設情況下為DECIMAL(10, 0)，只儲存整數。而且它在儲存相同範圍的值是會佔用更多的空間，所以出於對額外的空間需求和計算成本的考慮，我們只在需要對小數進行精確計算時才使用該型別。

DECIMAL 的最大位數為 65，而且當為 DECIMAL 列指定的值小數點後位數超過小數位數精度範圍時，該值將舍入為精度範圍。同樣地，如果整數部分的寬度大於指定的精度範圍，那麼也會發生超出列範圍的異常而導致無法正常入庫，如下：

create table `decimal_t` (
  `d1` decimal(7, 4) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DECIMAL';

INSERT INTO decimal_t values (3.1415926535);

# 結果值為 3.1416

INSERT INTO decimal_t values (1234.1415926535);

# Data truncation: Out of range value for column 'd1' at row 1

除此之外，在一些大容量的場景下，可以考慮使用 BIGINT 代替 DECIMAL，在儲存時根據小數的位數乘以相應的倍數即可。這樣就可以同時避免浮點數計算不精確、 DECIMAL 精確計算代價高和數值精度範圍限制的問題。

巨人的肩膀

《高效能 MySQL 第四版》：第六章
11.7 Data Type Storage Requirements
mysql的日期時間型別及精度問題
MySQL之DATETIME與TIMESTAMP的時間精度問題
11.8 Choosing the Right Type for a Column
11.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE
B.3.4.8 Problems with Floating-Point Values
《MySQL 是怎樣執行的》：第四章

作者：京東物流王奕龍

來源：京東雲開發者社群自猿其說Tech 轉載請註明來源

高效能MySQL實戰（一）：表結構