The primary key is a core part of the design of a relational database table. In OceanBase Database, the primary key ensures data uniqueness and optimizes the performance of range queries because data is sorted by the primary key. Different primary key designs are suitable for different business scenarios.
The primary key is a core part of the design of a relational database table. In OceanBase Database, the primary key ensures data uniqueness and optimizes the performance of range queries because data is sorted by the primary key. Different primary key designs are suitable for different business scenarios.
Scenarios without a primary key
Choose a columnar table without a primary key
In a data warehouse or online analytical processing (OLAP) scenario, the business data table contains a large amount of data, and most queries only involve certain columns. If the business does not have special requirements, such as ensuring data uniqueness, avoiding duplicate writes, or performing transactional updates or inserts, creating a columnar table without a primary key is more efficient:
Data import: In an application processing (AP) scenario, data does not need to be sorted by the primary key. Therefore, the performance of importing large amounts of data is faster compared to a table with a primary key.
Reduced management cost of the primary key.
show parameters like "%store_format%";
CREATE TABLE customer (
user_id bigint NOT NULL,
login_time timestamp NOT NULL,
customer_name varchar(100) NOT NULL, -- Assume that the customer name is at most 100 characters long.
phone_num bigint NOT NULL, -- Store the phone number as a bigint.
city_name varchar(50) NOT NULL, -- Assume that the city name is at most 50 characters long.
sex int NOT NULL, -- Store the gender as an int (for example, 0 for female and 1 for male).
id_number varchar(18) NOT NULL, -- Assume that the ID number is at most 18 characters long.
home_address varchar(255) NOT NULL, -- Assume that the home address is at most 255 characters long.
office_address varchar(255) NOT NULL, -- Assume that the office address is at most 255 characters long.
age int NOT NULL -- Store the age as an int.
);
Scenarios with a primary key
In an AP scenario, the default table type is a heap table, which has the following characteristics:
Data storage: Data is stored in the order of insertion without physical sorting.
- Primary key:
- Ensures data uniqueness by including a field that uniquely identifies a record (such as user_id).
- Supports partition key embedding. The primary key must include all partition keys (such as user_id and age) to ensure even data distribution.
- Requires the use of indexes or partitioning strategies to accelerate queries (such as
ORDER BY).
Example: Design a heap-organized table with a primary key.
CREATE TABLE customer (
user_id BIGINT NOT NULL,
login_time TIMESTAMP NOT NULL,
customer_name VARCHAR(100) NOT NULL,
phone_num BIGINT NOT NULL,
city_name VARCHAR(50) NOT NULL,
sex INT NOT NULL,
id_number VARCHAR(18) NOT NULL,
home_address VARCHAR(255) NOT NULL,
office_address VARCHAR(255) NOT NULL,
age INT NOT NULL,
-- The primary key contains all partitioning keys (user_id and age).
PRIMARY KEY (user_id, age, login_time)
)
-- Primary partition: Hash distribution by user_id
PARTITION BY HASH(user_id)
PARTITIONS 128
SUBPARTITION BY RANGE(age)
SUBPARTITION TEMPLATE (
-- Example partition: by age group
SUBPARTITION p_youth VALUES LESS THAN (25), -- Age < 25
SUBPARTITION p_adult VALUES LESS THAN (40), -- 25 ≤ age <40
SUBPARTITION p_middle_aged VALUES LESS THAN (60),-- 40 ≤ age <60
SUBPARTITION p_senior VALUES LESS THAN (MAXVALUE) -- 60 and above
);
### Scenarios with a primary key
* **Ensure data uniqueness:** When data in the table must be unique, a primary key must be designed.
* **Improve query efficiency:** A primary key helps the optimizer generate more efficient query plans. When data queries depend on the primary key, it is recommended to include the query fields in the primary key to leverage the primary key for improved query performance.
```sql
CREATE TABLE customer (
user_id BIGINT NOT NULL,
login_time TIMESTAMP NOT NULL,
customer_name VARCHAR(100) NOT NULL,
phone_num BIGINT NOT NULL,
city_name VARCHAR(50) NOT NULL,
sex INT NOT NULL,
id_number VARCHAR(18) NOT NULL,
home_address VARCHAR(255) NOT NULL,
office_address VARCHAR(255) NOT NULL,
age INT NOT NULL,
-- The primary key contains all partitioning keys (user_id and age).
PRIMARY KEY (user_id, age, login_time)
)
-- Primary partition: hashed and distributed by user_id
PARTITION BY HASH(user_id)
PARTITIONS 128
SUBPARTITION BY RANGE(age)
SUBPARTITION TEMPLATE (
-- Example partition: divided by age group
SUBPARTITION p_youth VALUES LESS THAN (25), -- Age < 25
SUBPARTITION p_adult VALUES LESS THAN (40), -- 25 ≤ age <40
SUBPARTITION p_middle_aged VALUES LESS THAN (60),-- 40 ≤ age <60
SUBPARTITION p_senior VALUES LESS THAN (MAXVALUE) -- 60 and above
);
);