如何优化联接3表的SQL查询 [英] How to optimize the SQL query that joins 3 table
问题描述
你好吗
我有以下SQL查询，大约需要4秒钟才能运行:
I have the following SQL query which takes about 4 seconds to run:
select
o.id, tu.status_type, m.upload_date
from
(select order_id, max(last_updated) as maxudate from tracking_update group by order_id) t
inner join
tracking_update tu on t.order_id=tu.order_id and t.maxudate=tu.last_updated
right join
fgw247.order o on t.order_id=o.id
left join
manifest m on o.manifest_id=m.id
where
(m.upload_date >= '2015-12-12 00:00:00') or (m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD')
查询将联接以下3个表:
The query joins the following 3 tables:
订单，清单和Tracking_Update.
Order, Manifest and Tracking_Update.
查询将返回符合以下条件的订单:
The query will return orders that meet the following criteria:
- 订单< 30天(任何交货状态)
- 订单> 30天而不是交货状态
如果该订单的最新tracking_update的状态类型为"D"或"XD"，则该订单被视为已交付
An order is considered delivered if the latest tracking_update for that order has a status_type of 'D' or 'XD'
现在，订单在订单"表中列出.订单表有一个名为manifest_id的列，该列引用了上载订单时创建的清单.
Now, the orders are listed in the Order table. The Order table has a column called manifest_id which references the Manifest that was created when the order was uploaded.
一个清单可以有多个订单.这是用来确定过去30天内是否已上传订单的信息.
A manifest can have multiple orders. This is what is used to determine if an order has been uploaded in the past 30 days.
最后，tracking_update表包含每个订单的tracking_updates.一个订单可以有多个tracking_updates.
Finally, the tracking_update table contains the tracking_updates per order. An order can have multiple tracking_updates.
当前，tracking_update表的记录长度超过1M.
Currently, the tracking_update table is over 1M records long.
下面列出了每个表的create语句:
Listed below are the create statements for each table:
CREATE TABLE "order" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"ShipmentId" varchar(50) DEFAULT NULL,
"RecipientName" varchar(160) DEFAULT NULL,
"CompanyName" varchar(160) DEFAULT NULL,
"Address1" varchar(160) DEFAULT NULL,
"Address2" varchar(160) DEFAULT NULL,
"City" varchar(50) DEFAULT NULL,
"State" varchar(3) DEFAULT NULL,
"ZIP" int(11) DEFAULT NULL,
"TEL" int(11) DEFAULT NULL,
"Email" varchar(255) DEFAULT NULL,
"Bottles" int(11) DEFAULT NULL,
"Weight" float DEFAULT NULL,
"Resi" tinyint(1) DEFAULT NULL,
"Wave" int(11) DEFAULT NULL,
"url_slug" varchar(50) DEFAULT NULL,
"manifest_id" int(11) DEFAULT NULL,
"shipment_date" datetime DEFAULT NULL,
"tracking_number" varchar(30) DEFAULT NULL,
"shipping_carrier" varchar(10) DEFAULT NULL,
"last_tracking_update" datetime DEFAULT NULL,
"delivery_date" datetime DEFAULT NULL,
"customer_code" varchar(50) DEFAULT NULL,
"sub_cust_code" int(11) DEFAULT NULL,
PRIMARY KEY ("id"),
UNIQUE KEY "ShipmentID" ("ShipmentId"),
KEY "manifest_id" ("manifest_id"),
KEY "order_idx3" ("tracking_number"),
KEY "order_idx4" ("customer_code"),
CONSTRAINT "order_ibfk_1" FOREIGN KEY ("manifest_id") REFERENCES "manifest" ("id")
);
Tracking_Update表:
Tracking_Update table:
CREATE TABLE "tracking_update" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"order_id" int(11) DEFAULT NULL,
"ship_update_date" datetime DEFAULT NULL,
"message" varchar(400) DEFAULT NULL,
"location" varchar(100) DEFAULT NULL,
"status_type" varchar(2) DEFAULT NULL,
"last_updated" datetime DEFAULT NULL,
"hash" varchar(32) DEFAULT NULL,
PRIMARY KEY ("id"),
KEY "order_id" ("order_id"),
KEY "tracking_update_idx2" ("status_type"),
KEY "tracking_update_idx3" ("ship_update_date"),
CONSTRAINT "tracking_update_ibfk_1" FOREIGN KEY ("order_id") REFERENCES "order" ("id")
);
清单表:
CREATE TABLE "manifest" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"upload_date" datetime DEFAULT NULL,
"name" varchar(100) DEFAULT NULL,
"destination_gateway" varchar(40) DEFAULT NULL,
"arrived" tinyint(1) DEFAULT NULL,
"customer_code" varchar(50) DEFAULT NULL,
"upload_user" varchar(50) DEFAULT NULL,
"trip_id" int(11) DEFAULT NULL,
PRIMARY KEY ("id")
);
这也是通过JSON导出的select语句上的EXPLAIN:
Here is also the EXPLAIN on the select statement exported with JSON:
{
"data":
[
{
"id": 1,
"select_type": "PRIMARY",
"table": "m",
"type": "ALL",
"possible_keys": "PRIMARY",
"key": null,
"key_len": null,
"ref": null,
"rows": 220,
"Extra": "Using where"
},
{
"id": 1,
"select_type": "PRIMARY",
"table": "o",
"type": "ref",
"possible_keys": "manifest_id",
"key": "manifest_id",
"key_len": "5",
"ref": "fgw247.m.id",
"rows": 246,
"Extra": "Using index"
},
{
"id": 1,
"select_type": "PRIMARY",
"table": "tu",
"type": "ref",
"possible_keys": "order_id",
"key": "order_id",
"key_len": "5",
"ref": "fgw247.o.id",
"rows": 7,
"Extra": "Using where"
},
{
"id": 1,
"select_type": "PRIMARY",
"table": "<derived2>",
"type": "ref",
"possible_keys": "<auto_key0>",
"key": "<auto_key0>",
"key_len": "11",
"ref": "fgw247.o.id,fgw247.tu.last_updated",
"rows": 13,
"Extra": "Using index"
},
{
"id": 2,
"select_type": "DERIVED",
"table": "tracking_update",
"type": "index",
"possible_keys": "order_id",
"key": "order_id",
"key_len": "5",
"ref": null,
"rows": 1388275,
"Extra": null
}
]
}
感谢您的帮助
UPDATE
UPDATE
这是我正在使用的当前查询，速度更快:
This is the current query that I'm using which is MUCH faster:
SELECT
o.*, tu.*
FROM
fgw247.`order` o
JOIN
manifest m
ON
o.`manifest_id` = m.`id`
JOIN
`tracking_update` tu
ON
tu.`order_id` = o.`id` and tu.`ship_update_date` = (select max(last_updated) as last_updated from tracking_update where order_id = o.`id` group by order_id)
WHERE
m.`upload_date` >= '2015-12-14 11:50:12'
OR
(o.`delivery_date` IS NULL AND m.`upload_date` < '2015-12-14 11:50:12')
LIMIT 100
推荐答案
考虑以下选项以优化查询的执行:
Consdider the the following options to optimise the execution of your query:
-
联接类型:您确定需要左右联接吗?您是否要报告甚至不包含在清单中的订单?如果不是，则使用内部联接而不是左右联接.
Join types: are you sure that you need the left and right joins? Do you want to report on orders that are not even included in a manifest? If not, then use inner join instead of left and right.
其他索引:manifest.upload_date字段上没有索引，您应该添加一个.我还将在tracking_update表的order_id，update_date和status_type字段上(按此顺序！)创建一个复合索引.
Additional indexes: There is no index on manifest.upload_date field, you should add one. I would also create a composite index on order_id, update_date, and status_type fields (in this order!) on tracking_update table.
在其中使用union代替or准则:传统上，mysql并不擅长优化where子句中的or准则.因此，将(m.upload_date >= '2015-12-12 00:00:00') or (m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD')变成具有2个查询的并集.直到where部分，这2个查询将与现有查询相同.第一个查询在where子句中仅具有(m.upload_date >= '2015-12-12 00:00:00')条件，而第二个查询将具有(m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD')条件.
Use union instead of the or criterion in where: Mysql is traditionally not really good at optimising or criterion in where clauses. So turn the (m.upload_date >= '2015-12-12 00:00:00') or (m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD') into a union with 2 queries. The 2 queries will be the same as the existing query up to the where part. The 1st query will have only the (m.upload_date >= '2015-12-12 00:00:00') condition in the where clause, while the 2nd one will have the (m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD') criteria.
如果添加新索引，则请通过运行新的解释来检查查询是否使用了它们.
If you add new indexes, then pls check if the query uses them by running a new explain.
这篇关于如何优化联接3表的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
