django - Django 模型数据库查询优化（使用 select_related / prefetch_related）

Question

我有这个一对多的关系模型[每个域可以有几个 kpi]。我使用 MySQL 数据库。

class Domains(models.Model):
    class Meta:
        managed = True
        db_table = 'edison_domains'

    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=50, unique=True)
    display_name = models.CharField(max_length=50)
    description = models.TextField(blank=True, null=True)

class Kpis(models.Model):
    class Meta:
        managed = True
        db_table = 'edison_kpis'

    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=50, unique=True)
    MORE FIELDS...
    domain_id = models.ForeignKey(Domains, on_delete=models.CASCADE, db_column='domain_id')

我想要的是为每个域提供其 KPI 列表。我尝试了很多组合，select_related但prefetch_related它总是最终数据库为每个域执行查询，而不是 1 或 2 个查询。我试图在这里阅读它。

我的主要目标是减少数据库调用次数并提高整体性能时间。

我当前的代码：

(views.py)
class DomainsAndKpisDescriptionsPage(View):
    def __init__(self):
        self.resource = 'domains_kpis_descriptions'

    def get(self, request, *args, **kwargs):
        final_list = []
        domains_list = Domains.objects.all().prefetch_related('kpis_set').order_by('display_name')
        kpis = Kpis.objects.select_related('domain_id').values('id', 'display_name', 'number_display_format', 'description', 'calculation_type').order_by('display_name')
        for domain in domains_list:
            # For each domain, get all related KPIs
            domain_kpis = kpis.prefetch_related('domain_id').filter(domain_id=domain.id).order_by('domain_display_order')
            final_list.append({'name':domain.name,
                               'display_name':domain.display_name,
                               'description':domain.description,
                               'kpis':domain_kpis})
        context = {'domains_and_kpis_data':final_list}
        return render(request,
                      'tests_details/domains_kpis_descriptions.html',
                      context=context)

还有我的 HTML 模板

<div class="row">
        <div class="col-lg-3 col-xlg-2 col-md-4">
            <div class="stickyside">
                <h3> Domains </h3>
                <div class="list-group" id="top-menu">
                    {% for domain in domains_and_kpis_data %}
                        <a href="#{{ domain.name }}" class="list-group-item {% if forloop.counter0 == 0 %} active {% endif %}">{{ domain.display_name }}</a>
                    {% endfor %}
                </div>
            </div>
        </div>
        <div class="col-lg-9 col-xlg-10 col-md-8">
            <div class="card">
                <div class="card-body">
                    {% for domain in domains_and_kpis_data %}
                        <div style="padding:10px;">
                            <h3 class="card-title text-themecolor kpi_header" id="{{ domain.name }}">{{ domain.display_name }} <font size="3"> ({{ domain.kpis|length }} KPIs) </font></h3>
                            <h6 class="text-themecolor">{{ domain.description }}</h6>
                        </div>
                        <table class="table table-bordered">
                            <thead>
                                <tr>
                                    <th width="30%">KPI</th>
                                    <th width="10%">Type</th>
                                    <th width="60%">Description</th>
                                </tr>
                            </thead>
                            {% for kpi in domain.kpis %}
                                <tr>
                                    <th> {{ kpi.display_name }} </th>
                                    <th> {{ kpi.calculation_type }} </th>
                                    <th> {{ kpi.description }} </th>
                                </tr>
                            {% endfor %}
                        </table>
                    {% endfor %}
                </div>
            </div>
        </div>
    </div>

假设我有 14 个域 - 数据库执行 14 个查询。附件是来自 Django 调试工具栏的图像：

标签： djangodatabaseoptimizationone-to-manydjango-select-related