Effective Indexing Strategies for Database Performance

前言

网上各种文档教程看了一堆，总是在说“建索引”，各种索引类型，可为啥我建了索引，它还是慢呢！别着急，耐心看完，你一定有所收获。

正文

1. 在经常用于查询条件的字段上创建索引

这类字段在 WHERE 条件里经常用到，如果不加索引，每次查询都要全表扫描，随着数据量增长查询效率迅速下降。加了索引可以避免扫描大部分数据。

CREATE INDEX idx_status ON table_name(status);
SELECT * FROM table_name WHERE status = 1;

以下条件查询大概率会走索引：

等值查询： WHERE column_name = 'value'

范围查询： WHERE column_name BETWEEN 'value1' AND 'value2' 或 WHERE column_name > 'value'

部分模糊查询（右模糊）： WHERE column_name LIKE 'value%'

排序和分组：当查询涉及 ORDER BY 和 GROUP BY 子句时，如果相应列有索引，数据库可能使用索引加速。

连接查询：连接字段有索引时，数据库可利用索引优化。

2. 对于查找单条记录的字段创建唯一索引

唯一键对应的数据只有一条，若不建唯一索引会全表扫描，数据量大时非常慢。加唯一索引可以直接定位记录。

CREATE UNIQUE INDEX idx_uid ON table_name(uid);
SELECT * FROM table_name WHERE uid = 123;

3. 在表与表的关联键上创建索引

JOIN 时依赖关联键，如果不建索引会全表扫描，数据量大时关联效率低。加索引可快速匹配关联键值。

CREATE INDEX idx_post_id ON comments(post_id);
SELECT * FROM comments WHERE post_id = 12345;

4. 在经常需要排序的字段建立索引

排序时若不建索引，需要全表扫描后再排序，数据量大时开销巨大。索引可以按序直接读取记录，避免全表扫描。

CREATE INDEX idx_create_time ON table_name(create_time);
SELECT * FROM table_name ORDER BY create_time DESC;

5. 对于频繁用于 LEFT JOIN 的表的关联键创建索引

左连表时会全表扫描右表匹配关联条件，数据量大时非常慢。加索引可快速过滤匹配记录。

CREATE INDEX idx_user_id ON table_a(user_id);
SELECT * FROM table_a LEFT JOIN table_b ON table_a.user_id = table_b.user_id;

6. 在表的数据量巨大时对主键创建索引

主键默认有索引，若去掉索引，增删改查都会变慢，尤其是主键查询需要全表扫描。

CREATE INDEX idx_id ON large_table(id);

7. 对于组合查询的字段建立联合索引

单列过滤效果不好时，联合索引可以多列组合过滤提升效率。

8. 在文本字段创建前缀索引

针对模糊查询的不同模式，右模糊（如 LIKE 'abc%' ）可以使用普通 B‑tree 索引，而左模糊或左右模糊通常无法使用。

右模糊查询：将通配符放在模式末尾，数据库可使用索引快速定位前缀记录。

使用其他索引类型：如 PostgreSQL 的 Trigram 索引可处理模糊查询。

搜索引擎：如 Elasticsearch 可用于全文搜索。

CREATE INDEX idx_title_prefix ON table(title(10));
SELECT * FROM table WHERE title LIKE 'key%';

收尾

讲到这里其实也差不多了，但还有一个至关重要的问题，怎样才能知道索引有没有生效？这就得用到 EXPLAIN 大法了。