这篇文章是对”批量插入效率如何“这个常见问题的回答.实际上这个常见问题是非常具有歧义性的.
首先我们都知道插入的意思是将数据写入到数据库中,而数据库的唯一可使用的语言是sql,写入数据属于sql中的dml语句.
当然,大多数时候编程中是不会直接使用sql语句来进行编程的,以java为例,通常不会使用jdbc的方式来操作数据库,而是使用如mybatis、hibernate、spring data jpa等ORM框架来将对象操作转换为sql来间接地操作数据库.
正是由于这一层转换使得批量插入这个词语产生了歧义.
ORM框架层面的批量插入
批量插入的第一种意思是多条insert语句插入.
DML中的插入数据在SQL92规范如下:
INSERT 是将数据插入到数据库对象中的指令，可以插入数据的数据库对象有数据表以及可更新查看表两种。 基本格式有：
INSERT INTO [資料表或可更新檢視表物件名稱] (欄位1, 欄位2, 欄位3, ...) VALUES (值1, 值2, 值3, ...) -- 只需要根據指定的欄位來設定插入值。 INSERT INTO [資料表或可更新檢視表物件名稱] VALUES (值1, 值2, 值3, ...) -- 必須要指定所有欄位的插入值。例如：
INSERT INTO myTable (col1, col2, col3, col4, col5) VALUES (1, 2, 3, '4', '5') INSERT INTO myTable VALUES (1, 2, 3, '4', '5')
以mybatis为例,我们的代码通常是如下写法:
mapper.insert(entity);
最终这样一行代码会转换出一句上面的INSERT INTO语句,来操作数据库.
而如果封装一个如下方法:
@Transactional(rollbackFor = Exception.class)
@Override
public void insert(Collection<T> entities) {
for (T entity : entities) {
insert(entity);
}
}
就可以达成”批量插入数据”的目的,可以称之为批量插入.
然而类似这样的”批量插入”的批量实际上是ORM框架层面上的批量,方法多种多样,实际上这样的ORM操作转换为sql后实际上是生成了集合数量的INSERT INTO语句,然后发送了多条INSERT INTO语句发送给数据库执行.
数据库每次收到的都是SQL92规范中的单条insert语句,在数据库层面上其实并没有所谓的批量.
从性能来上来,在数据库层面这样的”批量插入”中的每一条数据的插入效率当然是和普通的单体插入效率完全一致的.批量插入的整体效率大致上等同于普通的单体插入效率乘以插入的数据条数.
当然,实际上将所有的insert语句作为一个事务还是每句insert语句单独一个事务会极大的影响效率,并且在通讯中一次性发送了所有sql语句肯定比多次通讯发送语句要效率高.ORM框架对于以上两个因素的处理还是会极大地影响最终的总性能.
数据库层面的批量插入
批量插入的第二种意思是单条insert语句插入多条数据. 而这就需要数据库支持才能做到.
上面我们提到了SQL92规范中的insert语句,是只能插入单条数据的. 而至今也没有更新的SQL规范规定了能插入多条数据的insert语句标准.
所以并不一定每个数据库都支持单条insert语句插入多条数据的操作,即使支持,每个数据库的写法也都是不同的.
以mysql数据库为例,写法如下:
insert intoTable (col1,col2...) values (val11,val12...),(val11,val12...),...;
对于这种数据库层面的批量插入,达成方法只有一种,只能按你使用的数据库的规定来做.
至于效率方面,数据库层面的批量插入效率是远高于ORM框架层面的批量插入的,原因在于平均每条数据的数据库层面写入效率极高.
当然,以上两种”批量插入”的达成方式并不是泾渭分明,代码层面是没有任何限制的,只要想要,完全可以做到两种混用.
