Suggestions on using partitioned tables

This topic provides suggestions on using partitioned tables in OceanBase Database in MySQL mode.

Background

You can use partitioned tables when the amount of data is so large that it uses up the storage space or when the tables are so large that they slow down the performance of SQL queries.

To use partitioned tables, you need to use the appropriate partitioning key and strategy. For large logs, RANGE partitioning with datetime columns is the best choice. For tables with high concurrency, it is best to choose one or more columns that satisfy most of the core business query requirements as the partitioning key based on the business characteristics. No column can meet all query requirements.

You can use the PRIMARY KEY and UNIQUE constraints to ensure global uniqueness in a partitioned table. In OceanBase Database, the PRIMARY KEY and UNIQUE constraints of a partitioned table must contain the partitioning key. The UNIQUE constraint is also a global index. You can also use a local UNIQUE index to ensure global uniqueness. To do this, you only need to include the partitioning key in the UNIQUE index.

Examples

Create a partitioned table with a UNIQUE constraint.

1. Create a table named account.

   CREATE TABLE account(
            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;
   

2. Create an index named account_uk.

    CREATE UNIQUE INDEX account_uk ON account(name, id) LOCAL ;
   

3. View the indexes on the account table.

   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 | account_uk |            1 | name        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | account |          0 | account_uk |            2 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   3 rows in set
   

4. View the constraints on the account table.

   obclient>  SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_schema='data_ny' AND table_name='ACCOUNT';
   +--------------------+-------------------+-----------------+--------------+------------+-----------------+
   | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
   +--------------------+-------------------+-----------------+--------------+------------+-----------------+
   | def                | data_ny           | PRIMARY         | data_ny      | account    | PRIMARY KEY     |
   | def                | data_ny           | account_uk      | data_ny      | account    | UNIQUE          |
   +--------------------+-------------------+-----------------+--------------+------------+-----------------+
   2 rows in set