MySQLで適切なインデックスを選択する方法

MySQLで適切なインデックスを選択する方法

まずは栗を見てみましょう

EXPLAIN select * from employees where name > 'a'; 

名前インデックスを使用してデータを検索する場合は、名前フィールドのジョイント インデックス ツリーをトラバースし、トラバースした主キー値を使用して主キー インデックス ツリーの最終データを検索する必要があります。コストは、テーブル全体をスキャンするよりも高くなります。

カバーリング インデックスの最適化を使用すると、名前フィールドの結合インデックス ツリーをトラバースするだけですべての結果を取得できます。

EXPLAIN 名前、年齢、職位を従業員から選択します。名前 > 'a'; 

選択されたフィールドがインデックスをカバーしており、MySQL が最下層でインデックス最適化を使用していることがわかります。別のケースを見てみましょう:

EXPLAIN select * from employees where name > 'zzz'; 

上記の name>'a' と name>'zzz' の 2 つの実行結果については、MySQL が最終的にインデックスを使用するか、テーブルに複数のインデックスが含まれているか、MySQL が最終的にどのようにインデックスを選択するかは、トレース ツールを使用して確認できます。トレース ツールをオンにすると、MySQL のパフォーマンスに影響するため、SQL の使用状況を一時的に分析するためにのみ使用でき、使用後はすぐに閉じる必要があります。

SET SESSION optimizer_trace="enabled=on",end_markers_in_json=on; -- トレースを有効にする
SELECT * FROM employees WHERE name > 'a' ORDER BY position;
information_schema.OPTIMIZER_TRACE から * を選択します。

トレースフィールドを確認します。

{
 「ステップ」: [
 {
  "join_preparation": { --第1段階: SQL準備段階 "select#": 1,
  「ステップ」: [
   {
   "expanded_query": "/* #1 を選択 */ `employees`.`id` AS `id`、`employees`.`name` AS `name`、`employees`.`age` AS `age`、`employees`.`position` AS `position`、`employees`.`hire_time` AS `hire_time` を `employees` から選択し、(`employees`.`name` > 'a') を `employees`.`position` で並べ替えます"
   }
  ] /* 手順 */
  } /* 参加準備 */
 },
 {
  "join_optimization": { --第2段階: SQL最適化段階 "select#": 1,
  「ステップ」: [
   {
   "condition_processing": { --条件処理 "condition": "WHERE",
    "original_condition": "(`employees`.`name` > 'a')",
    「ステップ」: [
    {
     「変換」:「等価性伝播」、
     "resulting_condition": "(`employees`.`name` > 'a')"
    },
    {
     "変換": "定数伝播",
     "resulting_condition": "(`employees`.`name` > 'a')"
    },
    {
     「変換」: 「trivial_condition_removal」、
     "resulting_condition": "(`employees`.`name` > 'a')"
    }
    ] /* 手順 */
   } /* 条件処理 */
   },
   {
   "table_dependencies": [ -- テーブル依存関係の詳細 {
    "テーブル": "`従業員`",
    "row_may_be_null": false、
    "マップビット": 0,
    "マップビットに依存する": [
    ] /* マップビットに依存する */
    }
   ] /* テーブル依存関係 */
   },
   {
   "ref_optimizer_key_uses": [
   ] /* ref_optimizer_key_uses */
   },
   {
   "rows_estimation": [ --推定ターゲットアクセスコスト {
    "テーブル": "`従業員`",
    "範囲分析": {
     "table_scan": { --フルテーブルスキャン "rows": 3, --スキャンされた行数 "cost": 3.7 --クエリコスト} /* table_scan */,
     "potential_range_indices": [ -- 使用される可能性のあるインデックスを照会します {
      "index": "PRIMARY", --主キーインデックス "usable": false,
      「原因」: 「該当なし」
     },
     {
      "index": "idx_name_age_position", -- 補助インデックス "usable": true,
      "キーパーツ": [
      "名前"、
      "年"、
      "位置"、
      「ID」
      ] /* キーパーツ */
     },
     {
      "インデックス": "idx_age",
      「使用可能」: false、
      「原因」: 「該当なし」
     }
     ] /* 潜在的範囲インデックス */,
     "設定範囲条件": [
     ] /* 設定範囲条件 */,
     "グループインデックス範囲": {
     「選択」:偽、
     「原因」: 「グループ化または区別されない」
     } /* グループインデックス範囲 */,
     "analyzing_range_alternatives": { ‐‐各インデックスの使用コストを分析する "range_scan_alternatives": [
      {
      "インデックス": "idx_name_age_position",
      「範囲」: [
       「a < 名前」
      ] /* 範囲 */,
      "index_dives_for_eq_ranges": true、
      "rowid_ordered": 偽、
      "using_mrr": 偽、
      "index_only": false, - カバーインデックスを使用するかどうか "rows": 3, --- インデックスをスキャンする行数 "cost": 4.61, -- インデックスの使用コスト "chosen": false, - このインデックスを選択するかどうか "cause": "cost"
      }
     ] /* 範囲スキャンの代替 */,
     「行順序の交差を分析する」: {
      「使用可能」: false、
      「原因」: 「行順序スキャンが少なすぎる」
     } /* 行順序の交差を分析する */
     } /* 範囲の代替案を分析する */
    } /* 範囲分析 */
    }
   ] /* 行数推定 */
   },
   {
   「考慮された実行計画」: [
    {
    「プランプレフィックス」: [
    ] /* プランプレフィックス */,
    "テーブル": "`従業員`",
    「最適なアクセスパス」: {
     「考慮されたアクセスパス」: [
     {
      "アクセスタイプ": "スキャン",
      「行」: 3,
      「コスト」: 1.6,
      「選択」:真、
      "use_tmp_table": 真
     }
     ] /* 考慮されるアクセスパス */
    } /* 最適なアクセスパス */,
    「プランのコスト」: 1.6,
    "計画の行数": 3,
    "ソートコスト": 3,
    「新しいプランのコスト」: 4.6,
    「選択済み」: true
    }
   ] /* 考慮された実行計画 */
   },
   {
   "テーブルへの条件の添付": {
    "original_condition": "(`employees`.`name` > 'a')",
    「添付条件の計算」: [
    ] /* 添付条件の計算 */,
    「添付条件の概要」: [
    {
     "テーブル": "`従業員`",
     "添付": "(`employees`.`name` > 'a')"
    }
    ] /* 添付条件の概要 */
   } /* テーブルに条件を添付する */
   },
   {
   「句処理」: {
    "句": "ORDER BY",
    "original_clause": "`従業員`.`職位`",
    「アイテム」: [
    {
     "item": "`従業員`.`職位`"
    }
    ] /* 項目 */,
    "resulting_clause_is_simple": true、
    "resulting_clause": "`従業員`.`職位`"
   } /* 句処理 */
   },
   {
   「計画を絞り込む」: [
    {
    "テーブル": "`従業員`",
    "アクセスタイプ": "テーブルスキャン"
    }
   ] /* リファインプラン */
   },
   {
   「インデックス順序のアクセスパスの再検討」: {
    "句": "ORDER BY",
    「インデックス注文サマリー」: {
    "テーブル": "`従業員`",
    "index_provides_order": false、
    "order_direction": "未定義",
    "インデックス": "不明",
    「計画変更」: false
    } /* インデックス順序サマリー */
   } /* インデックス順序のアクセスパスの再検討 */
   }
  ] /* 手順 */
  } /* 結合最適化 */
 },
 {
  "join_execution": { --フェーズ3: SQL実行フェーズ "select#": 1,
  「ステップ」: [
   {
   「ファイルソート情報」: [
    {
    "方向": "昇順",
    "テーブル": "`従業員`",
    "フィールド": "位置"
    }
   ] /* ファイルソート情報 */,
   「ファイルソート優先度キュー最適化」: {
    「使用可能」: false、
    「原因」:「該当なし(制限なし)」
   } /* ファイルソート優先度キュー最適化 */,
   "ファイルソート実行": [
   ] /* ファイルソート実行 */,
   「ファイルソートサマリー」: {
    「行」: 3,
    "検査された行": 3,
    "tmp ファイルの数": 0,
    "ソートバッファサイズ": 200704,
    "sort_mode": "<ソートキー、追加フィールド>"
   } /* ファイルソートサマリー */
   }
  ] /* 手順 */
  } /* 結合実行 */
 }
 ] /* 手順 */
}

フルテーブルスキャンのコストはインデックススキャンよりも低いため、MySQL は最終的にフルテーブルスキャンを選択します。

SELECT * FROM employees WHERE name > 'zzz' ORDER BY position;
information_schema.OPTIMIZER_TRACE から * を選択します。

{
 「ステップ」: [
 {
  「参加準備」: {
  「選択番号」: 1,
  「ステップ」: [
   {
   "expanded_query": "/* #1 を選択 */ `employees`.`id` AS `id`、`employees`.`name` AS `name`、`employees`.`age` AS `age`、`employees`.`position` AS `position`、`employees`.`hire_time` AS `hire_time` を `employees` から選択し、(`employees`.`name` > 'zzz') を `employees`.`position` で並べ替えます"
   }
  ] /* 手順 */
  } /* 参加準備 */
 },
 {
  「結合最適化」: {
  「選択番号」: 1,
  「ステップ」: [
   {
   「条件処理」: {
    「条件」: 「WHERE」、
    "original_condition": "(`employees`.`name` > 'zzz')",
    「ステップ」: [
    {
     「変換」:「等価性伝播」、
     "resulting_condition": "(`employees`.`name` > 'zzz')"
    },
    {
     "変換": "定数伝播",
     "resulting_condition": "(`employees`.`name` > 'zzz')"
    },
    {
     「変換」: 「trivial_condition_removal」、
     "resulting_condition": "(`employees`.`name` > 'zzz')"
    }
    ] /* 手順 */
   } /* 条件処理 */
   },
   {
   "テーブル依存関係": [
    {
    "テーブル": "`従業員`",
    "row_may_be_null": false、
    "マップビット": 0,
    "マップビットに依存する": [
    ] /* マップビットに依存する */
    }
   ] /* テーブル依存関係 */
   },
   {
   "ref_optimizer_key_uses": [
   ] /* ref_optimizer_key_uses */
   },
   {
   "行推定": [
    {
    "テーブル": "`従業員`",
    "範囲分析": {
     "テーブルスキャン": {
     「行」: 3,
     「コスト」: 3.7
     } /* テーブルスキャン */,
     「潜在的範囲指標」: [
     {
      "インデックス": "プライマリ",
      「使用可能」: false、
      「原因」: 「該当なし」
     },
     {
      "インデックス": "idx_name_age_position",
      「使用可能」:true、
      "キーパーツ": [
      "名前"、
      "年"、
      "位置"、
      「ID」
      ] /* キーパーツ */
     },
     {
      "インデックス": "idx_age",
      「使用可能」: false、
      「原因」: 「該当なし」
     }
     ] /* 潜在的範囲インデックス */,
     "設定範囲条件": [
     ] /* 設定範囲条件 */,
     "グループインデックス範囲": {
     「選択」:偽、
     「原因」: 「グループ化または区別されない」
     } /* グループインデックス範囲 */,
     「範囲の代替案を分析する」: {
     "範囲スキャンの代替": [
      {
      "インデックス": "idx_name_age_position",
      「範囲」: [
       「zzz < 名前」
      ] /* 範囲 */,
      "index_dives_for_eq_ranges": true、
      "rowid_ordered": 偽、
      "using_mrr": 偽、
      "index_only": 偽、
      「行」: 1,
      「コスト」: 2.21,
      「選択済み」: true
      }
     ] /* 範囲スキャンの代替 */,
     「行順序の交差を分析する」: {
      「使用可能」: false、
      「原因」: 「行順序スキャンが少なすぎる」
     } /* 行順序の交差を分析する */
     } /* 範囲の代替案を分析する */,
     「選択された範囲のアクセスの概要」: {
     「範囲アクセスプラン」: {
      "タイプ": "範囲スキャン",
      "インデックス": "idx_name_age_position",
      「行」: 1,
      「範囲」: [
      「zzz < 名前」
      ] /* 範囲 */
     } /* 範囲アクセスプラン */,
     "計画の行数": 1,
     「プランのコスト」: 2.21,
     「選択済み」: true
     } /* 選択された範囲のアクセス要約 */
    } /* 範囲分析 */
    }
   ] /* 行数推定 */
   },
   {
   「考慮された実行計画」: [
    {
    「プランプレフィックス」: [
    ] /* プランプレフィックス */,
    "テーブル": "`従業員`",
    「最適なアクセスパス」: {
     「考慮されたアクセスパス」: [
     {
      "アクセスタイプ": "範囲",
      「行」: 1,
      「コスト」: 2.41,
      「選択」:真、
      "use_tmp_table": 真
     }
     ] /* 考慮されるアクセスパス */
    } /* 最適なアクセスパス */,
    「プランのコスト」: 2.41,
    "計画の行数": 1,
    "ソートコスト": 1,
    「新しいプランのコスト」: 3.41,
    「選択済み」: true
    }
   ] /* 考慮された実行計画 */
   },
   {
   "テーブルへの条件の添付": {
    "original_condition": "(`employees`.`name` > 'zzz')",
    「添付条件の計算」: [
    ] /* 添付条件の計算 */,
    「添付条件の概要」: [
    {
     "テーブル": "`従業員`",
     "添付": "(`employees`.`name` > 'zzz')"
    }
    ] /* 添付条件の概要 */
   } /* テーブルに条件を添付する */
   },
   {
   「句処理」: {
    "句": "ORDER BY",
    "original_clause": "`従業員`.`職位`",
    「アイテム」: [
    {
     "item": "`従業員`.`職位`"
    }
    ] /* 項目 */,
    "resulting_clause_is_simple": true、
    "resulting_clause": "`従業員`.`職位`"
   } /* 句処理 */
   },
   {
   「計画を絞り込む」: [
    {
    "テーブル": "`従業員`",
    "pushed_index_condition": "(`employees`.`name` > 'zzz')",
    "table_condition_attached": null、
    "アクセスタイプ": "範囲"
    }
   ] /* リファインプラン */
   },
   {
   「インデックス順序のアクセスパスの再検討」: {
    "句": "ORDER BY",
    「インデックス注文サマリー」: {
    "テーブル": "`従業員`",
    "index_provides_order": false、
    "order_direction": "未定義",
    "インデックス": "idx_name_age_position",
    「計画変更」: false
    } /* インデックス順序サマリー */
   } /* インデックス順序のアクセスパスの再検討 */
   }
  ] /* 手順 */
  } /* 結合最適化 */
 },
 {
  "参加実行": {
  「選択番号」: 1,
  「ステップ」: [
   {
   「ファイルソート情報」: [
    {
    "方向": "昇順",
    "テーブル": "`従業員`",
    "フィールド": "位置"
    }
   ] /* ファイルソート情報 */,
   「ファイルソート優先度キュー最適化」: {
    「使用可能」: false、
    「原因」:「該当なし(制限なし)」
   } /* ファイルソート優先度キュー最適化 */,
   "ファイルソート実行": [
   ] /* ファイルソート実行 */,
   「ファイルソートサマリー」: {
    「行」: 0,
    "検査された行": 0,
    "tmp ファイルの数": 0,
    "ソートバッファサイズ": 200704,
    "sort_mode": "<ソートキー、追加フィールド>"
   } /* ファイルソートサマリー */
   }
  ] /* 手順 */
  } /* 結合実行 */
 }
 ] /* 手順 */
}

トレース フィールドを見ると、インデックス スキャンのコストが完全なテーブル スキャンのコストよりも低いため、MySQL は最終的にインデックス スキャンを選択することがわかります。

SET SESSION optimizer_trace="enabled=off"; -- トレースを無効にする

要約する

上記は、MySQL に適したインデックスを選択する方法についての紹介です。お役に立てれば幸いです。ご質問がある場合は、メッセージを残していただければ、すぐに返信いたします。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。この記事が役に立ったと思われた方は、ぜひ転載していただき、出典を明記してください。ありがとうございます!

以下もご興味があるかもしれません:
  • MySQL データベース インデックスが B+ ツリーの使用を選択するのはなぜですか?
  • MySQL オプティマイザのインデックスと JOIN 順序の選択の調査
  • mysql はインデックスを無効にしますか?
  • MySQL の結合インデックスと左端一致原則の詳細な説明
  • MySQLにインデックスを追加する方法
  • MySQL inndbジョイントインデックスを正しく使用する方法を徹底的に理解するためのケーススタディ
  • MySQL でデータベース インデックスが失敗する状況の詳細な分析
  • MySQLジョイントインデックスの左端一致原則の詳細な分析
  • MySQL インデックスの正しい使い方とインデックスの原理の詳細な説明
  • MySQL インデックスの詳細な説明
  • MySQL インデックスの使用方法 (単一列インデックスと複数列インデックス)

<<:  JSはプログレスバーのスムーズバージョンの詳細な計画を実装します

>>:  Linux ファイアウォールを設定してポート 80 と 3306 を開く方法

推薦する

シンプルなCSSアニメーションのtransition属性の詳しい説明

1. 遷移属性の理解1. transition 属性は、次の 4 つの遷移プロパティを設定するために...

Nginx プロキシ axios リクエストと注意事項

序文最近、小さなデモを作成しました。大規模な工場のオンライン データを使用したため、インターフェイス...

Windows 10 で MySQL を完全にアンインストールして再インストールするための詳細な手順

さまざまな理由で、誰もが MySQL を再インストールする必要があると思います。 MySQL と Q...

Mac で MySQL バージョン 5.6 のパスワードを設定する方法

MySQLはインストール時に設定できますが、それより低いバージョンは設定できないようで、インストール...

CSS 表示属性のインラインブロックレイアウト実装の詳細な説明

CSS 表示プロパティ注: !DOCTYPE が指定されている場合、Internet Explore...

Linux ps および pstree コマンドの知識ポイントのまとめ

Linux の ps コマンドは Process Status の略です。 ps コマンドは、システ...

LinuxのCentos7でmysql5.7.29を構築する詳細なプロセス

1. MySQLをダウンロードする1.1 ダウンロードアドレスhttps://downloads.m...

Linux tac コマンドの実装例

1. コマンドの紹介tac (cat の逆順) コマンドは、ファイルの内容を行単位で逆順に出力します...

vue3.0プロジェクトアーキテクチャを段階的に構築する方法を教えます

目次序文: 1. vue-cliでプロジェクトを作成する2. ルーターをインストールする3. ディレ...

Windows システムに VirtualBox と Ubuntu 16.04 をインストールするための詳細なチュートリアル

1. ソフトウェアの紹介バーチャルボックスVirtualBox は、無料のオープンソース仮想マシン ...

CSS3 で背景ぼかしを実現する 3 つの方法 (要約)

1. 通常の背景ぼかしコード: <スタイル> html, 体 { 幅: 100%; 高...

負のz-indexを持つ要素がクリックできない問題の解決策

最近、ポップアップ広告に取り組んでいました。デフォルト ページには z-index が設定されていな...

MySQL で特殊文字を含むデータベース名を作成する方法の例

序文この記事では、MySQL で特殊文字を使用してデータベース名を作成する方法について説明します。こ...

フロントエンド開発に必須:推奨されるブラウザ互換性テストツール 12 選

フロントエンド開発者にとって、さまざまな主要ブラウザのさまざまなバージョンでコードが適切に動作するこ...