【MySQL】単一テーブルのクエリ実行計画【index】

DB

emp表 ※indexはなし(primary keyのみ)

CREATE TABLE `emp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `dept` int DEFAULT NULL,
  `age` int DEFAULT NULL,
  `sex` int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bikou` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

レコードは100万件
※データの作成はこちらを参照

ageが35の条件でselectをしてみる。

平均が0.5 sec

ここでempテーブルのageにindexを作成。

create index idx_age on emp(age);
CREATE TABLE `emp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `dept` int DEFAULT NULL,
  `age` int DEFAULT NULL,
  `sex` int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bikou` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

再実行してみます

0.1 secと実行結果が速くなりました。

実行計画の結果を比較してみます。

indexなし

EXPLAINの結果の各カラムの意味

  • id: クエリの各部分に一意の識別子を割り当てます。このクエリは単一のSELECT文であるため、1です。
  • select_type: クエリのタイプを示します。SIMPLEは、サブクエリを含まない単純なSELECT文であることを示します。
  • table: クエリの対象となるテーブルを示します。ここではempテーブルです。
  • partitions: 使用されるパーティションを示します。ここではNULLで、パーティションは使用されていないことを示します。
  • type: テーブルへのアクセス方法を示します。ALLは全行スキャン（フルテーブルスキャン）が行われていることを示します。
  • possible_keys: クエリで使用可能なインデックスを示します。ここではNULLで、使用可能なインデックスがないことを示します。
  • key: 実際に使用されるインデックスを示します。ここではNULLで、インデックスが使用されていないことを示します。
  • key_len: 使用されているインデックスキーの長さを示します。ここではNULLで、インデックスが使用されていないことを示します。
  • ref: インデックスのカラムに一致する列を示します。ここではNULLで、インデックスが使用されていないことを示します。
  • rows: MySQLがクエリ実行時に検査すると推定する行の数を示します。ここでは985,251行です。
  • filtered: 条件を満たすと予想される行の割合を示します。ここでは10.00で、全行の10%が条件を満たすと予想されます。
  • Extra: クエリの追加情報を示します。Using whereは、WHERE句によるフィルタリングが行われていることを示します。

解釈

この結果から、次のことがわかります：

  1. 全行スキャン（フルテーブルスキャン）:
    • typeがALLであるため、MySQLはempテーブルの全行をスキャンしています。これは、適切なインデックスがない場合によく見られる状況です。
  2. インデックスの欠如:
    • possible_keysとkeyがNULLであるため、このクエリで使用可能なインデックスが存在せず、全行をチェックする必要があります。
  3. 行数の推定:
    • rowsが985,251で、これはMySQLがクエリ実行時にスキャンすると推定する行の数です。テーブルの全行をスキャンすることを意味します。
  4. フィルタリングの効率:
    • filteredが10.00で、これは全行のうち約10%の行がWHERE句の条件（age = 35）を満たすと予想されていることを示します。
  5. 追加のフィルタリング:
    • ExtraがUsing whereであるため、MySQLはスキャンした行に対してさらにフィルタリングを行い、age = 35の条件を適用しています。

indexあり

EXPLAINの結果の各カラムの意味

  • id: クエリの各部分に一意の識別子を割り当てます。このクエリは単一のSELECT文であるため、1です。
  • select_type: クエリのタイプを示します。SIMPLEは、サブクエリを含まない単純なSELECT文であることを示します。
  • table: クエリの対象となるテーブルを示します。ここではempテーブルです。
  • partitions: 使用されるパーティションを示します。ここではNULLで、パーティションは使用されていないことを示します。
  • type: テーブルへのアクセス方法を示します。refはインデックスによる参照が行われていることを示します。
  • possible_keys: クエリで使用可能なインデックスを示します。ここではidx_ageが表示されています。
  • key: 実際に使用されるインデックスを示します。ここではidx_ageが使用されています。
  • key_len: 使用されているインデックスキーの長さを示します。ここでは5で、ageカラムのインデックスの長さです。
  • ref: インデックスのカラムに一致する値を示します。ここではconstで、定数値（ここではage = 35）がインデックスに使用されています。
  • rows: MySQLがクエリ実行時に検査すると推定する行の数を示します。ここでは9,654行です。
  • filtered: 条件を満たすと予想される行の割合を示します。ここでは100.00で、インデックスによる検索結果すべてが条件を満たすことを示します。
  • Extra: クエリの追加情報を示します。Using indexは、クエリがインデックスカバーされている（インデックスだけでクエリが完了する）ことを示します。

解釈

この結果から、次のことがわかります：

  1. インデックスの使用:
    • typeがrefであるため、MySQLはidx_ageインデックスを使用して、age = 35の条件に一致する行を効率的に検索しています。
  2. 適切なインデックス:
    • possible_keysおよびkeyがidx_ageであるため、このインデックスがクエリに使用されています。適切なインデックスが存在し、使用されていることを示します。
  3. インデックスキーの長さ:
    • key_lenが5で、これはインデックスキーの長さを示しています。ageカラムのデータ型に基づいてこの長さが決定されます。
  4. 参照の種類:
    • refがconstで、定数値（ここではage = 35）がインデックスに使用されています。
  5. 行数の推定:
    • rowsが9,654で、これはMySQLがインデックスを使用して検索すると推定する行の数です。
  6. フィルタリングの効率:
    • filteredが100.00で、インデックスによる検索結果が条件を完全に満たしていることを示します。これは、インデックスが効率的に条件に合致する行を特定できていることを示します。
  7. 追加のフィルタリングの欠如:
    • ExtraがUsing indexで、クエリがインデックスカバーされていることを示します。インデックスだけでクエリが完了するため、テーブルの行にアクセスする必要がありません。これによりパフォーマンスが向上します。

まとめ

このEXPLAIN結果は、empテーブルのageカラムに対するインデックスidx_ageが効果的に使用されていることを示しています。このインデックスにより、MySQLはage = 35の条件に一致する行を効率的に検索し、全行スキャンを回避しています。その結果、クエリのパフォーマンスが向上し、より迅速な結果が得られます。

タイトルとURLをコピーしました