数据设计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 fromproduct_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