禁用內嵌檢視的排序

壹頁書發表於2014-04-25
排序
MySQL會對group by欄位進行排序，但是一些內嵌檢視中的group by本身就是中間過程，沒有必要進行排序。
這種場景可以使用order by null禁用內嵌檢視的排序。

如下SQL
  1. SELECT DISTINCT a.*, c.downNum
  2. FROM sod_artist a INNER JOIN sod_artist_category_relation b ON a.ArtistID = b.ArtistID LEFT JOIN (SELECT d.ArtistID, COUNT(a.DownNum) AS downNum
  3.     FROM (SELECT downnum, songid
  4.         FROM sod_song_ranking1
  5.         WHERE SongDate = 20140422
  6.         ) a INNER JOIN sod_song_artist_relation d ON a.SongID = d.SongID
  7.     GROUP BY d.ArtistID
  8.     ) c ON a.ArtistID = c.ArtistID
  9. WHERE a.State = 1
  10.     AND b.CategoryID = 1363
  11.     OR b.CategoryID = 1628
  12. ORDER BY c.downNum DESC
  13. LIMIT 0, 45
執行計劃:

  1. +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
  2. | id | select_type | table             | type   | possible_keys   | key         | ref               | rows  | Extra                                                     |
  3. +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
  4. | 1  | PRIMARY     | b                 | range  | PRIMARY,Index_1 | PRIMARY     | NULL              | 286   | Using where; Using index; Using temporary; Using filesort |
  5. | 1  | PRIMARY     | a                 | eq_ref | PRIMARY         | PRIMARY     | songod.b.ArtistID | 1     | Using where                                               |
  6. | 1  | PRIMARY     |        | ref    |     | | songod.b.ArtistID | 10    | NULL                                                      |
  7. | 2  | DERIVED     |        | ALL    | NULL            | NULL        | NULL              | 55316 | Using temporary; Using filesort                           |
  8. | 2  | DERIVED     | d                 | ref    | PRIMARY,inx_1   | inx_1       | a.songid          | 1     | Using index                                               |
  9. | 3  | DERIVED     | sod_song_ranking1 | ref    | songdate        | songdate    | const             | 55316 | NULL                                                      |
  10. +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
使用Order By Null禁用排序

  1. SELECT DISTINCT a.*, c.downNum
  2. FROM sod_artist a INNER JOIN sod_artist_category_relation b ON a.ArtistID = b.ArtistID LEFT JOIN (SELECT d.ArtistID, COUNT(a.DownNum) AS downNum
  3.     FROM (SELECT downnum, songid
  4.         FROM sod_song_ranking1
  5.         WHERE SongDate = 20140422
  6.         ) a INNER JOIN sod_song_artist_relation d ON a.SongID = d.SongID
  7.     GROUP BY d.ArtistID order by null
  8.     ) c ON a.ArtistID = c.ArtistID
  9. WHERE a.State = 1
  10.     AND b.CategoryID = 1363
  11.     OR b.CategoryID = 1628
  12. ORDER BY c.downNum DESC
  13. LIMIT 0, 45
執行計劃：
可以看到執行計劃第四行，已經消除了排序。

    1. +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
    2. | id | select_type | table             | type   | possible_keys   | key         | ref               | rows  | Extra                                                     |
    3. +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
    4. | 1  | PRIMARY     | b                 | range  | PRIMARY,Index_1 | PRIMARY     | NULL              | 286   | Using where; Using index; Using temporary; Using filesort |
    5. | 1  | PRIMARY     | a                 | eq_ref | PRIMARY         | PRIMARY     | songod.b.ArtistID | 1     | Using where                                               |
    6. | 1  | PRIMARY     |        | ref    |     |  | songod.b.ArtistID | 10    | NULL                                                      |
    7. | 2  | DERIVED     |        | ALL    | NULL            | NULL        | NULL              | 55316 | Using temporary                                           |
    8. | 2  | DERIVED     | d                 | ref    | PRIMARY,inx_1   | inx_1       | a.songid          | 1     | Using index                                               |
    9. | 3  | DERIVED     | sod_song_ranking1 | ref    | songdate        | songdate    | const             | 55316 | NULL                                                      |
    10. +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+


參考：
http://hudeyong926.iteye.com/blog/785181

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1149590/，如需轉載，請註明出處，否則將追究法律責任。

相關文章