数据库 AR create 数组形成的 sql 是多条的?

as181920 · April 22, 2013 · Last by ywjno replied at April 22, 2013 · 2892 hits

同时插入多条数据,sql 中 insert 语句可以跟多条数据; ActiveRecord 的 create 也可以跟数组,例如

Xxx.create [{fielda: "x"},{fielda: "xx"}]

这样看 log 是分开两条 insert,每次插入一条数据,这样性能和 sql 的一次插入多条一样么?

select * from Xxx where id in (1,2,3,4,5) ; 和 select * from Xxx where id = 1; select * from Xxx where id = 2; select * from Xxx where id = 3; select * from Xxx where id = 4; select * from Xxx where id = 5; 应该有区别吧。

#1 楼 @zgm select 是 ok 的,我说 create

应该上 transaction 来包住这种一次插入多条记录的操作

好吧,既然 lz 不想做实验,那我帮你做。

你说的两条记录我觉得应该没有多少差别,现在我插入 100 条记录,首先是一次在一个 sql 中执行,我用了 activerecord-import 这个 gem。

 Class Create Many Without Validations Or Callbacks (1.1ms)  INSERT INTO `dogs` (`id`,`name`) VALUES (NULL,'dog0'),(NULL,'dog1'),(NULL,'dog2'),(NULL,'dog3'),(NULL,'dog4'),(NULL,'dog5'),(NULL,'dog6'),(NULL,'dog7'),(NULL,'dog8'),(NULL,'dog9'),(NULL,'dog10'),(NULL,'dog11'),(NULL,'dog12'),(NULL,'dog13'),(NULL,'dog14'),(NULL,'dog15'),(NULL,'dog16'),(NULL,'dog17'),(NULL,'dog18'),(NULL,'dog19'),(NULL,'dog20'),(NULL,'dog21'),(NULL,'dog22'),(NULL,'dog23'),(NULL,'dog24'),(NULL,'dog25'),(NULL,'dog26'),(NULL,'dog27'),(NULL,'dog28'),(NULL,'dog29'),(NULL,'dog30'),(NULL,'dog31'),(NULL,'dog32'),(NULL,'dog33'),(NULL,'dog34'),(NULL,'dog35'),(NULL,'dog36'),(NULL,'dog37'),(NULL,'dog38'),(NULL,'dog39'),(NULL,'dog40'),(NULL,'dog41'),(NULL,'dog42'),(NULL,'dog43'),(NULL,'dog44'),(NULL,'dog45'),(NULL,'dog46'),(NULL,'dog47'),(NULL,'dog48'),(NULL,'dog49'),(NULL,'dog50'),(NULL,'dog51'),(NULL,'dog52'),(NULL,'dog53'),(NULL,'dog54'),(NULL,'dog55'),(NULL,'dog56'),(NULL,'dog57'),(NULL,'dog58'),(NULL,'dog59'),(NULL,'dog60'),(NULL,'dog61'),(NULL,'dog62'),(NULL,'dog63'),(NULL,'dog64'),(NULL,'dog65'),(NULL,'dog66'),(NULL,'dog67'),(NULL,'dog68'),(NULL,'dog69'),(NULL,'dog70'),(NULL,'dog71'),(NULL,'dog72'),(NULL,'dog73'),(NULL,'dog74'),(NULL,'dog75'),(NULL,'dog76'),(NULL,'dog77'),(NULL,'dog78'),(NULL,'dog79'),(NULL,'dog80'),(NULL,'dog81'),(NULL,'dog82'),(NULL,'dog83'),(NULL,'dog84'),(NULL,'dog85'),(NULL,'dog86'),(NULL,'dog87'),(NULL,'dog88'),(NULL,'dog89'),(NULL,'dog90'),(NULL,'dog91'),(NULL,'dog92'),(NULL,'dog93'),(NULL,'dog94'),(NULL,'dog95'),(NULL,'dog96'),(NULL,'dog97'),(NULL,'dog98'),(NULL,'dog99')
=> #<struct ActiveRecord::Import::Result failed_instances=[], num_inserts=1>

大概 1.1ms,但是这种方式不执行回调和验证。

第二种方式,一条一条插入,,每执行一条产生下面的 log:

(0.2ms)  SAVEPOINT active_record_1
SQL (0.3ms)  INSERT INTO `dogs` (`name`) VALUES ('dogs0')
(0.2ms)  RELEASE SAVEPOINT active_record_1

一条大概是 0.7ms,即使不算上 savepoint,一条也要 0.3ms, 那么 100 条是 30ms.

粗略的比较一下,就可以很明显的看出差别。

#3 楼 @ywjno transaction 不是解决我这个效率问题的吧。不过提醒我了,这里可能有这方面的问题

用事务的话只会产生一次 commit 自然比每次 commit 要来得快

当然,这是以前的概念,不排除现在数据库进化之快都可以不需要考虑这种情况了

You need to Sign in before reply, if you don't have an account, please Sign up first.