Zenn
Closed1

Django ORM にて SQL発行数を抑制しながら順参照、逆参照する方法

django
SQL
#
orm
デクラインデクライン

結論

パターン methods
順参照 select_related("foo")
順参照-順参照 select_related("foo__bar")
逆参照 prefetch_related("foo_set")
逆参照-逆参照 prefetch_related(
Prefetch("foo_set", queryset=foo.objects.all().prefetch_related(
Prefetch("bar_set", queryset=bar.objects.all())))
逆参照-順参照 prefetch_related(Prefetch("bar_set", queryset=bar.select_related("foo")))

前置き

下記のER図のようなDB構造を想定する。

順参照

  • 順参照の場合、foo.bar にてアクセス可能。
  • select_related を使用することによって順参照先のテーブルを結合しデータをキャッシュするため、データアクセスの度にSQLを発行するのを抑えられる。
# select_related を使用しない場合
employees = Employee.objects.all()
for employee in employees:
    print(employees.department)

"""
発行されたSQL
データ数に伴ってSQL発行数が増加する
[
    {
        "sql": "SELECT \"app_employee\".\"id\", \"app_employee\".\"name\", \"app_employee\".\"department_id\", \"app_employee\".\"insurance_id\" FROM \"app_employee\"",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"id\" = 1 LIMIT 21",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"id\" = 1 LIMIT 21",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"id\" = 2 LIMIT 21",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"id\" = 2 LIMIT 21",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"id\" = 2 LIMIT 21",
        "time": "0.000"
    }
]
"""

# select_related を使用した場合
employees = Employee.objects.all().select_related("department")
for employee in employees:
    print(employees.department)
"""
発行されたSQL
データ数が増えてもSQL発行数は増加しない
[
    {
        "sql": "SELECT \"app_employee\".\"id\", \"app_employee\".\"name\", \"app_employee\".\"department_id\", \"app_employee\".\"insurance_id\", \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_employee\" INNER JOIN \"app_department\" ON (\"app_employee\".\"department_id\" = \"app_department\".\"id\")",
        "time": "0.000"
    }
]
"""

逆参照

  • 逆参照の場合、foo.bar_set によってアクセス可能。
  • prefetch_related('bar_set') を使用することによって逆参照先のテーブルを取得＆pythonコードで結合しデータをキャッシュするため、データアクセスの度にSQLを発行するのを抑えられる。※1
# prefetch_related を使用しない場合
companies = Company.objects.all()
for company in companies:
    departments = company.department_set.all()
    for department in departments:
        print(department.name)

"""
発行されたSQL
データ数に伴いSQL発行数が増加する
[
    {
        "sql": "SELECT \"app_company\".\"id\", \"app_company\".\"name\" FROM \"app_company\"",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 1",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 2",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 3",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 4",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 5",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 6",
        "time": "0.000"
    }
]
"""

# prefetch_related を使用した場合
companies = Company.objects.all().prefetch_related("department_set")  
for company in companies:
    departments = company.department_set.all()
    for department in departments:
        print(department.name)

"""
発行されたSQL
データ数が増えてもSQL発行数は増加しない
[
    {
        "sql": "SELECT \"app_company\".\"id\", \"app_company\".\"name\" FROM \"app_company\"",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" IN (1, 2, 3, 4, 5, 6)", 
        "time": "0.000"
    }
]
"""

順参照-順参照

  • 順参照-順参照の場合、foo.bar.baz にてアクセス可能。
  • select_related(foo__bar) を使用することによって順参照先＆順参照先の順参照先のテーブルを結合しデータをキャッシュするため、データアクセスの度にSQLを発行するのを抑えられる。
# select_related を使用しない場合
employees = Employee.objects.all()
for employee in employees:
    print(employee.department.company.name)

"""
発行されたSQL
データ数に伴いSQL発行数が増加する
[
    {
        "sql": "SELECT \"app_employee\".\"id\", \"app_employee\".\"name\", \"app_employee\".\"department_id\", \"app_employee\".\"insurance_id\" FROM \"app_employee\"",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"id\" = 1 LIMIT 21",
        "time": "0.000"
    },

    (略)

    {
        "sql": "SELECT \"app_company\".\"id\", \"app_company\".\"name\" FROM \"app_company\" WHERE \"app_company\".\"id\" = 1 LIMIT 21",
        "time": "0.000"
    }
]
"""

# select_related を使用した場合
employees = Employee.objects.all().select_related("department__company")
for employee in employees:
    print(employee.department.company.name)

"""
発行されたSQL
データ数が増えてもSQL発行数は増加しない
[
    {
        "sql": "SELECT \"app_employee\".\"id\", \"app_employee\".\"name\", \"app_employee\".\"department_id\", \"app_employee\".\"insurance_id\", \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\", \"app_company\".\"id\", \"app_company\".\"name\" FROM \"app_employee\" INNER JOIN \"app_department\" ON (\"app_employee\".\"department_id\" = \"app_department\".\"id\") INNER JOIN \"app_company\" ON (\"app_department\".\"company_id\" = \"app_company\".\"id\")",
        "time": "0.000"
    }
]
"""

逆参照-逆参照

  • 逆参照のときと同様、foo.bar_set を使用することによってアクセス可能。
  • prefetch_related(Prefetch("bar_set"), queryset=hoge)を使用することによってSQL発行数を抑制できる。
# Prefetch を使用しない場合
companies = Company.objects.all().prefetch_related("department_set")
for company in companies:
    departments = company.department_set.all().prefetch_related("employee_set")
    for department in departments:
        employees = department.employee_set.all()
        for employee in employees:
            print(employee.name)

"""
発行されたSQL
データ数に伴いSQL発行数が増加する
[
    {
        "sql": "SELECT \"app_company\".\"id\", \"app_company\".\"name\" FROM \"app_company\"",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" IN (1, 2, 3, 4, 5, 6)", 
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 1",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_employee\".\"id\", \"app_employee\".\"name\", \"app_employee\".\"department_id\", \"app_employee\".\"insurance_id\" FROM \"app_employee\" WHERE \"app_employee\".\"department_id\" IN (1, 2, 3)",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 2",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 3",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 4",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 5",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" = 6",
        "time": "0.000"
    }
]
"""

# Prefetch を使用した場合
companies = Company.objects.all().prefetch_related(
    Prefetch('department_set', queryset=Department.objects.all().prefetch_related(
    Prefetch('employee_set', queryset=Employee.objects.all(), to_attr="employees")), 
    to_attr="departments"))
for company in companies:
    for department in company.departments:
        for employee in department.employees:
            print(employee.name)

"""
発行されたSQL
データ数が増えてもSQL発行数は増加しない
[
    {
        "sql": "SELECT \"app_company\".\"id\", \"app_company\".\"name\" FROM \"app_company\"",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"company_id\" IN (1, 2, 3, 4, 5, 6)", 
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_employee\".\"id\", \"app_employee\".\"name\", \"app_employee\".\"department_id\", \"app_employee\".\"insurance_id\" FROM \"app_employee\" WHERE \"app_employee\".\"department_id\" IN (1, 2, 3)",
        "time": "0.000"
    }
]
"""

逆参照-順参照

  • 逆参照、順参照のときと同様、foo.bar_set でアクセス後、bar.bazにてアクセス可能。
  • prefetch_related(Prefetch("bar_set"), queryset=hoge)を使用することによってSQL発行数を抑制できる。
# Prefetch を使用しない場合
insurances = Insurance.objects.all().prefetch_related("employee_set")
for insurance in insurances:
    employees = insurance.employee_set.all()
    for employee in employees:
        print(employee.department)

"""
発行されたSQL
データ数に伴いSQL発行数が増加する
[
    {
        "sql": "SELECT \"app_insurance\".\"id\", \"app_insurance\".\"name\" FROM \"app_insurance\"",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_employee\".\"id\", \"app_employee\".\"name\", \"app_employee\".\"department_id\", \"app_employee\".\"insurance_id\" FROM \"app_employee\" WHERE \"app_employee\".\"insurance_id\" IN (1)",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"id\" = 2 LIMIT 21",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_department\" WHERE \"app_department\".\"id\" = 2 LIMIT 21",
        "time": "0.000"
    }
]
"""

# Prefetch を使用した場合
insurances = Insurance.objects.all().prefetch_related(
    Prefetch(
        'employee_set', 
        queryset=Employee.objects.all().select_related("department"), 
        to_attr="employees"
    )
)
for insurance in insurances:
    for employee in insurance.employees:
        print(employee.department)

"""
発行されたSQL
データ数が増えてもSQL発行数は増加しない
[
    {
        "sql": "SELECT \"app_insurance\".\"id\", \"app_insurance\".\"name\" FROM \"app_insurance\"",
        "time": "0.000"
    },
    {
        "sql": "SELECT \"app_employee\".\"id\", \"app_employee\".\"name\", \"app_employee\".\"department_id\", \"app_employee\".\"insurance_id\", \"app_department\".\"id\", \"app_department\".\"name\", \"app_department\".\"company_id\" FROM \"app_employee\" INNER JOIN \"app_department\" ON (\"app_employee\".\"department_id\" = \"app_department\".\"id\") WHERE \"app_employee\".\"insurance_id\" IN (1)",
        "time": "0.000"
    }
]
"""

参考

※1：https://akiyoko.hatenablog.jp/entry/2016/08/03/080941

このスクラップは2023/07/17にクローズされました