MySQL更新使用NOW（）执行速度非常慢

For the last few days, I have observed a simple update query performing very badly. The update time ranges from 2.5 seconds to 15 seconds. The query has been working within performance limits (<1 sec) for the last year and a half but has suddenly started showing degraded performance over the last two days. Any insights would be greatly appreciated.

在过去的几天里，我观察到一个简单的更新查询表现非常糟糕。更新时间范围为2.5秒至15秒。该查询在过去一年半的性能限制（<1秒）内工作，但在过去两天突然开始显示性能下降。任何见解将不胜感激。

update user.auth_token 
set last_access_time = NOW() 
where token = '488f4f040090f1cb749e09a514d3dd3d';

The table contains only 9 rows and has the following definition.

该表仅包含9行，并具有以下定义。

CREATE TABLE `auth_token` (
    `user_name` varchar(45) NOT NULL,
    `token` varchar(45) DEFAULT NULL,
    `last_access_time` datetime DEFAULT NULL,
    `creation_time` datetime NOT NULL,
    `token_type` varchar(45) NOT NULL,
    UNIQUE KEY `token_UNIQUE` (`token`),
    KEY `fk_user_name_idx` (`user_name`),
    CONSTRAINT `fk_user_name` FOREIGN KEY (`user_name`) 
        REFERENCES `user` (`name`) 
        ON DELETE CASCADE ON UPDATE CASCADE
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL version is 5.5.50-0ubuntu0.14.04.1-log

MySQL版本是5.5.50-0ubuntu0.14.04.1-log

Update The explain output is as follows:

更新说明输出如下：

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'auth_token', 'const', 'token_UNIQUE', 'token_UNIQUE', '138', 'const', '1', ''

1 个解决方案

#1

It feels like the unique key on token might as well be a primary key.

感觉就像令牌上的唯一键也可能是主键。

Not having an explicit PK will cause innoDB to add a hidden PK to that table and have all secondary indexes reference that hidden PK.

没有明确的PK将导致innoDB向该表添加隐藏的PK并让所有二级索引引用该隐藏的PK。

Whenever that PK is created it takes a global lock in the server making unrelated queries effect each other in ways that are very hard to find out.

无论何时创建PK，它都会在服务器中进行全局锁定，使得不相关的查询以非常难以找到的方式相互影响。

I would also try to remove the foreign key and execute the same query again, just to make sure that isn't getting in the way of anything.

我也会尝试删除外键并再次执行相同的查询，只是为了确保不会妨碍任何事情。

alter table auth_token add primary key (token),
                       drop key token_unique,
                       drop foreign key fk_user_name;

#1