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

来源:https://www.cnblogs.com/jingdongkeji/archive/2023/08/21/17645811.html
-Advertisement-
Play Games

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


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

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 則是將該列所有數據都保存在其他數據頁中，在該列數據處只保存對應溢出頁的地址。

COMPACT行格式示意圖.png

但是實際上將列的定義修改為 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，如下所示：

tyint.png

實數類型

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

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 精確計算代價高和數值精度範圍限制的問題。

巨人的肩膀

作者：京東物流 王奕龍

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SELinux 入門 pt.1
    哈嘍大家好，我是鹹魚 文章《[SELinux 導致 Keepalived 檢測腳本無法執行](https://mp.weixin.qq.com/s?__biz=MzkzNzI1MzE2Mw==&mid=2247486319&idx=1&sn=4932d10d7ad39d02d2536be1a70b1 ...
  • 最小化安裝Linux系統初始化腳本
    [toc] # 最小化安裝Linux系統初始化腳本 **註：此腳本適用於centos 7/8、Ubuntu1804，具體需要根據實際情況進行測試調整。** 此腳本包含的功能： 1. 允許 root 用戶使用 ssh 登錄 2. 關閉 selinux 3. 關閉防火牆 4. 設置 ps1 5. 設置默 ...
  • 多摩川編碼器協議理解與自做經歷-（1）
    多摩川協議理解-（1） 多摩川編碼器有絕對值的和增量的兩種，這邊主要是我對多摩川絕對值的理解。 前文， 多摩川編碼器其實是日本有一家公司叫多摩川的公司做出來的，其實他們家有很多種編碼器，但是這裡我只對他的們出的協議作出理解。 硬體介面方面，因為有單圈和多圈只分，多圈一般有電池，所以會多一個帶電池的。 ...
  • linux設備驅動模型：kobject、kobj_type
    為什麼需要設備驅動模型 內核版本發展 2.4版本之前內核沒有統一的設備驅動模型，但是可以用（例如先前的led字元設備驅動實驗，使用前需要手動調用mknod命令創建設備文件，從而進一步控制硬體）。 2.4~2.6版本內核使用devfs，掛載在/dev目錄。需要在內核驅動中創建設備文件（調用devfs_ ...
  • 讀SQL學習指南（第3版）筆記02_數據類型
    ![](https://img2023.cnblogs.com/blog/3076680/202308/3076680-20230821142549307-1962607264.png) # 1. 命令行工具 ## 1.1. mysql -u root -p; ## 1.2. mysql＞ show ...
  • 深入理解資料庫事務：確保數據完整性與一致性
    # 前言 在現代信息系統中，數據是至關重要的資產之一。作為一名後端開發人員，與資料庫的交道必不可少，為了確保數據的完整性、一致性和可靠性，資料庫引入了事務的概念。本次將帶您深入瞭解資料庫事務的重要性、特性以及如何在應用程式中正確地使用事務來維護數據的穩定性。 ## 什麼是資料庫事務？ 資料庫事務是一 ...
  • Apache DolphinScheduler 支持使用 OceanBase 作為元資料庫啦!
    ![file](https://img2023.cnblogs.com/other/2685289/202308/2685289-20230821163010585-15599264.png) DolphinScheduler是一個開源的分散式任務調度系統，擁有分散式架構、多任務類型、可視化操作、分 ...
  • 中國雲數據倉庫，雙第一！
    近日全球領先的IT市場研究和咨詢公司IDC正式發佈《中國關係型資料庫軟體市場跟蹤報告-數據倉庫市場Add-on》報告華為雲GaussDB(DWS)憑藉領先的技術和優異的市場表現榮獲“雙第一”。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類