Медленный UPDATE по Primary Key MySQL/MariaDB?
Запрос:
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 мс — это шикарно. SELECT этой строки всегда за 0мс.
Размер таблицы (6,354 rows).
describe cart; +-----------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | YES | MUL | NULL | | | completed | tinyint(1) | NO | | 0 | | +-----------+------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) 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 | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Вот кусок профилирования этого запроса который виновен:
| 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 |
Server version: 10.0.17-MariaDB-1~wheezy
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 мс — это шикарно. SELECT этой строки всегда за 0мс.
Размер таблицы (6,354 rows).
describe cart; +-----------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | YES | MUL | NULL | | | completed | tinyint(1) | NO | | 0 | | +-----------+------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) 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 | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Вот кусок профилирования этого запроса который виновен:
| 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 |
Server version: 10.0.17-MariaDB-1~wheezy