MySQL テーブルを返すとインデックスが無効になるケースの説明

導入

MySQL InnoDB エンジンがレコードをクエリし、インデックス カバレッジを使用できない場合は、テーブル リターン操作を実行してレコードの必要なフィールドを取得する必要があります。

MySQL は SQL を実行する前に、SQL の最適化、インデックスの選択などの操作を実行します。MySQL は、各インデックスに必要なクエリ コストと、インデックスを使用しない場合に必要なクエリ コストを見積もり、SQL クエリ操作を実行するためのコストが最も低いと MySQL が判断する方法を選択します。テーブル内のデータ量が多い場合、MySQL はテーブルを返す操作のクエリ コストが高すぎると推定することが多く、その結果、インデックスが不適切に使用されます。

場合

次の例では、MySQL バージョン 5.6、1CPU、2G メモリの Linux 環境でテスト テーブルを作成し、テスト用に約 200 万件のレコードを作成します。

テーブル `salary_static` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
  `school_id` int(11) NOT NULL COMMENT '学校ID',
  `student_id` int(11) NOT NULL COMMENT '卒業生ID',
  `salary` int(11) NOT NULL DEFAULT '0' COMMENT '卒業時の給与',
  `year` int(11) NOT NULL COMMENT '卒業年',
  主キー (`id`)、
  キー `school_id_key` (`school_id`) BTREE 使用、
  キー `year_school_key` (`year`,`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='卒業生の給与統計';

区切り文字 //
プロシージャ init_salary_static() を作成する 
始める 
	年を宣言します INT;
	schid INT を宣言します。
	stuid INT を宣言します。
	年を 2000 に設定します。
	2020年未満の場合
		トランザクションを開始します。 
		schid = 1 を設定します。
		schid < 100の場合
			SET stuid = 1;
			勉強中 < 1000 DO
				salary_static(school_id,student_id,salary,year) に値 (schid,stuid,floor(rand()*10000),year) を挿入します。
				SET stuid = stuid + 1;
			終了しながら;
			schid = schid + 1 を設定します。
		終了しながら;
		SET 年 = 年 + 1;
		専念; 
	終了しながら;
終わり //
区切り文字 ;
init_salary_static() を呼び出します。

テスト データが作成されたら、統計クエリ用に次の SQL ステートメントを実行します。

salary_static から school_id、avg(salary) を選択します。year は 2016 年から 2019 年までで、group by school_id です。

SQL では、クエリに year_school_key インデックスを使用する必要があると予想されます。ただし、explain コマンドを実行すると、SQL が school_id_key インデックスを使用していることがわかります。また、間違ったインデックスが使用されているため、SQL は完全なテーブル スキャンを実行し、クエリ時間は 7 秒になります。

クエリに year_school_key インデックスの使用を強制すると、SQL のクエリ時間が 0.6 秒に大幅に短縮され、school_id_key インデックスの時間よりも 10 倍短くなることがわかりました。

salary_static force index(year_school_key) から school_id、avg(salary) を選択します。year は 2015 年から 2019 年までで、school_id でグループ化します。

分析する

MySQL のオプティマイザー トレース (MySQL 5.6 でサポート) を使用して、SQL 実行プランを分析します。

optimizer_trace を「有効 = オン」に設定します。
salary_static から school_id、avg(salary) を選択します。year は 2016 年から 2019 年までで、group by school_id です。
INFORMATION_SCHEMA.OPTIMIZER_TRACE から * を選択します。

出力結果は JSON ファイルで、MySQL での SQL 最適化プロセスとインデックス選択プロセスの実行プランが表示されます。

実行プランの json 内の range_analysis の下のコンテンツに注目してください。これは、where range クエリ プロセス中のインデックス選択を示しています。 table_scan はフル テーブル スキャンを示しており、1,973,546 レコードをスキャンする必要があると推定されます。ただし、フル テーブル スキャンではクラスター化インデックスが使用され、シーケンシャル IO 読み取りであるため、各レコードのクエリ コストは非常に小さく、最終的に計算されたクエリ コストは 399,741 です。 range_scan_alternatives は、インデックスを使用した範囲クエリを示します。year_school_key インデックスは、812,174 レコードをスキャンする必要があると推定します。ただし、テーブルに戻る必要があるため、ランダム IO 読み取りが必要になります。最終的に計算されたクエリ コストは 974,610 です。したがって、where クエリ プロセスでは、最終的にインデックスではなく完全なテーブル スキャンが選択されます。

"範囲分析": {
  "テーブルスキャン": {
	「行数」: 1973546,
	「コスト」: 399741
  },
  「潜在的範囲指標」: [
	{
	  "インデックス": "プライマリ",
	  「使用可能」: false、
	  「原因」: 「該当なし」
	},
	{
	  "インデックス": "学校IDキー",
	  「使用可能」：true、
	  "キーパーツ": [
		「学校ID」、
		「ID」
	  ]
	},
	{
	  "インデックス": "年_学校_キー",
	  「使用可能」：true、
	  "キーパーツ": [
		"年"、
		「学校ID」、
		「ID」
	  ]
	}
  ]、
  "設定範囲条件": [
  ]、
  "グループインデックス範囲": {
	「選択」：偽、
	「原因」: 「集計関数が適用できない」
  },
  「範囲の代替案を分析する」: {
	"範囲スキャンの代替": [
	  {
		"インデックス": "年_学校_キー",
		「範囲」: [
		  「2016 <= 年 <= 2019」
		]、
		"index_dives_for_eq_ranges": true、
		"rowid_ordered": 偽、
		"using_mrr": 偽、
		"index_only": 偽、
		「行数」: 812174,
		「コスト」: 974610,
		「選択」：偽、
		「原因」：「コスト」
	  }
	]、
	「行順序の交差を分析する」: {
	  「使用可能」: false、
	  「原因」: 「行順序スキャンが少なすぎる」
	}
  }
}

ここでのクエリ コスト値は手動で計算できます。コスト = I/O コスト (レコード ページの読み取りごとに 1 つのコスト、各コストは 1.0) + CPU コスト (レコードごとに 1 つのコスト、各コストは 0.2)。

フルテーブルスキャンクエリコスト

table_scan を使用してテーブル全体をスキャンする場合、1973546 レコードをスキャンする必要があると推定されます。show table status like "salary_static" コマンドを実行すると、テーブル内のレコードの合計数が 82411520 バイト (Data_length) であることが示されます。innodb 内の各レコード ページは 16 KB であるため、テーブル全体をスキャンするには 82411520/1024/16 = 5030 レコード ページを読み取る必要があります。

• I/Oコスト

5030 * 1.0 = 5030

• CPUコスト

1973546 * 0.2 = 394709.2

• 総クエリコスト

5030 + 394709.2 = 399739.2

インデックスクエリコスト

year_school_key のインデックスを作成するときに、812,174 件のレコードをスキャンする必要があると推定されます。このインデックスを使用するには、まずインデックスを介して rowId をクエリし、次に rowId を介してテーブルに戻る必要があります。 MySQLは各テーブルを返すには個別のI/Oコストが必要であるとみなします

• CPUコスト

812174 * 0.2 = 162434.8

• I/Oコスト

812174 * 1.0 = 812174

• 総クエリコスト

162434.8 + 812174 = 974608.8

次に、reconsidering_access_paths_for_index_ordering に注意してください。これは、最終的にソートが再度最適化されることを意味します。ここで school_id_key インデックスが選択され、上記の where 条件で選択された完全なテーブル スキャンを拒否します: "plan_changed": true。詳細については、group-by-optimization を参照してください。

{
    「インデックス順序のアクセスパスの再検討」: {
      "句": "GROUP BY",
      「インデックス注文サマリー」: {
        "テーブル": "`salary_static`",
        "index_provides_order": true、
        "order_direction": "asc",
        "インデックス": "学校IDキー",
        "プラン変更": true,
        "アクセスタイプ": "インデックススキャン"
      }
    }
}

実際、ソート インデックスの最適化にもバグがあります。詳細については、バグ #93845 を参照してください。

最適化

SQL 実行プロセスを分析すると、year_school_key インデックス バック テーブルにレコードが多すぎるために間違ったインデックスが選択され、推定クエリ コストが完全なテーブル スキャンよりも大きくなり、最終的に間違ったインデックスが選択されていることがわかります。

したがって、SQL の実行時間を短縮するための次の最適化ソリューションは、SQL のテーブル戻り操作を減らすこと、つまり SQL にインデックス カバレッジを実行させることです。 SQL ステートメントには、school_id、salary、year の 3 つのフィールドのみが含まれます。したがって、これら 3 つのインデックスの結合インデックスが作成され、結合インデックス内のこれら 3 つのフィールドの順序に注意が払われます。where フィルター ステートメントが最初に実行されるため、year フィールドは結合インデックスの 1 番目になります。group by ステートメントは、基本的に order by ステートメントと同じであるため、where ステートメントの後、つまり結合インデックスの 2 番目の場所に配置されます。salary は、テーブルの戻り値を減らすためだけに、結合インデックスの最後に配置されます。

salary_static (year、school_id、salary) に year_school_salary_key インデックスを作成します。

結合インデックスを作成した後、SQL ステートメントを実行した結果は次のとおりです。クエリの完了にはわずか 0.2 秒しかかかりませんでした。これは、school_id_key インデックスの時間よりも 35 倍短い時間です。

収益率の計算

上記の問題は、一度に実行される SQL クエリが多すぎるため、テーブルに戻るコストが高くなりすぎることです。実際、上記の現象の臨界値は次のように計算できます。

レコード行のサイズがaバイト、テーブル内のレコード数がb、重要なレコード数がcであると仮定すると、テーブル内のレコードページ数はb*a/1024/16になります。

フルテーブルスキャンのクエリコスト = I/O コスト + CPU コスト = b*a/1024/16 * 1.0 + b * 0.2


インデックススキャンのクエリコスト = I/O コスト + CPU コスト = c * 1.0 + c * 0.2 = c * 1.2


b*a/1024/16 * 1.0 + b * 0.2 = c * 1.2
クリティカル比 = c/b 
= (a/1024/16 + 0.2)/1.2
= a * 5E-5 + 0.1667

つまり、SQL クエリがテーブル内のレコードの約 17% を超え、カバー インデックスを使用できない場合、テーブルにインデックスを返すコストが高すぎるため、完全なテーブル スキャンが選択されます。また、1 行のバイト サイズが増加すると、この比率はわずかに増加します。

これで、MySQL テーブル リターンによるインデックス無効化のケースに関するこの記事は終了です。MySQL テーブル リターンによるインデックス無効化に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。