수평 테이블 분할 후 ThinkPHP5 페이징 쿼리 솔루션-ThinkPHP-php.cn

수평 테이블 분할 후 ThinkPHP5 페이징 쿼리 솔루션

藏色散人

풀어 주다： 2019-12-31 13:17:57

앞으로

3167명이 탐색했습니다.

ThinkPHP5에는 간단한 테이블 파티션을 구현하는 데 사용할 수 있는 파티션 방법이 내장되어 있습니다. 단일 데이터를 추가, 수정, 삭제, 조회할 때 파티션 방식을 사용하면 쉽게 사용할 수 있습니다. 왜냐하면 이러한 작업에는 어떤 레코드를 작업하려는지 미리 명확하게 알 수 있다는 공통점이 있기 때문입니다. 그러나 예를 들어 큰 테이블이 여러 개의 하위 테이블로 분할될 때 관련 조건 및 정렬을 기반으로 페이징 데이터를 얻는 방법은 ThinkPHP5가 해결하지 못하는 것 같습니다.

이 수요 시나리오에서는 첫 번째 페이지에 어떤 데이터가 나타날지, 두 번째 페이지에 어떤 데이터가 나타날지 미리 알 수 없기 때문에 검색 조건에 따라 동적으로 매칭되는 목록 데이터를 어떻게 쿼리할 수 있을까요?

실패한 시도

가장 먼저 떠오르는 가장 직접적인 방법은 파티션 방식과 페이지 매김 방식을 결합하는 것입니다. 논리적인 것 같지만 결과는 비극적이며 데이터베이스가 직접 충돌합니다. . 그 이유는 페이징 쿼리를 구현하기 위해서는 분할 방식이 여러 하위 테이블을 유니온해야 하는데, 각 유니온의 하위 테이블이 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=&#39;&#39;,$num=1,$where=&#39;&#39;) {
    $countTable = [];
    $listTable = [];
    $fieldList = [$idKey];
    if ($fields) {
        $fieldList = array_merge($fieldList,explode(&#39;,&#39;,$fields));
        $fieldList = array_unique($fieldList);
    }
    $fieldStr = implode(&#39;,&#39;,$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 지정된 항목에 해당하는 레코드를 가져오는 sql 하위 쿼리 문을 구성합니다. id

/**
 * 构造获取指定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 문은 다음과 같습니다.