超全面！Java核心知識總結(jié)（點擊查看）

超全面！Java核心知識總結(jié)（點擊查看）

在MySQL中我們常常用order by來進行排序，使用limit來進行分頁，當需要先排序后分頁時我們往往使用類似的寫法select * from 表名 order by 排序字段 limt M,N。但是這種寫法卻隱藏著較深的使用陷阱。在排序字段有數(shù)據(jù)重復的情況下，會很容易出現(xiàn)排序結(jié)果與預期不一致的問題。

比如現(xiàn)在有一張user表，表結(jié)構(gòu)及數(shù)據(jù)如下：

現(xiàn)在想根據(jù)創(chuàng)建時間升序查詢user表，并且分頁查詢，每頁2條，那很容易寫出sql為：

select * from user order by create_time limit pageNo,2;

在執(zhí)行查詢過程中會發(fā)現(xiàn)：

1、查詢第一頁數(shù)據(jù)時：

2、查詢第四頁數(shù)據(jù)時：

user表共有8條數(shù)據(jù)，有4頁數(shù)據(jù)，但是實際查詢過程中第一頁與第四頁竟然出現(xiàn)了相同的數(shù)據(jù)。

這是什么情況？難道上面的分頁SQL不是先將兩個表關(guān)聯(lián)查詢出來，然后再排好序，再取對應分頁的數(shù)據(jù)嗎？？？

上面的實際執(zhí)行結(jié)果已經(jīng)證明現(xiàn)實與想像往往是有差距的，實際SQL執(zhí)行時并不是按照上述方式執(zhí)行的。這里其實是Mysql會對Limit做優(yōu)化，具體優(yōu)化方式見官方文檔：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html這個是5.7版本的說明，提取幾個問題直接相關(guān)的點做下說明。

上面官方文檔里面有提到如果你將Limit rowcount與order by混用，mysql會找到排序的rowcount行后立馬返回，而不是排序整個查詢結(jié)果再返回。如果是通過索引排序，會非?？欤蝗绻俏募判颍衅ヅ洳樵兊男校ú粠imit的）都會被選中，被選中的大多數(shù)或者全部會被排序，直到limit要求的rowcount被找到了。如果limit要求的rowcount行一旦被找到，Mysql就不會排序結(jié)果集中剩余的行了。

這里我們查看下對應SQL的執(zhí)行計劃：

可以確認是用的文件排序，表確實也沒有加額外的索引。所以我們可以確定這個SQL執(zhí)行時是會找到limit要求的行后立馬返回查詢結(jié)果的。

不過就算它立馬返回，為什么分頁會不準呢？

官方文檔里面做了如下說明：

如果order by的字段有多個行都有相同的值，mysql是會隨機的順序返回查詢結(jié)果的，具體依賴對應的執(zhí)行計劃。也就是說如果排序的列是無序的，那么排序的結(jié)果行的順序也是不確定的。

基于這個我們就基本知道為什么分頁會不準了，因為我們排序的字段是create_time，正好又有幾個相同的值的行，在實際執(zhí)行時返回結(jié)果對應的行的順序是不確定的。對應上面的情況，第一頁返回的name為8的數(shù)據(jù)行，可能正好排在前面，而第四頁查詢時name為8的數(shù)據(jù)行正好排在后面，所以第四頁又出現(xiàn)了。

那這種情況應該怎么解決呢？

官方給出了解決方案：

如果想在Limit存在或不存在的情況下，都保證排序結(jié)果相同，可以額外加一個排序條件。例如id字段是唯一的，可以考慮在排序字段中額外加個id排序去確保順序穩(wěn)定。

所以上面的情況下可以在SQL再添加個排序字段，比如fundflow的id字段，這樣分頁的問題就解決了。修改后的SQL可以像下面這樣：SELECT * FROM user ORDER BY createtime,id LIMIT 6,2;

再次測試問題解決?。?/span>

作者：丘八老爺

blog.csdn.net/qiubabin/article/details/70135556

如有文章對你有幫助，
“在看”和轉(zhuǎn)發(fā)是對我最大的支持！

推薦， GitHub 書籍倉庫
https://github.com/ebooklist/awesome-ebooks-list
整理了大部分常用 技術(shù)書籍PDF，持續(xù)更新中... 你需要的技術(shù)書籍，這里可能都有...

點擊文末“閱讀原文”可直達
整理不易，麻煩各位小伙伴在GitHub中來個Star支持一下