手把手教你PolarDB-X中的表设计——用户表后端

简介：本系列旨在描述一个具体的业务场景，给出建表的例子，帮助大家更好的使用PolarDB-X。本期的主题是：用户表。
本系列旨在描述一个具体的业务场景，给出建表的例子，帮助大家更好的使用PolarDB-X。本期的主题是：用户表。
需求描述
大多业务都会有一张用户表，用来存用户的数据，例如这样一张用户表：
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)
)
对这张表，一般会有以下几种业务操作：
【手把手教你PolarDB-X中的表设计——用户表】● 注册，特点是要保证用户名、手机号、邮箱等唯一：
INSERT INTO users VALUES (?, ?, ?)
● 登录，现在大多数APP都支持手机号、邮箱地址、用户名等多个维度进行登录，所以会有多种类型的SQL：
//按照用户名（user_name）进行登录：
SELECT *
FROM users
WHERE user_name = ?;
//按照手机号（mobile_phone）进行登录：
SELECT *
FROM users
WHERE mobile_phone = ?;
//按照邮箱（email）进行登录：
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/docum...），给出两种示例：
DRDS模式
在DRDS模式的数据库中，我们需要设计表的分区键。
users表的查询条件有user_id、user_name、mobile_phone、email，这四种条件的查询的量都差不多，都属于在线类查询。对于传统的分库分表中间件来说，一个表的分区键只能选择一个，那么无论选择哪一个做分区键，对于其他三个条件的查询，都会是一场灾难。
PolarDB-X支持全局索引（什么是全局索引：https://zhuanlan.zhihu.com/p/...），那这个问题就很好解决了，我们按照下面语句建表即可：
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/docum...：
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（version >= 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)
可以看到，这个SQL会正确的使用索引来进行查询，而不会进行全表扫描。
测试环境
以上示例均在阿里云公共云PolarDB-X 2.0 5.4.13-16462728验证通过。
本文作者：梦实
本文来源：PolarDB-X 知乎号
原文链接：https://click.aliyun.com/m/10...
本文为阿里云原创内容，未经允许不得转载。