查询规划（SQLite）

(Android系统集成了轻量级的数据库：SQLite（其源代码解压后的大小为5M多），因此，在移动设备上操作数据库尤其需要考虑数据库优化的问题，以下是对官方文档中关于Query Planning一章的翻译，不当之处，敬请指出，谢谢)

SQL语句的最佳功能（在其所有的实现，而不仅仅是SQLite的）是，它是一种声明性语言，而不是一种程序语言。当使用SQL编程，你告诉系统你要计算什么，而不是如何计算它。需要清楚的任务就是SQL数据引擎如何将任务委派给查询规划子系统。缓解程序员设计具体查询算法的苦差，减少工作量，减少犯错的次数。

本文档的目的是为SQL新手提供相关的背景知识，以帮助他们了解在SQLite背后发生了什么，从而使它更容易为程序员创建，这将有助于SQLite的查询规划的指标挑选最好的方案。

1.0搜索

1.1无指数表

SQLite中的每个表包含一个唯一的整数键（ROWID或INTEGER PRIMARY KEY）和是大于等于零行的内容。这些数据行存储具有逻辑结构，并且使得ROWID不断升高。本文使用了一个名为“FruitsForSale”的表作为例子，它涉及各种水果的种植的国家和它们在市场上的单位价格表。创建语句如下：

CREATE TABLE FruitsForSale(

  Fruit TEXT,

  State TEXT,

  Price REAL

);

这些数据可能具有逻辑的存在磁盘上，如图1所示：

图 1 FruitsForSale的逻辑存储

在本例中，值得关注的点是：ROWID并不是连续的，但是它们被排列好了。SQLite通常从1开始创建ROWID，并且每次增加1。但是如果出现行被删除，ROWID的序列中就会出现空隙，并且如果希望，程序可以控制ROWID的分配，所以，插入数据时，并非一定要插入到数据表的底部。但不论如何，ROWID始终都是唯一的，并且严格按升序排列。

现在，假设你想查看一下peach的价格。查询语句将会是这样：

SELECT price FROM fruitsforsale WHERE fruit='Peach';

为了满足查询，SQLite必须读取表中的每一行数据，检查fruit行是否等于peach，如果相等，输出该行对应的价格。该过程可以用图2来说明。这叫做全表扫描，因为表中的所有内容都需要被检查以找出感兴趣的行，对于只有7行的表来说，这不是什么大事，但是对于700万行的表来说，全表扫描将导致以M字节计的内容被读取只为了找到8字节的数字，因为这个原因，应该竭力避免全表扫描。

图 2 全表扫描

1.2 通过ROWID来查询

一个避免全表扫描的技术就是通过ROWID来查询（或者是通过主键），比如：需要查询peach的价格，可以通过ROWID=4来查询：

SELECT price FROM fruitsforsale WHERE rowid=4;

因为信息在表中存储时，是按照ROWID的顺序来存储的，所以SQLite可以在ROWID进行二分查找来找到正确的行。如果数据表包含N个元素，找到正确的行的时间复杂度将从之前全表扫描时的N降低至logN，如果数据表包含1000万个元素，查询将要比之前快100万倍。

图 3 通过ROWID来查询

1.3 通过索引查询

通过ROWID查询的问题在于你可能并不关系第4行对应的价格是多少，而你真正关心的是peach的价格是多少。在这种情况下，ROWID似乎就没有用啦。

为了让查询更加高效，我们可以为FruitsForSale表中的fruit列添加索引：

CREATE INDEX idx1 ON fruitsforsale(fruit);

索引就是另外一个与原始FruitsForSale表有相似之处的表，它的内容在ROWID之前（本例中就是fruit列），并且所有的行都以一个满意的顺序排列。表4给出了idx1的逻辑视图，fruit是排列其他元素的主键，ROWID为次键。

图 4 fruit列的索引

有了新的索引，我们可以为查询peach的价格采用另外的方案：

SELECT price FROM fruitsforsale WHERE fruit='Peach';

查询开始时，首先在idx1上做一个二分查找来匹配fruit=peach，SQLite能在索引上做二分查找而不是原始数据表中，主要是因为idx1索引是根据fruit列进行排序好的。在索引idx1中找到fruit=peach，数据库引擎将会提取该行的ROWID，然后在原始的数据表fruitsforsale 中做一个分离的二分查找找到原始行中包含fruit=peach的行，由此，SQLite的可提取该列的价格值，该过程如图5所示。

图 5 通过索引查找peach的价格

使用上面的方法，SQLite将使用两次二分查找找到peach的价格，但对一个用于大量行的数据来说，这仍然要比全表扫描要快得多。

1.4 多个结果行

在前面fruit=peach的查询中，结果收窄到仅有一行。但同样的技术却可以用来获取多行结果，假设我们想要获取orange的价格：

SELECT price FROM fruitsforsale WHERE fruit='Orange'

图 6 通过索引查找orange的价格

在这种情况下，SQLite仍然只需要执行一个二叉搜索在索引中找到fruit=peach的入口，然后提取出rowid，然后通过此rowid在原始表中进行二叉搜索，从中输出价格。SQLite引擎将进入索引指向的下一列重复fruit=peach的过程，而不是退出。由于下一行通常定义在于当前行相同的数据页上，所以下一行的搜索比二叉搜索要耗费少的多，实际上，由于该花费相对于二叉搜索来讲，实在是太小，经常忽略。所以上述搜索的总的代价就是3个二叉搜索。如果输入行数为K，表中的总行数为N，那么整个查询过程的花费为(K+1)*logN。

1.5 多重AND连接+WHERE+子句条款

接下来，假设你并不想要知道所有的orange的价格，仅仅想要知道加利福利亚产的orange的价格。正确的查询语言如下：

SELECT price FROM fruitsforsale WHERE fruit='Orange' AND state='CA'

图 7 通过索引查询加利福利亚产的orange的价格

这个查询采用的一个方案就是：通过fruit=orange找到所有包含orange的行，然后过滤掉不是加利福利亚产的orange的行。这种处理方法如图7所示。在大多数情况下，这是一个完全合理的做法。但是，SQLite引擎需要做一个极大的二叉搜索来排除FL产的orange，这并不是我们所期望的那么高的效率，尽管对于很多应用，这已经足够了。

设想一下，除了在orange列上建立索引，那么是不是还可以在state列上也建立索引呢？

CREATE INDEX Idx2 ON fruitsforsale(state);

图 8 state列上的索引

state列的索引只在fruit列的索引查找得到很多行的时候使用，Idx2与Idx1的区别仅仅是rowid的前一列由fruit变成了state。

通过使用Idx2，SQLite可以通过另外一种方式来查询产自加利福利亚的orange，即先找到所有产自加利福利亚的水果，然后再从中筛选出那些是orange。

图 9 图 7 通过索引查询加利福利亚产的orange的价格

使用Idx2而不是Idx1能够使SQLite搜素到不同的行，但得到了相同的答案，但依然做了同样多的工作，所以在这种情况下，Idx2并没有带来性能上的提升。

最后的两种搜索带来了相同的时间复杂度，那么SQLite引擎会选择哪一种索引呢？Idx2还是Idx1？如果ANALYZE命令在数据库中运行了，那么SQLite将有机会搜集所有可能方案的数据，然后SQlite知道了Idx1通常将搜索收窄至单行，然而Idx2通常会将搜索收窄至两行，所以，在其他情况相同时，SQLite将会选择Idx1来收窄搜索的结果，让其尽可能的少，这是在ANALYZE运行并且能够提供分析数据的情况下，否则，SQLite的选择将会是任意的。

未完待续。。。