pymysql与MySQLdb(mysqlclient)性能对比

程序员文章站 2022-07-14 22:53:43

...

1.python3.6.5； django2.1.7,未使用自带的ORM框架；数据库mysql8.0；连接库是pymysql0.9.3
2.因MySQLdb不支持python3.x，故而有了mysqlclient:支持python3.x，且属于MySQLdb的分支

先上结论：数据量大用MySQLdb(mysqlclient)，数据量小(1w行以下)用pymysql

问题描述：项目后台API响应过慢，将近7s，严重影响体验
pymysql与MySQLdb(mysqlclient)性能对比
而后将sql语句放入命令行执行，观察耗时

嗯…18W数据也不多，两者执行时间却相差8倍有余。不禁对pymysql这个连接库产生了怀疑,于是乎在Stack Overflow找到这样一篇回答https://*.com/questions/4960048/how-can-i-connect-to-mysql-in-python-3-on-windows
pymysql与MySQLdb(mysqlclient)性能对比
pymysql: 纯python语言编写，速度比mysql-connector快，在调用pymysql.install_as_MySQLdb()后，几乎完全兼容MySQLdb
mysqlclient: django框架推荐连接库，基于C编写，速度快，是MySQLdb的一个fork，最兼容MySQLdb
既然话说这么实在了，那就开始动手试试。

1.安装mysqlclient

1.1 windows:直接上https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient选择对应版本

pymysql与MySQLdb(mysqlclient)性能对比
再在cmd中跳到下载目录，执行

注：我没有用pip install mysqlclient，有兴趣的可以试一试

1.2 linux:我用的CentOS7虚拟机，先`yum install python-devel`再`yum install mysql-devel`最后`pip(3) install mysqlclient`

1.3 在python命令行中`import MySQLdb`，没报错就ok了，报错了就没必要看下去了0.0

2.测试

先上代码
databases.py

import importlib
import time
from DBUtils.PooledDB import PooledDB
from functools import wraps


def timeit(func):
    """ Decorator to calc the time cost
    :param func:
    :return:
    """

    @wraps(func)
    def wrapper(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        end_time = time.time()
        print('[{}] costs [{}]secs'.format(func.__name__, end_time - start_time))
        return result

    return wrapper


class DataBase(object):

    def __init__(self, db_type, config):

        self.__db_type = db_type

        if self.__db_type == 'mysqlclient':
            db_creator = importlib.import_module('MySQLdb')
        elif self.__db_type == 'pymysql':
            db_creator = importlib.import_module('pymysql')
        else:
            raise Exception('unsupported database type ' + self.__db_type)
        self.pool = PooledDB(
            creator=db_creator,
            mincached=0,
            maxcached=6,
            maxconnections=0,
            maxshared=3,
            maxusage=0,
            blocking=True,
            ping=0,
            **config
        )

    @timeit
    def execute_query(self, sql, as_dict=True):
        conn = None
        cur = None
        try:
            conn = self.pool.connection()
            cur = conn.cursor()
            cur.execute(sql)
            rst = cur.fetchall()
            if rst:
                if as_dict:
                    fields = [tup[0] for tup in cur._cursor.description]
                    return [dict(zip(fields, row)) for row in rst]
                return rst
            return rst

        except Exception as e:
            print('[{}]meet error'.format(sql))
            print(e.args[-1])
            return ()
        finally:
            if conn:
                conn.close()
            if cur:
                cur.close()


py_sql = DataBase(
    'pymysql', {'user': '*', 'host': '*', 'password': '*', 'database': '*', 'port': 3306}
)
c_sql = DataBase(
    'mysqlclient', {'user': '*', 'host': '*', 'password': '*', 'database': '*', 'port': 3306}
)

test.py

from databases import py_sql, c_sql

sql = """省略..."""
print('mysqlclient:', end='')
c_sql.execute_query(sql)
print('pymysql:', end='')
py_sql.execute_query(sql)