カスタム変数を使用した MySQL クエリの最適化

並べ替えクエリの最適化

カスタム変数の重要な機能は、i = i + 1 メソッドと同様に、数学計算の結果を変数に同時に割り当てることができることです。データ テーブルの行番号を計算する例を次に示します。

SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 AS rownum
sakila.actor から LIMIT 3;

主キーは 1 から増分され、行番号と主キーの値が同じになるため、結果は無意味に見えるかもしれません。ただし、この方法はソートに使用できます。たとえば、最も多くの映画に出演した俳優の上位 10 名を照会する必要がある場合、通常は次のように記述します。

actor_id を選択し、COUNT(*) を cnt として取得します。
sakila.film_actorより
俳優IDでグループ化
個数で降順で並べ替え
制限 10;

主キーは 1 から増分され、行番号と主キーの値が同じになるため、結果は無意味に見えるかもしれません。ただし、この方法はソートに使用できます。たとえば、最も多くの映画に出演した俳優の上位 10 名を照会する必要がある場合、通常は次のように記述します。

actor_id を選択し、COUNT(*) を cnt として取得します。
sakila.film_actorより
俳優IDでグループ化
個数で降順で並べ替え
制限 10;

対応するランキング値を取得したい場合は、変数を導入してそれを完成させることができます。

@curr_cnt := 0、@prev_cnt := 0、@rank := 0 に設定します。
俳優IDを選択します。
	@curr_cnt := cnt は cnt として扱われ、
  @rank := IF(@prev_cnt <> @curr_cnt, @rank+1, @rank) をランクとして、
  @prev_cnt := @curr_cnt ダミー
から （
  actor_id、COUNT(*) を cnt として選択します。
  sakila.film_actorより
	俳優IDでグループ化
	個数で降順で並べ替え
	制限 10
）をderとして;

ここでは、再生された映画の数が curr_cnt 変数に割り当てられ、prev_cnt は前の俳優が演じた役の数を格納するために使用されます。ランキングは1位から始まります。後ろの俳優の数が前の順位の俳優の数と異なる場合は、ランキングが下がります（+1）。同じ場合は、前の俳優と同じランキングになります。この方法では、データベース クエリからの二次処理を必要とせずに、クエリ結果から直接俳優のランキングを取得できます (もちろん、これはプログラム コードを通じて実現することもできます)。

変更されたばかりのデータ行を繰り返し取得しないようにする

データ行を更新する際に、データ行の情報を再取得したい場合には、データベースを再度読み込む必要がある場合が多くあります。これは、MySQL が PostgreSQL の UPDATE RETURNING 関数のように更新されたデータ行を同時に返さず、更新によって影響を受けた行数のみを返すためです。ただし、カスタム変数を使用するとこれを実行できます。たとえば、更新時刻が変更されたばかりの行を取得するには、カスタム変数を使用せずに追加のクエリが必要です。

tb1 を更新します。set lastUpdated = NOW() WHERE id = 1;
tb1 から lastUpdated を選択し、 id = 1 を指定します。

これはカスタム変数を使用することで回避できます。

tb1 を更新し、 lastUpdated = NOW() を設定します。 WHERE id = 1 かつ @now := NOW();
@now を選択します。

クエリ操作はまだありますが、後続のクエリ操作ではデータベースにアクセスする必要がなくなります。

遅延ロードされた結合クエリ

次のタスクを完了するためにユニオン クエリを記述する必要があるとします。ユニオンのブランチで一致するデータ行を検索し、見つかった場合は他のブランチをスキップします。これは、ホット データまたはアクセス頻度の低いデータ (最近の注文や履歴注文など) を検索する必要がある場合に発生します。以下はユーザークエリの一般的な SQL です。

SELECT id FROM users WHERE id = 123
ユニオンオール
users_archived から id を選択します。WHERE id = 123;

このクエリは、まず現在使用されているユーザー テーブルから ID 123 のユーザーを照会し、次にアーカイブされたユーザー テーブルから同じ ID のユーザーを検索します。ただし、この書き込み方法は非効率的です。目的のユーザーが users テーブルで見つかった場合でも、users_archived テーブルで再度検索する必要があります。実際のユーザー ID 123 は、いずれかのテーブルにのみ存在するか、2 つのテーブルのデータが同じになります。この状況は、遅延ロード結合クエリを使用することで回避できます。つまり、最初のブランチにデータが見つからない場合にのみ、2 番目のブランチがクエリされます。したがって、複数のデータ列が返されることを回避するために、クエリ結果のコンテナとして MySQL の GREATEST メソッドを使用できます。

SELECT GREATEST(@found := -1, id) AS id, users.name, 'users' as which_tb1
ユーザーID = 123 から
ユニオンオール
	SELECT id、users_archived.name、'users_archived'
  users_archived から、id = 123 かつ @found が NULL である
ユニオンオール
	SELECT 1, '', 'reset' FROM DUAL WHERE ( @found := NULL) IS NOT NULL;

上記のクエリの最初の行に結果がある場合、@found には値が割り当てられず NULL になり、2 番目のクエリが実行されます。 3 番目の UNION は実際には効果がありません。この SQL を繰り返し実行できるように、@found を NULL に戻すだけです。確認する別の方法は、同じテーブルに対してこのような操作を実行し、実際に返されるデータが 1 行のみであるか、データが返されないか (データが見つからない場合) を確認することです。

SELECT GREATEST(@found := -1, `id`) AS `id`, `infocenter_city`.`name`, 'city' as which_tb1 
`infocenter_city` から `id` = 460100 
ユニオンオール 
	`id`、`infocenter_city`.`name`、'infocenter_city' を選択 
	`infocenter_city` から、id = 460100 かつ @found が NULL の場合 
ユニオンオール 
	SELECT 1, '', 'reset' FROM DUAL WHERE ( @found := NULL) IS NOT NULL

上記は、クエリ最適化のためにカスタム変数を使用する MySQL の詳細です。クエリ最適化のためにカスタム変数を使用する MySQL の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。