分庫分表索引設計：二級索引、全局索引的最佳設計實踐

作者：讓我來搞這個 bug
鏈接：https://www.jianshu.com/p/071c4ee7a532

主鍵選擇

對主鍵來說，要保證在所有分片中都唯一，它本質上就是一個全局唯一的索引。如果用大部分同學喜歡的自增作爲主鍵，就會發現存在很大的問題。

因爲自增並不能在插入前就獲得值，而是要通過填 NULL 值，然後再通過函數 last_insert_id() 獲得自增的值。所以，如果在每個分片上通過自增去實現主鍵，可能會出現同樣的自增值存在於不同的分片上。

比如，對於電商的訂單表 orders，其表結構如下（分片鍵是 o_custkey，表的主鍵是 o_orderkey）：

CREATE TABLE `orders` (
  `O_ORDERKEY` int NOT NULL auto_increment,
  `O_CUSTKEY` int NOT NULL,
  `O_ORDERSTATUS` char(1) NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) NOT NULL,
  `O_CLERK` char(15) NOT NULL,
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79) NOT NULL,
  PRIMARY KEY (`O_ORDERKEY`),
  KEY (`O_CUSTKEY`)
  ......
) ENGINE=InnoDB

如果把 o_orderkey 設計成上圖所示的自增，那麼很可能 o_orderkey 同爲 1 的記錄在不同的分片出現，如下圖所示：

所以，在分佈式數據庫架構下，儘量不要用自增作爲表的主鍵：自增性能很差、安全性不高、不適用於分佈式架構。

講到這兒，我們已經說明白了 “自增主鍵” 的所有問題，那麼該如何設計主鍵呢？依然還是用全局唯一的鍵作爲主鍵，比如 MySQL 自動生成的有序 UUID；業務生成的全局唯一鍵（比如發號器）；或者是開源的 UUID 生成算法，比如雪花算法（但是存在時間回溯的問題）。

總之，用有序的全局唯一替代自增，是這個時代數據庫主鍵的主流設計標準，如果你還停留在用自增做主鍵，或許代表你已經落後於時代發展了。

索引設計

通過分片鍵可以把 SQL 查詢路由到指定的分片，但是在現實的生產環境中，業務還要通過其他的索引訪問表。

還是以前面的表 orders 爲例，如果業務還要根據 o_orderkey 字段進行查詢，比如查詢訂單 ID 爲 1 的訂單詳情：

SELECT * FROM orders WHERE o_orderkey = 1

我們可以看到，由於分片規則不是分片鍵，所以需要查詢 4 個分片才能得到最終的結果，如果下面有 1000 個分片，那麼就需要執行 1000 次這樣的 SQL，這時性能就比較差了。

但是，我們知道 o_orderkey 是主鍵，應該只有一條返回記錄，也就是說，o_orderkey 只存在於一個分片中。這時，可以有以下兩種設計：

這兩種設計的本質都是通過冗餘實現空間換時間的效果，否則就需要掃描所有的分片，當分片數據非常多，效率就會變得極差。

而第一種做法通過對錶進行冗餘，對於 o_orderkey 的查詢，只需要在 o_orderkey = 1 的分片中直接查詢就行，效率最高，但是設計的缺點又在於冗餘數據量太大。

所以，改進的做法之一是實現一個索引表，表中只包含 o_orderkey 和分片鍵 o_custkey，如：

CREATE TABLE idx_orderkey_custkey （
  o_orderkey INT
  o_custkey INT,
  PRIMARY KEY (o_orderkey)
)

如果這張索引表很大，也可以將其分庫分表，但是它的分片鍵是 o_orderkey，如果這時再根據字段 o_orderkey 進行查詢，可以進行類似二級索引的回表實現：先通過查詢索引表得到記錄 o_orderkey = 1 對應的分片鍵 o_custkey 的值，接着再根據 o_custkey 進行查詢，最終定位到想要的數據，如：

SELECT * FROM orders WHERE o_orderkey = 1

=>

# step 1
SELECT o_custkey FROM idx_orderkey_custkey 
WHERE o_orderkey = 1

# step 2
SELECT * FROM orders 
WHERE o_custkey = ? AND o_orderkey = 1

這個例子是將一條 SQL 語句拆分成 2 條 SQL 語句，但是拆分後的 2 條 SQL 都可以通過分片鍵進行查詢，這樣能保證只需要在單個分片中完成查詢操作。不論有多少個分片，也只需要查詢 2 個分片的信息，這樣 SQL 的查詢性能可以得到極大的提升。

通過索引表的方式，雖然存儲上較冗餘全表容量小了很多，但是要根據另一個分片鍵進行數據的存儲，依然顯得不夠優雅。

因此，最優的設計，不是創建一個索引表，而是將分片鍵的信息保存在想要查詢的列中，這樣通過查詢的列就能直接知道所在的分片信息。

如果我們將訂單表 orders 的主鍵設計爲一個字符串，這個字符串中最後一部分包含分片鍵的信息，如：

o_orderkey = string（o_orderkey + o_custkey）

那麼這時如果根據 o_orderkey 進行查詢：

SELECT * FROM Orders
WHERE o_orderkey = '1000-1';

由於字段 o_orderkey 的設計中直接包含了分片鍵信息，所以我們可以直接知道這個訂單在分片 1 中，直接查詢分片 1 就行。

同樣地，在插入時，由於可以知道插入時 o_custkey 對應的值，所以只要在業務層做一次字符的拼接，然後再插入數據庫就行了。

這樣的實現方式較冗餘表和索引表的設計來說，效率更高，查詢可以提前知道數據對應的分片信息，只需 1 次查詢就能獲取想要的結果。

這樣實現的缺點是，主鍵值會變大一些，存儲也會相應變大。但只要主鍵值是有序的，插入的性能就不會變差。而通過在主鍵值中保存分片信息，卻可以大大提升後續的查詢效率，這樣空間換時間的設計，總體上看是非常值得的。

當然，這裏我們談的設計都是針對於唯一索引的設計，如果是非唯一的二級索引查詢，那麼非常可惜，依然需要掃描所有的分片才能得到最終的結果，如：

SELECT * FROM Orders
WHERE o_orderate >= ? o_orderdate < ?

因此，再次提醒你，分佈式數據庫架構設計的要求是業務的絕大部分請求能夠根據分片鍵定位到 1 個分片上。

如果業務大部分請求都需要掃描所有分片信息才能獲得最終結果，那麼就不適合進行分佈式架構的改造或設計。

最後，我們再來回顧下淘寶用戶訂單表的設計：

上圖是我的淘寶訂單信息，可以看到，訂單號的最後 6 位都是 308113，所以可以大概率推測出：

全局表

在分佈式數據庫中，有時會有一些無法提供分片鍵的表，但這些表又非常小，一般用於保存一些全局信息，平時更新也較少，絕大多數場景僅用於查詢操作。

例如 tpch 庫中的表 nation，用於存儲國家信息，但是在我們前面的 SQL 關聯查詢中，又經常會使用到這張表，對於這種全局表，可以在每個分片中存儲，這樣就不用跨分片地進行查詢了。如下面的設計：

唯一索引

最後我們來談談唯一索引的設計，與主鍵一樣，如果只是通過數據庫表本身唯一約束創建的索引，則無法保證在所有分片中都是唯一的。

所以，在分佈式數據庫中，唯一索引一樣要通過類似主鍵的 UUID 的機制實現，用全局唯一去替代局部唯一，但實際上，即便是單機的 MySQL 數據庫架構，我們也推薦使用全局唯一的設計。因爲你不知道，什麼時候，你的業務就會升級到全局唯一的要求了。

總結

今天介紹了非常重要的分佈式數據庫索引設計，內容非常乾貨，是分佈式架構設計的重中之重，建議反覆閱讀，抓住本文的重點，總結來說：

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

猜你喜歡