インデックスを指定しないと MySQL が遅くなる理由

Question

SQL クエリを最適化しようとしていますが、正しく行う方法を知りたいです。

リーリー

ここでは、インデックス (booking_id, from_spot_id, to_spot_id) を強制します。これにより、クエリは最も近い日付から 25 秒以内に約 100 ミリ秒実行されます。

booking テーブルには約 200 万行があり、ride テーブルには約 500 万行があります。

ただし、強制インデックスを使用してより多くの行をスキャンしていることがわかります:

＃＃＃表面＃＃＃ ＃＃＃パーティション＃＃＃ ＃＃＃タイプ＃＃＃ ＃＃＃鍵＃＃＃ ＃＃＃参照する＃＃＃ ＃＃＃わかりました＃＃＃ ＃＃＃追加＃＃＃ ＃＃＃単純＃＃＃ ＃＃＃範囲＃＃＃ 6 ＃＃＃単純＃＃＃ ＃＃＃参照する＃＃＃ 100.0 ＃＃＃単純＃＃＃ ＃＃＃主要＃＃＃ ＃＃＃単純＃＃＃ ＃＃＃主要＃＃＃ ＃＃＃主要＃＃＃ インデックスを使用しない同じクエリとの比較: ＃＃＃表面＃＃＃ ＃＃＃パーティション＃＃＃ ＃＃＃タイプ＃＃＃ ＃＃＃鍵＃＃＃ ＃＃＃参照する＃＃＃ ＃＃＃わかりました＃＃＃ ＃＃＃追加＃＃＃ ＃＃＃単純＃＃＃ ＃＃＃参照する＃＃＃ 767 ＃＃＃絶え間ない＃＃＃

id	タイプを選択してください	使用可能なキー	key_len	フィルタ済み			1	b1_		メイン、booking_id_end_IDX、booking_id_IDX、booking_id_start_IDX、IDX_E00CEDDEB75363F7、IDX_E00CEDDE37D3107C、IDX_E00CEDDEDEA4208C、booking_paid_at_IDX、booking_cancelled_at_IDX	IDX_E00CEDDE37D3107C
111456	6.6	インデックス条件を使用し、場所を使用します		1	r0_		ride_booking_id_IDX	ride_booking_id_IDX	109	ector.b1_.id	1
		1	s2__		eq_ref	メイン、IDX_B9327A739F2C3FAB、スポットタイプ_IDX	4	ector.r0_.from_spot_id	1	72.52	使用場所
1	s3_		eq_ref	4	ector.r0_.to_spot_id	1	100.0	使用場所
id	タイプを選択してください	使用可能なキー	key_len	フィルタ済み			1	s2__		メイン、IDX_B9327A739F2C3FAB、スポットタイプ_IDX	スポットタイプ_IDX

161

100.0インデックス条件を使用する ＃＃＃単純＃＃＃ ＃＃＃参照する＃＃＃ 1 ＃＃＃単純＃＃＃ b1_ ＃＃＃主要＃＃＃ ＃＃＃単純＃＃＃ eq_ref ＃＃＃主要＃＃＃ ＃＃＃主要＃＃＃

	1	r0_		IDX_9B3D7CD0ABAF30D3、IDX_9B3D7CD03301C60、ride_booking_id_IDX、booking_from_spot_to_spot_IDX	IDX_9B3D7CD0ABAF30D3	5	ector.s2_.id	392	100.0
	eq_ref	メイン、booking_id_end_IDX、booking_id_IDX、booking_id_start_IDX、IDX_E00CEDDEB75363F7、IDX_E00CEDDE37D3107C、IDX_E00CEDDEDEA4208C、booking_paid_at_IDX、booking_cancelled_at_IDX	108	ector.r0_.booking_id	1	5.0	使用場所		1	s3_
4	ector.r0_.to_spot_id	1	100.0	使用場所		私の知る限り、start_at および end_at との比較に使用している日付が、クエリが著しく高速になる理由です。 そこで、最も遅い部分をより小さなクエリに分離しようとします。 b.start_at < '2021-01-01' 和 b.end_at > '2021-01-01'; 予約 b から * を選択してください テーブル予約には、(start_at) と (end_at) という 2 つのインデックスがあります。これにより、最大値と最小値に近づくにつれて、このクエリの実行が速くなります。ほとんどの行をフィルタリングし、スキャンする行はごくわずかに残します)。 ただし、かなり過去のランダムな値を使用すると、処理が大幅に遅くなります。上記のクエリは、予想どおり 2 つのインデックスのうち 1 つだけを使用するため、実行に 10 秒かかります。なぜこのような単純なクエリに merge_index の説明が表示されないのかわかりません。 idタイプを選択してください使用可能なキーkey_lenフィルタ済み1bIDX_E00CEDDEB75363F7,IDX_E00CEDDE37D3107CIDX_E00CEDDEB75363F76114731950インデックス条件を使用し、場所を使用します リーリー (id, start_at)(id, end_at)SELECT @@optimizer_switch の出力は次のとおりです: ＃＃＃表面＃＃＃ ＃＃＃パーティション＃＃＃ ＃＃＃タイプ＃＃＃ ＃＃＃鍵＃＃＃ ＃＃＃参照する＃＃＃ ＃＃＃わかりました＃＃＃ ＃＃＃追加＃＃＃ ＃＃＃単純＃＃＃ ＃＃＃範囲＃＃＃ 両方の範囲条件を満たすインデックスを作成できないため、クエリを半分に分割してみました このクエリは大幅に高速に実行され、約 600 ミリ秒かかります。ただし、クエリの単純さと約 7,000 行が返されるという事実により、せいぜい 2 桁になると予想されます。 クエリがインデックス と を自動的に選択しない理由がわかりません。私には何が欠けているのでしょうか？ より良い結果を得るためにテーブルを分割できることはわかっていますが、削除できない外部キーがあるため、それは解決策ではありません。別のスキーマを検討して、外部キーなしで予約日を個別に保持するテーブルを用意し、予約テーブルをパーティション化できるように予約テーブルがそれを参照できるようにする必要がありますか?外部キーを使用して、サブスクリプション時にパーティション分割されたサブスクリプション テーブルを参照することはできますか? Mysql エンジンは AWS で次のバージョンで実行されています: 8.0.mysql_aurora.3.02.2 index_merge=on、index_merge_union=on、index_merge_sort_union=on、index_merge_intersection=on、engine_condition_pushdown=on、index_condition_pushdown=on、mrr=on、mrr_cost_based=on、block_nested_loop=on、batched_key_access=off、materialization=on、 semijoin=on、loosescan=on、firstmatch=on、duplicateweedout=on、subquery_materialization_cost_based=on、use_index_extensions=on、condition_fanout_filter=on、provided_merge=on、use_invisible_indexes=off、skip_scan=on、hash_join=on、subquery_to_derivative=off、prefer_ordering_index=オン、ハイパーグラフオプティマイザー=オフ、derivative_condition_pushdown=on P粉787806024408日前538 全員に返信(1)返信します 検索する固定の id がないため、インデックス (id, start_at) は選択されません。 ユースケースに応じて、start_at に 1 つのインデックスを作成し、end_at に別のインデックスを作成することができます。その後、単純なクエリ SELECT * from Booking b where b.start_at < '2021-01-01' and b.end_at > '2021-01-01'; がすぐに有効になります。検索条件に応じて、MySQL は MERGE INDEX 最適化オペレーションを使用する場合があります。インデックスまたはその両方。 単一のインデックスを使用する場合、インデックスは定義された順序と同じ順序で使用されるため、フィールドの順序を慎重に選択する必要があります。 編集: OP 編集後の私の感想は次のとおりです。 これは物事を非常によく説明しています。 SELECT * を実行すると、MySQL はテーブル全体を強制的に読み取ることになります。 id のみを選択してみてください。この場合、インデックスでカバーされるクエリになるため、インデックスが使用される可能性が高くなります。 キャンセル返事 公開 ホームページ コース に質問 私の ホームページ コース 記事 に質問 辞書 マニュアル ダウンロード 特集 ホームページ 記事 に質問 コース 特集 ダウンロード ゲーム 辞書 简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）

Answer 1

検索する固定の id がないため、インデックス (id, start_at) は選択されません。

ユースケースに応じて、start_at に 1 つのインデックスを作成し、end_at に別のインデックスを作成することができます。その後、単純なクエリ SELECT * from Booking b where b.start_at < '2021-01-01' and b.end_at > '2021-01-01'; がすぐに有効になります。検索条件に応じて、MySQL は MERGE INDEX 最適化オペレーションを使用する場合があります。インデックスまたはその両方。

単一のインデックスを使用する場合、インデックスは定義された順序と同じ順序で使用されるため、フィールドの順序を慎重に選択する必要があります。

編集: OP 編集後の私の感想は次のとおりです。

これは物事を非常によく説明しています。 SELECT * を実行すると、MySQL はテーブル全体を強制的に読み取ることになります。 id のみを選択してみてください。この場合、インデックスでカバーされるクエリになるため、インデックスが使用される可能性が高くなります。