はじめに
成長中の店では、2 つの便利な注文表示が必要です。ある店舗の受け取り準備済み注文を古い順に並べたものと、ある顧客の注文履歴を新しい順に並べたものです。最初のページには注文が 7 件あります。日時と価格が同じでも、異なる注文 ID は区別する必要があります。0 セントの販促注文も、受け取り準備済みになり得ます。
再利用可能な MySQL クエリを 2 つと索引スクリプトを保存し、有用な索引を適用して、両方の最初のページを、共有のセカンダリ索引 2 個と、ページ当たり 64 回のネイティブ診断行リクエストという上限内に収めます。元の架空のスナップショットには 96,000 件を超える注文があります。この単元で調整するのは最初のページです。次の独立した単元で、カーソルによる完全なページ送りを学びます。
基本的なターミナル操作、MySQL 接続、SELECT/WHERE の知識が必要です。パラメーター、半開の時間範囲、並び順、索引、実際の実行計画の読み方は使用前に説明します。専用の MySQL サーバー、元のデータ、Terminal は準備済みです。Python やフロントエンドの開発は不要です。
/home/labex/project/order-desk で作業します。queue.sql、history.sql、indexes.sql を作成・編集し、最大 2 個のセカンダリ索引を管理します。元の注文、列、主キー、識別情報、状態、正確なアプリケーションのタイムスタンプ、セントの金額、メモ、実行ツールを保全してください。元の行の削除・書き換え、診断設定の変更、オプティマイザーヒントの追加、外部キャッシュの使用はしません。索引名や同等の SQL は異なってもかまいません。実行環境のラベルと出力は英語のままです。
準備済みの診断接続では、ストレージエンジン内の絞り込みを見えにくくする最適化であるインデックスコンディションプッシュダウンを無効にしています。これにより、ネイティブな行リクエストを比較しやすくなります。設定はこれらの接続だけに適用されます。この実習は物理ディスク I/O を測定せず、本番環境で何倍速くなるかを約束するものでもありません。ミリ秒の値や特定の実行計画文字列との完全一致は、合格条件ではありません。
処理対象を読み取り、受け取りキューを調整する
このステップでは、有用な索引を選ぶ前に、正当な注文を確認します。最初のページが速くなっても、必要な注文の事実は同じでなければなりません。
Terminal の通常のシェルから始めます。cd は作業領域に移動し、./ は現在のディレクトリのプログラムを実行します。db は、注文の読み取りとコース用索引の管理に使う準備済み MySQL クライアントを開きます。ヒアドキュメントは、引用符付きの SQL マーカーから閉じる SQL までの行を、そのクライアントに渡します。閉じるマーカーは単独行にします。SQL 文はセミコロンで終わり、ブロックが終わると、このクライアント接続は閉じます。パスやコマンド名を打ち間違えたら、ls で実際の名前を確認して、修正したコマンドを再試行します。元のデータは保全してください。
5 月 10 日の店舗 3 の準備済み注文を確認します。比較 >= は開始を含み、< は翌日の午前 0 時を除外します。ORDER BY placed_at,order_id は時刻、次に ID の昇順で並べます。ID が同じタイムスタンプでの順序を決めます。total_cents は整数のセントを保存し、placed_at はマイクロ秒 6 桁を保持するアプリケーションのタイムスタンプ DATETIME(6) です。提供されたタイムスタンプにタイムゾーン変換は不要です。
cd /home/labex/project/order-desk
./db <<'SQL'
SELECT order_id,shop_id,customer_id,state,placed_at,total_cents
FROM orders
WHERE shop_id=3 AND state='ready'
AND placed_at>='2025-05-10' AND placed_at<'2025-05-11'
ORDER BY placed_at,order_id;
SQL
対象の注文は 10 件です。異なる 9 ID が 09:15 を共有し、注文 900021 は 23:59:59.999999 にあります。注文 900004 は無料ですが、準備済みです。同時刻の注文の多くは 1250 セントですが、同じ価格は同じ注文を意味しません。翌日の午前 0 時ちょうどは翌日に属します。アクセス経路を変える際も、これらの事実を保ちます。
索引は、MySQL が行を探すのを助ける、追加の順序付き構造です。保存領域と書き込み時の保守コストがかかるため、考えられる索引をすべて加えることが目標ではありません。複合索引は複数の列でエントリーを並べます。主キーはすでに order_id を識別しています。この受付では、両方の処理を合わせて追加のセカンダリ索引を 2 個まで許可します。
実行計画は、MySQL が結果に到達する方法を示します。通常の EXPLAIN は推定値を含みます。EXPLAIN ANALYZE は SELECT を実際に実行し、actual ... rows=... loops=... などのイテレーターの観察結果を報告します。FORMAT=TREE は、イテレーターと呼ぶ実行ステップを、親子関係をインデントしたテキストで表示します。LIMIT 7 は、並べた行を最大 7 行要求します。最上位のイテレーターが 7 行を返しても、その下のテーブル走査イテレーターは、はるかに大きい集合を読み取る場合があります。経過時間は変動し、採点されません。以下の DATE(placed_at) は暦上の日付を取り出すので、この特定の基準リクエストでは、引き続き正しい 1 日を選択します。
./db <<'SQL'
EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id,shop_id,customer_id,state,placed_at,total_cents
FROM orders
WHERE shop_id=3 AND state='ready' AND DATE(placed_at)='2025-05-10'
ORDER BY placed_at,order_id
LIMIT 7;
SQL
最上位の 7 行だけでなく、下位のアクセスイテレーターを読んでください。適切な索引がなければ、約 96,000 件の注文を走査します。診断接続は、絞り込みを見える状態に保つためにインデックスコンディションプッシュダウンを無効にしています。この準備済みの比較設定を保ってください。小さいページが返るだけでは、データベースの作業量が少ない証明にはなりません。
店舗や状態の異なるリクエストと、任意の半開の時間範囲に対応するクエリを保存します。名前の先頭に @ を付けると、ネイティブな MySQL セッション変数になります。desk は各リクエストについて、そのクエリの接続に @group_id、@state、@from_at、@until_at を渡します。手動の db 接続で設定した変数は、別の接続には引き継がれません。SET 文は保存するクエリファイルの外に置きます。
両クエリのインターフェースは、order_id、group_id、actor_id、state、event_at、amount_cents の順で 6 列を返します。AS は、選択した列にインターフェース名を付けます。店舗が group、顧客が actor です。集計で置き換えず、実際の値を保ってください。LIMIT 7 は受付の 1 ページ 7 注文の契約です。減らせば、最適化ではなく注文を隠すことになります。ASC は昇順を明示します。
cat > queue.sql は以下の完全なテキストをファイルに書き込み、以前の内容を置き換えます。引用符付きヒアドキュメントマーカーはシェル展開を防ぎます。閉じるマーカーを単独行にして、ブロック全体を貼り付けます。
cat > queue.sql <<'SQL'
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE shop_id=@group_id AND state=@state
AND placed_at>=@from_at AND placed_at<@until_at
ORDER BY placed_at ASC,order_id ASC
LIMIT 7;
SQL
./desk page queue
page は保存した SQL をそのまま実行し、実際の行を表示します。最初のページには、無料注文を含め、ID 900001～900007 が含まれます。ネイティブな handler_requests 値は、この診断接続のサーバーとストレージエンジンの境界でのリクエスト数です。物理 I/O の回数ではありません。索引を付ける前は大きいままです。表示される次のリクエスト用カーソルは、実際に返った最後の行だけから求めます。この最初のページ用クエリは、まだカーソルを使いません。カーソル入力の null は、最初のページのリクエストに前の行がないことを意味します。
有用なアクセス用の索引の先頭部分は、まず店舗と状態を固定し、その後に指定された時間範囲と (time,ID) の順序を続けます。これらのクエリでは、等値条件の 2 列は順番を交換できます。WHERE 内の記述順が索引の順番を決めるわけではありません。時刻だけの索引では、別のリクエストで無関係な店舗や状態を調べ続けることがあります。新しいコピーにも適用できるよう、選んだ候補を indexes.sql に保存します。
cat > indexes.sql <<'SQL'
CREATE INDEX queue_time ON orders(shop_id,state,placed_at,order_id);
SQL
./db < indexes.sql
< はファイルをクライアントの SQL 入力として渡します。作るのはアクセス構造であり、新しい注文ではありません。後で同じ CREATE INDEX を再実行すると、索引がすでにあるため MySQL はキー名の重複を報告します。コース用に作った候補を保つか置き換えるか決める前に、既存の索引を確認してください。元の注文を削除したり、このスクリプトが何度適用しても同じ結果になる移行だと考えたりしないでください。
ここでの -e は、引用符内の SQL 文字列全体を実行して終了します。desk の plan アクションは、実際の保存済みクエリに EXPLAIN ANALYZE を実行します。--from と --until は、このリクエストの別の開始と排他的な終了を指定します。日付だけの値は午前 0 時を意味します。
./db -e "SHOW INDEX FROM orders;"
./desk page queue
./desk plan queue
./desk page queue --from 2025-01-05 --until 2025-01-12
アクセス経路は狭い索引範囲を使うようになるはずですが、元の 5 月 10 日のページは、同じ 7 注文 ID と正確なフィールドを保ちます。両リクエストで 64 回の上限を確認します。SHOW INDEX は存在を証明し、実際の計画、返された行、リクエスト数が、有用な動作を示します。索引名、コスト、ミリ秒の値は異なってもかまいません。
コマンドやファイル名を打ち間違えたら、エラーを読み、ls で作業領域を調べ、修正した名前で再試行します。Order desk error はファイル、SQL、接続のエラーであり、正常な空の結果ではありません。保存した SQL または索引候補を修正し、実際のリクエストを再実行します。正しさと観察した作業量の両方を確認してから、ステップのチェックを実行します。
顧客履歴と索引数の上限を共有する
このステップでは、受け取りキューを損なわず、セカンダリ索引 2 個を超えずに、2 つ目の業務リクエストに対応します。
Terminal の /home/labex/project/order-desk で続けます。元のテーブルと queue.sql を保ちます。履歴表示は、受け取り状態や店舗に関係なく 1 人の顧客を選び、最新の注文から表示します。DESC は順序を逆にします。同時刻の注文も決まった新しい順で並ぶよう、日時と ID の両方に使います。ネイティブな @actor_id パラメーターが顧客を識別します。固定の顧客で置き換えたり、キューの状態制限を加えたりしないでください。
最初のページ用の完全な履歴クエリを書きます。受け取りクエリと同じ 6 列のインターフェースと半開区間を使います。
cat > history.sql <<'SQL'
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE customer_id=@actor_id
AND placed_at>=@from_at AND placed_at<@until_at
ORDER BY placed_at DESC,order_id DESC
LIMIT 7;
SQL
./desk page history
./desk plan history
5 月 10 日の履歴は、最後のマイクロ秒の注文 900021 から始まり、その後に同時刻の ID が降順に続きます。実際のセントの金額と状態は見えるままです。この正しい最初のページでも、作業量が多すぎる場合があります。既存のキュー索引は、履歴で絞らない店舗・状態から始まるためです。
2 つ目の有用な索引の先頭部分は、顧客から始まり、日時・ID と続きます。昇順の索引は、履歴の降順に合わせて逆向きに走査できます。観察した計画で実際の動作が分かります。このタスクでは、追加のカバリング用の列は必要ありません。セカンダリ索引は 2 個以下に保ち、両処理の有用なアクセスを維持します。
cat >> indexes.sql は既存のソースファイルに新しい文を追記し、> なら置き換えます。この VM にはキュー索引がすでにあるため、追記後は以下の新しい CREATE 文だけを実行します。新しいデータベースでは、2 文を保存したファイルが完全な索引ソースになります。
cat >> indexes.sql <<'SQL'
CREATE INDEX actor_time ON orders(customer_id,placed_at,order_id);
SQL
./db -e "CREATE INDEX actor_time ON orders(customer_id,placed_at,order_id);"
./db -e "SHOW INDEX FROM orders;"
./desk page history
./desk plan history
より広い顧客履歴、別の識別情報、空の日付範囲、元のキューを確認します。--actor は指定する顧客 ID を変え、元データは変えません。有効なクエリが行を返さない場合もあり、実行エラーとは異なります。
./desk page history --from 2025-01-01 --until 2025-05-01
./desk page history --actor 78
./desk page history --from 2027-01-01 --until 2027-01-02
./desk page queue
両処理とも、診断上限内で正しい最初のページを保つ必要があります。該当なしのリクエストは、架空の注文ではなく、空の行リストを正直に返します。保存した indexes.sql と現在の定義は、独立に確認されます。良いファイルだけでは適用済みの証明にならず、動作する現在の索引も保存済みソースの代わりにはなりません。
候補を変える場合は SHOW INDEX を確認し、置き換えるコース用索引だけを削除し、indexes.sql を更新して新定義を適用します。元の主キー、列、行は決して削除しないでください。CREATE の繰り返しによる重複名エラーは、現在の定義を調べる理由であり、VM をリセットする理由ではありません。
この単元の到達点は、2 つの有用な最初のページの検索です。7 行が返っても、長いキュー全体を取得したことにはなりません。次の独立した単元では、これらの最初のページ用クエリと索引が準備された状態から始め、ページをまたいですべての注文を保つカーソル条件を学びます。保存した両クエリ、完全な索引スクリプト、有効なセカンダリ索引 2 個以下をそろえて、最終チェックを実行します。
まとめ
実際の注文データと実行時の観察結果を調べ、再利用可能な半開区間の受け取りクエリ・顧客履歴クエリを保存し、限られた有用な索引で両方の最初のページを支えました。元の識別情報、タイムスタンプ、セントの金額、メモはそのままです。ネイティブ診断行リクエストを、返された行数、推定値、物理 I/O と区別しました。複数ページにわたる完全な取得は、次の単元の目標です。
