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

简介： 本系列旨在描述一个具体的业务场景，给出建表的例子，帮助大家更好的使用PolarDB-XBigone。本期的主题是：用户表。

本系列旨在描述一个具体的业务场景，给出建表的例子，帮助大家更好的使用PolarDB-XBigone。本期的主题是：用户表。

需求描述

大多业务都会有一张用户表Bigone，用来存用户的数据，例如这样一张用户表：

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)

对这张表Bigone，一般会有以下几种业务操作：

● 注册Bigone，特点是要保证用户名、手机号、邮箱等唯一：

INSERT INTO users VALUES (?, ?, ?)

● 登录Bigone，现在大多数APP都支持手机号、邮箱地址、用户名等多个维度进行登录，所以会有多种类型的SQL：

//按照用户名（user_name）进行登录：

SELECT *

FROM users

WHERE user_name = ?;

//按照手机号（mobile_phone）进行登录：

SELECT *

FROM users

WHERE mobile_phone = ?;

展开全文

//按照邮箱（email）进行登录：

SELECT *

FROM users

WHERE email = ?;

● 登录后Bigone，系统内一般会使用用户ID（user_id）查询或者更新用户信息：

SELECT *

FROM users

WHERE user_id = ?;

UPDATE users

SET xxxx = ?

WHERE user_id = ?;

对于这样的一张表Bigone，我们在PolarDB-X中该如何设计呢？

这里我们根据数据库的MODE（PolarDB-X中数据库的MODE参数：，给出两种示例：

DRDS模式

在DRDS模式的数据库中，我们需要设计表的分区键

Bigone。

users表的查询条件有user_id、user_name、mobile_phone、email，这四种条件的查询的量都差不多，都属于在线类查询

Bigone。对于传统的分库分表中间件来说，一个表的分区键只能选择一个，那么无论选择哪一个做分区键，对于其他三个条件的查询，都会是一场灾难。

PolarDB-X支持全局索引（什么是全局索引：

，那这个问题就很好解决了，我们按照下面语句建表即可： 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上分别创建了三个全局唯一索引

Bigone

。对于上述的查询SQL，每一种都会非常的高效。同时，也会保证注册场景下的唯一性。 当然

Bigone

，这些创建索引的语句也可以直接合并在建表语句中，相关语法参考：： 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);

此外，如果想进一步提升查询的性能，避免全局索引回表的代价，还可以把全局索引创建为全局聚簇索引

Bigone

。这样会消耗更多的空间，但查询性能会更高。例如： 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）也同样适用

Bigone

。 AUTO模式

对于AUTO模式

Bigone

，则无需关注分区键等信息，像在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)

同样的能达到和手动分区一样的效果

Bigone

。

Bigone

我们可以使用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会正确的使用索引来进行查询，而不会进行全表扫描

Bigone

。 测试环境

以上示例均在阿里云公共云PolarDB-X 2.0 5.4.13-16462728验证通过

Bigone

。 本文作者：梦实

本文来源：PolarDB-X 知乎号

原文链接：/

本文为阿里云原创内容，未经允许不得转载

Bigone

。