数据库update 随机时间-mysql多库表关联问题症结

  0、题记

  项目中使用mysql作为数据存储，需要定期将库表中的数据按照给定格式生成报表。根据导出周期的不同分为：日报、周报、月报、季报、年报等格式。

  导出的整个逻辑我来负责编写，前期的日报、周报导出都没有问题。

  但是数据库update 随机时间，四月初统计上个月的月报的时候，发现到处全部的数据需要花费超过12个小时的时间。

  10几分钟的到处已然让人无法接受了，12个小时简直要了人命！

  本文基于导出时间长的问题，说下数据库表的设计和多表关联需要注意的事情！

  一、当前库表的情况

  1、导出给定格式的表数据分散到十几张表中；

  2、导出渠道也分为：搜索、微博、微信、论坛等近10种格式。

  3、核心业务数据分散在5张表中，其中4张表平均月数据量5万条+，另一张关联表的数据量10万条+。

  4、另五张表示配置相关的数据，如：渠道名称、主题名称、关键词名称等。

  二、导出格式的要求

  1、每个渠道有各自的导出模板；

  2、每个模板不同数据库update 随机时间，模板中的字段分散在近十张表中。

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

  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

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

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

  六、小结

  1、问题的根源还在于库表的设计，这点以后的开发中一定要注意，否则可能导致需要2-3天甚至更多的时间来排查一个本来设计阶段就可以避免的Bug。

  2、做好库表的设计，相当的工作积累必不可少。更重要的是扎实的基本功也不能落下。《高性能mysql》、《高可用mysql》有时间一定要好好研读（说给自己）。

  3、（）实践发现，如果对于多表关联建立了View视图，然后后续的操作对视图进行操作，可以实现的非常迅速的导入导出操作（这块知道的有点晚了）。

  七、引申思考的几个问题？1、多表关联如何根源避免？2、如何设计表能最小化配置、最大化性能？3、索引、主键、外键区别和联系？4、如何优化mysql性能？5、视图能解决多表关联问题，然后呢？

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

本文共 1146 个字数,平均阅读时长 ≈ 3分钟