索引选择度：高性能SQL的关键

英文版刊载于Flexport Engineering Blog

简介

一般普遍认为索引能提高SQL性能，但这并不总是成立， 因为只有高效的索引才能真正提高SQL性能。事实上，过多的索引甚至会减慢写操作的速度。我们之前的文章讨论了一个低效的索引是如何拖慢系统性能的，但那里的示例是不常用的GIN索引。这一次，我们介绍的示例是低效的BTREE索引。在这两种示例中，高性能SQL的共同关键都是索引选择度。

提示：若一个索引能帮助与它有关的查询在索引扫描中过滤掉大部分的行，就说这个索引有较好的选择度。有较差的选择度的索引经常会使索引扫描的性能降低到全表扫描的水平。

问题

Flexport平台让Flexport运营人员（客户运营团队和供应链运营团队），客户和货运合作伙伴能在我们的各个应用程序中发送消息。当一个消息被发送，它的接收方会收到通知。Flexport的一个运营人员有可能每周收到几千个通知，为了帮助他们管理好通知，我们开发了一些工具如：导航栏的“通知”下拉菜单，允许用户按多种条件（时间范围、货运信息、客户信息）来查询相关通知的“收件箱”。

值得重视的是，系统设计应满足未来的业务规模而不发生性能降级。这些工具面对着严酷的性能挑战：由于业务的迅速增长，有些用户收到了几百万个通知，于是他们的查询耗时竟然超过了1分钟。

表结构

通知表的结构大致如下所示：

上图的表中各个列的含义是：

• id：主键
• user_id：外键，指向接收方用户
• notification_type_id：外键，指向NotificationType
• subject_id：外键，指向执行了某个操作而引起此通知的用户
• object_id：外键，指向被执行了某个操作而引起此通知的对象
• created_at：通知创建时间
• updated_at：通知修改时间
• read_at：通知被标为“已读”的时间
• details：通知的文字内容

接下来，我们具体介绍几个慢SQL的例子和解决方案（文中的SQL和查询计划都有所修改，以隐藏商业敏感信息）。

例1: 未读通知计数

SQL

SELECT COUNT(*)
FROM notifications
WHERE notifications.user_id = ? AND read_at IS NULL

“read_at IS NULL”意为“未读”，因此这个查询会计算某用户的未读通知数。在user_id列上有一个BTREE索引服务于此查询。

一个用户的通知数可以是几千到几百万。对于多数用户，数量是几千，因此这个查询很快（毫秒级）。但客户运营团队的少数用户可有几百万个通知，因此这个查询很慢（秒级乃至分钟级），因为索引扫描需要更多时间来遍历几百万个索引项。可以称之为“数据倾斜”问题。

解决方案

我们发现只有不到10%的通知是未读的，因此索引扫描可以靠跳过已读通知来提速。PostgreSQL支持一种名为“部分索引”的特性。我们把user_id索引替换为“user_id WHERE read_at IS NULL”的部分索引。这个新索引只记录那些满足“read_at IS NULL”条件的行，因此大部分的行都被排除了，索引的大小减少了90%。由此，这个查询就总是毫秒级的快了。

例2: 最近通知列表

SQL

SELECT *
FROM notifications
WHERE notifications.user_id = ?
ORDER BY notifications.created_at desc
LIMIT ? OFFSET ?

这是一个分页查询，因此它查询的是某用户的最近一些通知（按创建时间降序排列）。与计数的例子相似，这个查询也是对多数用户快而对少数用户慢。又是“数据倾斜”问题。

解决方案

我们发现(user_id, created_at)上的“多列索引”比user_id单列索引表现得更好。索引项在被存储时是按值排序的。在这个查询中，对于多列索引的扫描会在此索引空间中先定位到满足user_id值的索引子集，然后依序遍历这个按created_at值排序的子集并且在找到足够多（满足LIMIT子句）的项后立即停止。请注意，此前已有一个(user_id, object_id, created_at)索引，但它的效率不如(user_id_ created_at)索引，因为其中的object_id列不但无用甚至有害于这个查询。在它这，满足user_id值的索引项子集不是按created_at排序，而是按(object_id, created_at)的组合排序的，因此对它的遍历（期望按created_at顺序）会在不同的object_id上跳来跳去。

多列索引（又称为复合索引或组合索引）由一组列（list of columns）组成。这些列的排列顺序很重要。正确的索引设计应该按照想要使用的搜索模式来从左到右摆放这些列。一般地，若一个查询用一些列来过滤，又用另一些列来排序，那么你可以创建一个多列索引，声明中靠左边的是用于过滤的列，靠右边的是用于排序的列。

一个经验法则是，把首要的过滤条件放在多列索引中的第一位，它之后的列用于辅助过滤。例如用户姓名表倾向于采用形如(last_name, first_name)的多列索引。

例3：按多种条件（时间范围、货运信息、客户信息）来查询相关通知

SQL

SELECT notifications.*
FROM notifications
INNER JOIN notification_types ON notification_types.id = notifications.notification_type_id
JOIN messages ON messages.id = notifications.object_id AND notification_types.object_type = ‘message’
JOIN shipments ON messages.messageable_id = shipments.id AND messages.messageable_type = ‘Shipment’
WHERE notifications.user_id = ?
AND notification_types.domain = ?
AND shipments.status = ?
AND shipments.client_id IN (?)
AND (messages.assignee_id = ? OR messages.assignee_id IS NULL)
AND messages.created_at >= ?
AND notifications.created_at >= ?
ORDER BY notifications.created_at DESC, notifications.id DESC

这个查询很复杂。与之前的查询不同，这个查询即使以相同的参数调用（当然，是对于同一个用户，因为user_id也相同）也时快时慢。

慢的查询计划
以下是一个慢的查询计划：

QUERY PLAN
----------------------------------------
->  Sort  (cost=58696.04..58696.04 rows=1 width=553) (actual time=34338.192..34338.192 rows=0 loops=1)
        Sort Key: notifications.created_at DESC, notifications.id DESC
        Sort Method: quicksort  Memory: 25kB
    ->  Nested Loop  (cost=3800.49..58660.11 rows=1 width=20) (actual time=34338.162..34338.162 rows=0 loops=1)
        ->  Gather  (cost=3800.35..58659.94 rows=1 width=24) (actual time=34338.161..34339.487 rows=0 loops=1)
                Workers Planned: 2
                Workers Launched: 2
                ->  Nested Loop  (cost=2800.35..57659.84 rows=1 width=24) (actual time=34328.555..34328.555 rows=0 loops=3)
                    ->  Nested Loop  (cost=2799.78..57401.65 rows=31 width=20) (actual time=1684.157..34318.009 rows=1581 loops=3)
                            ->  Parallel Bitmap Heap Scan on shipments  (cost=2799.21..46196.17 rows=138 width=12) (actual time=1365.688..1385.022 rows=7047 loops=3)
                                Recheck Cond: (client_id = ANY (?::integer[]))
                                Filter: (status = ?)
                                Heap Blocks: exact=5882
                                ->  Bitmap Index Scan on index_shipments_on_client_id  (cost=0.00..2799.13 rows=22114 width=0) (actual time=1364.729..1364.729 rows=22367 loops=1)
                                        Index Cond: (client_id = ANY (?::integer[]))
                            ->  Index Scan using index_messages_on_messageable_type_and_messageable_id on messages  (cost=0.56..81.19 rows=1 width=12) (actual time=4.341..4.672 rows=0 loops=21142)
                                Index Cond: (((messageable_type)::text = 'Shipment'::text) AND (messageable_id = shipments.id))
                                Filter: (((assignee_id = ?) OR (assignee_id IS NULL)) AND (created_at >= ?::timestamp without time zone))
                                Rows Removed by Filter: 8
                    ->  Index Scan using index_notifications_on_user_id_and_object_id_and_created_at on notifications notifications_1  (cost=0.57..8.32 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=4743)
                            Index Cond: ((user_id = ?) AND (object_id = messages.id) AND (created_at >= ?::timestamp without time zone))
        ->  Index Scan using notification_types_pkey on notification_types  (cost=0.14..0.17 rows=1 width=4) (never executed)
                Index Cond: (id = notifications_1.notification_type_id)
                Filter: (((object_type)::text = 'message'::text) AND (domain = ?))
 Planning Time: 116.579 ms
 Execution Time: 34339.810 ms

其中的关键一步是

-> Index Scan using index_messages_on_messageable_type_and_messageable_id on messages (cost=0.56..81.19 rows=1 width=12) (actual time=4.341..4.672 rows=0 loops=21142)
Index Cond: (((messageable_type)::text = ‘Shipment’::text) AND (messageable_id = shipments.id))
Filter: (((assignee_id = ?) OR (assignee_id IS NULL)) AND (created_at >= ?::timestamp without time zone))
Rows Removed by Filter: 8

这一步是一个Nested Loop Join，它的每一次loop都对目标表做一次索引扫描。每一次loop的平均时间为4.5毫秒(4.341..4.672)。从“loops=21142”可知总共有21142次loop，所以这一步的总时间应为4.5 * 21142 = 95139毫秒，但整个查询执行的总时间也只有34338毫秒。为什么？

我们能从查询计划中看到这个信息：

Workers Planned: 2
Workers Launched: 2

它表示并行度为3，即由1个领导进程和2个工作进程并行处理此查询（当前正在执行查询的进程为领导进程，还有两个额外的工作进程被启动）。因此，这一步的的实际总时间要除以并行度，即4.5 * 21142 / 3 = 31713毫秒，这就能匹配查询执行的总时间了。由于这一步消耗了大部分时间，我们要设法优化它。

快的查询计划
当我们用相同参数再次执行相同查询时，得到了一个很相像但快得多的查询计划：

QUERY PLAN
----------------------------------------
......
    ->  Index Scan using index_messages_on_messageable_type_and_messageable_id on messages  (cost=0.56..76.04 rows=1 width=12)
        (actual time=0.012..0.012 rows=0 loops=20463)
        Index Cond: (((messageable_type)::text = 'Shipment'::text) AND (messageable_id = shipments.id))
        Filter: (((assignee_id = ?) OR (assignee_id IS NULL)) AND (created_at >= ?::timestamp without time zone))
        Rows Removed by Filter: 8
......
 Planning Time: 4.771 ms
 Execution Time: 105.937 ms

它唯一的不同就是每一次loop的时间(0.012..0.012)，比之前的快了375倍(4.5 / 0.012 = 375)。如果我们这时换一组不同的参数来再次执行此查询，就又会得到一个慢的查询计划。简而言之，同一组参数对应的第一次执行较慢，而之后的再次执行就较快。快的查询计划与慢的查询计划几乎相同，而只有关键的那步的时间不同。由此现象能推测到什么呢？是的，有一个缓存。

分析

这个慢查询有两个主要问题：

一个问题是，当缓存未命中时，对messages表进行索引扫描的loop太慢。这个索引扫描的条件只是一个简单值，却花了4.5毫秒，难以接受呢。我们注意到，对notifications表的索引扫描就很快(慢至0.006毫秒，快至0.002毫秒)，这才比较合理。那么是什么让它们有此差距呢？

我们可以看到，对messages表的索引扫描有“Index Cond”和“Filter”这两种条件，而对notifications表的索引扫描则只有“Index Cond”这一种条件。messages表上的索引覆盖了messageable_type和messageable_id两列，所以此查询对它的扫描必须先加载索引文件，按messageable_type和messageable_id来过滤，然后加载表文件，再按assignee_id和created_at来过滤。表文件的加载需要多次很耗时的读盘，但可以受益于缓存（这也就是为什么此查询用相同参数再次执行就会变快）。要想让查询计划包含缓存信息，你可以执行“EXPLAIN (ANALYZE, BUFFERS) #{sql}”。索引查询的耗时可以通过索引选择度来改进。

另一个问题是，查询计划器低估了shipments表按client_id过滤后的预期行数。以下信息声称预期行数为138(rows=138)，但实际行数为21141(rows=7047 loops=3)：

-> Parallel Bitmap Heap Scan on shipments (cost=2799.21..46196.17 rows=138 width=12) (actual time=1365.688..1385.022 rows=7047 loops=3)

Hash Join会创建一个hashmap，对于行数较多的情况会速度更快，但消耗更多空间。Nested Loop Join只对于行数较少的情况更快，但节省空间。查询计划器会对预期行数较多的情况选择Hash Join，而对于预期行数较少的情况选择Nested Loop Join。此例中，它认为只会有138行，因此选择了Nested Loop Join。若采用Hash Join，这一步应该会有所改进吧。

解决方案

SQL查询不应该依赖缓存的仁慈。缓存确实很棒！但在考虑缓存之前，你的SQL查询应做到打铁还需自身硬。

首先我们要改进索引选择度。若messages表上的索引能通过覆盖更多列来获得更好的选择度，那么就能减少甚至免除表文件加载。因为“assignee_id IS NULL”条件过于宽泛而不适宜缓存，所以我们选择只给现有索引追加一个created_at列。作为一个小优化，我们还把选择度较强的messageable_id提前为索引结构中的第一列。现在这个索引是在messages表的(messageable_id, messageable_type, created_at)列组上。每次loop的平均时间从4.5毫秒降到了0.338毫秒，查询执行的总时间从34339毫秒降到了3893毫秒，快9倍。

那么，我们能告诉查询选择器“请选择Hash Join”么？PostgreSQL不支持SQL hints，因此我们只能设置“SET enable_nestloop = off”选型。这一选项强制PostgreSQL不许选Nested Loop Join，从而使其选Hash Join。查询执行的总时间从34339毫秒降到了5568毫秒，快6倍。事实上，由于这一原因，MySQL 8放弃了Nested Loop Join而全面转向Hash Join。

索引选择度的优化已经足够好了。Hash Join的优化需要修改数据库设置，因此在生产环境不是很敢用，而且其效果也不能线性叠加在索引选择度的优化效果上（1+1<2)。因此，我们决定只采用索引选择度的优化。

生产环境的真实改善

以下图表展示了对“收件箱”实施优化之后，生产环境的显著改善。

由于业务增长带来高负载， API端点的错误率（优化前）高达11.8%

API端点的错误率（优化后）仅为0.8%，15倍改善（我们在重构代码以进一步改善它）

结论

我们讨论了关于BTREE索引性能的3个例子，它们都受益于索引选择度的改进。选择度不佳的索引不但会消耗更多的内存和存储空间，而且也会降低SQL性能。作为一个原则，当设计业务关键性的数据库查询时，请注意和改进索引选择度。

致谢

感谢Joker Ye对相关工程工作的贡献，也感谢Dylan Irlbeck，Vinod Kumar，David Goussev和其他Flexport同事对文章的评阅。