MySQL会对group by字段进行排序，但是一些内嵌视图中的group by本身就是中间过程，没有必要进行排序。
这种场景可以使用order by null禁用内嵌视图的排序。
如下SQL
执行计划:
使用Order By Null禁用排序
执行计划：
可以看到执行计划第四行，已经消除了排序。
参考：
http://hudeyong926.iteye.com/blog/785181
这种场景可以使用order by null禁用内嵌视图的排序。
如下SQL
-
SELECT DISTINCT a.*, c.downNum
-
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
-
FROM (SELECT downnum, songid
-
FROM sod_song_ranking1
-
WHERE SongDate = 20140422
-
) a INNER JOIN sod_song_artist_relation d ON a.SongID = d.SongID
-
GROUP BY d.ArtistID
-
) c ON a.ArtistID = c.ArtistID
-
WHERE a.State = 1
-
AND b.CategoryID = 1363
-
OR b.CategoryID = 1628
-
ORDER BY c.downNum DESC
- LIMIT 0, 45
- +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | ref | rows | Extra |
- +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
- | 1 | PRIMARY | b | range | PRIMARY,Index_1 | PRIMARY | NULL | 286 | Using where; Using index; Using temporary; Using filesort |
- | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | songod.b.ArtistID | 1 | Using where |
-
| 1 | PRIMARY |
| ref | | | songod.b.ArtistID | 10 | NULL | -
| 2 | DERIVED |
| ALL | NULL | NULL | NULL | 55316 | Using temporary; Using filesort | - | 2 | DERIVED | d | ref | PRIMARY,inx_1 | inx_1 | a.songid | 1 | Using index |
- | 3 | DERIVED | sod_song_ranking1 | ref | songdate | songdate | const | 55316 | NULL |
- +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
-
SELECT DISTINCT a.*, c.downNum
-
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
-
FROM (SELECT downnum, songid
-
FROM sod_song_ranking1
-
WHERE SongDate = 20140422
-
) a INNER JOIN sod_song_artist_relation d ON a.SongID = d.SongID
- GROUP BY d.ArtistID order by null
-
) c ON a.ArtistID = c.ArtistID
-
WHERE a.State = 1
-
AND b.CategoryID = 1363
-
OR b.CategoryID = 1628
-
ORDER BY c.downNum DESC
- LIMIT 0, 45
可以看到执行计划第四行，已经消除了排序。
-
- +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | ref | rows | Extra |
- +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
- | 1 | PRIMARY | b | range | PRIMARY,Index_1 | PRIMARY | NULL | 286 | Using where; Using index; Using temporary; Using filesort |
- | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | songod.b.ArtistID | 1 | Using where |
-
| 1 | PRIMARY |
| ref | | | songod.b.ArtistID | 10 | NULL | -
| 2 | DERIVED |
| ALL | NULL | NULL | NULL | 55316 | Using temporary | - | 2 | DERIVED | d | ref | PRIMARY,inx_1 | inx_1 | a.songid | 1 | Using index |
- | 3 | DERIVED | sod_song_ranking1 | ref | songdate | songdate | const | 55316 | NULL |
- +----+-------------+-------------------+--------+-----------------+-------------+-------------------+-------+-----------------------------------------------------------+
-
参考：
http://hudeyong926.iteye.com/blog/785181
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29254281/viewspace-1149590/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
