时间戳

首页 > 解决方案 > 在 MySQL 中执行简单的更新查询需要很长时间（等待 innodb）

mysql - 在 MySQL 中执行简单的更新查询需要很长时间（等待 innodb）

问题描述

我有一个 54k 行的表，包含 10G 的数据

我正在对其运行此更新查询：

UPDATE my_table SET blog_object_version='19'

运行时间超过 1小时，
如何提高性能？

附加信息：

我在 AMAZON rds 上运行，db.m5.4xlarge
这是我的实例： 在此处输入图像描述

这是我在aws 性能洞察中看到的：
wait/io/file/innodb/innodb_data_file

在此处输入图像描述

我的数据库上没有运行任何其他查询：

mysql> show processlist;
+----+----------+---------------------+----------+---------+------+----------+----------------------------------------------+
| Id | User     | Host                | db       | Command | Time | State    | Info                                         |
+----+----------+---------------------+----------+---------+------+----------+----------------------------------------------+
|  3 | rdsadmin | localhost:65182     | NULL     | Sleep   |    0 |          | NULL                                         |
|  4 | rdsadmin | localhost           | NULL     | Sleep   |    1 |          | NULL                                         |
|  6 | admin    | 123.45.67.890:6170  | my_table | Query   | 3901 | updating | UPDATE my_table SET blog_object_version='19' |
| 12 | admin    | 123.45.67.890:6360  | NULL     | Sleep   | 2981 |          | NULL                                         |
| 18 | admin    | 123.45.67.890:7001  | NULL     | Query   |    0 | starting | show processlist                             |
+----+----------+---------------------+----------+---------+------+----------+----------------------------------------------+

这是我的桌子：

mysql> show create table my_table\G;
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `index` int(11) NOT NULL AUTO_INCREMENT,
  `id` varchar(100) DEFAULT NULL,
  `user_id` varchar(50) NOT NULL,
  `associate_object_id` varchar(50) NOT NULL,
  `type` varchar(50) DEFAULT NULL,
  `creation_date` datetime DEFAULT NULL,
  `version_id` varchar(50) NOT NULL,
  `blog_object` longtext,
  `blog_object_version` varchar(100) DEFAULT NULL,
  `last_update` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`index`),
  UNIQUE KEY `id_user_id_version_id` (`id`,`user_id`,`version_id`) USING BTREE,
  KEY `user_id_associate_object_id` (`user_id`,`associate_object_id`),
  KEY `user_id_associate_object_id_version_id` (`user_id`,`associate_object_id`,`version_id`)
) ENGINE=InnoDB AUTO_INCREMENT=54563151 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

这些是我的索引：

mysql> SHOW INDEX FROM my_table;
+----------+------------+----------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                               | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| my_table |          0 | PRIMARY                                |            1 | index               | A         |       43915 |     NULL | NULL   |      | BTREE      |         |               |
| my_table |          0 | id_user_id_version_id                  |            1 | id                  | A         |        3659 |     NULL | NULL   | YES  | BTREE      |         |               |
| my_table |          0 | id_user_id_version_id                  |            2 | user_id             | A         |        8783 |     NULL | NULL   |      | BTREE      |         |               |
| my_table |          0 | id_user_id_version_id                  |            3 | version_id          | A         |       43915 |     NULL | NULL   |      | BTREE      |         |               |
| my_table |          1 | user_id_associate_object_id            |            1 | user_id             | A         |         378 |     NULL | NULL   |      | BTREE      |         |               |
| my_table |          1 | user_id_associate_object_id            |            2 | associate_object_id | A         |        4391 |     NULL | NULL   |      | BTREE      |         |               |
| my_table |          1 | user_id_associate_object_id_version_id |            1 | user_id             | A         |         385 |     NULL | NULL   |      | BTREE      |         |               |
| my_table |          1 | user_id_associate_object_id_version_id |            2 | associate_object_id | A         |        6273 |     NULL | NULL   |      | BTREE      |         |               |
| my_table |          1 | user_id_associate_object_id_version_id |            3 | version_id          | A         |       43915 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

标签： mysqlamazon-rds

解决方案

非常基本的问题，有一个非常基本的解决方案：

INDEX(blog_object_version)

为什么？如果没有此索引，则UPDATE必须读取54K（或 54M？）行中的每一行以检查'19'.

使用该索引，只需要读取相关行。

尖端：

许多VARCHAR列听起来应该是INT（或者更小的东西，比如SMALLINT）？（更改类型不太可能加快查询速度。）

折腾user_id_associate_object_id；索引user_id_associate_object_id_version_id处理同样的事情。

更新所有行

最多更新 1K 行是合理的。如果合适的话，更新不到 20% 的表可能会使用索引。

但是...如果您需要更新所有 54K 行，则存在几个问题。

这将需要很长时间，并且可能需要大量磁盘空间，因为新旧副本都会保留到更新完成。（这样它就可以原子地提交或回滚整个更新。）

通常，需要更新整个表的所有行中的列是“糟糕的设计”。有时，可以将列放在另一个表中的一行中。然后是更新的单行查询blog_object_version。但这意味着JOIN当你需要它时在 a 中做 a SELECT。（这可能不是问题。）如果您没有更改所有行，那么它会更混乱。

所以，...如果您决定更新“很多”（或全部）一个大表，我建议以每行 100-1000 行的块进行更新。更多细节：http: //mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

更改缓冲区

另一个问题（不太重要）是更新非唯一索引列时，索引需要更新。这需要修改代表INDEX. 对于非唯一索引，这是在后台完成的，主要是在提交查询之后。

在完成更新 BTree 之前不存在索引不正确的风险。这是因为“更改缓冲区”。该构造保留挂起的索引更新，以便以后持久保存到磁盘。

推荐阅读