开发者社区> 开源小秘书> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

手把手教你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)
)

对这张表,一般会有以下几种业务操作:

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

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/document_detail/416411.html),给出两种示例:

DRDS模式

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

users表的查询条件有user_id、user_name、mobile_phone、email,这四种条件的查询的量都差不多,都属于在线类查询。对于传统的分库分表中间件来说,一个表的分区键只能选择一个,那么无论选择哪一个做分区键,对于其他三个条件的查询,都会是一场灾难。

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(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 知乎号

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PolarDB-X的特点
PolarDB-X的特点
1831 0
对PolarDB-X集群做动态扩缩容2
对PolarDB-X集群做动态扩缩容2
1320 0
快速入门—PolarDB-X首次使用流程—连接到数据库
本文将介绍如何通过数据管理DMS连接到PolarDB-X。PolarDB-X还支持通过SQL命令行、第三方客户端(MySQL WorkBench、SQLyog、Sequel Pro、Navicat for MySQL)以及符合MySQL官方交互协议的第三方程序代码进行连接。
1769 0
一篇文章带你了解CSS clear both清除浮动
一篇文章带你了解CSS clear both清除浮动
2851 0
PolarDB-X 1.0-用户指南-数据表管理-关闭全表扫描
PolarDB-X默认为新创建的表开启全表扫描功能,但频繁使用全表扫描功能会占用数据库资源降低查询效率,本文将介绍如何在控制台上手动关闭全表扫描。
2500 0
PolarDB-X 1.0-用户指南-数据恢复表回收站
背景介绍 PolarDB-X表回收站提供针对误删表操作的数据恢复能力。 开启PolarDB-X表回收站功能后,通过DROP TABLE指令删除的表将被移动至表回收站中不再可见,数据表移动至回收站2小时后,即被自动清理,无法恢复。您可以在表回收站中查看、恢复、清理已删除的表。
3836 0
快速掌握 PolarDB-X 拆分规则变更能力!
本文简单阐述了PolarDB-X实现拆分规则变更过程中使用到的各项技术点。
1711 0
一篇文章带你了解CSS clear both清除浮动
一篇文章带你了解CSS clear both清除浮动
2790 0
+关注
开源小秘书
开源数据库运营小编~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
《1-PolarDB-X 2.0使用一个透明的分布式数据库是一种什么体验》PDF
立即下载
PolarDB-X分布式数据库解决方案
立即下载
PolarDB-X 2.0 全局 Binlog 与备份恢复能
立即下载