经常有开发者问我，如何才能从他们的数据库中榨取最大的性能。到目前为止，最简单（也是最大）的性能改进来自于对索引的良好使用。我把这个问题分成两类：（1）知道什么是可能的（2）知道你实际得到了什么。
如何从你的索引中获得最大收益
考虑下面的查询，我们想知道一个用户最后收到的10条信息。
SELECT title, body, sender, datetime
FROM messages
WHERE email = 'person@example.com'
ORDER BY datetime DESC
LIMIT 10;
数据库要做多少工作才能检索到这个结果？如果没有索引，服务器将不得不加载表中的每一条记录（这被称为表扫描），扔掉不匹配的记录，然后执行排序操作，只是为了返回我们需要的10条信息，从最新到最旧的顺序。分布式数据库可以分配查询和排序操作的大部分工作，但如果不使用索引，就不能减少工作负荷。
一个好的索引会。
- 有效地过滤数据
- 消除对数据排序的需要
- 用索引中的数据回答你的查询
我们可以把这些表述为索引的规则。
规则1：用你的索引过滤你的查询
规则2：用你的索引对你的查询进行排序
规则3：用你的索引覆盖你的查询
我们很快就会提到这些，但是为了看到正在发生的事情，你需要看到数据库的行为，所以让我们称之为规则#0：用EXPLAIN ANALYZE 观察你的查询。
规则0：使用语句计划来查看数据库如何服务于你的查询
如果你不知道数据库到底在做什么，你就无法确定你的工作是否正确。每个数据库都以不同的方式提供这种可见性，但我们要看的是CockroachDB的物理语句计划。这告诉数据库运行查询，将其分解成多个阶段，并告诉你每个阶段你需要知道什么。要看这个，请运行查询，前缀为 EXPLAIN ANALYZE:
EXPLAIN ANALYZE SELECT title, body, sender, datetime
FROM messages
WHERE email = 'person@example.com'
ORDER BY datetime DESC
LIMIT 10;
结果会是这样的。
info
- - -
planning time: 683µs
execution time: 3s
distribution: local
vectorized: true
rows read from KV: 1,000,000 (226 MiB)
cumulative time spent in KV: 3s
maximum memory usage: 10 MiB
network usage: 0 B (0 messages)
• top-k
│ nodes: n1
│ actual row count: 10
│ estimated max memory allocated: 10 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 2
│ order: -datetime
│ k: 10
│
└── • filter
│ nodes: n1
│ actual row count: 1,000
│ estimated row count: 2
│ filter: email = 'person@example.com'
│
└── • scan
nodes: n1
actual row count: 1,000,000
KV time: 3s
KV contention time: 0µs
KV rows read: 1,000,000
KV bytes read: 226 MiB
estimated max memory allocated: 10 MiB
estimated row count: 1,000,000 (100% of the table; stats collected 6 seconds ago)
table: messages@primary
spans: FULL SCAN
(35 rows)
Time: 3.106s total (execution 2.991s / network 0.115s)
语句计划是由下往上读的。忽略底部的Time 字段--这可能会有很大的变化，这取决于许多你无法控制的因素，比如你的集群是否处于负载之下，是什么类型的负载，你的结果有多少已经在内存中了，等等。所以不要相信它。
你要看的是其他的东西。首先，请注意，这个查询有3个阶段。scan 阶段显示，该表必须被扫描，并加载所有100万条记录（见actual row count ）。你可以看到，它是从写着table: messages@messages_pkey 的那一行开始看表的。这100万行被传递到filter 阶段，该阶段将其裁剪成1000行。这些行又被传递到top-k 阶段，该阶段对这1000行进行排序并返回我们真正想要的10行。
有一个更好的方法。
规则1：用索引过滤你的查询，避免全表扫描
避免表扫描的唯一方法是建立一个索引。最简单的、帮助最大的是在 "电子邮件 "字段上建立一个单列索引。这个索引是按电子邮件排序的，使服务器能够只隔离用户的邮件，而忽略表中的其他999,000条。
CREATE INDEX email_idx ON messages (email);
现在索引已经建立，让我们再看一下语句计划。
info
- - -
planning time: 557µs
execution time: 121ms
distribution: local
vectorized: true
rows read from KV: 2,000 (280 KiB)
cumulative time spent in KV: 120ms
maximum memory usage: 1.1 MiB
network usage: 0 B (0 messages)
• top-k
│ nodes: n1
│ actual row count: 10
│ estimated max memory allocated: 10 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 2
│ order: -datetime
│ k: 10
│
└── • index join
│ nodes: n1
│ actual row count: 1,000
│ KV time: 117ms
│ KV contention time: 0µs
│ KV rows read: 1,000
│ KV bytes read: 211 KiB
│ estimated max memory allocated: 960 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 2
│ table: messages@primary
│
└── • scan
nodes: n1
actual row count: 1,000
KV time: 3ms
KV contention time: 0µs
KV rows read: 1,000
KV bytes read: 69 KiB
estimated max memory allocated: 120 KiB
estimated row count: 2 (<0.01% of the table; stats collected 2 minutes ago)
table: messages@email_idx
spans: [/'person@example.com' - /'person@example.com']
(41 rows)
Time: 228ms total (execution 122ms / network 107ms)
它仍然有3个阶段，但是当你仔细看的时候，scan 阶段只接触到1000行，而不是100万行。你可以看到，它在写着table: messages@email_idx 的那一行使用了一个索引。这意味着CockroachDB正在查看我们刚刚创建的messages 表的email_idx 索引。而且你可以从actual row count ，它只接触了1000条记录。
第二阶段曾是filter ，但现在是index join 。这是因为数据库从扫描索引的相关部分开始，并很快发现它需要按datetime （一个不在索引中的字段）进行排序，所以它需要到表中去查找那些被索引识别为具有正确电子邮件的每一条记录。
这已经是一个很大的改进：数据库现在不是扫描100万行并丢弃其中的99.9%，而是查看1000个索引条目，然后只查找它需要的1000行。最后一个阶段，top-k （数据库执行排序操作的地方），和以前完全一样。为了消除这个阶段，我们将需要一个更好的索引。
规则2：用你的索引对你的查询进行排序，以加快查询的速度
我们创建的索引是按email ，但是没有任何关于我们需要排序的timestamp 的信息--对于这些信息，数据库必须从表中提取相关的行。一个更好的索引也会包括时间戳的信息，最好是按时间戳排序。由于涉及到两个列，我们要在两个字段上使用复合索引--首先是电子邮件，然后是timestamp 。
CREATE INDEX email_datetime_idx ON messages (email, datetime);
当我们这样做的时候，我们应该放弃旧的索引--我们不再需要它了。
DROP INDEX email_idx;
做完这些，我们现在可以看看我们的新索引的性能如何。让我们再次运行查询，并看看语句计划。
EXPLAIN ANALYZE SELECT title, body, sender, datetime
FROM messages
WHERE email = 'person@example.com'
ORDER BY datetime DESC
LIMIT 10;
info
- - -
planning time: 796µs
execution time: 6ms
distribution: local
vectorized: true
rows read from KV: 20 (2.7 KiB)
cumulative time spent in KV: 6ms
maximum memory usage: 60 KiB
network usage: 0 B (0 messages)
• index join
│ nodes: n1
│ actual row count: 10
│ KV time: 3ms
│ KV contention time: 0µs
│ KV rows read: 10
│ KV bytes read: 1.9 KiB
│ estimated max memory allocated: 30 KiB
│ estimated max sql temp disk usage: 0 B
│ estimated row count: 10
│ table: messages@primary
│
└── • revscan
nodes: n1
actual row count: 10
KV time: 2ms
KV contention time: 0µs
KV rows read: 10
KV bytes read: 780 B
estimated max memory allocated: 20 KiB
estimated row count: 10 (<0.01% of the table; stats collected 43 seconds ago)
table: messages@email_datetime_idx
spans: [/'person@example.com' - /'person@example.com']
limit: 10
(33 rows)
Time: 92ms total (execution 8ms / network 84ms)
你应该注意的第一件事是，现在只有两个阶段。底部的(revscan)告诉我们，我们的集群正在以相反的顺序扫描索引的一个区域，而table 行告诉我们它正在使用我们刚刚创建的索引。你可以看到，actual row count ，现在只有10个。它能够到索引中去，只找到它在这个查询中需要的10条记录。最后阶段是一个索引连接，它只查找它需要的10条记录。
同样，这也是一个巨大的改进。我们的第一个索引，仅仅是关于电子邮件的，使我们从扫描100万条必须按电子邮件过滤然后排序的表行，变成了扫描1000个索引条目，在表中单独查找它们，然后对它们排序。因此，对100万行的扫描变成了对1000个索引项的扫描，加上1000个查找操作（连接），消除了对其他99.9%的表的需要。现在我们只扫描了10个索引项，并从表中查找了10条记录。因此，我们消除了之前所看的99.999%的表行，而且我们不需要对任何东西进行排序（因为索引已经被排序了）。现在让我们把它带到下一个层次。
规则3：用你的索引覆盖你的查询，以完全避免表的影响
再看一下原始查询。
SELECT title, body, sender, datetime
FROM messages
WHERE email = 'person@example.com'
ORDER BY datetime DESC
LIMIT 10;
正如你在SELECT子句中所看到的，它要求从四个列中获取数据：标题、正文、发件人和日期时间。然而，我们已经看过的索引是关于电子邮件和日期时间的。这些对于过滤和排序都很有用，但是日期时间只是我们需要显示的字段之一，所以我们仍然要到表中去寻找该行中的其他数据。
然而，你可以看到，如果你只需要日期时间，行为就更简单了。
EXPLAIN ANALYZE SELECT datetime FROM messages WHERE email = 'person@example.com' ORDER BY datetime DESC LIMIT 10;
info
- - -
planning time: 853µs
execution time: 3ms
distribution: local
vectorized: true
rows read from KV: 10 (780 B)
cumulative time spent in KV: 2ms
maximum memory usage: 20 KiB
network usage: 0 B (0 messages)
• revscan
nodes: n1
actual row count: 10
KV time: 2ms
KV contention time: 0µs
KV rows read: 10
KV bytes read: 780 B
estimated max memory allocated: 20 KiB
estimated row count: 2 (<0.01% of the table; stats collected 1 minute ago)
table: messages@email_datetime_idx
spans: [/'person@example.com' - /'person@example.com']
limit: 10
(21 rows)
Time: 85ms total (execution 4ms / network 81ms)
现在只有一个阶段：revscan 。数据库找到了它所需要的10个索引条目，而且因为这些条目包含了回答查询所需要的所有信息，所以它能够做到这一点，根本不需要到表中去。根本不需要！这就是所谓的覆盖式查询。这就是所谓的覆盖式查询，这是真正优化你的读取的关键，因为它甚至不需要接触表来回答一个查询。
但是，当然，我们只能进行覆盖式查询，因为我们没有要求我们需要的大部分数据，这不是很有帮助。
一个解决方案是建立一个索引，将更多的列添加到复合索引中，但这不是理想的方法。理想的方法是用我们需要的索引键来存储我们需要的数据，以便对数据进行过滤和排序。
CREATE INDEX email_datetime_storing_title_body_sender_idx
ON messages (email, datetime)
STORING (title, body, sender);
...并放弃旧的索引
DROP INDEX email_datetime_idx;
...并看看数据库是如何回答原始查询的。
EXPLAIN ANALYZE SELECT title, body, sender, datetime
FROM messages
WHERE email = 'person@example.com'
ORDER BY datetime DESC
LIMIT 10;
info
- - -
planning time: 2ms
execution time: 6ms
distribution: local
vectorized: true
rows read from KV: 10 (2.1 KiB)
cumulative time spent in KV: 5ms
maximum memory usage: 20 KiB
network usage: 0 B (0 messages)
• revscan
nodes: n1
actual row count: 10
KV time: 5ms
KV contention time: 0µs
KV rows read: 10
KV bytes read: 2.1 KiB
estimated max memory allocated: 20 KiB
estimated row count: 2 (<0.01% of the table; stats collected 3 minutes ago)
table: messages@email_datetime_storing_title_body_sender_idx
spans: [/'person@example.com' - /'person@example.com']
limit: 10
(21 rows)
Time: 88ms total (execution 9ms / network 79ms)
这是你能得到的最精简的索引。CockroachDB只需要找到相关的10条记录（这些记录按照需要的顺序位于一起）就可以得到所有需要的信息。因为所有的四列都在索引中，所以没有必要去找表：日期时间是索引键的一部分，而其他三列是和索引条目一起存储的。
所以现在我们不仅消除了扫描表或对行进行排序的需要，而且也消除了单独查找表数据的需要。这就是一个好的索引的力量。
这些索引最佳实践的注意事项
关于索引，我们可以说很多，但这是一篇博文，而不是一本小说，所以我们保持简单。尽管如此，这里有三件事情要记住。
- 首先，索引必须与它们所指向的表的行保持同步。这意味着，对某一行的写入必须到表和索引中去。因为你不希望索引最终是一致的，这意味着你需要一个事务，以及相关的开销。所以，不要在一个表上建立太多的索引，因为你在写数据的时候可能要写到所有的索引。
- 第二，如果你为一个大的字段建立索引（比如上面消息中的 "body "字段），你就会有一个大的索引，所以要尽量避免这样。
- 第三，总是使用你的EXPLAIN计划来检查事情是否按照你所期望的方式工作。
