Django项目优化数据库操作总结
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li>合理的创建索引<ul class="second_class_ul"><li>
设置数据库持久连接</li></ul></li><li>
减少SQL的执行次数<ul class="second_class_ul"></ul></li><li>
仅获取需要的字段数据<ul class="second_class_ul"></ul></li><li>
使用批量创建、更新和删除，不随意对结果排序<ul class="second_class_ul"></ul></li></ul></div><div id="navCategory">
<h5 class="catalogue">
目录</h5>
<ul class="first_class_ul">
<li>
合理的创建索引
<ul class="second_class_ul">
<li>
设置数据库持久连接
</li>
</ul>
</li>
<li>
减少SQL的执行次数
<ul class="second_class_ul"></ul>
</li>
<li>
仅获取需要的字段数据
<ul class="second_class_ul"></ul>
</li>
<li>
使用批量创建、更新和删除，不随意对结果排序
<ul class="second_class_ul"></ul>
</li>
</ul>
</div>
<p>
参考网址:Django官方数据库优化</p>
<p>
使用 QuerySet.explain() 来了解你的数据库是如何执行特定的 QuerySet 的。</p>
<p>
你可能还想使用一个外部项目，比如 django-debug-toolbar ，或者一个直接监控数据库的工具。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
合理的创建索引</h2>
<p>
索引可能有助于加快查询速度，但是也要注意索引会占用磁盘空间，创建不必要的索引只会形成浪费。数据库表中的主键、外键、唯一键默认会创建索引。<br>
那么哪些字段需要创建索引呢？这是一个好的且令人疑惑的问题，下面列出的几条可以作为参照意见:</p>
<ul>
<li>
频繁出现在WHERE条件子句的字段(也就是Django里filter筛选的字段)</li>
<li>
经常被用来分组(group by)或者排序(order by)的字段</li>
<li>
在经常存取的多个列上创建复合索引，但要注意复合索引的顺序要按照使用的频度来决定</li>
</ul>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_635504">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py keyword">class</code> <code class="py plain">ModelName(models.Model):</code>
</div>
<div class="line number2 index1 alt1">
<code class="py spaces"> </code><code class="py comments"># 字段添加索引使用db_index</code>
</div>
<div class="line number3 index2 alt2">
<code class="py spaces"> </code><code class="py plain">name </code><code class="py keyword">=</code> <code class="py plain">models.CharField(db_index</code><code class="py keyword">=</code><code class="py color1">True</code><code class="py plain">, max_length</code><code class="py keyword">=</code><code class="py value">100</code><code class="py plain">)</code>
</div>
<div class="line number4 index3 alt1">
<code class="py spaces"> </code><code class="py keyword">class</code> <code class="py plain">Meta:</code>
</div>
<div class="line number5 index4 alt2">
<code class="py spaces"> </code><code class="py comments"># 联合唯一索引使用index_together</code>
</div>
<div class="line number6 index5 alt1">
<code class="py spaces"> </code><code class="py plain">index_together </code><code class="py keyword">=</code> <code class="py plain">(</code><code class="py string">'字段1'</code><code class="py plain">, </code><code class="py string">'字段2'</code><code class="py plain">)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
设置数据库持久连接</h3>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
减少SQL的执行次数</h2>
<p>
多次访问数据库比单次查询所有内容的效率低。因此理解并学会使用 select_related() 和 prefetch_related() 。</p>
<p>
select_related():创建一个 SQL 连接，并在 SELECT 语句中包含相关对象的字段。一般用于一对多(ForeignKey)和一对一(OneToOneField)关系</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_557360">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py comments"># 标准查询</code>
</div>
<div class="line number2 index1 alt1">
<code class="py comments"># Hits the database.</code>
</div>
<div class="line number3 index2 alt2">
<code class="py plain">e </code><code class="py keyword">=</code> <code class="py plain">Entry.objects.get(</code><code class="py functions">id</code><code class="py keyword">=</code><code class="py value">5</code><code class="py plain">)</code>
</div>
<div class="line number4 index3 alt1">
<code class="py comments"># Hits the database again to get the related Blog object.</code>
</div>
<div class="line number5 index4 alt2">
<code class="py plain">b </code><code class="py keyword">=</code> <code class="py plain">e.blog</code>
</div>
<div class="line number6 index5 alt1">
<code class="py comments"># select_related 查询</code>
</div>
<div class="line number7 index6 alt2">
<code class="py comments"># Hits the database.</code>
</div>
<div class="line number8 index7 alt1">
<code class="py plain">e </code><code class="py keyword">=</code> <code class="py plain">Entry.objects.select_related(</code><code class="py string">'blog'</code><code class="py plain">).get(</code><code class="py functions">id</code><code class="py keyword">=</code><code class="py value">5</code><code class="py plain">)</code>
</div>
<div class="line number9 index8 alt2">
<code class="py comments"># Doesn't hit the database, because e.blog has been prepopulated</code>
</div>
<div class="line number10 index9 alt1">
<code class="py comments"># in the previous query.</code>
</div>
<div class="line number11 index10 alt2">
<code class="py plain">b </code><code class="py keyword">=</code> <code class="py plain">e.blog</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
prefetch_related(): 一般用于多对一(GenericForeignKey)和多对多(ManyToManyField)关系</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_148473">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py keyword">from</code> <code class="py plain">django.db </code><code class="py keyword">import</code> <code class="py plain">models</code>
</div>
<div class="line number2 index1 alt1">
<code class="py keyword">class</code> <code class="py plain">Topping(models.Model):</code>
</div>
<div class="line number3 index2 alt2">
<code class="py spaces"> </code><code class="py plain">name </code><code class="py keyword">=</code> <code class="py plain">models.CharField(max_length</code><code class="py keyword">=</code><code class="py value">30</code><code class="py plain">)</code>
</div>
<div class="line number4 index3 alt1">
<code class="py keyword">class</code> <code class="py plain">Pizza(models.Model):</code>
</div>
<div class="line number5 index4 alt2">
<code class="py spaces"> </code><code class="py plain">name </code><code class="py keyword">=</code> <code class="py plain">models.CharField(max_length</code><code class="py keyword">=</code><code class="py value">50</code><code class="py plain">)</code>
</div>
<div class="line number6 index5 alt1">
<code class="py spaces"> </code><code class="py plain">toppings </code><code class="py keyword">=</code> <code class="py plain">models.ManyToManyField(Topping)</code>
</div>
<div class="line number7 index6 alt2">
<code class="py spaces"> </code><code class="py keyword">def</code> <code class="py plain">__str__(</code><code class="py color1">self</code><code class="py plain">):</code>
</div>
<div class="line number8 index7 alt1">
<code class="py spaces"> </code><code class="py keyword">return</code> <code class="py string">"%s (%s)"</code> <code class="py keyword">%</code> <code class="py plain">(</code>
</div>
<div class="line number9 index8 alt2">
<code class="py spaces"> </code><code class="py color1">self</code><code class="py plain">.name,</code>
</div>
<div class="line number10 index9 alt1">
<code class="py spaces"> </code><code class="py string">", "</code><code class="py plain">.join(topping.name </code><code class="py keyword">for</code> <code class="py plain">topping </code><code class="py keyword">in</code> <code class="py color1">self</code><code class="py plain">.toppings.</code><code class="py functions">all</code><code class="py plain">()),</code>
</div>
<div class="line number11 index10 alt2">
<code class="py spaces"> </code><code class="py plain">)</code>
</div>
<div class="line number12 index11 alt1">
<code class="py comments"># Good</code>
</div>
<div class="line number13 index12 alt2">
<code class="py plain">pizza.objects.</code><code class="py functions">all</code><code class="py plain">().prefetch_related(</code><code class="py string">'toppings'</code><code class="py plain">)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
仅获取需要的字段数据</h2>
<p>
使用 QuerySet.values() 和 values_list()</p>
<p>
使用 QuerySet.defer() 和 only()</p>
<p>
使用 QuerySet.count()</p>
<p>
使用 QuerySet.exists()</p>
<p>
请不要过度使用 count() 和 exists()</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
使用批量创建、更新和删除，不随意对结果排序</h2>
<p>
批量创建: 当创建对象时，尽可能使用 bulk_create() 方法来减少 SQL 查询数量。比如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_625053">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py comments"># Good</code>
</div>
<div class="line number2 index1 alt1">
<code class="py plain">Entry.objects.bulk_create([</code>
</div>
<div class="line number3 index2 alt2">
<code class="py spaces"> </code><code class="py plain">Entry(headline</code><code class="py keyword">=</code><code class="py string">'This is a test'</code><code class="py plain">),</code>
</div>
<div class="line number4 index3 alt1">
<code class="py spaces"> </code><code class="py plain">Entry(headline</code><code class="py keyword">=</code><code class="py string">'This is only a test'</code><code class="py plain">),</code>
</div>
<div class="line number5 index4 alt2">
<code class="py plain">])</code>
</div>
<div class="line number6 index5 alt1">
<code class="py comments"># Bad</code>
</div>
<div class="line number7 index6 alt2">
<code class="py plain">Entry.objects.create(headline</code><code class="py keyword">=</code><code class="py string">'This is a test'</code><code class="py plain">)</code>
</div>
<div class="line number8 index7 alt1">
<code class="py plain">Entry.objects.create(headline</code><code class="py keyword">=</code><code class="py string">'This is only a test'</code><code class="py plain">)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
批量更新: 当更新对象时，尽可能使用 bulk_update() 方法来减少 SQL 查询数。给定对象的列表或查询集：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_376846">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py comments"># Good</code>
</div>
<div class="line number2 index1 alt1">
<code class="py plain">entries[</code><code class="py value">0</code><code class="py plain">].headline </code><code class="py keyword">=</code> <code class="py string">'This is not a test'</code>
</div>
<div class="line number3 index2 alt2">
<code class="py plain">entries[</code><code class="py value">1</code><code class="py plain">].headline </code><code class="py keyword">=</code> <code class="py string">'This is no longer a test'</code>
</div>
<div class="line number4 index3 alt1">
<code class="py plain">Entry.objects.bulk_update(entries, [</code><code class="py string">'headline'</code><code class="py plain">])</code>
</div>
<div class="line number5 index4 alt2">
<code class="py comments"># Bad</code>
</div>
<div class="line number6 index5 alt1">
<code class="py plain">entries[</code><code class="py value">0</code><code class="py plain">].headline </code><code class="py keyword">=</code> <code class="py string">'This is not a test'</code>
</div>
<div class="line number7 index6 alt2">
<code class="py plain">entries[</code><code class="py value">0</code><code class="py plain">].save()</code>
</div>
<div class="line number8 index7 alt1">
<code class="py plain">entries[</code><code class="py value">1</code><code class="py plain">].headline </code><code class="py keyword">=</code> <code class="py string">'This is no longer a test'</code>
</div>
<div class="line number9 index8 alt2">
<code class="py plain">entries[</code><code class="py value">1</code><code class="py plain">].save()</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
批量插入: 当插入对象到 ManyToManyFields 时，使用带有多个对象的 add() 来减少 SQL 查询的数量。</p>
<p>
举例：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_199082">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py comments"># Good</code>
</div>
<div class="line number2 index1 alt1">
<code class="py plain">my_band.members.add(me, my_friend)</code>
</div>
<div class="line number3 index2 alt2">
<code class="py comments"># Bad</code>
</div>
<div class="line number4 index3 alt1">
<code class="py plain">my_band.members.add(me)</code>
</div>
<div class="line number5 index4 alt2">
<code class="py plain">my_band.members.add(my_friend)</code>
</div>
<div class="line number6 index5 alt1">
<code class="py comments"># Good</code>
</div>
<div class="line number7 index6 alt2">
<code class="py plain">PizzaToppingRelationship </code><code class="py keyword">=</code> <code class="py plain">Pizza.toppings.through</code>
</div>
<div class="line number8 index7 alt1">
<code class="py plain">PizzaToppingRelationship.objects.bulk_create([</code>
</div>
<div class="line number9 index8 alt2">
<code class="py spaces"> </code><code class="py plain">PizzaToppingRelationship(pizza</code><code class="py keyword">=</code><code class="py plain">my_pizza, topping</code><code class="py keyword">=</code><code class="py plain">pepperoni),</code>
</div>
<div class="line number10 index9 alt1">
<code class="py spaces"> </code><code class="py plain">PizzaToppingRelationship(pizza</code><code class="py keyword">=</code><code class="py plain">your_pizza, topping</code><code class="py keyword">=</code><code class="py plain">pepperoni),</code>
</div>
<div class="line number11 index10 alt2">
<code class="py spaces"> </code><code class="py plain">PizzaToppingRelationship(pizza</code><code class="py keyword">=</code><code class="py plain">your_pizza, topping</code><code class="py keyword">=</code><code class="py plain">mushroom),</code>
</div>
<div class="line number12 index11 alt1">
<code class="py plain">], ignore_conflicts</code><code class="py keyword">=</code><code class="py color1">True</code><code class="py plain">)</code>
</div>
<div class="line number13 index12 alt2">
<code class="py comments"># Bad</code>
</div>
<div class="line number14 index13 alt1">
<code class="py plain">my_pizza.toppings.add(pepperoni)</code>
</div>
<div class="line number15 index14 alt2">
<code class="py plain">your_pizza.toppings.add(pepperoni, mushroom)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
批量删除: 当从 ManyToManyFields 删除对象时，可以使用带有多个对象的 remove() 来减少 SQL 查询的数量。</p>
<p>
比如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_694303">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py comments"># Good</code>
</div>
<div class="line number2 index1 alt1">
<code class="py plain">my_band.members.remove(me, my_friend)</code>
</div>
<div class="line number3 index2 alt2">
<code class="py comments"># Bad</code>
</div>
<div class="line number4 index3 alt1">
<code class="py plain">my_band.members.remove(me)</code>
</div>
<div class="line number5 index4 alt2">
<code class="py plain">my_band.members.remove(my_friend)</code>
</div>
<div class="line number6 index5 alt1">
<code class="py comments"># Good</code>
</div>
<div class="line number7 index6 alt2">
<code class="py keyword">from</code> <code class="py plain">django.db.models </code><code class="py keyword">import</code> <code class="py plain">Q</code>
</div>
<div class="line number8 index7 alt1">
<code class="py plain">PizzaToppingRelationship </code><code class="py keyword">=</code> <code class="py plain">Pizza.toppings.through</code>
</div>
<div class="line number9 index8 alt2">
<code class="py plain">PizzaToppingRelationship.objects.</code><code class="py functions">filter</code><code class="py plain">(</code>
</div>
<div class="line number10 index9 alt1">
<code class="py spaces"> </code><code class="py plain">Q(pizza</code><code class="py keyword">=</code><code class="py plain">my_pizza, topping</code><code class="py keyword">=</code><code class="py plain">pepperoni) |</code>
</div>
<div class="line number11 index10 alt2">
<code class="py spaces"> </code><code class="py plain">Q(pizza</code><code class="py keyword">=</code><code class="py plain">your_pizza, topping</code><code class="py keyword">=</code><code class="py plain">pepperoni) |</code>
</div>
<div class="line number12 index11 alt1">
<code class="py spaces"> </code><code class="py plain">Q(pizza</code><code class="py keyword">=</code><code class="py plain">your_pizza, topping</code><code class="py keyword">=</code><code class="py plain">mushroom)</code>
</div>
<div class="line number13 index12 alt2">
<code class="py plain">).delete()</code>
</div>
<div class="line number14 index13 alt1">
<code class="py comments"># Bad</code>
</div>
<div class="line number15 index14 alt2">
<code class="py plain">my_pizza.toppings.remove(pepperoni)</code>
</div>
<div class="line number16 index15 alt1">
<code class="py plain">your_pizza.toppings.remove(pepperoni, mushroom)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
以上就是Django项目优化数据库操作总结的详细内容，更多关于Django项目优化数据库的资料请关注其它相关文章！</p>
<p>
原文链接：https://blog.csdn.net/gf_lvah/article/details/120141516</p>
頁:
[1]