mysql - 如何加快 MySQL 中插入/更新 1-2mb 文本字段的速度？

Question

我编写了收集网页的机器人，但有些页面大约 1-2mb(通常很大，例如 pantip.com)，所以我现在如何加快速度

如果遇到超过 1mb 的页面，每行更新需要 4-9 秒。

提前致谢

Answer 1

你肯定需要增加max_allowed_pa​​cket并重新启动mysql。类似的东西

[mysqld]
max_allowed_packet=256M

MySQL 的无声 killer 之一是受 max_allowed_packet 控制的 MySQL 数据包。 .

了解 MySQL 数据包是什么可能会澄清这一点。

根据page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7) ，以下是第 1-3 段的解释:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

鉴于此解释，批量插入将相当快地加载/卸载 MySQL 数据包。当 max_allowed_pa​​cket 对于给定的数据负载而言太小时，尤其如此。

结论

在大多数 MySQL 安装中，我通常将其设置为 256M 或 512M。您应该在涉及 BLOB 和 TEXT 字段的数据加载中尝试使用更大的值。