ThinkPHP5水平分表后分页查询解决方案
ThinkPHP5内置了partition方法,可用于实现简单的分表。新增,修改,删除,查询单条数据时,用partition方法都可以轻松搞定,因为这些操作有一个共同的特点,就是能事先明确的知道,我要操作的是哪一条记录。但有一个需求,ThinkPHP5似乎没有解决,比如当一个大表,被拆分成若干个子表时,如何根据相关条件及排序获取分页数据。这种需求场景下,由于事先并不知道哪些数据会出现在第一页,哪些数据会出现在第二页,这些根据检索条件动态匹配的列表数据,该如何查询呢?
一次失败的尝试
最先想到的也是最直接的一种方式,就是将partition方法和paginate方法结合起来,看似顺理成章的事,结果悲剧了,数据库被搞得直接奔溃。究其原因,要想实现分页查询,partition方法中需要union若干个子表,而且每个union的子表中,都是select * 的形式,这样就会严重影响到查询的效率,况且,在获取记录总数的时候,也完全没必要查询出所有字段。
成功之道
既然select * 会影响效率,那么select 出主键会怎样呢?当然是相当的快!总体思路就是分两次获取数据,第一次先查询出主键,然后第二次,根据主键,获取对应的数据。具体实现如下:
核心思想
水平分表后,当需要分页获取数据时,效率会变得非常低下,拆分的子表越多,对查询性能的影响就会越大。所以核心思想就是,尽量通过主键id来获取对应的数据记录,也就是分两次来获取列表数据。
-
先查询总记录数及主键id该步骤中,union 子表的select语句中,只需要列出主键id和其它额外必须的字段即可,不相关的字段无需出现。
-
根据主键id查询对应的完整数据。
函数封装
- 构造获取总记录数及主键ID的sql子查询语句
/**
* 构造获取总记录数及主键ID的sql子查询语句
* @param $table 主表名称
* @param $idKey 主键id字段名称
* @param string $fields 其它字段名称,多个字段用英文逗号分隔
* @param int $num 子表数量
* @param string $where 查询条件
* @return array
*/
function buildPartitionSql($table,$idKey,$fields='',$num=1,$where='') {
$countTable = [];
$listTable = [];
$fieldList = [$idKey];
if ($fields) {
$fieldList = array_merge($fieldList,explode(',',$fields));
$fieldList = array_unique($fieldList);
}
$fieldStr = implode(',',$fieldList);
for ($i = 0; $i < $num; $i++) {
$countTable[] = sprintf('SELECT %s FROM %s_%s where 1=1 %s', $idKey, $table, ($i + 1), $where);
$listTable[] = sprintf('SELECT %s FROM %s_%s where 1=1 %s', $fieldStr,$table, ($i + 1), $where);
}
$countTable = '( ' . implode(" UNION ", $countTable) . ') AS ' . $table;
$listTable = '( ' . implode(" UNION ", $listTable) . ') AS ' . $table;
$tables = ['countSql' => $countTable, 'listSql' => $listTable];
return $tables;
}
调用方式:假设buildPartitionSql函数的执行结果为$tables,那么完整的SQL语句如下:获取总记录数的完整sql:
select count(1) as total from .$tables['countSql']
获取主键id的完整sql:select * from .$tables[‘listSql’]. limit 0,10
- 构造获取指定id对应记录的sql子查询语句
/**
* 构造获取指定id对应记录的sql子查询语句
* @param $table 主表名称
* @param $idKey 指定的id字段名称
* @param $idValues 指定的id字段值
* @param int $num 子表数量
* @return string
*/
function buildPartitionListSql($table,$idKey,$idValues,$num=1) {
$sql = '';
$ids = is_array($idValues) ? implode(',',$idValues) : $idValues;
if ($ids) {
$listTable = [];
for ($i = 0; $i < $num; $i++) {
$listTable[] = sprintf('SELECT * FROM %s_%s where %s in (%s)', $table, ($i + 1), $idKey, $ids);
}
$sql = '( ' . implode(" UNION ", $listTable) . ') AS ' . $table;
}
return $sql;
}
调用方式:
假设buildPartitionListSql函数的执行结果为$sql,那么完整的SQL语句如下:
select * from .$sql
注意:业务层面的所有检索条件,都放在了第一步的union子句中,第二步只需要根据id拿数据就行了。
历史文章推荐:
[Linux技术]lsyncd——多机器实时同步文件神器
[软件推荐]windows上快速查找文件的软件