MySQLのSQL_CALC_FOUND_ROWS クエリー修飾子とFOUND_ROWS() 関数について

今日は、MySQLのSQL_CALC_FOUND_ROWS クエリー修飾子とFOUND_ROWS() 関数についてご紹介します。

背景

SQL_CALC_FOUND_ROWSとFOUND_ROWSは、MySQLの結果セットの行数を特定するために使用される特殊なSQL関数です。

通常のSELECT文を使用して結果セットを取得すると、その結果セットの行数は取得した行数と同じです。しかし、SQL_CALC_FOUND_ROWSを使用することで、結果セットの行数を特定するための追加の情報を取得することができます。

SQL_CALC_FOUND_ROWSを使用する主な理由は、ページネーション（結果を複数のページに分割する方法）を実装する際に便利なことです。例えば、検索結果を複数のページに分割し、ユーザーにそれらのページを表示する場合、最初のページに表示される行数を制限するためにLIMIT句を使用します。しかし、ユーザーが次のページを要求した場合には、次のセットの結果を取得するためにLIMIT句を再度適用する必要があります。

このとき、SQL_CALC_FOUND_ROWSを使用して結果セットの行数を特定することで、全体の結果セットの行数を取得するための追加のクエリを実行せずに済みます。FOUND_ROWS関数を使用して直前のSELECT文によって返された全体の結果セットの行数を取得することができます。

具体例

具体的な例を見てみましょう。

SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE age > 25 LIMIT 0, 10;

このクエリは、ageが25よりも大きいユーザーの最初の10行を取得します。

SELECT時に SQL_CALC_FOUND_ROWS クエリー修飾子を使用することで、FOUND_ROWS()関数を使用して、全体の結果セットの行数を特定することもできます。上記の例だと、age > 25の条件を満たす件数を取得することになります。

SELECT FOUND_ROWS();

このクエリは、直前のSELECT文によって返された全体の結果セットの行数を返します。

SQL_CALC_FOUND_ROWSとFOUND_ROWSは、結果セットの行数を特定するための便利な機能であり、ページネーションなどの場面で特に有用です。

デメリット

SQL_CALC_FOUND_ROWSとFOUND_ROWSの使用にはいくつかのデメリットがあります。

1. パフォーマンスの低下: SQL_CALC_FOUND_ROWSを使用すると、データベースエンジンは実際の結果セットの行数を特定するために追加の処理を行う必要があります。これにより、クエリの実行時間が増加し、パフォーマンスが低下する可能性があります。特に、結果セットが非常に大きい場合や、複雑なクエリを実行する場合には影響が大きくなります。
2. キャッシュの問題: SQL_CALC_FOUND_ROWSを使用すると、データベースエンジンは結果セット全体をキャッシュする必要があります。結果セットが大きい場合、キャッシュの使用量が増加し、他のクエリのキャッシュ効果が低下する可能性があります。これにより、データベース全体のパフォーマンスが低下する可能性があります。
3. メモリ使用量の増加: SQL_CALC_FOUND_ROWSを使用すると、データベースエンジンは結果セット全体をメモリに保持する必要があります。結果セットが大きい場合、メモリ使用量が増加し、システムのメモリリソースが枯渇する可能性があります。
4. 結果の一貫性の欠如: SQL_CALC_FOUND_ROWSは、実際の結果セットの行数を特定するための近似値を提供します。これは、同じクエリを実行しても結果セットの行数が異なる場合があることを意味します。これは、同時に複数のトランザクションが同じテーブルに変更を加える場合や、テーブルが頻繁に変更される場合に特に問題となります。

したがって、SQL_CALC_FOUND_ROWSとFOUND_ROWSを使用する際には、パフォーマンスやメモリ使用量、結果の一貫性などのデメリットを考慮し、使用するかどうかを慎重に判断する必要があります。小規模な結果セットや必要な場面でのみ使用することが推奨されます。

デメリットへの対策

結果セットが非常に大きい場合に使用するときは、下記のようにPKだけを取得して、PKから必要なカラムを取得するという方法もありかもしれません。

-- PKだけ取得
SELECT SQL_CALC_FOUND_ROWS PK FROM users WHERE age > 25 LIMIT 0, 10;

-- 総数を取得
SELECT FOUND_ROWS();

-- 必要なカラムをPKから取得
SELECT * FROM users WHERE PK in (pk1, pk2, ..., pk10)

この手法だとSQLを3回発行することになるので、下記のようにCOUNT(*)しても良いのかなとも思います。

-- 結果セット取得
SELECT * FROM users WHERE age > 25 LIMIT 0, 10;

-- 総数を取得
SELECT COUNT(*) FROM users WHERE age > 25;

ただし、where句の条件次第では、取得までに時間がかかったりすることも考えられるので、ケースバイケースで使用箇所を見極める必要がありそうです。

MySQL 8.0.17で非推奨に

ここまで書いておいてなんなんですが、なんと、SQL_CALC_FOUND_ROWS クエリー修飾子および付随する FOUND_ROWS() 関数は、MySQL 8.0.17 で非推奨になりました。詳しくは下記の公式サイトをご覧ください。

なので、今後は、COUNT(*) 関数を使用して、件数をとる方法にしたほうが良さそうです。

おわりに

今日は、MySQLのSQL_CALC_FOUND_ROWS クエリー修飾子とFOUND_ROWS() 関数についてご紹介しました。

SQL_CALC_FOUND_ROWS クエリー修飾子および付随する FOUND_ROWS() 関数は、MySQL 8.0.17 で非推奨になりました。これらは、MySQL の将来のバージョンで削除される予定です。

SQL_CALC_FOUND_ROWS クエリー修飾子は、LIMIT 句に関係なく、結果セットに存在する行数を計算するように MySQL に指示します。そのあと、行数は SELECT FOUND_ROWS() を使用して取得できます。

ただし、この機能は非効率的であり、必要がない場合でも MySQL が結果セット内のすべての行を計算するように強制するため、非推奨になりました。

FOUND_ROWS() 関数は、LIMIT 句が指定されていない SELECT ステートメントの結果セット内の行数を返します。ただし、この関数は非信頼性が高く、結果セット内の行数が正確でない場合があります。

MySQL 8.0.17 以降、行数を計算するには、COUNT() 関数を使用することをお勧めします。COUNT() 関数は、必要に応じて結果セット内のすべての行を計算する必要がなく、結果セット内の行数を正確に返すため、より効率的です。

非推奨になっていたことが衝撃的すぎて備忘のため記事に残しました。