[MySQL]Order By Null

        第一次看到MySQL可以用Order By Null時很訝異，而且這樣用並不是要將Null排在最前還是最後，而是不需要排序的意思，但要配合Group By使用

        像SQL Server不支援這樣寫；Oracle可以這樣寫Order By Null First或Order By Null Last，看是Null值要放前放後而已

        MySQL的Group By有隱含排序的效果，意思就是會花額外的資源去做排序，這會有效能的疑慮，我自己測試在Group By Table1.Col, Table2.Col這種情況下，最為明顯，在執行計畫Extra欄位可以看到Using filesort，這就表示有額外去作排序喔，可參考下面的例子

DROP TABLE numlist;

DROP TABLE ref;

CREATE TABLE numlist AS SELECT @ROW:=@ROW + 1 AS id,

    CASE MOD(@ROW, 3)

        WHEN 1 THEN 1

        WHEN 2 THEN 2

        ELSE 3

    END AS num FROM

    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t,

    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,

    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3,

    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4,

    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t5,

    (SELECT @ROW:=0) t0;

CREATE  INDEX idx_num ON numlist(id);

CREATE  INDEX idx_num1 ON numlist(num);

SELECT

    *

FROM

    numlist;

CREATE TABLE ref AS SELECT 1 id, 'a' name

UNION ALL SELECT 2 id, 'b' name

UNION ALL SELECT 3 id, 'c' name;

CREATE INDEX idx_ref ON ref(id);

EXPLAIN

SELECT SQL_NO_CACHE  n.id, name ,COUNT(*)

FROM numlist n INNER JOIN ref r ON n.num = r.id

GROUP BY n.id, name;

EXPLAIN

SELECT SQL_NO_CACHE  n.id, name ,COUNT(*)

FROM numlist n INNER JOIN ref r ON n.num = r.id

GROUP BY n.id, name ORDER BY NULL;

        從上面兩張圖的執行計畫，可看到Group By配合Order By NULL，的確可去除額外排序Using filesort喔

       這奇怪的特性，據說在5.6會棄用，我自己沒試過，有興趣的自己試試

       可參考MySQL :: MySQL 5.6 Reference Manual :: 13.2.9 SELECT Syntax

Relying on implicit GROUP BY sorting in MySQL 5.6 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.