使用select_related和prefetch_related提高数据库查询效率

[Time1ess]

根据django_debug_toolbar工具查询并分析，目前代码中存在的查询语句效率极其低下，如工艺模块的焊接接头工艺分析，对单个工艺分析的获取进行了52次SQL查询，消耗~82ms。主要原因在于serializer中多次使用类似obj.detail.weldingseam_set.count()，而该结果不会被Django缓存，导致大量SQL查询，该性能问题官方做出了解答:

If you are going to need other data from the QuerySet, just evaluate it.

ERP/Process/serializers/process_serializers.py

Lines 613 to 670 in 3cfd47f

	class WeldingWorkInstructionSerializer(serializers.ModelSerializer):
	bm_1 = serializers.SerializerMethodField()
	bm_2 = serializers.SerializerMethodField()
	bm_thick_1 = serializers.SerializerMethodField()
	bm_thick_2 = serializers.SerializerMethodField()
	wt_1 = serializers.SerializerMethodField()
	wt_2 = serializers.SerializerMethodField()
	work_order_uid = serializers.SerializerMethodField()
	uid = serializers.SerializerMethodField()
	weld_position = serializers.SerializerMethodField()
	welding_work_instruction_index = serializers.SerializerMethodField(
	read_only=True)
	def get_welding_work_instruction_index(self, obj):
	return str(obj)
	def get_weld_position(self, obj):
	return obj.detail.weldingseam_set.first().weld_position
	def get_uid(self, obj):
	return obj.detail.joint_index
	def get_work_order_uid(self, obj):
	return obj.detail.spec.work_order.uid
	def get_bm_1(self, obj):
	if obj.detail.weldingseam_set.count() > 0:
	return obj.detail.weldingseam_set.first().bm_1
	return None
	def get_bm_2(self, obj):
	if obj.detail.weldingseam_set.count() > 0:
	return obj.detail.weldingseam_set.first().bm_2
	return None
	def get_bm_thick_1(self, obj):
	if obj.detail.weldingseam_set.count() > 0:
	return obj.detail.weldingseam_set.first().bm_thick_1
	return None
	def get_bm_thick_2(self, obj):
	if obj.detail.weldingseam_set.count() > 0:
	return obj.detail.weldingseam_set.first().bm_thick_1
	return None
	def get_wt_1(self, obj):
	if obj.detail.weldingseam_set.count() > 0:
	return obj.detail.weldingseam_set.first().wt_1
	return None
	def get_wt_2(self, obj):
	if obj.detail.weldingseam_set.count() > 0:
	return obj.detail.weldingseam_set.first().wt_2
	return None
	class Meta:
	model = WeldingWorkInstruction
	fields = '__all__'

select_related和prefetch_related

select_related和prefetch_related方法是Django提供的数据库查询优化方法，前者通过在数据库层面进行join操作减少查询数据所需的往返次数，后者通过关联关系预先获取当前查询集的外键信息，在Python层面进行'join'，二者均能大幅提高性能，具体选用哪一种需要根据关联关系，select_related需要正向的外键关系，而prefetch_related同时支持正反向，但目前测试来看性能略低于select_related(可能由于数据量太小，数据库层面join操作耗时不多，有待研究)。

作为性能优化对比，以库存模块辅材领用台账为例:/api/auxiliary_material_apply_ledgers/。

优化前

查询列表操作耗时随着对象数量的增加呈线性增长，直至达到单页最大数量后，查询时间不再增加，当达到每页10个(单页最大数量)，此时的查询操作进行82次SQL查询，耗时~145.32ms，平均单个台账的查询耗时~14.53ms。
原API代码:

ERP/Inventory/api/apply_ledger.py

Lines 27 to 31 in df906ed

	class AuxiliaryMaterialApplyLedgerViewSet(viewsets.ReadOnlyModelViewSet):
	pagination_class = SmallResultsSetPagination
	serializer_class = serializers.AuxiliaryMaterialApplyLedgerSerializer
	queryset = AuxiliaryMaterialApplyCard.objects.all().order_by('-pk')
	filter_class = filters.AuxiliaryMaterialApplyLedgerFilter

优化后

时间不再随数量线性增长，此时任意数量(以10个对象为例)的查询操作均进行2次SQL查询，耗时~11.16ms，平均单个台账的查询耗时~1.12ms。
新API代码:

ERP/Inventory/api/apply_ledger.py

Lines 33 to 42 in 3cfd47f

	class AuxiliaryMaterialApplyLedgerViewSet(viewsets.ReadOnlyModelViewSet):
	pagination_class = SmallResultsSetPagination
	serializer_class = serializers.AuxiliaryMaterialApplyLedgerSerializer
	queryset = (AuxiliaryMaterialApplyCard.objects.all().order_by('-pk')
	.select_related(
	('apply_inventory__entry_detail__procurement_material'
	'__process_material'),
	('actual_inventory__entry_detail__procurement_material'
	'__process_material')))
	filter_class = filters.AuxiliaryMaterialApplyLedgerFilter