MySQL中order by在子查詢中失效的問題

我們的項目MySQL版本是5.7，之前有一張圖書表，里面有三個關鍵字段type(圖書類型)和book_name（圖書名稱）和visits_num（瀏覽量）字段，遇到一個需求需要分別查詢出電子書和紙質書瀏覽量最高的書籍，我心想不是很簡單嗎，只需要先將圖書表按瀏覽量倒序然后再分組（分組會保留第一條記錄）即可，于是三下五除二就寫出了sql

select type, book_name
from (select * from book order by visits_num desc) t1
group by type

結果一運行發(fā)現和預想的結果集不一樣，研究才發(fā)現原來在5.7版本中會忽略掉子查詢中的order by語句，也就是排序被優(yōu)化掉了，可以通過在子查詢中添加limit來顯式的限制生成的子查詢結果集

select type, book_name
from (select * from book order by visits_num desc limit 9999) t1
group by type

Mysql 5.7版本導致的子查詢order by排序無效問題的探究

一、問題背景

在我們的考試系統(tǒng)中，用戶可以多次考試，然后需要去用戶最新的考試記錄，而且是需要批量去取多個用戶的最新的考試記錄或者單個用戶的多個最新考試記錄，之前寫的sql是子查詢根據時間排序，然后進行分組取最新的。sql如下：

select * from (select * from `user_exam` where uid = 666 and exam_id in (1,2,3) order by create_time desc) as t group by t.exam_id

開始這樣寫的時候，在測試環(huán)境和線上都沒什么問題，不過最近線上有用戶反饋獲取到的考試記錄列表不是最新的，但是測試在測試環(huán)境復現不了，線上能穩(wěn)定復現，很是詭異。

二、問題排查

1.檢查相關緩存，并于數據庫數據對比，發(fā)現二者并不一致，刪除緩存后，本以為可以正常了，但是獲取后發(fā)現仍是存在問題，更新后的緩存仍然是錯誤數據；

2.對SQL語句進行排查，語法等均為發(fā)現問題；在本地對sql語句進行執(zhí)行驗證，在我本地執(zhí)行sql語句是正常沒問題的，但是在同事本地執(zhí)行就會出現線上的問題；

同事的Mysql版本如下：

用戶考試記錄數據如下：

SELECT * FROM user_exam where exam_id = 156;

該考試僅有一個用戶考試，考了三次，其主鍵id依次為142933、142934、142935.

對比按創(chuàng)建時間排序的子查詢語句獲取到的結果：

select * from user_exam where uid = 229031 and exam_id=156 order by create_time desc;

根據創(chuàng)建時間排序，其主鍵id依次仍為142933、142934、142935.

放入group by語句中作為子查詢獲取結果如下：

select * from (select * from user_exam where uid = 229031 and exam_id = 156 order by create_time desc) as t group by t.exam_id;

我們期望的結果應該為創(chuàng)建時間最新的用戶考試記錄，即主鍵為142935，但是可以看到，獲取到的結果并不是最新創(chuàng)建的記錄，而是最早創(chuàng)建的那條用戶考試記錄，我們知道group by 通過分組條件取數據時，是取的滿足條件的第一條數據，那么就好像我們的子查詢中order by create_time desc并沒有起作用。

知道了問題所在，我便從sql語句中order by不起作用作為切入點進行排查，看網上說MySQL的5.7版本當order by在子查詢中時會存在失效情況，對于這種情況，我找了公司運維求證，他們說線上最新的MySQL實例大部分變?yōu)榱?.7，做了升級。這也進一步印證了前面上線后都是正常的，最近才出問題，原因在于當時MySQL版本不是5.7，現在變?yōu)榱?.7版本導致子查詢order by失效。

三、問題解決

看到網上也有網友遇到這個問題，通過在order by 后加limit限制來解決問題，因為在MySQL的5.7版本中，如果group by的子查詢中包含order by，但是order by不與limit配合使用，order by會被忽略掉。嘗試如下：

select * from (select * from user_exam where uid = 229031 and exam_id = 156 order by create_time desc limit 10) as t group by t.exam_id;

通過加limit限制獲取到的結果是正確的，因為有數量限制，我們可以把limit的數值設置大一些，讓它能cover住所需后取數量的最大值。

當然還有其他的解決辦法，可以結合實際的業(yè)務需求進行優(yōu)化。

到此這篇關于MySQL中order by在子查詢中失效的問題解決的文章就介紹到這了,更多相關mysql order by在子查詢中失效內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論