Shulou(Shulou.com)06/03 Report--

This article mainly introduces the Django project optimization database example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Use QuerySet.explain () to understand how your database performs a specific QuerySet.

You may also want to use an external project, such as django-debug-toolbar, or a tool that directly monitors the database.

Create an index reasonably

Indexes may help speed up queries, but be aware that indexes take up disk space, and creating unnecessary indexes can only be wasteful. The primary key, foreign key, and unique key in the database table are created by default.

So which fields need to be indexed? This is a good and puzzling question, and the following can be used as a reference:

Fields that appear frequently in the WHERE conditional clause (that is, fields filtered by filter in Django)

Fields that are often used to group (group by) or sort (order by)

Create a composite index on multiple columns that are frequently accessed, but note that the order of the composite index is determined according to the frequency of use

Class ModelName (models.Model): # Field add index using db_index name = models.CharField (db_index=True, max_length=100) class Meta: # Joint unique index sets database persistent connections using index_together index_together = ('field 1', 'field 2')

Reduce the number of times SQL is executed

Accessing the database multiple times is less efficient than querying all the contents at once. So understand and learn to use select_related () and prefetch_related ().

Select_related (): create a SQL connection and include fields of related objects in the SELECT statement. Commonly used in one-to-many (ForeignKey) and one-to-one (OneToOneField) relationships

# Standard query # Hits the database.e = Entry.objects.get (id=5) # Hits the database again to get the related Blog object.b = e.blog# select_related query # Hits the database.e = Entry.objects.select_related ('blog'). Get (id=5) # Doesn't hit the database, because e.blog has been prepopulated# in the previous query.b = e.blog

Prefetch_related (): commonly used in many-to-one (GenericForeignKey) and many-to-many (ManyToManyField) relationships

From django.db import modelsclass Topping (models.Model): name = models.CharField (max_length=30) class Pizza (models.Model): name = models.CharField (max_length=50) toppings = models.ManyToManyField (Topping) def _ _ str__ (self): return "% s (% s)"% (self.name, "," .join (topping.name for topping in self.toppings.all ()) ) # Goodpizza.objects.all () .prefetch_related ('toppings') to get only the field data you need

Use QuerySet.values () and values_list ()

Use QuerySet.defer () and only ()

Use QuerySet.count ()

Use QuerySet.exists ()

Please do not overuse count () and exists ()

Use batch creation, update and deletion, and do not sort the results at will

Batch creation: when creating objects, use the bulk_create () method as much as possible to reduce the number of SQL queries. For example:

# GoodEntry.objects.bulk_create ([Entry (headline='This is a test'), Entry (headline='This is only a test'),]) # BadEntry.objects.create (headline='This is a test') Entry.objects.create (headline='This is only a test')

Batch updates: when updating objects, use the bulk_update () method as much as possible to reduce the number of SQL queries. A list or query set of a given object:

# Goodentries [0] .headline = 'This is not a test'entries [1] .headline =' This is no longer a test'Entry.objects.bulk_update (entries, ['headline']) # Badentries [0] .headline =' This is not a test'entries [0] .save () entries [1] .headline = 'This is no longer a test'entries [1] .save ()

Bulk insert: when inserting objects into ManyToManyFields, use add () with multiple objects to reduce the number of SQL queries.

For example:

# Goodmy_band.members.add (me, my_friend) # Badmy_band.members.add (me) my_band.members.add (my_friend) # GoodPizzaToppingRelationship = Pizza.toppings.throughPizzaToppingRelationship.objects.bulk_create ([PizzaToppingRelationship (pizza=my_pizza, topping=pepperoni), PizzaToppingRelationship (pizza=your_pizza, topping=pepperoni), PizzaToppingRelationship (pizza=your_pizza, topping=mushroom),], ignore_conflicts=True) # Badmy_pizza.toppings.add (pepperoni) your_pizza.toppings.add (pepperoni, mushroom)

Bulk deletion: when deleting objects from ManyToManyFields, you can use remove () with multiple objects to reduce the number of SQL queries.

For example:

# Goodmy_band.members.remove (me, my_friend) # Badmy_band.members.remove (me) my_band.members.remove (my_friend) # Goodfrom django.db.models import QPizzaToppingRelationship = Pizza.toppings.throughPizzaToppingRelationship.objects.filter (Q (pizza=my_pizza, topping=pepperoni) | Q (pizza=your_pizza, topping=pepperoni) | Q (pizza=your_pizza, topping=mushroom). Delete () # Badmy_pizza.toppings.remove (pepperoni) your_pizza.toppings.remove (pepperoni, mushroom) Thank you for reading this article carefully I hope the article "sample Analysis of Django Project Optimization Database" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.