如何选择合适的索引类型

我们已经深入了解了索引的理论，认识了各种类型，学会了创建和删除索引，还搞明白了怎么给像数组和JSONB这种复杂数据类型建索引。现在，是时候聊聊怎么选出最适合你需求的索引了——毕竟选错了，后果可能很惨。

想象一下，你的数据库就像一个图书馆，而查询就是来找书的访客。如果书都扔地上，找起来就跟迷路一样。索引就是有序的书架和目录，让你能很快找到想要的东西，不用全屋乱翻。

但如果你放错了书架或者目录，比如你用HASH索引去做范围查找，这就像图书管理员想按书名找书，但手头只有按出版年份排的目录——找起来慢得要命，大家都要吐槽。在数据库里，这就表现为查询慢、系统压力大。

今天我们就来聊聊怎么选对索引，让你的查询飞起来，数据库也不累。选错了结果会很惨：查询卡死，资源被吃光，图书管理员（PostgreSQL）都emo了。

选索引的标准：清单

选索引的时候，先问自己几个问题：

1. 你这个字段的数据类型是什么？
   • 比如，数字INTEGER、FLOAT一般用B-TREE索引，数组用GIN，文本字段就要看具体需求了。

2. 你最常执行什么查询？

   • WHERE field = value？精确查找？那大概率用B-TREE或者HASH。
   • 查数组或JSONB？那就看GIN。
   • 地理数据、范围？考虑GiST。

3. 你的数据经常发生什么变化？

   • 如果表经常插入和更新，别乱建太多索引，不然开销会很大。

4. 需要保证唯一性吗？

   • 那你得用带UNIQUE属性的索引。

案例：真实的索引选择例子

来看看几个真实场景。

1. 简单的等值查找

你有个学生数据库，想通过email快速找到某个学生：

SELECT * FROM students WHERE email = 'student@example.com';

这里关键是查等值。最合适的就是B-TREE索引，因为它查精确匹配很强。

CREATE INDEX idx_students_email ON students (email);

如果email要唯一：

CREATE UNIQUE INDEX idx_students_email_unique ON students (email);

2. 范围查找

假设你想找所有大于18岁的学生：

SELECT * FROM students WHERE age > 18;

查范围用B-TREE也很合适，因为它的结构就是为顺序查找设计的。

CREATE INDEX idx_students_age ON students (age);

3. 按数组过滤

你有个courses表，其中有个字段存着报名学生的ID数组。你想查所有包含ID为123的课程。

SELECT * FROM courses WHERE student_ids @> ARRAY[123];

这种查询最适合用GIN索引，因为它专门优化了数组操作。

CREATE INDEX idx_courses_students_ids ON courses USING gin (student_ids);

4. 从JSONB提取数据

比如你有个存着JSONB数据的表，里面是订单信息。你想查所有城市是"Moscow"的订单：

SELECT * FROM orders WHERE data->>'city' = 'Moscow';

这里用GIN索引很合适，可以高效查JSONB里的key和value。

CREATE INDEX idx_orders_data ON orders USING gin (data);

5. 地理数据

如果你在搞地理信息，比如查某个半径内的所有点，用GiST索引。这种索引对几何和范围数据很友好。

CREATE INDEX idx_locations_geom ON locations USING gist (geom);

不同索引性能对比

举个查学生email的真实例子。表里有100万条记录。我们用不同索引和不加索引来试试：

结论：这里用B-TREE索引，查询速度提升了500多倍。

选索引时的常见错误

最常见的坑——“以防万一”就给每个字段都建索引。比如你给表里每个字段都建了索引，结果发现插入性能暴跌。记住，索引不是万能的魔法棒。用对了很强，用错了反而坑自己。

另一个常见错误——选错索引类型。比如你用HASH索引去做范围查找，结果查询慢到爆。因为HASH索引只适合精确查找。

选索引的建议

• 如果你经常查等值或排序，用B-TREE。
• 要精确匹配，又想省内存，可以用HASH。
• 搞数组或JSONB，首选GIN。
• 查范围或地理数据，用GiST。

最后，最重要的建议：一定要分析你的查询！用EXPLAIN和EXPLAIN ANALYZE命令，看看PostgreSQL到底怎么用索引的，能不能再优化。

EXPLAIN ANALYZE
SELECT * FROM students WHERE email = 'student@example.com';

今天就到这！现在你已经有本事像绝地武士选光剑一样选索引了。别乱建索引，没用的就别加，随时关注它们对性能的影响。