分布式數(shù)據(jù)庫(kù)的分頁(yè)方案
本篇文章介紹了在 DRDS 上做時(shí)間序數(shù)據(jù)分頁(yè)展示的一種方法，初看簡(jiǎn)單，實(shí)則細(xì)節(jié)較多，需要一定的關(guān)系型數(shù)據(jù)庫(kù)基礎(chǔ)，也是我們用戶在實(shí)際使用產(chǎn)品過(guò)程中碰到的問(wèn)題之一，這里分享給大家。##
來(lái)實(shí)現(xiàn)一個(gè)分頁(yè)吧
日常業(yè)務(wù)需求中，按時(shí)間序?qū)?shù)據(jù)進(jìn)行分頁(yè)展示是一個(gè)非常普遍的需求，單機(jī)關(guān)系型數(shù)據(jù)庫(kù)有比較標(biāo)準(zhǔn)化的解決方案，但是這個(gè)需求在分布式數(shù)據(jù)庫(kù)中實(shí)現(xiàn)，粗看比較簡(jiǎn)單，實(shí)際上卻相當(dāng)復(fù)雜，如果要做好，需要有比較扎實(shí)的關(guān)系型數(shù)據(jù)庫(kù)認(rèn)知。
這個(gè)問(wèn)題分為兩個(gè)部分：</br>
- 功能性要求：
? a.數(shù)據(jù)需按照時(shí)間順序進(jìn)行返回
? b.所有數(shù)據(jù)均需要被遍歷到，不能有遺漏
? c.每頁(yè)返回100條數(shù)據(jù)
? d.同一個(gè)時(shí)間精度內(nèi)（例如同一個(gè)毫秒內(nèi)），會(huì)有多條數(shù)據(jù)同時(shí)被插入 - 性能要求：
? a.性能不能隨頁(yè)碼的增加而衰減，需要高效并且恒定
? b.性能不能隨著數(shù)據(jù)量的增加而衰減，例如10W條數(shù)據(jù)的時(shí)候很高效，100億條數(shù)據(jù)的時(shí)候也很高效
單機(jī)數(shù)據(jù)庫(kù)的做法
? 對(duì)于單機(jī)數(shù)據(jù)庫(kù)而言，以MySQL 為例，功能部分，直接按照自增主鍵從小到大排列即可，因?yàn)樽栽鲋麈I的大小能夠區(qū)分出數(shù)據(jù)生成的前后關(guān)系，所以功能上不存在問(wèn)題。
? 性能部分優(yōu)化，在 SQL帶其他過(guò)濾條件的情況下，可以將扁平的帶 LIMIT m,n 語(yǔ)句優(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ù)庫(kù)中實(shí)現(xiàn)的難點(diǎn)
??對(duì)于單機(jī)數(shù)據(jù)庫(kù)來(lái)說(shuō)，因?yàn)榇嬖跁r(shí)間序的自增主鍵，這個(gè)需求變得比較簡(jiǎn)單，但是對(duì)于分布式數(shù)據(jù)庫(kù)而言，這個(gè)問(wèn)題就變得比較復(fù)雜，主要因?yàn)橹麈I大小和數(shù)據(jù)生成時(shí)間并沒(méi)有本質(zhì)關(guān)聯(lián)，即使是分布式強(qiáng)一致數(shù)據(jù)庫(kù)，保證嚴(yán)格時(shí)間序代價(jià)也很高，這個(gè)導(dǎo)致只能通過(guò)類似 GMT_CREATE 這種時(shí)間字段進(jìn)行排序分頁(yè)，但是 GMT_CREATE 可能重復(fù)，或者存在大量重復(fù)，這個(gè)導(dǎo)致分頁(yè)按時(shí)間排序處理變得更加復(fù)雜。
??另外性能層面，我們?nèi)缤?code>MySQL 不能使用扁平的帶LIMIT m,n語(yǔ)句進(jìn)行分頁(yè)處理，但是同時(shí)也不能優(yōu)化成帶子查詢的分頁(yè)語(yǔ)句，因?yàn)閿?shù)據(jù)分片的原因，需要將 LIMIT m,n優(yōu)化成 LIMIT 0,m+n,分頁(yè)挪到非常大的時(shí)候，需要返回到 DRDS 大量數(shù)據(jù),再 skip掉不必要的數(shù)據(jù)，即使做了倒序優(yōu)化，一張拆分表性能最差的一頁(yè)數(shù)據(jù)查詢需要返回表中一半的數(shù)據(jù)才能滿足需求，這個(gè)在拆分表有10億或者100億數(shù)據(jù)的時(shí)候，很難滿足性能需求。
DRDS的方案
??對(duì)這個(gè)問(wèn)題仔細(xì)分析后，我們提出了一個(gè)方案。為了避免返回大量的中間結(jié)果數(shù)據(jù)，我們希望不指定 LIMIT 的 OFFSET，而是用上一頁(yè)的最大值直接跳轉(zhuǎn)到下一頁(yè)的起始處，因此業(yè)務(wù)做一定的妥協(xié)，功能上增加如下約束：
??1. 提供下一頁(yè)、上一頁(yè)、首頁(yè)、尾頁(yè)功能
??2. 可以在當(dāng)前頁(yè)相鄰的幾頁(yè)進(jìn)行跳轉(zhuǎn)（例如前后10頁(yè)）
??3. 不允許做任意頁(yè)的跳轉(zhuǎn)
對(duì)于單機(jī)數(shù)據(jù)庫(kù)，我們可以使用如下的 SQL 實(shí)現(xiàn)上述的需求：
SELECT * FROM table WHERE pk> 上一頁(yè)pk最大值 ORDRE BY pk LIMIT n
分布式數(shù)據(jù)庫(kù)：
增加 GMT_CREATE 字段，其默認(rèn)值為
CURRENT_TIMESTAMP我們?nèi)匀话凑?
GMT_CREATE時(shí)間字段進(jìn)行第一個(gè)維度排序，但是GMT_CREATE有重復(fù)，我們希望有一個(gè)固定順序，所以再以主鍵為第二個(gè)維度排序，所以排序部分為ORDER BY gmt_create,pk。-
我們記住每一頁(yè)
GMT_CREATE和PK的最大值作為下一頁(yè)數(shù)據(jù)的起始值，但是前面提到過(guò)GMT_CREATE有數(shù)據(jù)重復(fù)，如果通過(guò)簡(jiǎn)單AND條件拼接，會(huì)導(dǎo)致漏數(shù)據(jù)，所以我們將條件寫為：gmt_create>= ? AND(gmt_create> ?or pk> ?)
從查詢邏輯上規(guī)避掉GMT_CREATE 重復(fù)帶來(lái)的可能漏數(shù)據(jù)的狀況，分頁(yè) SQL 變成了這個(gè)：
SELECT *
FROM page_test
WHERE gmt_create>= ?
AND(gmt_create> ?
OR pk> ?)
ORDER BY gmt_create,
pk LIMIT n
當(dāng)然在MySQL 5.7上，我們還可以直接做二元組的比較（5.7之前的版本，多元組的比較 MySQL 無(wú)法利用到組合索引）：
SELECT *
FROM page_test
WHERE(gmt_create, pk)> （?, ?）ORDER BY gmt_create, pk LIMIT n
結(jié)果驗(yàn)證和總結(jié)
如下圖page_test3表，數(shù)據(jù)量為36億多條，表結(jié)構(gòu)如圖所示，總共拆了96張表，4個(gè)RDS存儲(chǔ)數(shù)據(jù)。
其中數(shù)據(jù)起始值如下:
如果我們挑選數(shù)據(jù)集中間的值，從下圖看性能也很好，所以只要按照這套方案做分布式數(shù)據(jù)庫(kù)分頁(yè)或者全量掃描數(shù)據(jù)，性能將不會(huì)劣化，可以嚴(yán)格按照時(shí)間序排列，并且不會(huì)掃漏已經(jīng)存在于數(shù)據(jù)庫(kù)中的數(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();
}
}
}
}
