iphone - 阅读有关编写最佳SQL数据库查询的 Material

Question

我不知道我的数据库查询是否干净，高效等等。充其量，我可以从数据库中获得所需的东西，但没有太多。

我应该读什么书（或网站）以前进？我想了解各种条件和可能会查询的关键字（例如JOIN，IN，WHERE等）的性能成本。

我在iOS设备上使用SQLite。

Answer 1

对于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中是个问题），使用out-of行数据（同样，如果这是DBMS中的问题）。
表和行数据的索引，约束和基本顺序：这些索引的堆，集群，非集群，唯一和非唯一性，主键，唯一约束，包括的列。在所有这些索引中，是否允许使用null，仅允许一个null或不允许。唯一符。覆盖指数。
SARGability（查找SARG，它是“ Search ARGument”的缩写）。
外键，默认值，级联删除/更新，它们对插入和删除的影响。
NULL是否需要任何存储空间以及是否受列位置影响。存储每种数据类型所需的字节数。字符串数据类型是否存储尾随空格。打包与非打包数据类型（例如，浮点数和十进制与整数）。集群索引和非集群索引中每页的行数（或磁盘读取的最小单位）的概念。
填充因子，碎片，统计信息，索引选择性，页面拆分，转发指针。
“分批”操作可以提高性能，原因以及提高效率的方法。
内，左，右，全和交叉联接。半联接（EXISTS）和反半联接（NOT EXISTS）。任何其他特定于语言的语法，例如mySql中的USING和SQL Server中的CROSS APPLY / OUTER APPLY。将连接条件放在外部连接的ON子句中与将其放在WHERE子句中的效果。
独立子查询，相关子查询，派生表，公用表表达式，应了解EXISTS和NOT EXISTS通常看起来会引入相关子查询，但在执行计划中通常将其视为联接（半联接或反半联接）。
以图形或文本方式查看和了解执行计划。查看CPU的统计信息/配置文件，读取，写入以及整个SQL批处理或单个语句使用的持续时间。了解执行计划和配置文件的局限性，实际上这意味着您通常必须同时使用两者来进行优化。缓存和重用执行计划，缓存中的计划到期。参数嗅探和参数化。与这些有关的动态SQL。
将数据类型转换为其他数据类型或仅使用这些数据类型的相对成本。 （例如，一条可靠的经验法则是，使用字符串比使用数字更昂贵。）
与基于集合的方法相比，逐行处理的成本通常很高。游标的正确用法（罕见，尽管有时需要）。功能如何隐藏执行计划成本。当问题可以成组解决时，编写函数的诱人陷阱会逐行调用（尽管要学会如何看待它会很棘手，特别是因为传统的应用程序编程往往会训练人们根据此类函数进行思考） 。
搜寻，扫描，范围扫描，“跳过”扫描。使用在索引查找中找到的值，将书签查找又称为索引查找，然后是表查找到同一表。循环，合并和哈希联接。渴望和懒惰的线轴。加入订单。估计的行数。实际行数。
当查询太大时，应使用临时表或其他方式将其拆分为多个查询。
多处理器功能以及并行执行的优点和缺点。
Tempdb或其他临时文件的用法。临时表和表变量的寿命和范围（如果您的数据库引擎有）。是否收集这些统计信息（在SQL Server临时表中使用统计信息，而表变量不收集）。
锁定，锁定粒度，锁定类型，锁定升级，块，死锁。数据访问模式（如UPDATE首先，INSERT其次，DELETE最后）。意向，共享，排他锁。锁定提示（例如，在SQL Server UPDLOCK，HOLDLOCK，READPAST，TABLOCKX中）。
事务和事务隔离。读已提交，未提交，可重复读，可序列化，快照以及其他我现在不记得的内容。
数据文件，文件组，单独的磁盘，事务日志，简单恢复，完全恢复，最早的打开事务（也称为最小日志序列号（LSN）），文件增长。
序列，数组，列表，标识列，窗口函数，TOP /行数/返回的限制行数。
物化视图又称为索引视图。计算列。
1比1、1比0或1比1，很多对很多。
UNION，UNION ALL和其他“垂直”联接。 SQL Server也具有EXCEPT和INTERSECT。
将IN（）列表扩展为OR。将IsNull（），Coalcece（）或其他null处理机制扩展为CASE语句。
使用DISTINCT来“修复”查询而不是处理基本问题的陷阱。
链接服务器如何无法很好地通过链接进行联接，对链接服务器的查询通常会逐行进行，即使不明智，也可以跨链接提取大量数据以在本地执行联接。
在触发器中执行任何I / O或容易出错的任务的陷阱。触发器的范围（是为每行触发一次还是为每个数据操作触发一次）。
使前端，GUI，报告工具或其他客户端代替数据库引擎来执行客户端类型的工作（例如，将日期或数字格式化为字符串）。
错误处理。回滚事务以及无论嵌套深度如何，回滚事务始终会回滚到第一个事务，但COMMIT只提交一个工作级别。


其中大多数与性能有关。有些对性能不太重要，但是（我认为）对于要成为一名优秀的SQL开发人员来说很重要，因为仅仅拥有快速的查询是不够的，他们还需要正确，与他人打交道并与他人打交道。正确地故障。如果您继续专业地编写SQL并想进行改进，那么您最终将需要了解大多数这些知识。我将从了解表组织，索引，查找和扫描以及哈希/合并/循环联接开始。我对SQLite不太了解，但是这些对于任何DBMS都是全局的。

可能对您有很大帮助的一件事是认识到查询涉及搜索数据，就像您在大型电话簿或背面有各种索引的电话簿系列中查找姓名，地址，电话号码和其他内容一样。可能有反向电话号码索引或按名字组织的索引。考虑最少的工作/最短的路径来从这些物理对象获取信息将帮助您了解查询引擎在选择执行计划时的任务。这种理解将帮助您说出诸如“等等，为什么它应该在进行搜索时进行扫描？该表非常大，并且在X上具有索引！”之类的东西。

方案示例：您有一个电话簿，按姓氏照常组织。您还将在后面有一个索引，其中只有名字和姓氏，并按名字排序。

任务1：您需要写下每个人的所有电话号码，并使用Torstein的名字。最佳计划：在名字索引中查找具有该名字的5个人的姓氏，然后在主电话簿中查找这些姓氏。您只是使用针对聚集索引的书签查找进行了非聚集索引查找。

任务2：您需要写下每个名字都以A开头的人的所有电话号码。迅速意识到即使转向名字索引也没有任何意义，您转向主电话簿并仅阅读每一页从第1页开始。您刚刚进行了表格扫描。

为这两项任务选择一个好的“执行计划”的本质（这就是我们刚刚做的）是您拥有的一些领域知识：您知道Torstein是一个非常罕见的名字，并且可能有成千上万的人名称以A开头。该领域知识等同于为查询引擎提供的统计信息。如果没有统计信息，则可能会选择错误的执行计划。

尽管这些常规技巧可能并不全部适用于iPhone上的SQLite，但使您牢记这些概念将对使用SQLite数据库有巨大帮助。无论您使用的是哪种系统，都有某些原则必定是全局性的（例如，了解索引永远不会浪费精力，因为任何没有索引的系统都可能不值得使用）。

我希望这有帮助。随时要求澄清。如果您找不到资源，请询问，我会为您指出的。