mysql大表的解决方案，及Hive分页查询

先说一下遇到的问题，然后给出自己的解决方案，虽然不一定是最佳的解决方法，但是目前确实解决了公司的大表数据问题，如果其他小伙伴有更好的解决方法，也请多多交流，给出自己的解决方法。

目前所有的数据和工具组件都是基于腾讯云搭建和管理，先说下我们遇到的情况：

1.mysql表查询数据量较大（最大的一张表已经达到33亿条数据，整个mysql已经达到2.9T的数据存储），

2.mysql表每日新增数据量较大（增量最大的表，每日新增数据量在4千万条左右的数据）

3.需要支持事务操作，部分表需要支持更新操作。

4.支持复杂操作，包括模糊，排序，分组统计和分页等操作。

5.低延迟，用户选择的任务条件查询，响应时间应该控制在3-5s。

6.实时数据的写入和查询操作,目前实时数据每10分钟处理一次，大概400G左右的数据。

从上面很容易看出来我们遇到的，

1.单表数据量较大，已经无法支撑前台部分业务的正常操作；

2.部分统计程序写入已经达到上限（MySQL每天写入8万条的数据），造成其他任务无法正常执行，经常在凌晨脚本启动后，不能按时处理完数据，造成其他任务的挤压（注：所有离线和实时均采用spark处理，yarn资源调度采用的Fair策略，目前腾讯云不支持Capacity策略）。

对于我们的mysql表，我们存储的大部分都是统计数据，因为早期数据量较少，统计能力有限，以及考虑到和前台交互较频繁，所以一直存储在关系型数据库中，才造成目前遇到这种的窘境，已经到不了不改变的的地步，因为数据的增量越来越大。

我们的解决步骤和方法：

1.首先，我们第一个考虑的时候结合业务，前面用户的操作

2.对表中数据进行了检查和审查之后，发现大数据中经常遇到的2/8定律，20%的用户占了80%的数据。我们考虑分库和分表操作，将部分数据量较大的用户迁入到其他服务器的数据库中，但是增大的前端可视化部门的工作压力，同时统计部门的统计程序需要全部调整，用来区分不同的用户。

3.使用大数据技术解决，将离线数据，全部迁移到Hive中，按天分区管理，使用presto做复杂查询，实时数据保留，继续写入mysql，对于有更新操作的表，直接写入到Hbase中，使用phoenix处理。

在迁移数据的过程中，我们刚开始使用的是sqoop导取mysql数据到Hive，动态分区，后来发现这样有一些弊端，因为无法解决spark写入Mysql的性能问题，我们对spark进行了各种优化，将数据的处理时间控制在了5分钟左右，但是却经常在写mysql的时候需要将近1个钟头的时间，于是直接将离线统计全部直接直接写入HDFS，现在基本上分析和写入时间在10分钟左右。同时也解决了数据挤压的问题。

最后问题解决了，目前我们做了一个中间微服务，使用springboot做查询，分为两部分处理，

a.用户对历史数据（昨天，最近1周或最近1个月，以及指定任意时间段区间，除今天之外）的操作，我们均采用presto操作;

b.用户查询今天的实时操作，我们直接读取mysql中的数据，目前单表数据量最大在千万级，所以msyql完全能支撑的住，而且性能还不错。对于用户指定较长时间段的查询，包括实时今天的数据操作时，我们采取，presto+mysql同时分析处理，然后通过程序对数据进行最后统一的合并，分页，排序，这个目前还在优化，以达到准确性在可承受范围内。

以上是我们的解决方法，PHP人员直接调用我们的接口，不需要关心我们底层使用的什么技术（当然这个解决方法只是基于我们目前的业务场景的解决方法，不一定是最优的解决方案，但是目前给我们统计大量的数据涌进来时，提供了一定的缓冲时间），同时将可视化和底层数据剥离开，如果目前的方法达到瓶颈，再次无法满足性能的要求，后面考虑使用其他技术处理，最主要的是这次调整降低了公司的经济支出，不用被动的去升级资源配置。

最后，给出了一个Hive中分页的查询操作，主要使用到row_number和between，and。

select c.* 
from( 
select row_number() over (order by b.ev_paras_value ASC ) as sort_field, b.* 
from (
select a.app_key, a.event_key, a.ev_paras_value, a.paras_uv, a.paras_count 
FROM  (
select app_key, event_key, ev_paras_value,
sum(ev_paras_uv) as paras_uv, 
sum(ev_paras_count) as paras_count 
FROM aldstat_event_paras_partition 
where 1=1  
AND day_time in ('2018-08-01') 
AND app_key='xxxxxxxxxx' 
AND event_key='bbbbbbbb' 
AND ev_paras_name like '%name%' 
GROUP BY app_key, event_key, ev_paras_value) a 
order by a.ev_paras_value ASC ) b ) c 
where c.sort_field between 1 and 30