为什么我的查询这么慢？(Why is my query so slow? Trying to find the null fields on a left join in mysql)
编辑：更新了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当我explain上面的查询时，我看到：
+----+-------------+------------------+--------+------------------------------+---------+---------+-------------------------------------------------------+-------+-------------------------+ | 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有任何想法吗？
EDIT: Updated with suggestions from Bill Karwin below. Still very slow.
I'm trying to write a query that will find all items on an order that are entered to a warehouse that doesn't have a record for that item in that warehouse. As an example, if item XYZ is entered for warehouse A, but warehouse A doesn't actually carry item XYZ, I want the order item to show up in my report.
I'm able to run the query just fine, but it seems to take forever (50 seconds). It seems to be hanging mainly on the "is null" condition I have in the where clause. If I remove the condition with the "is null" and run it, it executes in about 4.8s. Here's my query:
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 NullWhen I explain the query above, I see:
+----+-------------+------------------+--------+------------------------------+---------+---------+-------------------------------------------------------+-------+-------------------------+ | 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 | +----+-------------+------------------+--------+------------------------------+---------+---------+-------------------------------------------------------+-------+-------------------------+I'm pretty sure I have indexes for all of the fields of the respective tables in my joins, but can't figure out how to rewrite the query to make it go faster.
EDIT: Here are the indexes I have set up on my tables:
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 setAny ideas?
最满意答案
我试着让它使用覆盖索引 。 也就是说，不测试Balno是否为null，而是测试左外连接条件中的一列是否为空。 例如Stock或Warehouse 。
您还应该在两个表saw_orderitem和saw_stockbalance的两列（ Stock ， Warehouse ）上定义索引。
I'd try to make it use a covering index. That is, instead of testing if Balno is null, test if one of the columns in your left outer join conditions is null. E.g. Stock or Warehouse.
You should also define an index over the two columns (Stock, Warehouse) in both tables saw_orderitem and saw_stockbalance.
更多推荐
发布评论