[PHP]ThinkPHP PHP MySQL大数据量导入导出

大数据量的导入导出

备注：大数据量指单文本（含Office相关文档docx，xlsx）10W+的导入导出。

MySQL有两种非常常用的表类型MyISAM和InnoDB。网上针对这两种表类型的介绍和比较非常多，其中主要的区别就是InnoDB支持事务和行级锁，但MyISAM不支持。MyISAM相对来讲效率比较高，二者各有优劣。

MyISAM 和 InnoDB的适用场景

MyISAM适合：

做很多count的计算；
插入不频繁，查询频繁；
没有事务。

InnoDB适合：

可靠性要求比较高，或者要求事务；
表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

MyISAM 和 InnoDB的区别

MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
MyISAM表不支持外键。
在执行数据库写入的操作（insert, update, delete）的时候，MyISAM表会锁表，而InnoDB表会锁行。
当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择InnoDB表。当你的数据库主要以查询为主，相比较而言更新和写入比较少，并且业务方面数据完整性要求不那么严格，就选择MyISAM表。因为MyISAM表的查询操作效率和速度都比InnoDB要快。

综上，InnoDB在当前的场景下是不错的选择。

插入大量数据时的优化方法

当PHP需要插入大量数据的时候，当一条一条的插入时，会发现IO耗时严重拖性能。

Demo代码：

1
2
3
4
5
6
7


$params = array('value' => '50');
set_time_limit(0);
echo date("H:i:s");
for($i = 0; $i < 2000000; $i++) {
    $connect_mysql->insert($params);
}
echo date("H:i:s");

有两种方法可以解决这种问题：

1. 使用事务（InnoDB支持）

比如可以每隔10000或者100000条提交一次事务，这样大大降低了IO。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


echo date("H:i:s");
$connect_mysql->query('BEGIN');
$params = array('value' => '50');
for($i = 0; $i < 2000000; $i++) {
    $connect_mysql->insert($params);
    if($i % 100000 == 0) {
        $connect_mysql->query('COMMIT');
        $connect_mysql->query('BEGIN');
    }
}
$connect_mysql->query('COMMIT');
echo date("H:i:s");

2. SQL语句拼接

这是MySQL的一种特性，使用优化SQL语句：将SQL语句进行拼接，使用 insert into table () values (),(),(),() 然后再一次性插入，如果字符串太长，则需要配置下MySQL，在MySQL命令行中运行：

1

SET GLOBAL max_allowed_packet = 2 * 1024 * 1024 * 10;

1
2
3
4
5
6


$sql = "INSERT INTO twenty_million (value) VALUES";
for($i = 0; $i < 2000000; $i++) {
    $sql .= "('50'),";
}
$sql = substr($sql, 0, strlen($sql) - 1);
$connect_mysql->query($sql);

第一种方法较为常用，第二种方法多用于测试数据。

一般默认情况下，PHP的内存为128MB，当使用ThinkPHP超内存的时候，可以调高PHP的默认最大内存，也可以将只需要在入口文件中删除常量APP_DEBUG的定义或者定义为false。这个时候会很省很多内存。当然也可以使用事务模式每隔n条提交一次。当然一次性提交也可以用ThinkPHP的addAll函数，不过该函数Bug较多。

ThinkPHP开启和提交事务

1
2


$table->startTrans();
$table->commit();

当如果需要操作Office相关文件的时候推荐使用PHPWord和PHPExcel。