修复 SQLite 中的慢查询
当应用开始变慢时, 罪魁祸首往往就是数据库查询. 在 SQL Lite 中, 找到性能不佳的查询通常可以通过设置良好的日志记录来完成. 但是, 当已经识别出一个慢查询后, 如何找到并修复导致速度缓慢的根本原因呢?
手动调查慢查询对于经验丰富的开发者来说通常效果很好, 因为他们往往对查找方向有很好的直觉. 然而, 我们不必完全依赖手动排查. 有一些优秀的性能分析工具可供我们使用, 这些工具对于 SQL 新手以及处理复杂查询的经验丰富的开发者来说都非常有帮助.
那么, SQLite 提供了哪些性能分析工具呢? 其中最有用的工具之一就是 EXPLAIN QUERY PLAN 命令.
使用 EXPLAIN QUERY PLAN
如何使用 EXPLAIN QUERY PLAN 命令? 只需将短语 explain query plan 放在查询语句的前面, 就像这样:
EXPLAIN QUERY PLAN SELECT * FROM Customers WHERE CustomerID = 'ALFKI';
让我们在一个 Northwind 数据库实例上使用这个性能分析工具. 顺便说一句, 我们使用的这个数据库没有用户定义的索引, 因此我们应该会看到一些查询性能问题.
在终端中, 我输入 sqlite3, 这是 SQL Lite 自带的命令行实用工具, 并指定 Northwind 数据库的名称:
sqlite3 northwind.db
接下来, 输入刚才看到的查询, 并在前面加上命令 explain query plan. 当我按下回车键时, 我们没有看到查询返回的数据, 而是看到了对查询计划的描述, 如下所示:
QUERY PLAN
`--SEARCH TABLE Customers USING PRIMARY KEY (rowid=?)
这是什么意思呢? 对于查询中的每个表或视图, 都会看到这样的一行, 它以 SEARCH 开头, 或者以 SCAN 开头(稍后将看到一个 SCAN 的例子).
SEARCH 这个词是什么意思? SEARCH 意味着正在使用索引, 查询性能良好. 但是, 我刚才说过, 这里没有用户定义的索引. 那么这个索引从哪里来的呢? 可以通过查看名称来找到一些线索: PRIMARY KEY.
sqlite 会自动生成一些索引, 之所以这么做, 使用 .schema 命令继续探索:
.schema Customers
我们会看到 Customers 表的 CREATE TABLE 语句:
CREATE TABLE Customers(
CustomerID TEXT NOT NULL,
CompanyName TEXT NOT NULL,
ContactName TEXT,
ContactTitle TEXT,
Address TEXT,
City TEXT,
Region TEXT,
PostalCode TEXT,
Country TEXT,
Phone TEXT,
Fax TEXT,
PRIMARY KEY (CustomerID)
);
.schema 命令会显示与 Customers 表关联的所有 CREATE 语句, 更具体的说, 它会显示用于创建该表的 CREATE TABLE 语句, 以及该表的任何 CREATE INDEX 语句(如果有).
有 CREATE TABLE 语句, 但没有看到任何 CREATE INDEX 语句.
PRIMARY KEY (CustomerID) 约束是 sqlite 自动生成索引的原因. 因为执行主键约束的唯一方法是在构成主键的列上建立唯一索引.
理解 SCAN
对 Customers 表运行另一个查询, 按客户名称搜索客户. 我将输入查询:
EXPLAIN QUERY PLAN SELECT * FROM Customers WHERE CompanyName = 'Island Trading';
并在查询前面加上 explain query plan. 查询计划显示是对 Customers 表进行 SCAN 操作.
QUERY PLAN
`--SCAN TABLE Customers
SCAN 意味着此查询没有可用的索引. 因此, 必须扫描整个表才能找到所需的客户. SCAN, 这是一个危险信号, 因为它很可能是导致查询缓慢的罪魁祸首.
添加索引来优化查询
如何改进这个查询呢? 方法显而易见: 只需在 CompanyName 列上添加一个索引, 慢查询问题就解决了.
CREATE INDEX idx_company_name ON Customers (CompanyName);
再次分析按客户名称查询的语句, 看到查询计划是使用新创建的索引 idx_company_name 进行 SEARCH 操作.
EXPLAIN QUERY PLAN SELECT * FROM Customers WHERE CompanyName = 'Island Trading';
输出:
QUERY PLAN
`--SEARCH TABLE Customers USING INDEX idx_company_name (CompanyName=?)
SCAN 和新的基于 SEARCH 的查询计划之间的性能差异是什么? SCAN 是 O(n) 操作, 而 SEARCH 是 O(log n) 操作. 如果表中有 100 万行, SCAN 将需要查看所有 100 万行.
索引 SEARCH 只需要访问大约 20 行就能找到结果. 性能影响是巨大的, 这可能是查询耗时几微秒与几秒之间的差别.
打开计时功能, 然后再次运行最后一个查询:
.timer on
SELECT * FROM Customers WHERE CompanyName = 'Island Trading';
执行时间是以微秒为单位的. 如果删除刚刚创建的索引, 然后再次运行查询:
DROP INDEX idx_company_name;
SELECT * FROM Customers WHERE CompanyName = 'Island Trading';
执行时间增加了几个数量级.
使用 .expert 模式获取索引建议
通过添加索引来解决慢查询问题是个常识. 但是, 有时应该添加哪个索引并不那么明显, 这可能是因为查询非常复杂, 或者SQL经验不丰富.
如果数据库可以建议应该添加哪些索引以提高查询性能, 那岂不是很棒?
SQLite有一个点命令可以建议添加索引以提高查询性能. 输入点命令 .expert 并按下回车.
.expert
目前还没有任何作用. 但是请注意, 当再次运行查询时会发生什么:
SELECT * FROM Customers WHERE CompanyName = 'Island Trading';
基本上, 我们在这里看到了一个建议, 建议在 Customers 表的 CompanyName 列上添加索引. 如果存在此索引, 则查询计划将是使用该建议索引的 SEARCH 操作.
我们按照这里的建议添加一个索引.
CREATE INDEX customer_company_name_idx ON Customers (CompanyName);
遵照专家的建议, 给它起了一个更有意义的名字 customer_company_name_idx. 再次分析按客户名称查询的语句, 看到这次查询计划是使用新创建的索引搜索客户.
复杂查询的索引优化
看一个更复杂的查询, 连接 Customers 和 Orders 表, 并查找特定客户的所有订单.
EXPLAIN QUERY PLAN
SELECT *
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE CompanyName = 'Island Trading');
在查询计划中看到了两行.
QUERY PLAN
`--SCAN TABLE Orders
`--SCAN TABLE Customers
看到任何潜在的问题吗? 再次强调, 任何时候看到表 SCAN 操作(就像我们在这里看到的那样), 需要知道我们正在处理 O(n) 操作, 数据稍多, 都希望使用索引来实现 log n 的性能.
如果这是一个常见的查询, 可能应该添加一个索引. 应该添加什么索引? 可以再次使用 .expert 模式来获得建议的索引, 先自己分析一下:
先查看 WHERE 子句. 在 Orders 表中查询特定客户, 这是查询中存在问题的部分. 应该为 Orders 表的 CustomerID 列添加索引.
CREATE INDEX idx_order_customer_id ON Orders (CustomerID);
现在我再次分析查询:
EXPLAIN QUERY PLAN
SELECT *
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE CompanyName = 'Island Trading');
这一次, 没有看到任何 SCAN 操作, 这是一个好兆头! 相反, 我们看到了两个 SEARCH 操作, 这意味着两个表都在此查询中利用了索引.
QUERY PLAN
`--SEARCH TABLE Orders USING INDEX idx_order_customer_id (CustomerID=?)
`--SEARCH TABLE Customers USING INDEX customer_company_name_idx (CompanyName=?)
自动生成的临时索引
与主键相关的自动索引并不是唯一一种自动生成的索引. 例如, 创建两个表 T1 和 T2:
CREATE TABLE T1 (a INTEGER, b INTEGER);
CREATE TABLE T2 (a INTEGER, c INTEGER);
使用两个表的 a 列对这两个表执行内连接, 并在查询前面加上 explain query plan.
EXPLAIN QUERY PLAN
SELECT *
FROM T1 JOIN T2 ON T1.a = T2.a;
实际上没有在 T1 和 T2 表上创建任何索引. 显然也不期望在此查询中使用索引. 但是: T1 表将执行表扫描(正如你可能预期的那样), 但令人惊讶的是, T2 表正在使用从未创建过的索引!
QUERY PLAN
`--SCAN TABLE T1
`--SEARCH TABLE T2 USING AUTOINDEX_2 (a=?)
这个索引是怎么回事, 它从哪里来的? 如果使用 .index 命令显示 T2 表上的索引, 会发现没有任何返回结果.
.index T2
所以, 无论这是什么索引, 此时它都不存在. 到底发生了什么? 在这种特殊情况下, 查询计划器注意到, 如果它必须执行表扫描, 则此查询将导致糟糕的性能.
如果换自己考虑一下这个查询, 如何找到解决方案将导致如下所示的嵌套循环加速呢?
FOR each row in T1: // 扫描 T1
FOR each row in T2: // 扫描 T2 (对于 T1 的每一行)
IF T1.a == T2.a:
// 找到匹配项
显然对 T1 表执行一次表扫描, 并且必须对 T2 表执行 n 次扫描, 其中 n 是 T1 表中的行数. 这种低效查询的时间复杂度是 O(n * m), 其中 n 是 T1 表中的行数, m 是 T2 表中的行数.
假设两个表具有相似的行数, 则这种低效的解决方案将近似于平方性能, 这非常糟糕.
查询计划器认识到像这样的幼稚解决方案是多么糟糕, 并寻找更好的选择. 更好的选择是什么样的? 计划器可以在 T2 表的 a 列上创建一个临时索引, 这需要 M log M 的时间, 然后可以使用此临时索引, 从而使总查询复杂度为 n log M. 因此, 创建和使用临时自动索引的总复杂度为 M log M(创建索引)+ n log M(执行查询). 这种组合性能比幼稚的未索引解决方案快几个数量级.
看到像这样的自动索引, 需要意识到它是针对此查询即时生成的, 这仍然相当昂贵. 因此, 仅仅因为看到带有索引的 SEARCH 操作并不意味着正在使用高效的查询.
在这种情况下, 仍然应该创建一个持久索引, 以便可以避免这种相对昂贵的即时索引操作.
总结
总而言之, 如果查询性能不佳, 可以使用 explain query plan 命令来确定问题所在. 此外, 可以使用 .expert 模式让数据库主动建议应添加的索引.
expert 模式是一项实验性功能, 没有能力为所有可能的查询提供优化建议.