最近在重写 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…