分布式數(shù)據(jù)庫的分頁方案
本篇文章介紹了在 DRDS 上做時間序數(shù)據(jù)分頁展示的一種方法趾疚，初看簡單，實則細節(jié)較多以蕴，需要一定的關(guān)系型數(shù)據(jù)庫基礎(chǔ)糙麦，也是我們用戶在實際使用產(chǎn)品過程中碰到的問題之一，這里分享給大家丛肮。##
來實現(xiàn)一個分頁吧
日常業(yè)務需求中赡磅，按時間序?qū)?shù)據(jù)進行分頁展示是一個非常普遍的需求，單機關(guān)系型數(shù)據(jù)庫有比較標準化的解決方案宝与，但是這個需求在分布式數(shù)據(jù)庫中實現(xiàn)仆邓，粗看比較簡單，實際上卻相當復雜伴鳖，如果要做好节值，需要有比較扎實的關(guān)系型數(shù)據(jù)庫認知。
這個問題分為兩個部分：</br>
- 功能性要求：
? a.數(shù)據(jù)需按照時間順序進行返回
? b.所有數(shù)據(jù)均需要被遍歷到榜聂，不能有遺漏
? c.每頁返回100條數(shù)據(jù)
? d.同一個時間精度內(nèi)（例如同一個毫秒內(nèi)）搞疗，會有多條數(shù)據(jù)同時被插入 - 性能要求：
? a.性能不能隨頁碼的增加而衰減，需要高效并且恒定
? b.性能不能隨著數(shù)據(jù)量的增加而衰減须肆，例如10W條數(shù)據(jù)的時候很高效匿乃，100億條數(shù)據(jù)的時候也很高效
單機數(shù)據(jù)庫的做法
? 對于單機數(shù)據(jù)庫而言，以MySQL 為例豌汇，功能部分幢炸，直接按照自增主鍵從小到大排列即可，因為自增主鍵的大小能夠區(qū)分出數(shù)據(jù)生成的前后關(guān)系拒贱，所以功能上不存在問題宛徊。
? 性能部分優(yōu)化佛嬉，在 SQL帶其他過濾條件的情況下，可以將扁平的帶 LIMIT m,n 語句優(yōu)化成嵌套子查詢以便讓優(yōu)化器做索引覆蓋闸天，避免在磁盤上遍歷數(shù)據(jù)暖呕，SQL 如下所示:
SELECT *
FROM table a JOIN(
SELECT pk FROMtable
WHERE some_column= ?
ORDER BY pk LIMIT m, n) b ON a.pk= b.pk
在分布式數(shù)據(jù)庫中實現(xiàn)的難點
??對于單機數(shù)據(jù)庫來說，因為存在時間序的自增主鍵苞氮，這個需求變得比較簡單湾揽，但是對于分布式數(shù)據(jù)庫而言，這個問題就變得比較復雜笼吟，主要因為主鍵大小和數(shù)據(jù)生成時間并沒有本質(zhì)關(guān)聯(lián)库物，即使是分布式強一致數(shù)據(jù)庫，保證嚴格時間序代價也很高贷帮，這個導致只能通過類似 GMT_CREATE 這種時間字段進行排序分頁戚揭，但是 GMT_CREATE 可能重復，或者存在大量重復皿桑，這個導致分頁按時間排序處理變得更加復雜。
??另外性能層面蔬啡，我們?nèi)缤?code>MySQL 不能使用扁平的帶LIMIT m,n語句進行分頁處理诲侮，但是同時也不能優(yōu)化成帶子查詢的分頁語句，因為數(shù)據(jù)分片的原因箱蟆，需要將 LIMIT m,n優(yōu)化成 LIMIT 0,m+n,分頁挪到非常大的時候沟绪，需要返回到 DRDS 大量數(shù)據(jù),再 skip掉不必要的數(shù)據(jù)，即使做了倒序優(yōu)化空猜，一張拆分表性能最差的一頁數(shù)據(jù)查詢需要返回表中一半的數(shù)據(jù)才能滿足需求绽慈，這個在拆分表有10億或者100億數(shù)據(jù)的時候，很難滿足性能需求辈毯。
DRDS的方案
??對這個問題仔細分析后坝疼，我們提出了一個方案。為了避免返回大量的中間結(jié)果數(shù)據(jù)谆沃，我們希望不指定 LIMIT 的 OFFSET钝凶，而是用上一頁的最大值直接跳轉(zhuǎn)到下一頁的起始處，因此業(yè)務做一定的妥協(xié)唁影，功能上增加如下約束：
??1. 提供下一頁耕陷、上一頁、首頁据沈、尾頁功能
??2. 可以在當前頁相鄰的幾頁進行跳轉(zhuǎn)（例如前后10頁）
??3. 不允許做任意頁的跳轉(zhuǎn)
對于單機數(shù)據(jù)庫哟沫，我們可以使用如下的 SQL 實現(xiàn)上述的需求：
SELECT * FROM table WHERE pk> 上一頁pk最大值 ORDRE BY pk LIMIT n
分布式數(shù)據(jù)庫：
增加 GMT_CREATE 字段，其默認值為
CURRENT_TIMESTAMP我們?nèi)匀话凑?
GMT_CREATE時間字段進行第一個維度排序锌介，但是GMT_CREATE有重復嗜诀，我們希望有一個固定順序猾警，所以再以主鍵為第二個維度排序，所以排序部分為ORDER BY gmt_create,pk裹虫。-
我們記住每一頁
GMT_CREATE和PK的最大值作為下一頁數(shù)據(jù)的起始值肿嘲，但是前面提到過GMT_CREATE有數(shù)據(jù)重復，如果通過簡單AND條件拼接筑公，會導致漏數(shù)據(jù)雳窟，所以我們將條件寫為：gmt_create>= ? AND(gmt_create> ?or pk> ?)
從查詢邏輯上規(guī)避掉GMT_CREATE 重復帶來的可能漏數(shù)據(jù)的狀況，分頁 SQL 變成了這個：
SELECT *
FROM page_test
WHERE gmt_create>= ?
AND(gmt_create> ?
OR pk> ?)
ORDER BY gmt_create,
pk LIMIT n
當然在MySQL 5.7上匣屡，我們還可以直接做二元組的比較（5.7之前的版本封救，多元組的比較 MySQL 無法利用到組合索引）：
SELECT *
FROM page_test
WHERE(gmt_create, pk)> （?, ?）ORDER BY gmt_create, pk LIMIT n
結(jié)果驗證和總結(jié)
如下圖page_test3表，數(shù)據(jù)量為36億多條捣作，表結(jié)構(gòu)如圖所示誉结，總共拆了96張表，4個RDS存儲數(shù)據(jù)券躁。
其中數(shù)據(jù)起始值如下:
如果我們挑選數(shù)據(jù)集中間的值惩坑，從下圖看性能也很好，所以只要按照這套方案做分布式數(shù)據(jù)庫分頁或者全量掃描數(shù)據(jù)也拜，性能將不會劣化以舒，可以嚴格按照時間序排列，并且不會掃漏已經(jīng)存在于數(shù)據(jù)庫中的數(shù)據(jù)慢哈。
代碼
package com.taobao.tddl.sample;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.alibaba.druid.pool.DruidDataSource;
public class PageSample {
public static void main(String[] args) throws Exception {
DruidDataSource ds = new DruidDataSource();
ds.setUrl(
"jdbc:mysql://drdsxxxx.drds.aliyuncs.com:3306/dbname?characterEncoding=utf8&rewriteBatchedStatements=true&clobberStreamingResults=true&allowMultiQueries=true");
ds.setUsername("user");
ds.setPassword("password");
ds.init();
int index = 0;
boolean first = true;
Object maxGmtCreate = null;
long maxId = -1;
while (true) {
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement ps = null;
if (first) {
ps = conn.prepareStatement("SELECT * FROM page_test order by gmt_create,id limit 99");
first = false;
} else {
ps = conn.prepareStatement(
"SELECT * FROM page_test where gmt_create >= ? and (gmt_create > ? or id > ?) order by gmt_create,id limit 99");
ps.setObject(1, maxGmtCreate);
ps.setObject(2, maxGmtCreate);
ps.setLong(3, maxId);
}
ResultSet rs = ps.executeQuery();
maxGmtCreate = null;
maxId = -1;
while (rs.next()) {
System.out.println((++index) + " " + rs.getInt("id") + " " + rs.getString("gmt_Create"));
maxGmtCreate = rs.getObject("gmt_create");
maxId = rs.getLong("id");
}
if (maxId == -1) {
break;
}
} finally {
conn.close();
}
}
}
}
