时间戳

首页 > 解决方案 > MySQL索引如何不加快更新查询？

mysql - MySQL索引如何不加快更新查询？

问题描述

我有一个位于 RAM 中的表并进行一些性能测试。

让我们考虑一个示例查询，添加解释语句和结果

mysql> explain update users_ram set balance = balance + speed where sub = 1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | UPDATE      | users_ram | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2333333 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)

mysql>  update users_ram set balance = balance + speed where sub = 1;
Query OK, 1166970 rows affected (0.37 sec)
Rows matched: 1166970  Changed: 1166970  Warnings: 0

如您所见，没有索引需要 0.37 秒。然后我在sub列上创建一个索引，这是一个只有两个可能值 0 和 1 的 int 列，令人惊讶的是没有任何变化

mysql> create index sub on users_ram (sub);
Query OK, 2333333 rows affected (2.04 sec)
Records: 2333333  Duplicates: 0  Warnings: 0

mysql> show index from lords.users_ram;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users_ram |          0 | user     |            1 | user        | NULL      |     2333333 |     NULL | NULL   | YES  | HASH       |         |               |
| users_ram |          1 | sub      |            1 | sub         | NULL      |           2 |     NULL | NULL   |      | HASH       |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> explain update users_ram set balance = balance + speed where sub = 1;
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
|  1 | UPDATE      | users_ram | NULL       | range | sub           | sub  | 5       | const | 1166666 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
1 row in set (0.00 sec)

mysql>  update users_ram set balance = balance + speed where sub = 1;
Query OK, 1166970 rows affected (0.37 sec)
Rows matched: 1166970  Changed: 1166970  Warnings: 0

如果我删除索引并再次添加它，但现在使用btree，它会变得更加奇怪

mysql> explain update users_ram set balance = balance + speed where sub = 1;
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
|  1 | UPDATE      | users_ram | NULL       | range | sub           | sub  | 5       | const | 1057987 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+-------+---------+----------+-------------+
1 row in set (0.00 sec)

mysql> update users_ram set balance = balance + speed where sub = 1;
Query OK, 1166970 rows affected (0.62 sec)
Rows matched: 1166970  Changed: 1166970  Warnings: 0

添加索引怎么会没有效果甚至会减慢查询速度？

让我们考虑到我没有修改被索引的列，所以 mysql 不必做额外的写操作，所以我真的无法得到这里真正发生的事情。

标签： mysqlindexing

解决方案

“位于 RAM 中的表”——我怀疑这在技术上是不正确的。可能性（在 MySQL 中）：

  • 该表位于磁盘上，但通常完全缓存在 RAM 中的“buffer_pool”中。

  • 表是ENGINE=MEMORY。但这仅用于临时的东西；如果服务器出现故障，它将完全丢失。

    update users_ram set balance = balance + speed where sub = 1;

该表users_ram需要一些以 . 开头的索引sub。这样，它可以直接进入行。但...

似乎有 1166970 行这样的行。这似乎是桌子的一半？？在这一点上，索引是非常无用的。但...

无论索引如何，更新 1M 行都非常慢。

计划 A：避免UPDATE. 也许这可以通过存储speed在其他表中并在+您读取数据时执行。（需要这样的大量更新通常是糟糕的架构设计。）

计划 B：分块更新：http: //mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

你到底是怎么得到 index-type 的HASH？也许`ENGINE=MEMORY？什么版本的MySQL？

是什么speed？另一个专栏？一个常数？

请提供SHOW CREATE TABLE users_ram- 我们需要查看其他一些内容，例如PRIMARY KEYand ENGINE。

（在处理“添加索引怎么可能没有效果甚至减慢查询速度？”之前，我需要上述一些信息）

推荐阅读