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 ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。この記事が役に立ったと思われた方は、ぜひ転載していただき、出典を明記してください。ありがとうございます!

以下もご興味があるかもしれません: