MySQL 8.0中SELECT时的默认排序列

在SELECT时如果没有显式的添加ORDER BY条件, MySQL会按什么顺序返回结果呢?

是按 PK 再 UNIQUE INDEX 的顺序吗?

那可不一定哦!

环境: win10 + MySQL 8.0.27

这里只讨论 InnoDB, 如果你想看 MyISAM, 请自行研究

来看一个实际发生的例子。我们建一个省份的表（省份名称唯一），然后存入几个省份名称（这里用的加拿大的），完整代码如下：

USE `test`;
CREATE TABLE `provinces`(
    `province_id` smallint NOT NULL AUTO_INCREMENT COMMENT '省份id',
    `province_name` varchar(50) NOT NULL COMMENT '省份, 必须唯一',
    PRIMARY KEY(`province_id`)
) ENGINE=InnoDB;

-- 添加唯一索引
ALTER TABLE `provinces`
ADD UNIQUE INDEX `UK_provinceName`(`province_name`);

-- 填充测试数据
INSERT INTO `provinces`(`province_name`)
VALUES('British Columbia'),('Ontario'),('Newfoundland & Labrador'),('New Brunswick')
,('Nova Scotia'),('Saskatchewan'),('Alberta'),('Prince Edward Island')
,('Manitoba'),('Northwest Territories'),('Yukon');

数据准备就绪, 可以查询了:

SELECT * FROM `provinces`

执行结果:

mysql> SELECT `province_id`, `province_name` FROM `provinces`;
+-------------+-------------------------+
| province_id | province_name           |
+-------------+-------------------------+
|           7 | Alberta                 |
|           1 | British Columbia        |
|           9 | Manitoba                |
|           4 | New Brunswick           |
|           3 | Newfoundland & Labrador |
|          10 | Northwest Territories   |
|           5 | Nova Scotia             |
|           2 | Ontario                 |
|           8 | Prince Edward Island    |
|           6 | Saskatchewan            |
|          11 | Yukon                   |
+-------------+-------------------------+
11 rows in set (0.00 sec)

这结果让我很意外，竟然没有按主键排序！而是按照唯一索引排序的！要知道这是InnoDB引擎，不是MyISAM。

MyISAM可能会按数据插入的顺序来显示

难道是在数据库文件中不是按主键顺序存储的吗? 那我们用 notepad++看一下ibd文件:

文件显示, MySQL仍然是按主键顺序存储的.

mysql> EXPLAIN SELECT `province_id`, `province_name` FROM test.provinces;
+----+-------------+-----------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | provinces | NULL       | index | NULL          | UK_provinceName | 152     | NULL |   11 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

通过上面的explain我们发现，该查询选择的索引是唯一索引 UK_provinceName，而这个唯一索引中肯定是包含了主键的。而且，我们这个查询中，直接通过这个唯一索引即可返回所有的数据，不需要再回表。

如果我们把这个表扩展一下, 添加一个字段:

USE `test`;
CREATE TABLE `provinces2`(
    `province_id` smallint NOT NULL AUTO_INCREMENT COMMENT '省份id',
    `province_name` varchar(50) NOT NULL COMMENT '省份, 必须唯一',
    `sequence` int NOT NULL,
    PRIMARY KEY(`province_id`)
) ENGINE=InnoDB;
 
-- 添加唯一索引
ALTER TABLE `provinces2`
ADD UNIQUE INDEX `UK_provinceName`(`province_name`);
 
-- 填充测试数据
INSERT INTO `provinces2`(`province_name`, `sequence`)
VALUES('British Columbia', 1),('Ontario', 2),('Newfoundland & Labrador', 3),('New Brunswick', 4)
,('Nova Scotia', 5),('Saskatchewan', 6),('Alberta', 7),('Prince Edward Island', 8);

我们创建了表provinces2, 新增了字段sequence。现在，我们再来看一下下面的查询：

mysql> SELECT `province_id`, `province_name`, `sequence` FROM test.provinces2;
+-------------+-------------------------+----------+
| province_id | province_name           | sequence |
+-------------+-------------------------+----------+
|           1 | British Columbia        |        1 |
|           2 | Ontario                 |        2 |
|           3 | Newfoundland & Labrador |        3 |
|           4 | New Brunswick           |        4 |
|           5 | Nova Scotia             |        5 |
|           6 | Saskatchewan            |        6 |
|           7 | Alberta                 |        7 |
|           8 | Prince Edward Island    |        8 |
+-------------+-------------------------+----------+
8 rows in set (0.00 sec)

这次的查询结果显示的是按主键排序的.

那我们看一下expalin:

mysql> EXPLAIN SELECT `province_id`, `province_name`, `sequence` FROM test.provinces2;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | provinces2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

这种情况下, type=ALL, 表示直接走的全表扫描. 参考官方文档: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#jointype_all

这时, 我们再换一下SQL, 查询与第一个表一样的两个字段, 也是只需要走唯一索引即可的:

mysql> SELECT `province_id`, `province_name` FROM test.provinces2;
+-------------+-------------------------+
| province_id | province_name           |
+-------------+-------------------------+
|           7 | Alberta                 |
|           1 | British Columbia        |
|           4 | New Brunswick           |
|           3 | Newfoundland & Labrador |
|           5 | Nova Scotia             |
|           2 | Ontario                 |
|           8 | Prince Edward Island    |
|           6 | Saskatchewan            |
+-------------+-------------------------+
8 rows in set (0.00 sec)

mysql> EXPLAIN SELECT `province_id`, `province_name` FROM test.provinces2;
+----+-------------+------------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | provinces2 | NULL       | index | NULL          | UK_provinceName | 152     | NULL |    8 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

到此, 我觉得，MySQL没有什么默认的排序，so, 建议显式声明要排序的字段和方式。