MySQL - LIMIT句

概要

LIMIT 句は、MySQLのSQLクエリの一部として使用されており、取得するレコードの数を制限するためのものである。

大量のレコードがあるテーブルから特定の数のレコードを取得する場合に使用する。
これにより、不要なレコードの読み込みを避けて、パフォーマンスを向上させることができる。

LIMIT 句は SELECT 文、DELETE 文で使用することができ、OFFSET 句と組み合わせることで、取得開始位置を指定することも可能である。

ページネーション機能の実装においては、オフセットベース方式とカーソルベース方式の2つのアプローチが存在する。
大規模なデータセットを扱う場合、適切なインデックス設計とページネーション方式の選択がパフォーマンスに大きく影響する。

MySQL 8.0以降では、ウィンドウ関数との組み合わせにより、より高度なデータ取得が可能となっている。
また、ORDER BY 句と組み合わせることにより、特定の順序でレコードを取得したり、ランダムなレコードを取得したりすることもできる。

システム変数 max_allowed_packet の設定値にも注意が必要であり、大量のレコードを取得する場合には適切な値に調整する必要がある。

LIMITの基本構文

LIMIT 句は、SELECT 文の最後に記述する。

 SELECT <* または カラム名> FROM <テーブル名> LIMIT <数値>;
 
 -- 例: hogeテーブルから最初の5件のレコードを取得する
 SELECT * FROM hoge LIMIT 5;

例えば、ユーザにランダムな商品を表示する場合、ORDER BY 句 と RAND() 関数と組み合わせて使用する。

 SELECT * FROM <テーブル名> ORDER BY RAND() LIMIT <数値>;

新しい順にレコードを取得する場合、ORDER BY 句 と DESC キーワードを組み合わせて使用する。

 SELECT * FROM <テーブル名> ORDER BY <カラム名> DESC LIMIT <数値>;

※注意
LIMIT句のみを使用する場合、レコードの取得順序は保証されない。
特定の順序でレコードを取得する場合は、必ずORDER BY句と組み合わせる必要がある。

OFFSET句

OFFSET 句は、LIMIT 句と組み合わせて、取得開始位置を指定するために使用される。

OFFSET構文

OFFSET 句を使用することにより、指定した位置からレコードを取得することができる。

 -- 標準構文
 SELECT * FROM <テーブル名> LIMIT <row_count> OFFSET <offset>;
 
 -- 省略構文
 SELECT * FROM <テーブル名> LIMIT <offset>, <row_count>;
 
 -- 例: 10件目から5件のレコードを取得
 SELECT * FROM users LIMIT 5 OFFSET 10;
 SELECT * FROM users LIMIT 10, 5;  -- 上と同じ結果

※注意
省略構文では、引数の順序が逆になることに注意が必要である。

引数の仕様

LIMIT句とOFFSET句の引数には、以下に示す仕様が存在する。

• 非負の整数定数を指定する必要がある。
• プリペアドステートメントでは、プレースホルダー (?) を使用することができる。
• ストアドプロシージャ内では、整数値の変数またはパラメータを使用することができる。

 -- プリペアドステートメントでの使用例
 
 PREPARE stmt FROM 'SELECT * FROM users LIMIT ? OFFSET ?';
 SET @limit = 10;
 SET @offset = 20;
 EXECUTE stmt USING @limit, @offset;

特定位置から全行を取得

特定の位置から残りの全ての行を取得する場合は、大きな数値を指定する。

 -- 95行目以降の全レコードを取得
 
 SELECT * FROM users LIMIT 95, 18446744073709551615;

LIMIT 0の用途

LIMIT 0 は、実際のデータを取得せずに、以下に示す用途で使用される。

• クエリ構文の妥当性確認
• テーブルのメタデータ取得 (列名、データ型等)
• クエリの実行計画の確認

 -- メタデータのみを取得
 
 SELECT * FROM users LIMIT 0;

レコード取得の制限

特定のアプリケーションでは、ユーザがレコードを取得する場合に、一定の制限を設ける場合がある。
これにより、以下に示すようなメリットがある。

• サーバへの過度な負荷を防ぐ。
• 情報漏洩を制限できる。
• サービス拒否攻撃 (DoS) の影響を軽減できる。
• ユーザエクスペリエンスの向上。

必要な数のみレコードを取得することにより、サーバのリソースを効果的に使用できる。

MySQLの設定とLIMIT句

システム変数 max_allowed_packet は、1度に送受信できるデータの最大サイズを制御することができる。

ただし、この値が小さすぎる場合、LIMIT 句を使用して大量のレコードを取得する時にエラーが発生する可能性がある。
適切な値にすることにより、スムーズに大量のレコードを取得することができる。

ページネーションの実装

ページネーションは、大量のデータを複数のページに分割して表示する手法である。
MySQLでは、主に2つの方式が存在する。

オフセットベースのページネーション

最も基本的なページネーション方式であり、OFFSET 句を使用して実装する。

 -- 1ページ目 (0〜9件目)
 SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;
 
 -- 2ページ目 (10〜19件目)
 SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;
 
 -- 3ページ目 (20〜29件目)
 SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

※注意
この方式は、大きなOFFSET値を使用する場合にパフォーマンスが低下する。

決定論的なソートの重要性

ページネーションを実装する場合、決定論的なソート (常に同じ順序を返すソート) が重要である。

複数の行が同じ値を持つ場合、ORDER BY 句の結果は不確定になる可能性がある。
これにより、ページをまたいで同じレコードが表示されたり、レコードが欠落したりする問題が発生する。

 -- 不確定なソート (複数のユーザが同じcreated_atを持つ場合)
 SELECT * FROM users ORDER BY created_at LIMIT 10 OFFSET 0;
 
 -- 決定論的なソート (一意性を保証)
 SELECT * FROM users ORDER BY created_at, id LIMIT 10 OFFSET 0;

主キーや一意なカラムを ORDER BY 句に追加することにより、決定論的なソートを実現できる。

カーソルベースのページネーション (キーセット方式)

カーソルベースのページネーションは、OFFSET 句を使用せず、WHERE 条件でカーソル位置を指定する方式である。

この方式は、大規模なデータセットにおいて高いパフォーマンスを発揮する。

 -- 1ページ目
 SELECT * FROM products ORDER BY id LIMIT 10;
 
 -- 2ページ目 (最後に取得したidが100の場合)
 SELECT * FROM products WHERE id > 100 ORDER BY id LIMIT 10;
 
 -- 3ページ目 (最後に取得したidが110の場合)
 SELECT * FROM products WHERE id > 110 ORDER BY id LIMIT 10;

カーソルベース方式の利点

• O(log N) の時間計算量であり、大規模なデータセットでも高速である。
• 行の削除に強い。(他のユーザがレコードを削除しても、ページのずれが発生しにくい)
• インデックスを効果的に活用できる。

カーソルベース方式の欠点

• 特定のページ番号に直接アクセスすることができない。
• 前のページに戻ることが複雑になる。
• 実装がやや複雑である。

複数列でのカーソルベースページネーション

複数の列でソートする場合のカーソルベースページネーションの実装例を以下に示す。

 -- created_atとidでソートする場合
 
 -- 1ページ目
 SELECT * FROM users ORDER BY created_at DESC, id DESC LIMIT 10;
 
 -- 2ページ目 (最後に取得したレコードがcreated_at='2024-01-01', id=100の場合)
 SELECT * FROM users
    WHERE (created_at < '2024-01-01')
       OR (created_at = '2024-01-01' AND id < 100)
    ORDER BY created_at DESC, id DESC
    LIMIT 10;

デファードジョイン方式

デファードジョイン方式は、オフセットベースとカーソルベースの利点を組み合わせた手法である。

この方式では、まずインデックスを使用して必要な主キーのみを取得し、その後にJOINで全てのカラムを取得する。

 -- 通常のオフセットベース (非効率)
 SELECT * FROM large_table ORDER BY created_at LIMIT 10 OFFSET 100000;
 
 -- デファードジョイン方式 (効率的)
 SELECT t.*
 FROM large_table t
 INNER JOIN (
    SELECT id
    FROM large_table
    ORDER BY created_at
    LIMIT 10 OFFSET 100000
 ) AS subquery USING (id);

この方式により、大きな OFFSET 値を使用する場合でも、パフォーマンスの低下を抑えることができる。

パフォーマンス最適化

LIMIT 句を使用する場合、パフォーマンスを最適化するための手法が存在する。

大きなOFFSET値の問題点

OFFSET 句に大きな値を指定する場合、MySQLは指定された位置までの全ての行を読み取る必要がある。

これにより、時間計算量がO(N)となり、OFFSET 値が大きいほどパフォーマンスが低下する。

 -- 100万件目から10件を取得する場合、
 -- MySQLは100万件を読み取ってから結果を返す
 
 SELECT * FROM large_table ORDER BY id LIMIT 10 OFFSET 1000000;

このような場合、カーソルベースのページネーションやデファードジョイン方式を使用することが推奨される。

カバリングインデックスの活用

カバリングインデックスは、クエリで必要な全てのカラムを含むインデックスである。

カバリングインデックスを使用することにより、テーブルデータにアクセスせずにインデックスのみでクエリを処理できる。

 -- カバリングインデックスを作成
 CREATE INDEX idx_covering ON users (status, created_at, id, name);
 
 -- カバリングインデックスを活用したクエリ
 SELECT id, name, created_at
 FROM users
 WHERE status = 'active'
 ORDER BY created_at DESC
 LIMIT 10;

EXPLAIN コマンドを使用して、クエリがカバリングインデックスを使用しているかを確認できる。

ORDER BY + LIMIT最適化

MySQLは、ORDER BY 句 と LIMIT 句を組み合わせたクエリに対して、特別な最適化を行う。

適切なインデックスが存在する場合、MySQLは全ての行をソートせずに、必要な数の行のみを処理する。

 -- インデックスを作成
 CREATE INDEX idx_created_at ON users (created_at);
 
 -- 最適化されたクエリ
 SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

この最適化により、大規模なテーブルでも高速にクエリを実行できる。

InnoDBでの主キーの自動追加

InnoDBストレージエンジンでは、ORDER BY 句に主キーが自動的に追加される場合がある。

これにより、決定論的なソートが自動的に保証されることがある。
ただし、この動作に依存せず、明示的に主キーを指定することが推奨される。

 -- 明示的に主キーを指定
 
 SELECT * FROM users ORDER BY status, id LIMIT 10;

サブクエリ・JOIN・UNIONでのLIMIT句

LIMIT 句は、サブクエリ、JOIN、UNION と組み合わせて使用することができる。

サブクエリでのLIMIT

サブクエリ内で LIMIT 句を使用することにより、サブクエリの結果を制限できる。

 -- サブクエリで最新の10件の注文を取得し、その合計金額を計算
 
 SELECT SUM(amount) AS total
 FROM (
    SELECT amount
    FROM orders
    ORDER BY created_at DESC
    LIMIT 10
 ) AS recent_orders;

JOINでのLIMIT使用時の注意

JOIN を使用する場合、LIMIT 句は結合後の結果に適用される。

 -- JOINした後の結果から10件を取得
 
 SELECT users.name, orders.amount
 FROM users
 INNER JOIN orders ON users.id = orders.user_id
 ORDER BY orders.created_at DESC
 LIMIT 10;

各テーブルから10件ずつ取得する場合は、サブクエリを使用する必要がある。

 -- 各ユーザの最新10件の注文を取得
 
 SELECT users.name, recent_orders.amount
 FROM users
 INNER JOIN (
    SELECT user_id, amount, created_at
    FROM orders
    ORDER BY created_at DESC
    LIMIT 10
 ) AS recent_orders ON users.id = recent_orders.user_id;

UNIONでのLIMIT

UNION を使用する場合、LIMIT 句は個別の SELECT 文または全体の結果に適用できる。

 -- 各SELECTで10件ずつ取得し、結果を結合
 
 (SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 10)
 UNION ALL
 (SELECT * FROM users WHERE status = 'inactive' ORDER BY created_at DESC LIMIT 10);
 
 -- 結合後の全体から10件を取得
 (SELECT * FROM users WHERE status = 'active')
 UNION ALL
 (SELECT * FROM users WHERE status = 'inactive')
 ORDER BY created_at DESC
 LIMIT 10;

※注意
個別の SELECT 文に LIMIT 句を適用する場合は、括弧で囲む必要がある。

UPDATE文 / DELETE文でのLIMIT句

LIMIT 句は、DELETE 文で使用することができるが、UPDATE 文では直接使用することができない。

DELETE文でのLIMIT

DELETE 文で LIMIT 句を使用することにより、削除する行数を制限できる。

 -- 古いログレコードを10件削除
 
 DELETE FROM logs
 ORDER BY created_at ASC
 LIMIT 10;

バッチ削除パターン

大量のレコードを削除する場合、一度に削除せずにバッチで削除することが推奨される。

これにより、テーブルロックの時間を短縮し、レプリケーションへの影響を最小限に抑えることができる。

 -- 古いログを1000件ずつ削除
 
 DELIMITER $$
 
 CREATE PROCEDURE delete_old_logs()
 BEGIN
    DECLARE rows_affected INT DEFAULT 1;
 
    WHILE rows_affected > 0 DO
       DELETE FROM logs
       WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
       ORDER BY created_at ASC
       LIMIT 1000;
 
       SET rows_affected = ROW_COUNT();
 
       -- レプリケーションの負荷を軽減するために短い待機時間を設ける
       DO SLEEP(0.1);
    END WHILE;
 END$$
 
 DELIMITER ;

制限事項

LIMIT 句を DELETE 文で使用する場合、以下に示す制限事項が存在する。

• 単一テーブルのみサポートされる。(マルチテーブル削除では使用不可)
• サブクエリとの併用には制限がある。

 -- エラー: マルチテーブル削除ではLIMITを使用できない
 
 DELETE users, orders
 FROM users
 INNER JOIN orders ON users.id = orders.user_id
 WHERE users.status = 'deleted'
 LIMIT 10;  -- エラー

UPDATE文での代替方法

UPDATE 文では LIMIT 句を直接使用できないが、サブクエリを使用することで同様の効果を得られる。

 -- サブクエリを使用して最初の10件を更新
 
 UPDATE users
 SET status = 'processed'
 WHERE id IN (
    SELECT id FROM (
       SELECT id
       FROM users
       WHERE status = 'pending'
       ORDER BY created_at ASC
       LIMIT 10
    ) AS tmp
 );

※注意
サブクエリを2重にネストしているのは、MySQLの制限により、更新対象のテーブルを直接サブクエリで参照できないためである。

MySQL 8.0以降の機能

MySQL 8.0では、ウィンドウ関数が導入され、LIMIT 句と組み合わせることで高度なデータ取得が可能になった。

ウィンドウ関数との組み合わせ

ウィンドウ関数を使用することにより、各グループ内での順位付けや集計を行うことができる。

 -- ウィンドウ関数で順位を付けてから上位10件を取得
 
 SELECT *
 FROM (
    SELECT
       id,
       name,
       department,
       salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
 ) AS ranked_employees
 WHERE salary_rank <= 10;

ROW_NUMBER関数を使用したページネーション

ROW_NUMBER 関数を使用することにより、決定論的なページネーションを定義できる。

この方式は、決定論的なソートを保証しつつ、特定のページ範囲を取得することができる。

 -- ROW_NUMBER関数を使用したページネーション
 
 SELECT *
 FROM (
    SELECT
       id,
       name,
       created_at,
       ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS row_num
    FROM users
 ) AS numbered_users
 WHERE row_num BETWEEN 11 AND 20;  -- 2ページ目 (11〜20件目)

RANK()、DENSE_RANK()の使用

RANK 関数 や DENSE_RANK 関数を使用することにより、同順位を考慮したランキングを実装できる。

 -- 各部門の上位5名を取得
 
 SELECT *
 FROM (
    SELECT
       id,
       name,
       department,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
    FROM employees
 ) AS ranked_employees
 WHERE dept_rank <= 5;

PARTITION BYの活用

PARTITION BY 句を使用することにより、グループごとに LIMIT のような制限を実現できる。

 -- 各カテゴリの最新3件の商品を取得
 
 SELECT *
 FROM (
    SELECT
       id,
       name,
       category,
       created_at,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY created_at DESC) AS row_num
    FROM products
 ) AS numbered_products
 WHERE row_num <= 3;

FETCH FIRST構文の非サポート

SQL標準の FETCH FIRST 構文は、MySQL 8.0でもサポートされていない。

 -- エラー : MySQLではFETCH FIRST構文はサポートされていない
 
 SELECT * FROM users
 ORDER BY created_at DESC
 FETCH FIRST 10 ROWS ONLY;  -- エラー
 
 -- MySQLでは以下を使用する
 SELECT * FROM users
 ORDER BY created_at DESC
 LIMIT 10;

ROW_COUNT関数との関係

ROW_COUNT 関数は、直前のSQL文が影響を与えた行数を返す関数である。

ROW_COUNT関数の動作

ROW_COUNT 関数は、INSERT、UPDATE、DELETE 文で影響を受けた行数を返す。

 -- DELETE文で削除された行数を確認
 
 DELETE FROM logs
 WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
 LIMIT 1000;
 
 SELECT ROW_COUNT();  -- 削除された行数が返される

SELECT文では-1を返す

SELECT 文を実行した場合、ROW_COUNT 関数は -1 を返す。

SELECT 文の結果行数を取得する場合は、FOUND_ROWS 関数を使用する必要があるが、これはMySQL 8.0で非推奨となっている。

 SELECT * FROM users LIMIT 10;
 
 SELECT ROW_COUNT();  -- -1が返される

バッチ処理での活用

ROW_COUNT 関数は、バッチ削除やバッチ更新の処理で活用できる。

 -- バッチ削除の例
 
 DELIMITER $$
 
 CREATE PROCEDURE batch_delete()
 BEGIN
    DECLARE affected_rows INT;
    DECLARE total_deleted INT DEFAULT 0;
 
    REPEAT
       DELETE FROM logs
       WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
       LIMIT 1000;
 
       SET affected_rows = ROW_COUNT();
       SET total_deleted = total_deleted + affected_rows;
 
       DO SLEEP(0.1);
    UNTIL affected_rows = 0 END REPEAT;
 
    SELECT total_deleted AS total_deleted_rows;
 END$$
 
 DELIMITER ;

SQL_CALC_FOUND_ROWS、FOUND_ROWS関数の非推奨

MySQL 8.0.17以降、SQL_CALC_FOUND_ROWS クエリ修飾子 と FOUND_ROWS 関数は非推奨となった。

 -- 非推奨 : SQL_CALC_FOUND_ROWSの使用
 
 SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 10;
 SELECT FOUND_ROWS();  -- 全体の行数を返す (非推奨)

代わりに、COUNT(*) クエリを別途実行することが推奨される。

 -- 推奨 : COUNT(*)を使用
  
 SELECT COUNT(*) AS total FROM users WHERE status = 'active';
 SELECT * FROM users WHERE status = 'active' LIMIT 10;