PHP+MySQL百万级数据插入的优化操作技巧
2019-04-02
插入分析
MySQL中插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：
连接：（3）
发送查询给服务器：（2）
分析查询：（2）
插入记录：（1x记录大小）
插入索引：（1x索引）
关闭：（1）
如果我们每插入一条都执行一个SQL语句，那么我们需要执行除了连接和关闭之外的所有步骤N次，这样是非常耗时的，优化的方式有一下几种：
在每个insert语句中写入多行，批量插入
将所有查询语句写入事务中
利用Load Data导入数据
每种方式执行的性能如下。
Innodb引擎
InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)以及外键约束(FOREIGN KEY constraints)。
InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
测试环境
Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16
总数100W条数据
插入完后数据库大小38.6MB（无索引），46.8（有索引）
无索引单条插入 总耗时：229s 峰值内存：246KB
有索引单条插入 总耗时：242s 峰值内存：246KB
无索引批量插入 总耗时：10s 峰值内存：8643KB
有索引批量插入 总耗时：16s 峰值内存：8643KB
无索引事务插入 总耗时：78s 峰值内存：246KB
有索引事务插入 总耗时：82s 峰值内存：246KB
无索引Load Data插入 总耗时：12s 峰值内存：246KB
有索引Load Data插入 总耗时：11s 峰值内存：246KB
MyIASM引擎
MyISAM 是MySQL缺省存贮引擎。设计简单，支持全文搜索。
测试环境
Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16
总数100W条数据
插入完后数据库大小19.1MB（无索引），38.6（有索引）
无索引单条插入 总耗时：82s 峰值内存：246KB
有索引单条插入 总耗时：86s 峰值内存：246KB
无索引批量插入 总耗时：3s 峰值内存：8643KB
有索引批量插入 总耗时：7s 峰值内存：8643KB
无索引Load Data插入 总耗时：6s 峰值内存：246KB
有索引Load Data插入 总耗时：8s 峰值内存：246KB
总结
我测试的数据量不是很大，不过可以大概了解这几种插入方式对于速度的影响，最快的必然是Load Data方式。这种方式相对比较麻烦，因为涉及到了写文件，但是可以兼顾内存和速度。
二、PHP+MySQL百万数据插入
第一种方法：使用insert into 插入，代码如下：
最后显示为：23:25:05 01:32:05 也就是花了2个小时多! 这是时间撸几把 都行。。。
第二种方法：使用事务提交，批量插入数据库(每隔10W条提交下)
最后显示消耗的时间为：22:56:13 23:04:00 ，一共8分13秒 ，代码如下：
时间是一下缩短不少但还是有点长。
第三种方法：使用优化SQL语句：将SQL语句进行拼接，使用 insert into table () values (),(),(),()然后再一次性插入，如果字符串太长，
则需要配置下MYSQL，在mysql 命令行中运行 ：set global max_allowed_packet = 2*1024*1024*10;消耗时间为：11:24:06 11:24:11;
我擦 插入200W条测试数据仅仅用了不到6秒钟!代码如下：
可能这里又会有人说数据不大 sql文件导出来也是小200M 5秒钟 对一表格 进行200万条数据 速度已经不错了。
这个再跟大家说一个 TP框架了的 addall方法
速度也很快 但是还不如原生。
着了说一下其中可能会遇到的问题 比如试验时可能会出现PHP内存限制 和mysql的SQL语句溢出甚至宕机
这里数几个方案
1、可以进行相关的配置php.ini my.ini 等
2、大家可以用队列进行分批处理
3、或者自己的一些方法
