目录

  • 第八章 优化（三十一）—— 优化数据更改语句
  • 8.2 优化SQL语句
  • 8.2.5 优化数据更改语句
  • 8.2.5.1 优化```INSERT```语句
  • 8.2.5.2 优化UPDATE语句
  • 8.2.5.3 优化DELETE语句


第八章 优化（三十一）—— 优化数据更改语句

8.2 优化SQL语句

8.2.5 优化数据更改语句

8.2.5.1 优化INSERT语句

8.2.5.2 优化UPDATE语句

8.2.5.3 优化DELETE语句

本节将介绍如何加快INSERT、UPDATE和DELETRE等改变数据的语句。传统的联机事务处理（OLTP）应用程序和现代web应用程序通常会做很多小数据改变的操作，其中并发是非常重要的。而数据分析和报告的应用程序通常会一次运行影响许多行的数据改变操作，其中主要考虑的是写入大量数据的I/O和如何保持索引的更新。为了插入和更新大量数据（业界称为ETL—— extract-transform-load “提取-转换-加载”），有时您会使用模拟INSERT、UPDATE和DELETRE语句效果的其他SQL语句或外部命令。

8.2.5.1 优化INSERT语句

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

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

  • 连接：（3）
  • 发送查询到服务器：（2）
  • 解析查询：（2）
  • 插入行：（1 × 行大小）
  • 插入索引：（1 × 索引的个数）
  • 关闭连接：（1）

这里并没有考虑打开这些表的初始时间的开销，对于每个并发运行的查询而言，这些表的打开都是一次性的。

假释使用的是B树索引，那么，随着表的体积不断增大，会以 log N的速度减慢索引的插入。

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

  • 如果您要同时插入来自用一个客户端的许多行，那么，请使用带多个VALUES列表的INSERT语句一次性插入多行。这比使用单独的单行INSERT语句快得多（在某些情况下，要快很多倍）。如果您要向非空表中添加数据，那么可以调整bulk_insert_buffer_size（块插入缓冲区大小）变量的值，使数据插入更快。具体参见第5.1.8节“服务器系统变量”。
  • 当从文本文件加载一个表时，请使用LOAD DATA。这通常比使用INSERT语句快20倍。参见第13.2.7节“LOAD DATA语句”。
  • 利用好有些列有默认值的情形。只有在要插入的值与默认值不同时才会显示地插入值。这会降低MySQL必须要做的解析，且提升了插入的速度。
  • 有关InnoDB表的具体提示，请参见第8.5.5节“InnoDB表加载块数据”。
  • 有关MyISAM表的具体提示，请参见第8.6.2节“MyISAM表加载块数据”。
8.2.5.2 优化UPDATE语句

update更新语句的优化与SELECT查询相似，但增加了额外写入操作的开销。写入的速度取决于要更新的数据量和要更新的索引的个数。没有改变的索引是不会得到更新的。

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

对于使用了动态行格式的MyISAM表而言，把一行更新为更长的总长度可能会拆分该行。如果需要经常这样做，则偶尔使用OPTIMIZE TABLE（优化表）命令是非常重要的。参见第13.7.3.4节“OPTIMIZE TABLE语句”。

8.2.5.3 优化DELETE语句

在MyISAM表中，要删除单行所需的时间与索引的数量成正比。想要删除行更快，可以通过增加key_buffer_size系统变量的值来增加键缓冲的大小。参见第5.1.1“配置服务器”。

想要从MyISAM表中删除所有行，使用TRUNCATE TABLE tbl_name比使用DELETE FROM tbl_name更快。截断操作不是事物安全的，如果在活动事物或活动表锁定时试图操作该命令会发生错误。参见第13.1.37节“TRUNCATE TABLE语句”。