MySQL與MariaDB功能對比 ---子查詢中order by的處理
本文最初於2016年發表在我的個人微信公眾號裡面，現發布在這裡。本文的技術信息針對的是mysql-5.7.x和mariadb-10.1.9。
MariaDB 聲稱是 MySQL的drop-in replacement，也就是說可以直接替換程序就可以使用。那麼，這兩者的功能是不是100%完全相同呢 ？ 很多用戶都非常關心這個問題。其實還是有蠻多細微的差別的，後面我會發文若干篇講一講這些差別。
MySQL與MariaDB對嵌套的查詢語句當中的order by的處理方法不同。MySQL會忠實執行內層查詢的排序子句，但是MariaDB會將這個order by去掉，理論依據就是關係理論 --- 一個表是行的集合，因此沒有順序要求。由於嵌套的查詢語句是外層查詢的數據表，因此可以以任何順序提供給外層查詢。如果在內層查詢語句中不僅有order by,還有limit子句，那麼這時這個order by是不會被MariaDB忽略的，因為此時的行的順序會決定哪些行會返回給外層查詢。 MySQL的查詢執行過程mysql> create table t1 (a int, b int);Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 values(1, 3),(3, 2),(2, 4);Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0 mysql> select*from (select * from t1 order by a) as t2;+------+------+
| a | b |
+------+------+| 1 | 3 || 2 | 4 || 3 | 2 |+------+------+3 rows in set (0.00 sec) mysql> select*from (select * from t1 order by b) as t2;+------+------+| a | b |
+------+------+| 3 | 2 || 1 | 3 || 2 | 4 |+------+------+3 rows in set (0.00 sec) MariaDB的查詢執行過程MariaDB [test]> create table t1 (a int, b int);Query OK, 0 rows affected (0.06 sec) MariaDB [test]> insert into t1 values(1, 3),(3, 2),(2, 4);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> select*from (select * from t1 order by a) as t2;+------+------+
| a | b |
+------+------+| 1 | 3 || 3 | 2 || 2 | 4 |+------+------+3 rows in set (0.00 sec) MariaDB [test]> select*from (select * from t1 order by b) as t2;+------+------+| a | b |
+------+------+| 1 | 3 || 3 | 2 || 2 | 4 |+------+------+3 rows in set (0.00 sec) MariaDB [test]> select*from (select * from t1 order by a limit 10000000) as t2;+------+------+| a | b |
+------+------+| 1 | 3 || 2 | 4 || 3 | 2 |+------+------+3 rows in set (0.00 sec) MariaDB [test]> select*from (select * from t1 order by b limit 10000000) as t2;+------+------+| a | b |
+------+------+| 3 | 2 || 1 | 3 || 2 | 4 |+------+------+3 rows in set (0.00 sec)
推薦閱讀：
