为何MariaDB中无LIMIT的ORDER BY与带LIMIT查询结果不一致？

我有一个MariaDB数据库表employees，包含字段employeeNumber（主键）、firstName、lastName和email，表中存储的记录如下：

+----------------+-----------+-----------+---------------------------------+
| employeeNumber | firstName | lastName  | email                           |
+----------------+-----------+-----------+---------------------------------+
|           1002 | Diane     | Murphy    | dmurphy@classicmodelcars.com    |
|           1056 | Mary      | Patterson | mpatterso@classicmodelcars.com  |
|           1076 | Jeff      | Firrelli  | jfirrelli@classicmodelcars.com  |
|           1088 | William   | Patterson | wpatterson@classicmodelcars.com |
|           1102 | Gerard    | Bondur    | gbondur@classicmodelcars.com    |
|           1143 | Anthony   | Bow       | abow@classicmodelcars.com       |
|           1165 | Leslie    | Jennings  | ljennings@classicmodelcars.com  |
|           1166 | Leslie    | Thompson  | lthompson@classicmodelcars.com  |
|           1188 | Julie     | Firrelli  | jfirrelli@classicmodelcars.com  |
|           1216 | Steve     | Patterson | spatterson@classicmodelcars.com |
|           1286 | Foon Yue  | Tseng     | ftseng@classicmodelcars.com     |
|           1323 | George    | Vanauf    | gvanauf@classicmodelcars.com    |
|           1337 | Loui      | Bondur    | lbondur@classicmodelcars.com    |
|           1370 | Gerard    | Hernandez | ghernande@classicmodelcars.com  |
|           1401 | Pamela    | Castillo  | pcastillo@classicmodelcars.com  |
|           1501 | Larry     | Bott      | lbott@classicmodelcars.com      |
|           1504 | Barry     | Jones     | bjones@classicmodelcars.com     |
|           1611 | Andy      | Fixter    | afixter@classicmodelcars.com    |
|           1612 | Peter     | Marsh     | pmarsh@classicmodelcars.com     |
|           1619 | Tom       | King      | tking@classicmodelcars.com      |
|           1621 | Mami      | Nishi     | mnishi@classicmodelcars.com     |
|           1625 | Yoshimi   | Kato      | ykato@classicmodelcars.com      |
|           1702 | Martin    | Gerard    | mgerard@classicmodelcars.com    |
+----------------+-----------+-----------+---------------------------------+
23 rows in set (0.001 sec)

执行以下查询时：

SELECT employeeNumber,lastName,firstName,email FROM employees ORDER BY lastName;

返回的第一条结果是1337 Bondur Loui，但我预期应该是1102 Bondur Gerard。

当添加LIMIT 1后：

SELECT employeeNumber,lastName,firstName,email FROM employees ORDER BY lastName LIMIT 1;

查询结果正确返回Gerard；使用LIMIT 2时，同样能正确返回Gerard排在第一位。

请问为何无LIMIT的ORDER BY查询返回Loui作为第一条，而带LIMIT的查询返回Gerard？

这是因为当ORDER BY指定的字段存在重复值时（此处两条记录的lastName均为Bondur），MariaDB不保证重复值对应记录的排序顺序，除非你在ORDER BY中添加额外的排序字段来打破这种排序平局。

具体差异源于数据库执行计划的不同：

• 不带LIMIT时，MariaDB可能采用全表扫描后排序的执行计划，或者依赖了某个未显式指定的索引顺序，导致重复lastName的记录顺序不符合预期。
• 添加LIMIT时，查询优化器会选择更高效的执行路径（比如利用主键索引快速定位前N条记录），此时重复值的排序会默认遵循主键employeeNumber的升序（因为主键唯一且有序），而1102小于1337，所以Gerard会排在Loui前面。

本质上，SQL标准明确规定：当排序键存在重复时，结果集中重复键对应的记录顺序是未定义的，数据库可根据自身优化策略返回任意顺序。

要确保重复lastName的记录始终按预期顺序排列，需要在ORDER BY中添加额外的排序字段，比如主键employeeNumber或firstName：

-- 按lastName升序，再按employeeNumber升序（推荐，主键唯一保证排序稳定）
SELECT employeeNumber,lastName,firstName,email FROM employees ORDER BY lastName, employeeNumber;

-- 或者按lastName升序，再按firstName升序
SELECT employeeNumber,lastName,firstName,email FROM employees ORDER BY lastName, firstName;

添加额外排序字段后，无论是否使用LIMIT，排序结果都会稳定，Gerard会始终排在Loui前面。

内容的提问来源于stack exchange，提问作者Jordy Deweer