天天看点

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));

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

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