印刻数据库

外观

SQLite索引设计与优化 ​

本文档详细介绍 SQLite 索引的设计原则、创建方法和优化策略，帮助您提高数据库查询性能。

索引概述 ​

什么是索引 ​

索引是一种数据结构，用于加速数据库查询。它通过维护列值与行位置之间的映射关系，使数据库系统能够快速定位到符合查询条件的行。

索引的优缺点 ​

优点 ​

  • 提高查询速度
  • 加速排序操作
  • 加速连接操作

缺点 ​

  • 增加存储空间
  • 减慢插入、更新和删除操作
  • 需要定期维护

索引设计原则 ​

1. 为经常查询的列创建索引 ​

  • 经常出现在 WHERE 子句中的列
  • 经常用于连接操作的列
  • 经常用于排序的列

2. 考虑索引选择性 ​

  • 选择性高的列（唯一值比例高）适合创建索引
  • 选择性低的列（唯一值比例低）不适合创建索引

3. 限制索引数量 ​

  • 每个表的索引数量不宜过多（建议不超过 5-10 个）
  • 避免为经常更新的列创建索引

4. 考虑联合索引 ​

  • 对于经常一起查询的多个列，可以创建联合索引
  • 联合索引遵循最左前缀原则

5. 避免冗余索引 ​

  • 避免创建重复或冗余的索引
  • 定期检查并删除不再使用的索引

索引创建 ​

基本语法 ​

sql
CREATE [UNIQUE] [INDEX] [IF NOT EXISTS] index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

示例 ​

sql
-- 单列索引
CREATE INDEX idx_users_email ON users (email);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- 联合索引
CREATE INDEX idx_orders_user_id_created_at ON orders (user_id, created_at DESC);

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- 部分索引
CREATE INDEX idx_users_active ON users (email) WHERE active = 1;

索引管理 ​

查看索引 ​

sql
.indices table_name

删除索引 ​

sql
DROP INDEX [IF EXISTS] index_name;

重建索引 ​

sql
-- SQLite 没有直接的重建索引命令，可以通过删除并重新创建索引来实现
DROP INDEX IF EXISTS idx_users_email;
CREATE INDEX idx_users_email ON users (email);

索引优化策略 ​

1. 利用最左前缀原则 ​

  • 联合索引的查询遵循最左前缀原则
  • 例如，对于索引 (a, b, c)，可以加速查询 (a)、(a, b) 和 (a, b, c)，但不能加速查询 (b) 或 (b, c)

2. 避免索引失效 ​

  • 避免在索引列上使用函数或表达式
  • 避免使用 NOT IN、!=、<> 等操作符
  • 避免使用 OR 连接条件（除非所有条件列都有索引）
  • 避免使用 LIKE '%value%' 这样的前缀模糊查询

3. 使用覆盖索引 ​

  • 当查询的所有列都包含在索引中时，称为覆盖索引
  • 覆盖索引可以避免回表查询，提高查询性能

4. 考虑索引排序 ​

  • 为经常用于排序的列创建索引
  • 索引的排序顺序（ASC/DESC）应与查询的 ORDER BY 子句匹配

索引性能分析 ​

使用 EXPLAIN 分析查询计划 ​

sql
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com';

查看索引使用情况 ​

sql
-- 启用索引统计
PRAGMA optimize;

-- 查看索引使用统计
PRAGMA index_info(index_name);

常见问题（FAQ） ​

Q: 如何判断是否需要创建索引？ ​

A: 分析查询频率和性能，为经常查询的列创建索引。

Q: 如何优化已有的索引？ ​

A: 使用 EXPLAIN 分析查询计划，检查索引是否被有效使用，调整索引结构。

Q: 索引越多越好吗？ ​

A: 不是，索引会增加存储空间和维护成本，应根据实际需求创建索引。

Q: 什么是覆盖索引？ ​

A: 当查询的所有列都包含在索引中时，称为覆盖索引，可以避免回表查询。