一、前言

分頁查詢的需求如同家常便飯，多數(shù)情況下主要利用order by和limit即可實(shí)現(xiàn)，有些稍復(fù)雜一點(diǎn)的可能需要用到union操作去連接多個(gè)子查詢結(jié)果集。

然而這三個(gè)操作是有一些需要留意的問題，下文將列舉出3個(gè)可能碰到的情況。

MySQL版本：5.7.21

二、問題列舉

2.1 子查詢中不能使用order by

簡單的union操作如下：

SELECT * FROM tb_artist WHERE id >= 10
UNION
SELECT * FROM tb_artist WHERE id < 10

其結(jié)果為：

若子查詢中包含order by，寫法假設(shè)如下：

SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC
UNION
SELECT * FROM tb_artist WHERE id < 10

則語法報(bào)錯(cuò)：1221 - Incorrect usage of UNION and ORDER BY

應(yīng)當(dāng)將子查詢括起來，正確寫法如下：

(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC)
UNION
(SELECT * FROM tb_artist WHERE id < 10)

其結(jié)果為：

語法沒問題了，但是注意到子查詢的order by卻沒有效果，故引出下面第二種問題。

2.2 子查詢order by無效

解決方式是在子查詢的order by后添加limit操作即可，具體可以limit一個(gè)不小于子查詢結(jié)果集大小的數(shù)值，如下：

(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC LIMIT 99999)
UNION
(SELECT * FROM tb_artist WHERE id < 10)

其結(jié)果為：

2.3 排序條件不夠嚴(yán)格導(dǎo)致分頁數(shù)據(jù)重復(fù)

該問題與union無直接聯(lián)系，屬于order by及limit本身的注意點(diǎn)。

即，如果SQL中的order by條件比較寬松不夠嚴(yán)格，或者說是結(jié)果集中的每行記錄存在并列或不唯一的次序的話，MySQL可能會(huì)隨機(jī)給并列記錄行進(jìn)行排序，特別是排序又分頁查詢配合了limit操作，可能會(huì)導(dǎo)致上一頁有的記錄行又出現(xiàn)在了下一頁的結(jié)果集之中。

對一些后臺(tái)系統(tǒng)的分頁表格中可能感覺不明顯，但對于同樣SQL查詢邏輯的C端下拉列表的效果一目了然，特別是對帶有圖片的，很容易看到數(shù)據(jù)的重復(fù)。

解決方案：

所以在做分頁查詢時(shí)，要盡量保證每行記錄都有唯一確定的次序，具體做法可以在原有排序條件后添加id或編號(hào)等這類唯一值的字段（索引字段也可以）。

例如，在藝術(shù)家artist創(chuàng)建時(shí)間降序排序的基礎(chǔ)上，再對唯一code進(jìn)行排序：

SELECT * FROM tb_artist WHERE logic_delete = 0 
ORDER BY 
	create_time DESC, 
	code ASC 
LIMIT 0, 5

原因分析：

排序離不開算法，在關(guān)系型數(shù)據(jù)庫中，往往會(huì)存在多種排序算法。通過MySQL的源碼和官方文檔介紹可以得知，它的排序規(guī)律可以總結(jié)如下：

• 當(dāng)order by不能使用索引進(jìn)行排序時(shí)，將使用排序算法進(jìn)行排序；
• 若排序內(nèi)容能全部放入內(nèi)存，則僅在內(nèi)存中使用快速排序；
• 若排序內(nèi)容不能全部放入內(nèi)存，則分批次將排好序的內(nèi)容放入文件，然后將多個(gè)文件進(jìn)行歸并排序；
• 若排序中包含limit語句，則使用堆排序（不穩(wěn)定）優(yōu)化排序過程。

所以解決排序分頁數(shù)據(jù)重復(fù)問題有兩種方式，第一種就是，在排序中加上唯一值，比如主鍵 id，這樣由于 id 是唯一的，就能確保參與排序的 key 值不相同；第二種就是避免使用堆排序，讓order by根據(jù)索引來排序。

說白了，就是order by后面的字段要有索引。

另外，使用JPA分頁查詢時(shí)，若order by的是非索引字段，通過查看JPA的sql發(fā)現(xiàn)，不會(huì)再去自動(dòng)在order by后添加索引或id字段，需要注意。

MySQL5.7 相關(guān)文檔：

• https://dev.mysql.com/doc/refman/5.7/en/union.html
• https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
• https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評論