SQL性能最佳实践

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

DML最佳实践

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

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

当需要修改多行数据时，推荐使用单个SQL多行数据的语句：

insert into t values (10, 'aa'), (20, 'bb'), (30, 'cc');

delete from t where id in (10, 20, 30);

不推荐使用多个SQL单行数据的语句：

insert into t values (10, 'aa');
insert into t values (20, 'bb');
insert into t values (30, 'cc');

delete from t where id = 10;
delete from t where id = 20;
delete from t where id = 30;

避免不必要的查询

如非必要，不要总是用select *返回所有列的数据，下面查询是低效的：

select * from students where name = 'Mike';

应该仅查询需要的列信息，例如：

select sex,class from students where  name = 'Mike';

在没有二级索引的表上使用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');

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

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

批量删除最佳实践

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

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

当需要删除一个表的所有数据时，推荐使用TRUNCATE语句：

truncate table t;

或者

truncate  t;

不推荐使用DELETE全表数据：

delete from t;

批量删除过期数据

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

使用批量删除删除大量行

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

列族

分配列族

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

唯一ID最佳实践

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

• 使用SERIAL列的伪类型来生成随机的唯一ID。这可能会导致性能瓶颈，因为在时间上彼此靠近生成的ID具有相似的值，并且在表的存储中物理上彼此靠近。

• 通过使用带有往返的事务来生成单调递增的ID。这具有非常高的性能成本，因为它使所有INSERT事务都等待轮到他们插入下一个ID。在某些情况下，使用更改数据捕获 (CDC)可以帮助避免严格的ID排序要求。如果想避免严格的 ID排序要求，则可以使用下面概述的一种更高性能的ID策略。

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

使用多列主键

设计良好的多列主键可以产生比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

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

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;