mysql - 提高该查询的效率: Update With Joins and Subqueries

Question

我有一个 mysql 数据库
- 需要发送给人们的包裹表(此处有 16,000 条记录)，account_no、服务上的索引
- 价格表(500,000 条记录) - 价格取决于:送货区域、客户价格和服务类型(例如次日等)、指数面积、价格、服务
- 邮政编码(或邮政编码)第一部分的表格，给出区域(3000)- 客户账户表，包含价格(1600)、价格索引
该查询查找发送包裹的价格，并使用唯一 ID 更新该包裹的客户价格
16000 个包裹记录需要 70 秒更新每个包裹的发送价格

UPDATE
   tbl_parcel AS t20, ( 
   SELECT 
      id, service, rate_group, area, 
      (
         SELECT
            rate 
         FROM
            tbl_rates_all t4 
         WHERE
            t4.service = t10.service 
            AND t4.area = t10.area 
            AND t4.rate_group = t10.rate_group 
      )
      AS price 
   FROM
      (
         SELECT
            id,
            t1.service,
            rate_group,
            area
         FROM
            tbl_parcel t1 
            JOIN
               tbl_account t2 
               ON t1.account_no = t2.account_no 
            JOIN
               tbl_pr_postcode t3 
               ON LEFT(full_pcode, locate(' ', full_pcode) - 1) = t3.postcode 
      ) t10 
) AS src 
   SET
      t20.customer_price = src.price 
   WHERE
      t20.id = src.id

16000条包裹记录耗时70秒

最终正是这部分降低了效率

从 tbl_rates_all t4 在哪里 t4.服务 = t10.服务 AND t4. 区域 = t10. 区域 并且 t4.rate_group = t10.rate_group

我可以为每个费率设置单独的费率表，因为这是原始设计，因此变量可以调用例如tbl_rates001 可能只有 3000 条记录，而不是 500,000 条。在 mysql 中这样做的问题是，在动态创建表名时，如果不使用准备好的语句是不可能的，所以我认为这种方法不好。遗憾的是您无法使用用户变量来保存价格编号，然后将其添加到表价格名称中。

我对数据库和查询还很陌生，所以如果有什么东西对你大喊大叫，这会对你有所帮助，那么感谢你的任何意见

问候

根据要求添加架构

CREATE TABLE `tbl_x_rate_all` (
`id` bigint(20) NOT NULL, 
`service` varchar(4) NOT NULL,
`chargetype` char(1) NOT NULL,
`area` smallint(6) NOT NULL,
`rate` float(7,2) NOT NULL,
`rate_group` smallint(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `tbl_x_rate_all` ADD PRIMARY KEY (`id`), ADD KEY `rate_group` (`rate_group`), ADD KEY `area` (`area`), ADD KEY `service` (`service`),   
ADD KEY `chargetype` (`chargetype`);

Answer 1

假设id、rate_group和area来自t10内的t1，那么你的查询速度较慢吗？以下版本:

UPDATE
    tbl_parcel AS t20
INNER JOIN (
    SELECT
        t1.id,
        t4.rate as price
    FROM tbl_parcel t1
    JOIN tbl_account t2 ON t1.account_no = t2.account_no
    JOIN tbl_pr_postcode t3 ON LEFT(full_pcode, locate(' ', full_pcode) - 1) = t3.postcode
    LEFT JOIN tbl_rates_all t4 ON t1.service = t4.service AND t1.area = t4.area
        AND t1.rate_group = t4.rate_group
    ) src ON t20.id = src.id
SET
    t20.customer_price = src.price
WHERE
    t20.id = src.id

<小时/>

我猜你可以进一步失去往往很麻烦的子查询:

UPDATE
    tbl_parcel AS t20
INNER JOIN tbl_parcel t1 ON t20.id = t1.id
INNER JOIN tbl_account t2 ON t1.account_no = t2.account_no
INNER JOIN tbl_pr_postcode t3 ON LEFT(full_pcode, locate(' ', full_pcode) - 1) = t3.postcode
LEFT JOIN tbl_rates_all t4 ON t1.service = t4.service AND t1.area = t4.area
        AND t1.rate_group = t4.rate_group
SET
    t20.customer_price = t4.rate
WHERE
    t20.id = t1.id
    -- can also replace with
    -- TRUE
    -- or lose it altogether
;

<小时/>

如果您有理由相信连接是瓶颈，您可以尝试在 t1 和 t4 的连接上添加索引:

create index tbl_rates_all_service_area_rate_group_index
    on tbl_rates_all (service, area, rate_group);

create index tbl_parcel_service_area_rate_group_index
    on tbl_parcel (service, area, rate_group);