无水印做海报的网站,广州开发网站服务,php网站开发需求分析,wordpress posted on当你需要在 MySQL 数据库中批量插入数百万条数据时#xff0c;你就会意识到#xff0c;逐条发送 INSERT 语句并不是一个可行的方法。MySQL 文档中有些值得一读的 INSERT 优化技巧。在这篇文章里#xff0c;我将概述高效加载数据到 MySQL 数据库的两大技术。LOAD DATA INFILE…当你需要在 MySQL 数据库中批量插入数百万条数据时你就会意识到逐条发送 INSERT 语句并不是一个可行的方法。MySQL 文档中有些值得一读的 INSERT 优化技巧。在这篇文章里我将概述高效加载数据到 MySQL 数据库的两大技术。LOAD DATA INFILE如果你正在寻找提高原始性能的方案这无疑是你的首选方案。LOAD DATA INFILE 是一个专门为 MySQL 高度优化的语句它直接将数据从 CSV / TSV 文件插入到表中。有两种方法可以使用 LOAD DATA INFILE。你可以把数据文件拷贝到服务端数据目录通常 /var/lib/mysql-files/并且运行LOAD DATA INFILE /path/to/products.csv INTO TABLE products;这个方法相当麻烦因为你需要访问服务器的文件系统为数据文件设置合适的权限等。好消息是你也能将数据文件存储在客户端并且使用 LOCAL 关键词LOAD DATA LOCAL INFILE /path/to/products.csv INTO TABLE products;在这种情况下从客户端文件系统中读取文件将其透明地拷贝到服务端临时目录然后从该目录导入。总而言之这几乎与直接从服务器文件系统加载文件一样快不过你需要确保服务器启用了此 选项。LOAD DATA INFILE 有很多可选项主要与数据文件的结构有关字段分隔符、附件等。请浏览 文档 以查看全部内容。虽然从性能角度考虑 LOAD DATA INFILE 是最佳选项但是这种方式需要你先将数据以逗号分隔的形式导出到文本文件中。如果你没有这样的文件你就需要花费额外的资源来创建它们并且可能会在一定程度上增加应用程序的复杂性。幸运的是还有一种另外的选择。扩展的插入语句Extended inserts一个典型的 INSERT SQL 语句是这样的INSERT INTO user (id, name) VALUES (1, Ben);extended INSERT 将多条插入记录聚合到一个查询语句中INSERT INTO user (id, name) VALUES (1, Ben), (2, Bob);关键在于找到每条语句中要插入的记录的最佳数量。没有一个放之四海而皆准的数字因此你需要对数据样本做基准测试以找到性能收益的最大值或者在内存使用和性能方面找到最佳折衷。为了充分利用 extended insert我们还建议使用预处理语句在事务中运行该语句基准测试我要插入 120 万条记录每条记录由 6 个 混合类型数据组成平均每条数据约 26 个字节大小。我使用了两种常见的配置进行测试客户端和服务端在同一机器上通过 UNIX 套接字进行通信客户端和服务端在不同的机器上通过延迟非常低小于 0.1 毫秒的千兆网络进行通信作为比较的基础我使用 INSERT ... SELECT 复制了该表这个操作的性能表现为每秒插入 313,000 条数据。LOAD DATA INFILE令我吃惊的是测试结果证明 LOAD DATA INFILE 比拷贝表更快LOAD DATA INFILE每秒 377,000 次插入LOAD DATA LOCAL INFILE 通过网络每秒 322,000 次插入这两个数字的差异似乎与从客户端到服务端传输数据的耗时有直接的关系数据文件的大小为 53 MB两个基准测试的时间差了 543 ms这表示传输速度为 780 mbps接近千兆速度。这意味着很有可能在完全传输文件之前MySQL 服务器并没有开始处理该文件因此插入的速度与客户端和服务端之间的带宽直接相关如果它们不在同一台机器上考虑这一点则非常重要。Extended inserts我使用 BulkInserter 来测试插入的速度BulkInserter 是我编写的 开源库 PHP 类的一部分每个查询最多插入 10,000 条记录正如我们所看到的随着每条查询插入数的增长插入速度也会迅速提高。与逐条插入速度相比我们在本地主机上性能提升了 6 倍在网络主机上性能提升了 17 倍在本地主机上每秒插入数量从 40,000 提升至 247,000在网络主机上每秒插入数量从 1,2000 提升至 201,000这两种情况都需要每个查询大约 1,000 个插入来达到最大吞吐量。但是每条查询 40 个插入就足以在本地主机上达到 90% 的吞吐量这可能是一个很好的折衷。还需要注意的是达到峰值之后随着每个查询插入数量的增加性能实际上是会下降。extended insert 的优势在网络连接的情况下更加明显因为连续插入的速度取决于你的网络延迟。max sequential inserts per second ~ 1000 / ping in milliseconds客户端和服务端之间的延迟越高你从 extended insert 中获益越多。结论不出所料LOAD DATA INFILE 是在单个连接上提升性能的首选方案。它要求你准备格式正确的文件如果你必须先生成这个文件并/或将其传输到数据库服务器那么在测试插入速度时一定要把这个过程的时间消耗考虑进去。另一方面extended insert 不需要临时的文本文件并且可以达到相当于 LOAD DATA INFILE 65% 的吞吐量这是非常合理的插入速度。有意思的是无论是基于网络还是本地主机聚集多条插入到单个查询总是能得到更好的性能。如果你决定开始使用 extended insert一定要先用生产环境的数据样本和一些不同的插入数来测试你的环境以找出最佳的数值。。在增加单个查询的插入数的时候要小心因此它可能需要在客户端分配更多的内存增加 MySQL 服务器的 max_allowed_packet 参数配置。最后值得一提的是根据 Percona 的说法你可以使用并发连接、分区以及多个缓冲池以获得更好的性能。更多信息请查看 他们博客的这篇文章。基准测试运行在装有 Centos 7 和 MySQL 5.7 的裸服务器上它的主要硬件配置有 Xeon E3 3.8 GHz 处理器32 GB RAM 和 NVMe SSD。MySQL 的基准表使用 InnoBD 存储引擎。基准测试的源代码保存在 gist 上结果图保存在 plot.ly 上。原文地址High-speed inserts with MySQL原文作者Benjamin Morel译文出自掘金翻译计划译者司徒公子校对者GJXAIOU、QinRoc
