Flask SQL性能测试：使用flask-sqlalchemy
2021-01-14 本文已影响0人
xiaogp
摘要：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)
