ThinkPHP5 paging query solution after horizontal table splitting

ThinkPHP5 has a built-in partition method, which can be used to implement simple table partitions. When adding, modifying, deleting, or querying a single piece of data, you can easily use the partition method, because these operations have a common feature, that is, you can clearly know in advance which record you want to operate. But there is a need that ThinkPHP5 does not seem to solve. For example, when a large table is split into several sub-tables, how to obtain paging data based on relevant conditions and sorting.

In this demand scenario, since we don’t know in advance which data will appear on the first page and which data will appear on the second page, how to query the list data that is dynamically matched according to the search conditions?

A failed attempt

The first and most direct way that comes to mind is to combine the partition method and the paginate method. It seems logical. The result was tragic, and the database crashed directly. The reason is that in order to implement paging query, the partition method needs to union several sub-tables, and the sub-tables of each union are in the form of select *, which will seriously affect the efficiency of the query. Moreover, when obtaining records When calculating the total number, there is absolutely no need to query all fields.

The Way to Success

Since select * will affect efficiency, what will happen if the primary key is selected? Of course it is quite fast! The general idea is to obtain data in two steps. The first time is to query the primary key, and then the second time is to obtain the corresponding data based on the primary key. The specific implementation is as follows:

Core Idea

After horizontal splitting the table, when data needs to be obtained in pages, the efficiency will become very low, the more sub-tables are split , the impact on query performance will be greater. So the core idea is to try to obtain the corresponding data record through the primary key id, that is, to obtain the list data in two times.

1. First query the total number of records and primary key id

In this step, in the select statement of the union subtable, you only need to list the primary key id and other additional necessary fields. No The relevant fields need not be present.

2. Query the corresponding complete data based on the primary key id.

Function encapsulation

1. Construct a sql subquery statement to obtain the total number of records and primary key ID

/**
 * 构造获取总记录数及主键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(&#39;SELECT %s FROM %s_%s where 1=1 %s&#39;, $idKey, $table, ($i + 1), $where);
        $listTable[] = sprintf(&#39;SELECT %s FROM %s_%s where 1=1 %s&#39;, $fieldStr,$table, ($i + 1), $where);
    }
    $countTable = &#39;( &#39; . implode(" UNION ", $countTable) . &#39;) AS &#39; . $table;
    $listTable = &#39;( &#39; . implode(" UNION ", $listTable) . &#39;) AS &#39; . $table;
    $tables = [&#39;countSql&#39; => $countTable, &#39;listSql&#39; => $listTable];
    return $tables;
}

Calling method:

Assume that the execution result of the buildPartitionSql function is $tables, then the complete SQL statement is as follows:

Get the complete sql of the total number of records:

select count(1) as total from .$tables['countSql']

Get the complete sql of the primary key id:

select * from .$tables['listSql']. limit 0,10

2. Construct a sql subquery statement to obtain the record corresponding to the specified id

/**
 * 构造获取指定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(&#39;SELECT * FROM %s_%s where %s in (%s)&#39;, $table, ($i + 1), $idKey, $ids);
        }
        $sql = &#39;( &#39; . implode(" UNION ", $listTable) . &#39;) AS &#39; . $table;
    }
    return $sql;
}

Calling method:

Assume that the execution result of the buildPartitionListSql function is $sql, then the complete SQL statement is as follows:

select * from .$sql

Note: All search conditions at the business level are placed in the union clause of the first step. In the second step, you only need to get the data based on the ID.

php Chinese website, a large number of free thinkphp introductory tutorials, welcome to learn online!

The above is the detailed content of ThinkPHP5 paging query solution after horizontal table splitting. For more information, please follow other related articles on the PHP Chinese website!