php - MySQL UNION Sub Select 速度慢

Question

我有6张 table

1. 表 a_orders = 41915 行。
2. 表 a_orders_items = 77765 行。
3. 表 a_orders_items_history = 379611 行。
4. 表 b_orders = 743032 行。
5. 表 b_orders_items = 984839 行。
6. 表 b_orders_items_history = 5685609 行。

我想通过过滤订单日期和订单状态来获取订单数据，所以，我有这样的查询:

SELECT orders_id, item_id, qty, status, order_date
FROM (
       (SELECT bo.orders_id AS orders_id,
          boi.orders_items_id AS item_id, boi.order_quantity AS qty,
          boish.status AS status, bo.import_date AS order_date
        FROM b_orders bo
          LEFT JOIN b_orders_items boi ON boi.orders_id = bo.orders_id
          LEFT JOIN b_orders_items_history boih ON boih.orders_items_id = boi.orders_items_id
        WHERE bo.partner_id IN (2,10) AND DATE(boish.status_date) >= DATE('2015-10-27')
              AND DATE(boish.status_date) <= DATE('2016-04-26') AND boish.status = 10
        GROUP BY boish.status_date, boish.status)
       UNION ALL
       (SELECT ao.orders_id AS orders_id, aoi.orders_products_id AS item_id, aoi.products_quantity AS qty,
          opsh.status AS status, ao.date_purchased AS order_date
        FROM a_orders ao
          LEFT JOIN a_orders_items aoi ON aoi.orders_id = ao.orders_id
          LEFT JOIN a_orders_items_history aoih ON aoih.orders_products_id = aoi.orders_products_id
        WHERE DATE(aoih.status_date) >= DATE('2015-10-27') AND DATE(aoih.status_date) <= DATE('2016-04-26')
              AND aoih.status = 10 GROUP BY aoih.status_date, aoih.status)
     ) temp_table GROUP BY item_id, status;

耗时5秒完成，mysql慢查询也记录下来。我能做些什么来让它更快吗？我尝试过使用 UNION，并将其放置在子查询 WHERE 过滤器之外，但结果是相同的。

编辑:我将在这里显示 EXPLAIN 结果:

      id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra

1|PRIMARY|<derived2>|ALL|null|null|null|null|3385|Using temporary; Using filesort
2|DERIVED|bo|ALL|PRIMARY,customer_billing_id,customer_shipping_id|null|null|null|743032|Using where; Using temporary; Using filesort
2|DERIVED|boi|ref|PRIMARY,Index_Orders|Index_Orders|4|bo.bo_orders_id|1|Using where
2|DERIVED|boih|ref|orders_items_id|orders_items_id|5|boi.orders_items_id|1|Using where
3|UNION|aoi|ALL|PRIMARY,orders_id|null|null|null|77765|Using temporary; Using filesort
3|UNION|ao|eq_ref|PRIMARY|PRIMARY|4|aoi.orders_id|1|empty
3|UNION|aoih|ref|orders_products_id|orders_products_id|5|aoi.orders_products_id|1|Using where
null|UNION RESULT|<union2,3>|ALL|null|null|null|null|null|empty

谢谢，感谢任何帮助。

Answer 1

您可以尝试解释您的查询，这将为您提供有关查询的索引使用情况的信息...您可以在 MySQL 文档中找到它。

http://dev.mysql.com/doc/refman/5.7/en/explain.html