mysql - 更新查询缓慢(选择快 20 倍或错误的表/查询定义)？

Question

我目前为 prestashop 开发了一个模块并进行了一些调试。我的更新查询花费的时间甚至比多表选择查询还要长 20 倍。

完整调试如下:

37.61 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'dpd', `cena` = 18.36 WHERE `package_id` = 304620 LIMIT 1
36.695 ms UPDATE `ps_fur_orders` SET `id_order` = 3, `kurier` = 'dpd', `cena` = 139.99 WHERE `package_id` = 303915 LIMIT 1
28.798 ms UPDATE `ps_fur_orders` SET `id_order` = 3, `kurier` = 'ups', `cena` = 20.34 WHERE `package_id` = 303914 LIMIT 1
25.523 ms UPDATE `ps_fur_orders` SET `id_order` = 3, `kurier` = 'kex', `cena` = 15.85 WHERE `package_id` = 303913 LIMIT 1
21.014 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'dpd', `cena` = 19.74 WHERE `package_id` = 290048 LIMIT 1
21.013 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'dpd', `cena` = 18.36 WHERE `package_id` = 289821 LIMIT 1
20.988 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'dpd', `cena` = 17.68 WHERE `package_id` = 290788 LIMIT 1
20.981 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'ups', `cena` = 19.74 WHERE `package_id` = 290790 LIMIT 1
20.944 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'kex', `cena` = 15.03 WHERE `package_id` = 289822 LIMIT 1
20.858 ms UPDATE `ps_fur_orders` SET `id_order` = 2, `kurier` = 'ups', `cena` = 19.74 WHERE `package_id` = 290242 LIMIT 1
19.805 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'ups', `cena` = 19.74 WHERE `package_id` = 290789 LIMIT 1
19.791 ms UPDATE `ps_fur_orders` SET `id_order` = 2, `kurier` = 'ups', `cena` = 20.34 WHERE `package_id` = 303912 LIMIT 1
19.759 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'dpd', `cena` = 17.68 WHERE `package_id` = 290339 LIMIT 1
19.656 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'dpd', `cena` = 18.36 WHERE `package_id` = 290791 LIMIT 1
19.654 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'dpd', `cena` = 18.36 WHERE `package_id` = 289820 LIMIT 1
19.597 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'dpd', `cena` = 19.74 WHERE `package_id` = 290241 LIMIT 1
19.54 ms UPDATE `ps_fur_orders` SET `id_order` = 1, `kurier` = 'ups', `cena` = 19.74 WHERE `package_id` = 289951 LIMIT 1
2.315 ms SELECT SQL_NO_CACHE c.`name`, cl.`id_lang`, IF(cl.`id_lang` IS NULL, c.`value`, cl.`value`) AS value, c.id_shop_group, c.id_shop FROM `ps_configuration` c LEFT JOIN `ps_configuration_lang` cl ON (c.id_configuration = cl.id_configuration)
1.574 ms SELECT SQL_NO_CACHE h.`name` as hook, m.`id_module`, h.`id_hook`, m.`name` as module, h.`live_edit` FROM `ps_module` m INNER JOIN `ps_hook_module` hm ON hm.`id_module` = m.`id_module` INNER JOIN `ps_hook` h ON hm.`id_hook` = h.`id_hook` WHERE ((SELECT COUNT(*) FROM ps_module_shop ms WHERE ms.id_module = m.id_module AND ms.id_shop IN (1)) = 1) AND (h.name != "displayPayment") AND (hm.id_shop = 1) ORDER BY hm.`position`
-- [rest is lower than 0.6ms, that's why I wrote 20 times :) ]

表有 17 个条目，并且在 package_id 上设置了唯一键。表定义如下:

CREATE TABLE IF NOT EXISTS `ps_fur_orders` (
    `id_order` int(10) unsigned NOT NULL,
    `package_id` bigint(20) unsigned NOT NULL,
    `package_no` varchar(50) DEFAULT NULL,
    `kurier` char(5) DEFAULT NULL,
    `cena` decimal(10,2) DEFAULT NULL,
    `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `package_id` (`package_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

UPDATE 需要将数据提交到磁盘，这可能是您的时间的来源。在未调整或低性能的机器上，20 毫秒的提交时间并非闻所未闻。