MySQL 大量 insert 操作的效率优化

最近在重写 szucal 的爬虫,之前的爬虫是用 MFC 写的,虽然速度不错,但是写起来很麻烦,不便于交给他人维护,这次看中 PHP 的函数封装得好,很多东西直接拿来就可以用,于是就选了 PHP 来写,初步写完之后,尝试着跑了一下,发现效率惨不忍睹,把 PHP 脚本的时间 set_time_limit(10) 跑了一下得到下面的结果

mysql> show table status\G
*************************** 1. row ***************************
           Name: course
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 27262976
 Auto_increment: 4
    Create_time: 2013-01-14 22:33:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: course_time_location
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 27262976
 Auto_increment: 7
    Create_time: 2013-01-14 22:33:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 3. row ***************************
           Name: log
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 8
 Avg_row_length: 2048
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 27262976
 Auto_increment: 9
    Create_time: 2013-01-14 22:33:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 4. row ***************************
           Name: stu
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 85
 Avg_row_length: 192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 27262976
 Auto_increment: 86
    Create_time: 2013-01-14 22:33:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 5. row ***************************
           Name: stu_course
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 86
 Avg_row_length: 190
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 27262976
 Auto_increment: 87
    Create_time: 2013-01-14 22:33:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
5 rows in set (0.00 sec)

mysql>

可以看到 10 秒钟下来,才处理了 3 页课程名单表,插入了 80 多个学生数据,以及 80 条选课关系,这么估算下来,深大一个学期大约有 4000 门课程,一门课程 3 秒的话,就是 3 个多小时,这个效率肯定是无法接受的,因为我原来的 MFC 爬虫爬完全部数据也才只需要 5 到 10 分钟。

出现了问题,于是就开始想是网络拉取数据那里慢了,还是数据库写入数据那里慢了,简单实验了一下,把封装的数据库类的写入函数注释掉,关掉了写入数据的部分,再跑了一下,发现下载全部数据并解析好只需要 8 分多钟,那么明显就是在 insert 数据的时候效率低下了。

搜了一下,看到这里,http://fred.oracle1.com/weblog…

执行 INSERT 操作的时候,可以考虑使用以下的方式优化 SQL 的执行效率:

如果你同时从同一客户插入很多行,使用多个值表的 INSERT 语句。这比使用分开 INSERT 语句快。
Insert into test values(1,2),(1,3),(1,4) …
如果你从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。 Delayed的含义是让 INSERT 语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘;这比每条语句分别插入要快的多; LOW_PRIORITY 刚好相反,在所有其他用户对表的读写完后才进行插入;
将索引文件和数据文件分在不同的磁盘上存放;
如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是,这只能对 MyISAM 表使用;
当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍;
根据应用情况使用 REPLACE 语句代替 INSERT ;
根据应用情况使用 IGNORE 关键字忽略重复记录。

有提到在一行当中并着写入多个 values 的,这个其实我之前的 MFC 爬虫就是这样的,因为 MFC 连接 MySQL 不方便,所以当时我的做法就是根据 MySQL 的导入导出文件的格式,在爬取数据的时候,自己生成一份格式相仿的导入文件,等到爬虫跑完了,再把那份文件导入到数据库中去,但是在这次的 PHP 中,如果要这么做,其实是不方便的,再看到后面有说使用 delayed 的,于是试了一下,查到这里,http://www.uml.org.cn/sjjm/201…

一、DELAYED 的使用

使用延迟插入操作DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候,服务器把数据行放入一个队列中,并立即给客户端返回一个状态信息,这样客户端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据表中读取数据,队列中的数据就会被保持着,直到没有读取者为止。

接着服务器开始插入延迟数据行(delayed-row)队列中的数据行。在插入操作的同时,服务器还要检查是否有新的读取请求到达和等待。如果有,延迟数据行队列就被挂起,允许读取者继续操作。当没有读取者的时候,服务器再次开始插入延迟的数据行。这个过程一直进行,直到队列空了为止。

几点要注意事项:

INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED…SELECT语句的DELAYED。服务器忽略用于INSERT DELAYED…ON DUPLICATE UPDATE语句的DELAYED。

因为在行被插入前,语句立刻返回,所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。

对于SELECT语句,DELAYED行不可见,直到这些行确实被插入了为止。

DELAYED在从属复制服务器中被忽略了,因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。注意,目前在队列中的各行只保存在存储器中,直到它们被插入到表中为止。这意味着,如果您强行中止了mysqld(例如,使用kill -9)或者如果mysqld意外停止,则所有没有被写入磁盘的行都会丢失。

另外由于 delayed 用法只能在 MyISAM 中支持,所以先把数据引擎从 InnoDB 换成 MyISAM,换过来之后,先跑一个 10 秒的测试

mysql> show table status\G
*************************** 1. row ***************************
           Name: course
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 104
 Avg_row_length: 148
    Data_length: 15404
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: 105
    Create_time: 2013-01-15 11:53:59
    Update_time: 2013-01-15 11:54:53
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: course_time_location
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 155
 Avg_row_length: 43
    Data_length: 6756
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: 156
    Create_time: 2013-01-15 11:53:59
    Update_time: 2013-01-15 11:54:53
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 3. row ***************************
           Name: log
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 114
 Avg_row_length: 137
    Data_length: 15680
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: 115
    Create_time: 2013-01-15 11:53:59
    Update_time: 2013-01-15 11:54:53
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 4. row ***************************
           Name: stu
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1901
 Avg_row_length: 51
    Data_length: 97468
Max_data_length: 281474976710655
   Index_length: 21504
      Data_free: 0
 Auto_increment: 1902
    Create_time: 2013-01-15 11:53:59
    Update_time: 2013-01-15 11:54:53
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 5. row ***************************
           Name: stu_course
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 5499
 Avg_row_length: 31
    Data_length: 170520
Max_data_length: 281474976710655
   Index_length: 57344
      Data_free: 0
 Auto_increment: 5500
    Create_time: 2013-01-15 11:53:59
    Update_time: 2013-01-15 11:54:53
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
5 rows in set (0.00 sec)

mysql>

惊讶的看到,只是简单的换了一个引擎,没有改动代码,就可以得到如此之高的效率提升,10 秒钟处理的课程量达到了 104 门,写入了 1901 个学生的信息和 5499 条选课关系,这么算的话,4000 门课程只需要 400 秒左右,下面试一下使用 delayed,注意到代码中有一个地方是写入学生信息数据的,在写入一个学生之前,要先 select 一下,但是使用了 delayed 之后,就会导致 select 的结果不准确,于是需要使用 ignore,看这里,http://www.uml.org.cn/sjjm/201…

二、IGNORE的使用

IGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字,或者当STRICT模式启动后出现警告,则使用IGNORE控制ALTER TABLE的运行。

如果没有指定IGNORE,当重复关键字错误发生时,复制操作被放弃,返回前一步骤。

如果指定了IGNORE,则对于有重复关键字的行,只使用第一行,其它有冲突的行被删除。并且,对错误值进行修正,使之尽量接近正确值。insert ignore into tb(…) value(…)这样不用校验是否存在了,有则忽略,无则添加。

原来的代码是这样的

// 写入学生表之前先检查是否存在			
$query = "select * from $t_stu where stu_id = ?";
$dbr = $this->db->query($query, $stu_id);
if (count($dbr) == 0) {
	// 不存在该学生的信息
	$query = "insert delayed into $t_stu (stu_id, stu_name, sex, class) values (?, ?, ?, ?)";
	$this->db->query($query, array($stu_id, $stu_name, $sex, $class));
}

修改之后代码变成这样

// 写入学生表	
$query = "insert delayed ignore into $t_stu (stu_id, stu_name, sex, class) values (?, ?, ?, ?)";
$this->db->query($query, array($stu_id, $stu_name, $sex, $class));

跑一个 10 秒测试的结果如下

mysql> show table status\G
*************************** 1. row ***************************
           Name: course
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 171
 Avg_row_length: 150
    Data_length: 25812
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: 172
    Create_time: 2013-01-15 12:46:53
    Update_time: 2013-01-15 12:47:11
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: course_time_location
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 224
 Avg_row_length: 42
    Data_length: 9456
Max_data_length: 281474976710655
   Index_length: 5120
      Data_free: 0
 Auto_increment: 225
    Create_time: 2013-01-15 12:46:53
    Update_time: 2013-01-15 12:47:11
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 3. row ***************************
           Name: log
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 181
 Avg_row_length: 139
    Data_length: 25292
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: 182
    Create_time: 2013-01-15 12:46:53
    Update_time: 2013-01-15 12:47:01
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 4. row ***************************
           Name: stu
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 9259
 Avg_row_length: 50
    Data_length: 465700
Max_data_length: 281474976710655
   Index_length: 97280
      Data_free: 0
 Auto_increment: 9260
    Create_time: 2013-01-15 12:46:53
    Update_time: 2013-01-15 12:47:11
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 5. row ***************************
           Name: stu_course
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 9259
 Avg_row_length: 31
    Data_length: 287164
Max_data_length: 281474976710655
   Index_length: 97280
      Data_free: 0
 Auto_increment: 9260
    Create_time: 2013-01-15 12:46:53
    Update_time: 2013-01-15 12:47:11
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
5 rows in set (0.00 sec)

mysql>

可以看到速度依然能够有所提升,10 秒钟写入了 170 门课和 9200 多个学生信息以及 9200 多条选课记录,不过这里可以注意到学生信息的条数应该是出了问题的,核查数据也确实可以发现学生信息发生了重复,那么试试对 stu_id 建一个索引

ALTER TABLE  `stu` ADD UNIQUE (`stu_id`)

再跑一个测试

mysql> show table status\G
*************************** 1. row ***************************
           Name: course
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 160
 Avg_row_length: 148
    Data_length: 23752
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: 161
    Create_time: 2013-01-15 13:22:06
    Update_time: 2013-01-15 13:22:59
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: course_time_location
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 213
 Avg_row_length: 42
    Data_length: 9060
Max_data_length: 281474976710655
   Index_length: 5120
      Data_free: 0
 Auto_increment: 214
    Create_time: 2013-01-15 13:22:06
    Update_time: 2013-01-15 13:22:59
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 3. row ***************************
           Name: log
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 170
 Avg_row_length: 139
    Data_length: 23684
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: 171
    Create_time: 2013-01-15 13:22:06
    Update_time: 2013-01-15 13:22:59
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 4. row ***************************
           Name: stu
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 2620
 Avg_row_length: 50
    Data_length: 132944
Max_data_length: 281474976710655
   Index_length: 82944
      Data_free: 0
 Auto_increment: 2621
    Create_time: 2013-01-15 13:22:14
    Update_time: 2013-01-15 13:22:59
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 5. row ***************************
           Name: stu_course
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 8740
 Avg_row_length: 31
    Data_length: 271792
Max_data_length: 281474976710655
   Index_length: 92160
      Data_free: 0
 Auto_increment: 8741
    Create_time: 2013-01-15 13:22:06
    Update_time: 2013-01-15 13:22:59
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
5 rows in set (0.00 sec)

mysql>

效率稍有下降,160 多门课程名单,2600 多个学生信息以及 8700 多条选课关系,不过这样起码保证了学生信息的唯一性。

最后附上一些官方文档,INSERT语句的速度,http://dev.mysql.com/doc/refma…,INSERT Syntax,http://dev.mysql.com/doc/refma…,INSERT DELAYED Syntax,http://dev.mysql.com/doc/refma…,另外,还有一个据说效率更高的 load data infile 用法,但是这个是适用于从文件中读入数据的,LOAD DATA INFILE语法,http://dev.mysql.com/doc/refma…

Leave a Reply

Your email address will not be published. Required fields are marked *