数据设计2

数据设计

-数据存在冗余
-数据表过宽 会影响修改表结构的效率 不经常使用的列放到其他表

数据库设计范式

-数据库分区
-RANGE 按范围分区
适用场景 分区键为日期或者时间类型
-LIST分区的特点

为customer_login_log表分区

CREATE TABLE customer_login_log (
login_id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '登录日志ID',
customer_id int(10) unsigned NOT NULL COMMENT '登录用户ID',
login_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
login_ip int(10) unsigned NOT NULL COMMENT '登录IP',
login_type tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功',
PRIMARY KEY (login_id)
) ENGINE=InnoDB
PARITION BY RANGE ( YEAR(login_time) ) (
PARTITION p0 VALUES LESS THAN (2015),
PARTITION p1 VALUES LESS THAN (2016),
PARTITION p2 VALUES LESS THAN (2017),
);

常见业务需求

加索引

SELECT COUNT(DISTINCT audit_status)/COUNT() AS audit_rate
COUNT(DISTINCT product_id)/COUNT() AS product_rate
FROM product_comment

• 对评论进行分页展示
  select customer_id,title,content from product_comment where audit_status = 1 and product_id = 199726 limit 0,5
  索引IO加索引全部数据
  优化: 建立联合索引
  CREATE INDEX idx_productID_auditStatus ON product_comment(product_id,audit_status)

删除评论表中对同一订单的重复评论 只保留最早的一条

• 步骤一 查看是否存在同一订单同一商品的重复评论

• 步骤二 备份product_comment表

• 步骤三 删除同一订单的重复评论
  select order_id,product_id,COUNT() from product_comment group by order_id,product_id having COUNT() > 1;

  create table temp_product_comment20171117 select * from product_comment;

  delete a from product_comment a join (
  select order_id,product_id,MIN(comment_id) as comment_id
  group by order_id,product_id
  having COUNT(*) >= 2
  ) b on a.order_id=b.order_id and a.product_id=b.product_id and a.comment_id >b.comment_id