想提升你的数据库技能, 索引绝对是你应该优先关注的领域. 应该了解各种索引类型, 它们的工作原理以及何时应该使用它们等等.

在SQLite 数据库中的 orders 表中包含了 2000 万行数据. 在讨论索引之前, 先快速看一下在没有索引的情况下, 查询是如何执行的. 目前orders 表除了主键外, 没有任何其他索引.

先从一个简单的查询开始:

SELECT customerID, SUM(orderTotal)
FROM orders
GROUP BY customerID;

数据库是如何执行这个查询的呢? 数据库将不得不扫描表中的每一行. 全表扫描在底层是什么样的呢?

SQLite 会找出哪些数据库页面包含 orders 表的行. 这些页面会从磁盘加载到内存中, 然后 customerID 和 orderTotal 会从页面中提取出来, 用于查询. 提取页面并从中提取所需值的过程会重复进行, 直到检索完所有订单数据.

注意到刚才描述的任何低效之处吗?

低效之处与**输入/输出 (I/O)**有关. 看出来了吧?

这个查询的问题在于, 必须从磁盘中拉取整个页面. QLite 中的页面是行式存储的, 这意味着数据库页面包含行中的所有列, 或者存储在页面中的所有行. 因此, 为了执行这个查询, 我们最终不得不执行 I/O 操作来获取大量我们并不真正感兴趣的数据.

能以某种方式改进这个查询吗? 当然可以, 解决方案可能并不明显. 但在尝试改进查询之前, 让我们先测量一下这个查询大约花了8秒完成.

改进大多数查询的方法是添加一个或多个索引.

为 customerID 添加一个索引. 为此, 将创建一个名为 orders_customerID_idx 的索引, 针对 orders 表的 customerID 列.

CREATE INDEX orders_customerID_idx ON orders (customerID);

我们再次运行查询, 查询花了将近30秒 才完成. 看来我们添加的索引并没有帮助, 实际上似乎让情况更糟了!

这是怎么回事? 似乎 查询规划器选择了一个低效的解决方案. 如果我们再次运行上次的查询, 但在前面加上 EXPLAIN QUERY PLAN, 你会看到 正在进行 orders 表的扫描, 并且它还 引用了我们新创建的索引.

EXPLAIN QUERY PLAN
SELECT customerID, SUM(orderTotal)
FROM orders
GROUP BY customerID;

我们的查询正在读取 orders 表的每个页面, 并且由于某些原因, 它还在读取我们新的索引, 可能是在索引和表数据之间来回跳转.

应该 删除 创建的索引吗? 可能不应该, 因为看起来很有可能其他查询 需要按 customerID 进行搜索, 这很常见.

如果创建另一个索引, 包含 customerID 和 orderTotal 两列, 这会有帮助吗? 让我们尝试一下.

在 两列 上创建我们的新索引.

CREATE INDEX orders_customerID_orderTotal_idx ON orders (customerID, orderTotal);

再次运行我们的查询.

我们的查询在 1 秒 内完成, 这比我们上次运行这个查询快了 大约 30 倍!

结果让人感到惊讶. 为什么这个索引对我们的查询有帮助呢? 因为它现在可以将 此索引用作我们查询的数据源. 换句话说, 我们的索引包含了 计算结果所需的所有数据. 因此, 不再需要扫描 orders 表, 只扫描索引来计算结果.

为什么扫描索引比扫描表更有效率呢? 因为当我们在拉取索引的页面时, 我们 只获取了我们需要的数据. 我们不必像之前全表扫描那样, 拉取所有不必要的数据. 因此, 计算结果所需的 I/O 操作大大减少了.

这种索引有一种名称, 它被称为 覆盖索引 (covering index).

添加这个索引是个好主意吗? 这是一个需要权衡的判断. 以下是你在做出决定时需要考虑的事项:

• 这是一个经常使用的查询吗? 如果你经常使用这个查询, 那么添加这个索引可能是正确的选择.

• 权衡. 例如, 拥有此索引会占用 磁盘空间. 可以使用 sqlite3_analyzer 来查看使用了多少磁盘空间, sqlite3_analyzer 默认与 SQLite 命令行界面一起安装.

sqlite3_analyzer orders.db

正如你在这里看到的, 这个新索引 消耗了大约 12.5% 的总磁盘空间, 约为 373 兆字节. 如果我们有足够的磁盘空间, 那么拥有这个索引可能完全没问题. 但磁盘空间不是唯一的考虑因素. 另一个相关的考虑因素是, 我们拥有的索引越多, 突变 (mutation) 的性能就越差. 换句话说, 插入, 更新和删除操作可能需要更长的时间才能完成, 因为每次突变都需要更新索引.

我们应该保留这个索引吗? 再次强调, 这是一个需要权衡的判断. 但更重要的是要知道, 覆盖索引可以极大地提高查询效率, 它是应该掌握的工具之一.

针对 orders 表的一个常见查询是 按采购订单 ID (purchase order ID) 查询订单. 例如, 这是一个按采购订单号查询订单的非常常见的查询:

SELECT *
FROM orders
WHERE purchaseOrderID = 'PO-1234';

这个查询 非常慢. 如果我们在这个查询前面加上 EXPLAIN QUERY PLAN, 我们会发现它很慢, 因为它正在对 orders 表进行 全表扫描. 换句话说, 它必须查看所有 2000 万行才能确定结果.

EXPLAIN QUERY PLAN
SELECT *
FROM orders
WHERE purchaseOrderID = 'PO-1234';

继续在订单表的 purchaseOrderID 列上 添加一个索引.

CREATE INDEX orders_purchaseOrderID_idx ON orders (purchaseOrderID);

立即完成, 这太棒了! 但我想指出关于 purchaseOrderID 列的一些细节. 注意到 purchaseOrderID 列很多行是 NULL.

考虑到 purchaseOrderID 索引似乎包含相当多的空值, 我们创建的采购订单索引有任何潜在问题吗?

问题在于 空值也被包含在索引中, 但用户 通常不会搜索空采购订单值. 他们通常搜索特定的采购订单号. 这意味着我们的索引正在 使用空间来存储空值, 这是浪费的, 因为用户不会搜索空采购订单.

有没有办法 节省空值消耗的浪费磁盘空间 呢? 我们可以将我们的索引更改为所谓的 部分索引 (partial index). 但在我们修复索引之前, 让我们先看一下 当前包含空值的索引的大小.

sqlite3_analyzer orders.db

该索引 消耗了大约 7.7% 的磁盘空间, 大小约为 249 兆字节.

好的, 我们 如何修复这个索引 呢?

首先, 删除采购订单索引.

DROP INDEX orders_purchaseOrderID_idx;

然后, 创建一个新索引, 但会添加一个 WHERE 子句, 指定 仅当 purchaseOrderID 不为 NULL 时才应创建索引.

CREATE INDEX orders_purchaseOrderID_not_null_idx
ON orders (purchaseOrderID)
WHERE purchaseOrderID IS NOT NULL;

再次运行我们的采购订单查询. 它的执行速度和以前一样快.

让我们看一下 新的采购订单索引占用了多少磁盘空间.

sqlite3_analyzer orders.db

它 降至大约4.1% 或 133 兆字节 大小. 因此, 我们能够在 不牺牲任何性能 的情况下, 将采购订单索引使用的空间 几乎减少了一半, 真是太棒了!

好的, 还有 最后一种索引介绍一下.

有时, 用户 按联系人姓名 (contact name) 查询 orders 表, 就像这样:

SELECT *
FROM orders
WHERE contactName = 'John Smith';

但是这个查询是 区分大小写的. 通常, 由于大小写敏感性, 用户无法从此查询中获得结果. 例如, 如果我 用小写 查询这个联系人, 不会得到结果, 因为, 它是区分大小写的. 希望使这个查询更友好和更宽容. 换句话说, 我希望这个查询以 不区分大小写 的方式工作. 你对我们如何实现这一点有什么想法吗?

如果我们这样做会怎么样? 我们 查询联系人列 (contact column) 的小写版本与我们的小写姓名匹配的订单.

SELECT *
FROM orders
WHERE LOWER(contactName) = LOWER('john smith');

这样可行, 但这 不是一个非常高效的查询. 问题在于 WHERE 子句的这一部分, 我们 调用 LOWER 函数, 必须在 表中的每一行 的 contactName 列上调用 LOWER 函数, 才能正确评估整个表达式. 因此, 实际上, 这个查询必须执行 昂贵的全表扫描 才能获得结果.

如何解决这个问题 呢? 必须 在 contactName 列上添加索引, 对吧? 认为这个 CREATE INDEX 语句对我们有效吗?

CREATE INDEX orders_contactName_idx ON orders (contactName);

添加索引, 然后再次运行我们的查询. 它 仍然是一个非常慢的查询. 为什么这个索引对我们不起作用呢? 因为它以区分大小写的方式进行索引.

此处有一个技巧, 不直接在 contactName 列上建立索引, 而是对 contactName 列的小写版本建立索引, 方法是在此处调用 LOWER 函数.

CREATE INDEX orders_lower_contactName_idx ON orders (LOWER(contactName));

现在我再次运行我们的查询. 检查一下, 速度真的很快! 酷! 顺便说一句, 这种索引称为 表达式索引 (indexed expression).