DB: ページネーションのSQL高速化

「使われていないカラムのSelectをやめる」と「ページネーションでのOFFSET問題」を考えて、SubqueryでページのSelectを作りました。「ページネーションでのOFFSET問題」とは、OFFSETで指定する值が大きくなるにつれSQLの実行結果が遅くなるという罠です。なぜなら、MySQLはOFFSETが指定されている場合、そのOFFSETまでのレコードを全て読み込んでしまうからです。

SELECT `user_id`, `user_description`, `name_katagana`, `name_kanji`, `email`, `update_time`, `user_status`
FROM `users`
ORDER BY `update_time` DESC
LIMIT 6 OFFSET 1000

それでJOINのONで取得するレコード数がもっと少なくなるように絞り込む方法を使いました。

SELECT `record_id`,`records`.`update_time`, `description`, `user_pic`, `publisher_id`, `user_loginid`, `user_status`
FROM `users` INNER JOIN ( 
SELECT `user_id` 
FROM `users` 
ORDER BY  users.update_time DESC 
LIMIT 6 
OFFSET 1000 ) AS `a` 
ON (`users`.`user_id` = `a`.`user_id`) ;

また、ページネーションを作ることには「システム全体でどのぐらいのデータ数があるかカウントして表示する」というような機能が必要です。

MySQLでMyISAMを使っていれば Select count(*) from usersという高速なSQL文がありますが、InnoDBではデープル全体をスキャンする必要があるため、レコード数が多ければ多いほど遅くなります。少量のデータのSELECTでは、SELECT SQL_CALC_FOUND_ROWSというprefixを使って、SELECT FOUND_ROWS()でデータ数を取りますが（Select count(*) from usersより早いです！）、やはり多量のデータのSELECTは遅いです。

それで、新しいカウントテブールを作りました。結果として、高速でページを表示することができました。しかし、そこのデメリットはテブールのINSERT/DELETEは前より遅くなりました。

多量のリストは下記のように実行します。

sub search_with_pager {
....
 my ($self, $table_name, $where, $opt) = @_;

    my $table = $self->schema->get_table($table_name) or Carp::croak("'$table_name' is unknown table");

    my $page = $opt->{page};
    my $rows = $opt->{rows};

    for (qw/page rows/) {
        Carp::croak("missing mandatory parameter: $_") unless exists $opt->{$_};
    }
    my $columns = $opt->{'+columns'}
    ? [@{$table->{columns}}, @{$opt->{'+columns'}}]
    : ($opt->{columns} || $table->{columns})
    ;
    
    if ( $opt->{offset_key} ){

       my $id = $where->{'+offset_key'}
        ? [@{$table->{columns}}, @{$opt->{'+offset_key'}}]
        : ($opt->{offset_key} || $table->{columns})
        ;

        my ($sql, @binds) = $self->sql_builder->select(
            $table_name,
            $id,
            {},
            +{
                %$opt,
                limit => $rows + 1,
                offset => $rows*($page-1),
            }
        );

        my $new_table = '( '.$sql.' ) AS a ';

        my $id_name = ${@$id[0]}[0];
        my $joins = $opt->{joins};

        my @new_joins = [ $table_name => {table => \$new_table , condition => $table_name.".".$id_name." = a.".$id_name } ];
        push @$joins, @new_joins;

        ($sql, @binds) = $self->sql_builder->select(
            $table_name,
            $columns,
            {},
            +{
                joins => $joins, 
            }
        );

        my $sth = $self->dbh->prepare($sql) or Carp::croak $self->dbh->errstr;
        $sth->execute(@binds) or Carp::croak $self->dbh->errstr;
        
        my $table_count = $table_name."_count";
        my $total_entries = 0+$self->dbh->selectrow_array("SELECT cnt from $table_count ; ");

        my $ret = [ Teng::Iterator->new(
            teng             => $self,
            sth              => $sth,
            sql              => $sql,
            row_class        => $self->schema->get_row_class($table_name),
            table            => $table,
            table_name       => $table_name,
            suppress_object_creation => $self->suppress_row_objects,
        )->all];

        my $has_next = ( $rows + 1 == scalar(@$ret) ) ? 1 : 0;
        if ($has_next) { pop @$ret }

        my $current_page = $page;
        my $entries_per_page = $rows;
        my $pager = Data::Page->new($total_entries, $entries_per_page, $current_page, $has_next);

        return ($ret, $pager);
  ....
}