SQLite3数据库访问性能优化7个建议
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一.空间释放</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1.如何查询：</a></li><li><a href="#_lab2_0_1">2.标记含义：</a></li><li><a href="#_lab2_0_2">3.如何设置：</a></li><li><a href="#_lab2_0_3">4.注意事项：</a></li></ul><li><a href="#_label1">二.缓存大小</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_4">1.如何查询：</a></li><li><a href="#_lab2_1_5">2.标记含义：</a></li><li><a href="#_lab2_1_6">3.如何设置</a></li><li><a href="#_lab2_1_7">4.注意事项：</a></li></ul><li><a href="#_label2">三.LIKE运算符PRAGMA case_sensitive_like; </a></li><ul class="second_class_ul"><li><a href="#_lab2_2_8">1.注意事项：</a></li><li><a href="#_lab2_2_9">2.建议：</a></li></ul><li><a href="#_label3">四.LIKE运算符PRAGMA count_changes; </a></li><ul class="second_class_ul"><li><a href="#_lab2_3_10">1.注意事项：</a></li><li><a href="#_lab2_3_11">2.建议：</a></li></ul><li><a href="#_label4">五.页面大小PRAGMA page_size; </a></li><ul class="second_class_ul"><li><a href="#_lab2_4_12">注意事项：</a></li></ul><li><a href="#_label5">六.磁盘同步</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_13">1.如何查询：</a></li><li><a href="#_lab2_5_14">2.如何设置：</a></li><li><a href="#_lab2_5_15">3.参数含义：</a></li><li><a href="#_lab2_5_16">4.建议：</a></li></ul><li><a href="#_label6">七.内存模式</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_17">1.如何查询：</a></li><li><a href="#_lab2_6_18">2.如何设置：</a></li><li><a href="#_lab2_6_19">3.参数含义：</a></li><li><a href="#_lab2_6_20">4.注意事项：</a></li><li><a href="#_lab2_6_21">5.建议：</a></li></ul><li><a href="#_label7">附指令表集：</a></li><ul class="second_class_ul"></ul><li><a href="#_label8">八.总结</a></li><ul class="second_class_ul"></ul></ul></div><p><a href="https://zhida.zhihu.com/search?content_id=254047368&content_type=Article&match_order=1&q=SQLite&zd_token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJ6aGlkYV9zZXJ2ZXIiLCJleHAiOjE3NDI3ODUzOTUsInEiOiJTUUxpdGUiLCJ6aGlkYV9zb3VyY2UiOiJlbnRpdHkiLCJjb250ZW50X2lkIjoyNTQwNDczNjgsImNvbnRlbnRfdHlwZSI6IkFydGljbGUiLCJtYXRjaF9vcmRlciI6MSwiemRfdG9rZW4iOm51bGx9.ZWoe1grNg-rOzT0B4mujCC-l3SZQ1lUw2rIRMQg_29w&zhida_source=entity" rel="external nofollow" target="_blank">SQLite</a> 是一个轻量级的关系型数据库管理系统，广泛应用于嵌入式设备、移动应用和小型桌面程序中。它的一个强大特性是通过 <code>PRAGMA</code> 命令，用户可以查询和设置数据库的配置参数，以优化查询性能和数据库管理。<code>PRAGMA</code> 命令提供了多种控制数据库行为的功能，包括缓存大小、索引设置、查询优化等。在这篇文章中，我们将探讨如何使用 SQLite 的 <code>PRAGMA</code> 命令来优化查询性能，提升数据库的响应速度。</p><p>在 SQLite 中，<code>PRAGMA</code> 是一个特殊的命令，用来控制数据库的行为、查询优化、以及性能调优。<code>PRAGMA</code> 命令可以用来设置数据库的各种配置，如缓存大小、数据库模式、事务行为等。</p>
<p class="maodian"><a name="_label0"></a></p><h2><strong>一.空间释放</strong></h2>
<p class="maodian"><a name="_lab2_0_0"></a></p><p class="maodian"><a name="_lab2_1_4"></a></p><p class="maodian"><a name="_lab2_5_13"></a></p><p class="maodian"><a name="_lab2_6_17"></a></p><h3>1.如何查询：</h3>
<p>PRAGMA auto_vacuum;</p>
<p>含义：查询数据库的auto-vacuum标记。</p>
<p class="maodian"><a name="_lab2_0_1"></a></p><p class="maodian"><a name="_lab2_1_5"></a></p><h3>2.标记含义：</h3>
<p>auto-vacuum标记的含义：<br />正常情况下，当提交一个从数据库中删除数据的事务时，数据库文件不改变大小。未使用的文件页被标记并在以后的添加操作中再次使用。这种情况下使用VACUUM命令释放删除得到的空间。但是Vacuum的效率非常低！</p>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>3.如何设置：</h3>
<p>PRAGMA auto_vacuum = 0 | 1;</p>
<p>当开启auto-vacuum，也就是执行pragma auto_vacuum=1;</p>
<p>当提交一个从数据库中删除数据的事务时，数据库文件自动收缩， (VACUUM命令在auto-vacuum开启的数据库中不起作用)。数据库会在内部存储一些信息以便支持这一功能，这使得 数据库文件比不开启该选项时稍微大一些。</p>
<p class="maodian"><a name="_lab2_0_3"></a></p><p class="maodian"><a name="_lab2_1_7"></a></p><p class="maodian"><a name="_lab2_6_20"></a></p><h3>4.注意事项：</h3>
<p>只有在数据库中未建任何表时才能改变auto-vacuum标记。试图在已有表的情况下修改不会导致报错。</p>
<p>不要打开。也就是“PRAGMA auto_vacuum = 0；”执行命令。</p>
<p class="maodian"><a name="_label1"></a></p><h2>二.缓存大小</h2>
<h3>1.如何查询：</h3>
<p>PRAGMA cache_size;</p>
<p>查询SQLite一次存储在内存中的数据库文件页数。</p>
<h3>2.标记含义：</h3>
<p>每页使用约1.5K内存，缺省的缓存大小是2000. 若需要使用改变大量多行的UPDATE或DELETE命令，并且不介意SQLite使用更多的内存的话，可以增大缓存以提高性能。</p>
<p class="maodian"><a name="_lab2_1_6"></a></p><h3>3.如何设置</h3>
<p>PRAGMA cache_size = Number-of-pages;</p>
<p>修改SQLite一次存储在内存中的数据库文件页数。</p>
<h3>4.注意事项：</h3>
<p>当使用cache_size pragma改变缓存大小时，改变仅对当前对话有效，当数据库关闭重新打开时缓存大小恢复到缺省大小。要想永久改变缓存大小，使用default_cache_size pragma.</p>
<p>修改为8000，也就是执行命令“PRAGMA cache_size =8000；”即可；<br /> </p>
<p class="maodian"><a name="_label2"></a></p><h2>三.LIKE运算符PRAGMA case_sensitive_like; </h2>
<p>PRAGMA case_sensitive_like = 0 | 1;</p>
<p><strong>标记含义：</strong>LIKE运算符的缺省行为是忽略latin1字符的大小写。因此在缺省情况下'a' LIKE 'A'的值为真。可以通过打开 case_sensitive_like pragma来改变这一缺省行为。当启用case_sensitive_like，'a' LIKE 'A'为假而'a' LIKE 'a'依然为真。</p>
<p class="maodian"><a name="_lab2_2_8"></a></p><p class="maodian"><a name="_lab2_3_10"></a></p><h3>1.注意事项：</h3>
<p><strong>SQLite3.6.22版本不支持。</strong></p>
<p class="maodian"><a name="_lab2_2_9"></a></p><p class="maodian"><a name="_lab2_3_11"></a></p><h3>2.建议：</h3>
<p>打开。也就是执行命令“PRAGMA case_sensitive_like = 1;”命令。不然搜索中文字串会出错。</p>
<p class="maodian"><a name="_label3"></a></p><h2>四.LIKE运算符PRAGMA count_changes; </h2>
<p>PRAGMA count_changes = 0 | 1;<br />查询或更改count-changes标记。</p>
<p>正常情况下INSERT, UPDATE和DELETE语句不返回数据。</p>
<p>当开启count-changes，以上语句返回一行含一个整数值的数据——该语句插入，修改或删除的行数。</p>
<h3>1.注意事项：</h3>
<p>返回的行数不包括由触发器产生的插入，修改或删除等改变的行数。</p>
<h3>2.建议：</h3>
<p>打开，便于调试。也就是执行“PRAGMA count_changes = 1；”该命令。</p>
<p class="maodian"><a name="_label4"></a></p><h2>五.页面大小PRAGMA page_size; </h2>
<p>PRAGMA page_size = bytes;<br />查询或设置page-size值。</p>
<p class="maodian"><a name="_lab2_4_12"></a></p><h3>注意事项：</h3>
<p>只有在未创建数据库时才能设置page-size。页面大小必须是2的整数倍且大于等于512小于等于8192。</p>
<p>上限可以通过在编译时修改宏定义SQLITE_MAX_PAGE_SIZE的值来改变。上限的上限是32768。<br /> </p>
<p class="maodian"><a name="_label5"></a></p><h2>六.磁盘同步</h2>
<h3>1.如何查询：</h3>
<p>PRAGMA synchronous;</p>
<p><strong>指令含义</strong>：查询"synchronous"标记的设定，返回整数值；</p>
<p class="maodian"><a name="_lab2_5_14"></a></p><p class="maodian"><a name="_lab2_6_18"></a></p><h3>2.如何设置：</h3>
<p>PRAGMA synchronous = FULL; (2) </p>
<p>PRAGMA synchronous = NORMAL; (1) <br />PRAGMA synchronous = OFF; (0)</p>
<p class="maodian"><a name="_lab2_5_15"></a></p><p class="maodian"><a name="_lab2_6_19"></a></p><h3>3.参数含义：</h3>
<p>当synchronous设置为FULL (2), SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。</p>
<p>当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停，但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上，在这种情况 下很可能你的硬盘已经不能使用，或者发生了其他的不可恢复的硬件错误。</p>
<p>设置为synchronous OFF (0)时，SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃， 数据不会损伤，但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面，在synchronous OFF时 一些操作可能会快50倍甚至更多。<br />在SQLite 2中，缺省值为NORMAL.而在3中修改为FULL。</p>
<p class="maodian"><a name="_lab2_5_16"></a></p><h3>4.建议：</h3>
<p>如果有定期备份的机制，而且少量数据丢失可接受，用OFF。</p>
<p class="maodian"><a name="_label6"></a></p><h2>七.内存模式</h2>
<h3>1.如何查询：</h3>
<p>PRAGMA temp_store;</p>
<p><strong>指令含义</strong>：查询"temp_store"参数的设置；</p>
<h3>2.如何设置：</h3>
<p>PRAGMA temp_store = DEFAULT; (0) <br />PRAGMA temp_store = FILE; (1) <br />PRAGMA temp_store = MEMORY; (2)</p>
<h3>3.参数含义：</h3>
<p>当temp_store设置为DEFAULT (0),使用编译时的C预处理宏 TEMP_STORE来定义储存临时表和临时索引的位置。</p>
<p>当设置为FILE (1)，则存放于文件中。temp_store_directorypragma 可用于指定存放该文件的目录。</p>
<p>当设置为MEMORY (2)，临时表和索引则存放于内存中。</p>
<h3>4.注意事项：</h3>
<p>当改变temp_store设置，所有已存在的临时表，索引，触发器及视图将被立即删除。</p>
<p class="maodian"><a name="_lab2_6_21"></a></p><h3>5.建议：</h3>
<p>使用2，即内存模式。</p>
<p class="maodian"><a name="_label7"></a></p><h2>附指令表集：</h2>
<table><tbody><tr><td><p><strong>序号</strong></p></td><td><p><strong>指令</strong></p></td><td><p><strong>含义</strong></p></td><td><p><strong>缺省值</strong></p></td></tr><tr><td><p><strong>1</strong></p></td><td><p>auto_vacuum</p></td><td><p><strong>空间释放</strong></p></td><td><p><strong>0</strong></p></td></tr><tr><td><p><strong>2</strong></p></td><td><p>cache_size</p></td><td><p><strong>缓存大小</strong></p></td><td><p><strong>2000</strong></p></td></tr><tr><td><p><strong>3</strong></p></td><td><p>case_sensitive_like</p></td><td><p><strong>LIKE大小写敏感</strong></p></td><td><p><strong>（注意：SQLite3.6.22不支持）</strong></p></td></tr><tr><td><p><strong>4</strong></p></td><td><p>count_changes</p></td><td><p><strong>变更行数</strong></p></td><td><p><strong>0</strong></p></td></tr><tr><td><p><strong>5</strong></p></td><td><p>page_size</p></td><td><p><strong>页面大小</strong></p></td><td><p><strong>1024</strong></p></td></tr><tr><td><p><strong>6</strong></p></td><td><p>synchronous</p></td><td><p><strong>硬盘大小</strong></p></td><td><p><strong>2</strong></p></td></tr><tr><td><p><strong>7</strong></p></td><td><p>temp_store;</p></td><td><p><strong>内存模式</strong></p></td><td><p><strong>0</strong></p></td></tr></tbody></table>
<p class="maodian"><a name="_label8"></a></p><h2>八.总结</h2>
<p>SQLite 的<code>PRAGMA</code>命令为开发者提供了灵活的配置选项，可以显著提升数据库的查询和写入性能。通过调整缓存大小、同步模式、WAL 模式、<a href="https://zhida.zhihu.com/search?content_id=254047368&content_type=Article&match_order=1&q=%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96&zd_token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJ6aGlkYV9zZXJ2ZXIiLCJleHAiOjE3NDI3ODUzOTUsInEiOiLntKLlvJXkvJjljJYiLCJ6aGlkYV9zb3VyY2UiOiJlbnRpdHkiLCJjb250ZW50X2lkIjoyNTQwNDczNjgsImNvbnRlbnRfdHlwZSI6IkFydGljbGUiLCJtYXRjaF9vcmRlciI6MSwiemRfdG9rZW4iOm51bGx9.O7xvF1JswwcGLv__CuRAJAcNjEaukvPtoNywpaNCJ5U&zhida_source=entity" rel="external nofollow" target="_blank">索引优化</a>等配置，你可以针对特定的应用场景对 SQLite 进行定制化优化。合理使用 <code>PRAGMA</code> 命令不仅能提高性能，还能确保数据库在高负载环境下的稳定性和可靠性。</p>
頁:
[1]