总结Python多进程操作mysql遇到的坑

【主要遇到的问题】：

1. (2013, 'Lost connection to MySQL server during query')  [WinError 10054] 远程主机强迫关闭了一个现有的连接。

2. 通常每个套接字地址(协议/网络地址/端口)只允许使用一次(10048)

3.已经pip安装了mysql连接池库DBUtils，依然报错ModuleNotFoundError: No module named 'DBUtils'

4.子进程不执行任务的问题

5.子进程传递变量大小超过限制问题，报错_get_more_data 及 assert left > 0 AssertionError

6.SELECT * FROM Table 读取百万级大数据量的表中数据速度慢的问题

        最近一个项目需要操作一张数据量比较大的表，400多万条记录，然后通过一些操作，将操作后生成的新记录写入新表。开始的时候用的单线程跑，发现实在是太慢，操作一条记录+插表平均花费时间0.05秒，400万也要55个小时多，后来开始想到利用多线程插表应该可以提升效率，先试了下，没想到Python的GIL锁导致效率其实提升不了太多。

        后来直接上了多进程，没想到一堆大坑正在等待着我。

        初始写法是，设定好4个进程后，用pandas配合pymysql去读表再将各字段数据存入list中，分割这400万的数据各个字段list给4个进程，让各自进程跑操作函数就行，结果遇到了第一个坑，即问题5，查了下是传递给每个进程当做参数的变量的数据量过大，导致了断言错误，后来查了其他博主的博文，原因及解决方案如下 ‘python-c里有一个bug如果有进程喂的参数（例如data）超过255MB会导致无法准确的合并进程的结果，出现assert left>1之类的报错就要注意了。简单的方式可以增加进程数减少每个进程的数据量，反正CPU进程过多也会自动排队，把cpu用到100%即可。’

        之后改变思路，分割数据后只给每个进程分配一个名字并且仅将与该名字相关的数据作为参数传递，每个进程分配一个名字，在表里找到该名字的所有记录，然后做处理，处理完该进程就释放，当然有了进程池后，这些都由进程池替你管理，还是挺方便的。使用进程池也很简单，你可以一开始就初始化10个100个甚至几万个进程，进程池会保证同时不会有超过processes的进程在运行:

import multiprocessing

def func(test):
    print('This is {0}'.format(test))
    time.sleep(3)

if __name__ == "__main__":
    pool = multiprocessing.Pool(processes = 4)
    test = 'test '
    for i in range(10):
        test += str(i)
        pool.apply_async(func, (test, ))

        好，这里理清楚了，也避开了第一个坑了，遇到第二个坑，Lost connection to MySQL server during query，诶，代码跑着跑着给我把连接给断了？？？查了下，觉得是等待时间过短，mysql会觉得你这里开了端口进行数据通信了，但是迟迟不传输数据或者单次数据量过大，那不好意思，到时间我就直接把连接给你断了。好，改等待时间，调大数据量传输限制，问题1pass。

        我的函数功能比较简单，主要就是一些求交集、余弦相似度等等的运算，然后将一些字段内容去重合并。现在每个进程只处理一个名字，记录最多也不过几百条，处理起来还是很快的。正当我对速度相当满意时，下一个坑又来了：通常每个套接字地址(协议/网络地址/端口)只允许使用一次(10048)，查了半天，全是通信方面的编程遇到的错误，那我这里是怎么遇到的呢？想来想去，连接mysql也是通信啊！于是一番操作，做了两手准备：

        1. 看看端口号怎么设置的，以及进程处理完是怎么释放该端口号的

        2. 不让进程单独连接mysql，共享父进程的连接或者使用连接池试试

        于是就查到了增加本地的端口号数量和修改TcpTimedWaitDelay的时间的办法，问题2解决，后来也尝试使用连接池，但是实话说不好用，可能我看DBUtils的教程看得太快了。

        端口分配和释放有条不紊地进行，本以为万事大吉了！(๑•̀ㅂ•́)و✧邦！下一个坑袭来，怎么子进程不做任务操作1秒都没有然后告诉我跑完了？？害，原来是进程之间变量不共享，而我的子进程里有几个变量是直接用的父进程的，子进程内部报错，也不在控制台提示。如果一定要共享的话，需要BaseManager()/Manager()

        过了这5个坑，这下终于跑起来，一看速度，傻眼了，光读表就真慢啊，一个大表竟然要读900秒，那读完表开始处理那简直慢得跟蜗牛一样，于是开始考虑mysql数据表优化和代码优化。详细思路在下面的办法6里，改完才明白多进程的好，速度真的是飞起，一小时就处理完400万条数据了。

【解决办法】

1. 主要原因是max_allowed_packet设置的太小，以及一些等待时间过短的问题，修改可参考：解决Lost connection to MySQL server during query错误方法_lovemysea的专栏-CSDN博客

pymysql报错OperationalError: (2013, 'Lost connection to MySQL server during query') - 千年寒冰火 - 博客园

2. 我代码的写法是利用线程池保证同时只有最多4个进程在运行，一个进程对某一字段的所有同名记录做操作，比如对Name字段里所有叫李明的分配给一个进程让它做函数里的操作。所以一旦数据量上来了，外加一个进程处理几条几十条记录的速度非常快，导致本地分配端口的数量非常多且分配频繁。尽管已做完处理的端口已经通过conn.close()断开，但是在WIN10系统里TCP默认的端口释放时间TcpTimedWaitDelay是120秒，这将导致120秒内未来得及释放的端口号无法被重新分配，从而产生该错误。解决办法一个是增加本地的端口号数量，二是修改TcpTimedWaitDelay的时间，官方推荐是30秒。还有mysql自身的交互超时等待时间timeout的修改。参考MySQL的wait_timeout 参数 set global 设置不生效

MySQL - wait_timeout与interactive_timeout详解

​​​​​​​Windows time_wait过多解决办法 - asdyzh - 博客园

3. 版本和写法冲突了，可以修改写法或者使用旧的DBUtils版本，主要参考：ModuleNotFoundError: No module named 'DBUtils'

4. 进程和线程不同，进程里变量都是独立的，子进程不能直接用父进程的变量，只能让父进程传递变量给子进程。所以一旦子进程直接调用父进程的变量，就会报错找不着该变量，而如果你的子进程里没有try...except结构，子进程报错也传不到控制台上，控制台只会显示父进程的东西，然后告诉你代码跑完了，另发现直接声明全局变量好像不起作用。如果一定要共享变量，需要BaseManager()/Manager()，参考Python3多进程共享变量实现方法 - 诸子流 - 博客园 python 多进程之间共享变量_六指黑侠i的博客-CSDN博客_python多进程共享变量

5. python-c里有一个bug如果有进程喂的参数（例如data）超过255MB会导致无法准确的合并进程的结果，出现assert left>1之类的报错就要注意了。简单的方式可以增加进程数减少每个进程的数据量，反正CPU进程过多也会自动排队，把cpu用到100%即可。

6. 数据表有两个常用引擎：MyISAM和InnoDB，各有所长，我是需要读旧表插新表的，所以读表时选择速度比较快的MyISAM引擎，插新表用的InnoDB，可以在Navicat里调整。实测读取300万的表使用InnoDB耗时814秒，而换了MyISAM后，仅120秒就读完了。区别和优缺点参考： ​​​​​​MyISAM与InnoDB 的区别（9个不同点）_张花生的博客-CSDN博客_myisam和innodb的区别

mysql中innodb和myisam对比及索引原理区别_杰哥一号号的博客-CSDN博客

有一定概率修改引擎遇到报错情况，参考，注意修改配置的时候将my.ini拉到底下，找到innodb_buffer_pool_size然后修改，不要直接在[mysqld]下面改，没用。mysql 数据库缓存调优之解决The total number of locks exceeds the lock table size错误 - 努力哥 - 博客园

另：尽量不要在list里做查找，百万级数据量做查找的效率实在是太低了，最好可以将list先转为set或者字典，再进行查找，效率立马飞起。如果是查找子串，或者判断一个字符串在不在另一个字符串里，in比find()效率更高。python 的in 和 find 执行效率问题