SQL数据库中什么情况该加索引？以及常见的陷阱。

sql 数据库中什么情况该加索引 index？根据我自己的经验，5 个情况。（加上索引，会导致写入速度变慢！）

第一个是 【频繁作为 WHERE 条件的列】，这个不用多说，就是避免全表扫描嘛。

就是说，你总是按"用户名"查数据，那就给"用户名"这列加个索引。

第二个，是 多列联合查询时 。

也就是 复合索引 ，复合查询。你总是同时按"城市"和"年龄"来筛选，可以把这两列合在一起建一个索引，叫复合索引。

第三个，只查少量数据。比如一列里 99% 是"否"，你只关心那 1% 的"是"。这时候可以建一个【部分索引】（WHERE 子句）），只给"是"的那部分做目录。

第四个，用了函数的查询，像 WHERE lower(name) = 'tom' 这种，你得专门给 lower(name) 这个表达式，建立索引。

第五个，学名叫做【覆盖索引】。就是你要查的列，刚好都在索引里。这个时候，你就不用翻原始表了，直接在索引里拿答案。比如你的索引里，直接就把页码和作者写出了，你查作者，就不用翻表了。

一个陷阱

索引其实也是很值得说说的，今天顺便稍微聊聊 SQL 里的索引一个陷阱。

数据库里的索引（index），就和书前面的目录一样，左边是主题、右边是页码。

这是一个经典的数据表：

编号 | 姓名       | 主属性     | 副属性     | 入学年份 | 是否荣誉生 | 基础力量
-----+------------+------------+------------+----------+------------+---------
1    | 阿伦       | 大地       | 毒素       | 2020     | 否         | 49
4    | 辛德       | 烈火       | 无         | 2020     | 否         | 52
...
25   | 斯帕克     | 雷电       | 无         | 2020     | 否         | 55
...
150  | 泽尼斯     | 心灵       | 无         | 2020     | 是         | 110

有个问题，你如果想查询 【斯帕克 】，那你得逐行读取、核对。如果你这个表里有 1亿 行数据，肯定会很低效。然后人们发明了索引，可理解为目录。可以让机器先读这个 目录，然后数据库底层，自动将其存为 B 树，之后二分法定位，省时间、省内存。

二分法： 就像猜数字游戏,每次猜中间值，排除一半，几轮就找到答案。 B 树： 把二分法的思路做成多层目录树，每一层帮你快速缩小范围。

索引是贮存在磁盘的，使用时会加载到内存。

但索引有代价，【读取速度快了，可是写入速度慢了】！INSERT、UPDATE 、DELETE 都需要更新索引。

我们往往会发现，加上索引反而让速度变慢了。

复合索引

我们上面那个表，你有一个常用查询，比如【显示 XX 主属性和 XX 副属性的人物姓名】，那索引就在【主属性】和【副属性】加吧。

CREATE INDEX ON pokemon (主属性, 副属性);

但这个，有陷阱，这个 SELECT * FROM 人物表 WHERE 副属性 = '毒素'; 是查不了的。

它首先按【主属性】排序，然后在每个分组内按【副属性】排序。只查【主属性】的查询，或者同时查【主属性】和【副属性】的查询效果还行，但无法单独基于【副属性】的查询。

因为索引长这个样。

大地     → 毒素     → [阿伦, 洛克, ...]
         → 岩石     → [盖亚, ...]
         → 无       → [特拉, ...]
雷电     → 无       → [斯帕克, 雷恩, ...]
         → 飞行     → [雷鹰, ...]
         → 金属     → [磁铁, ...]

因此，复合索引，你得明白，如果经常查【副属性】，那就单独给【副属性】一个索引。

使用 EXPLAIN

对于数据库索引优化，这个命令很重要。

这个 PostgreSQL 有自带 EXPLAIN ，它的作用是告诉你，数据库如何查。只需要在数据查询语句前加上 EXPLAIN 就行 ：

EXPLAIN SELECT * FROM 学生 WHERE 主属性 = '雷电';

它会输出：

Index Scan using idx_主属性 on 学生  (cost=0.15..8.17 rows=10 width=64)
  Index Cond: (主属性 = '雷电')

Index Scan 说这个查询使用了索引，直接找到了在第 10 行。

实际上，这个 EXPLAIN 并没有执行查询，只是告诉你数据库会怎么查。对于数据库优化，这个命令很重要。