MySQL索引如何不加速更新查询

Question

我在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列上创建一个索引，这是一个int列，只有两个可能的值0和1，令人惊讶的是，没有任何变化

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不必做额外的写操作，所以我真的无法了解这里发生了什么。

Answer 1

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

• 表存在于磁盘上，但它通常被完全缓存在内存中；缓冲池"；。

• 该表为ENGINE=MEMORY。但这只用于临时材料；如果服务器出现故障，它将完全丢失。

  update users_ram set balance=balance+speed，其中sub=1；

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

这样的行似乎有1166970个。这似乎占了桌子的一半？？在这一点上，该指数是相当无用的。但是

不管索引如何，更新1M行都非常慢。

方案A：避开UPDATE也许这可以通过将speed存储在其他表中并在读取数据时执行+来实现。(通常情况下，模式设计不好，需要大量的更新。(

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

你是怎么把索引类型变成HASH的？也许`ENGINE=MEMORY？MySQL的哪个版本？

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

请提供SHOW CREATE TABLE users_ram——还有一些其他的东西我们需要看，比如PRIMARY KEY和ENGINE。

(在处理"添加索引怎么可能没有效果甚至减慢查询速度？"之前，我需要上面的一些信息。(