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

# やりたいこと

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


たとえばこんなやつ。
![キャプチャ.PNG](https://qiita-image-store.s3.amazonaws.com/0/129737/e20eca17-3263-f9f4-ed79-0cebec13fbc9.png)


上記例のようにページ数を指定して飛べるものや、
単に「前へ」「次へ」のナビゲーションを行うものまで多種多様。

サンプルコードについては動作確認していない。
あくまでヒントとして。


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

- PHP
- MySQL系RDB


# サンプルコード

## COUNT/LIMIT法、ページ数対応

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

```php: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` を無視したレコード件数を取得することができる。

```php: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_ROWS` が `COUNT()` よりも遅いため、データ量によってはパフォーマンスがかなり低下する。
ごく小規模（～1,000件）程度の使用に留める。

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

## COUNT/BETWEEN法、ページ数対応

COUNT/LIMIT法を改良したもの。
**キーが連番かつ歯抜けになっていないことが条件** だが
`LIMIT` のパフォーマンス低下を回避できる。

```php: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法、次ページのみ

ページで切り替えなんていらない！
次のページへ進むのみ！ 猪突猛進！

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

```php: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 思いついたら追記する。