Table Design in PolarDB-X - User Table

Introduction: This series aims to describe a specific business scenario, give an example of building a table, and help everyone use PolarDB-X better. The topic of this issue is: User Tables.

Description of Requirement

Most businesses will have a user table to store user data, such as this user table:

user_id bigint AUTO_INCREMENT,
user_name varchar(64),
mobile_phone varchar(64),
email varchar(64),
enc_password varchar(256),
address varchar(128),
other_info1 varchar(128),
other_info2 varchar(128),
PRIMARY KEY (user_id)
)

For this table, there are generally the following business operations:

● Registration, which is characterized by ensuring that the user name, mobile phone number, email address, etc. are unique:

INSERT INTO users VALUES (?, ?, ?)
● Login, most APPs now support multiple dimensions such as mobile phone number, email address, user name, etc., so there will be various types of SQL:

//Log in according to the user name (user_name):
SELECT *
FROM users
WHERE user_name = ?;

//Log in according to the mobile phone number (mobile_phone):
SELECT *
FROM users
WHERE mobile_phone = ?;

//Log in according to the mailbox (email):
SELECT *
FROM users
WHERE email = ?;
● After logging in, the user ID (user_id) is generally used in the system to query or update user information:

SELECT *
FROM users
WHERE user_id = ?;

UPDATE users
SET xxxx = ?
WHERE user_id = ?;
For such a table, how should we design it in PolarDB-X?

Here we give two examples based on the MODE of the database (the MODE parameter of the database in PolarDB-X: https://help.aliyun.com/document_detail/416411.html):

DRDS mode
In the database of DRDS schema, we need to design the partition key of the table.

The query conditions of the users table are user_id, user_name, mobile_phone, and email. The query volume of these four conditions is similar, and they are all online queries. For traditional sharding and sharding middleware, only one partition key can be selected for a table, so no matter which one is chosen as the partition key, it will be a disaster for the query of the other three conditions.

PolarDB-X supports global index (what is global index: https://zhuanlan.zhihu.com/p/395415647), then this problem is solved very well, we can build a table according to the following statement:

CREATE DATABASE drds_test MODE='drds';
use drds_test;
CREATE TABLE users (
user_id bigint AUTO_INCREMENT,
user_name varchar(64),
mobile_phone varchar(64),
email varchar(64),
enc_password varchar(256),
address varchar(128),
other_info1 varchar(128),
other_info2 varchar(128),
PRIMARY KEY (user_id)
) DBPARTITION BY HASH(user_id);
CREATE GLOBAL UNIQUE INDEX gsi_users_user_name ON users (user_name) DBPARTITION BY HASH(user_name);
CREATE GLOBAL UNIQUE INDEX gsi_users_mobile_phone ON users (mobile_phone) DBPARTITION BY HASH(mobile_phone);
CREATE GLOBAL UNIQUE INDEX gsi_users_email ON users (email) DBPARTITION BY HASH(email);
In this way, we created three globally unique indexes on user_name, mobile_phone, and email respectively. For the above query SQL, each will be very efficient. At the same time, the uniqueness in the registration scenario will also be guaranteed.

Of course, these index creation statements can also be directly merged into the table creation statement, related syntax reference: https://help.aliyun.com/document_detail/316584.html:

DROP TABLE users;
CREATE TABLE users (
user_id bigint AUTO_INCREMENT,
user_name varchar(64),
mobile_phone varchar(64),
email varchar(64),
enc_password varchar(256),
address varchar(128),
other_info1 varchar(128),
other_info2 varchar(128),
PRIMARY KEY (user_id),
UNIQUE GLOBAL KEY gsi_users_email (email) DBPARTITION BY HASH(email),
UNIQUE GLOBAL KEY gsi_users_mobile_phone (mobile_phone) DBPARTITION BY HASH(mobile_phone),
UNIQUE GLOBAL KEY gsi_users_user_name (user_name) DBPARTITION BY HASH(user_name)
) DBPARTITION BY hash(user_id);
In addition, if you want to further improve the performance of the query and avoid the cost of returning the global index to the table, you can also create the global index as a global clustered index. This will consume more space, but the query performance will be higher. E.g:

CREATE GLOBAL CLUSTERED UNIQUE INDEX gsi_clustered_users_user_name ON users (user_name) DBPARTITION BY HASH(user_name);
Note: The above usage also works for PolarDB-X 1.0 (version >= 5.4.12).

AUTO mode

For AUTO mode, you don't need to pay attention to the partition key and other information, just create a table in MySQL:

CREATE DATABASE auto_test MODE='auto';
use auto_test;
CREATE TABLE users(
user_id bigint auto_increment,
user_name varchar(64),
mobile_phone varchar(64),
email varchar(64),
enc_password varchar(256),
address varchar(128),
other_info1 varchar(128),
other_info2 varchar(128),
PRIMARY KEY(user_id),
UNIQUE KEY uk_user_name(user_name),
UNIQUE KEY uk_mobile_phone(mobile_phone),
UNIQUE KEY uk_email(email)
);
The same can achieve the same effect as manual partitioning.

We can use the EXPLAIN statement to view the execution plan:

EXPLAIN SELECT * FROM users WHERE mobile_phone = 1;

| Project(user_id="user_id", user_name="user_name", mobile_phone="mobile_phone", email="email", enc_password="enc_password", address="address", other_info1="other_info1", other_info2="other_info2" ) |
| BKAJoin(condition="user_id = user_id", type="inner") |
| IndexScan(tables="uk_mobile_phone_$1ace[p16]", sql="SELECT `user_id`, `mobile_phone` FROM `uk_mobile_phone_$1ace` AS `uk_mobile_phone_$1ace` WHERE (`mobile_phone` = ?)") |
| Gather(concurrent=true)

As you can see, this SQL will correctly use the index to query instead of a full table scan.