mysql - SQL 查询运行速度极慢

Question

我有一个 sql 查询，用于收集库存中所有产品的年初至今总计。此查询运行速度很快，在一秒多一点的时间内返回了大约 5000 个结果。

使用的查询是

SELECT `ITINCODE` as Code, `IT_product_id` as ID,
SUM(IF(YEAR(`ITDATE`) = YEAR(CURDATE() ), IF(ITTYPE = "O",`ITVAL`, -`ITVAL` ), 0) ) as 'YTD_VAL'
FROM `FITEMS`
WHERE (ITTYPE = 'O' OR `ITTYPE` = 'R') AND ITBACKORDER = 'N' AND ITAMNT > 0 AND YEAR(`ITDATE`) >= YEAR(CURDATE() ) -1
GROUP BY `ITINCODE`
ORDER BY YTD_VAL DESC

我想获取 YTD_VAL 中的值并将它们存储在实际产品表中，以便它们可以用作其他查询的 ORDER BY。我在产品表中添加了一个名为 ytd_val 的新字段，然后运行

UPDATE products p SET p.ytd_val = 
(SELECT SUM(IF(YEAR(`ITDATE`) = YEAR(CURDATE() ), IF(ITTYPE = 'O',`ITVAL`, -`ITVAL` ), 0) ) as 'YTD_VAL'
FROM `FITEMS`
WHERE ITINCODE  = p.products_model AND (ITTYPE = 'O' OR `ITTYPE` = 'R') AND ITBACKORDER = 'N' AND ITAMNT > 0 AND YEAR(`ITDATE`) >= YEAR(CURDATE() ) -1
GROUP BY `ITINCODE`
ORDER BY YTD_VAL DESC)

想法是每晚通过 cron 作业运行此程序，以便更新值以反射(reflect)前几天的销售额。

但是，运行此查询已花费 10 多分钟，但仍未完成。

FITEMS 表中的 ITINCODE 与产品表中的 products_model 相同。FITEMS 表中的 IT_product_id 与 products 表中的 products_id 相同。

我可以做些什么来加快查询速度？我认为，由于原始结果查询的返回速度足够快，因此仅更新另一个表上的值将花费几秒钟的时间!

表结构如下:

show create table fitems\G;

Create Table: CREATE TABLE `fitems` (
  `ITUNIQUEREF` int(11) unsigned NOT NULL,
  `ITAMNT` int(11) NOT NULL,
  `ITREF` int(11) unsigned NOT NULL,
  `ITTYPE` char(1) NOT NULL,
  `ITVAL` decimal(10,4) NOT NULL,
  `ITVAT` decimal(10,4) NOT NULL,
  `ITPRICE` decimal(10,4) NOT NULL,
  `ITDATE` date NOT NULL,
  `ITBACKORDER` char(1) NOT NULL,
  `ITDISC` decimal(10,2) NOT NULL,
  `ITORDERREF` int(11) NOT NULL,
  `ITTREF` int(11) unsigned NOT NULL,
  `ITDATEDLY` date NOT NULL,
  `ITINCODE` char(20) NOT NULL,
  `IT_product_id` int(11) unsigned NOT NULL,
  `ITBUILT` int(11) NOT NULL,
  PRIMARY KEY (`ITUNIQUEREF`),
  KEY `ITREF` (`ITREF`,`ITTYPE`,`ITDATE`,`ITBACKORDER`,`ITORDERREF`,`ITDATEDLY`,`ITINCODE`,`IT_product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

.

show create table products\G;

CREATE TABLE `products` (
  `products_id` int(11) NOT NULL,
  `products_type` int(11) NOT NULL DEFAULT '1',
  `products_quantity` float NOT NULL DEFAULT '0',
  `products_model` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `products_image` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_group_a_price` decimal(15,4) NOT NULL,
  `products_group_b_price` decimal(15,4) NOT NULL,
  `products_group_c_price` decimal(15,4) NOT NULL,
  `products_group_d_price` decimal(15,4) NOT NULL,
  `products_group_e_price` decimal(15,4) NOT NULL,
  `products_group_f_price` decimal(15,4) NOT NULL,
  `products_group_g_price` decimal(15,4) NOT NULL,
  `products_virtual` tinyint(1) NOT NULL DEFAULT '0',
  `products_date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `products_last_modified` datetime DEFAULT NULL,
  `products_date_available` datetime DEFAULT NULL,
  `products_weight` float NOT NULL DEFAULT '0',
  `products_status` tinyint(1) NOT NULL DEFAULT '0',
  `products_tax_class_id` int(11) NOT NULL DEFAULT '0',
  `manufacturers_id` int(11) DEFAULT NULL,
  `products_ordered` float NOT NULL DEFAULT '0',
  `products_quantity_order_min` float NOT NULL DEFAULT '1',
  `products_quantity_order_units` float NOT NULL DEFAULT '1',
  `products_priced_by_attribute` tinyint(1) NOT NULL DEFAULT '0',
  `product_is_free` tinyint(1) NOT NULL DEFAULT '0',
  `product_is_call` tinyint(1) NOT NULL DEFAULT '0',
  `products_quantity_mixed` tinyint(1) NOT NULL DEFAULT '0',
  `product_is_always_free_shipping` tinyint(1) NOT NULL DEFAULT '0',
  `products_qty_box_status` tinyint(1) NOT NULL DEFAULT '1',
  `products_quantity_order_max` float NOT NULL DEFAULT '0',
  `products_sort_order` int(11) NOT NULL DEFAULT '0',
  `products_canonical` text COLLATE utf8_unicode_ci NOT NULL,
  `products_discount_type` tinyint(1) NOT NULL DEFAULT '0',
  `products_discount_type_from` tinyint(1) NOT NULL DEFAULT '0',
  `products_price_sorter` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `master_categories_id` int(11) NOT NULL DEFAULT '0',
  `products_mixed_discount_quantity` tinyint(1) NOT NULL DEFAULT '1',
  `metatags_title_status` tinyint(1) NOT NULL DEFAULT '0',
  `metatags_products_name_status` tinyint(1) NOT NULL DEFAULT '0',
  `metatags_model_status` tinyint(1) NOT NULL DEFAULT '0',
  `metatags_price_status` tinyint(1) NOT NULL DEFAULT '0',
  `metatags_title_tagline_status` tinyint(1) NOT NULL DEFAULT '0',
  `pricing_group` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ytd_val` int(20) NOT NULL,
  PRIMARY KEY (`products_id`),
  KEY `idx_products_date_added_zen` (`products_date_added`),
  KEY `idx_products_status_zen` (`products_status`),
  KEY `idx_products_date_available_zen` (`products_date_available`),
  KEY `idx_products_ordered_zen` (`products_ordered`),
  KEY `idx_products_model_zen` (`products_model`),
  KEY `idx_products_price_sorter_zen` (`products_price_sorter`),
  KEY `idx_master_categories_id_zen` (`master_categories_id`),
  KEY `idx_products_sort_order_zen` (`products_sort_order`),
  KEY `idx_manufacturers_id_zen` (`manufacturers_id`),
  KEY `products_price` (`products_price`),
  KEY `products_status_products_price` (`products_status`,`products_price`),
  FULLTEXT KEY `idx_enhanced_products_model` (`products_model`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

SELECT 总是比 UPDATE 快得多。

加快更新速度:

• 查看您正在更新的表上的索引:是否都需要它们？如果没有，请删除不需要的(我至少会删除 idx_products_status_zen，因为它也包含在 products_status_products_price 中)

• 查看数据模型:您能否对正在更新的表进行分区？如果是这样，那将加快更新速度，因为您正在更新的索引将更小，因此更新速度更快；

• 使用 InnoDB。它更快；

• 您需要符合 ACID 标准吗？如果还不行，那就更改InnoDB的设置来加速系统。

• 如果你真的在使用 MySQL:切换到 MariaDB:大约快 8%；

• 安装监控以查看瓶颈在哪里:IO 或 CPU:如果是 IO on read，则尝试压缩。