MySQL SELECT vs UPDATE 索引性能比较

MySQL 是一款常用的关系型数据库管理系统，可以对大量的数据进行高效的操作，其中 SELECT 和 UPDATE 是两个常用的操作。但是，当涉及到索引的时候，这两个操作的性能表现会有所不同。本文将分别介绍 SELECT 和 UPDATE 操作在使用索引时的性能表现，并且通过测试数据进行说明。

阅读更多：MySQL 教程

SELECT 操作

当进行 SELECT 操作时，如果使用了合适的索引，MySQL 可以非常快速地找到所需的行，因为它不需要遍历整个表格来查找。索引使 MySQL 能够快速定位满足 WHERE 子句的行。这就意味着，在查询性能方面，索引是非常重要的。

示例

以下是一个示例表格，包含 100 万行数据，其中包含主键和一个 num 列：

CREATE TABLE test_table (
  id INT PRIMARY KEY,
  num INT
);

INSERT INTO test_table VALUES (1, 100), (2, 200), (3, 300), ..., (1000000, 1000000);

接下来，我们将对该表进行查询，以查找 num 大于 50000 的行：

SELECT * FROM test_table WHERE num > 50000;

测试

使用如下语句对上述查询进行性能测试：

SET profiling=1; 
SELECT * FROM test_table WHERE num > 50000; 
SHOW profiles;

从测试结果可以看出，使用索引后查询语句只需要花费 0.4 毫秒的时间，并且只扫描了 50 万行数据：

+----------+------------+-------------------------------------------+
| Query_ID | Duration   | Query                                     |
+----------+------------+-------------------------------------------+
| 1        | 0.00041025 | SELECT * FROM test_table WHERE num > 50000 |
+----------+------------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

UPDATE 操作

当进行 UPDATE 操作时，如果更新的行包含索引列，则 MySQL 会在更新时重新构造索引。这会增加更新操作的开销。相比之下，如果更新的行未包含索引列，则更新操作的开销可能会更小。因此，在更新性能方面，索引可能不是很重要（除非更新的行包含了索引列）。

示例

以下是一个示例表格，包含 100 万行数据，其中包含主键和一个 num 列：

CREATE TABLE test_table (
  id INT PRIMARY KEY,
  num INT
);

INSERT INTO test_table VALUES (1, 100), (2, 200), (3, 300), ..., (1000000, 1000000);

接下来，我们将对该表进行更新，将 num 大于等于 500000 的行的 num 值改为 -1：

UPDATE test_table SET num = -1 WHERE num >= 500000;

测试

使用如下语句对上述查询进行性能测试：

SET profiling=1; 
UPDATE test_table SET num = -1 WHERE num >= 500000; 
SHOW profiles;

从测试结果可以看出，借助索引的情况下更新数据的时间要更长，更新的行越多，所需的时间就越长。这是因为在进行更新操作的同时，MySQL 还需要更新相应的索引。例如，在本例中，更新操作需要花费 1.4 秒的时间，并更新了 500000 行数据：

+----------+------------+----------------------------+
| Query_ID | Duration   | Query                      |
+----------+------------+----------------------------+
| 1        | 1.43884350 | UPDATE test_table SET ..    |
+----------+------------+----------------------------+
1 row in set, 1 warning (0.01 sec)