分區表使用建議 · k8s原理與實踐

# 分區表使用建議通常當表的數據量非常大，以致于可能使數據庫空間緊張，或者由于表非常大導致相關 SQL 查詢性能變慢時，可以考慮使用分區表。使用分區表時要選擇合適的拆分鍵以及拆分策略。如果是日志類型的大表，根據時間類型的列做 RANGE 分區是最合適的。如果是并發訪問非常高的表，結合業務特點選擇能滿足絕大部分核心業務查詢的列作為拆分鍵是最合適的。無論選哪個列做為分區鍵，都不大可能滿足所有的查詢性能。分區表中的全局唯一性需求可以通過主鍵約束和唯一約束實現。OceanBase 數據庫的分區表的主鍵約束和唯一鍵約束必須包含拆分鍵。唯一約束也是一個全局索引。全局唯一的需求也可以通過本地唯一索引實現，只需要在唯一索引里包含拆分鍵。 **示例：創建有唯一性需求的分區表** ~~~ obclient> CREATE TABLE account( id bigint NOT NULL PRIMARY KEY , name varchar(50) NOT NULL UNIQUE , value number NOT NULL , gmt_create timestamp DEFAULT current_timestamp NOT NULL , gmt_modified timestamp DEFAULT current_timestamp NOT NULL ) PARTITION BY HASH(id) PARTITIONS 16; Query OK, 0 rows affected (0.16 sec) obclient> CREATE TABLE account2( id bigint NOT NULL PRIMARY KEY , name varchar(50) NOT NULL , value number NOT NULL , gmt_create timestamp DEFAULT current_timestamp NOT NULL , gmt_modified timestamp DEFAULT current_timestamp NOT NULL ) PARTITION BY HASH(id) PARTITIONS 16; Query OK, 0 rows affected (0.11 sec) obclient> CREATE UNIQUE INDEX account2_uk ON account2(name, id) LOCAL ; Query OK, 0 rows affected (0.73 sec) obclient> show indexes from account; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | account | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | | account | 0 | name | 1 | name | A | NULL | NULL | NULL | | BTREE | available | | YES | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ 2 rows in set (0.01 sec) obclient> show indexes from account2; +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ | account2 | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | | account2 | 0 | account2_uk | 1 | name | A | NULL | NULL | NULL | | BTREE | available | | YES | | account2 | 0 | account2_uk | 2 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+ 3 rows in set (0.00 sec) obclient> SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_schema='TPCCDB' AND table_name LIKE 'ACCOUNT%'; +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | def | tpccdb | PRIMARY | tpccdb | account | PRIMARY KEY | | def | tpccdb | name | tpccdb | account | UNIQUE | | def | tpccdb | PRIMARY | tpccdb | account2 | PRIMARY KEY | | def | tpccdb | account2_uk | tpccdb | account2 | UNIQUE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+ 4 rows in set (0.02 sec) ~~~