最近因需求改動新增了一些數(shù)據(jù)庫表，但是在定義表結(jié)構(gòu)時，具體列屬性的選擇有些不知其所以然，索引的添加也有遺漏和不規(guī)范的地方，所以我打算為創(chuàng)建一個高性能表的過程以實戰(zhàn)的形式寫一個專題，以此來學(xué)習(xí)和鞏固這些知識。

1. 實戰(zhàn)

我使用的 MySQL 版本是 5.7，建表 DDL 語句如下所示：根據(jù)需求創(chuàng)建接口調(diào)用日志數(shù)據(jù)庫表，請大家瀏覽具體字段的屬性信息，它們有不少能夠優(yōu)化的點。

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 '調(diào)用方',
  `service_receiver` varchar(15) DEFAULT NULL COMMENT '接收方',
  `status` int(3) DEFAULT '10' COMMENT '狀態(tài) 10-成功 20-異常',
  `error_message` varchar(200) DEFAULT NULL COMMENT '異常信息',
  `message` text DEFAULT NULL COMMENT '報文內(nèi)容',
  `create_user` varchar(50) DEFAULT NULL COMMENT '創(chuàng)建者',
  `create_time` datetime NOT NULL COMMENT '創(chuàng)建時間',
  `update_user` varchar(50) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime NOT NULL COMMENT '更新時間',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '刪除標(biāo)志',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '時間戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接口調(diào)用日志';

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

2. 優(yōu)化和改進(jìn)

慷慨不是明智的

一般來說，要盡量使用能夠正確存儲和表示數(shù)據(jù)的最小數(shù)據(jù)類型，更小的數(shù)據(jù)類型通常更快，因為它們占用的磁盤、內(nèi)存和CPU緩存的空間更少，并且處理時需要的CPU周期也更少。但是，這也要確保沒有低估需要存儲的值的范圍，否則會因入庫失敗而造成數(shù)據(jù)丟失，而且表結(jié)構(gòu)修改的流程審批也很麻煩。

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

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

顯然，主鍵定義100位寬度是有些“無腦的”，而且也是沒有意義的：因為它不會限制值的合法范圍，即使是定義了 BIGINT(100) 也沒辦法存儲寬度為100的數(shù)字，實際上定義 BIGINT(1) 和 BIGINT(20) 的存儲空間是相同的，寬度的定義只是規(guī)定了 MySQL 的一些交互工具（MySQL命令行客戶端）用來顯示字符的個數(shù)。

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

那么，我們應(yīng)該對id列的定義如下所示：

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

message列保存的是接口交互報文內(nèi)容，定義的類型是 TEXT，它還有一些相關(guān)的類型，具體如下（L代表字符串的字節(jié)長度，數(shù)字表示存儲字符串字節(jié)長度的字節(jié)數(shù)）：

若報文內(nèi)容中每個字符只占用1字節(jié)的話，那么 TEXT 類型能最多存儲大約 65535 個字符，而實際上報文內(nèi)容遠(yuǎn)遠(yuǎn)達(dá)不到這個長度，而且 TEXT 類型是為了存儲很大的數(shù)據(jù)而設(shè)計的字符串?dāng)?shù)據(jù)類型。

我們可以將其調(diào)整成 VARCHAR 類型，并根據(jù)實際的報文長度都不超過 1000 來指定它的字符數(shù)為 1000，避免發(fā)生因報文長度過長而無法保存數(shù)據(jù)的情況。通常情況下MySQL會在內(nèi)容分配固定大小的內(nèi)存來保存值，我們這樣做節(jié)省了存儲空間，對性能也有幫助。

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

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

VARCHAR 類型也需要額外使用 1 或 2 字節(jié)來記錄字符串字節(jié)的長度：如果列的最大長度小于或等于 255 字節(jié)，則只使用 1 字節(jié)來表示；否則使用 2 字節(jié)來表示。

MySQL 字符串長度定義的不是字節(jié)數(shù)，而是字符數(shù)。像 UTF-8 這樣復(fù)雜的字符集可能需要多個字節(jié)來存儲一個字符。

更小的通常更好

MySQL 總是為 CHAR 類型分配所定義長度的空間，所以它是固定長度的，它相比于 VARCHAR 在面對經(jīng)常修改的數(shù)據(jù)時表現(xiàn)更好，因為固定長度的列不容易出現(xiàn)內(nèi)存碎片，而且對于 CHAR(1) 這種非常短的列，它要比 VARCHAR(1) 更高效，因為前者只占用 1 個字節(jié)的空間，后者占用 2 個字節(jié)（其中 1 字節(jié)記錄長度）。

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

service_method字段實際上保存的是接口協(xié)議，無非是 HTTP 和 TCP 這兩種，我們可以將其定義修改為如下所示：

`service_method` char(4) DEFAULT NULL COMMENT '接口方式'

但是實際上，整型數(shù)據(jù)比字符數(shù)據(jù)的比較操作代價更低，如果在允許改變字段類型的情況下，我們將其修改為 TINYINT 類型，通過定義枚舉值來表示不同的協(xié)議效率會更高。

`service_method` tinyint DEFAULT NULL COMMENT '接口方式 1-HTTP 2-TCP'

service_caller和service_receiver字段也是一樣的道理，這些值都是固定的枚舉，最初應(yīng)該也定義成 TINYINT 的形式，如下

`service_caller` tinyint DEFAULT NULL COMMENT '調(diào)用方',
`service_receiver` tinyint DEFAULT NULL COMMENT '接收方'

service_type字段中存儲的是對應(yīng)接口的編碼值，它們都是寬度為 4 的整型數(shù)據(jù)，最大值不會超過 9999，所以根據(jù)它的取值范圍將其修改為 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 '狀態(tài) 10-成功 20-異常'

DATETIME 和 TIMESTAMP

這兩種類型非常相似，對于大多數(shù)系統(tǒng)來說，這兩種類型都可以，不過它們也有所不同。

DATETIME 可以保存的日期范圍更大，從 1000 年到 9999 年，精度為 1 微秒，非小數(shù)部分 占用 5 個字節(jié)的存儲空間，小數(shù)部分根據(jù)精度大小占用 0 ~ 3 個字節(jié)，并且它與時區(qū)無關(guān)。默認(rèn)情況下，MySQL 以 yyyy-MM-dd HH:mm:ss 的格式顯示時間，如果需要指定精度，可以以datetime(6)的形式定義。

TIMESTAMP 類型存儲的是自 1970 年 1 月 1 日格林尼治標(biāo)準(zhǔn)時間以來的秒數(shù)（精度也為 1 微秒），非小數(shù)部分占用 4 個字節(jié)的存儲空間，小數(shù)部分與 DATETIME 類型占用空間規(guī)則一致，所以它的取值范圍相比于 DATETIME 要小，只能表示從 1970 年到 2038 年 1 月 19 日的時間范圍。而且該類型與MySQL服務(wù)指定的時區(qū)相關(guān)，這就使得在查詢?nèi)掌跁r，會將時間戳轉(zhuǎn)換為所在時區(qū)的時間后再顯示，所以不同地區(qū)看到的同一時間戳的實際時間展示是不一樣的。

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

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

• 存儲空間對我們來說重要嗎？

• 需要支持前后多大時間范圍的日期和時間？

• 保存的日期數(shù)據(jù)有精度要求嗎？

• 是在MySQL中處理時區(qū)還是在代碼中處理時區(qū)？

拿我們的應(yīng)用來說，DATETIME 類型會更合適一些：

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
`ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '時間戳'

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

避免使用 NULL

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

MySQL 默認(rèn)的行格式為 DYNAMIC，它會在每行數(shù)據(jù)中記錄額外信息，其中就包括對 NULL 值列表的記錄，如果我們所有的列都為 NOT NULL 的話，那么這部分額外信息是不需要記錄的。

了解：COMPRESSED 行格式與 DYNAMIC 不同的是，它會對存儲數(shù)據(jù)的頁進(jìn)行壓縮以節(jié)省空間；COMPACT 行格式與 DYNAMIC 和 COMPRESSED 不同的是在對溢出列的處理上，COMPACT 會存儲溢出列的部分?jǐn)?shù)據(jù)，剩余的數(shù)據(jù)使用其他數(shù)據(jù)頁保存，并記錄下保存這些數(shù)據(jù)頁的指針，DYNAMIC 和 COMPRESSED 則是將該列所有數(shù)據(jù)都保存在其他數(shù)據(jù)頁中，在該列數(shù)據(jù)處只保存對應(yīng)溢出頁的地址。

但是實際上將列的定義修改為 NOT NULL 帶來的性能提升并不明顯，所以并不會將這種優(yōu)化作為首選，而是在表結(jié)構(gòu)初始化時考慮到這一點。

修改好，最終初始化表結(jié)構(gòu)的 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 '調(diào)用方',
  `service_receiver` tinyint DEFAULT -1 COMMENT '接收方',
  `status` tinyint DEFAULT 10 COMMENT '狀態(tài) 10-成功 20-異常',
  `error_message` varchar(200) DEFAULT '' COMMENT '異常信息',
  `message` varchar(1000) DEFAULT '' COMMENT '報文內(nèi)容',
  `create_user` varchar(50) DEFAULT '' COMMENT '創(chuàng)建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  `update_user` varchar(50) DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '刪除標(biāo)志',
  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '時間戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接口調(diào)用日志';

TINYINT 表示 Boolean 類型

需要注意，Boolean 類型的值在 MySQL 中是通過 TINYINT 來映射的，如果在數(shù)據(jù)庫中該值為 0，那么映射到 Java 對象中為 False，如下所示：

實數(shù)類型

實數(shù)類型因為在該表結(jié)構(gòu)中使用不到我們沒有介紹，所以在這里進(jìn)行補(bǔ)充。

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

FLOAT 使用 4 個字節(jié)的存儲空間，DOUBLE 使用 8 個字節(jié)的存儲空間，可以指定列的精度，但是通常情況下建議只指定數(shù)據(jù)類型，而不指定精度，否則 MySQL 會根據(jù)精度自行進(jìn)行舍入，而且它們還會受到平臺或?qū)崿F(xiàn)依賴性的影響。

我們看下邊這個例子：

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='實數(shù)';

# 插入數(shù)據(jù)
INSERT into real_number values (
    3.1415926535,
    3.1415926535,
    3.1415926535,
    3.1415926535
);

# 查詢結(jié)果
select * from real_number;

根據(jù)結(jié)果值我們可以發(fā)現(xiàn)，指定了精度的浮點類型進(jìn)行了舍入，沒有指定精度的 FLOAT 類型默認(rèn)保留了小數(shù)點后 5 位小數(shù)，自行的舍入可能會引起混淆。

通常情況下，我們?yōu)榱吮ＷC最大限度的實現(xiàn)可移植性，需要存儲近似數(shù)字?jǐn)?shù)據(jù)值的代碼應(yīng)該使用 FLOAT 或 DOUBLE，而不指定精度或位數(shù)。

還有一種情況需要注意，如果我們要插入超過指定精度的整數(shù)范圍，會導(dǎo)致數(shù)據(jù)入庫失敗，如下：

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

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

如果沒有指定精度范圍，那么則會對小數(shù)部分進(jìn)行壓縮，精度變小，而不是提示入庫失敗，如下：

# f2 列插入該值，查看結(jié)果
INSERT into real_number values (
3.1415926535,
3210.1415926535,
3.1415926535,
3.1415926535
);

DECIMAL 與 FLOAT 和 DOUBLE 不同，在進(jìn)行精確的小數(shù)計算時，需要指定它的精度，否則默認(rèn)情況下為DECIMAL(10, 0)，只保存整數(shù)。而且它在存儲相同范圍的值是會占用更多的空間，所以出于對額外的空間需求和計算成本的考慮，我們只在需要對小數(shù)進(jìn)行精確計算時才使用該類型。

DECIMAL 的最大位數(shù)為 65，而且當(dāng)為 DECIMAL 列指定的值小數(shù)點后位數(shù)超過小數(shù)位數(shù)精度范圍時，該值將舍入為精度范圍。同樣地，如果整數(shù)部分的寬度大于指定的精度范圍，那么也會發(fā)生超出列范圍的異常而導(dǎo)致無法正常入庫，如下：

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

INSERT INTO decimal_t values (3.1415926535);

# 結(jié)果值為 3.1416

INSERT INTO decimal_t values (1234.1415926535);

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

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

巨人的肩膀

• 《高性能 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 是怎樣運行的》：第四章

作者：京東物流 王奕龍

來源：京東云開發(fā)者社區(qū) 自猿其說Tech 轉(zhuǎn)載請注明來源文章來源地址http://www.zghlxwxcb.cn/news/detail-661818.html

到了這里，關(guān)于高性能MySQL實戰(zhàn)（一）：表結(jié)構(gòu)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！