电商千万用户 mysql设计_Mysql电商设计用户模块 一

项目结构

用户模块

商品模块

订单模块

仓配模块

常见问题

数据插入异常

数据更新异常

数据删除异常

数据冗余

数据库设计范式

第一范式

第二范式

第三范式

数据库设计的最低要求要满足第三范式的要求

3NF：一个表中的列和其它列之间及不包含部分函数依赖关系，也不包含传递函数依赖关系，那么这个表的设计就符合第三范式

拆分原用户表以符合第三范式

Mysql分区表

确认mysql服务器是否支持分区表: show plugins; 如果出现 partition 这一项说明时支持分区表操作的

特点为在逻辑上为一个表，在物理上存储在多个文件中

结合业务场景选择分区键，避免跨分区查询

对分区表进行查询时最好在 where从句中包含分区键

具有主键或唯一索引的表，主键或唯一的索引必须时分区键的一部分

hash分区的特点

根据mod(分区键，分区数)的值吧数据行存储到表的不同分区中

数据可以平均的分布在各个分区中

hash分区的键值必须是一个 int类型的值，或是通过函数课转换为int类型

示例：大体于传统的类似

create table customer_login_log(

...

) engine = innodb

partition by hash(customer_id) partition 4;

create table customer_login_log(

...

) engine = innodb

partition by hash(unix timestamp(login_time)) partition 4;

range分区特点

根据分区键值的范围把数据行存储到表的不同分区中

多个分区的范围要连续，但是不能重叠

默认情况下使用 VALUES LESS THAN属性，即每个分区不包括指定的那个值

使用场景：分区键为日期或是时间类型

建议所有的查询中都包括分区键

定期按分区范围清理历史数据

示例：

create tbale customer_login_log(

...

) engine = innodb

partitiion by range (customer_id)(

partition p0 values less than (1000),

partition p1 values less than (2000),

partition p2 values less than (3000),

partition p3 values less than maxvalue

);

list分区特点

按分区键取值的列表进行分区

通范围的分区一样，各分区的列表值不能重复

每一行数据必须找到对应的分区列表，否则数据插入失败

示例：

create tbale customer_login_log(

...

) engine = innodb

partitiion by list (login_type)(

partition p0 values in (1,3,5,7,9),

partition p1 values in (2,4,6,8),

);

用户模块

用户登录表：登录名，密码，用户状态

用户地址表： 省，市，区，邮编，地址

用户信息表：用户姓名，证件类型，证件号码手机号，邮箱，性别，积分，注册时间，生日，会员级别，用户余额

用户登录表

create table customer_login(

customer_id int unsigned AUTO_INCREMENT NOT NULL comment '用户ID',

login_name varchar(20) not null comment '用户登录名',

password char(32) not null comment 'md5加密的密码',

user_stats tinyint not null default 1 comment '用户状态',

modified_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

primary key pk_customerid(customer_id) ) engine = innodb comment ='用户登录表'

;

用户信息表

create table 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 '用户真实姓名',

identify_card_type tinyint nout null default 1 comment '证件类型：1身份证，2军官证，3护照',

identity_card_no varchar(20) comment '证件号码',

mobile_phone int unsigned commcent '手机号',

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 nout null default 1 comment '会员级别：1普通会员，2青铜会员，3白银会员',

user_money decimal(8,2) not null default 0.00 comment '用户余额',

modifyied_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

primary key pk_customerinfd (customer_inf_if)

) engine = innodb comment '用户信息表'

;

用户级别表

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 '该级别最高积分',

modifyied_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

primary key pk_levelid (customer_level)

)engine = innodb comment '用户级别信息表'

;

用户地址表

create table customer_addr(

customer_addr_id int unsigned AUTO_INCREMENT not null commcent '自增主键ID',

customer_id int unsigned not null comment 'customer_login表自增ID',

zip smallint not null comment '邮编',

province smallint nout 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 '是否默认',

modifyied_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

primary key pk_customeraddid (customer_addr_id)

)engine = innodb comment '用户地址表'

;

用户积分日志表

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_pointed (point_id)

)engine = innodb comment '用户积分日志表'

;

用户余额变动表

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 '用户余额变动表'

;

用户登录日志表

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 '用户登录日志表';

使用分区表进行操作 range分区，以login_time作为分区键,按年进行存储

CREATE TABLE customer_login_log(

customer_id INT UNSIGNED NOT NULL,

login_time DATETIME NOT NULL,

login_ip INT UNSIGNED NOT NULL,

login_type TINYINT NOT NULL

)

PARTITION BY RANGE (YEAR(login_time))(

PARTITION p0 VALUES LESS THAN(2015),

PARTITION p1 VALUES LESS THAN(2016),

PARTITION p2 VALUES LESS THAN(2017)) ;

插入数据

INSERT INTO customer_login_log(customer_id,login_time,login_ip,login_type) VALUES

(1001,'2014-01-04',0,1),(1001,'2015-01-05',0,1),(1001,'2016-01-04',0,1);

#添加分区

ALTER TABLE customer_login_log ADD PARTITION ( PARTITION p4 VALUES LESS THAN (2018));

#查看分区信息表，是否添加成功

SELECT

table_name,

partition_name,

partition_description,

table_rows

FROM information_schema.PARTITIONS

WHERE table_name='customer_login_log';

#删除分区

ALTER TABLE customer_login_log DROP PARTITION p0;

#建立归档表，把数据迁移到归档表中，注意迁移表的数据结构要与原结构一致，非分区表，非临时表

ALTER TABLE customer_login_log exchange PARTITION p1 WITH TABLE arch_customer_login_log;