mysql 按日期水平分表之后的数据查询方式以及分页策略
问题描述
mysql数据库在数据量较大的情况下，对数据表进行水平分表，按照年份，如下：
目前的解决方案
在这种情况下的数据查询我暂时的解决方案是对每个数据库进行循环查询，然后返回每个数据表符合查询条件的数据，并且将查询到的数据合并到一个数组中，渲染到模板：
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();
感谢大神们~
一小撮火花
10 years, 4 months ago
Answers
有遇到过这种需求，想法：
1.有损服务，只给他查一年内的数据，或者只存1kw条数据。建一个表存一年内的数据，每隔一个月把表最旧的数据迁到分表上面。如果需求方要查所有数据，让他自己选年份去查。
2.在楼主的基础上改改，先由关键字算出所有年份的总数total，根据前端传来的页面数请求（即limit，start），确定需要查询的数据在哪一个年份，或者数据是多个年份组合出来。
假如
2012 25,2013 40,2014 15 ，共 80条
limit 0,20 ＝>落到2012年，那么只需查2012就够了;
limit 20,20 =>2012 后5条 +2013 15条 以此类推。。
如果再折腾一下，可以以关键字+年份为key，把非当前年份的条数存个cache，减少计算次数
3.最后是无脑union了，应该会很慢
4.如果是针对特定的关键字做报表统计，一次性的那就随意了
果断的选了1，因为老数据基本是没什么人关心的了。。
ZH.C.
answered 10 years, 4 months ago