优化Python MySQL/Connector速度

Question

我在MySQL中有一张表，包含以下列：

id int(11)
contract_id int(11)
datetime datetime
open decimal(18, 10)
high decimal(18, 10)
low decimal(18, 10)
close decimal(18, 10)

表格相当大（> 3亿行），但在数据库内执行的查询即使返回300,000行也仅需半秒钟。然而，当我从Python检索数据时速度非常慢（同一请求在MySQL Workbench中为0.5秒，在Python中为34秒）：

import pandas as pd
import mysql.connector 

con = mysql.connector.connect(**CONFIG) 
cur = con.cursor()

def get_data1():
    df = pd.read_sql(
        """
        SELECT datetime, open, high, low, close 
        FROM prices
        WHERE contract_id = 1 
            AND datetime >= '2015-01-01 09:00:00' 
            AND datetime <= '2015-10-15 16:00:00'; 
        """, con)
    return df

我发现将数据从MySQL导出到平面文件，然后在Python中读取，比直接查询数据库快23倍：

def get_data2():
    cur.execute(
        """
        SELECT datetime, open, high, low, close 
        FROM prices
        WHERE contract_id = 1 
            AND datetime >= '2015-01-01 09:00:00' 
            AND datetime <= '2015-10-15 16:00:00'
        INTO OUTFILE 'C:/Data/Temp.csv'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY "\n";
        """)
    return pd.read_csv('C:/Data/Temp.csv')

怎么会这样？我怀疑这与数据类型转换有关。你有什么办法可以使函数get_data1更快，而不必先导出为CSV文件吗？谢谢。

- agiap

---

刚看到这个：https://dev59.com/BF_Va4cB1Zd3GeqPSl_N，不知道是否有帮助？ 另外，我看到你正在使用mysql连接器并调用pd.read_sql...你尝试过使用SQLAlchemy和新的函数吗？http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html - measureallthethings

另外，我不得不尝试使用SQLAlchemy的替代方案，以下是我使用MySQL Connector的示例：https://gist.github.com/msure/24ce45067d598fa7a5b6 - measureallthethings

谢谢，我会尝试一下，虽然我不指望它会显著提高性能 - 我相信问题在于数据类型转换。 - agiap

Answer 1

以下解决方案比初始方案快3倍（12秒 vs. 34秒）：

import mysql.connector
con = mysql.connector.connect(**CONFIG)
cur = con.cursor()
class MySQLConverter(mysql.connector.conversion.MySQLConverter):
    def _DECIMAL_to_python(self, value, desc=None):
        return float(value)
    _NEWDECIMAL_to_python = _DECIMAL_to_python

con.set_converter_class(MySQLConverter)

它将MySQL的十进制类型转换为Python中的浮点数，而不是decimal.Decimal，这样速度更快。但是它仍然比“CSV解决方案”慢得多，需要1.57秒才能完成。还在继续探索中...

- agiap