pymysql与MySQLdb(mysqlclient)性能对比

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

问题描述：项目后台API响应过慢，将近7s，严重影响体验

1.安装mysqlclient

1.1 windows:直接上选择对应版本

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)

过程：
第一个sql测试(运行两遍)：

3. 结论

单次返回1w行以下时建议用pymysql,使用方便，安装和部署简单，单次1w以上时建议MySQLdb(mysqlclient)

4.备注

4.1 中文乱码时，注意设置charset。像我这个库就是utf8mb4编码，不设置的话，返回中文会乱码
4.2 少用select * from，只返回你需要的字段，sql性能会提升很多