python操作MySQL的几点经验之谈

不经意间实习了一个多月了，也渐渐习惯了工作的节奏。前段时间老大给了个任务，没想到做了20多天了才算做完，自觉得工作的效率还是太低了。做完这个任务后，觉得有很多值得记录的东西，会整理成几篇博客，供大家分享。

这个任务只不过是做一个web平台上的一些数据查询工作，本来是一件很简单的活，事实上接到任务后三四天我就交出了一个版本。事实上出去一些页面上需要改动外，最根本的一个问题就是数据库中的数据太大，上千万条的数据，在执行sql的时候速度相当慢。在web连接的过程中容易超时断掉链接。且数据库并非属于我们部门，我们提出加索引却没有得到允许。这个问题困扰了我一段时间，最后才决定将数据库中的数据进行过滤并迁出到我们这个web平台的数据库下，这样数据的大大减少，且过滤掉的基本都是一些无用的数据，对结果造成的影响不大。于是我便开始了用python来编写脚本。

脚本编码的时间不多，大约花了两天就编码完成。然而最痛苦的是测试了。由于sql查询速度慢，每次测试的时间都很长，导致进度很慢。测试的过程我就省略了，下面说说我在编写和测试这个脚本的过程中遇到的一些问题或注意点和我解决的方法吧。

1、其实这也不算问题。在执行insert语句的时候，我们往往是一次执行就插入一行数据。然而在数据量大的时候，多次执行sql的性能比起只执行一条sql的性能要差几个数量级。所以，在参考了如何优化MYSQL insert性能一文后，将代码中的insert语句全拼接成一句来执行，性能果然提升。

2、这是个注意点。在最开始写这个脚本的时候，在测试执行query时，打开了任务管理器发现这个python进程占的内存在极速增加，很快内存被挤爆进程终止。看到这些才发现自己犯了个特别傻逼的错误——想把数据用一个query给拿出来，却没想到数据量实在是太大撑爆了内存。不得已，按照查询条件分成了多条小查询来执行，解决了这个问题。

3、这个问题是困扰我最长时间的一个问题，基本上贯穿了任务的中后期。在最后测试的时候，经常出现"lost connection to mysql server during query“这样的错误。当时仅仅以为只是网络问题（数据库在NAS上）便没有在意。后来每次执行基本上都会出现这样的问题了。连接被断根本没法测试，没办法，上网google了一圈，发现最多的解决方案是在mysql配置文件[myslqd]下面添加一行设置skip-name-resolve，跳过mysql接受连接时的ip解析域名步骤。而由于数据库不归我们所管，想修改配置有一定的难度。于是我只能继续搜索。在stackoverflow上遇到类似问题，他们的解决方法对我似乎也没效果。就这样蛋疼了好几天。后来想到了断线重连（我自己都在想为什么我第一时间想到的方法不是这个？上网搜寻发现了conn.ping(True)这个函数，这个函数可以测试与mysqlserver的连接是否成功。然而如果connection已经close了，调用这个函数仍然会抛出异常。所以我用了下面几行代码，基本上解决了这个问题。之后的测试中发现有掉线现象，但都自动重连了，bingo！

<pre name="code" class="html">while True:try:result = cm_cur.fetchmany(100)except Exception,e:print eprint 'Lost connection during query,to be reconnect...'cm_conn.close()cm_conn = get_connection(host,user,pwd,db,port)cm_cur = cm_conn.cursor(MySQLdb.cursors.SSCursor)cm_cur.execute(sql,params)continueif not result:break

4、这也是个注意点，参考上面的代码。由于检出的数据量太大，在优化性能上我将cursor由默认的换为了MySQLdb.cursors.SSCursor。这是一个服务器流式游标，它将数据检出后不是保存在客户端而是存在mysql服务器上，在fetch的时候在传回客户端。同时fetch的时候不要fetchall()，要用fetchmany(...)甚至fetchone()。以免过高消耗web服务器内存。

上面是我这段时间工作的一些心得。本人还是一只菜鸟实习生，很多知识不甚了解。希望能与大家共同分享，共同进步。