SQL::Inserter是基于我们在SpareRoom上使用的内容来容易管理缓冲插入物的新CPAN模块,并替换了SQL::Abstract的slow insert 方法。方法。
。插入器OO接口
这个想法是,如果您想插入许多行数据(例如ETL管道的一部分或编写日志等),则您希望使用多行插入语句来执行此操作。使用 sql :: inserster 您创建一个非常轻巧的对象来处理插入,只需将其传递给DBI db句柄,表格和您将要插入的列(如果您使用哈希(如果您使用哈希),则插入,请参见下一节):
use SQL::Inserter;
my $inserter = SQL::Inserter->new(
dbh => $dbh,
table => 'tablename',
cols => [qw/col1 col2.../],
);
默认情况下,插入器具有100行缓冲区(可以随意增加“小”行),只要缓冲区满足。
您有2个用于插入的选项,最快的方法是用 flat array 包含构造函数(cols
)的顺序中的行值 - 它们将用作bind变量。您也可以通过大小N x number_of_cols
的数组来传递多行。即使他们无法安装缓冲区,您也可以将所有行传递到单个呼叫中 - 它们将被插入细分市场。
第二种方法是 hash ,它允许您将引用传递到SQL语句而不是简单的绑定值,而是一次一行:
# Fastest: pass single or multiple rows of data as an array
$sql->insert($col1_val1, $col2_val1, $col1_val2...);
# Alt: pass a single row as a hash, allows SQL code passed as
$sql->insert({
column1 => $data1,
column2 => \'NOW()',
...
});
# Force flush the buffer at any time with no argument on insert
$sql->insert();
只需根据需要调用insert
,只要缓冲区填充缓冲区填充,插入物就会发生,并且当插入器对象被摧毁/范围内时,缓冲区将自动冲洗。如果您愿意,您总是可以通过致电$inserter->insert()
手动强制冲洗。
SQL构建功能
在过去的I have complained上,关于sql ::摘要的缓慢(创建SQL字符串所花费的时间可以比执行本身更重要!)。 SQL::Maker是一种快速的选择,但是过去,我们过去的开发人员似乎偏爱 sql :: Abstract 语法,因此我们没有用自己的快速函数在内部替换它,这些功能采用了类似的语法。 sql :: inserster 包括我们用于插入的功能:
# Similar to SQL::Abstract's insert, but with much less overhead:
my ($sql, @bind) = simple_insert($table, {col1=>$val...});
# Multi-row possible:
my ($sql, @bind) = simple_insert(
$table,
[{col1=>$val1...},{col1=>$val2...},...]
);
由于我们使用mySQL,它也提供了INSERT IGNORE
甚至ON DUPLICATE KEY UPDATE
变体的便利性:
my ($sql, @bind) = simple_insert(
$table, {col1=>$val1,col1=>$val2}, {duplicates => 'update'}
);
## INSERT INTO table_name (col1,col2)
## VALUES (?,?),(?,?)
## ON DUPLICATE KEY UPDATE col1=VALUES(col1),col2=VALUES(col2)
最后,如果您只想与占位符的SQL插入语句(也许使用IGNORE
或ON DUPLICATE KEY
变体)来执行您自己的插入,则有一个更简单的功能:
my $sql = multi_insert_sql(
'table', [qw/col1 col2.../], $num_of_rows
);
表现
多行与单行性能优势可能是巨大的(我们从单个500k邮件中切换到多行的时,我们节省了二十分钟的处理时间),但是量化非常特定的应用程序。作为指示,我可以为您提供在我们的测试环境中使用100和1000行缓冲区(GCP VM连接到Cloud SQL),将100K小行(3列)插入100和1000行缓冲区所需的时间:
Single row insert: 87.1s
100-row insert: 1.36s
1000-row insert: 0.62s
在这里加速 100x 。您会得到这个想法 - 单行插入包括大部分延迟的网络往返时间,您可以使用多排插入物避免使用。根据行的大小,您一次可以插入多少行是有限制的。
该过程的SQL构建部分的性能更容易量化。 sql :: inserster 正如您期望的那样,构建SQL的时间很少。模块中包含一个基准脚本,可以比较给我的功能的速度(在M1 Pro上):
Compare SQL::Abstract, SQL::Maker, simple_insert:
Rate Abstract Abstract cached Maker Maker cached simple_insert
Abstract 4207/s -- -6% -90% -91% -98%
Abstract cached 4482/s 7% -- -90% -90% -98%
Maker 44245/s 952% 887% -- -4% -76%
Maker cached 46205/s 998% 931% 4% -- -75%
simple_insert 187398/s 4355% 4081% 324% 306% --
Compare simple_insert, multi_insert_sql for single row:
Rate simple_insert multi_insert_sql
simple_insert 190037/s -- -76%
multi_insert_sql 797596/s 320% --
sql :: inserster 's simple_insert 比 sql ::摘要快40倍,比 sql快3倍。 :Maker 。后者根本不慢,因此您不必出于绩效原因将其交换,但是 simple_insert 为mysql/mariadb用户提供了一些额外的选择。