mysql多库表关联问题症结

0、题记

项目中使用mysql作为数据存储,需要定期将库表中的数据按照给定格式生成报表。根据导出周期的不同分为:日报、周报、月报、季报、年报等格式。
导出的整个逻辑我来负责编写,前期的日报、周报导出都没有问题。
但是,四月初统计上个月的月报的时候,发现到处全部的数据需要花费超过12个小时的时间。
10几分钟的到处已然让人无法接受了,12个小时简直要了人命!
本文基于导出时间长的问题,说下数据库表的设计和多表关联需要注意的事情!

一、当前库表的情况

1、导出给定格式的表数据分散到十几张表中;
2、导出渠道也分为:搜索、微博、微信、论坛等近10种格式。
3、核心业务数据分散在5张表中,其中4张表平均月数据量5万条+,另一张关联表的数据量10万条+。
4、另五张表示配置相关的数据,如:渠道名称、主题名称、关键词名称等。

二、导出格式的要求

1、每个渠道有各自的导出模板;
2、每个模板不同,模板中的字段分散在近十张表中。

三、为什么需要那么多库表的关联?

1、导出的数据往往是关键的数据,小而精;而创建的库表往往是基础数据,大而全。
2、一类库表是基础配置信息;另一类库表是结果存储信息。结果信息表中的部分字段取自基础配置信息表。于是,便产生了表之间的关联。

四、导出时间长的根本原因是什么?

经过近2天排查,耗时的是微信导出渠道中的处理结果表中的处置标记1和处置标记2字段。这两个字段涉及到和另外两个表的关联。
处理结果表中已经入库了处置结果标记1和处置结果标记2的ID。
排查发现,如果去掉这三张表的关联,导出是分钟级的;而如果加上,导出则是小时级别的。
那两个关联表涉及的数据有多少呢?
根因1——处置标记1:涉及10X5W=50W数量级的关联;处置标记2:涉及50X5W=250W数量级的关联;
慢就慢在处置标记2上。
根因2——处理结果表没有进行分区;主键不合理、没有建立外键。

五、库表关联注意点是什么?

1、设计表的时候,想的更长远一些。

比如:关联表中如果存储配置表中的相关ID,导出需要名称的话就需要字段的关联;相反,如果直接存储名称的话,则少了一层遍历或关联操作。

2、设立主键、外键。

3、尽可能的设立表分区存储结构。

这个在创立表的时候就应该想到,因为,后续的修改可能会有错误提示。
以下是以月为分区修改的表结构:

ALTER TABLE store_rst
PARTITION BY RANGE COLUMNS (update_time)
(
  PARTITION p201612 VALUES LESS THAN ('2016-12-01'),
  PARTITION p201701 VALUES LESS THAN ('2017-01-01'),
  PARTITION p201702 VALUES LESS THAN ('2017-02-01'),
  PARTITION p201703 VALUES LESS THAN ('2017-03-01'),
  PARTITION p201704 VALUES LESS THAN ('2017-04-01'),
  PARTITION p201705 VALUES LESS THAN ('2017-05-01'),
  PARTITION p201706 VALUES LESS THAN ('2017-06-01'),
  PARTITION p201707 VALUES LESS THAN ('2017-07-01'),
  PARTITION p201708 VALUES LESS THAN ('2017-08-01'),
  PARTITION p201709 VALUES LESS THAN ('2017-09-01'),
  PARTITION p201710 VALUES LESS THAN ('2017-10-01'),
  PARTITION p201711 VALUES LESS THAN ('2017-11-01'),
  PARTITION p201712 VALUES LESS THAN ('2017-12-01'),
  PARTITION pMax VALUES LESS THAN (MAXVALUE));

4、多表关联一定是小表关联大表,能提高查询效率。

根本原因在于: 永远用小结果集驱动大记录集,因为在mysql中,只有Nested Join一种Join方式,就是说mysql的join是通过嵌套循环来实现的。通过小结果集驱动大记录集这个原则来减少嵌套循环的循环次数,以减少IO总量及CPU运算次数。(from腾讯)

六、小结

1、问题的根源还在于库表的设计,这点以后的开发中一定要注意,否则可能导致需要2-3天甚至更多的时间来排查一个本来设计阶段就可以避免的Bug。
2、做好库表的设计,相当的工作积累必不可少。更重要的是扎实的基本功也不能落下。《高性能mysql》、《高可用mysql》有时间一定要好好研读(说给自己)。
3、(20170429)实践发现,如果对于多表关联建立了View视图,然后后续的操作对视图进行操作,可以实现的非常迅速的导入导出操作(这块知道的有点晚了)。

七、引申思考的几个问题?

1、多表关联如何根源避免?

2、如何设计表能最小化配置、最大化性能?

3、索引、主键、外键区别和联系?

4、如何优化mysql性能?

5、视图能解决多表关联问题,然后呢?

20170510 待思考总结。

参考:
https://www.qcloud.com/community/article/164816001481011831?fromSource=gwzcw.82729.82729.82729

2017年4月6日 22:48 于家中床前

作者:铭毅天下
转载请标明出处,原文地址:
http://blog.csdn.net/laoyang360/article/details/69487415
如果感觉本文对您有帮助,请点击‘顶’支持一下,您的支持是我坚持写作最大的动力,谢谢!

©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页