一個(gè)偶然的機(jī)會(huì)，發(fā)現(xiàn)一條SQL語句在不同的MySQL實(shí)例上執(zhí)行得到了不同的結(jié)果苦囱。
問題描述
創(chuàng)建商品表product_tbl和商品操作記錄表product_operation_tbl兩個(gè)表造壮，來模擬下業(yè)務(wù)場(chǎng)景淫茵，結(jié)構(gòu)和數(shù)據(jù)如下：
接下來需要查詢所有商品最新的修改時(shí)間茶宵，使用如下語句：
select t1.id, t1.name, t2.product_id, t2.created_at from product_tbl t1 left join (select * from product_operation_log_tbl order by created_at desc) t2 on t1.id = t2.product_id group by t1.id;
通過結(jié)果可以看到状答，子查詢先將product_operation_log_tbl里的所有記錄按創(chuàng)建時(shí)間(created_at)逆序擎勘，然后和product_tbl進(jìn)行join操作咱揍，進(jìn)而查詢出的商品的最新修改時(shí)間。
在區(qū)域A的MySQL實(shí)例上棚饵，查詢商品最新修改時(shí)間可以得到正確結(jié)果煤裙，但是在區(qū)域B的MySQL實(shí)例上掩完，得到的修改時(shí)間并不是最新的，而是最老的硼砰。通過對(duì)語句進(jìn)行簡(jiǎn)化且蓬，發(fā)現(xiàn)是子查詢中的order by created_at desc語句在區(qū)域B的實(shí)例上沒有生效。
排查過程
難道區(qū)域會(huì)影響MySQL的行為题翰？經(jīng)過DBA排查恶阴，區(qū)域A的MySQL是5.6版，區(qū)域B的MySQL是5.7版豹障，并且找到了這篇文章：
mysql 5.7 5.6排序mysql 5.6升級(jí)到5.7之后 子查詢里面的order排序無效火鍋與理想的博客-CSDN博客
根據(jù)文章的描述冯事，MySQL 5.7版會(huì)忽略掉子查詢中的order by語句，可令人疑惑的是血公，我們模擬業(yè)務(wù)場(chǎng)景的MySQL是8.0版昵仅，并沒有出現(xiàn)這個(gè)問題。使用docker分別啟動(dòng)MySQL 5.6累魔、5.7摔笤、8.0三個(gè)實(shí)例，來重復(fù)上面的操作垦写，結(jié)果如下：
可以看到吕世，只有MySQL 5.7版忽略了子查詢中的order by。有沒有可能是5.7引入了bug梯投，后續(xù)版本又修復(fù)了呢命辖？
問題根因
繼續(xù)搜索文檔和資料，發(fā)現(xiàn)官方論壇中有這樣一段描述：
A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order).
You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT.
問題的原因清晰了晚伙，原來SQL標(biāo)準(zhǔn)中吮龄，table的定義是一個(gè)未排序的數(shù)據(jù)集合，而一個(gè)SQL子查詢是一個(gè)臨時(shí)的table咆疗，根據(jù)這個(gè)定義漓帚，子查詢中的order by會(huì)被忽略。同時(shí)午磁，官方回復(fù)也給出了解決方案：將子查詢的order by移動(dòng)到最外層的select語句中尝抖。
總結(jié)
- 在SQL標(biāo)準(zhǔn)中，子查詢中的order by是不生效的
- MySQL 5.7由于在這個(gè)點(diǎn)上遵循了SQL標(biāo)準(zhǔn)導(dǎo)致問題暴露迅皇，而在MySQL 5.6/8.0中這種寫法依然是生效的
參考文檔
https://stackoverflow.com/questions/26372511/mysql-mariadb-order-by-inside-subquery
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/
