Skip to main content

使用基于SQLite表达式的索引提高查询性能

介绍基于SQLite表达式的索引

创建索引时,通常使用表中的一列或多列。除了常规索引之外,SQLite还允许您基于表列所涉及的表达式形成索引。这种索引称为基于表达式的索引。

以下查询选择公司长度大于10个字符的客户。

SELECT customerid,
company
FROM customers
WHERE length(company) > 10
ORDER BY length(company) DESC;

如果使用EXPLAIN查询计划语句,您会发现SQLite查询计划器必须扫描整个customers表才能返回结果集。

EXPLAIN QUERY PLAN
SELECT customerid,
company
FROM customers
WHERE length(company) > 10
ORDER BY length(company) DESC;

SQLite查询计划器是一个软件组件,用于确定执行SQL语句的最佳算法或查询计划。从SQLite 3.8.0版开始,query planner组件被重写,以更快地运行并生成更好的查询计划。这种重写被称为下一代查询规划器或NGQP。

要基于表达式长度(公司)创建索引,请使用以下语句。

CREATE INDEX customers_length_company 
ON customers(LENGTH(company));

现在,如果再次执行上面的查询,SQLite将使用表达式索引来搜索选择数据,这会更快。

基于SQLite表达式的索引如何工作

只有在CREATE index语句中指定的表达式与WHERE子句或ORDER BY子句中的表达式相同时,SQLite查询计划器才会使用基于表达式的索引。

例如,在示例数据库中,我们有invoice_items表。

下面的语句使用单价和数量列创建索引。

CREATE INDEX invoice_line_amount 
ON invoice_items(unitprice*quantity);

但是,当运行以下查询时:

EXPLAIN QUERY PLAN 
SELECT invoicelineid,
invoiceid,
unitprice*quantity
FROM invoice_items
WHERE quantity*unitprice > 10;

SQLite查询计划器没有使用索引,因为创建索引(单价数量)中的表达式与WHERE子句(数量单价)中的表达式不同

基于SQLite表达式的索引限制

下面列出了CREATE INDEX语句中对表达式的所有限制。

在本教程中,您学习了如何使用基于SQLite表达式的索引来提高查询性能。