现在单表超过一千万记录,虽然索引完全使用,依旧会出现慢查询。日增平均40万。
数据库要重新设计,目的提高查询速度,只查询热数据。
设计思路
- 根据热数据时效性,保持在近三个月内,计划一年生成6张表。
- 利用union来查询,当前月在内的近三个月或四个月的数据。
- 牵扯查询尽量使用索引。
如何生成表?
比如2020年,生成20200102,20200304,20200506,20200708,20200910,20201112六张表
从一月到十二月,连续每两个月生成一张表
可提前把一年要用的数据表用mysql直接创建。
查询如何确定表?
依托当前月份可以直接取到表,设置一个数组来保存对应关系,这样感觉挺方便的
入库的时候直接用self:: i n t a l b e − − − − − − − 查 询 的 时 候 直 接 用 s e l f : : intalbe -------查询的时候直接用self:: intalbe−−−−−−−查询的时候直接用self::seltalbe
$rent_arrs = [
'2020'=>[
'1'=>['name'=>'20200102','table'=>['20200102']],
'2'=>['name'=>'20200102','table'=>['20200102']],
'3'=>['name'=>'20200304','table'=>['20200102','20200304']],
'4'=>['name'=>'20200304','table'=>['20200102','20200304']],
'5'=>['name'=>'20200506','table'=>['20200304','20200506']],
'6'=>['name'=>'20200506','table'=>['20200304','20200506']],
'7'=>['name'=>'20200708','table'=>['20200506','20200708']],
'8'=>['name'=>'20200708','table'=>['20200506','20200708']],
'9'=>['name'=>'20200910','table'=>['20200708','20200910']],
'10'=>['name'=>'20200910','table'=>['20200708','20200910']],
'11'=>['name'=>'20201112','table'=>['20200910','20201112']],
'12'=>['name'=>'20201112','table'=>['20200910','20201112']]
],
'2021'=>[
'1'=>['name'=>'20210102','table'=>['20210102','20201112']],
'2'=>['name'=>'20210102','table'=>['20210102','20201112']],
]
];
$y=date("Y");
$m=date("n");
self::$intalbe = $rent_arrs[$y][$m]['name'];
self::$seltalbe = $rent_arrs[$y][$m]['table'];
查询的时候用哪种方式比较高效呢?目前经过本地多次测试,explain查看,查询消耗时间对比,第二种速度较快,后期数据积累到一定程度,还不能确定
thinkphp5框架
第一种,利用union连接sql生成临时表查询(需要传查询条件,分页,索引)
$w['city_id'] = 220;
$page=1;
$a = Db::name(self::$seltalbe[0])->field('title,date')->where($w)->buildSql();
$c = Db::name(self::$seltalbe[1])->field('title,date')->where($w)->union([$a])->buildSql();
$list = Db::table($c . ' temp')
->order('date desc')
->limit($page-1,3)
->select();
var_dump($list);
原生SQL
SELECT * FROM ( SELECT `title`,`date` FROM `fcxlt_rent_20200304` WHERE `city_id` = 220 UNION ( SELECT `title`,`date` FROM `fcxlt_rent_20200102` WHERE `city_id` = 220 ) ) temp ORDER BY date desc LIMIT 0,3
第二种,直接union查询(需要传查询条件,分页,索引)
$s = Db::field('title,date')
->name(self::$seltalbe[0])
->union(function($query) use ($page,$w){
$query->field('title,date')->name(self::$seltalbe[1])->where($w)->order('date desc')->page($page,3);
})
->where($w)
->select();
var_dump($s);
原生SQL
SELECT `title`,`date` FROM `fcxlt_rent_20200102` WHERE `city_id` = 220 UNION SELECT `title`,`date` FROM `fcxlt_rent_20200304` WHERE `city_id` = 220 ORDER BY date desc LIMIT 0,3