这是给你的一个简洁的（显然是 MySQL）：
# Setting things up
DROP DATABASE IF EXISTS index_test_gutza;
CREATE DATABASE index_test_gutza;
USE index_test_gutza;
CREATE TABLE customer_order (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
invoice MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(id)
);
INSERT INTO customer_order
(id, invoice)
VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);
CREATE TABLE customer_invoice (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
invoice_no MEDIUMINT UNSIGNED DEFAULT NULL,
invoice_pdf LONGBLOB,
PRIMARY KEY(id)
);
INSERT INTO customer_invoice
(id, invoice_no)
VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);
# Ok, here's the beef
EXPLAIN
SELECT co.id
FROM customer_order AS co;
EXPLAIN
SELECT co.id
FROM customer_order AS co
ORDER BY co.id;
EXPLAIN
SELECT co.id, ci.invoice_no
FROM customer_order AS co
LEFT JOIN customer_invoice AS ci ON ci.id=co.invoice;
EXPLAIN
SELECT co.id, ci.invoice_no
FROM customer_order AS co
LEFT JOIN customer_invoice AS ci ON ci.id=co.invoice
ORDER BY co.id;
底部有四个 EXPLAIN 语句。前两个结果正是您所期望的：
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | co | index | NULL | PRIMARY | 3 | NULL | 5 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
第三个已经很有趣了——注意 customer_order 中的主键如何不再被使用：
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| 1 | SIMPLE | co | ALL | NULL | NULL | NULL | NULL | 5 | |
| 1 | SIMPLE | ci | eq_ref | PRIMARY | PRIMARY | 3 | index_test_gutza.co.invoice | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
然而，第四个是最有趣的——只需添加 ORDER BY在主键上导致对 customer_order 进行文件排序（这是预料之中的，因为上面已经令人困惑了）：
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+----------------+
| 1 | SIMPLE | co | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
| 1 | SIMPLE | ci | eq_ref | PRIMARY | PRIMARY | 3 | index_test_gutza.co.invoice | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+----------------+
文件排序！虽然我从来不使用除了 customer_order 表中的主键进行排序和 customer_invoice 表中的 JOIN 主键之外的任何内容。那么，以一切美好和正确的名义，为什么突然切换到文件排序？！更重要的是，我该如何避免这种情况？作为记录，我很乐意接受一个记录的答案，解释为什么会这样cannot避免（如果是这样的话。）
正如您现在可能怀疑的那样，这实际上发生在生产中，尽管表并不大（只有数百条记录），但当我运行时，发票表（包含 PDF 文件）上的文件排序正在杀死服务器类似于上面的查询（我需要这些查询才能知道哪些订单已开具发票，哪些没有）。
在你问之前，我设计了数据库，并且我认为我可以安全地将 PDF 文件存储在该表中，因为我永远不能需要对其进行任何搜索查询——我手边总是有它的主键！
更新（评论概要）
以下是下面评论中建议内容的概要，因此您不必阅读所有内容：
- *您应该在 customer_order.invoice 上添加一个密钥* - 我实际上在生产中尝试过，它没有什么区别（因为它不应该）
-
你应该使用
USE INDEX——试过了，没用。我也尝试过FORCE INDEX-- 也没有结果（没有任何改变）
-
您过于简化了用例，我们需要实际的生产查询-- 我可能在第一次迭代中剥离了太多，所以我更新了它（我刚刚添加了
, ci.invoice_no in the SELECT对于最后几个查询）。作为记录，如果有人真的很好奇，这里是生产查询，完全按照原样（这会检索订单的最后一页）：
SELECT
corder.id,
corder.public_id,
CONCAT(buyer.fname," ",buyer.lname) AS buyer_name,
corder.status,
corder.payment,
corder.reserved AS R,
corder.tracking_id!="" as A,
corder.payment_received as pay_date,
invoice.invoice_no AS inv,
invoice.receipt_no AS rec,
invoice.public AS pub_inv,
proforma.proforma_no AS prof,
proforma.public AS pub_pf,
corder.rating,
corder.rating_comments!="" AS got_comment
FROM
corder
LEFT JOIN user as buyer ON buyer.id=corder.buyer
LEFT JOIN invoice as invoice ON invoice.id=corder.invoice
LEFT JOIN invoice as proforma ON proforma.id=corder.proforma
ORDER BY
id DESC
LIMIT 400, 20;
上面的查询（同样，这正是我在生产中运行的查询）大约需要 14 秒才能运行。这是在生产中执行的简化查询，如上面的用例所示：
SELECT
corder.id,
invoice.invoice_no
FROM
corder
LEFT JOIN invoice ON invoice.id=corder.invoice
ORDER BY
corder.id DESC
LIMIT 400, 20;
这个运行需要 13 秒。请注意，LIMIT 没有任何区别只要我们谈论最后一页结果（我们就是）。也就是说，当涉及文件排序时，检索最后 12 个结果或所有 412 个结果之间绝对没有显着差异。
结论
ypercube 的答案不仅是正确的，而且不幸的是它似乎是唯一合法的答案。我尝试进一步将条件与字段分开，因为SELECT * FROM corder如果 corder 本身包含 LONGBLOB（并且在子查询中复制主查询中的字段是不优雅的），则子查询最终可能会涉及大量数据，但不幸的是它似乎不起作用：
SELECT
corder.id,
corder.public_id,
CONCAT(buyer.fname," ",buyer.lname) AS buyer_name,
corder.status,
corder.payment,
corder.reserved AS R,
corder.tracking_id != "" AS A,
corder.payment_received AS pay_date,
invoice.invoice_no AS inv,
invoice.receipt_no AS rec,
invoice.public AS pub_inv,
proforma.proforma_no AS prof,
proforma.public AS pub_pf,
corder.rating,
corder.rating_comments!="" AS got_comment
FROM
corder
LEFT JOIN user as buyer ON buyer.id = corder.buyer
LEFT JOIN invoice AS invoice ON invoice.id = corder.invoice
LEFT JOIN invoice AS proforma ON proforma.id = corder.proforma
WHERE corder.id IN (
SELECT id
FROM corder
ORDER BY id DESC
LIMIT 400,20
)
ORDER BY
corder.id DESC;
此操作失败，并显示以下错误消息：
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
我使用的是 MySQL 5.1.61，它是 5.1 系列中最新的版本（显然 5.5.x 也不支持它）。
