Table Design in PolarDB-X - 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)
)

このテーブルに対して、一般的に以下のビジネス運用があります：

● 登録。user name、携帯電話番号、メールなどの一意性が特徴です：

INSERT INTO users VALUES (？, ？, ？)

● ログイン。現在、ほとんどのアプリは携帯電話番号、メールアドレス、ユーザー名など多次元のログインをサポートしているため、複数のタイプの SQL が存在します：

//ユーザー名 (user_name) でログイン：
SELECT *
FROM users
WHERE user_ name = ？;

//携帯電話番号 (mobile_phone) でログイン：
SELECT *
FROM users
WHERE mobile_ phone = ？;

//メールでログイン：
SELECT *
FROM users
WHERE email = ？;

● ログイン後、システム内では一般的にユーザー ID (user_id) を使用してユーザー情報の照会や更新を行います：

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

PolarDB-X ではこのようなテーブルをどのように設計すればよいでしょうか。

ここでは、データベースの MODE (PolarDB-X のデータベース MODE パラメーター：https://help.aliyun.com/document_detail/416411.html) に基づいて、2 つの例を示します：

DRDS モード

DRDS スキーマデータベースでは、テーブルパーティショニングキーを設計する必要があります。

users テーブルのクエリ条件である user_ id、user_ name、mobile_ phone、email のクエリ数はほぼ同等で、すべてオンラインクエリです。従来のデータベース/テーブルミドルウェアでは、1 つのテーブルに対してパーティションキーを 1 つしか選択できません。どのキーをパーティションキーとして選択しても、他の 3 つの条件によるクエリは壊滅的な影響を受けます。

PolarDB-X はグローバルインデックス (グローバルインデックスとは：https://zhuanlan.zhihu.com/p/395415647) をサポートしているため、この問題を容易に解決できます。以下の文でテーブルを作成できます：

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

これにより、user_ name、mobile_ phone、email にそれぞれグローバル一意インデックスが作成されます。前述のクエリ SQL はいずれも非常に効率的に実行されます。同時に、登録シナリオでの一意性も保証されます。

これらのインデックス作成文は、テーブル作成文に直接組み込むこともできます。構文リファレンス：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);

さらに、クエリパフォーマンスを向上させ、グローバルインデックスからテーブルへの戻り検索コストを回避したい場合は、グローバルインデックスをグローバルクラスター化インデックスとして作成することもできます。これにより多くのスペースを消費しますが、クエリパフォーマンスはより高くなります。例：

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

注：上記の使用方法は PolarDB-X 1.0 (バージョン>=5.4.12) にも適用されます。

AUTO モード

AUTO モードでは、パーティションキーなどの情報に注意を払う必要はなく、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)

);

手動パーティショニングと同じ効果を得ることができます。

EXPLAIN ステートメントを使用して実行計画を確認できます：

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)

| HitCache:false |

| Source:PLAN_ CACHE |

| TemplateId: beaaba3a |

+------+

8 rows in set (0.32 sec)

ご覧の通り、この SQL はインデックスを正しく使用してクエリを実行し、フルテーブルスキャンを行いません