MySQL 原理与优化之Update 优化

MySQL 原理与优化之Update 优化攻略

Update 的基本语法

UPDATE table_name SET column1=value1, column2=value2,... WHERE some_column=some_value;

Update 语句的执行过程

1. 执行查询操作：选择更新记录，并进行行锁定
2. 根据 SET 子句中的值更新相应列
3. 提交更改并释放行锁定

Update 语句的优化

在大型数据库系统中，Update 语句的优化是非常重要的，因为它们通常需要对数百万行进行操作，而且这些操作还需要用到磁盘 I/O 操作，因此对 Update 语句进行优化可以大大提高 MySQL 数据库的性能。

以下是 Update 语句的常用优化方法：

尽可能少的更新列

Update 语句涉及到的列越多，所需更新的数据就越多，由此更新所使用的时间也就越长。因此，尽可能少地更新列通常是 Update 语句的一个优化策略。

比如，我们可以将只需要更新的列放在 SET 子句中，而不管其他列，例如：

UPDATE table_name SET column1=value1, column2=value2 WHERE some_column=some_value;

确定 WHERE 子句的索引字段

WHERE 子句中的条件字段必须创建索引以加速优化，否则将会导致查询变慢。最好的方案是创建一个基于搜索条件的联合索引。

分段更新

分段更新是将大 SQL 分成更小的、可执行的部分，因此每个部分都比整个查询更快。

例如，我们可以将一个大的 Update 查询分解为多个较小的 Update 查询，每个查询更新一段数据。比如，我们可以按照 ID 范围分别进行更新，例如：

UPDATE table_name SET column1=value1 WHERE id >= 1 AND id <=1000;
UPDATE table_name SET column1=value1 WHERE id >= 1001 AND id <=2000;

移除触发器

当我们更新一张表时，如果有触发器，也会触发触发器相关的事件，从而增加了查询的执行时间。因此，为了提高 Update 查询性能，我们可以移除相关的触发器，然后再执行更新操作。

使用事务性 Update 查询

事务性 Update 查询可以大大提高 Update 查询的性能。当我们在执行一个大型 Update 查询时，为了避免数据丢失或数据不一致的情况，我们可以将查询放在一个事务中执行，并将其提交，而不是逐个提交。

示例1

更新爱好为“篮球”的用户的地址，只更新city字段：

UPDATE user SET city='北京' WHERE hobby='篮球';

优化：

直接用EXPLAIN查看：

EXPLAIN UPDATE user SET city='北京' WHERE hobby='篮球';

结果：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref hobby hobby 40 const 2 Using where
其中，type 为 ref，说明该查询使用了基于索引的搜索条件。

示例2

将 a 表的字段 b 的大小加1，并将该表的所有行的 c 字段的值替换为 10：

UPDATE a SET b=b+1, c=10;

优化：

分段更新：

UPDATE a SET b=b+1 WHERE id >= 1 AND id <=1000;
UPDATE a SET b=b+1 WHERE id >= 1001 AND id <=2000;
......
UPDATE a SET c=10 WHERE id >= 1 AND id <=1000;
UPDATE a SET c=10 WHERE id >= 1001 AND id <=2000;
......

这样将大SQL拆分为多个小SQL，可以有效提高更新性能。