阅读有关编写最佳SQL数据库查询的材料

对于sqlite和/或ios的特定帮助,这里有一些资源似乎有很好的建议。

• iOS Application Programming Guide - Tuning for Performance and Responsiveness
• SQLite Optimization FAQ
• SQLite performance tuning and optimization on embedded systems
• How to improve SQLite write performance
• 书 The Definitive Guide to SQLite 得到了很高的评价,尽管它花费了很多时间讨论您可能不需要的数据库设计。
• 还有一本全新的书,尚未出版,似乎也很有用: Professional iPhone and iPad Database Application Programming .

现在,更一般地说,要真正为任何企业数据库系统进行性能调整,我认为您需要了解以下重要方面:

• 您正在使用的系统的基本逻辑数据存储体系结构。例如,B-树、范围、页、这些数据的大小和配置、一次读取的数据量、行的最大大小(如果这是DBMS中的问题)、行外数据的处理方法(如果这是DBMS中的问题),等等。

• 表和行数据的索引、约束和基本排序:堆、聚集、非聚集、这些索引的唯一性和非唯一性、主键、唯一约束和包含的列。在所有这些索引中,无论是否允许空值,只允许一个空值,或者不允许一个空值。优等因子覆盖指数

• sargability(查找sarg,简称“搜索参数”)。

• 外键、默认值、级联删除/更新及其对插入和删除的影响。

• 空值是否需要任何存储空间,以及是否受列位置影响。存储每个数据类型所需的字节数。当存储或不存储字符串数据类型的尾随空格时。压缩数据类型与非压缩数据类型(例如浮点和十进制与整数)。聚集索引和非聚集索引中每页行(或磁盘读取的最小单位)的概念。

• 填充因子、分段、统计、索引选择性、页面拆分、转发指针。

• 当“批处理”操作可以提高性能时,以及为什么以及如何最有效地执行它。

• 内部、左侧、右侧、完全和交叉联接。半连接(存在)和反半连接(不存在)。任何其他语言特定的语法,如在MySQL中使用和在SQL Server中交叉应用/外部应用。在外部联接的ON子句中加入联接条件与将其放入WHERE子句中的效果。

• 独立子查询、相关子查询、派生表、公共表表达式、存在和不存在的理解通常会引入相关子查询,但通常在执行计划中被视为联接(半联接或反半联接)。

• 以图形或文本形式查看和理解执行计划。查看CPU的统计信息/配置文件,读取、写入和整个SQL批处理或单个语句使用的持续时间。了解执行计划和配置文件的局限性,这实际上意味着您通常必须同时使用这两者来进行优化。缓存和重用执行计划,从缓存中终止计划。参数嗅探和参数化。与这些相关的动态SQL。

• 将数据类型转换为其他数据类型或仅使用这些数据类型的相对成本。(例如,一个可靠的经验法则是使用字符串比使用数字成本更高。)

• 与基于集合的处理相比,逐行处理的成本通常过高。光标的正确使用(很少,尽管有时需要)。函数如何隐藏执行计划成本。当问题可以以集合的形式解决时,编写为每一行调用的函数是一个诱人的陷阱(尽管这很难学习如何查看,尤其是因为传统的应用程序编程倾向于训练人们按照这样的函数进行思考)。

• 搜索、扫描、范围扫描、“跳过”扫描。书签查找又称索引查找,然后使用索引查找中找到的值查找同一个表。循环、合并和哈希联接。渴望和懒惰的线轴。加入订单。估计行计数。实际行计数。

• 当查询太大,应使用临时表或其他方法拆分为多个查询时。

• 多处理器功能以及并行执行的好处和要点。

• tempdb或其他临时文件用法。临时表、表变量的生存期和范围(如果您的DB引擎有这样的变量)。是否为这些收集统计信息(在SQL Server临时表中,使用统计信息,不使用表变量)。

• 锁定、锁定粒度、锁定类型、锁升级、块、死锁。数据访问模式(如先更新、后插入、后删除)。意图、共享、独占锁。锁提示(例如,在SQL Server updlock、holdlock、readpass、tablockx中)。

• 事务和事务隔离。读提交,读未提交,可重复读,可序列化,快照,其他我现在记不起来了。

• 数据文件、文件组、单独的磁盘、事务日志、简单恢复、完全恢复、最早打开的事务,即最小日志序列号(LSN)、文件增长。

• 序列、数组、列表、标识列、窗口函数、返回的top/rownum/限制行数。

• 物化视图也称为索引视图。计算列。

• 1到1,1到0或1,1到多,多到多。

• union、union all和其他“垂直”联接。SQL Server也有except和intersect。

• 将in()列表扩展到或。将isNull()、coalesce()或其他空处理机制扩展到case语句。

• 使用distinct“修复”查询而不是处理底层问题的陷阱。

• 链接服务器如何不能很好地跨链接连接,对链接服务器的查询通常会逐行进行,即使这不明智,也可以通过链接拉取大量数据在本地执行连接。

• 在触发器中执行任何I/O或易出错任务的陷阱。触发器的作用域(无论它们是针对每行触发,还是针对每个数据操作触发一次)。

• 使前端、GUI、报告工具或其他客户机执行客户机类型工作(例如将日期或数字格式化为字符串),而不是使用DB引擎。

• 错误处理。回滚事务以及如何始终回滚到第一个事务,不管嵌套有多深,但提交只提交一个级别的工作。

其中大多数与绩效有关。有些对性能不太重要,但是(在我看来)如果您想成为一个优秀的SQL开发人员,那么这就很重要了,因为仅仅拥有快速查询还不够,它们还需要正确,并且善待他人,并正确地处理错误。如果您继续专业地编写SQL并希望改进,那么您最终将需要了解其中的大部分内容。我将从了解表组织、索引、查找和扫描以及哈希/合并/循环联接开始。我不太了解sqlite,但这些东西对于任何DBMS都是全局的。

有一件事可以帮你很大的忙,那就是确认查询包括搜索数据,就像在一本大电话簿或一系列电话簿的背面有各种索引一样,你可以查找姓名、地址、电话号码和其他信息。也许有一个反向的电话号码索引或者一个按名字组织的索引。考虑从这些物理对象获取信息的最少工作/最短时间路径将有助于您了解查询引擎在选择执行计划时的任务。这种理解可以帮助你说“等一下,为什么它要在扫描时进行搜索?”那张表很大,在X上有一个索引!”

示例场景:您有一个电话簿,按姓氏按惯例组织。后面还有一个索引,只有名字和姓氏,按名字排序。

任务1:你需要写下每个人的电话号码,名字是托尔斯坦。最佳计划:在名字索引中查找5个人的姓氏,然后在主电话簿中查找这些姓氏。您刚刚使用书签查找对聚集索引执行了非聚集索引查找。

任务2:你需要记下每个名字以a开头的人的所有电话号码。很快意识到甚至连名字索引都没有意义,你转到主电话簿,从第1页开始阅读每一页。你刚刚扫描了一张桌子。

为这两个任务选择一个好的“执行计划”(这正是我们刚刚做的)所固有的是您所拥有的一些领域知识:您知道Torstein是一个非常罕见的名字,而且可能有数万人的名字以a开头。这个领域知识相当于统计数据为查询引擎提供的信息。e.如果没有统计数据,可以选择一个糟糕的执行计划。

虽然这些一般性的提示可能并不都适用于iPhone上的sqlite,但是让这些概念在您的头脑中变得真正可靠对于使用sqlite数据库有很大的帮助。有一些原则必然是全局的,不管您使用的是什么系统(例如,理解索引永远不会是浪费的工作,因为任何没有索引的系统都可能不值得使用)。

我希望这有帮助。请随时要求澄清任何问题。如果你找不到资源,问我,我看看能给你指什么。