首頁 >php框架 >ThinkPHP >ThinkPHP5水平分錶後分頁查詢解決方案

ThinkPHP5水平分錶後分頁查詢解決方案

藏色散人
藏色散人轉載
2019-12-31 13:17:573151瀏覽

ThinkPHP5內建了partition方法,可用於實現簡單的分錶。新增,修改,刪除,查詢單一資料時,用partition方法都可以輕鬆搞定,因為這些操作有一個共同的特點,就是能事先明確的知道,我要操作的是哪一筆記錄。但有一個需求,ThinkPHP5似乎沒有解決,例如當一個大表,被拆分成若干個子表時,如何根據相關條件及排序獲取分頁資料。

這種需求情境下,由於事先並不知道哪些數據會出現在第一頁,哪些數據會出現在第二頁,這些根據檢索條件動態匹配的列表數據,該如何查詢呢?

一次失敗的嘗試

最先想到的也是最直接的一種方式,就是將partition方法和paginate方法結合起來,看似順理成章的事,結果悲劇了,資料庫被搞得直接逃跑。究其原因,要實現分頁查詢,partition方法中需要union若干個子表,而且每個union的子表中,都是select * 的形式,這樣就會嚴重影響到查詢的效率,況且,在獲取記錄總數的時候,也完全沒必要查詢出所有欄位。

成功之道

既然select * 會影響效率,那麼select 出主鍵會怎麼樣呢?當然是相當的快!整體想法就是分兩次取得數據,第一次先查詢出主鍵,然後第二次,根據主鍵,取得對應的數據。具體實現如下:

核心思想

水平分錶後,當需要分頁取得資料時,效率會變得非常低下,拆分的子表越多,對查詢效能的影響就會越大。所以核心想法就是,盡量透過主鍵id來取得對應的資料記錄,也就是分兩次來取得列表資料。

1. 先查詢總記錄數及主鍵id

該步驟中,union 子表的select語句中,只需要列出主鍵id和其它額外必須的字段即可,不相關的欄位無需出現。

2. 根據主鍵id查詢對應的完整資料。

函數封裝

1. 建構取得總記錄數及主鍵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(&#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;
}

呼叫方式:

假設buildPartitionSql函數的執行結果為$tables,那麼完整的SQL語句如下:

取得總記錄數的完整sql:

select count(1) as total from .$tables[&#39;countSql&#39;]

取得主鍵id的完整sql:

select * from .$tables[&#39;listSql&#39;]. limit 0,10

2. 建構取得指定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 = &#39;&#39;;
    $ids = is_array($idValues) ? implode(&#39;,&#39;,$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;
}

呼叫方式:

假設buildPartitionListSql函數的執行結果為$sql,那麼完整的SQL語句如下:

select * from .$sql

注意:業務層面的所有檢索條件,都放在了第一步的union子句中,第二步只需要根據id拿資料就行了。

php中文網,大量的免費thinkphp入門教學,歡迎線上學習!

以上是ThinkPHP5水平分錶後分頁查詢解決方案的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:csdn.net。如有侵權,請聯絡admin@php.cn刪除