MySQL/MariaDB主键的缓慢更新

Question

查询：

UPDATE `cart` SET `user_id` = NULL, `completed` = 0 WHERE `id` = 6948;
Query OK, 0 rows affected (1.21 sec)
Rows matched: 1  Changed: 0  Warnings: 0

0行受影响，但花费了1210 it。选择本行时，按id始终取0ms。表大小为(6,354行)。

> show create table cart;
CREATE TABLE `cart` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `completed` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'флаг, указывающий на оформление заказа из данной корзины',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6964 DEFAULT CHARSET=utf8

> show index from cart;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cart  |          0 | PRIMARY  |            1 | id          | A         |        6386 |     NULL | NULL   |      | BTREE      |         |               |
| cart  |          1 | user_id  |            1 | user_id     | A         |        2128 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

以下是此查询的show profile all摘录

| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file   | Source_line |

| query end            | 2.502555 | 0.003000 |   0.000000 |                88 |                   8 |             0|           136 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc  |        5093 |

为什么block_ops_out不一致，从0到400，取0ms ~2500 0ms？如何找出它高企的根本原因？

服务器版本:10.0.17-MariaDB-1~喘息。VPS根本没有任何明显的负载。

UPD:在查询后添加了状态vars：

MariaDB> flush status; UPDATE `cart` SET `user_id` = NULL, `completed` = 0 WHERE `id` = 6948; SHOW SESSION STATUS LIKE 'Handler%';
Query OK, 0 rows affected (0.54 sec)

^[[A^[[BQuery OK, 0 rows affected (3.88 sec)
Rows matched: 1  Changed: 0  Warnings: 0

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 2     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 2     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 1     |
| Handler_write              | 0     |
+----------------------------+-------+
25 rows in set (0.00 sec)

Answer 1

我所能建议的就是尝试将表和数据迁移到Mysql MyIsam引擎并执行相同的查询。也许您只是发现了MariaDB的一个弱点，或者某些东西配置错误或调过头了。