数据库查询性能优化手记

2021-03-09

简单的手记,没有什么系统性。相关的变量名是经过简单替换的,以免关注点偏离(bushi

 

尝试 1:使用 SQLAlchemy 的 Dynamic Relationship Loaders 功能: #

class BasicInfo(Base):
    __tablename__ = "basic_info"
    day_infos = relationship(
        "DayInfo", order_by="desc(DayInfo.date)", lazy="dynamic"
    )


class DayInfo(Base):
    __tablename__ = "day_info"
    code = Column(String(8), ForeignKey("basic_info.code"), primary_key=True)
python

并在读取中使用 limit 控制读取的个数。处理 3 天的数据查找指定条件,用时约 1 分钟。

尝试 2:一次性读取所有的记录 #

按照尝试 1 的做法数据库请求的个数会很多,数千次请求不会很快。如果改用所有数据合并到一次请求当中,则大大加快。用时达到 8 秒。

问题 1:启动时间 #

由于需要导入大量的库,启动时间约有 4 秒钟,不容乐观。

使用 PYTHONPROFILEIMPORTTIME=1 运行,并将 stderr 导出至文件,使用 tuna 查看,发现大头是 pandas 和 SQLAlchemy。由于 pandas 并不是每次运行都需要使用,故将其从 top level import 挪到的相应的函数中。时间减少了 2 秒。

此时用时达到 6 秒。

尝试 3:使用 List Comprehension 运行匹配算法 #

时间几乎没有变化。后来知道是匹配算法本来运行时间就短的原因。

尝试 4:使用 SQLAlchemy 自带的分页功能读取数据 #

for data in session.execute(
    session.query(BasicInfo, DayInfo)
    .join(BasicInfo.day_infos)
    .where(DayInfo.date > date_after)
    .execution_options(yield_per=1000)
).partitions():
python

时间并没有显著变化,但是内存的占用从大约 9% 降至 2%。可以肯定是 Python 的对象数量减少带来的。

尝试 5:更换 Python 的 MySQL 连接库 #

使用 python -m cProfile -o program.prof -m myprog ,再使用 snakeviz 可视化,发现很多时间被用在了 parse string, int 等上。而且使用 top 命令也可以看到 CPU 占满了一个核。将 PyMySQL 更换为 mysqlclient 后,处理 40 天数据从 22 秒降至 12 到 13 秒。

问题 2:分页 #

由于股票数据很多,尽管尝试 4 中使用了分页,但是在 MySQL 中,数据仍是一次读取。

参考了网上的资料:

看到应该结合所选的条件进行分页而不是暴力地 LIMIT OFFSET. 如:

SELECT day_info.*, basic_info.data FROM basic_info
JOIN day_info ON basic_info.code=day_info.code
WHERE DATE > '2021-03-01'
AND (
    basic_info.code > 'sh600011'
    OR (basic_info.code='sh600011' AND day_info.date > '2021-03-03')
)
LIMIT 30
sql

尝试 6:不使用 SQLAlchemy #

进一步 profile 看到有很多运行时间是花在 SQLAlchemy 上的。直接跳过 ORM 层操作,时间减少了约 1 秒(可能就是不 import SQLAlchemy 带来的),但是在运行时间中,CPU 占用比率((user + sys)/real) 从 96% 降至 50% - 60%。这意味着性能瓶颈由原来的 CPU 转化为 CPU 和 IO

问题 3:Query Time #

过程中发现 MariaDB 突然表现出了缓存功能,再次运行后 IO 时间显著减小至可以忽略。所以 MySQL 的 query 速度还有很大的优化空间。

使用

SHOW GlOBAL VARIABLES LIKE 'slow%log%'
sql

发现并没有开启对慢查询的日志,使用以下命令开启:

SET GLOBAL slow_query_log='on';
SET GLOBAL slow_query_log_file='/var/log/mysql/sql-slow.log';
SET GLOBAL long_query_time = 1;
sql

查阅日志可以看到,一条一万数据的 select 花了 7 秒钟才完成。

Query_time: 6.917748  Lock_time: 0.000308  Rows_sent: 10000  Rows_examined: 799
text

为方便调试性能,需要关闭缓存功能,可使用 SELECT SQL_NO_CACHE ... 实现。

如问题 2 中的 query:

SELECT sql_no_cache day_info.*, basic_info.data
FROM basic_info
JOIN day_info ON basic_info.code=day_info.code
WHERE DATE > '2021-01-25' AND (
 basic_info.code > 'specificode' OR (
 basic_info.code='specificode' AND day_info.date > '2021-02-09'
)
)
LIMIT 5000;
sql

单独运行需要 3 秒钟。但是如果拆成 2 个:

SELECT sql_no_cache day_info.*, basic_info.data
FROM basic_info
JOIN day_info ON basic_info.code=day_info.code
WHERE DATE > '2021-01-25' AND  basic_info.code > 'specificode'
LIMIT 5000;
sql
SELECT sql_no_cache day_info.*, basic_info.data
FROM basic_info
JOIN day_info ON basic_info.code=day_info.code
WHERE basic_info.code='specificode' AND day_info.date > '2021-02-09';
sql

则半秒钟内即可完成。

有没有将两个合成一个但又不影响性能的方法呢?是有的。使用 UNION 合并两个 query,并注意要用 UNION ALL 跳过去重步骤,并用括号第把二个语句括起来。如果不使用括号,则 LIMIT 将被识别为整个大语句的,严重影响速度。

SELECT SQL_NO_CACHE day_info.*, basic_info.data
FROM basic_info
JOIN day_info ON basic_info.code=day_info.code
WHERE basic_info.code='specificode' AND day_info.date > '2021-02-09'
UNION ALL
(
  SELECT day_info.*, basic_info.data
  FROM basic_info
  JOIN day_info ON basic_info.code=day_info.code
  WHERE DATE > '2021-01-25' AND (
   basic_info.code > 'specificode'
  )
  LIMIT 5000
);
sql

处理 40 天数据降至 7 秒。

尝试 7:use_result vs store_result #

在 mysqlclient 中,默认的 cursor 类在底层使用 store_result,即将每一次查询的结果一股脑存下来备用。相比之下 use_result 就人性化很多,先放在 server 里有需要再取,也符合 fetchmany 的初衷。

使用默认 Cursor 类的 profile 结果:

before

使用 SSCursor 类的 profile 结果(橘色那块是 execute,剩下那块是另一个与 SQL 无关的函数,也就是上图较深灰的部分):

after
👍
1
Leave your comments and reactions on GitHub