mysql 加速更新语句以在表之间复制值

Question

我有 2 个表，我试图将值从迁移表复制到常规表。看来还需要一段时间。 (43k 条记录 10 分钟)

UPDATE phppos_sales_items,phppos_sales_items_migrate 
SET 
     phppos_sales_items.subtotal = phppos_sales_items_migrate.subtotal,
     phppos_sales_items.tax = phppos_sales_items_migrate.tax,
     phppos_sales_items.total = phppos_sales_items_migrate.total,
     phppos_sales_items.profit = phppos_sales_items_migrate.profit

WHERE 
phppos_sales_items.sale_id = phppos_sales_items_migrate.sale_id and           
phppos_sales_items.line = phppos_sales_items_migrate.line and      
phppos_sales_items.item_id = phppos_sales_items_migrate.item_id;

迁移表

CREATE TABLE `phppos_sales_items_migrate` (
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `item_id` int(10) NOT NULL DEFAULT '0',
  `line` int(11) NOT NULL DEFAULT '0',
  `subtotal` decimal(23,10) DEFAULT NULL,
  `total` decimal(23,10) DEFAULT NULL,
  `tax` decimal(23,10) DEFAULT NULL,
  `profit` decimal(23,10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

销售商品

 CREATE TABLE `phppos_sales_items` (
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `item_id` int(10) NOT NULL DEFAULT '0',
  `rule_id` int(10) DEFAULT NULL,
  `rule_discount` decimal(23,10) DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `serialnumber` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `line` int(11) NOT NULL DEFAULT '0',
  `quantity_purchased` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `item_cost_price` decimal(23,10) NOT NULL,
  `item_unit_price` decimal(23,10) NOT NULL,
  `regular_item_unit_price_at_time_of_sale` decimal(23,10) DEFAULT NULL,
  `discount_percent` decimal(15,3) NOT NULL DEFAULT '0.000',
  `commission` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `subtotal` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `tax` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `total` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `profit` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  PRIMARY KEY (`sale_id`,`item_id`,`line`),
  KEY `item_id` (`item_id`),
  KEY `phppos_sales_items_ibfk_3` (`rule_id`),
  CONSTRAINT `phppos_sales_items_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`),
  CONSTRAINT `phppos_sales_items_ibfk_2` FOREIGN KEY (`sale_id`) REFERENCES `phppos_sales` (`sale_id`),
  CONSTRAINT `phppos_sales_items_ibfk_3` FOREIGN KEY (`rule_id`) REFERENCES `phppos_price_rules` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

有什么办法可以加快速度吗？

Answer 1

decimal(23,10) 过多，尤其是对于整数值，例如 quantity_purchased。缩小数据类型将有助于加快速度。

使用带有 JOIN ... ON ... 语法的“多表更新”。

让我们看看输出

EXPLAIN
SELECT * FROM phppos_sales_items         AS i
         JOIN phppos_sales_items_migrate AS m
       ON  i.sale_id = m.sale_id
      and  i.line    = m.line
      and  i.item_id = m.item_id;

如果您有足够新的 MySQL 版本，还请提供EXPLAIN UPDATE ...

migrate 中是否有可能出现新行？如果是这样，您不需要INSERT ... ON DUPLICATE KEY UPDATE ...吗？

哪个表中有 43K 行？另一个表中有多少个？