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

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

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

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

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

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

一、問題背景

在我們的考試系統(tǒng)中，用戶可以多次考試，然后需要去用戶最新的考試記錄，而且是需要批量去取多個用戶的最新的考試記錄或者單個用戶的多個最新考試記錄，之前寫的sql是子查詢根據(jù)時間排序，然后進(jìn)行分組取最新的。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)境復(fù)現(xiàn)不了，線上能穩(wěn)定復(fù)現(xiàn)，很是詭異。

二、問題排查

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

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

同事的Mysql版本如下：

用戶考試記錄數(shù)據(jù)如下：

SELECT * FROM user_exam where exam_id = 156;

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

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

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

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

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

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;

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

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

三、問題解決

看到網(wǎng)上也有網(wǎng)友遇到這個問題，通過在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限制獲取到的結(jié)果是正確的，因為有數(shù)量限制，我們可以把limit的數(shù)值設(shè)置大一些，讓它能cover住所需后取數(shù)量的最大值。

當(dāng)然還有其他的解決辦法，可以結(jié)合實際的業(yè)務(wù)需求進(jìn)行優(yōu)化。

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

最新評論