mysql分表分库后的多子表查询
近几年我的经验看mysql数据库单表20个字段以上数据超过400w的时候查询效率就肉眼可见的下降了，尽管你把需要关联查询的字段都加上索引都无济于事，得考虑分表分库。然而分表又有垂直分表和水平分表之分，我这边一般都是按日期或者某个类型做水平分表，比如常见的按一个月的数据分一次表。
具体做法就就是代码里面事先创建好下个月的子表，数据来了以后根据当前日期写入对应的子表，这样以来表就分好了。需要丢掉历史数据的时候直接删除对应的子表就好不会产生碎片也不用担心批量删数据的时候要处理锁表影响性能。
然而这样做会也会产生一系列问题，比如需要跨表查询的情况和需要回溯历史数据覆盖原来的数据的情况。mysql原生并不支持直接把几个子表当成一个表来操作，只能用子查询或者union联合查询来把多个查询结果合并输出，这样又会带来分页和id重复的问题。虽然问题多多但是能用总比没有好，下面是以php laravel框架为例展示根据当前日期模型动态查不同的子表的代码：
思路很简单：在模型里面写一个公开方法通过动态更新$table变量来切换模型对应的表，然后根据查询日期来查询多个子表再unionAll合并到一个查询里面。
<code>
//模型部分，假如子表叫test_table_年月
<?php
.........//省略模型的其他基础代码
//构造方法，默认模型查的是当月的子表
public function __construct(){
$this->setMonthTable();
parent::__construct();
}
//根据传入月份更改当前模型绑定的子表
public function setMonthTable($month=null){
$month = $month ?? date('Ym');
$this->table = 'test_table_'.$month;
}
public static function getDataByDate($date_part, $where=[]){
$start = $date_part['start'];
$end = $date_part['end'];
$orm = static::where($where)
->where('date_at','>=', $start)
->where('date_at','<',$end);
$self = new self();
$month_end = date('Ym', strtotime($end)); //从哪个月结束
//这里限制的最多只查6个月
for($i=0; $i<6; $i++){
$m = date('Ym', strtotime($start."+$i month") );
//TODO 这里应该考虑到查询时间段内可能有的子表不存在，需要排除
$table = "test_table_".$m;
if($self->table==$table){ //跳过当前模型绑定的子表
continue;
}
if($m > $month_end){ //大于结束月份了就应该停止
break ;
}
$new_query = DB::table($table)
->where($where)
->where('date_at','>=', $start)
->where('date_at','<',$end)
->get();
$orm->unionAll($new_query);
}
return $orm;
}
</code>
事实上在数据比较多的时候用union联合查询多个子表的效率也很差，如果没有跨表查询的需求完全没必要这样做。我最后的做法是在用户不怎么使用的时间段比如凌晨三四点把耗费时间的查询从主表里先查出来放到一个临时表，等用户使用比较耗性能查询的时候直接从临时表里面查数据，子表查询虽然可以做但是也有局限性，根据实际需求灵活变通才行。
更多>>