Edited at

ページネーション(ページ切り替え)の実装方法いろいろ

More than 1 year has passed since last update.


やりたいこと

データが大量に存在するとき、1ページにn項目だけ表示したい的な仕組みの実装方法、自分用まとめ。

たとえばこんなやつ。

キャプチャ.PNG

上記例のようにページ数を指定して飛べるものや、

単に「前へ」「次へ」のナビゲーションを行うものまで多種多様。

サンプルコードについては動作確認していない。

あくまでヒントとして。


目標環境、使用ライブラリ等


  • PHP

  • MySQL系RDB


サンプルコード


COUNT/LIMIT法、ページ数対応

一番オーソドックスと思われる方法。


count/limit

<?php

$_get_page = $_GET["page"];
$_get_query = $_GET["q"];

$csql = "SELECT COUNT(*) as 'cnt' FROM table WHERE data=:q"; // 総件数カウント用SQL
$ssql = "SELECT * FROM table WHERE data=:q LIMIT :start, 10 ORDER BY `id`"; // データ抽出用SQL
$dbh = new PDO("mysql:localhost", "user", "pass"); // DB接続

// データ抽出用SQLを、プリペアドステートメントで実行
$ssth = $dbh->prepare($ssql);
$ssth->bindValue(":q", $_get_query);
$ssth->bindValue(":start", $_get_page * 10);
$ssth->execute();
$data = $ssth->fetchAll(PDO::FETCH_ASSOC);

// 総件数カウント用SQLを、プリペアドステートメントで実行
$csth = $dbh->prepare($csql);
$csth->bindValue(":q", $_get_query);
$csth->execute();
$total = $csth->fetchColumn(PDO::FETCH_ASSOC);

$pages = ceil($total / 10); // 総件数÷1ページに表示する件数 を切り上げたものが総ページ数
?>
<html>
<body>
<ul>
<?php
foreach($data as $row) {
printf("<li>%s</li>\n", $row["column"]);
}
?>
</ul>

<?php
for($i=0; $i < $pages; $i++) {
printf("<a href='?page=%d&q=%s'>%dページへ</a><br />\n", $i, $_get_query, $i);
}
?>

</body>
</html>


良い点


  • ググると同様の例が大量に出てくる。採用事例多数。

悪い点


  • データ量に従って、主に COUNT() のパフォーマンスが低下する。

    小規模(~10,000件)向け。


  • ページが深くなるにつれ、LIMIT のパフォーマンスも低下する。

    LIMIT(X, Y) は、「(X+Y)件取得して、X件捨てる」挙動をするため。


※ ただし、MyISAM型テーブルを「絞り込み( WHERE)無しで」使用する場合は COUNT() のパフォーマンス低下について無視できる。

 MyISAM型テーブルは、行数を内部的に持っているので COUNT() の結果が即返ってくる。


SQL_CALC_FOUND_ROWS/LIMIT法、ページ数対応

MySQL系限定だが、単一クエリで済む方法。

SQL_CALC_FOUND_ROWS を指定することによって、

LIMIT を無視したレコード件数を取得することができる。


sql_calc_found_rows/limit

<?php

$_get_page = $_GET["page"];
$_get_query = $_GET["q"];

$ssql = "SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE data=:q LIMIT :start, 10 ORDER BY `id`"; // データ抽出用SQL
$dbh = new PDO("mysql:localhost", "user", "pass"); // DB接続

// データ抽出用SQLを、プリペアドステートメントで実行
$ssth = $dbh->prepare($ssql);
$ssth->bindValue(":q", $_get_query);
$ssth->bindValue(":start", $_get_page * 10);
$ssth->execute();
$data = $ssth->fetchAll(PDO::FETCH_ASSOC);

// 総件数カウント用SQLを、プリペアドステートメントで実行
$csth = $dbh->query("SELECT FOUND_ROWS()");
$total = $csth->fetchColumn(PDO::FETCH_ASSOC);

$pages = ceil($total / 10); // 総件数÷1ページに表示する件数 を切り上げたものが総ページ数
?>
<html>
<body>
<ul>
<?php
foreach($data as $row) {
printf("<li>%s</li>\n", $row["column"]);
}
?>
</ul>

<?php
for($i=0; $i < $pages; $i++) {
printf("<a href='?page=%d&q=%s'>%dページへ</a><br />\n", $i, $_get_query, $i);
}
?>

</body>
</html>


良い点


  • SQL発行が1回で済む。

悪い点


  • SQL_CALC_FOUND_ROWSCOUNT() よりも遅いため、データ量によってはパフォーマンスがかなり低下する。

    ごく小規模(~1,000件)程度の使用に留める。


  • MySQL系独自の実装のため、汎用性の面で劣る。



COUNT/BETWEEN法、ページ数対応

COUNT/LIMIT法を改良したもの。

キーが連番かつ歯抜けになっていないことが条件 だが

LIMIT のパフォーマンス低下を回避できる。


count/between

<?php

$_get_page = $_GET["page"];
$_get_query = $_GET["q"];

$csql = "SELECT COUNT(*) as 'cnt' FROM table WHERE data=:q"; // 総件数カウント用SQL
$ssql = "SELECT * FROM table WHERE data=:q AND `id` BETWEEN :start AND :end ORDER BY `id`"; // データ抽出用SQL
$dbh = new PDO("mysql:localhost", "user", "pass"); // DB接続

// データ抽出用SQLを、プリペアドステートメントで実行
$ssth = $dbh->prepare($ssql);
$ssth->bindValue(":q", $_get_query);
$ssth->bindValue(":start", $_get_page * 10);
$ssth->bindValue(":end", $_get_page * 10 + 10);
$ssth->execute();
$data = $ssth->fetchAll(PDO::FETCH_ASSOC);

// 総件数カウント用SQLを、プリペアドステートメントで実行
$csth = $dbh->prepare($csql);
$csth->bindValue(":q", $_get_query);
$csth->execute();
$total = $csth->fetchColumn(PDO::FETCH_ASSOC);

$pages = ceil($total / 10); // 総件数÷1ページに表示する件数 を切り上げたものが総ページ数
?>
<html>
<body>
<ul>
<?php
foreach($data as $row) {
printf("<li>%s</li>\n", $row["column"]);
}
?>
</ul>

<?php
for($i=0; $i < $pages; $i++) {
printf("<a href='?page=%d&q=%s'>%dページへ</a><br />\n", $i, $_get_query, $i);
}
?>

</body>
</html>


良い点


  • キーを使用するので、ページが深くなってもパフォーマンスの低下はない。

悪い点


  • データ量に従って COUNT() のパフォーマンスが低下する。
    小規模(~10,000件)向け。

  • キーが連番でなく歯抜けになっていると、結果の件数がバラバラになる。

MyISAM型テーブルで、絞り込み( WHERE )なしの場合は、この手法が最速かも。


起点/LIMIT法、次ページのみ

ページで切り替えなんていらない!

次のページへ進むのみ! 猪突猛進!

もう一工夫すれば、前ページボタンも付けられそう。。。


from/limit

<?php

$_get_from = $_GET["from"];
$_get_query = $_GET["q"];

$ssql = "SELECT * FROM table WHERE data=:q AND `id`>=:from LIMIT 11 ORDER BY `id`"; // データ抽出用SQL
// あえて LIMIT+1 の 11件を取得しようとしている

$dbh = new PDO("mysql:localhost", "user", "pass"); // DB接続

if(empty($_get_from)) { $_get_from = 1; }

// データ抽出用SQLを、プリペアドステートメントで実行
$ssth = $dbh->prepare($ssql);
$ssth->bindValue(":q", $_get_query);
$ssth->bindValue(":from", $_get_from);
$ssth->execute();
$data = $ssth->fetchAll(PDO::FETCH_ASSOC);

// 次のページがあるかどうか判断
$hasNext = false; // 次ページがあるかどうかフラグ
$nextId = 0;
if(count($data) > 10) { // 取得できた件数がLIMITより大きい=次ページに1件以上データがある!
$nextId = $data[count($data)]["id"]; // あふれた1件のIDを次ページの起点とする
array_pop($data); // あふれ分を消す
$hasNext = true; // 次ページがあるよ!
}

?>
<html>
<body>
<ul>
<?php
foreach($data as $row) {
printf("<li>%s</li>\n", $row["column"]);
}
?>
</ul>

<?php
if($hasNext) {
printf("<a href='?from=%d&q=%s'>次ページへ</a><br />\n", $nextId, $_get_query);
}
?>

</body>
</html>


良い点


  • インデックスを使って目的の場所まで飛んだ後、指定行数のみ取得するため、とても高速。
    ページが深くなってもパフォーマンスの低下はないと思われる。

悪い点


  • (現時点においては) 次ページにしか進めない。 戻れないのはページャーとしてどうかと。。。

  • 「nページ先が見たい」というニーズに応えることができない。


TODO:こういう場合どうするの?


  • データが大量(300万件~)にある。

  • IDは歯抜け状態。

  • WHEREで絞り込みを行っている。

今現在、ぶち当たっている壁がこれ。

COUNTすると5分ぐらい待たされてしまう。

どうすればいいんだろう? 教えて偉い人。


所感

とりあえず、思いついたものをざっくり書いてみた。

また見つけたら or 思いついたら追記する。