mysql多库关联_mysql多库表关联问题症结

╰+哭是因爲堅強的太久メ 2022-11-01 00:51 75阅读 0赞

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、视图能解决多表关联问题，然后呢？