优化Python MySQL/连接器速度

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工作台的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吗？谢谢。

Answer 1

以下解决方案比最初的解决方案快3倍(12秒与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)

它将Python decimal类型转换为MySQL float，而不是decimal.Decimal，后者更快。它仍然比"CSV解决方案“慢得多，后者需要1.57秒才能完成。还在挖掘。