SQL最佳化:組內排序取最大值
最近業務反饋一個查詢異常的問題,需要DBA對查詢結果異常給出解釋,並幫助他們解決該問題。問題本質是一個組內排序取最大值的問題,根據業務需求,我構建了測試用例
測試用例
--建表
create table testorder
(id int not null,
no int not null,
name char(10) not null,
primary key(id)
)engine=innodb;
--寫入資料
insert into testorder values (1,1,'Mike'),(2,2,'John'),(3,3,'wyett'),(4,4,'Herry'),(5,5,'Mike'),(6,1,'John'),(7,2,'John'),(8,1,'Mike'),(9,1,'Mike');
--查詢1
select * from testorder;
+----+----+-------+
| id | no | name |
+----+----+-------+
| 1 | 1 | Mike |
| 2 | 2 | John |
| 3 | 3 | wyett |
| 4 | 4 | Herry |
| 5 | 5 | Mike |
| 6 | 1 | John |
| 7 | 2 | John |
| 8 | 1 | Mike |
| 9 | 1 | Mike |
+----+----+-------+
--查詢2
select * from testorder order by no desc;
+----+----+-------+
| id | no | name |
+----+----+-------+
| 5 | 5 | Mike |
| 4 | 4 | Herry |
| 3 | 3 | wyett |
| 2 | 2 | John |
| 7 | 2 | John |
| 1 | 1 | Mike |
| 6 | 1 | John |
| 8 | 1 | Mike |
| 9 | 1 | Mike |
+----+----+-------+
--查詢3 select * from (select id,no,name from testorder order by no desc)a group by a.name;
查詢3這條SQL是我們需要討論的內容,也是業務線為實現組內排序取最大值所採用的SQL。標準的程式設計師反饋問題方式:XXX時間點之前查詢時正常的,這之後突然就不正常了,你們DBA是不是做什麼改動了?我把資料恢復到自己的測試機,返回值也是正常的。暫且不去管姿勢是否正確,對這條SQL的分析,我們其實可以看出:(1)程式設計師期待group by執行結果是按照臨時表a的資料順序來取值;(2)程式設計師未考慮版本因素,資料量變化的因素;為此,我構建了上面的測試用例。
測試
在不同版本的MySQL來進行測試:發現在Percona 5.5,Percona 5.1,MySQL 5.6關閉sql_mode= ONLY_FULL_GROUP_BY,MySQL5.1等版本下,返回值確如程式設計師期待的順序,按照order by no desc的順序,相同name返回no值最大的資料;
+----+----+-------+ | id | no | name | +----+----+-------+ | 4 | 4 | Herry | | 2 | 2 | John | | 5 | 5 | Mike | | 3 | 3 | wyett | +----+----+-------+
在mysql5.7,關閉sql_mode= ONLY_FULL_GROUP_BY和mariadb 10.*版本中,相同的name值,返回則是取了最早寫入的資料行,忽略了order by no desc,按照資料的邏輯儲存順序來返回;
+----+----+-------+ | id | no | name | +----+----+-------+ | 4 | 4 | Herry | | 2 | 2 | John | | 1 | 1 | Mike | | 3 | 3 | wyett | +----+----+-------+
其實在這裡,SQL等價於select id,no,name from testorder group by name。
這裡我們看出不同版本的返回值是不同的,先擱置資料量的變化引起執行結果不同的討論,因為資料量大小很難測試。
官方文件
對上面的測試結果,在官方文件上,有如下的參考
If ONLY_FULL_GROUP_BY is disabled...In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses.
ONLY_FULL_GROUP_BY這個SQL_MODE出在mysql5.6(mariadb 10.0)時被引入,但本文討論的內容和它無關,具體可以自己檢視文件,這裡不做討論。在5.6,5.5的官方文件有相同的內容,Mariadb也有類似的解釋
If you select a non-grouped column or a value computed from a non-grouped column, it is undefined which row the returned value is taken from. This is not permitted if the ONLY_FULL_GROUP_BY SQL_MODE is used.
並且,對from後的subquery子表中的order by也給出瞭解釋
A query such as SELECT field1, field2 FROM ( SELECT field1, field2 FROM table1 ORDER BY field2 ) alias returns a result set that is not necessarily ordered by field2. This is not a bug. 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.
好了,有了這些解釋,問題很明朗:
- 在from 後的subquery中的order by會被忽略
- group by cloumn返回的行是無序的
因此,業務獲得的正確的返回值也是誤打誤撞。
解決辦法
那麼這個問題該怎麼解決?
在網上有一些SQL,很明顯不滿足需求,在這裡做一下展示,希望同學們避免被誤導:
錯誤SQL集合
select id,sbustring(GROUP_CONCAT(distinct no order by no desc separator ''),'',1),name from testorder group by name;
--透過新增索引來影響返回的結果集順序 alter table testorder add index idx_no_name(no desc, name); --結果證明即使如此,desc也不會被正確執行;
--我司程式設計師的寫法 select * from (select id,no,name from testorder order by no desc)a group by a.name
select id,max(no),name from testorder group by name
我們可以這樣寫,雖然效率不高
select a.id,a.no,a.name from testorder a inner join (select max(no) no,name from testorder group by name) b on a.no=b.no and a.name=b.name group by name,no
或者這樣
select a.id,a.no,a.name from testorder a group by a.name,a.no having a.no=(select max(no) from testorder where name=a.name)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2136211/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL優化:組內排序取最大值SQL優化排序
- SQL 分組排序取最新一條記錄SQL排序
- 選取組內最小值的sqlSQL
- SQL 分組排序group bySQL排序
- MySQL GROUP BY分組取欄位最大值MySql
- Mysql 分組排序的sql寫法MySql排序
- MapReduce最佳化之位元組級別快速排序排序
- sql取每組最新資料SQL
- mysql使用group by實現組內排序實戰MySql排序
- MySQL分組排序取前N條記錄 以及 生成自動數字序列 的SQLMySql排序
- JavaScript 陣列排序 與 求最大值JavaScript陣列排序
- SQL Server 如何合併組內字串SQLServer字串
- oracle最佳化sql的內部過程OracleSQL
- SQL Server對組合查詢結果排序方法SQLServer排序
- mongodb 取欄位最大值MongoDB
- SQL SERVER 儲存過程 獲取三個數中的最大值SQLServer儲存過程
- sql分組查詢語句--行內分組(非聚合分組)SQL
- sql 按著時間分組,每組取 20 條資料SQL
- oracle分組查詢獲得組的最大值記錄Oracle
- JavaScript 獲取陣列中最大值JavaScript陣列
- sql最佳化:使用sql profile最佳化sql語句SQL
- MySql先分組統計總記錄數,再獲取記錄數中的最大值MySql
- 常用SQL Server:取所有型別的前10條並排序SQLServer型別排序
- order by改分組排序排序
- MySQL 分組排序後 → 如何取前N條或倒數N條MySql排序
- 微課sql最佳化(9)、如何獲取執行計劃SQL
- SQL最佳化SQL
- Oracle效能最佳化之最佳化排序操作(轉)Oracle排序
- ms sql server排序SQLServer排序
- SQL自定義排序SQL排序
- js如何獲取陣列中的最大值JS陣列
- SQL Server一行多列求最大值SQLServer
- sql 多組條資料取最新的一條資料SQL
- Sql group by 分組取時間最新的一條資料SQL
- 【SQL最佳化】SQL最佳化的10點注意事項SQL
- 快取最佳化(快取穿透)快取穿透
- 資料庫分組查詢最大值的問題資料庫
- MySQL實現分組排序MySql排序