MySQL手册版本 5.0.20-MySQL优化(四) （4）

  来源：MySQL手册版本 5.0.20    作者：译者：叶金荣

可以在锁表后，一起执行几个语句来加速 INSERT 操作：

LOCK TABLES a WRITE;

INSERT INTO a VALUES (1,23),(2,34),(4,33);

INSERT INTO a VALUES (8,26),(6,29);

UNLOCK TABLES;

这对性能提高的好处在于：直到所有的 INSERT 语句都完成之后，索引缓存一次性刷新到磁盘中。通常情况是，多有少次 INSERT 语句就会有多数次索引缓存刷新到磁盘中的开销。如果能在一个语句中一次性插入多个值的话，显示的锁表操作也就没必要了。对事务表而言，用 BEGIN/COMMIT 代替 LOCK TABLES 来提高速度。锁表也回降低多次连接测试的总时间，尽管每个独立连接为了等待锁的最大等待时间也会增加。例如：

Connection 1 does 1000 inserts

Connections 2, 3, and 4 do 1 insert

Connection 5 does 1000 inserts

如果没有锁表，则连接2，3，4会在1，5之前就做完了。如果锁表了，则连接2，3，4可能在1，5之后才能完成，但是总时间可能只需要40%。MySQL的 INSERT, UPDATE, DELETE 操作都非常快，不过在一个语句中如果有超过5个插入或者更新时最好加锁以得到更好的性能。如果要一次性做很多个插入，最好是在每个循环（大约1000次）的前后加上 LOCK TABLES 和 UNLOCK TABLES，从而让其他进程也能访问数据表;这么做性能依然不错。INSERT 总是比 LOAD DATA INFILE 插入数据来得慢，因为二者的实现策略有着分明的不同。

想要让 MyISAM 表更快，在 LOAD DATA

INFILE 和 INSERT 时都可以增加系统变量 key_buffer_size 的值，详情请看"7.5.2 Tuning Server Parameters"。

7.2.13 加速 UPDATE

UPDATE 语句的优化和 SELECT 一样，只不过它多了额外的写入开销。写入的开销取决于要更新的记录数以及索引数。如果索引没有发生变化，则就无需更新。

另一个提高更新速度的办法是推迟更新并且把很多次更新放在后面一起做。如果锁表了，那么同时做很多次更新比分别做更新来得快多了。

注意，如果是在 MyISAM 表中使用了动态的记录格式，那么记录被更新为更长之后就可能会被拆分。如果经常做这个，那么偶尔做一次 OPTIMIZE TABLE 就显得非常重要了。详情请看"14.5.2.5 OPTIMIZE TABLE Syntax"。

7.2.14 加速 DELETE

删除单个记录的时间和它的索引个数几乎成正比。想更快地删除记录，可以增加索引键的缓存。详情请看"7.5.2 Tuning Server Parameters"。

如果想要删除数据表的所有记录，请使用 TRUNCATE TABLE tbl_name 而不是 DELETE FROM tbl_name。详情请看"14.1.9 TRUNCATE Syntax"。

7.2.15 其他优化点子

本章节列出了一些改善查询处理速度的其他点子：

使用永久连接到数据库，避免连接的开销。如果需要初始化很多连接，而又不能用永久连接，那么可以修改变量 thread_cache_size 的值，详情请看"7.5.2 Tuning Server Parameters"。

总是检查查询是否利用了表中已有的索引。在MySQL中，可以用 EXPLAIN 语句来分析。详情请看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。

尽量不要在经常需要更新的 MyISAM 表上用太过复杂的 SELECT 语句，这是为了避免在读和写之间争夺锁。

在 MyISAM 表中，如果没有正在删除记录，则可以在其他查询正在读取数据的同时插入记录。如果这种情况十分重要，那么就要尽量在表没有删除记录时才使用表。另一个可能的办法就是在删除一大堆记录之后执行 OPTIMIZE TABLE 语句。

如果总是需要按照 expr1, expr2, ... 的顺序取得记录，那么请使用 ALTER TABLE ... ORDER BY expr1, expr2, ... 修改表。通过这种方法扩充修改表之后，就可能获得更高的性能表现。

在一些情况下，让一个字段类型是 ``hashed`` ，它基于其他字段信息。如果这个字段比较短而且基本上都是唯一值的话，那么就可能会比在几个字段上使用一个大索引来得更快，很简单的就能使用这样的额外字段，如下：

SELECT * FROM tbl_name WHERE hash_col=MD5(CONCAT(col1,col2))

AND col1='constant' AND col2='constant';

如果 MyISAM 表经常大量修改，那么要尽量避免修改所有的变长字段（VARCHAR, BLOB，TEXT）。尽管表中只有一个变长字段，它也会采用动态记录格式的。详情请看"15 MySQL Storage Engines and Table Types"。

通常情况下，当数据表记录变 ``大`` 之后，将表拆分成几个不同的表并没有多大用处。访问一条记录是最大的性能点在于磁盘搜索时找到记录的第一个字节上。只要找到记录的位置后，现在的大部分磁盘对于大部分的应用程序来说都能很快的读取到记录。将 MyISAM 表拆分成多个唯一有关系的情况是，数据表中动态格式的字段（见上）就可以被修改成固定大小的记录，或者需要频繁的扫描表，但是却不需要读取出大部分的字段。详情请看"15 MySQL Storage Engines and Table Types"。

如果需要频繁的对一个表做基于很多字段信息的统计信息的话，那么可能新建一个表来存储这些实时更新的统计结果会更好。类似下面的更新就会非常快了：

UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;如果只需要表级锁（多个读/一个写），那么采用 MyISAM 存储引擎就非常重要了，例如 MyISAM 和 ISAM 表。这在很多的数据库中也会有不错的性能表现，因为行级锁管理程序在这种情况下也基本上没什么用。

如果需要从很大的日志表中搜集统计信息的话，可以用摘要表来代替扫描整个日志表。维护摘要表比保持 ``实时`` 的统计信息来得更快。当事情发生变化时（比如商业决策），重新建里摘要表比修改运营中的应用程序快多了。

如果可能，最好是分类报告 ``实时`` 还是 ``统计`` 的，报告所需要的数据只需要来自摘要表，摘要表的信息则是周期的从实时数据中产生。

应该认识到一个优点就是字段有默认值。当要插入的值和默认值不一致时才需要明确指定。这就省去了MySQL需要来提高插入速度这步了。

在一些情况下，将数据组装存储在 BLOB 类型字段中更方便。那么在应用程序中就需要增加额外的命令来组装和拆开 BLOB 字段中的值，不过这么做在一些时候就可以节省很多存储开销。这在数据无需遵从 记录-和-字段 格式的表结构是很实用。