背景

在日常開發(fā)中，不可避免的要用到分批查詢或分頁查詢，其中的場景有很多，有的是WEB頁面的分頁查詢效果，或移動端向下滑動的分頁查詢，有的則是因為目標(biāo)數(shù)據(jù)量巨大，不得已而分批查詢。無論是出于性能考慮，還是大報文考慮，抑或頁面的效果，分批或分頁查詢都是研發(fā)的日常。

?

?

本文嘗試，對日常項目用到的分批分頁查詢做一下方案的回顧和淺析。

查詢場景及方案

一、普通分批分頁查詢場景

方案1 普通LIMIT OFFSET分頁查詢方式

通過數(shù)據(jù)庫直接LIMIT OFFSET 的方式是最簡單，也是最常用的分頁查詢方式。

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

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

?

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

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

丟棄這1,000,000條記錄

返回接下來的10條記錄

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

?

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

?

方案2 基于子查詢或二次查詢的分頁查詢

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中進行分頁處理，還可以通過二次查詢的方式來實現(xiàn)。

第一步，先分頁查詢id列表；

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

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

?

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

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方式是錯誤的，SQL語法不支持：

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 錯誤 [1235] [42000]: This version of SQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

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

?

方案3 游標(biāo)分頁，滾動式查詢

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，通過id滾動式查詢，縮小檢索范圍。

?

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

?

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

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

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

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

?

優(yōu)勢

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

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

?

劣勢

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

?

?

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

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

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

?

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

?

優(yōu)勢

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

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

?

劣勢

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

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

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

?

三、內(nèi)存分頁查詢場景

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

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

?

內(nèi)存分頁方案

?

?

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

總結(jié)

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

審核編輯 黃宇