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

最新推荐文章于 2024-12-15 19:39:02 发布

转载最新推荐文章于 2024-12-15 19:39:02 发布 · 3.4k 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://juejin.im/post/5cf8ca57f265da1ba9156d9d

文章标签：

#数据库

本文探讨了在MySQL中使用主键查询时遇到的排序问题。通过对比不同查询语句和执行计划，揭示了为何直接查询主键可能不会按照预期排序，以及如何通过强制索引或添加ORDER BY子句来解决此问题。

正文

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

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 |
+----+
复制代码

强制使用主键索引，果然没问题了。

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
复制代码

或者使用order by id引导查询优化器使用主键索引也可以。

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