mysql对于优化好的查询的更新速度较慢

Question

我正面临一个更新状态的问题，在非常小的查询中需要长达4s的时间。

Count: 1  Time=3.87s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), ***[***]@localhost
UPDATE p_villages v SET v.resources='1 3273182 240000000 1500 245000 25,2 3273236 240000000 1500 245000 25,3 3273280 240000000 1500 245000 25,4 166973998 280000000 1500 3675000 325', v.cp='2941.8712 1867', v.crop_consumption=v.crop_consumption+1, v.troops_num='-1:11 0,12 0,13 0,14 9628,15 0,16 0,17 0,18 0,19 0,20 0,99 0', v.last_update_date=NOW() WHERE v.id='20899'

Count: 1  Time=2.97s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), ***[***]@localhost
UPDATE p_villages v
SET
v.update_key='38c47',
v.resources='1 827064241 840000000 1500 800 0,2 814159650 840000000 1500 800 0,3 801255060 840000000 1500 800 0,4 839968830 840000000 1500 1200 0',
v.cp='27.304202777778 1106',
v.last_update_date=NOW()
WHERE
v.id='1084' AND v.player_id='22290'

查询的例子是:

db::query("UPDATE p_villages v SET v.resources=:res, v.cp=:cp, v.crop_consumption=v.crop_consumption+$troops_crop_consumption, v.troops_num=:tnum, v.last_update_date=NOW() WHERE v.id=:id", array(
'res' => $this->_getResourcesString($resultArr['resources']),
'cp' => $resultArr['cp']['cpValue'] . " " . $resultArr['cp']['cpRate'],
'tnum' => $troopTrainingStr,
'id' => intval($taskRow['village_id'])
));

id被索引

updates://

这是explain

的结果你的SQL查询已经成功执行。

explain UPDATE p_villages v
SET
v.update_key='38c47',
v.resources='1 827064241 840000000 1500 800 0,2 814159650 840000000 1500 800 0,3 801255060 840000000 1500 800 0,4 839968830 840000000 1500 1200 0',
v.cp='27.304202777778 1106',
v.last_update_date=NOW()
WHERE
v.id='1084' AND v.player_id='1'

1   UPDATE  v   
NULL
range   PRIMARY,NewIndex2,NewIndex6 PRIMARY 4   const   1   100.00  Using where 

这是一个简单得多的查询，耗时2.39秒。

Count: 5  Time=2.39s (11s)  Lock=0.00s (0s)  Rows=0.0 (0), ***[***]@localhost
UPDATE g_settings gs SET gs.qlocked=1, qlocked_date=NOW() WHERE gs.qlocked=0

SHOW CREATE TABLE p_villages

p_villages  CREATE TABLE `p_villages` (
 `id` int(6) NOT NULL AUTO_INCREMENT,
 `rel_x` smallint(6) DEFAULT NULL,
 `rel_y` smallint(7) DEFAULT NULL,
 `field_maps_id` tinyint(4) DEFAULT NULL,
 `rand_num` int(11) DEFAULT NULL,
 `image_num` tinyint(4) DEFAULT NULL,
 `parent_id` int(6) DEFAULT NULL,
 `tribe_id` tinyint(1) DEFAULT NULL,
 `player_id` int(5) DEFAULT NULL,
 `alliance_id` int(5) DEFAULT NULL,
 `player_name` varchar(15) DEFAULT NULL,
 `village_name` varchar(35) DEFAULT NULL,
 `alliance_name` varchar(25) DEFAULT NULL,
 `is_capital` tinyint(1) DEFAULT 0,
 `is_special_village` tinyint(1) DEFAULT 0,
 `is_oasis` tinyint(1) DEFAULT NULL,
 `people_count` int(11) DEFAULT 2,
 `crop_consumption` bigint(16) DEFAULT 2,
 `time_consume_percent` float DEFAULT 100,
 `offer_merchants_count` tinyint(4) DEFAULT 0,
 `resources` text DEFAULT NULL,
 `cp` varchar(300) DEFAULT NULL,
 `buildings` text DEFAULT NULL,
 `troops_training` text DEFAULT NULL,
 `troops_num` text DEFAULT NULL,
 `troops_out_num` text DEFAULT NULL,
 `troops_intrap_num` text DEFAULT NULL,
 `troops_out_intrap_num` text DEFAULT NULL,
 `troops_trapped_num` int(11) DEFAULT 0,
 `allegiance_percent` int(11) DEFAULT 100,
 `child_villages_id` text DEFAULT NULL,
 `village_oases_id` text DEFAULT NULL,
 `creation_date` datetime DEFAULT NULL,
 `update_key` varchar(5) DEFAULT NULL,
 `last_update_date` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `NewIndex2` (`player_id`),
 KEY `NewIndex3` (`is_special_village`),
 KEY `NewIndex4` (`is_oasis`),
 KEY `NewIndex5` (`people_count`),
 KEY `NewIndex1` (`village_name`),
 KEY `NewIndex6` (`player_id`,`is_oasis`)
) ENGINE=InnoDB AUTO_INCREMENT=22501 DEFAULT CHARSET=utf8mb3    

Answer 1

是ENGINE=InnoDB吗?如果不是，那么这可能是一个大问题。请提供SHOW CREATE TABLE p_villages

你说id被索引了，但它是PRIMARY KEY吗?根据定义，PRIMARY KEY是唯一的。因此只涉及一行，查询应该非常快(几个毫秒)。

是否有其他查询同时运行?他们可能阻止访问一行。

增加RAM，再增加innodb_buffer_pool_size不会有太大帮助;"3 seconds"是不是有别的问题。你认为这个设置有什么价值?

如果id是唯一的，为什么你也在WHERE v.id='1084' AND v.player_id='1'中测试player_id(这里没有问题，只是一个谜题)

如果id是唯一的，其中一个将成功，一个将失败:

v.id='1084' AND v.player_id='22290'
v.id='1084' AND v.player_id='1'

"一切都有索引"&;——查询设置4列;这意味着需要更新4个索引。(不过，这只会多花几毫秒。)