sql - 为什么我的查询这么慢？试图在 mysql 的左连接上找到空字段

Question

编辑:更新了下面 Bill Karwin 的建议。还是很慢。

我正在尝试编写一个查询，以查找输入到仓库的订单上的所有商品，而该仓​​库中没有该商品的记录。例如，如果为仓库 A 输入了商品 XYZ，但仓库 A 实际上没有商品 XYZ，我希望订单商品显示在我的报告中。

我能够很好地运行查询，但它似乎需要很长时间(50 秒)。它似乎主要卡在 where 子句中的“is null”条件上。如果我删除带有“is null”的条件并运行它，它会在大约 4.8 秒内执行。这是我的查询:

SELECT
saw_order.Wo,
saw_orderitem.Item,
saw_orderitem.Stock,
saw_order.`Status`,
saw_order.`Date`,
saw_orderitem.Warehouse,
saw_stockbalance.Balno,
saw_stockbalance.Stock
FROM
saw_order
Inner Join saw_orderitem ON saw_order.Wo = saw_orderitem.Wo
Inner Join saw_stock ON saw_orderitem.Stock = saw_stock.Stock
Left Join saw_stockbalance ON saw_orderitem.Stock = saw_stockbalance.Stock 
    AND saw_orderitem.Warehouse = saw_stockbalance.Warehouse
WHERE
saw_order.`Status` Between 3 and 81 and
saw_stockbalance.Stock Is Null

当我解释上面的查询时，我看到:

+----+-------------+------------------+--------+------------------------------+---------+---------+-------------------------------------------------------+-------+-------------------------+
| id | select_type | table            | type   | possible_keys                | key     | key_len | ref                                                   | rows  | Extra                   |
+----+-------------+------------------+--------+------------------------------+---------+---------+-------------------------------------------------------+-------+-------------------------+
|  1 | SIMPLE      | saw_stock        | index  | PRIMARY                      | PRIMARY | 17      | NULL                                                  | 32793 | Using index             |
|  1 | SIMPLE      | saw_orderitem    | ref    | PRIMARY,Stock,StockWarehouse | Stock   | 17      | saws.saw_stock.Stock                                  |    68 |                         |
|  1 | SIMPLE      | saw_order        | eq_ref | PRIMARY,Status               | PRIMARY | 4       | saws.saw_orderitem.Wo                                 |     1 | Using where             |
|  1 | SIMPLE      | saw_stockbalance | ref    | Stock,Warehouse              | Stock   | 20      | saws.saw_orderitem.Stock,saws.saw_orderitem.Warehouse |     1 | Using where; Not exists |
+----+-------------+------------------+--------+------------------------------+---------+---------+-------------------------------------------------------+-------+-------------------------+

我很确定我的连接中各个表的所有字段都有索引，但无法弄清楚如何重写查询以使其运行得更快。

编辑:这是我在我的表上设置的索引:

mysql> show index from saw_order;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| saw_order |          0 | PRIMARY  |            1 | Wo          | A         |      553425 | NULL     | NULL   |      | BTREE      |         |
| saw_order |          1 | Customer |            1 | Customer    | A         |       14957 | NULL     | NULL   |      | BTREE      |         |
| saw_order |          1 | Other    |            1 | Other       | A         |         218 | NULL     | NULL   |      | BTREE      |         |
| saw_order |          1 | Site     |            1 | Site        | A         |           8 | NULL     | NULL   |      | BTREE      |         |
| saw_order |          1 | Date     |            1 | Date        | A         |        1594 | NULL     | NULL   |      | BTREE      |         |
| saw_order |          1 | Status   |            1 | Status      | A         |          15 | NULL     | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set

mysql> show index from saw_orderitem;
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| saw_orderitem |          0 | PRIMARY        |            1 | Wo          | A         | NULL        | NULL     | NULL   |      | BTREE      |         |
| saw_orderitem |          0 | PRIMARY        |            2 | Item        | A         |     1842359 | NULL     | NULL   |      | BTREE      |         |
| saw_orderitem |          1 | Stock          |            1 | Stock       | A         |       27093 | NULL     | NULL   |      | BTREE      |         |
| saw_orderitem |          1 | Product        |            1 | Product     | A         |         803 | NULL     | NULL   |      | BTREE      |         |
| saw_orderitem |          1 | GGroup         |            1 | GGroup      | A         |         114 | NULL     | NULL   |      | BTREE      |         |
| saw_orderitem |          1 | ShipVia        |            1 | ShipVia     | A         |         218 | NULL     | NULL   |      | BTREE      |         |
| saw_orderitem |          1 | Warehouse      |            1 | Warehouse   | A         |           9 | NULL     | NULL   |      | BTREE      |         |
| saw_orderitem |          1 | StockWarehouse |            1 | Stock       | A         |       27093 | NULL     | NULL   |      | BTREE      |         |
| saw_orderitem |          1 | StockWarehouse |            2 | Warehouse   | A         |       49793 | NULL     | NULL   |      | BTREE      |         |
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
9 rows in set

mysql> show index from saw_stock;
+-----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name          | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| saw_stock |          0 | PRIMARY           |            1 | Stock             | A         |       32793 | NULL     | NULL   |      | BTREE      |         |
| saw_stock |          1 | Class             |            1 | Class             | A         |         655 | NULL     | NULL   | YES  | BTREE      |         |
| saw_stock |          1 | DateFirstReceived |            1 | DateFirstReceived | A         |        2732 | NULL     | NULL   |      | BTREE      |         |
+-----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set

mysql> show index from saw_stockbalance;
+------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| saw_stockbalance |          0 | PRIMARY   |            1 | Balno       | A         |      146315 | NULL     | NULL   |      | BTREE      |         |
| saw_stockbalance |          1 | Stock     |            1 | Stock       | A         |       36578 | NULL     | NULL   |      | BTREE      |         |
| saw_stockbalance |          1 | Stock     |            2 | Warehouse   | A         |      146315 | NULL     | NULL   |      | BTREE      |         |
| saw_stockbalance |          1 | Warehouse |            1 | Warehouse   | A         |          11 | NULL     | NULL   |      | BTREE      |         |
+------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set

有什么想法吗？

Answer 1

我会尝试让它使用 covering index .也就是说，而不是测试是否 Balno为空，测试你的左外连接条件中的一列是否为空。例如。 Stock或 Warehouse .

您还应该在两个表 Stock 的两列( Warehouse 、 saw_orderitem )上定义索引和 saw_stockbalance .