SQL性能最佳实践

此页面提供了优化hubble查询性能的最佳实践。

DML最佳实践

使用多行语句而不是多个单行语句

对于INSERT、UPSERT和DELETE语句，单个多行语句比多个单行语句更快。尽可能对DML查询使用多行语句，而不是多个单行语句。

在没有二级索引的表上使用UPSERT代替INSERT ON CONFLICT

当插入或者更新表的所有列时，表没有二级索引，建议使用UPSERT语句而不是等效INSERT ON CONFLICT语句。虽然INSERT ON CONFLICT总是执行读取以确定必要的写入，但UPSERT语句只写入而不读取，从而使其速度更快。UPSERT对于有二级索引的表，和INSERT ON CONFLICT之间没有性能差异。

批量插入最佳实践

使用多行INSERT语句向现有表中批量插入

要将数据批量插入到现有表中，在一个多行INSERT语句中批量处理多行。通过监控不同批量大小(10行、100行、1000行)的数据性能，通过实验确定应用程序的最佳批量大小。

插入多行示例

insert into cust_info (id, city, name, address, credit_card) values
    ('8', 'beijing', 'LUCY', '400 Broad St', '1111111111'),
    ('9', 'new york', 'MIKE', '214 43rd St',  '2222222222'),
    ('10', 'shanghai', 'daiwei', '04 41rd St', '3333333333');

使用IMPOR代替INSERT批量插入到新表中

要将数据批量插入到全新的表中，该IMPORT语句的性能优于INSERT。

批量删除最佳实践

使用TRUNCATE而不是DELETE删除表中的所有行

该TRUNCATE语句通过删除表并重新创建同名的新表来删除表中的所有行。这比使用DELETE执行得更好，后者执行多个事务以删除所有行。

批量删除过期数据

支持表行的生存时间(TTL)过期，也称为行级TTL。行级 TTL 是一种机制，通过该机制，表中的行被视为'已过期'，并且一旦这些行的存储时间超过指定的过期时间，就可以自动删除这些行。

使用批量删除删除大量行

要删除大量行，我们建议反复删除成批的行，直到删除所有不需要的行。

列族

分配列族

列族是表中的一组列，作为单个键值对存储在底层键值存储中。创建表时，所有列都存储为单个列族。在大多数情况下，这种默认方法可确保高效的键值存储和性能。然而，当频繁更新的列与很少更新的列组合在一起时，很少更新的列仍然会在每次更新时被重写。特别是当很少更新的列很大时，将它们分配给不同的列族会提高性能。

唯一ID最佳实践

在hubble这样的分布式数据库中生成唯一 ID的最佳实践与单节点数据库有很大不同。为单节点数据库生成唯一 ID的传统方法包括：

使用SERIAL列的伪类型来生成随机的唯一ID。这可能会导致性能瓶颈，因为在时间上彼此靠近生成的ID具有相似的值，并且在表的存储中物理上彼此靠近。
通过使用带有往返的事务来生成单调递增的ID。这具有非常高的性能成本，因为它使所有INSERT事务都等待轮到他们插入下一个ID。在某些情况下，使用更改数据捕获 (CDC)可以帮助避免严格的ID排序要求。如果想避免严格的 ID排序要求，则可以使用下面概述的一种更高性能的ID策略。

上述方法可能会在hubble中为读取和写入创建热点。为避免此问题，建议采用以下方法:

方法	优点	缺点
使用多列主键	如果做得好，可能最快	复杂，需要预先设计和测试以确保性能
用于`UUID`生成唯一ID	很好地分散负载，简单的选择	可能会保留一些性能，需要其他列在查询中有用
与从句一起使用`INSERT RETURNING`	易于查询，熟悉的设计	性能比其他选项慢

使用多列主键

设计良好的多列主键可以产生比UUID主键更好的性能，但它需要更多的前期架构设计工作。要获得最佳性能，请确保任何单调递增的字段都位于主键的第一列之后。如果操作正确，这样的复合主键应该会产生：

一个单调递增的列，它是主键的一部分，这在查询中也很有用。
主键中有足够的随机性以在集群中相对均匀地分布表数据查询负载，这将避免热点。足够的随机性指主键的前缀应该相对均匀地分布在其域中。

示例数据

create table cust_info (
    name string,
    res_timestamp TIMESTAMP,
    CONSTRAINT ci_pk PRIMARY KEY(name, res_timestamp)
);

这将使以下查询变得高效

select * from cust_info
         where  name = 'mike'
       ORDER BY res_timestamp DESC
          LIMIT 5;

要了解原因，看一下EXPLAIN输出

EXPLAIN (VERBOSE)
select * from cust_info
         where  name = 'mike'
       ORDER BY res_timestamp DESC
          LIMIT 5;

                    info
--------------------------------------------
  distribution: local
  vectorized: false

  • revscan
    columns: (name, res_timestamp)
    ordering: -res_timestamp
    estimated row count: 5 (missing stats)
    table: cust_info@ci_pk
    spans: /"mike"-/"mike"/PrefixEnd
    limit: 5

上述查询遵循索引子句中所有列的索引最佳实践。

用于UUID生成唯一ID

要自动生成唯一的行标识符，使用UUID带有gen_random_uuid()函数的列作为默认值：

create table cust_info (
        id uuid not null DEFAULT gen_random_uuid(),
        city string not null,
        name string null,
        address string null,
        credit_card string null,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);

insert into cust_info (name, city) values ('liu dehua', 'beijing'), ('gao yuanyuan', 'shanghai'), ('zhen zidan', 'hk');

select * from cust_info;

                  id                  |   city   |     name     | address | credit_card
---------------------------------------+----------+--------------+---------+--------------
  3ea70a2e-51c6-4c39-9c7a-87b55a3c2d3c | beijing  | liu dehua    | NULL    | NULL
  95b1d540-eb0f-4c80-84d0-3bb576fd6487 | hk       | zhen zidan   | NULL    | NULL
  21457791-1d8f-44c2-9cf7-1f9df79518ba | shanghai | gao yuanyuan | NULL    | NULL

INSERT与子句一起使用RETURNING以生成唯一ID

生成随机唯一ID

假设表架构如下：

CREATE TABLE test (
    ID1 INT,
    ID2 INT,
    ID3 INT DEFAULT 1,
    PRIMARY KEY (ID1,ID2)
  );

生成随机唯一ID的常用方法是使用以下语句：

BEGIN;

INSERT INTO test VALUES (1,1);

SELECT * FROM X WHERE ID1=1 AND ID2=1;

COMMIT;

性能最佳实践是使用RETURNING子句，而不是使用事物：

INSERT INTO test VALUES (1,1),(2,2),(3,3)
    RETURNING ID1,ID2,ID3;