Python 进阶 (三十一) 往 MySQL 数据库里添加数据，update 和 insert 效率分析

在编写“Water Spider”过程中，遇到了批量更新数据的情况。自己刚开始时想使用mysql提供的cursor.executemany(operation, seq_of_params)方法执行批量更新操作，但是在写 sql 语句时遇到了问题，不知道如何写了。

后来换种思路，考虑在执行数据插入之前先做数据表删除操作，然后再执行写入操作。因为自己臆想的是“数据插入的效率应该优于更新效率。”因此，决定对于该问题进行实际考察。

下面总结一下网络上针对该问题大家给出的观点。

1. “不考虑主键变动，索引变动，触发器联动的情况下，update比Insert效率高。”

2. 这个很难说，相关因素太多了：存储引擎类型、是否加索引（索引结构如 B+树索引或者哈希索引、索引更新、聚集索引还是非聚集索引）、约束（如唯一性约束、外键约束等）...

还有提下三种插入语句（也有可能影响插入速度，从而难以判断插入快还是更新快）：MySQL 中常用的三种插入数据的语句:

• insert into表示插入数据，数据库会检查主键，如果出现重复会报错；

• replace into表示插入替换数据，需要表中有Primary Key，或者唯一索引，如果表中已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；

• insert ignore 表示，如果表中如果已经存在相同的记录，则忽略当前新数据。

SQL 中插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：连接：（3）发送查询给服务器：（2）分析查询：（2）插入记录：（1x 记录大小）插入索引：（1x 索引）关闭：（1）

如果我们每插入一条都执行一个 SQL 语句，那么我们需要执行除了连接和关闭之外的所有步骤 N 次，这样是非常耗时的，优化的方式有以下几种：

• 在每个insert语句中写入多行，批量插入；

• 将所有查询语句写入事务中；

• 利用Load Data导入数据；

每种方式执行的性能如下。

InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)以及外键约束(FOREIGN KEY constraints)。

InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

对于这种大数据量的更新情况，可以采用多线程的方式，每个线程更新 100 条数据，这样就能提高更新的速度了。

当然这里的 100 只是一个猜想值，哪个值合适，需要你测试才能得出。我想的话，应该能比单线要快些，至于能不能优化到 2s，这个就不清楚了。

同时需要注意你的连接池大小、线程池大小（核心线程数）。这些资源也会影响到你的更新速度（即这些资源不能出现竞争） 。

最后，我对你需要更新这么大的数据量操作有一些疑问：

这个操作是 online 的吗？ 这个操作的返回是实时的吗？

对于大数据量的更新一般会做成一个异步的操作，而不需要是实时的。