Chapter 16: Performance Optimization
16.1 Database Optimization
Query Optimization
Database query optimization is key to improving Django application performance:
python
# models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
slug = models.SlugField(unique=True)
class Meta:
indexes = [
models.Index(fields=['slug']),
]
class Tag(models.Model):
name = models.CharField(max_length=50)
slug = models.SlugField(unique=True)
class Meta:
indexes = [
models.Index(fields=['slug']),
]
class Article(models.Model):
title = models.CharField(max_length=200)
author = models.ForeignKey(User, on_delete=models.CASCADE, db_index=True)
category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, blank=True, db_index=True)
tags = models.ManyToManyField(Tag, blank=True)
content = models.TextField()
view_count = models.PositiveIntegerField(default=0, db_index=True)
created_at = models.DateTimeField(auto_now_add=True, db_index=True)
updated_at = models.DateTimeField(auto_now=True)
status = models.CharField(max_length=10, default='draft', db_index=True)
class Meta:
indexes = [
models.Index(fields=['status', 'created_at']),
models.Index(fields=['author', 'created_at']),
models.Index(fields=['view_count']),
models.Index(fields=['-created_at']),
]
ordering = ['-created_at']
# views.py - Query before optimization
def article_list_bad(request):
"""Poor performance article list query"""
articles = Article.objects.all() # Get all fields
for article in articles:
# N+1 query problem
print(article.author.username) # Query author each time
print(article.category.name) # Query category each time
for tag in article.tags.all(): # Query tags each time
print(tag.name)
return render(request, 'article_list.html', {'articles': articles})
# views.py - Optimized query
def article_list_good(request):
"""Optimized article list query"""
articles = Article.objects.select_related(
'author', 'category' # Preload foreign key related objects
).prefetch_related(
'tags' # Preload many-to-many relationships
).filter(
status='published'
).only( # Only get needed fields
'title', 'content', 'author__username', 'category__name'
)[:20] # Limit result count
return render(request, 'article_list.html', {'articles': articles})
# Using Prefetch for more precise control
from django.db.models import Prefetch
def article_list_advanced(request):
"""Advanced optimized article list query"""
# Preload tags with filtering conditions
articles = Article.objects.select_related(
'author', 'category'
).prefetch_related(
Prefetch(
'tags',
queryset=Tag.objects.filter(name__startswith='tech'),
to_attr='tech_tags' # Custom attribute name
)
).filter(
status='published'
).annotate(
# Add annotation fields
comment_count=models.Count('comments', distinct=True)
).order_by('-created_at')[:20]
return render(request, 'article_list.html', {'articles': articles})
# Avoid unnecessary queries
def article_detail_optimized(request, slug):
"""Optimized article detail query"""
try:
# Use select_related and prefetch_related
article = Article.objects.select_related(
'author__profile', 'category'
).prefetch_related(
Prefetch(
'comments',
queryset=Comment.objects.select_related('author').filter(
is_approved=True, parent=None
),
to_attr='approved_comments'
),
'tags'
).get(slug=slug, status='published')
# Get related articles (avoid N+1 query)
related_articles = Article.objects.filter(
category=article.category,
status='published'
).exclude(
pk=article.pk
).select_related('author').only(
'title', 'slug', 'created_at'
)[:5]
return render(request, 'article_detail.html', {
'article': article,
'related_articles': related_articles
})
except Article.DoesNotExist:
raise Http404("Article not found")
# Use aggregate queries to reduce database access
def category_stats(request):
"""Category statistics query"""
# Use annotate for aggregation
categories = Category.objects.annotate(
article_count=models.Count('article', filter=models.Q(article__status='published')),
total_views=models.Sum('article__view_count', filter=models.Q(article__status='published'))
).filter(
article_count__gt=0
).order_by('-article_count')
return render(request, 'category_stats.html', {'categories': categories})
# Batch operation optimization
def bulk_create_articles(request):
"""Optimized bulk article creation"""
articles_data = [
{'title': f'Article{i}', 'content': f'Content{i}', 'author_id': 1}
for i in range(1000)
]
# Use bulk_create to reduce SQL query count
articles = [Article(**data) for data in articles_data]
Article.objects.bulk_create(articles, batch_size=100)
return JsonResponse({'status': 'success'})
def bulk_update_articles(request):
"""Optimized bulk article update"""
# Get articles that need to be updated
articles = Article.objects.filter(status='draft')
# Use bulk_update to reduce SQL query count
for article in articles:
article.status = 'published'
Article.objects.bulk_update(articles, ['status'], batch_size=100)
return JsonResponse({'status': 'success'})select_related and prefetch_related
Detailed usage of select_related and prefetch_related:
python
# select_related example - for foreign key and one-to-one relationships
def select_related_examples():
"""select_related usage examples"""
# Basic usage
articles = Article.objects.select_related('author').all()
# Generated SQL: SELECT * FROM article INNER JOIN auth_user ON ...
# Multi-level relationships
articles = Article.objects.select_related('author__profile', 'category').all()
# Generated SQL: SELECT * FROM article
# INNER JOIN auth_user ON ...
# INNER JOIN user_profile ON ...
# INNER JOIN category ON ...
# Combined with filtering
published_articles = Article.objects.select_related(
'author', 'category'
).filter(
status='published'
).order_by('-created_at')[:10]
# prefetch_related example - for many-to-many and reverse foreign key relationships
def prefetch_related_examples():
"""prefetch_related usage examples"""
# Basic usage
articles = Article.objects.prefetch_related('tags').all()
# Generate two SQL queries:
# 1. SELECT * FROM article
# 2. SELECT * FROM tag INNER JOIN article_tags ON ...
# Multiple relationships
articles = Article.objects.prefetch_related('tags', 'comments').all()
# Nested preloading
articles = Article.objects.prefetch_related(
'tags',
'comments__author' # Author of comments
).all()
# Using Prefetch object for fine control
from django.db.models import Prefetch
articles = Article.objects.prefetch_related(
Prefetch(
'comments',
queryset=Comment.objects.select_related('author').filter(is_approved=True),
to_attr='approved_comments' # Custom attribute name
)
).all()
# Performance comparison example
def performance_comparison():
"""Performance comparison example"""
# Unoptimized query - N+1 problem
print("Unoptimized query:")
start_time = time.time()
articles = Article.objects.all()
for article in articles[:10]: # Only look at first 10 articles
print(f"Article: {article.title}")
print(f"Author: {article.author.username}") # Query database each time
print(f"Category: {article.category.name}") # Query database each time
for tag in article.tags.all(): # Query database each time
print(f"Tag: {tag.name}")
end_time = time.time()
print(f"Time: {end_time - start_time:.2f} seconds")
# Optimized query
print("\nOptimized query:")
start_time = time.time()
articles = Article.objects.select_related(
'author', 'category'
).prefetch_related('tags').all()[:10]
for article in articles:
print(f"Article: {article.title}")
print(f"Author: {article.author.username}")
print(f"Category: {article.category.name}")
for tag in article.tags.all():
print(f"Tag: {tag.name}")
end_time = time.time()
print(f"Time: {end_time - start_time:.2f} seconds")Database Indexes
Creating and optimizing database indexes:
python
# models.py
class Article(models.Model):
title = models.CharField(max_length=200)
slug = models.SlugField(unique=True)
author = models.ForeignKey(User, on_delete=models.CASCADE)
category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True)
content = models.TextField()
view_count = models.PositiveIntegerField(default=0)
created_at = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)
status = models.CharField(max_length=10, default='draft')
is_featured = models.BooleanField(default=False)
class Meta:
# Single field indexes
indexes = [
models.Index(fields=['slug']), # Unique index already specified in field definition
models.Index(fields=['view_count']),
models.Index(fields=['created_at']),
models.Index(fields=['status']),
models.Index(fields=['is_featured']),
]
# Composite indexes
indexes += [
models.Index(fields=['status', 'created_at']), # Status and creation time
models.Index(fields=['author', 'created_at']), # Author and creation time
models.Index(fields=['category', 'status', 'created_at']), # Category, status, and creation time
]
# Partial indexes (PostgreSQL)
# indexes += [
# models.Index(
# fields=['created_at'],
# condition=models.Q(status='published'),
# name='published_articles_created_at_idx'
# ),
# ]
# Custom index names
class Category(models.Model):
name = models.CharField(max_length=100)
slug = models.SlugField(unique=True)
class Meta:
indexes = [
models.Index(
fields=['slug'],
name='category_slug_idx' # Custom index name
),
]
# Function indexes example (PostgreSQL)
# class Article(models.Model):
# title = models.CharField(max_length=200)
#
# class Meta:
# indexes = [
# models.Index(
# models.functions.Lower('title'),
# name='article_title_lower_idx'
# ),
# ]
# Database query analysis
def analyze_queries():
"""Analyze database queries"""
from django.db import connection
from django.conf import settings
# Enable query log
settings.DEBUG = True
# Execute query
articles = Article.objects.select_related('author', 'category').filter(
status='published'
).order_by('-created_at')[:20]
# Analyze query
print(f"Executed {len(connection.queries)} queries")
for i, query in enumerate(connection.queries):
print(f"Query {i+1}: {query['sql']}")
print(f"Time: {query['time']} seconds")
print("-" * 50)
# Reset query log
connection.queries_log.clear()
# Using django-debug-toolbar to monitor queries
# Install: pip install django-debug-toolbar
# settings.py
# INSTALLED_APPS = [
# # ...
# 'debug_toolbar',
# ]
# MIDDLEWARE = [
# # ...
# 'debug_toolbar.middleware.DebugToolbarMiddleware',
# ]
# INTERNAL_IPS = [
# '127.0.0.1',
# ]Through these database optimization techniques, you can significantly improve Django application query performance and reduce database load.
Summary
Core points of Django database optimization:
- ✅ Use select_related and prefetch_related to solve N+1 query problems
- ✅ Design database indexes reasonably to improve query efficiency
- ✅ Implement batch operations to reduce database access frequency
- ✅ Use aggregate queries and annotations to reduce data transmission
- ✅ Monitor and analyze query performance to identify bottlenecks
Database optimization is an important means to improve application performance.
Next Article
We will learn about using the caching system.