Skip to main content

32.优化数据变更语句

本节介绍如何加快数据更改语句：INSERT、UPDATE 和 DELETE。 传统的 OLTP 应用程序和现代 Web 应用程序通常会执行许多小型数据更改操作，其中并发性至关重要。 数据分析和报告应用程序通常运行同时影响许多行的数据更改操作，其中主要考虑因素是写入大量数据并使索引保持最新的 I/O。 为了插入和更新大量数据（业内称为 ETL，即“提取-转换-加载”），有时会使用其他 SQL 语句或外部命令来模拟 INSERT、UPDATE 和 DELETE 语句的效果。

优化 INSERT 语句​

为了优化插入速度，请将许多小操作组合成一个大操作。 理想情况下，建立一个连接，一次发送许多新行的数据，并将所有索引更新和一致性检查延迟到最后。

插入一行所需的时间由以下因素决定，其中数字表示大致比例：

  • 连接：(3)
  • 向服务器发送查询：(2)
  • 解析查询：(2)
  • 插入行：（1 × 行大小）
  • 插入索引：（1 × 索引数量）
  • 结束：(1)

这没有考虑打开表的初始开销，每个并发运行的查询都会执行一次打开表的开销。

假设 B 树索引，表的大小会使索引的插入速度减慢 log N。

可以使用以下方法来加快插入速度：

如果要同时从同一客户端插入多行，请使用具有多个 VALUES 列表的 INSERT 语句一次插入多行。 这比使用单独的单行 INSERT 语句要快得多（在某些情况下快很多倍）。 如果要将数据添加到非空表中，则可以调整bulk_insert_buffer_size 变量以使数据插入速度更快。

从文本文件加载表时，请使用 LOAD DATA。 这通常比使用 INSERT 语句快 20 倍。

利用列具有默认值这一事实。 仅当要插入的值与默认值不同时才显式插入值。 这减少了MySQL必须做的解析并提高了插入速度。

优化 UPDATE 语句​

更新语句的优化类似于 SELECT 查询，但具有额外的写入开销。 写入速度取决于更新的数据量和更新的索引数量。 未更改的索引不会更新。

获得快速更新的另一种方法是延迟更新，然后稍后连续进行多次更新。 如果锁定表，一起执行多个更新比一次执行一个更新要快得多。

对于使用动态行格式的 MyISAM 表，将行更新为更长的总长度可能会拆分行。 如果经常这样做，那么偶尔使用 OPTIMIZE TABLE 就非常重要。

优化 DELETE 语句​

删除 MyISAM 表中的各个行所需的时间与索引的数量成正比。 要更快地删除行，可以通过增加 key_buffer_size 系统变量来增加键缓存的大小。

要从 MyISAM 表中删除所有行，TRUNCATE TABLE tbl_name 比 DELETE FROM tbl_name 更快。 截断操作不是事务安全的； 在活动事务或活动表锁定过程中尝试进行操作时会发生错误。