概要
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;