高性能可扩展mysql 笔记（二）用户模型设计、用户实体表结构设计、设计范式

落日映苍穹つ 2023-06-04 13:54 50阅读 0赞

### 个人博客网：[https://wushaopei.github.io/][https_wushaopei.github.io]    (你想要这里多有) ###

### 一、用户模型设计 ###

电商羡慕中用户模型的设计涉及以下几个部分：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70][]

以电商平台京东的登录、注册页面作为例：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1][]

用户的基本模块涉及用户的账户名、密码、以及手机号码、所在地等信息。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2][]

由个人后台信息截图可知，用户的实体信息其实很多，不仅仅是当前页面所看到的，如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3][]

### 二、用户实体属性的CRUD操作的问题分析 ###

1、当我们确定了需要保存用户的属性后，就需要考虑怎么将属性存到表中了？同时还要考虑保存数据时的一些问题以及优劣性分析！

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 4][]

在将数据进行保存操作时会出现如下几个问题：

**1、数据插入异常**

>     PK **:** 用户登录名
> 
>     用户表 **：\{登录名 ... ... 会员级别，级别积分上限，级别积分下线\}**
> 
> **    insert into 用户表（会员级别）values(\`青铜\`);**

**2、数据更新异常**

>         要修改某一行的值时，不得不修改多行数据
> 
>       例：  用户等级： 青铜级 —> 注册会员
> 
>                update 用户表 set 等级= \`注册会员\` where 等级= \`青铜级\`

**3、数据删除异常**

>         删除某一数据时不得不同时删除另一数据
> 
>     例：    如何删除用户等级名为青铜的等级
> 
>                delete from 用户表 where 用户等级=\`青铜\`;

**4、数据存在冗余**

**5、数据表过宽，会影响修改表结构的效率**

### 三、用户实体属性的操作问题的解决方式 ###

遵守数据库的设计方式：

>  *        设计方式是数据库设计的规范
>  *        有多种设计范式，如数据库设计第一范式，第二范式和第三范式
>  *        数据库设计最低要满足第三范式的要求

1、第三范式（3NF）的定义

>   一个表中的列和其他列之间既不包括部分函数依赖关系，也不包括传递函数依赖关系，那么这个表的设计就符合第三范式。

2、根据第三范式（3NF）优化拆分用户表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 5][]

这里根据范式将第一张表中的会员级别、级别积分上限、级别积分下线进行拆分成一个小表，因为用户的个人信息修改的频率不高，而用户的级别可能随着时间、经验值等的提升而经常变动，单独拆分出来可以避免对冷数据的重复检索、节约IO资源。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 6][]

3、根据第三范式设计好的表如下：

用户登录表（customer\_login）

DROP TABLE IF EXISTS `customer_login`;
    CREATE TABLE `customer_login` (
      `customer_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
      `login_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户登录名',
      `password` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'md5加密的密码',
      `user_stats` tinyint(4) NOT NULL DEFAULT '1' COMMENT '用户状态',
      `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
      PRIMARY KEY (`customer_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='用户登录表';

用户信息表(customer\_inf)

create table customer.customer_inf(
    	customer_inf_id int unsigned auto_increment not NULL comment '自增主键ID',
      customer_id int unsigned not null comment 'customer_login表的自增ID',
      customer_name varchar(20) not null COMMENT '用户真实姓名',
      identity_card_type TINYINT not null default 1 COMMENT '证件类型：1身份证，2军官证，3护照',
      identity_card_no varchar(20) comment '证件号码',
      mobile_phone int unsigned comment '手机号',
      customer_email varchar(50) COMMENT '邮件',
      gender char(1) comment '性别',
      user_point int not null DEFAULT 0 COMMENT '用户积分',
      register_time TIMESTAMP not null COMMENT '注册事件',
      birthday datetime COMMENT '会员生日',
      customer_level tinyint not null DEFAULT 1 COMMENT '会员级别：1普通会员，2青铜会员，3白银会员，4黄金会员，5钻石会员',
      user_money decimal(8,2) not null default 0.00 COMMENT '用户余额',
      modified_time TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP 
      on update CURRENT_TIMESTAMP COMMENT '最后修改时间',
      primary key pk_customerinfid (customer_inf_id)
    ) engine=innodb comment '用户信息表'
    ;

用户级别表（customer\_level\_inf）

create table customer_level_inf(
    	customer_level TINYINT not null auto_increment COMMENT '会员级别ID',
      level_name varchar(10) not null comment '会员级别名称',
      min_point int unsigned not null default 0 comment '该级别最低积分', 
      max_point int unsigned not null DEFAULT 0 COMMENT '该级别最高积分',
      modified_time TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP  
      on update CURRENT_TIMESTAMP comment '最后修改时间',
      PRIMARY key pk_levelid (customer_level)
    ) engine=innodb comment '用户级别信息表'
    ;

用户地址表 (customer\_addr)

create table customer_addr (
     customer_addr_id int UNSIGNED auto_increment 
     not null comment '自增主键ID',
     customer_id int UNSIGNED not null COMMENT 'customer_login表的自增ID',
     zip smallint not null comment '邮编',
     province smallint not null comment '地区表中省份的id',
     city  smallint not null comment '地区表中城市的id',
     district smallint not null comment '地区表中的区id',
     address varchar(200) not null comment '具体的地址门牌号',
     is_default TINYINT not null comment '是否默认',
     modified_time TIMESTAMP not null default CURRENT_TIMESTAMP 
     on update CURRENT_TIMESTAMP comment '最后修改时间',
     PRIMARY key pk_customeraddid(customer_addr_id)
    ) engine=innodb comment '用户地址表'
    ;

用户积分日志表（customer\_point\_log）

create table customer_point_log(
     point_id int UNSIGNED not null auto_increment comment '积分日志ID',
     customer_id int UNSIGNED not null comment '用户ID',
     source tinyint unsigned not null comment '积分来源：0订单，1登录，2活动',
     refer_number int unsigned not null default 0 comment '积分来源相关编号',   
     change_point SMALLINT not null default 0 comment '变更积分数',
     create_time TIMESTAMP not null comment '积分日志生成时间', 
     PRIMARY key pk_pointid(point_id)
    ) engine=innodb comment '用户积分日志表'
    ;

用户余额变动表（customer\_balance\_log）

create table customer_balance_log(
     balance_id int UNSIGNED not null auto_increment comment '余额日志id',
     customer_id int UNSIGNED not null comment '用户ID',
     source TINYINT UNSIGNED not null DEFAULT 1 COMMENT '记录来源：1订单，2退货单',
     source_sn int UNSIGNED not null COMMENT '相关单据ID',
     create_time TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP COMMENT '记录生成时间',
     amount decimal(8,2) not null DEFAULT 0.00 comment '变动金额',
     PRIMARY key pk_balanceid(balance_id)
    ) ENGINE=INNODB COMMENT '用户余额变动表'
    ;

用户登录日志表（customer\_login\_log）

create table customer_login_log(
     login_id int UNSIGNED not null auto_increment comment '登录日志ID',
     customer_id int UNSIGNED not null COMMENT '登录用户ID',
     login_time TIMESTAMP not null COMMENT '用户登录时间', 
     login_ip int unsigned not null COMMENT '登录IP', 
     login_type TINYINT not null COMMENT '登录类型：0未成功 1成功',
     PRIMARY key pk_loginid (login_id)
    ) ENGINE=INNODB COMMENT '用户登录日志表'
    ;

[https_wushaopei.github.io]: https://wushaopei.github.io/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70]: /images/20230601/acc900c96b5140c7bfcd6225728ec166.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1]: /images/20230601/00ce5b518f8740e4a467b6bd35637e10.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2]: /images/20230601/1b9e4b5f7c0f42daae74e007a525a1f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3]: /images/20230601/308a532a528e46f8b606d01c098f9251.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 4]: /images/20230601/1b27cda5671b49509f3a59166dbd0526.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 5]: /images/20230601/f70f7f4b037b487286a4d695fa213890.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 6]: /images/20230601/1f0c01f6eb9e47acbf3ca3e7cbc12f1d.png