基础操作（建库、建分布式分区表）__快速入门_云数据库 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更多的时候，理论上差距会更明显。