背景

在日常開(kāi)發(fā)中，不可避免的要用到分批查詢或分頁(yè)查詢，其中的場(chǎng)景有很多，有的是WEB頁(yè)面的分頁(yè)查詢效果，或移動(dòng)端向下滑動(dòng)的分頁(yè)查詢，有的則是因?yàn)槟繕?biāo)數(shù)據(jù)量巨大，不得已而分批查詢。無(wú)論是出于性能考慮，還是大報(bào)文考慮，抑或頁(yè)面的效果，分批或分頁(yè)查詢都是研發(fā)的日常。

?

?

本文嘗試，對(duì)日常項(xiàng)目用到的分批分頁(yè)查詢做一下方案的回顧和淺析。

查詢場(chǎng)景及方案

一、普通分批分頁(yè)查詢場(chǎng)景

方案1 普通LIMIT OFFSET分頁(yè)查詢方式

通過(guò)數(shù)據(jù)庫(kù)直接LIMIT OFFSET 的方式是最簡(jiǎn)單，也是最常用的分頁(yè)查詢方式。

SELECT
	id,
	warehouse_no,
	location_no,
	sku,
	sku_level,
	lot_no,
	pack_code,
	owner_no,
	extend_content
FROM
	st_stock
WHERE
	deleted = 0
	AND warehouse_no = '6_666'
ORDER BY
	id ASC 
LIMIT 100,10

該方法直接簡(jiǎn)單，開(kāi)發(fā)和運(yùn)維簡(jiǎn)單，可讀性高，但當(dāng)offset值（偏移量）非常大時(shí)，弊端也比較明顯：深分頁(yè)性能問(wèn)題比較嚴(yán)重，例如 LIMIT 1000000, 10 。

?

當(dāng)執(zhí)行LIMIT 1000000, 10時(shí)，SQL的處理流程是：

掃描并讀取前1,000,000條記錄

丟棄這1,000,000條記錄

返回接下來(lái)的10條記錄

這意味著即使只需要10條數(shù)據(jù)，數(shù)據(jù)庫(kù)也必須訪問(wèn)和處理大量的"無(wú)用"數(shù)據(jù)。

?

簡(jiǎn)言之，深分頁(yè)，IO開(kāi)銷大：需要讀取大量無(wú)用數(shù)據(jù)頁(yè)；內(nèi)存消耗高：大量數(shù)據(jù)加載到內(nèi)存后被丟棄；CPU消耗高：排序、過(guò)濾操作消耗大量CPU資源。

?

方案2 基于子查詢或二次查詢的分頁(yè)查詢

SELECT
	s.id,
	warehouse_no,
	location_no,
	sku,
	sku_level,
	lot_no,
	pack_code,
	owner_no,
	extend_content
FROM
	st_stock s
JOIN
	(
		SELECT
			id
		FROM
			st_stock
		WHERE
			deleted = 0
			AND warehouse_no = '6_666'
		ORDER BY
			id ASC LIMIT 100,10
	)
	s2
ON
	s.id = s2.id

或

SELECT
    s.id,
    s.warehouse_no,
    s.location_no,
    s.sku,
    s.sku_level,
    s.lot_no,
    s.pack_code,
    s.owner_no,
    s.extend_content
FROM st_stock s
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT id
        FROM st_stock
        WHERE deleted = 0
            AND warehouse_no = '6_666'
        ORDER BY id ASC
        LIMIT 100,10
    ) AS s2
    WHERE s.id = s2.id
);

?

除了直接在SQL中進(jìn)行分頁(yè)處理，還可以通過(guò)二次查詢的方式來(lái)實(shí)現(xiàn)。

第一步，先分頁(yè)查詢id列表；

SELECT id
FROM st_stock
WHERE deleted = 0
    AND warehouse_no = '6_666'
ORDER BY id ASC
LIMIT 100,10;

id字段有主鍵索引，避免回表。

?

第二步，以第一步的id列表作為in條件，查詢庫(kù)存信息。

SELECT
    id,
    warehouse_no,
    location_no,
    sku,
    sku_level,
    lot_no,
    pack_code,
    owner_no,
    extend_content
FROM st_stock
WHERE id IN (id1, id2, id3, ...);

?

注意：下面的SQL方式是錯(cuò)誤的，SQL語(yǔ)法不支持：

SELECT
	id,
	warehouse_no,
	location_no,
	sku,
	sku_level,
	lot_no,
	pack_code,
	owner_no,
	extend_content
FROM
	st_stock s
    where id in 
	(
		SELECT
			id
		FROM
			st_stock
		WHERE
			deleted = 0
			AND warehouse_no = '6_666'
		ORDER BY
			id ASC LIMIT 100,10
	)

SQL 錯(cuò)誤 [1235] [42000]: This version of SQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

解決方案就是使用上面的方式實(shí)現(xiàn)。

?

方案3 游標(biāo)分頁(yè)，滾動(dòng)式查詢

SELECT
	id,
	warehouse_no,
	location_no,
	sku,
	sku_level,
	lot_no,
	pack_code,
	owner_no,
	extend_content
FROM
	st_stock
WHERE
	deleted = 0
	AND warehouse_no = '6_666'
        AND id > 100
ORDER BY
	id ASC 
LIMIT 10

與方案一相比，最大的區(qū)別是增加了id條件，本次id的條件是上一次查詢結(jié)果集中的最大id，通過(guò)id滾動(dòng)式查詢，縮小檢索范圍。

?

上圖就是一個(gè)游標(biāo)分頁(yè)查詢的案例。

?

二、動(dòng)態(tài)數(shù)據(jù)分批分頁(yè)導(dǎo)出查詢場(chǎng)景

對(duì)于動(dòng)態(tài)變化的數(shù)據(jù)，想要分批分頁(yè)導(dǎo)出，而且想要保證數(shù)據(jù)的準(zhǔn)確性，該如何處理呢？

方案1 對(duì)目標(biāo)數(shù)據(jù)加鎖

將導(dǎo)出條件對(duì)應(yīng)的目標(biāo)數(shù)據(jù)鎖定，導(dǎo)出結(jié)束后再解鎖這批數(shù)據(jù)。導(dǎo)出時(shí)間被鎖定的數(shù)據(jù)行，不能update、delete，可以select。

?

優(yōu)勢(shì)

?可以保持在導(dǎo)出期間穩(wěn)定導(dǎo)出數(shù)據(jù)，減少因?yàn)閿?shù)據(jù)的動(dòng)態(tài)變化影響數(shù)據(jù)的準(zhǔn)確性。

?如果在導(dǎo)出期間，符合條件的數(shù)據(jù)庫(kù)行有新增（insert），在數(shù)據(jù)庫(kù)主鍵ID遞增的情況下，新增行的id更大，排序在后，可以正常導(dǎo)出這部分新增數(shù)據(jù)，不受影響。

?

劣勢(shì)

?鎖定的這部分導(dǎo)出數(shù)據(jù)，在導(dǎo)出期間，只讀，不能執(zhí)行寫服務(wù)，相當(dāng)于停產(chǎn)導(dǎo)出，適合于生產(chǎn)低谷時(shí)段或停產(chǎn)時(shí)段進(jìn)行導(dǎo)出。

?

?

方案2 生成導(dǎo)出數(shù)據(jù)快照

將導(dǎo)出條件對(duì)應(yīng)的目標(biāo)數(shù)據(jù)生成導(dǎo)出庫(kù)存快照數(shù)據(jù)，導(dǎo)出執(zhí)行是將本次版本的快照數(shù)據(jù)導(dǎo)出，導(dǎo)出數(shù)據(jù)快照過(guò)時(shí)可以清理。

實(shí)時(shí)數(shù)據(jù)

?

快照數(shù)據(jù)

?

優(yōu)勢(shì)

?在數(shù)據(jù)導(dǎo)出期間穩(wěn)定導(dǎo)出數(shù)據(jù)，每次導(dǎo)出的數(shù)據(jù)都有單獨(dú)的導(dǎo)出數(shù)據(jù)快照版本，導(dǎo)出期間數(shù)據(jù)的準(zhǔn)確性得到保障。

?在數(shù)據(jù)導(dǎo)出期間，即使有數(shù)據(jù)的變化，也不影響導(dǎo)出效果。不鎖數(shù)據(jù)行，不影響生成生產(chǎn)作業(yè)。

?

劣勢(shì)

?如果在導(dǎo)出期間，符合條件的數(shù)據(jù)庫(kù)行有新增（insert），這部分?jǐn)?shù)據(jù)即使符合導(dǎo)出條件，也不會(huì)導(dǎo)出，因?yàn)檫@部分新增的數(shù)據(jù)在導(dǎo)出數(shù)據(jù)快照之后生成，并未在快照數(shù)據(jù)中。

?需要生成導(dǎo)出數(shù)據(jù)快照，導(dǎo)出數(shù)據(jù)快照版本需要單獨(dú)的庫(kù)表存儲(chǔ)，同時(shí)也會(huì)占用磁盤資源。

?導(dǎo)出數(shù)據(jù)快照生成期間，倘若符合條件的數(shù)據(jù)行有變化，需要對(duì)快照數(shù)據(jù)生成特殊處理，比如一次性生成快照等方式。

?

三、內(nèi)存分頁(yè)查詢場(chǎng)景

在日常研發(fā)過(guò)程中遇到的分頁(yè)查詢，大部分都可以借助SQL數(shù)據(jù)庫(kù)、ES等存儲(chǔ)中間件自身的分頁(yè)功能實(shí)現(xiàn)，但個(gè)別場(chǎng)景下并不符合，比如數(shù)據(jù)并未存儲(chǔ)在SQL數(shù)據(jù)庫(kù)或ES中，而是內(nèi)存計(jì)算出來(lái)的一種結(jié)果數(shù)據(jù)；或者數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)維度并不符合，并不能通過(guò)簡(jiǎn)單的GROUP BY等方式實(shí)現(xiàn)維度加工；或者數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)，需要通過(guò)第三方RPC遠(yuǎn)程接口實(shí)時(shí)獲取特殊屬性打標(biāo)過(guò)濾后，才可以作為目標(biāo)數(shù)據(jù)使用。

在這些場(chǎng)景下，我們會(huì)用到內(nèi)存分頁(yè)的方式處理。

?

內(nèi)存分頁(yè)方案

?

?

上面的示例，是一個(gè)簡(jiǎn)單的內(nèi)存分頁(yè)處理方式。

總結(jié)

本文回顧了日常研發(fā)過(guò)程中，經(jīng)常遇到的普通分批分頁(yè)查詢場(chǎng)景、動(dòng)態(tài)數(shù)據(jù)分批分頁(yè)導(dǎo)出查詢場(chǎng)景、內(nèi)存分頁(yè)查詢等場(chǎng)景，探討了對(duì)應(yīng)的解決方案。方案并非固定一成不變的，也有各自的利弊和局限性，在合適場(chǎng)景下，選擇合適的方案即可。

審核編輯 黃宇