MySQL 調優，大表處理探索

當 MySQL 單表記錄數過大時，數據庫的 CRUD 性能會明顯下降，一些常見的優化措施如下：

限定數據的範圍：務必禁止不帶任何限制數據範圍條件的查詢語句。比如：我們當用戶在查詢訂單歷史的時候，我們可以控制在一個月的範圍內。
優化 SQL 和索引
緩存：使用 MySQL 的緩存，另外對重量級、更新少的數據可以考慮使用應用級別的緩存如 Redis；
讀 / 寫分離：經典的數據庫拆分方案，主庫負責寫，從庫負責讀；
MySQL 自帶的分區表。無需更改代碼, 但是 sql 語句是需要針對分區表做優化的，sql 條件中要帶上分區條件的列，從而使查詢定位到少量的分區上，否則就會掃描全部分區
垂直分區，其實就是根據你模塊的耦合度，將一個大的系統分爲多個小的系統，也就是分佈式系統；
水平切分，針對數據量大的表，這一步最麻煩，最能考驗技術水平，要選擇一個合理的 sharding key, 爲了有好的查詢效率，表結構也要改動，做一定的冗餘，應用也要改，sql 中儘量帶 sharding key，將數據定位到限定的表上去查，而不是掃描全部的表；

【1】讀寫分離架構

讀寫分離是依賴於主從複製，而主從複製又是爲讀寫分離服務的。因爲主從複製要求 slave 不能寫只能讀（如果對 slave 執行寫操作，那麼 show slave status 將會呈現 Slave_SQL_Running=NO，此時你需要手動同步一下 slave）。

讀寫分離架構能提升整體負載，但是不能解決單表數據量過大的問題。

① 使用 mysql-proxy 代理

優點：直接實現讀寫分離和負載均衡，不用修改代碼，master 和 slave 用一樣的帳號，mysql 官方不建議實際生產中使用

缺點：降低性能，不支持事務

② 指定數據源

使用 AbstractRoutingDataSource+aop+annotation 在 dao 層決定數據源。如果採用了 mybatis，可以將讀寫分離放在 ORM 層，比如 mybatis 可以通過 mybatis plugin 攔截 sql 語句，所有的 insert/update/delete 都訪問 master 庫，所有的 select 都訪問 salve 庫，這樣對於 dao 層都是透明。

plugin 實現時可以通過註解或者分析語句是讀寫方法來選定主從庫。不過這樣依然有一個問題，也就是不支持事務，所以我們還需要重寫一下 DataSourceTransactionManager，將 read-only 的事務扔進讀庫，其餘的有讀有寫的扔進寫庫。

當然可以使用 AbstractRoutingDataSource+aop+annotation 在 service 層決定數據源，可以支持事務。這種方式的缺點：類內部方法通過 this.xx() 方式相互調用時，aop 不會進行攔截，需進行特殊處理。

【2】表分區

MySQL 在 5.1 版引入的分區是一種簡單的水平拆分，用戶需要在建表的時候加上分區參數，對應用是透明的無需修改代碼。

對用戶來說，分區表是一個獨立的邏輯表，但是底層由多個物理子表組成，實現分區的代碼實際上是通過對一組底層表的對象封裝，但對 SQL 層來說是一個完全封裝底層的黑盒子。MySQL 實現分區的方式也意味着索引也是按照分區的子表定義，沒有全局索引。

用戶的 SQL 語句是需要針對分區表做優化，SQL 條件中要帶上分區條件的列，從而使查詢定位到少量的分區上，否則就會掃描全部分區，可以通過 EXPLAIN PARTITIONS 來查看某條 SQL 語句會落在那些分區上，從而進行 SQL 優化。

分區的好處

可以讓單表存儲更多的數據
分區表的數據更容易維護，可以通過清除整個分區批量刪除大量數據，也可以增加新的分區來支持新插入的數據。另外，還可以對一個獨立分區進行優化、檢查、修復等操作
部分查詢能夠從查詢條件確定只落在少數分區上，速度會很快
分區表的數據還可以分佈在不同的物理設備上，從而高效利用多個硬件設備
可以使用分區表來避免某些特殊瓶頸，例如 InnoDB 單個索引的互斥訪問、ext3 文件系統的 inode 鎖競爭
可以備份和恢復單個分區

分區的限制和缺點

一個表最多隻能有 1024 個分區
如果分區字段中有主鍵或者唯一索引的列，那麼所有主鍵列和唯一索引列都必須包含進來
分區表無法使用外鍵約束
NULL 值會使分區過濾無效
所有分區必須使用相同的存儲引擎

分區的類型

RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區。
LIST 分區：類似於按 RANGE 分區，區別在於 LIST 分區是基於列值匹配一個離散值集合中的某個值來進行選擇。
HASH 分區：基於用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含 MySQL 中有效的、產生非負整數值的任何表達式。
KEY 分區：類似於按 HASH 分區，區別在於 KEY 分區只支持計算一列或多列，且 MySQL 服務器提供其自身的哈希函數。必須有一列或多列包含整數值。

分區適合的場景

最適合的場景數據的時間序列性比較強，則可以按時間來分區，如下所示：

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);

查詢時加上時間範圍條件效率會非常高，同時對於不需要的歷史數據能很容的批量刪除。

如果數據有明顯的熱點，而且除了這部分數據，其他數據很少被訪問到，那麼可以將熱點數據單獨放在一個分區，讓這個分區的數據能夠有機會都緩存在內存中，查詢時只訪問一個很小的分區表，能夠有效使用索引和緩存。

另外 MySQL 有一種早期的簡單的分區實現 – 合併表（merge table），限制較多且缺乏優化，不建議使用，應該用新的分區機制來替代。

【3】垂直拆分和水平拆分

① 垂直拆分 (分表、分庫)

垂直分庫是根據數據庫裏面的數據表的相關性進行拆分，比如：一個數據庫裏面既存在用戶數據，又存在訂單數據，那麼垂直拆分可以把用戶數據放到用戶庫、把訂單數據放到訂單庫。或者用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將用戶表拆分成兩個單獨的表。

如果數據庫中的數據表過多，可以採用垂直分庫的方式，將關聯的數據表部署在同一個數據庫上。
如果數據表中的列過多，可以採用垂直分表的方式，將一張數據表分拆成多張數據表，把經常一起使用的列放到同一張表裏。

垂直分表是對數據表進行垂直拆分的一種方式，常見的是把一個多字段的大表按常用字段和非常用字段進行拆分，每個表裏面的數據記錄數一般情況下是相同的，只是字段不一樣，使用主鍵關聯，比如原始的用戶表是：

垂直拆分後是：

簡單來說垂直分表是指數據表列的拆分，把一張列比較多的表拆分爲多張表。把主鍵和一些列放在一個表，然後把主鍵和另外的列放在另一個表中。

垂直拆分的優點

可以使得行數據變小，一個數據塊 (Block) 就能存放更多的數據，在查詢時就會減少 I/O 次數(每次查詢時讀取的 Block 就少)
可以達到最大化利用 Cache 的目的，具體在垂直拆分的時候可以將不常變的字段放一起，將經常改變的放一起
簡化表的結構，數據維護簡單

垂直拆分的缺點

主鍵會出現冗餘，需要管理冗餘列，並會引起 Join 操作，可以通過在應用層進行 Join 來解決。此外，垂直分區會讓事務變得更加複雜。垂直拆分並不能解決單表數據量過大的問題，如果需要解決單表數據量過大還需要進行水平拆分。

如果需要解決單臺服務器性能瓶頸，還需要根據模塊進行垂直拆分表放到不同服務器數據庫，比如訂單表和臺賬表存放到兩個數據庫服務器上。這種稱之爲垂直分庫。

有些分表的策略基於應用層的邏輯算法，一旦邏輯算法改變，整個分表邏輯都會改變，擴展性較差對於應用層來說，邏輯算法增加開發成本管理冗餘列。

② 水平分表 (分庫)

水平拆分是通過某種策略將數據分片來存儲，分庫內分表和分庫兩部分，每片數據會分散到不同的 MySQL 表或庫，達到分佈式的效果，能夠支持非常大的數據量。前面的表分區本質上也是一種特殊的庫內分表。

保持數據表結構不變，通過某種策略存儲數據分片。這樣每一片數據分散到不同的表或者庫中，達到了分佈式的目的。常見的策略有 Hash、取模、根據實際維度進行分片等。

庫內分表，僅僅是單純的解決了單一表數據過大的問題，由於沒有把表的數據分佈到不同的機器上，因此對於減輕 MySQL 服務器的壓力來說，並沒有太大的作用，大家還是競爭同一個物理機上的 IO、CPU、網絡，這個就要通過分庫來解決。

水品拆分可以支持非常大的數據量。需要注意的一點是: 分表僅僅是解決了單一表數據過大的問題，但由於表的數據還是在同一臺機器上，其實對於提升 MySQL 併發能力沒有什麼意義，所以水平拆分最好分庫。

前面垂直拆分的用戶表如果進行水平拆分，結果是：

實際情況中往往會是垂直拆分和水平拆分的結合，即將 Users_A_M 和 Users_N_Z 再拆成 Users 和 UserExtras，這樣一共四張表。

水平拆分的優點

不存在單庫大數據和高併發的性能瓶頸
應用端改造較少
提高了系統的穩定性和負載能力

水平拆分的缺點

分片事務一致性難以解決
跨節點 Join 性能差，邏輯複雜
數據多次擴展難度跟維護量極大

水平拆分能夠支持非常大的數據量存儲，應用端改造也少，但分片事務難以解決，跨界點 Join 性能較差，邏輯複雜。給應用增加複雜度，通常查詢時需要多個表名，查詢所有數據都需 UNION 操作

儘量不要對數據進行分片，因爲拆分會帶來邏輯、部署、運維的各種複雜度，一般的數據表在優化得當的情況下支撐千萬以下的數據量是沒有太大問題的。如果實在要分片，儘量選擇客戶端分片架構，這樣可以減少一次和中間件的網絡 I/O。

③ 分庫分表後面臨的問題

① 事務支持

分庫分表後，就成了分佈式事務了。如果依賴數據庫本身的分佈式事務管理功能去執行事務，將付出高昂的性能代價；如果由應用程序去協助控制，形成程序邏輯上的事務，又會造成編程方面的負擔。

② 跨庫 join

只要是進行切分，跨節點 Join 的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。

解決這一問題的普遍做法是分兩次查詢實現。在第一次查詢的結果集中找出關聯數據的 id, 根據這些 id 發起第二次請求得到關聯數據。

③ ID 問題

一旦數據庫被切分到多個物理結點上，我們將不能再依賴數據庫自身的主鍵生成機制。一方面，某個分區數據庫自生成的 ID 無法保證在全局上是唯一的；另一方面，應用程序在插入數據之前需要先獲得 ID, 以便進行 SQL 路由。

常見的主鍵生成策略有 UUID 和 Snowflake 。

UUID

使用 UUID 作主鍵是簡單的方案，但是缺點也是非常明顯的。由於 UUID 非常的長，除佔用大量存儲空間外，主要的問題是在索引上，在建立索引和基於索引進行查詢時都存在性能問題。

最好避免隨機的（不連續且值的分別範圍非常大）聚簇索引，特別是對於 IO 密集型的應用。例如，從性能的角度考慮，使用 UUID 來作爲聚簇索引則會很糟糕：它使得聚簇索引的插入變得完全隨機，這是最壞的情況，使得數據沒有任何聚集特性。

Twitter 的分佈式自增 ID 算法 Snowflake

在分佈式系統中，需要生成全局 UID 的場合還是比較多的，twitter 的 snowflake 解決了這種

需求，實現也還是很簡單的，除去配置信息，核心代碼就是毫秒級時間 41 位機器 ID 10 位毫秒內序列 12 位。

sequenc

在 postgreSQL、oracle、db2 數據庫中有一個特殊的特性—sequence。任何時候數據庫可以根據當前表中的記錄數大小和步長來獲取到該表下一條記錄數。然而，MySQL 是沒有這種序列對象的。

MySQL 可以這樣做，對於插入也就是 insert 操作，首先就是獲取唯一的 id 了，就需要一個表來專門創建 id，插入一條記錄，並獲取最後插入的 ID。也就是說，當我們需要插入數據的時候，必須由這個表來產生 id 值。

這種方法效果很好，但是在高併發情況下，MySQL 的 AUTO_INCREMENT 將導致整個數據庫慢。如果存在自增字段，MySQL 會維護一個自增鎖，innodb 會在內存裏保存一個計數器來記錄 auto_increment 值，當插入一個新行數據時，就會用一個表鎖來鎖住這個計數器，直到插入結束。如果是一行一行的插入是沒有問題的，但是在高併發情況下，那就悲催了，表鎖會引起 SQL 阻塞，極大的影響性能，還可能會達到 max_connections 值。

innodb_autoinc_lock_mode：可以設定 3 個值：0、1、2

0：traditonal （每次都會產生表鎖）
1：consecutive （默認, 可預判行數時使用新方式，不可時使用表鎖，對於 simple insert 會獲得批量的鎖，保證連續插入）
2：interleaved （不會鎖表，來一個處理一個，併發最高）

對於 myisam 表引擎是 traditional，每次都會進行表鎖的。

通過 redis 生成 ID

這種方法將 id 的生成工作交給了 redis，能保證高可用、唯一性。

④ 跨分片的排序分頁

一般來講，分頁時需要按照指定字段進行排序。當排序字段就是分片字段的時候，我們通過分片規則可以比較容易定位到指定的分片，而當排序字段非分片字段的時候，情況就會變得比較複雜了。

爲了終結果的準確性，我們需要在不同的分片節點中將數據進行排序並返回，並將不同分片返回的結果集進行彙總和再次排序，後再返回給用戶。

③ 分片原則

能不分就不分，參考單表優化。

分片數量儘量少，分片儘量均勻分佈在多個數據結點上。因爲一個查詢 SQL 跨分片越多，則總體性能越差。雖然要好於所有數據在一個分片的結果，只在必要的時候進行擴容，增加分片數量。

分片規則需要慎重選擇做好提前規劃，分片規則的選擇，需要考慮數據的增長模式，數據的訪問模式，分片關聯性問題，以及分片擴容問題，最近的分片策略爲範圍分片，枚舉分片，一致性 Hash 分片，這幾種分片都有利於擴容。

儘量不要在一個事務中的 SQL 跨越多個分片，分佈式事務一直是個不好處理的問題。

查詢條件儘量優化，儘量避免 Select * 的方式，大量數據結果集下，會消耗大量帶寬和 CPU 資源，查詢儘量避免返回大量結果集，並且儘量爲頻繁使用的查詢語句建立索引。

通過數據冗餘和表分區賴降低跨庫 Join 的可能。

這裏特別強調一下分片規則的選擇問題，如果某個表的數據有明顯的時間特徵，比如訂單、交易記錄等，則他們通常比較合適用時間範圍分片，因爲具有時效性的數據，我們往往關注其近期的數據，查詢條件中往往帶有時間字段進行過濾，比較好的方案是，當前活躍的數據，採用跨度比較短的時間段進行分片，而歷史性的數據，則採用比較長的跨度存儲。

總體上來說，分片的選擇是取決於最頻繁的查詢 SQL 的條件，因爲不帶任何 Where 語句的查詢 SQL，會遍歷所有的分片，性能相對最差，因此這種 SQL 越多，對系統的影響越大，所以我們要儘量避免這種 SQL 的產生。

【5】數據庫分片方案

由於水平拆分牽涉的邏輯比較複雜，當前也有了不少比較成熟的解決方案。這些方案分爲兩大類：客戶端架構和代理架構。

① 客戶端架構

通過修改數據訪問層，如 JDBC、Data Source、MyBatis，通過配置來管理多個數據源，直連數據庫，並在模塊內完成數據的分片整合，一般以 Jar 包的方式呈現

這是一個客戶端架構的例子：

可以看到分片的實現是和應用服務器在一起的，通過修改 Spring JDBC 層來實現。

客戶端架構的優點是：

應用直連數據庫，降低外圍系統依賴所帶來的宕機風險。
集成成本低，無需額外運維的組件。

缺點是：

限於只能在數據庫訪問層上做文章，擴展性一般，對於比較複雜的系統可能會力不從心。
將分片邏輯的壓力放在應用服務器上，造成額外風險。

② 代理架構

通過獨立的中間件來統一管理所有數據源和數據分片整合，後端數據庫集羣對前端應用程序透明，需要獨立部署和運維代理組件。

這是一個代理架構的例子：

代理組件爲了分流和防止單點，一般以集羣形式存在，同時可能需要 Zookeeper 之類的服務組件來管理。

代理架構的優點是：

能夠處理非常複雜的需求，不受數據庫訪問層原來實現的限制，擴展性強
對於應用服務器透明且沒有增加任何額外負載

缺點是：

需部署和運維獨立的代理中間件，成本高
應用需經過代理來連接數據庫，網絡上多了一跳，性能有損失且有額外風險

③ 各方案比較

如此多的方案，如何進行選擇？可以按以下思路來考慮：

確定是使用代理架構還是客戶端架構。中小型規模或是比較簡單的場景傾向於選擇客戶端架構，複雜場景或大規模系統傾向選擇代理架構。

具體功能是否滿足，比如需要跨節點 ORDER BY，那麼支持該功能的優先考慮。不考慮一年內沒有更新的產品，說明開發停滯，甚至無人維護和技術支持。最好按大公司 -> 社區 -> 小公司 -> 個人這樣的出品方順序來選擇。選擇口碑較好的，比如 github 星數、使用者數量質量和使用者反饋。

開源的優先，往往項目有特殊需求可能需要改動源代碼。按照上述思路，推薦以下選擇：

客戶端架構：ShardingJDBC
代理架構：MyCat 或者 Atlas

④ 兼容 MySQL 且可水平擴展的數據庫

目前也有一些開源數據庫兼容 MySQL 協議，如：TiDB，Cubrid。

但其工業品質和 MySQL 尚有差距，且需要較大的運維投入，如果想將原始的 MySQL 遷移到可水平擴展的新數據庫中，可以考慮一些雲數據庫：

阿里雲 PetaData
阿里雲 OceanBase
騰訊雲 DCDB

⑤ NoSQL

在 MySQL 上做 Sharding 是一種戴着鐐銬的跳舞，事實上很多大表本身對 MySQL 這種 RDBMS 的需求並不大，並不要求 ACID，可以考慮將這些表遷移到 NoSQL，徹底解決水平擴展問題，例如：

日誌類、監控類、統計類數據

非結構化或弱結構化數據

對事務要求不強，且無太多關聯操作的數據

原文鏈接：https://blog.csdn.net/J080624/article/details/127486245

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/nfvf2RcxAvi4yi5Bv2l0vQ

猜你喜歡