MySQL-5.7-8.2.4.1 Optimizing INSERT Statements

最新推荐文章于 2024-10-12 23:09:38 发布

KING BOMB

最新推荐文章于 2024-10-12 23:09:38 发布

阅读量86

点赞数

分类专栏： Mysql 文章标签： mysql

原文链接：https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html

版权

Mysql 专栏收录该内容

142 篇文章 2 订阅

订阅专栏

To optimize insert speed, combine many small operations into a single large operation.

为了优化插入速度，可以将许多小操作合并为单个大操作。

Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

理想情况下，您可以进行单个连接，一次发送多个新行的数据，并将所有索引更新和一致性检查延迟到最后。

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

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

This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.

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

The size of the table slows down the insertion of indexes by log N, assuming B-tree indexes.

假设索引是b树索引，那么表的大小会使索引插入的速度降低log N。

You can use the following methods to speed up inserts:

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

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time.

如果同时从同一客户端插入许多行，请使用带有多个VALUES列表的INSERT语句一次插入几行。

This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.

这比使用单独的单行INSERT语句快得多(在某些情况下快很多倍)。

If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster.

如果要向非空表添加数据，可以调优bulk_insert_buffer_size变量，使数据插入更快。

See Section 5.1.7, “Server System Variables”.

When loading a table from a text file, use LOAD DATA. This is usually 20 times faster than using INSERT statements.

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

See Section 13.2.6, “LOAD DATA Statement”.

Take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL must do and improves the insert speed.

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