mysql 默认 主键 排序_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 |

type | ALL

possible_keys |

key |

key_len |

ref |

rows | 211

filtered | 100.0

Extra |

复制代码

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

EXPLAIN SELECT id FROM foo.bar LIMIT 10 OFFSET 0 \G

***************************[ 1. row ]***************************

id | 1

select_type | SIMPLE

table | bar

partitions |

type | index

possible_keys |

key | idx_a

key_len | 8

ref |

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 |

type | index

possible_keys |

key | PRIMARY

key_len | 8

ref |

rows | 10

filtered | 100.0

Extra | Using index

复制代码

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

参考链接