我正在尝试使用JOIN从两个大型表中进行选择:
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
*/
i将当前ID存储在应用程序端(338107),以便通过批处理(从开始ID到最大ID)获得总结果如果我被ID 删除订单,那没有问题,但是我不确定MySQL订购PK默认情况下。如果我使用单独选择而没有加入,也没有问题:
SELECT id FROM `table_B` WHERE id > 338107
AND `index_field_A` = 900000000 AND `index_field_B` = 1 ORDER BY id
但这是无用的
如果我强迫索引,我可以得到更好的解释:
FORCE index (primary, index_A)
但是离好太远。
我可以通过ID订购而不会否定后果吗?
加法:订购PK字段是自动启动,Innodb表
这样的东西呢?
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`
;