对于django模型使用外键时多次查询sql的后续

为了方便排查出sql重复查询的次数，做了一个中间件

sql建议中间件

# -*- coding: utf-8 -*-
from django.db import connection
from django.utils.deprecation import MiddlewareMixin

proposal_time = 1


class SqlProposal(MiddlewareMixin):
    def process_response(self, request, response):
        queries = connection.queries
        consume_time = 0
        sql_count = {}
        proposal_sql = []
        # 统计
        for query in queries:
            consume_time += float(query['time'])
            sql_count.update({query['sql']: sql_count.get(query['sql'], 0) + 1})
        # sql建议收集
        for sql, frequency in sql_count.items():
            if frequency > 1:
                proposal_sql.append((sql, frequency))
        # 建议
        if proposal_sql:
            print('*' * 50)
            print('url: {}'.format(request.get_full_path()))
            if proposal_sql:
                print('sql次数优化建议:')
                for proposal_sql_one in proposal_sql:
                    print('{}\t{}次'.format(proposal_sql_one[0], proposal_sql_one[1]))
            if consume_time > proposal_time:
                print('本次接口sql查询消耗{}秒'.format(consume_time))
            print('*' * 50)
        return response

拓展

这里针对了sql重复查询的优化，但并不完善，在上篇测试文章中一对多的查询中包含如下结果

优化前

# [DEBUG]> (0.001) b'SELECT `sqltry_author`.`id`, `sqltry_author`.`name` FROM `sqltry_author`'; args=()
# [DEBUG]> (0.005) b'SELECT `sqltry_article`.`id`, `sqltry_article`.`name`, `sqltry_article`.`author_id` FROM `sqltry_article` WHERE `sqltry_article`.`author_id` = 1'; args=(1,)
# [DEBUG]> (0.003) b'SELECT `sqltry_article`.`id`, `sqltry_article`.`name`, `sqltry_article`.`author_id` FROM `sqltry_article` WHERE `sqltry_article`.`author_id` = 2'; args=(2,)

优化后

# [DEBUG]> (0.001) b'SELECT `sqltry_author`.`id`, `sqltry_author`.`name` FROM `sqltry_author`'; args=()
# [DEBUG]> (0.005) b'SELECT `sqltry_article`.`id`, `sqltry_article`.`name`, `sqltry_article`.`author_id` FROM `sqltry_article` WHERE `sqltry_article`.`author_id` IN (1, 2)'; args=(1, 2)

此处的两条sql并不相同，但是还是可以进行查询优化的。

个人想法是再增加一个sql语句相似度的计算匹配，参考https://blog.csdn.net/wumian0123/article/details/81435680

由于自身对sql的熟练度不高，初步的想法未必是高效好用的，所以这里不做拓展。

望大牛看到能指点指点！！！