SQL性能优化系列——反范式设计[三]

2019年8月6日22:21:36 发表评论 21
摘要

1.有了范式设计,为什么有时还需要反范式设计?
2.反范式设计的适用场景?存在哪些问题?

反范式设计

尽管数据表的设计有很多范式,但实际上在设计表的时候不一定要参照这些标准。因为在设计的过程中,有时为了性能和读取效率会违反范式化的原则,即通过增加少量冗余、通过空间换取时间的优化思路,对查询效率进行优化。

比如,查询某商品前1000评论,涉及两张表。

商品评论表:product_comment

字段 comment_id product_id comment_text comment_time user_id
含义 商品评论id 商品id 评论内容 评论时间 用户id
DROP TABLE IF EXISTS `product_comment`;
CREATE TABLE `product_comment`  (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `comment_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `comment_time` datetime(0) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`comment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10000 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

用户表:user

字段 user_id user_name create_time
含义 用户id 用户昵称 注册时间
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `create_time` datetime(0) NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

下面用这两种表进行模拟反范式优化。

模拟数据:两张百万量级数据表(通过存储过程实现)

用户表:随机生成100万用户

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_user`(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, interval RAND()*60 second);
INSERT INTO user(user_id, user_name, create_time)
VALUES((start+i), CONCAT('user_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END

date_start用来定义注册的初始时间;

date_temp变量计算每个用户的注册时间,新注册的和上一个注册的间隔60s;

REPEAT。。UNTIL 。。END REPEAT循环,对max_num个用户的数据进行计算;

autocommit设置为0,这样等计算完成再统一插入,效率更高。

然后运行call insert_many_user(10000,1000000);调用存储过程,通过start和max_num两个参数对初始的user_id和要创建的用户数量进行设置。

 

商品评论表:随机生成100万条商品评论

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_product_comments`(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE user_id INT;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, INTERVAL RAND()*60 SECOND);
SET comment_text = substr(MD5(RAND()),1, 20);
SET user_id = FLOOR(RAND()*1000000);
INSERT INTO product_comment(comment_id, product_id, comment_text, comment_time, user_id)
VALUES((START+i), 10001, comment_text, date_temp, user_id);
UNTIL i = max_num
END REPEAT;
COMMIT;
END

执行call insert_many_products_commonts(10000,1000000);调用存储过程。

 

反范式优化实验对比

如果查询10001这个商品的前1000条评论,sql:

SELECT p.comment_text, p.comment_time, u.user_name FROM product_comment AS p 
LEFT JOIN user AS u 
ON p.user_id = u.user_id 
WHERE p.product_id = 10001 
ORDER BY p.comment_id DESC LIMIT 1000

运行时长0.395秒,查询效率并不高。

关联查询product_comment和user,数据量超过百万量级,查询效率会降低。因为查询在两表上进行聚集索引扫描,然后嵌套循环,这样查询耗费的时间会有几百毫秒甚至更久。

如果想提升查询效率,可以考虑在商品评论表中添加用户昵称字段:user_name

这样形成了product_comment2:

DROP TABLE IF EXISTS `product_comment2`;
CREATE TABLE `product_comment2`  (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `comment_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `comment_time` datetime(0) NOT NULL,
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`comment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10000 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

这样查询只需要:

SELECT comment_text, comment_time, user_name FROM product_comment2 WHERE product_id = 10001 ORDER BY comment_id DESC LIMIT 1000

优化后只需要扫描一次聚集索引,运行时间是之前的十分之一,查询效率明显提升。(难怪个别网站不允许修改昵称)

反范式存在的问题与适用场景

当冗余信息有价值或能显著提升查询效率的时候,就可以采取反范式优化。

此外,反范式优化常用在数据仓库的设计中,因为数据仓库通常存储历史数据,实时性要求不高,对历史数据分析需求强。此时适当允许数据冗余度,更方便数据分析。

数据仓库和数据库的使用区别:

1.数据库设计目的在于捕获数据,数据仓库设计目的在于分析数据;

2.数据库对数据增删改实时性要求高,需要存储在线用户数据,数据仓库存储的一般是历史数据;

3.数据库设计尽量避免冗余,但为了提升查询效率允许少量冗余,数据仓库设计更偏向反范式设计。

了解

最近正在基于Hadoop建设某国企的数据集市项目(地域性非全网),恰如老师所言,我们就是在遵循反范式的设计。

简要来说,我们把数据加工链路分为四层,从下到上依次为:ODS贴源层、DWD明细层、DWS汇总层和ADS应用层。

多源异构的业务数据被源源不断ETL到ODS贴源层之后,经过清洗、规范、转换、拼接等,生成各类宽表存储在DWD明细层;再根据业务模型设计,以这些宽表为基础,生成各类标准的指标数据存储在DWS汇总层;ADS层则基于DWS层的汇总指标再度组合,计算得出应用层数据,直接面向业务需求。

在这样的系统设计中,反范式不仅体现在“宽表”的设计中,更体现在数据加工链路的完整生命周期中——上层都是对下层的冗余。

 

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: