基礎操作（建庫、建分布式分區表）__快速入門_雲數據庫 HybridDB-阿裏雲

=> create table vtbl(id serial, key integer, value text, shape cuboid, location geometry, comment text) distributed by (key);
CREATE TABLE

當不指定分布鍵的時候（即不帶後麵的”distributed by (key)”），GP會默認對id字段以round-robin的方式進行隨機分配。

分布鍵

分布鍵對於查詢性能至關重要。均勻為分布鍵選擇的第一大原則，選取更有業務意義的字段，並非必須選擇原庫的主鍵。參考中，《Greenplum 數據庫最佳實踐》做了很好的說明。

查詢

構造數據：

• 創建隨機字符串生成函數和類型

CREATE OR REPLACE FUNCTION random_string(integer) RETURNS text AS $body$
SELECT array_to_string(array
                         (SELECT substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
                                           FROM (ceil(random()*62))::int
                                           FOR 1)
                          FROM generate_series(1, $1)), ''); $body$ LANGUAGE SQL VOLATILE;
CREATE TYPE cuboid AS (LENGTH integer, width integer, height integer);

• 創建分布表和構造數據

CREATE TABLE tbl(id serial, KEY integer, value text, shape cuboid, locate geometry, COMMENT text) distributed by (key);
INSERT INTO tbl(KEY, COMMENT, shape, locate, value) SELECT KEY, COMMENT, shape, ST_GeomFromText(locate) AS locate, value FROM (SELECT (a.t + 1) AS KEY, random_string(ceil(random() * 24)::integer) AS COMMENT, row(ceil(random() * 36 + 49), ceil(random() * 6 + 99), ceil(random() * 12 + 199))::cuboid AS shape, 'POINT(' || ceil(random() * 36 + 99) || ' ' || ceil(random() * 24 + 50) || ')' AS locate, (CASE WHEN a.b = 1 THEN 'red' WHEN a.b = 2 THEN 'green' WHEN a.b = 3 THEN 'yellow' WHEN a.b = 4 THEN 'blue' END) AS value FROM (SELECT ceil(random() * 4) b, t FROM generate_series(0, 99999) AS t) AS a) AS t;

• 查詢

=> select * from vtbl where key = 13944;
   id   |  key  | value |    shape     |                   locate                   |    comment     
--------+-------+-------+--------------+--------------------------------------------+----------------
 113944 | 13944 | red   | (84,102,206) | 01010000000000000000405D400000000000C05140 | 22EaPlVqvfSgpq
(1 row)
Time: 513.101 ms

• PG上的查詢

=> select * from tbl where key = 13944;
  id   |  key  | value |    shape     |                   locate                   |   comment    
-------+-------+-------+--------------+--------------------------------------------+--------------
 13944 | 13944 | green | (55,105,207) | 01010000000000000000C05C400000000000004C40 | 3wQrCJqnHPds
(1 row)
Time: 1162.243 ms

在同樣的表結構、記錄數和沒有添加任何索引的情況下，GP的查詢要比PG的快一些。這個GP的實例是兩個Segments，當記錄數更多、Segments更多的時候，理論上差距會更明顯。