Mysql 查询主键未指定排序时的默认排序问题

跑批量任务需要分批按顺序把主键取出来，语句如下：

SELECT id FROM foo.bar LIMIT 10 OFFSET 0
+-----+
| id  |
+-----+
| 109 |
| 13  |
| 14  |
| 15  |
| 128 |
| 129 |
| 130 |
| 190 |
| 226 |
| 227 |
+-----+

发现虽然用主键去查，但结果没有按照主键排序。

查询*试试

SELECT * FROM foo.bar LIMIT 10 OFFSET 0
+----+-------+---+
| id | a     | b |
+----+-------+---+
| 1  | 24274 | 0 |
| 2  | 24274 | 0 |
| 3  | 24274 | 0 |
| 4  | 24274 | 0 |
| 5  | 24274 | 0 |
| 6  | 24274 | 0 |
| 7  | 24274 | 0 |
| 8  | 24274 | 0 |
| 9  | 24274 | 0 |
| 10 | 24274 | 0 |
+----+-------+---+

排序按照主键。
查看执行计划，结果如下：

EXPLAIN SELECT * FROM foo.bar LIMIT 10 OFFSET 0 \G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | bar
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 211
filtered      | 100.0
Extra         | <null>

发现select * 没走索引，使用了全表扫描，因此顺序为主键顺序。

EXPLAIN SELECT id FROM foo.bar LIMIT 10 OFFSET 0 \G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | bar
partitions    | <null>
type          | index
possible_keys | <null>
key           | idx_a
key_len       | 8
ref           | <null>
rows          | 211
filtered      | 100.0
Extra         | Using index

而select id并没有用到聚簇索引。innodb二级索引会自动添加主键作为索引列最后一项，使用该索引也能做到覆盖查询。查询优化器使用该索引，导致返回的顺序不符合预期。

SELECT a,id FROM foo.bar LIMIT 10 OFFSET 0
+------+-----+
| a    | id  |
+------+-----+
| 1004 | 109 |
| 1823 | 13  |
| 1823 | 14  |
| 1823 | 15  |
| 1823 | 128 |
| 1823 | 129 |
| 1823 | 130 |
| 1823 | 190 |
| 1823 | 226 |
| 1823 | 227 |
+------+-----+

发现果然之前select id用的是a的索引，并且是按照a,id的顺序排序。
强制使用主键索引试一下

SELECT id FROM foo.bar FORCE INDEX(PRI) LIMIT 10 OFFSET 0
+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
| 6  |
| 7  |
| 8  |
| 9  |
| 10 |
+----+

强制使用主键索引，果然没问题了。
或者使用order by id引导查询优化器使用主键索引也可以：

explain SELECT id FROM boss_business.boss_block_refund_order order by id LIMIT 10 OFFSET 0 \G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | boss_block_refund_order
partitions    | <null>
type          | index
possible_keys | <null>
key           | PRIMARY
key_len       | 8
ref           | <null>
rows          | 10
filtered      | 100.0
Extra         | Using index

另外需要注意，MyISAM引擎表在没有任何的删除、修改操作下，执行select 不带order by，那么会按照插入顺序进行排序。因为使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。总之Mysql的查询优化器一定会倾向于使用最优的方式。

参考链接
MySQL 默认排序真的是按主键来排序的吗
mysql默认的排序方式

原文地址 : https://juejin.cn/post/6844903861086322702