Table Design in PolarDB-X - User Table

Requirement description

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

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, characterized by the uniqueness of user name, mobile phone number, email, etc.:

INSERT INTO users VALUES (?, ?, ?)

● Login. Most APPs now support multiple dimensions such as mobile phone number, email address, and user name, so there will be multiple types of SQL:

//Login by user name (user_name):
SELECT *
FROM users
WHERE user_ name = ?;

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

//Log in by email:
SELECT *
FROM users
WHERE email = ?;

● After logging in, the user ID (user_id) is generally used to query or update user information in the system:

SELECT *
FROM users
WHERE user_ id = ?;
UPDATE users
SET xxxx = ?
WHERE user_ id = ?;

How can we design such a table in PolarDB-X?

Here, according to the MODE of the database (the MODE parameter of the database in PolarDB-X: https://help.aliyun.com/document_detail/416411.html ）, give two examples:

DRDS mode

In the DRDS schema database, we need to design the table partitioning key.

The query criteria of the users table are user_ id、user_ name、mobile_ The number of queries for phone and email is almost the same, and they are all online queries. For traditional database/table middleware, only one partition key can be selected for a table. No matter which one is selected as the partition key, the query of the other three conditions will be a disaster.

PolarDB-X supports global indexes (what are global indexes: https://zhuanlan.zhihu.com/p/395415647 ）, then this problem can be solved easily. We can create 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 can_ name、mobile_ Three globally unique indexes are created on phone and email respectively. For the above query SQL, each will be very efficient. At the same time, it will also ensure the uniqueness of the registration scenario.

Of course, these index creating statements can also be directly incorporated into the table building statements. For 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 query performance 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. For example:

CREATE GLOBAL CLUSTERED UNIQUE INDEX gsi_ clustered_ users_ user_ name ON users (user_name) DBPARTITION BY HASH(user_name);

Note: The above usage also applies to PolarDB-X 1.0 (version>=5.4.12).

AUTO mode

For the AUTO mode, you do not need to pay attention to the partition key and other information, such as creating tables 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 effect can be achieved as manual partitioning.

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

EXPLAIN SELECT * FROM users WHERE mobile_ phone = 1;

+------+

| LOGICAL EXECUTIONPLAN |

+------+

| 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) |

| LogicalView(tables="users[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `user_id`, `user_name`, `email`, `enc_password`, `address`, `other_info1`, `other_info2` FROM `users` AS `users` WHERE ((`mobile_phone` = ?) AND (`user_id` IN (...)))") |

| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: beaaba3a |
+------+
8 rows in set (0.32 sec)
可以看到，这个SQL会正确的使用索引来进行查询，而不会进行全表扫描
您输入的可能是: 加利西亚语

| HitCache:false |

| Source:PLAN_ CACHE |

| TemplateId: beaaba3a |

+------+

8 rows in set (0.32 sec)

As you can see, the SQL will correctly use the index to query, instead of full table scanning