金仓数据库KingbaseES的SQL优化-对表进行聚簇cluster_kingbase索引优化实例

概述

CLUSTER指示KingbaseES基于index_name 所指定的索引来聚簇 table_name 所指定的表。该索引必须已经定义在 table_name上。

当一个表被聚簇时，会基于索引信息对它进行物理上的排序。聚簇是一种一次性的操作：当表后续被更新时，更改没有被聚簇。也就是说，不会尝试根据新行或者被更新行的索引顺序来存储它们（如果想这样做，可以周期性地通过发出该命令重新聚簇。还有，把表的 fillfactor存储参数设置为小于 100% 有助于在更新期间保持聚簇顺序，因为如果空间足够会把被更新行保留在同一个页面中）。

当一个表被更新时，会记住它是按照哪个索引聚簇的。形式 CLUSTER table_name 会使用前面所用的同一个索引对表重新聚簇。你也可以使用 CLUSTER或者ALTER TABLE 的SET WITHOUT CLUSTER形式把索引设置为可用于未来的聚簇操作，或者清除任何之前的设置。

不带任何参数的CLUSTER会重新聚簇调用用户所拥有的当前数据库中已经被聚簇过的表（如果是超级用户调用，则是所有已被聚簇过的表）。这种形式的 CLUSTER不能在一个事务块内执行。

当一个表被聚簇时，会在其上要求一个ACCESS EXCLUSIVE锁。这会阻止任何其他数据库操作（包括读和写） 在CLUSTER结束前在该表上操作。

Cluster原理

Cluster 是一个特殊的SQL命令，因为与非唯一创建索引一样，它只影响性能。事实上，cluster需要存在一个索引。那么，cluster会做什么呢？那么，创建索引能做些什么呢？让我们来看看KingbaseES的存储空间是如何工作的。

用户数据行存储在文件系统中的堆文件中，而这些行以不确定的顺序存储。如果表最初以插入/复制的顺序加载，那么以后的插入、更新和删除将导致在堆文件中以不可预测的顺序添加行。创建索引创建一个指向堆行的辅助文件，并将索引条目与创建索引命令中指定的列中的值进行排序。通过在索引中快速查找所需的值，可以跟踪索引指针，以快速找到匹配的堆行。

对于大多数情况，创建索引就满足对性能的需求。但是有些情况，索引的有序与堆表的无序，会导致性能问题。这就是cluster有用的地方，它对堆进行排序，以匹配其中一个索引的排序。（非btree索引不能被聚簇，因为它们缺乏线性排序。）

堆表排序

堆表排序是如何提高性能。如果您只寻找一行，那么它在表文件中的位置并不重要。但是，假设基于索引列，检索连续的1000行，那么，我们可以快速找到1000个匹配的索引条目，那么那1000行数据呢？如果它们分散在1000个8kB的页上，则需要许多I/O访问。如果这些行都在相邻的页面上，这将减少所需的页面的数量。如果这些堆页面都在内存中，这可能并不重要，但如果有些堆页面在存储中，减少堆访问的数量可以产生显著的性能好处。

堆表排序什么时候有助于提高性能？设想以下三种情况：

1. 索引列单个值，具有大量数据，例如，检索colname=5返回大量数据
2. 访问索引列的范围值，例如，colname>=10 and colname<20
3. 读取经常访问的值，例如未结算订单

对于这些工作负载，对堆表的页进行排序可以大大减少I/O访问的次数。

使用cluster有两个缺点。

1. 当 cluster 命令创建一个新的堆文件以匹配索引时，禁止其他会话读取或写入该表。
2. 与索引组织表不同，堆表不会保持cluster状态——其后的插入和更新操作会将行以非顺序的放置在堆中，导致堆表变得不那么有序——将需要稍后的cluster操作来恢复理想的排序。然而，cluster 确实会记住以前的cluster 操作，并且可以按照预设，自动恢复所有曾经cluster的表。

示例

在下面的示例中，行由于它们的插入顺序而自动排序，并且对pg_stats 的查询验证相关性为1：

准备数据表
CREATE TABLE public.cluster_test (col1 INTEGER, col2 TEXT);
 
CREATE INDEX i_cluster_test ON cluster_test (col1 );
 
INSERT INTO public.cluster_test
        SELECT *, repeat('col1', 250) FROM generate_series(1, 100000);
 
-- 计算相关统计数据
ANALYZE cluster_test;
 
-- 查看视图 pg_stats，correlation物理行顺序和列值逻辑顺序之间的统计关联。其范围从-1到+1。当值接近-1或+1时，在列上的一个索引扫描被认为比值接近0时的代价更低，因为这种情况减少了对磁盘的随机访问
SELECT correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'cluster_test' AND attname = 'col1';
 correlation
-------------
           1
 
EXPLAIN (analyze,buffers) SELECT * FROM cluster_test WHERE col1 < 74000;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using i_cluster_test on cluster_test  (cost=0.29..4863.41 rows=74064 width=258) (actual time=0.022..9.850 rows=73999 loo
ps=1)
   Index Cond: (x < 74000)
   Buffers: shared hit=2945
 Planning Time: 0.067 ms
 Execution Time: 11.931 ms
(5 行记录)
 
EXPLAIN SELECT * FROM cluster_test WHERE col1 < 75000;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Seq Scan on cluster_test  (cost=0.00..4954.00 rows=75038 width=258) (actual time=0.011..10.739 rows=74999 loops=1)
   Filter: (x < 75000)
   Rows Removed by Filter: 25001
   Buffers: shared hit=3704
 Planning Time: 0.071 ms
 Execution Time: 12.852 ms
(6 行记录)
 
您可以看到优化器在 74k 和 75k 行访问之间从索引扫描切换到顺序扫描。

下一个示例以随机顺序插入行，这会产生接近于零的相关性，以及停止使用索引的较低值，即 31k 与 75k：

--准备数据
DELETE FROM public.cluster_test; 
  
INSERT INTO public.cluster_test
        SELECT *, repeat('col1', 250) FROM generate_series(1, 100000) ORDER BY random();
 
--  
ANALYZE cluster_test;
 
-- 
SELECT correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'cluster_test' AND attname = 'col1';
  correlation
---------------
0.0016185313
 
--堆表的实际存储，首尾行
select * from (select ctid,col1 from cluster_test order by ctid asc limit 2)
union all
select * from (select ctid,col1 from cluster_test order by ctid desc limit 2)
order by ctid
;
 
   ctid    |   col1
-----------+-------
 (14285,6) | 75935
 (14285,7) | 30601
 (28571,2) | 48774
 (28571,3) | 81557
 
 
 
EXPLAIN SELECT * FROM cluster_test WHERE col1 < 10;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Index Scan using i_cluster_test on cluster_test  (cost=0.42..40.57 rows=9 width=1008) (actual time=0.005..0.022 rows=9 loops=1)
   Index Cond: (col1 < 10)
   Buffers: shared hit=12
 Planning Time: 0.093 ms
 Execution Time: 0.036 ms
(5 行记录)
 
EXPLAIN SELECT * FROM cluster_test WHERE col1 < 15;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on cluster_test  (cost=4.53..59.94 rows=14 width=1008) (actual time=0.011..0.035 rows=14 loops=1)
   Recheck Cond: (col1 < 15)
   Heap Blocks: exact=14
   Buffers: shared hit=17
   ->  Bitmap Index Scan on i_cluster_test  (cost=0.00..4.52 rows=14 width=0) (actual time=0.006..0.006 rows=14 loops=1)
         Index Cond: (col1 < 15)
         Buffers: shared hit=3
 Planning Time: 0.140 ms
 Execution Time: 0.060 ms
 
 
EXPLAIN SELECT * FROM cluster_test WHERE col1 < 31000;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on cluster_test  (cost=1595.40..43203.42 rows=30836 width=1008) (actual time=4.229..15.893 rows=30999 loops=1)
   Recheck Cond: (col1 < 31000)
   Heap Blocks: exact=13206
   Buffers: shared hit=13547
   ->  Bitmap Index Scan on i_cluster_test  (cost=0.00..1587.69 rows=30836 width=0) (actual time=2.837..2.837 rows=30999 loops=1)
         Index Cond: (col1 < 31000)
         Buffers: shared hit=341
 Planning Time: 0.071 ms
 Execution Time: 16.786 ms
 
EXPLAIN SELECT * FROM cluster_test WHERE col1 < 32000;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on cluster_test  (cost=0.00..44108.00 rows=31893 width=1008) (actual time=9.072..25.702 rows=31999 loops=1)
   Filter: (col1 < 32000)
   Rows Removed by Filter: 68001
   Buffers: shared hit=42858
 Planning Time: 0.076 ms
 Execution Time: 26.683 ms
(6 行记录)
 
请注意，它在10行之后从索引扫描切换到位图堆扫描，因为统计数据表明匹配的行随机存储在堆中。当使用堆排序与索引排序非常匹配的索引时，与索引扫描相比，使用位图堆扫描没有任何价值。您可以看到优化器在 31k 和 32k 行访问之间从索引扫描切换到顺序扫描，比堆表有序时，更早的切换和更长的用时。
 
使用cluster，我们可以强制堆匹配索引排序，并再次使得index scan可用于更多行：
 
CLUSTER cluster_test USING i_cluster_test;
 
ANALYZE cluster_test;
 
SELECT correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'cluster_test' AND attname = 'col1';
 correlation
-------------
           1
 
--表的实际存储位置，首尾行
select * from (select ctid,col1 from cluster_test order by ctid asc limit 2)
union all
select * from (select ctid,col1 from cluster_test order by ctid desc limit 2)
order by ctid
;
col1	  |    col1
-----------+--------
 (0,1)     |      1
 (0,2)     |      2
 (14285,4) |  99999
 (14285,5) | 100000
 
 
 
 
EXPLAIN SELECT * FROM cluster_test WHERE col1 < 74000;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using i_cluster_test on cluster_test  (cost=0.29..4836.03 rows=73642 width=258)
   Index Cond: (col1 < 74000)
 
EXPLAIN SELECT * FROM cluster_test WHERE col1 < 75000;
                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on cluster_test  (cost=0.00..4954.00 rows=74696 width=258)
   Filter: (col1 < 75000)

总结

当然，explain只是估计检索数据的最有效方式——需要适当的性能测试来确定cluster何时有用。

连续时间的数据，不需要cluster table，通常近期的数据是最常访问的。如果表几乎没有更新和删除，新行会追加到文件的末尾，自然有良好的相关排序，可以最佳的被读取处理。但是，如果有很多更新/删除，插入和更新的行被放置在表中任何free空间中，因此连续性会很低。如果对执行过了cluster的表，进行大量更新/删除，并且只访问最近的数据，这样可能会得到一个不准确的高相关值和低效的计划，因为虽然大多数行都被cluster了，而新增的行是最经常访问的，没有匹配索引加以排序。 表分区可以被认为是一种粗略的聚簇，它可以通过使用基于时间的分区来改善数据局部性来帮助处理这些工作负载。

以上是我对cluster table的一些思考，希望这点认知能为大家提供了一些有关何时使用聚簇表的提示。