MySQL中order by在子查詢中失效的問(wèn)題

我們的項(xiàng)目MySQL版本是5.7，之前有一張圖書表，里面有三個(gè)關(guān)鍵字段type(圖書類型)和book_name（圖書名稱）和visits_num（瀏覽量）字段，遇到一個(gè)需求需要分別查詢出電子書和紙質(zhì)書瀏覽量最高的書籍，我心想不是很簡(jiǎn)單嗎，只需要先將圖書表按瀏覽量倒序然后再分組（分組會(huì)保留第一條記錄）即可，于是三下五除二就寫出了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)原來(lái)在5.7版本中會(huì)忽略掉子查詢中的order by語(yǔ)句，也就是排序被優(yōu)化掉了，可以通過(guò)在子查詢中添加limit來(lái)顯式的限制生成的子查詢結(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排序無(wú)效問(wèn)題的探究

一、問(wèn)題背景

在我們的考試系統(tǒng)中，用戶可以多次考試，然后需要去用戶最新的考試記錄，而且是需要批量去取多個(gè)用戶的最新的考試記錄或者單個(gè)用戶的多個(gè)最新考試記錄，之前寫的sql是子查詢根據(jù)時(shí)間排序，然后進(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

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

二、問(wèn)題排查

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

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

同事的Mysql版本如下：

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

SELECT * FROM user_exam where exam_id = 156;

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

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

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

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

放入group by語(yǔ)句中作為子查詢獲取結(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)建時(shí)間最新的用戶考試記錄，即主鍵為142935，但是可以看到，獲取到的結(jié)果并不是最新創(chuàng)建的記錄，而是最早創(chuàng)建的那條用戶考試記錄，我們知道group by 通過(guò)分組條件取數(shù)據(jù)時(shí)，是取的滿足條件的第一條數(shù)據(jù)，那么就好像我們的子查詢中order by create_time desc并沒有起作用。

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

三、問(wèn)題解決

看到網(wǎng)上也有網(wǎng)友遇到這個(gè)問(wèn)題，通過(guò)在order by 后加limit限制來(lái)解決問(wèn)題，因?yàn)樵贛ySQL的5.7版本中，如果group by的子查詢中包含order by，但是order by不與limit配合使用，order by會(huì)被忽略掉。嘗試如下：

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;

通過(guò)加limit限制獲取到的結(jié)果是正確的，因?yàn)橛袛?shù)量限制，我們可以把limit的數(shù)值設(shè)置大一些，讓它能cover住所需后取數(shù)量的最大值。

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

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

最新評(píng)論