我们经常碰见很多业务逻辑简单的应用，服务器配置也不差，但接口却要好几秒才返回。问题基本上不在代码本身，而在于数据库交互。最普遍、也最容易优化的原因，就是全表扫描拖慢了速度。

数据库执行SQL的核心逻辑，是遵循成本最小原则。提交一条SQL语句后，不像一般的编程语言会顺序执行，数据库会像以前经验丰富的图书馆管理员一样，先根据查询条件、各表内容的经验估算，分析各种可能的查询方法，并选择它认为成本最低的那一个。

对于一张没有任何索引的表，数据库唯一的查询方式就是全表扫描，即从第一条记录逐行读到最后一条记录，直到找出所有符合条件的行。这个过程的时间消耗与表的大小呈线性增长关系。当表的数据量从几MB膨胀到几GB时，查询耗时就明显会从毫秒级提高到好几秒。如果加了LIMIT限制返回数量，找到足够的条数之后就可以停了。

数据库索引像是有些教科书末尾的术语索引。书中的内容（表数据）本身是无序的，但索引按字母顺序（排序规则）排列了所有关键词（索引列），并标注了它们所在的页码（行位置）。当你想查找某个特定术语时，无需一页页翻遍全书，只需在索引中通过二分查找快速缩小范围，定位到术语所在的一个或多个页码，然后直接翻到那些页面看具体内容。如果只要找一个结果，找到就停，加LIMIT也能减少搜索时间。对于OR或IN条件，数据库会用每个条件查一遍索引，把重复的结果去掉，再根据行号去表中挨个查找。

一张表可以有多个索引，就像字典既有拼音又有部首，满足不同的查询需求。但数据库不会同时用多个索引，只会选最合适的那个。哪怕WHERE条件里有多个带索引的列，它也会估算哪个索引能过滤掉更多数据，然后用这个索引找到行，再在这些行里筛选其他条件。

索引好不好，关键看区分度。一个高效的索引应该迅速过滤掉大量不相关的数据。一个区分度低的索引，就如同用“性别”作为电话簿的索引，只能筛选掉一半人，剩下的页码还零零散散，前后跳着翻是需要耗费很多时间的，查起来比全表扫描还慢。因此，像“状态”、“类型”这类取值很少的字段，不适合单独建立索引。

当查询条件涉及多个字段时，可能需要考虑联合索引。联合索引就像是字典的部首笔画检字法。它首先按部首排序，部首相同的字再按笔画排序。它必须从左往右查，不能跳过部首，直接查剩余笔画。难检字表在数据库里就是（‘’, 笔画），不是单独的笔画索引。基于从左往右查的特点，设计联合索引时，最左边的列一定要区分度最高，不然后面的列难以发挥作用。

索引不是越多越好：一般来说，一张表的索引不超过两个，联合索引不超过两列。太多索引会直接拖慢写入速度，每次插入或更新数据，都要维护所有索引；数据库选索引时也更容易选择困难，选错了区分度低的索引，查询速度可能慢几倍。

一张表，如果 A, B 两列都有可能需要查询，可以用建 (A, B), B 两个索引的方法，其中 A 列一定是重复行多的列。比如员工完成任务表，既要按时间查所有任务，又要查某人的任务，就可以建 （人员ID，时间） 和 时间 两个索引。不用建（时间，人员）——因为同一时间基本不会有两个人完成任务，第二列相当于摆设。

如果表中设置了主键或唯一列约束，就不用对它们手动建索引，数据库会自动建。再手动建只会增加开销，没有任何好处。此时，多列主键或多列唯一值约束的写法，就和联合索引一样了，要把区分度高的列放在左边，而不是按业务逻辑顺序。比如部门人员岗位表，主键应该是（人员ID，部门ID），而不是（部门ID，人员ID），因为人员比部门多得多，区分度更高，能更快定位到数据。

很多人认为外键或JOIN的关联字段必须建索引，这些说法早就过时了。如果这些字段不在WHERE条件里，又不能精确定位，就别建索引。现代数据库的优化器在处理表连接时，会根据表的大小、内存等因素智能选择连接算法。比如左表用索引筛选出10000条记录，要和右表JOIN，匹配结果有8000条。如果右表用索引查这10000条，成本可能远高于全表扫描右表，再用哈希连接（Hash Join，如同编程语言中的字典，适合可放入内存的数据集）或归并连接（Merge Join，先排序再合并）。因此，索引应优先服务于WHERE子句中能显著过滤数据的条件，而非想当然地给所有JOIN字段都加上索引。

数据库查询时永远选成本最低的路径，因为其SQL一定会经过“基于成本的优化器”（Cost-based optimizer）。我们设计索引的目标，就是帮数据库降低查询成本，找到成本最低的查询路径。通过为区分度高、查询频繁的字段建立精准的索引，就能有效避免全表扫描，提高接口返回速度。

参考资料

1. SQLite 文档《Query Planning》, https://www.sqlite.org/queryplanner.html