Mysql 3表2连接

Question

目标是更新 order_items 表，然后更新 transportcost 列。

Table test.inserttest2
- Rows: 480 000
 - Order table
 - Column: Ordernumber and Country


Table test.inserttest3
- Rows: 2 600 000
 - Order items
 - Column: Ordernumber, SKU and Transportcost

Table transportcost
- Rows: 17 000
- a global list of transport price
- Column: SKU, Country, Transportprice

在做出更新语句之前，我会执行一个选择语句来检查是否获得了正确的字段: - 64秒 - 行数:62 000

SELECT
  a.ordernumber,
  b.country,
  b.transportprice
FROM test.inserttest3 a
INNER JOIN transportcost AS b
  ON a.sku = b.sku
WHERE a.system_created > '2017-12-01'

我得到了这样的列表:

17004706    Germany 3.00
17004706    France  6.00
17004706    Netherlands 3.00
17004706    Belgium 3.00
17004706    Austria 4.00
17004706    Italy   5.00
17004706    Switzerland 1.00
17004706    UK  8.00
17004706    Spain   4.00
17004706    Poland  4.00
17004706    Norway  9.00
17004706    Denmark 5.00
17004706    Sweden  6.00
17004706    Czech   4.00
17004706    Finland 6.00
17004706    Ireland 3.00

下一步是将此信息用于 order_items 表。这里出了问题，这是我尝试过的，但是在运行这些查询 8 小时后我停止了它们..

SELECT
  ps2.ordernumber,
  ps2.sku,
  ps2.country,
  ps2.transportprice
FROM test.inserttest2 aa
JOIN (SELECT
  a.ordernumber,
  b.sku,
  b.country,
  b.transportprice
FROM test.inserttest3 a
INNER JOIN transportcost AS b
  ON a.sku = b.sku
WHERE a.system_created > '2017-12-01') ps2
  ON aa.odernumber = ps2.ordernumber
WHERE aa.country = ps2.country
AND aa.system_created > '2017-12-01'
;

.

SELECT
  b.sku,
  b.country,
  b.transportcost
FROM test.inserttest3 AS a
INNER JOIN test.inserttest2 AS aa
  ON aa.ordernumber = a.ordernumber
INNER JOIN transportcost AS b
  ON a.sku = b.sku
WHERE aa.country = b.country
AND a.system_created > '2017-12-01'

;

Answer 1

1. 您想要使用 transportcost 表的运输价格更新调用 inserttest3 的订单商品表。为此，您首先尝试查询。这听起来很简单:只需加入表即可。
2. 您希望将其限制为 2017 年 12 月 1 日之后创建的订单和订单商品。使用适当的数据库，不可能在订单之前插入订单项，因此我们可以将其限制为 2017 年 12 月 1 日之后创建的订单。

查询:

select
  io.ordernumber,
  io.sku,
  io.transportcost as cost_wrong,
  t.transportprice as cost_right
from test.inserttest2 o 
join test.inserttest3 io on io.ordernumber = o.ordernumber
join transportcost t on t.sku = io.sku and t.country = o.country
where o.system_created > date '2017-12-01';

所以日期是我们限制读取数据量的唯一参数。因此，您应该在该列上有一个索引。可能的索引:

create index idx_o on inserttest2 (system_created, ordernumber, country);
create index idx_io on inserttest3 (ordernumber, sku);
create index idx_t on transportcost (sku, country, transportprice);