mysql - 仅在 PK 选择期间，MySQL 是否默认对 PK 字段进行排序？

Question

我正在尝试使用连接从两个大表中进行选择:

EXPLAIN SELECT SQL_NO_CACHE  
  e.*
FROM `table_A` e 
  JOIN 
    (SELECT id FROM `table_B` /* FORCE index (primary, index_A) */
    WHERE id > 338107  AND `index_field_A` = 900000000 AND `index_field_B` = 1 
 ORDER BY id) AS c 
        ON `c`.`id` = `e`.`fk_id` 
    WHERE e.`some_field` IS NULL; 
    LIMIT 2000;

    /*  BEST EXPLAIN RESULT
    USING intersect(index_A,index_B); USING WHERE; USING INDEX
    */

我将当前 ID 存储在应用程序端 (338107)，以便能够通过批处理(从起始 ID 到最大 ID)获得总结果如果我摆脱 ORDER BY id 没有问题但是我不确定默认情况下 MySQL order PK。如果我在没有 JOIN 的情况下使用单独的 SELECT 也没有问题:

SELECT id FROM `table_B` WHERE id > 338107  
AND `index_field_A` = 900000000 AND `index_field_B` = 1 ORDER BY id

但是没用

如果我强制索引，我可以获得更好的 EXPLAIN :

FORCE index (primary, index_A)

但这还差得远。

我可以摆脱 ORDER BY id 而不会产生负面后果吗？

补充:排序PK字段是自增的，InnoDB表

Answer 1

像这样的东西怎么样？

SELECT *
FROM `table_A`
WHERE `some_field` IS NULL
   AND `fk_id` IN (
        SELECT `id` 
        FROM `table_B`
        WHERE id > 338107 AND `index_field_A` = 900000000 AND `index_field_B` = 1 
      )
ORDER BY `fk_id`
;

或

SELECT e.*
FROM `table_B` AS c 
   LEFT JOIN `table_A` AS e 
      ON c.`id` = e.`fk_id` 
      AND e.`someField` IS NULL
WHERE c.id > 338107 
   AND c.`index_field_A` = 900000000 
   AND c.`index_field_B` = 1
HAVING e.fk_id IS NOT NULL
ORDER BY e.`fk_id`
;