解决mysqldb查询大量数据导致内存使用过高的问题
来源:互联网 发布:软件开发项目实施方案 编辑:程序博客网 时间:2024/06/16 12:10
1.源代码
3.优化后的代码
connection=MySQLdb.connect( host="thehost",user="theuser", passwd="thepassword",db="thedb")cursor=connection.cursor()cursor.execute(query)for row in cursor.fetchall(): print(row)
2.问题
普通的操作无论是fetchall()还是fetchone()都是先将数据载入到本地再进行计算,大量的数据会导致内存资源消耗光。解决办法是使用SSCurosr光标来处理。
3.优化后的代码
import MySQLdb.cursorsconnection=MySQLdb.connect( host="thehost",user="theuser", passwd="thepassword",db="thedb", cursorclass = MySQLdb.cursors.SSCursor)cursor=connection.cursor()cursor.execute(query)for row in cursor: print(row)
参考文档:http://mysql-python.sourceforge.net/MySQLdb.html#
关键段落截取:
- BaseCursor
- The base class for Cursor objects. This does not raise Warnings.
- CursorStoreResultMixIn
- Causes the Cursor to use the mysql_store_result() function to get the query result. The entire result set is stored on the client side.
- CursorUseResultMixIn
- Causes the cursor to use the mysql_use_result() function to get the query result. The result set is stored on the server side and is transferred row by row using fetch operations.
- CursorTupleRowsMixIn
- Causes the cursor to return rows as a tuple of the column values.
CursorDictRowsMixIn
Causes the cursor to return rows as a dictionary, where the keys are column names and the values are column values. Note that if the column names are not unique, i.e., you are selecting from two tables that share column names, some of them will be rewritten as table.column. This can be avoided by using the SQL ASkeyword. (This is yet-another reason not to use * in SQL queries, particularly where JOIN is involved.)
- Cursor
- The default cursor class. This class is composed of CursorWarningMixIn, CursorStoreResultMixIn, CursorTupleRowsMixIn, and BaseCursor, i.e. it raises Warning, usesmysql_store_result(), and returns rows as tuples.
- DictCursor
- Like Cursor except it returns rows as dictionaries.
- SSCursor
- A "server-side" cursor. Like Cursor but uses CursorUseResultMixIn. Use only if you are dealing with potentially large result sets.
- SSDictCursor
- Like SSCursor except it returns rows as dictionaries.
0 0
- 解决mysqldb查询大量数据导致内存使用过高的问题
- 如何解决PHP查询大量数据内存耗尽的问题
- 如何解决PHP查询大量数据内存耗尽的问题
- php查询mysql大量数据占用大量内存导致内存不足
- 解决Ubuntu14.04下使用foxit reader(福昕阅读器)导致CPU占用过高的问题
- JVM内存溢出导致的CPU过高问题排查案例
- MySQLdb解决中文数据的问题
- PHP查询MySQL大量数据的内存
- PHP查询MySQL大量数据的内存
- PHP查询MySQL大量数据的内存
- 使用NFS时出现nfs_inode_cache占用内存过高问题的解决
- 解决WordPress被利用xmlrpc.php导致VPS CPU内存占用过高问题
- AIX系统NONCOMP内存占比过高导致换页内存使用过多问题
- IIS解决CPU和内存占用率过高的问题
- 解决linux buffer/cache 消耗内存过高引发的问题
- 解决oracle服务占用内存过高的问题
- 内存频率过高导致的不开机
- mysql查询大量数据,php内存占用溢出问题
- [Swift]枚举
- linux 中的gmtime和localtime函数
- loadrunner 常用事务判断
- 未能加载程序集或者命名空间
- 获得资源文件
- 解决mysqldb查询大量数据导致内存使用过高的问题
- DDOS攻击主要类型解析
- C++类型转换与this指针调整
- 法线贴图
- route IP 地址的处理
- NSTimeInterval的使用
- JNI生成头文件及签名
- 蓝底表格样式
- 通俗理解LDA主题模型