Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PolarDB-X Best Practice Series (1): How to Design a User Table

PolarDB-X Best Practice Series (1): How to Design a User Table

This article describes how to desgine a user table efficiently.

This series aims to describe a specific business scenario to help you learn how to use PolarDB-X more efficiently. Topic of this article: user table.

Requirement Description

Most businesses have a user table to store user data. The following shows an example:

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

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

  • Registration: it ensures that the user name, mobile phone, and email are unique:
INSERT INTO users VALUES (?, ?, ?)
  • Login: now most APP support mobile phone, email, and user name for login, so there are many types of SQL:
//log in by user name (user_name):
SELECT *
FROM users
WHERE user_name = ?;

//log in by mobile phone (mobile_phone):
SELECT *
FROM users
WHERE mobile_phone = ?;

//log in by email (email):
SELECT *
FROM users
WHERE email = ?;
  • After login, the system generally uses the user ID (user_id) to query or update user information:
SELECT *
FROM users
WHERE user_id = ?;

UPDATE users
SET xxxx = ?
WHERE user_id = ?;

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

Here are two examples based on the MODE (Databases in AUTO mode and DRDS mode) of the database:

DRDS Mode

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

The user table has the following query conditions: user_id, user_name, mobile_phone, and email. All of the four conditions are online queries. For traditional database and table sharding middleware, only one partition key can be selected for a table. No matter which one is selected as the partition key, it will be a disaster for the other three query conditions.

PolarDB-X supports global index. Therefore, we can build a table according to the following statements to solve the problem:

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

We create three globally unique indexes on user_name, mobile_phone, and email. For the preceding query SQL, each index will be efficient. It also ensures the uniqueness in the registration scenario.

These index creation statements can also be directly merged into the table creation statements. For more information about the syntax, see CREATE INDEX (DRDS mode)

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 a global index to the table, you can also create a global index as a globally clustered index. It consumes more space, but the query can have performance. For example:

CREATE GLOBAL CLUSTERED UNIQUE INDEX gsi_clustered_users_user_name ON users (user_name) DBPARTITION BY HASH(user_name);

Note: The preceding use also applies to PolarDB-X 1.0 (version later than 5.4.12).

AUTO Mode

For AUTO mode:

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),
  GLOBAL UNIQUE KEY uk_user_name(user_name) PARTITION BY HASH(user_name),
  GLOBAL UNIQUE KEY uk_mobile_phone(mobile_phone) PARTITION BY HASH(mobile_phone),
  GLOBAL UNIQUE KEY uk_email(email) PARTITION BY HASH(email)
)PARTITION BY HASH(user_id);

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)

As we can see, the SQL statement correctly uses indexes to query instead of scanning the full table.

Test Environment

The preceding examples are verified in Alibaba Cloud Public Cloud PolarDB-X 2.0 5.4.13-16462728.

SQL Database Best practices DRDS PolarDB-X PolarDB for Xscale Distributed database service
0 1 0
Share on

Read previous post:

New Features of PolarDB-X HTAP: Clustered Columnar Index (CCI)

Read next post:

PolarDB-X Best Practice Series (2): How to Implement Efficient Paged Queries

ApsaraDB

462 posts | 100 followers

You may also like

Comments

ApsaraDB

462 posts | 100 followers

Related Products

More Posts by ApsaraDB

See All