分表查询统计的一个具体案例
问题描述 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();感谢大神们~
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2016-08-18，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读