Postgres和MySQL中的索引合并与复合索引

　　复合索引比索引合并要快10倍左右。在Postgres中，这个差距比MySQL大，因为Postgres对涉及索引合并的查询时，不只仅进行索引的扫描。
　　那么与让数据库对多个索引进行索引合并相比，复合索引的速度有多大？考虑一下这个查询。SELECT count(*) /* matches ~100 rows out of 10M */
　　FROM table
　　WHERE int1000 = 1 AND int100 = 1
　　/* int100 rows are 0..99 and int1000 0...9999 */查看表的定义
　　我们可以在(int1000, int100)上创建一个复合索引，或者我们可以在(int1000)和(int100)上有两个单独的索引，依靠数据库来利用这两个索引。
　　一个复合索引是比较快，但是比两个单独的索引快多少呢？让我们做一下理论上的计算，然后在PostgreSQL和MySQL中测试一下。Napkin Math
　　Composite Index: ~1ms
　　Index Merge: ~10-30ms
　　Reality
　　Composite Index: 5ms
　　Index Merge
　　MySQL: 30-40ms
　　Postgres: 30-90ms
　　Conclusion理论计算
　　我们将从理论计算开始，然后用Postgres和MySQL来验证它。复合索引：1ms
　　这个count(*)的理想索引是:
　　CREATE INDEX ON table (int1000, int100)
　　它允许在这一个索引上进行整个计数。
　　＂WHERE int1000 = 1 AND int100 = 1 ＂ 匹配该表10M总数中的100条记录。 数据库会在索引树中快速搜索到索引中两列都是1的叶子，然后向前扫描直到条件不再成立。
　　对于这些64位的索引条目，我们预计只需要扫描100个匹配的条目，这是一个可以忽略不计的2 KiB大小。根据理论上的参考资料，我们可以从内存中读取1 MiB/100 μs ，所以这绝对不需要时间。考虑到查询开销、浏览索引树和其他一切，理论上数据库不应该花费超过100-500 μs的时间来满足这个查询。索引合并：10-30ms
　　但是数据库也可以对两个独立的索引进行索引合并。CREATE INDEX ON table (int1000)
　　CREATE INDEX ON table (int100)
　　但是，一个数据库如何利用两个索引？以及这种合并可能有多昂贵？
　　索引如何相交取决于数据库!有很多方法可以找到两个无序列表的交集：散列，排序，集合，KD树，位图，...
　　MySQL做的是所谓的索引合并相交，很可能是排序。Postgres通过在扫描每个索引后生成一个位图，然后把它们并在一起，来做索引交集。
　　int100 = 1返回大约10M 1/1000 100,000行，这大约是1.5 MiB的扫描量。 int1000 = 1只匹配10,000行，所以我们总共从两个索引中读取大约200μs的内存。
　　在我们得到索引中的匹配结果后，我们需要与它们相交。在这种情况下，为了简化计算，让我们假设对两个索引中的匹配数据进行排序，然后从那里进行交叉。
　　我们可以在5 ms内对1 MiB数据 进行排序。因此，我们总共需要10毫秒来排序，在两个排序的列表中迭代200微秒的内存读取，将交集写入内存，再花200微秒，然后我们就得到了交集，即符合两个条件的行。
　　因此，我们的计算表明，对于我们的两个独立索引，我们期望查询需要10毫秒。排序对索引的大小很敏感，这是相当近似的，所以给它一个低的乘数来着陆，10-30ms。
　　正如我们所看到的，交叉承担着有意义的成本，在纸面上，我们期望它比复合索引大约慢一个数量级。然而，对于大多数情况来说，10ms仍然是合理的，而且根据情况，没有一个更专业的复合索引来进行查询可能是很好的。例如，如果你经常在10几列的子集之间进行连接。实际情况
　　现在我们已经从第一原理上设定了对复合索引与索引合并的期望，让我们看看Postgres和MySQL在现实中的表现。复合索引：5ms
　　在我们创建索引后，MySQL和Postgres都会执行只读索引的扫描。/* 10M rows total, int1000 = 1 matches ~10K, int100 matches ~100K */
　　CREATE INDEX ON table (int1000, int100)
　　EXPLAIN ANALYZE SELECT count(*) FROM table WHERE int1000 = 1 AND int100 = 1/* postgres, index is ~70 MiB */
　　Aggregate (cost=6.53..6.54 rows=1 width=8) (actual time=0.919..0.919 rows=1 loops=1)
　　-> Index Only Scan using compound_idx on test_table (cost=0.43..6.29 rows=93 width=0) (actual time=0.130..0.909 rows=109 loops=1)
　　Index Cond: ((int1000 = 1) AND (int100 = 1))
　　Heap Fetches: 0
　　/* mysql, index is ~350 MiB */
　　-> Aggregate: count(0) (cost=18.45 rows=1) (actual time=0.181..0.181 rows=1 loops=1)
　　-> Covering index lookup on test_table using compound_idx (int1000=1, int100=1)
　　当索引被缓存时，它们各自需要大约3-5ms。这比我们从理论计算预期的1ms要慢一些，但根据我们使用数据库经验，结果在一个数量级内，似乎可以接受。我们把这归因于查询索引的开销。索引合并
　　MySQL：30-40ms
　　当我们在MySQL中执行查询时，需要30-40ms，这很好地符合了我们计算的上限。这意味着我们的第一原则性理解很可能与现实相符!
　　让我们通过查看查询计划来确认它正在做我们期望的事情。/* 10M rows total, int1000 = 1 matches ~10K, int100 matches ~100K */
　　EXPLAIN ANALYZE SELECT count(*) FROM table WHERE int1000 = 1 AND int100 = 1
　　/* mysql, each index is ~240 MiB */
　　-> Aggregate: count(0) (cost=510.64 rows=1) (actual time=31.908..31.909 rows=1 loops=1)
　　-> Filter: ((test_table.int100 = 1) and (test_table.int1000 = 1)) (cost=469.74 rows=409) (actual time=5.471..31.858 rows=86 loops=1)
　　-> Intersect rows sorted by row ID (cost=469.74 rows=410) (actual time=5.464..31.825 rows=86 loops=1)
　　-> Index range scan on test_table using int1000 over (int1000 = 1) (cost=37.05 rows=18508) (actual time=0.271..2.544 rows=9978 loops=1)
　　-> Index range scan on test_table using int100 over (int100 = 1) (cost=391.79 rows=202002) (actual time=0.324..24.405 rows=99814 loops=1)
　　MySQL的查询计划告诉我们，它正在做的正是我们所期望的：从每个索引中获取匹配的条目，将它们相交并在相交处进行计数。在没有分析的情况下运行EXPLAIN，我可以确认它从索引中提供了所有的东西，而且永远不会去寻找完整的行。
　　Postgres：30-90ms
　　Postgres也在我们计算的一个数量级内，但它在更高的范围内，有更多的变化，总的来说表现比MySQL差。它的基于位图的相交只是在这个查询上比较慢吗？还是它在做一些与MySQL完全不同的事情？
　　让我们看一下使用与MySQL相同的查询计划。/* postgres, each index is ~70 MiB */
　　Aggregate (cost=1625.63..1625.64 rows=1 width=8) (actual time=65.430..65.431 rows=1 loops=1)
　　-> Bitmap Heap Scan on test_table (cost=1222.22..1625.38 rows=101 width=0) (actual time=63.821..65.405 rows=109 loops=1)
　　Recheck Cond: ((int1000 = 1) AND (int100 = 1))
　　Rows Removed by Index Recheck: 179
　　Heap Blocks: exact=286
　　-> BitmapAnd (cost=1222.22..1222.22 rows=101 width=0) (actual time=63.694..63.695 rows=0 loops=1)
　　-> Bitmap Index Scan on int1000_idx (cost=0.00..110.98 rows=9940 width=0) (actual time=3.945..3.945 rows=10063 loops=1)
　　Index Cond: (int1000 = 1)
　　-> Bitmap Index Scan on int100_idx (cost=0.00..1110.94 rows=101667 width=0) (actual time=58.917..58.917 rows=100038 loops=1)
　　Index Cond: (int100 = 1)
　　它证实了它正在使用位图策略来交叉两个索引，但这并不是造成性能差异的原因。
　　当MySQL从索引中提供整个集合（count(*)）时，Postgres实际上是到堆中去获取每一条记录。堆包含了整个行，它的大小超过1KiB。这是很昂贵的，当堆缓存不热的时候，查询需要超过100ms的时间。
　　从查询计划中我们可以看出，Postgres似乎无法结合索引相交来进行纯索引扫描。也许在未来的Postgres版本中，他们会支持这一点。
　　当我们只为100条记录进入堆时，进入堆并没有很大的影响。然而，如果我们把条件改为WHERE int10 = 1 and int100 = 1，总共有10,000条匹配，那么这个查询在Postgres上需要7个小时，而在MySQL上则需要200ms，因为MySQL的纯索引扫描还在继续。
　　因此，MySQL在索引合并上更胜一筹，因为有机会从索引中完成整个查询。
　　不过，Postgres和MySQL在仅有索引的扫描上确实有大致相当的性能。例如，如果我们做int10 = 1，Postgres会做它自己的只读索引扫描，因为只涉及一个索引。
　　在我第一次运行Postgres的这种纯索引扫描时，耗时超过一秒钟，我不得不运行VACUUM以使其性能相匹配。仅有索引的扫描需要经常对表进行VACUUM，以避免在Postgres中去堆中获取整个行。
　　VACUUM有帮助，因为Postgres由于其MVCC的实现，必须访问堆中自上一次VACUUM后被触及的任何记录。根据我的经验，如果你有一个更新量很大的表，而VACUUM又很昂贵的话，那么在生产中，这可能会对仅有索引的扫描产生严重影响。总结
　　索引合并比复合索引要慢10倍，因为临时相交不是一个非常快的操作。它需要对每个索引扫描的输出进行排序来解决。索引可以进一步优化交集，但这可能会对负载的稳定产生其他影响。
　　如果你想知道是否需要添加一个复合索引，或者可以不用创建单个索引并依靠数据库使用这两个索引--那么我们建立的经验法则是，索引合并的速度将比复合索引慢10倍。然而，在大多数情况下，只要你对100多条记录进行操作（在一个关系型数据库中，希望你大多是这样），我们仍在谈论低于100ms的速度。
　　当相交的列超过两列时，性能上的差距可能会略微扩大，而且表也比较大
　　如果你使用的是Postgres，要小心依赖索引合并！Postgres不做索引合并。Postgres在索引合并后不做纯索引扫描，需要去堆里找可能是10万条记录的count(*)。但是如果你只返回10几到100几条记录，结果是可接受的。