MySQL大表優化的示例分析

126次閱讀

沒有評論

共計 3094 個字符，預計需要花費 8 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

丸趣 TV 小編給大家分享一下 MySQL 大表優化的示例分析，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

背景

阿里云 RDS FOR MySQL（MySQL5.7 版本）數據庫業務表每月新增數據量超過千萬, 隨著數據量持續增加, 我們業務出現大表慢查詢, 在業務高峰期主業務表的慢查詢需要幾十秒嚴重影響業務

方案概述

一、數據庫設計及索引優化

MySQL 數據庫本身高度靈活，造成性能不足，嚴重依賴開發人員的表設計能力以及索引優化能力, 在這里給幾點優化建議

時間類型轉化為時間戳格式, 用 int 類型儲存, 建索引增加查詢效率

建議字段定義 not null，null 值很難查詢優化且占用額外的索引空間

使用 TINYINT 類型代替枚舉 ENUM

存儲精確浮點數必須使用 DECIMAL 替代 FLOAT 和 DOUBLE

字段長度嚴重根據業務需求來, 不要設置過大

盡量不要使用 TEXT 類型，如必須使用建議將不常用的大字段拆分到其它表

MySQL 對索引字段長度是有限制的, innodb 引擎的每個索引列長度默認限制為 767 字節（bytes），所有組成索引列的長度和不能大于 3072 字節(mysql8.0 單索引可以創建 1024 字符)

大表有 DDL 需求時請聯系 DBA

最左索引匹配規則

顧名思義就是最左優先，在創建組合索引時，要根據業務需求，where 子句中使用最頻繁的一列放在最左邊。復合索引很重要的問題是如何安排列的順序，比如 where 后面用到 c1, c2 這兩個字段，那么索引的順序是 (c1,c2) 還是 (c2,c1) 呢，正確的做法是，重復值越少的越放前面，比如一個列 95% 的值都不重復，那么一般可以將這個列放最前面

復合索引 index(a,b,c)

where a=3 只使用了 a

where a=3 and b=5 使用了 a,b

where a=3 and b=5 and c=4 使用了 a,b,c

where b=3 or where c=4 沒有使用索引

where a=3 and c=4 僅使用了 a

where a=3 and b 10 and c=7 使用了 a,b

where a=3 and b like‘xx%’and c=7 使用了 a,b

其實相當于創建了多個索引：key(a)、key(a,b)、key(a,b,c)

二、數據庫切換到 PloarDB 讀寫分離

PolarDB 是阿里云自研的下一代關系型云數據庫，100% 兼容 MySQL 存儲容量最高可達 100 TB，單庫最多可擴展到 16 個節點，適用于企業多樣化的數據庫應用場景。PolarDB 采用存儲和計算分離的架構，所有計算節點共享一份數據，提供分鐘級的配置升降級、秒級的故障恢復、全局數據一致性和免費的數據備份容災服務。

集群架構，計算與存儲分離
PolarDB 采用多節點集群的架構，集群中有一個 Writer 節點（主節點）和多個 Reader 節點（只讀節點），各節點通過分布式文件系統（PolarFileSystem）共享底層的存儲（PolarStore）

讀寫分離
當應用程序使用集群地址時，PolarDB 通過內部的代理層（Proxy）對外提供服務，應用程序的請求都先經過代理，然后才訪問到數據庫節點。代理層不僅可以做安全認證和保護，還可以解析 SQL，把寫操作（例如事務、UPDATE、INSERT、DELETE、DDL 等）發送到主節點，把讀操作（例如 SELECT）均衡地分發到多個只讀節點，實現自動的讀寫分離。對于應用程序來說，就像使用一個單點的數據庫一樣簡單。

在離線混合場景：不同業務用不同的連接地址，使用不同的數據節點，避免相互影響

Sysbench 性能壓測報告：

PloarDB 4 核 16G 2 臺

PloarDB 8 核 32G 2 臺

三、分表歷史數據遷移到 MySQL8.0 X-Engine 存儲引擎

分表業務表保留 3 個月數據(這個根據公司需求來), 歷史數據按月分表到歷史庫 X -Engine 存儲引擎表, 為什么要選用 X -Engine 存儲引擎表, 它有什么優點?

節約成本, X-Engine 的存儲成本約為 InnoDB 的一半

X-Engine 分層存儲提高 QPS, 采用層次化的存儲結構，將熱數據與冷數據分別存放在不同的層次中，并默認對冷數據所在層次進行壓縮

X-Engine 是阿里云數據庫產品事業部自研的聯機事務處理 OLTP（On-Line Transaction Processing）數據庫存儲引擎。
X-Engine 存儲引擎不僅可以無縫對接兼容 MySQL（得益于 MySQL Pluginable Storage Engine 特性），同時 X -Engine 使用分層存儲架構。因為目標是面向大規模的海量數據存儲，提供高并發事務處理能力和降低存儲成本，在大部分大數據量場景下，數據被訪問的機會是不均等的，訪問頻繁的熱數據實際上占比很少，X-Engine 根據數據訪問頻度的不同將數據劃分為多個層次，針對每個層次數據的訪問特點，設計對應的存儲結構，寫入合適的存儲設備

X-Engine 使用了 LSM-Tree 作為分層存儲的架構基礎，并進行了重新設計：

熱數據層和數據更新使用內存存儲，通過內存數據庫技術（Lock-Free index structure/append only）提高事務處理的性能。

流水線事務處理機制，把事務處理的幾個階段并行起來，極大提升了吞吐。

訪問頻度低的數據逐漸淘汰或是合并到持久化的存儲層次中，并結合多層次的存儲設備（NVM/SSD/HDD）進行存儲。

對性能影響比較大的 Compaction 過程做了大量優化：

拆分數據存儲粒度，利用數據更新熱點較為集中的特征，盡可能的在合并過程中復用數據。

精細化控制 LSM 的形狀，減少 I / O 和計算代價，有效緩解了合并過程中的空間增大。

同時使用更細粒度的訪問控制和緩存機制，優化讀的性能。

四、阿里云 PloarDB MySQL8.0 版本并行查詢

分表之后我們的數據量依然很大, 并沒有完全解決我們的慢查詢問題, 只是降低了我們業務表的體量, 這部分慢查詢我們需要用到 PolarDB 的并行查詢優化

PolarDB MySQL 8.0 重磅推出并行查詢框架，當您的查詢數據量到達一定閾值，就會自動啟動并行查詢框架，從而使查詢耗時指數級下降
在存儲層將數據分片到不同的線程上，多個線程并行計算，將結果流水線匯總到總線程，最后總線程做些簡單歸并返回給用戶，提高查詢效率。
并行查詢（Parallel Query）利用多核 CPU 的并行處理能力，以 8 核 32 GB 配置為例，示意圖如下所示。