Memsql TPCH查询

Question

我正在对memsql进行TPCH查询。我对memsql还不熟悉。我无法将5个TPCH查询转换为memsql查询。无法使用memsql的FOREIGH SHARD密钥概念实现外键关系。请帮助我将8个表创建查询中的5个隐藏到memsql查询中。尽力了，但面临着许多不同的问题。

CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL PRIMARY KEY,
                   R_NAME       CHAR(25) NOT NULL,
                   R_COMMENT    VARCHAR(152)
                   );

CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL PRIMARY KEY,
                   N_NAME       CHAR(25) NOT NULL,
                   N_REGIONKEY  INTEGER NOT NULL REFERENCES REGION(R_REGIONKEY),
                   N_COMMENT    VARCHAR(152)
                   );

CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL PRIMARY KEY,
                 P_NAME        VARCHAR(55) NOT NULL,
                 P_MFGR        CHAR(25) NOT NULL,
                 P_BRAND       CHAR(10) NOT NULL,
                 P_TYPE        VARCHAR(25) NOT NULL,
                 P_SIZE        INTEGER NOT NULL,
                 P_CONTAINER   CHAR(10) NOT NULL,
                 P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                 P_COMMENT     VARCHAR(23) NOT NULL
                 );

CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL PRIMARY KEY,
                    S_NAME        CHAR(25) NOT NULL,
                    S_ADDRESS     VARCHAR(40) NOT NULL,
                    S_NATIONKEY   INTEGER NOT NULL REFERENCES NATION(N_NATIONKEY),
                    S_PHONE       CHAR(15) NOT NULL,
                    S_ACCTBAL     DECIMAL(15,2) NOT NULL,
                    S_COMMENT     VARCHAR(101) NOT NULL
                    );

CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL REFERENCES PART(P_PARTKEY),
                    PS_SUPPKEY     INTEGER NOT NULL REFERENCES SUPPLIER(S_SUPPKEY),
                    PS_AVAILQTY    INTEGER NOT NULL,
                    PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
                    PS_COMMENT     VARCHAR(199) NOT NULL,
                    PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY)
                    );

CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL PRIMARY KEY,
                    C_NAME        VARCHAR(25) NOT NULL,
                    C_ADDRESS     VARCHAR(40) NOT NULL,
                    C_NATIONKEY   INTEGER NOT NULL REFERENCES NATION(N_NATIONKEY),
                    C_PHONE       CHAR(15) NOT NULL,
                    C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
                    C_MKTSEGMENT  CHAR(10) NOT NULL,
                    C_COMMENT     VARCHAR(117) NOT NULL
                    );

CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL PRIMARY KEY,
                   O_CUSTKEY        INTEGER NOT NULL REFERENCES CUSTOMER(C_CUSTKEY),
                   O_ORDERSTATUS    CHAR(1) NOT NULL,
                   O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
                   O_ORDERDATE      DATE NOT NULL,
                   O_ORDERPRIORITY  CHAR(15) NOT NULL,
                   O_CLERK          CHAR(15) NOT NULL,
                   O_SHIPPRIORITY   INTEGER NOT NULL,
                   O_COMMENT        VARCHAR(79) NOT NULL
                   );

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL REFERENCES ORDERS(O_ORDERKEY),
                    L_PARTKEY     INTEGER NOT NULL REFERENCES PART(P_PARTKEY),
                    L_SUPPKEY     INTEGER NOT NULL REFERENCES SUPPLIER(S_SUPPKEY),
                    L_LINENUMBER  INTEGER NOT NULL,
                    L_QUANTITY    DECIMAL(15,2) NOT NULL,
                    L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                    L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                    L_TAX         DECIMAL(15,2) NOT NULL,
                    L_RETURNFLAG  CHAR(1) NOT NULL,
                    L_LINESTATUS  CHAR(1) NOT NULL,
                    L_SHIPDATE    DATE NOT NULL,
                    L_COMMITDATE  DATE NOT NULL,
                    L_RECEIPTDATE DATE NOT NULL,
                    L_SHIPINSTRUCT CHAR(25) NOT NULL,
                    L_SHIPMODE     CHAR(10) NOT NULL,
                    L_COMMENT      VARCHAR(44) NOT NULL,
                    PRIMARY KEY (L_ORDERKEY,L_LINENUMBER),
                    FOREIGN KEY (L_PARTKEY,L_SUPPKEY) REFERENCES PARTSUPP(PS_PARTKEY, PS_SUPPKEY)
                    );                                                     

我能够在memsql中创建前3个表，但不能保留表。第1和第3次查询非常简单，并且可以正常工作。我可以创造第二表，但再次不确定这是否是正确的方式来实现。

CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                   N_NAME       CHAR(25) NOT NULL,
                   N_REGIONKEY  INTEGER NOT NULL,
                   N_COMMENT    VARCHAR(152),
                   FOREIGN SHARD KEY (N_REGIONKEY) REFERENCES REGION (R_REGIONKEY), 
           PRIMARY KEY (N_NATIONKEY, N_REGIONKEY)
                   );

是否可以在memsql中只创建复制表而不创建分区？怎么做？

Answer 1

由于MemSQL不支持引用完整性，所以外部碎片键是优化的辅助工具，而不是必需的。但是，在创建表时，外接碎片键允许您知道两个表可以在该键上本地连接(没有网络流量)。但是，优化器不需要使用外部碎片键来利用此数据局部性。

从ORDERS和LINEITEM表开始：

CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL PRIMARY KEY,
               O_CUSTKEY        INTEGER NOT NULL,
               O_ORDERSTATUS    CHAR(1) NOT NULL,
               O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
               O_ORDERDATE      DATE NOT NULL,
               O_ORDERPRIORITY  CHAR(15) NOT NULL,
               O_CLERK          CHAR(15) NOT NULL,
               O_SHIPPRIORITY   INTEGER NOT NULL,
               O_COMMENT        VARCHAR(79) NOT NULL,
               KEY (O_CUSTKEY)
               );

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                L_PARTKEY     INTEGER NOT NULL,
                L_SUPPKEY     INTEGER NOT NULL,
                L_LINENUMBER  INTEGER NOT NULL,
                L_QUANTITY    DECIMAL(15,2) NOT NULL,
                L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                L_TAX         DECIMAL(15,2) NOT NULL,
                L_RETURNFLAG  CHAR(1) NOT NULL,
                L_LINESTATUS  CHAR(1) NOT NULL,
                L_SHIPDATE    DATE NOT NULL,
                L_COMMITDATE  DATE NOT NULL,
                L_RECEIPTDATE DATE NOT NULL,
                L_SHIPINSTRUCT CHAR(25) NOT NULL,
                L_SHIPMODE     CHAR(10) NOT NULL,
                L_COMMENT      VARCHAR(44) NOT NULL,
                PRIMARY KEY (L_ORDERKEY,L_LINENUMBER),
                FOREIGN SHARD KEY (L_ORDERKEY) REFERENCES ORDERS (O_ORDERKEY),
                KEY (L_PARTKEY),
                KEY (L_SUPPKEY)
                );  

在这种情况下，我们知道我们可以利用ORDERS和LINEITEM之间的本地连接，因为它们都是在ORDERKEY上分割的。ORDERS和LINEITEM是TPCH中最大的两个表，因此我们希望确保它们可以在本地连接。因为ORDERS的主键是O_ORDERKEY，所以我不需要为ORDERS指定一个shard键。MemSQL将由O_ORDERKEY自动分解。

我还在剩余的外键列上了二级索引。这很有用，因为外键上会有连接。

将这些概念应用于PART、PARTSUPP、SUPPLIER和CUSTOMER

CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL PRIMARY KEY,
                C_NAME        VARCHAR(25) NOT NULL,
                C_ADDRESS     VARCHAR(40) NOT NULL,
                C_NATIONKEY   INTEGER NOT NULL,
                C_PHONE       CHAR(15) NOT NULL,
                C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
                C_MKTSEGMENT  CHAR(10) NOT NULL,
                C_COMMENT     VARCHAR(117) NOT NULL,
                KEY(C_NATIONKEY)
                );


CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL PRIMARY KEY,
                S_NAME        CHAR(25) NOT NULL,
                S_ADDRESS     VARCHAR(40) NOT NULL,
                S_NATIONKEY   INTEGER NOT NULL,
                S_PHONE       CHAR(15) NOT NULL,
                S_ACCTBAL     DECIMAL(15,2) NOT NULL,
                S_COMMENT     VARCHAR(101) NOT NULL,
                KEY(S_NATIONKEY)
                );

CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL PRIMARY KEY,
             P_NAME        VARCHAR(55) NOT NULL,
             P_MFGR        CHAR(25) NOT NULL,
             P_BRAND       CHAR(10) NOT NULL,
             P_TYPE        VARCHAR(25) NOT NULL,
             P_SIZE        INTEGER NOT NULL,
             P_CONTAINER   CHAR(10) NOT NULL,
             P_RETAILPRICE DECIMAL(15,2) NOT NULL,
             P_COMMENT     VARCHAR(23) NOT NULL
             );

CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
                PS_SUPPKEY     INTEGER NOT NULL,
                PS_AVAILQTY    INTEGER NOT NULL,
                PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
                PS_COMMENT     VARCHAR(199) NOT NULL,
                PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
                SHARD KEY(PS_PARTKEY),
                KEY(PS_SUPPKEY)
                );

虽然PARTSUPP和PART都是在相同的键(PARTKEY)上切分的，但我不需要指定外接的切分键就可以利用它们之间的本地连接；优化器将自动获取它们。

在回答最后一个问题时，MemSQL允许您创建一个复制表，而不是分区表。这称为参考表，对于NATION和REGION表很有用，因为它们非常小。对于运行分布式查询来说，引用表不是必需的，但它是一种有用的优化。

CREATE REFERENCE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL PRIMARY KEY,
               R_NAME       CHAR(25) NOT NULL,
               R_COMMENT    VARCHAR(152)
               );

CREATE REFERENCE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL PRIMARY KEY,
               N_NAME       CHAR(25) NOT NULL,
               N_REGIONKEY  INTEGER NOT NULL,
               N_COMMENT    VARCHAR(152)
               );

有关所描述的所有内容的更多文档，请参见：sql/