SQLiteindextutorial

SQLiteindexoverview

index is datalibraryin用于improvingqueryperformance datastructure, 它可以helpingdatalibrarysystem fast 速定位 and 访问data, 而不需要扫描整个表. in SQLitein, index for 于processing big 型表 and complex query尤 for important .

index basicconcepts

index is adatastructure, 用于加速datalibraryquery
indexclass似于书籍 Table of Contents, 可以 fast 速定位 to 需要 data
index可以 big big improvingquery速度, 但会增加写入operation 开销
index需要占用额 out store空间

SQLiteindexclass型

SQLitesupport many 种class型 index, 每种class型都 has 其specific 用途 and 优势.

1. Btreeindex (默认)

Btreeindex is SQLite默认using indexclass型, 它适用于 big many 数query场景. Btreeindex具 has 良 good 平衡性, 可以 high 效地support范围query and sortoperation.

2. 唯一index

唯一index确保index列in 值 is 唯一 , 这 and UNIQUE约束class似, 但唯一index可以improvingqueryperformance.

3. 主键index

主键index is a特殊唯一index, 用于加速 for 主键列 query. in SQLitein, 主键列会自动creationindex.

4. many 列index

many 列index (复合index) is 基于 many 个列creation index, 可以加速涉及这些列 query.

5. 部分index

部分index is 基于表子集creation index, 只package含满足specific条件行. 部分index可以减 small index big small , improvingqueryperformance.

6. 表达式index

表达式index is 基于列表达式creation index, 可以加速涉及这些表达式 query.

indexclass型	describes	适用场景
Btreeindex	默认indexclass型, 平衡treestructure	big many 数query场景
唯一index	确保index列值唯一	需要唯一性约束列
主键index	主键列特殊index	基于主键 query
many 列index	基于 many 个列 index	涉及 many 个列 query
部分index	基于表子集 index	只query表一部分data
表达式index	基于表达式 index	涉及表达式 query

creationindex (CREATE INDEX)

in SQLitein, 可以usingCREATE INDEX语句creationindex.

basic语法

                    -- creation普通index
CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

-- creation部分index
CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
ON table_name (column1, column2, ...)
WHERE condition;

-- creation表达式index
CREATE INDEX [IF NOT EXISTS] index_name
ON table_name (expression);

-- example
-- creation普通index
CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);

-- creation唯一index
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- creation many 列index
CREATE INDEX IF NOT EXISTS idx_orders_customer_date ON orders(customer_id, order_date DESC);

-- creation部分index
CREATE INDEX IF NOT EXISTS idx_active_users ON users(status) WHERE status = 'active';

-- creation表达式index
CREATE INDEX IF NOT EXISTS idx_users_name_lower ON users(LOWER(name));
                

index命名约定

using has 意义 index名称, such asidx_table_column
for 于 many 列index, 可以usingidx_table_col1_col2 格式
for 于唯一index, 可以 in 名称inpackage含unique before 缀

usingindex

SQLitequeryoptimization器会自动using合适 index来加速query, 不需要手动指定using哪个index.

index using场景

in WHERE子句inusingindex列
in ORDER BY子句inusingindex列
in GROUP BY子句inusingindex列
in JOINoperationinusingindex列

indexusingexample

                    -- 1. WHERE子句inusingindex
-- fake设 has index idx_users_name
SELECT * FROM users WHERE name = '张三';

-- 2. ORDER BY子句inusingindex
-- fake设 has index idx_users_name
SELECT * FROM users ORDER BY name;

-- 3. JOINoperationinusingindex
-- fake设 has index idx_orders_customer_id
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.total_amount > 1000;

-- 4.  many 列index using
-- fake设 has index idx_orders_customer_date
SELECT * FROM orders WHERE customer_id = 1 AND order_date > '2023-01-01';
                

index 局限性

index列 on usingfunction会导致index失效
using!=, <>, NOT INetc.operation符可能导致index失效
usingLIKE '%value' ( before 导通配符) 会导致index失效
usingOR连接 many 个条件可能导致index失效
data量较 small 时, index可能不会被using

indexoptimization

合理 indexdesign and optimization可以显著improvingdatalibraryqueryperformance.

indexdesignprinciples

选择性principles: 选择区分度 high 列serving asindex
最 left before 缀principles: many 列index遵循最 left before 缀principles
覆盖indexprinciples: such as果query只需要index列, 可以using覆盖index
index列顺序: 将最常用列放 in before 面
避免过度index: 过 many index会影响写入performance

index列顺序

                    --  good  index顺序: 将选择性 high  列放 in  before 面
CREATE INDEX idx_users_name_age ON users(name, age);

--  bad  index顺序: 选择性 low  列放 in  before 面
CREATE INDEX idx_users_age_name ON users(age, name);
                

覆盖index

                    -- creation覆盖index
CREATE INDEX idx_users_name_email ON users(name, email);

-- query只usingindex列, 不需要回表
SELECT name, email FROM users WHERE name LIKE '张%';
                

usingEXPLAINanalysisindexusing

                    -- analysisquery计划
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = '张三';

-- 输出example
-- SCAN TABLE users USING INDEX idx_users_name
                

indexmanagement

indexmanagementincluding查看, modify and deleteindexetc.operation.

查看index

                    -- 查看表 index
PRAGMA index_list(table_name);

-- 查看index 详细information
PRAGMA index_info(index_name);

-- example
PRAGMA index_list(users);
PRAGMA index_info(idx_users_name);
                

deleteindex

                    -- deleteindex
DROP INDEX [IF EXISTS] index_name;

-- example
DROP INDEX IF EXISTS idx_users_name;

-- delete many 个index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX IF EXISTS idx_users_age;
                

重建index

SQLite没 has 直接 REINDEXcommands来重建index, 但可以throughdelete并重 new creationindex来implementation:

                    -- 重建index
DROP INDEX IF EXISTS idx_users_name;
CREATE INDEX idx_users_name ON users(name);
                

indexperformance考虑

index虽然可以improvingqueryperformance, 但也会带来一些开销, 需要平衡考虑.

index 优点

improvingquery速度
加速ORDER BY and GROUP BYoperation
加速JOINoperation
强制实施唯一性约束

index 缺点

占用额 out store空间
减 slow INSERT, UPDATE and DELETEoperation
增加datalibrarymaintenance开销
过 many index会降 low performance

whencreationindex

表很 big , query频繁
列选择性 high
列经常用于WHERE子句
列经常用于JOINoperation
列经常用于ORDER BY or GROUP BY

when不creationindex

表很 small
列选择性 low
列很 few 用于query
表经常for big 量写入operation

实践case: optimization电商网站query

现 in 让我们through一个practicalcase来练习SQLiteindex using and optimization.

caserequirements

fake设 has 一个电商网站, package含以 under 表:

products: 产品表 (id, name, category, price, stock, created_at)
orders: 订单表 (id, customer_id, total_amount, order_date, status)
order_items: 订单详情表 (id, order_id, product_id, quantity, unit_price)
customers: 客户表 (id, name, email, phone, created_at)

commonquery场景

按classificationquery产品
按价格范围query产品
query客户订单history
query订单详情
按订单statusquery订单

indexoptimizationsolutions

                    -- 1.  for products表creationindex
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
CREATE INDEX IF NOT EXISTS idx_products_price ON products(price);
CREATE INDEX IF NOT EXISTS idx_products_name ON products(name);

-- 2.  for orders表creationindex
CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders(customer_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_date ON orders(order_date);

-- 3.  for order_items表creationindex
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_product_id ON order_items(product_id);

-- 4.  for customers表creationindex
CREATE INDEX IF NOT EXISTS idx_customers_email ON customers(email);
CREATE INDEX IF NOT EXISTS idx_customers_name ON customers(name);

-- testqueryperformance
-- 1. 按classificationquery产品
EXPLAIN QUERY PLAN SELECT * FROM products WHERE category = 'electronics' AND price < 1000;

-- 2. query客户 订单history
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1 ORDER BY order_date DESC;

-- 3. query订单详情
EXPLAIN QUERY PLAN SELECT * FROM order_items WHERE order_id = 100;
                

互动练习

请completion以 under index练习:

creation一个学生表 (students) , package含id, name, age, grade, majoretc.字段
for 常用query场景creation合适 index
testindex for queryperformance 影响
analysisindex usingcircumstances
optimizationindexdesign

提示: 考虑学生表 commonquery场景, such as按专业query, 按年级queryetc..

本课时summarized

in 本课时in, 我们Learning了SQLite indextechniques:

understanding了index basicconcepts and important 性
Learning了SQLitesupport indexclass型: Btreeindex, 唯一index, 主键index, many 列index, 部分index and 表达式index
Master了creationindex 语法 and techniques
Understand了index using场景 and 局限性
Learning了indexoptimization principles and method
Master了indexmanagement basicoperation
Understand了index performance考虑 and 权衡
through电商网站 case练习了index practicalapplication