踩坑描述:
寫分頁(yè)查詢接口彬伦，只取需要的字段進(jìn)行查詢,order by走的其他字段排序澜共。
在進(jìn)行第二頁(yè)查詢時(shí)板驳，出現(xiàn)與第一頁(yè)的數(shù)據(jù)一樣的記錄色瘩。
實(shí)例:
SELECT
`id`,
`name`
FROM
test_table
WHERE
`status` = 1
ORDER BY
c_time desc
LIMIT
1, 5
若使用上述sql查詢,很有可能會(huì)出現(xiàn)與 LIMIT 0,5 相同的記錄伪窖。
若使用如下方式，則恢復(fù)正常的情況：
SELECT
*
FROM
test_table
WHERE
`status` = 1
ORDER BY
c_time desc
LIMIT
1, 5
業(yè)務(wù)場(chǎng)景需要居兆，我們不可能把test_table表的所有的字段都取出來覆山。
而是希望拿出我們需要用到的字段。
為了解決上述的情況泥栖，可以使用以下的手段：
1.在ORDER BY使用索引或主鍵排序
SELECT
*
FROM
test_table
WHERE
`status` = 1
ORDER BY
id asc,
LIMIT
1, 5
2.在ORDER BY使用兩個(gè)排序條件解決
SELECT
*
FROM
test_table
WHERE
`status` = 1
ORDER BY
c_time desc
id asc,
LIMIT
1, 5
由上可知簇宽，mysql的排序默認(rèn)走的是id主鍵作為排序條件。則在c_time相等的情況下吧享，主鍵id就作為默認(rèn)的排序條件魏割，就不需要多此一舉加上id asc。
但是事實(shí)上钢颂，mysql在和order by和limit混用的時(shí)候钞它，出現(xiàn)了排序的混亂情況。
1.問題分析
查閱相關(guān)資料了解到，mysq5.6的版本上遭垛，優(yōu)化器在遇到order by limit語句的時(shí)候尼桶，做了一個(gè)優(yōu)化，即使用了priority queue耻卡。
使用priority queue的目的:
就是在不能使用索引有序性的時(shí)候疯汁，若要排序，并且使用了limit n卵酪，那么只需要在排序的過程中幌蚊，保留n條記錄即可。這樣雖然不能解決所有記錄都需要排序的開銷溃卡，但是只需要sort buffer 少量的內(nèi)存就可以完成排序溢豆。
所以mysql5.6以上版本出現(xiàn)了第二頁(yè)數(shù)據(jù)重復(fù)的問題，就是因?yàn)閜riority queue 使用了堆排序的排序方法瘸羡，而堆排序 是一個(gè)不穩(wěn)定的排序方法漩仙，也就是相同的值可能排序出來的結(jié)果和讀出來的數(shù)據(jù)順序不一致。
（mysql5.5沒有這個(gè)優(yōu)化犹赖，所有不會(huì)有這個(gè)問題）
再來看下mysql解釋sql語句時(shí)的執(zhí)行語句:
(1) SELECT
(2) DISTINCT <select_list>
(3) FROM <left_table>
(4) <join_type> JOIN <right_table>
(5) ON <join_condition>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
執(zhí)行順序?yàn)?
from / where / select / order by / limit
由于priority queue的原因队他，在完成select之后，所有記錄以堆排序的方法排列的峻村。
在進(jìn)行order by時(shí)麸折，僅把c_time值大的行往前移動(dòng)。
但是由于limit的因素粘昨，排序過程中只需要保留到5條記錄即可垢啼。c_time并不具備索引有序性。所以當(dāng)?shù)诙?yè)數(shù)據(jù)展示的時(shí)候张肾，mysql見到哪一條就拿哪一條芭析，因此，當(dāng)排序值相同的時(shí)候吞瞪，第一次排序是隨意排序的馁启，第二次再執(zhí)行該sql的時(shí)候，其結(jié)果應(yīng)該和第一次的結(jié)果一樣芍秆。
2.解決方法
1.索引排序字段
如果在字段添加上索引惯疙，就直接按照索引的有序性進(jìn)行讀取并分頁(yè)，從而規(guī)避此類問題浪听。
2.正確理解分頁(yè)
分頁(yè)是建立在排序的基礎(chǔ)上螟碎，進(jìn)行了數(shù)量范圍的分割眉菱。
排序是數(shù)據(jù)庫(kù)提供的功能迹栓，而分頁(yè)是咱們衍生的應(yīng)用需求。
在MySQL和Oracle的官方文檔中提供了limit n和rownum < n的方法俭缓，但卻沒有明確的定義分頁(yè)這個(gè)概念克伊。
還有重要的一點(diǎn)酥郭，雖然上面的的解決方法可以緩解問題，但是依然還會(huì)有問題：比如愿吹，這個(gè)表插入頻繁不从，用戶在查詢的時(shí)候，在read-committted的隔離級(jí)別下犁跪，第一頁(yè)和第二頁(yè)依然會(huì)有重合椿息。
所以，分頁(yè)一直都有這個(gè)問題坷衍，不同場(chǎng)景對(duì)數(shù)據(jù)分頁(yè)都沒有非常高的準(zhǔn)確性要求寝优。
3.一些常見的數(shù)據(jù)庫(kù)排序問題
不加order by時(shí)候的排序問題
用戶在使用mysql或者使用oracle的時(shí)候，會(huì)發(fā)現(xiàn)mysql總是有序的枫耳，oracle卻很混亂乏矾，這是因?yàn)閛racle是堆表，而mysql是索引聚簇表迁杨。 所在沒有order by的時(shí)候钻心，數(shù)據(jù)庫(kù)并不保證記錄返回的順序性，并且不保證每次返回都一致铅协。
分頁(yè)問題 分頁(yè)重復(fù)的問題
如前面所描述的捷沸，分頁(yè)是在數(shù)據(jù)庫(kù)提供的排序功能的基礎(chǔ)上，衍生出來的應(yīng)用需求警医，數(shù)據(jù)庫(kù)并不保證分頁(yè)的重復(fù)問題亿胸。
NULL值和空串問題
不同的數(shù)據(jù)對(duì)于null和空串的理解和處理是不一樣的，比如oracle null和null值是無法比較的预皇，既不是相等也是不相等侈玄，是未知的。而對(duì)于空串吟温，在插入的時(shí)候序仙，MySQL是一個(gè)字符串長(zhǎng)度為0的空串，而Oracle則直接進(jìn)行NULL值處理鲁豪。
本文章為記錄整理,感謝博主分享
www.reibang.com/p/544c319fd838
