mysql - MySQL为什么不对JOIN加ORDER使用主键？

Question

这是为您准备的一个整洁的（显然是MySQL）：

＃设置
如果存在则删除数据库index_test_gutza;
创建数据库index_test_gutza;
USE index_test_gutza;

创建表customer_order（
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT，
发票MEDIUMINT UNSIGNED NOT NULL DEFAULT 0，
主键（id）
）;
插入客户订单
（编号，发票）
价值
（1，1），
（2，2），
（3，3），
（4，4），
（5，5）;

创建表customer_invoice（
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT，
invoice_no MEDIUMINT UNSIGNED DEFAULT NULL，
invoice_pdf LONGBLOB，
主键（id）
）;
插入到customer_invoice
（编号，发票编号）
价值
（1，1），
（2，2），
（3，3），
（4，4），
（5，5）;

＃好，这是牛肉
说明
SELECT co.id
FROM customer_order AS co;

说明
SELECT co.id
FROM customer_order AS co
OR BY BY co.id;

说明
SELECT co.id，ci.invoice_no
FROM customer_order AS co
左加入customer_invoice AS ci ON ci.id = co.invoice;

说明
SELECT co.id，ci.invoice_no
FROM customer_order AS co
左加入customer_invoice AS ci ON ci.id = co.invoice
OR BY BY co.id;


底部有四个EXPLAIN语句。前两个结果完全符合您的期望：

+ ---- + ------------- + ------- + ------- + -------------- -+ --------- + --------- + ------ + ------ + ------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + ------------- + ------- + ------- + -------------- -+ --------- + --------- + ------ + ------ + ------------- +
| 1 |简单合作|索引| NULL |主要| 3 | NULL | 5 |使用索引
+ ---- + ------------- + ------- + ------- + -------------- -+ --------- + --------- + ------ + ------ + ------------- +


第三个已经很有趣了-请注意如何不再使用customer_order中的主键：

+ ---- + ------------- + ------- + -------- + ------------- -+ --------- + --------- + --------------------------- -+ ------ + ------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + ------------- + ------- + -------- + ------------- -+ --------- + --------- + --------------------------- -+ ------ + ------------- +
| 1 |简单合作|全部| NULL | NULL | NULL | NULL | 5 | |
| 1 |简单ci | eq_ref |主要|主要| 3 | index_test_gutza.co.invoice | 1 |使用索引
+ ---- + ------------- + ------- + -------- + ------------- -+ --------- + --------- + --------------------------- -+ ------ + ------------- +


但是，第四个是zinger －仅在主键上添加ORDER BY会导致对customer_order进行文件排序（这是可以预料的，因为上面已经很困惑了）：

+ ---- + ------------- + ------- + -------- + ------------- -+ --------- + --------- + --------------------------- -+ ------ + ---------------- +
| id | select_type |桌子|类型可能的钥匙|关键key_len |参考|行|额外|
+ ---- + ------------- + ------- + -------- + ------------- -+ --------- + --------- + --------------------------- -+ ------ + ---------------- +
| 1 |简单合作|全部| NULL | NULL | NULL | NULL | 5 |使用文件排序|
| 1 |简单ci | eq_ref |主要|主要| 3 | index_test_gutza.co.invoice | 1 |使用索引
+ ---- + ------------- + ------- + -------- + ------------- -+ --------- + --------- + --------------------------- -+ ------ + ---------------- +


Filesort！而且，尽管除了customer_order表中的主键和JOIN的customer_invoice表中的主键外，我什么都没用。那么，以所有正确和正确的名义，为什么它突然转换为文件排序？！更重要的是，如何避免这种情况？记录下来，我很乐意接受记录在案的答案，解释为什么无法避免（如果是这种情况）。

您可能现在已经怀疑，这实际上是在生产中发生的，尽管这些表绝对不是很大（只有数百条记录），但发票表（包含PDF文件）上的文件排序在我运行时会杀死服务器与上面的查询类似的查询（我需要知道哪些订单已发出发票，哪些没有。）

在您询问之前，我已经设计了数据库，并且我认为我可以安全地将PDF文件存储在该表中，因为我从不需要它的任何搜索查询-我总是可以随时使用它的主键！

更新（评论简介）

这是下面评论中所建议内容的提要，因此您不必阅读所有内容：


*您应该在customer_order.invoice *上添加一个密钥-我实际上在生产中尝试过，它没有什么区别（因为它不应该这样）
您应该使用USE INDEX-尝试过，没有用。我也尝试了FORCE INDEX-也没有结果（无任何变化）
您过分简化了用例，我们需要实际的生产查询-我可能在第一次迭代中就把它剥离了太多，所以我进行了更新（我在最后两个查询中只是在, ci.invoice_no中添加了SELECT ）。作为记录，如果有人真的很好奇，这是生产查询，完全一样（这将检索订单的最后一页）：


选择
corder.id，
corder.public_id，
CONCAT（buyer.fname，“”，buyer.lname）AS买方名称，
corder.status，
付款。
corder.reserved AS R，
corder.tracking_id！=“”作为A，
corder.payment_received作为pay_date，
invoice.invoice_no AS inv，
invoice.receipt_no AS rec，
invoice.public AS pub_inv，
proforma.proforma_no AS教授，
proforma.public AS pub_pf，
评分，
corder.rating_comments！=“” AS got_comment
从
科尔德
LEFT JOIN用户作为买方ON Buyer.id = corder.buyer
左联接发票作为invoice.id = corder.invoice上的发票
左加入发票作为proforma.id = corder.proforma上的形式
订购
ID DESC
LIMIT 400，20;


上面的查询（同样正是我在生产环境中运行的查询）大约需要14秒钟才能运行。如上面用例所示，这是在生产环境中执行的简化查询：

选择
corder.id，
invoice.invoice_no
从
科尔德
在invoice.id = corder.invoice上左加入发票
订购
编码器
LIMIT 400，20;


这需要13秒钟才能运行。请注意，只要我们谈论结果的最后一页（即我们），LIMIT就没有任何区别。也就是说，当涉及文件排序时，检索最后12个结果或所有412个结果之间绝对没有显着差异。

结论

ypercube的答案不仅是正确的，而且不幸的是，它似乎是唯一合理的答案。我试图进一步将条件与字段分开，因为SELECT * FROM corder子查询最终可能会涉及大量数据，如果corder本身包含LONGBLOB（并且在子查询中从主查询中复制字段很不方便），但不幸的是，它并没有做到这一点似乎可行：

选择
corder.id，
corder.public_id，
CONCAT（buyer.fname，“”，buyer.lname）AS买方名称，
corder.status，
付款。
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教授，
proforma.public AS pub_pf，
评分，
corder.rating_comments！=“” AS got_comment
从
科尔德
LEFT JOIN用户作为买方ON Buyer.id = corder.buyer
左联接发票为invoice.id = corder.invoice上的发票
在proforma.id = corder.proforma上以左形式加入AS形式的发票
在哪里corder.id输入（
选择ID
从科德
按ID DESC排序
限制400,20
）
订购
corder.id DESC；


这将失败，并显示以下错误消息：

错误1235（42000）：此版本的MySQL尚不支持“ LIMIT＆IN / ALL / ANY / SOME子查询”


我使用的是MySQL 5.1.61，它在5.1家族中是相当新的（显然5.5.x也不支持）。

Answer 1

您可以尝试这个版本吗（它基本上首先获取corder表的420行，保留其中的20行，然后进行3个外部联接）：

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
    ( SELECT * 
      FROM corder
      ORDER BY
        id DESC 
      LIMIT 400, 20
    )
    AS 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
    corder.id DESC ;