Flask SQL性能测试：使用flask-sqlalchemy

摘要：Flask，flask-sqlalchemy，SQL

记录SQL语句和执行时长

flask-sqlalchemy可以记录下在客户端请求应用的过程中，后台涉及的SQL语句，以及SQL语句执行的时长，主要基于flask-sqlalchemy下的get_debug_queries实现。在主视图脚本中加入一个after_request视图如下。

import logging

from flask import Flask
from flask_sqlalchemy import get_debug_queries

import config
from blueprints.detail import detail
from blueprints.index import index
from blueprints.compare import compare
from external import db

logging .basicConfig(level=logging.DEBUG, filemode='a+', filename='query_log.log', format='%(asctime)s -: %(message)s)')
app = Flask(__name__)

app.config.from_object(config)
app.config['SQLALCHEMY_RECORD_QUERIES'] = True
db.init_app(app)
with app.app_context():
    db.create_all()


app.register_blueprint(index)
app.register_blueprint(compare)
app.register_blueprint(detail)


@app.after_request
def after_request(response):
    for query in get_debug_queries():
        if query.duration >= 0.2:
            logging.info('----------Slow query:' + query.statement)
            logging.info('----------duration:{}'.format(query.duration))
    return response


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=False)

其中开启SQL Debug模式app.config['SQLALCHEMY_RECORD_QUERIES'] = True，get_debug_queries返回请求涉及的所有SQL语句，调用duration获得SQL执行时长，只要大于0.2就用logging模块记录到磁盘。
启动程序，点击应用功能，logging会把大于时间阈值的SQL写入本地文件query_log.log

查看SQL执行日志

直接搜索Slow query，得到以下几条记录

2021-01-14 16:20:11,073 -: ----------Slow query:SELECT pira_ent_alias.id AS pira_ent_alias_id, pira_ent_alias.ent_name AS pira_ent_alias_ent_name, pira_ent_alias.ent_shortname AS pira_ent_alias_ent_shortname, pira_ent_alias.score AS pira_ent_alias_score 
FROM pira_ent_alias 
WHERE pira_ent_alias.ent_name = %(ent_name_1)s)
2021-01-14 16:20:11,073 -: ----------duration:18.175966024398804)

2021-01-14 16:17:56,025 -: ----------Slow query:SELECT pira_score.id AS pira_score_id, pira_score.ent_name AS pira_score_ent_name, pira_score.score AS pira_sc
ore_score, pira_score.datetime AS pira_score_datetime, pira_score.industry AS pira_score_industry, pira_score.`rank` AS pira_score_rank 
FROM pira_score 
WHERE pira_score.ent_name = %(ent_name_1)s ORDER BY pira_score.datetime DESC 
 LIMIT %(param_1)s)
2021-01-14 16:17:56,025 -: ----------duration:7.9724133014678955)

2021-01-14 17:12:27,242 -: ----------Slow query:SELECT sum(pira_senti_stats.bad) AS sum_1, sum(pira_senti_stats.median) AS sum_2, sum(pira_senti_stats.good) A
S sum_3 
FROM pira_senti_stats 
WHERE pira_senti_stats.ent_name = %(ent_name_1)s AND pira_senti_stats.datetime <= %(datetime_1)s AND pira_senti_stats.datetime > %(datetime_2)s)
2021-01-14 17:12:27,242 -: ----------duration:4.695176601409912)

三张表pira_ent_alias，pira_score，pira_senti_stats都是简单的filter查询，没有复杂的逻辑，查看MySQL explain执行计划

mysql> EXPLAIN(SELECT pira_score.id AS pira_score_id, pira_score.ent_name AS pira_score_ent_name, pira_score.score AS pira_score_score, pira_score.datetime AS pira_score_datetime, pira_score.industry AS pira_score_industry, pira_score.`rank` AS pira_score_rank FROM pira_score WHERE pira_score.ent_name = '华为技术有限公司' ORDER BY pira_score.datetime DESC limit 15);
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | pira_score | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 141355 |    10.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

mysql> EXPLAIN(SELECT pira_ent_alias.id AS pira_ent_alias_id, pira_ent_alias.ent_name AS pira_ent_alias_ent_name, pira_ent_alias.ent_shortname AS pira_ent_alias_ent_shortname, pira_ent_alias.score AS pira_ent_alias_score FROM pira_ent_alias WHERE pira_ent_alias.ent_name = '华为技术有限公司');
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | pira_ent_alias | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 415831 |    10.00 | Using where |
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+

mysql> EXPLAIN(SELECT sum(pira_senti_stats.bad) AS sum_1, sum(pira_senti_stats.median) AS sum_2, sum(pira_senti_stats.good) AS sum_3  FROM pira_senti_stats  WHERE pira_senti_stats.ent_name = '华为技术有限公司' AND pira_senti_stats.datetime <= '2020-12-07' AND pira_senti_stats.datetime > '2020-06-07');
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | pira_senti_stats | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 167504 |     1.11 | Using where |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

explain结果解析

• select_type：simple为简单的select语句，不涉及子查询和联合查询union；PRIMARY带有子查询的最外层select；SUBQUERY，带有子查询的第一个select，UNION联合查询中第二个或者union后面的select
• table：显示这一步需要访问的表，是这一步操作的实际表
• type：对表的访问方式，从上到下，性能越来越好
  （1）ALL：全表扫描
  （2）index：遍历索引树
  （3）range：只检索指定范围的行，使用索引，使用where范围检索，<,<=,>,>=,between,in
  （4）ref：非唯一索引访问，普通索引
  （5）const：常量，经过mysql优化，将查询复杂度变为一个常量，只匹配一行数据，常用于primary key或者unique
  （6）system：system是const的一个特例，当查询表只有一列的时候是system
  （7）NULL：执行时甚至不需要访问表和索引
• possible_keys：表示可能使用的索引
• key：实际使用的索引
• rows：扫描出的行数，数值越大越不好，说明没有用好索引

增加索引
三张表都进行了全表扫描，先加上索引再说，给pira_ent_alias增加ent_name和ent_shortname的普通索引

mysql> alter table pira_ent_alias  add index ent_name_index (ent_name);
Query OK, 0 rows affected (14.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table pira_ent_alias add index ent_shortname_index (ent_shortname);
Query OK, 0 rows affected (13.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN(SELECT pira_ent_alias.id AS pira_ent_alias_id, pira_ent_alias.ent_name AS pira_ent_alias_ent_name, pira_ent_alias.ent_shortname AS pira_ent_alias_ent_shortname, pira_ent_alias.score AS pira_ent_alias_score FROM pira_ent_alias WHERE pira_ent_alias.ent_name = '华为技术有限公司');
+----+-------------+----------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | pira_ent_alias | NULL       | ref  | ent_name_index | ent_name_index | 602     | const |    4 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

给pira_score，pira_senti_stats增加ent_name和datetime的复合索引，复合索引从字段组合的最左边开始就要走，否则无效，因此如果filter条件只有datetime还是全表扫描，filter条件是ent_name或者ent_name和datetime有效。

mysql> alter table pira_score add index ent_name_datetime_index(ent_name, datetime);
Query OK, 0 rows affected (12.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN(SELECT pira_score.id AS pira_score_id, pira_score.ent_name AS pira_score_ent_name, pira_score.score AS pira_score_score, pira_score.datetime AS pira_score_datetime, pira_score.industry AS pira_score_industry, pira_score.`rank` AS pira_score_rank FROM pira_score WHERE pira_score.ent_name = '华为技术有限公司' ORDER BY pira_score.datetime DESC limit 15);
+----+-------------+------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | pira_score | NULL       | ref  | ent_name_datetime_index | ent_name_datetime_index | 602     | const |   38 |   100.00 | Using where |
+----+-------------+------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table pira_senti_stats add index ent_name_datetime_index(ent_name, datetime);
Query OK, 0 rows affected (7.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN(SELECT sum(pira_senti_stats.bad) AS sum_1, sum(pira_senti_stats.median) AS sum_2, sum(pira_senti_stats.good) AS sum_3  FROM pira_senti_stats  WHERE pira_senti_stats.ent_name = '华为技术有限公司' AND pira_senti_stats.datetime <= '2020-12-07' AND pira_senti_stats.datetime > '2020-06-07');
+----+-------------+------------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table            | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | pira_senti_stats | NULL       | range | ent_name_datetime_index | ent_name_datetime_index | 664     | NULL |  158 |   100.00 | Using index condition |
+----+-------------+------------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

对比SQL性能优化

给三张表增加索引之后，再次运行get_debug_queries，阈值降低为0，查看日志如下，执行时间都有很大降低

2021-01-14 17:39:35,356 -: ----------Slow query:SELECT pira_ent_alias.id AS pira_ent_alias_id, pira_ent_alias.ent_name AS pira_ent_alias_ent_name, pira_ent_alias.ent_shortname AS pira_ent_alias_ent_shortname, pira_ent_alias.score AS pira_ent_alias_score 
FROM pira_ent_alias 
WHERE pira_ent_alias.ent_name = %(ent_name_1)s)
2021-01-14 17:39:35,356 -: ----------duration:0.024198293685913086)

2021-01-14 17:39:35,356 -: ----------Slow query:SELECT pira_score.id AS pira_score_id, pira_score.ent_name AS pira_score_ent_name, pira_score.score AS pira_score_score, pira_score.datetime AS pira_score_datetime, pira_score.industry AS pira_score_industry, pira_score.`rank` AS pira_score_rank 
FROM pira_score 
WHERE pira_score.ent_name = %(ent_name_1)s ORDER BY pira_score.datetime DESC 
 LIMIT %(param_1)s)
2021-01-14 17:39:35,356 -: ----------duration:0.003040790557861328)

2021-01-14 17:39:35,356 -: ----------Slow query:SELECT sum(pira_senti_stats.bad) AS sum_1, sum(pira_senti_stats.median) AS sum_2, sum(pira_senti_stats.good) AS sum_3 
FROM pira_senti_stats 
WHERE pira_senti_stats.ent_name = %(ent_name_1)s AND pira_senti_stats.datetime <= %(datetime_1)s AND pira_senti_stats.datetime > %(datetime_2)s)
2021-01-14 17:39:35,356 -: ----------duration:0.0028617382049560547)