SQL :: iNSERTER用于更快/多行插入
#sql #mysql #perl #dbi

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插入语句(也许使用IGNOREON 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用户提供了一些额外的选择。