专栏首页后端技术探索分表查询统计的一个具体案例

分表查询统计的一个具体案例

问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

data_2013
data_2014
data_2015
…………

目前的解决方案 在这种情况下的数据查询我暂时的解决方案是对每个数据库进行循环查询,然后返回每个数据表符合查询条件的数据,并且将查询到的数据合并到一个数组中,渲染到模板:

 for($i = 0;$i<=$n;$i++)
            {
                //对日期时间进行处理
                if($i == 0) $map['d.collected_time'] = array('EGT',$start_date);
                if($i == $n) $map['d.collected_time'] = array('ELT',$end_date);

                $data = M('data_'.($start_year+$i));
//                dump($map);
                //对数据总量进行统计
                $count += $data
                    ->join('monitor_point p ON d.point_id = p.point_id')
                    ->join('hydro h ON h.hydro_id = p.hydro_id')
                    ->join('monitor_type t ON d.monitor_type_id = t.monitor_type_id')
                    ->join('agency a ON p.agency_id = a.id')
                    ->table('data_'.($start_year+$i).' d')
                    ->field('d.*,p.*,t.*,a.*,h.*')
                    ->where($map)
                    ->count();
                //不同年份的数据进行组合
                $page = new \Think\MyPage($count,30);
                $this->page = $page->show();
                $result_data[$i] = $data
                    ->join('monitor_point p ON d.point_id = p.point_id')
                    ->join('hydro h ON h.hydro_id = p.hydro_id')
                    ->join('monitor_type t ON d.monitor_type_id = t.monitor_type_id')
                    ->join('agency a ON p.agency_id = a.id')
                    ->table('data_'.($start_year+$i).' d')
                    ->field('d.*,p.*,t.*,a.*,h.*')
                    ->where($map)
                    ->limit($page->firstRow.','.$page->listRows)
                    ->select();
//                echo $data->_sql();
                //先检查是否是数组再进行添加或者合并操作。
//                $result_data[$i]是一个未知元素数量的一维数组,需要对其循环并添加到新的数组中去。
                 $result_data_num = count($result_data[$i]);
                for($j = 0;$j<$result_data_num;$j++)
                {
                    array_push($years_data,$result_data[$i][$j]);
                }
            }

使用的是thinkphp框架。但是上述的解决方案问题在于,返回每个数据表的前30条数据,如果要查询2013、2014两年的数据,他就会返回每个年份的前30条数据,总共60条。而且在显示的时候是先显示13年的,然后显示14年的,按照我们的查询方式 ,这个方式也确实没有问题,但是它是不符合我们的问题解决方法的。 新问题 通过谷歌搜索“mysql 水平分表之后 按年份 查询”找到一种解决办法:

SELECT d.*,p.*,t.*,a.*,h.* FROM data_2013 d 
INNER JOIN monitor_point p ON d.point_id = p.point_id 
INNER JOIN hydro h ON h.hydro_id = p.hydro_id 
INNER JOIN monitor_type t ON d.monitor_type_id = t.monitor_type_id 
INNER JOIN agency a ON p.agency_id = a.id 
 WHERE ( d.point_id IN ('16') ) AND ( d.collected_time >= '2013-01-01+00:00' )
UNION
SELECT d.*,p.*,t.*,a.*,h.* FROM data_2014 d 
INNER JOIN monitor_point p ON d.point_id = p.point_id 
INNER JOIN hydro h ON h.hydro_id = p.hydro_id 
INNER JOIN monitor_type t ON d.monitor_type_id = t.monitor_type_id 
INNER JOIN agency a ON p.agency_id = a.id 
WHERE ( d.point_id IN ('16') ) AND ( d.collected_time <= '2014-01-24+09:50' )
LIMIT 0,30 

但是这个解决方案在于他在分页方面是在最后限制的,也就是两条查询语句只能用一个限制语句,现在需要一个好的分页策略。

对于上述两种解决方案,其实都是需要解决分页的问题,大神们求助了~

目前正在尝试中的方案

select * from 
(SELECT d2013.* FROM data_2013 d2013
WHERE ( d2013.point_id IN ('16') ) AND ( d2013.collected_time <= '2014-01-24+09:50' ) 
UNION
SELECT d2014.* FROM data_2014 d2014 
WHERE ( d2014.point_id IN ('16') ) AND ( d2014.collected_time >= '2013-01-01+00:00' )
) d
INNER JOIN monitor_point p ON d.point_id = p.point_id 
INNER JOIN hydro h ON h.hydro_id = p.hydro_id 
INNER JOIN monitor_type t ON d.monitor_type_id = t.monitor_type_id 
INNER JOIN agency a ON p.agency_id = a.id 
ORDER BY d.collected_time 
LIMIT 0,30 

最终的解决方案 在for循环中,对需要查询的年份构建子查询,然后将每次查询的sql语句组合成为一个数组(array_push),最后用implode(' union ',$union_sql)用union组合成为总的sql语句,然后,照着上面给出的sql语句,将总的子查询语句添加进去,再加入排序、分页等~很美妙~虽然今早6.30就被38°的太阳刺眼到睡不着,早早过来做,用了一上午做好的…… 最后的分页控制:

$years_data = $union_model->table('('.$subQuery.') d')
                ->field('d.*)
                ->limit($page->firstRow.','.$page->listRows)
                ->order('d.collected_time')
                ->select();

感谢大神们~

本文分享自微信公众号 - nginx(nginx-study)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-03-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 分表查询统计的一个具体案例

    问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

    后端技术探索
  • nginx+memcached构建页面缓存应用

    nginx的memcached_module模块可以直接从memcached服务器中读取内容后输出,后续的请求不再经过应用程序处理,如php-fpm、djang...

    后端技术探索
  • 业务id转密文短链的一种实现思路

    买家通过电商app下单后,会受到一条短信,短信内容中包括改订单详情页面的h5地址连接,因为是出现在短信中,所以对连接有要求:1.尽量短;2.安全性考虑,订单在数...

    后端技术探索
  • 分表查询统计的一个具体案例

    问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

    后端技术探索
  • java进阶|MyBatis系列文章(七)多表查询操作

    以上jar包依赖主要是mysql连接,支持mybatis操作以及简化get/set方法的lombok包。

    后端Coder
  • R语言 | 根据数据框的顺序进行筛选

    这里有两个数据框,两者有相同的列(ID),这里想把第一个数据框,按照第二个数据框的ID列进行提取,顺序和第二个数据框一致。

    邓飞
  • java进阶|Mybatis系列文章(三)表关联查询操作

    整个项目所需要的xml文件进行数据的增删改查操作ClassesMapper.xml文件

    后端Coder
  • 无限级子商户的查询优化方法

    A 有 2 个直接下级B、C,    B有2个直接下级D、E,    C有2个直接下级F、G

    宣言言言
  • flask_web网站收官

    首先恭喜我们的网站圆满完成,这么久终于大结局了,先小小的开心一下(放上网站的网址供大家查看:http://39.96.164.111,因为测试短信验证码的时候用...

    小闫同学啊
  • MySQL之MVCC初探(1)

    昨天的文章中,我们说了MVCC的基本概念,然后讲了记录额外的两个字段,今天我们通过例子来说明一下MVCC在实际应用中的表现。我们首先创建一张表,然后插入一条...

    AsiaYe

扫码关注云+社区

领取腾讯云代金券