mysql查询优化，加上limit 关键字速度快了很多

Question

一个多表关联的sql，能够返回140条数据，不加 limit 耗时10秒钟，加上limit 100仅
耗时 0.5秒，sql有Order by,如果有排序不应该查出全部数据后才执行limit吗，求教大神原理。sql如下
SELECT
sale.id,
sale.sale_no,
sale.express_no,
sale.thermometer_return,
sale.num,
sale.weight,
r.id AS "r.receiver_id",
r.receive_company,
r.receive_address,
r.receive_mobile,
r.receive_name
FROM
(
SELECT
sdr.sale_id
FROM
dis_distribution_warehouse_relation dwr,
dis_sale_distribution_relation sdr
WHERE
sdr.distribution_id = dwr.distribution_id
AND dwr.warehouse_id = '021aa21cc9204bd7bcb9d1ad0d271141'
AND dwr.status = 1
AND sdr.sale_id NOT IN (
SELECT
sdr1.sale_id
FROM
dis_distribution_warehouse_relation dwr1
JOIN dis_sale_distribution_relation sdr1 ON sdr1.distribution_id = dwr1.distribution_id
WHERE
dwr1.status = 0
AND dwr1.warehouse_id = '021aa21cc9204bd7bcb9d1ad0d271141'
)
) m STRAIGHT_JOIN order_sale sale ON m.sale_id = sale.id
AND sale.status = '1'
AND sale.carrier_id = '23b40f744e954567ac2ab6caa79ef8e8'
LEFT JOIN order_receiver r ON r.id = sale.receiver_id
ORDER BY
sale.create_date DESC
LIMIT 100

Answer 1

使用limit时，当扫描到足够的行数时，就停止进行扫描了。例如limit m,n，在有足够的数据行能够被扫描的情况下，那么会先扫描m行被丢弃，然后再扫描n行作为结果，一共扫描m+n行。所以当你的m很大时，一样慢。