MySQL インデックス オプティマイザーの仕組みについての深い理解

この記事は、mysql に関する関連知識を提供します。主に、MySQL サーバーの構成、MySQL オプティマイザーによるインデックス選択の原理、SQL コスト分析など、インデックス オプティマイザーの動作原理に関する関連コンテンツを紹介します。 、最後に選択クエリによるクエリのプロセス全体をまとめましたので、一緒に見ていきましょう。皆さんのお役に立てれば幸いです。

#推奨学習:

mysql ビデオ チュートリアル

##1. MySQL オプティマイザーによるインデックスの選択方法

このテーブルを見てみましょう。SUB_ODR_ID フィールドは 2 つの関連インデックスを作成しました。前に学習した内容に基づいて、PRIMARY KEY (

ID

) 自動インクリメント主キー インデックス (##) を作成します。 #LOG_ID, SUB_ODR_ID) がジョイントインデックスとユニークインデックスとして設定され、CREATE_TIME と UPDATE_TIME にそれぞれ 2 つのインデックスが設定されます。

CREATE TABLE `***`  (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `LOG_ID` varchar(32) NOT NULL COMMENT '交易流水号',
  `ODR_ID` varchar(32) NOT NULL COMMENT '父单号',
  `SUB_ODR_ID` varchar(32) NOT NULL COMMENT '子单号',
  `CREATE_TIME` datetime(0) NOT NULL COMMENT '创建时间',
  `CREATE_BY` varchar(32) NOT NULL COMMENT ' 创建人',
  `UPDATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  `UPDATE_BY` varchar(32) NOT NULL COMMENT '更新人',
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE INDEX `UNQ_LOG_SUBODR_ID`(`LOG_ID`, `SUB_ODR_ID`) USING BTREE,
  INDEX `IDX_ODR_ID`(`ODR_ID`) USING BTREE,
  INDEX `IDX_SUB_ID`(`SUB_ODR_ID`) USING BTREE,
  INDEX `IDX_CREATE_TIME`(`CREATE_TIME`) USING BTREE,
  INDEX `IDX_UPDATE_TIME`(`UPDATE_TIME`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 SET = utf8 COLLATE = utf8_general_ci COMMENT = '分摊业务明细表' ROW_FORMAT = Dynamic;

クエリ フィールド SUB_ODR_ID では、理論的には UNQ_LOG_SUBODR_ID、IDX_SUB_ID の 3 つの関連インデックスを使用できます。MySQL オプティマイザはこれら 3 つのインデックスからどのように選択するのでしょうか? リレーショナル データベースでは、B ツリーはストレージに使用される単なるデータ構造です。

使用方法はデータベースのオプティマイザに依存します。オプティマイザは、実行プランと呼ばれる特定のインデックスの選択を決定します。オプティマイザーの選択はコストに基づいて行われ、コストが低いほど優先インデックスは高くなります。

1. MySQL データベースの構成

MySQL データベースは Server (サーバー) 層と Engine (エンジン) 層で構成されます。

Serve レイヤーには、SQL アナライザー、SQL オプティマイザー、および SQL エグゼキューターがあり、SQL ステートメントの特定の実行プロセスを担当します。

エンジン レイヤーは、最も一般的に使用される InnoDB ストレージ エンジンや、一時的な結果セットをメモリに保存するために使用される TempTable エンジンなど、特定のデータを保存する役割を果たします。

SQL オプティマイザーは、考えられるすべての実行計画を分析し、最もコストの低い実行を選択します。このオプティマイザーは CBO (コストベースのオプティマイザー) と呼ばれます。

#2. MySQL データベースのコスト計算

MySQL では、SQL ステートメントの計算コストがわかりやすくなっています。つまり、データベース (データベース ページ、ディスク) にアクセスしてデータを処理します。

CPUコスト。インデックスキー値の比較、レコード値の比較、結果セットのソートなどの計算コストを表します。これらの操作はサーバー層

IO コストで完了します。これは、エンジン レベルの IO のコストを表します。MySQL 8.0 は、テーブル データがメモリ内にあるかどうかを区別することにより、メモリ IO とディスク IO の読み取りコストを個別に計算できます。 。

Cost  = Server Cost + Engine Cost  = CPU Cost + IO Cost

MySQL オプティマイザは、SQL の一部でディスク ベースの一時テーブルを作成する必要がある場合、この時点のコストが最大となり、メモリ ベースの一時テーブルの 20 倍になると考えます。インデックス キーの値とレコードを比較するコストは非常に低いですが、比較するレコードが多数ある場合、コストが非常に高くなる可能性があります。

MySQL オプティマイザは、ディスクからの読み取りコストがメモリのコストの 4 倍であると考えています (コストは静的ではなく、ハードウェアによって異なります)。

2. MySQL クエリのコスト

各コストの値と MySQL オプティマイザーの動作原理を確認する 以下の SQL ステートメントを実行し、実行プロセスを分析します。 MySQL インデックス選択は SQL 実行コストに基づきます

EXPLAIN FORMAT=json 
select * from test.fork_business_detail f where f.sub_odr_id = ''

read_cost は InnoDB ストレージ エンジンからの読み取りコストを表します;

eval_cost はサーバー層の CPU コストを表します;

prefix_cost は SQL の合計コストを表し、

data_read_per_join は読み取りレコードの合計バイト数を表します。

{
	"query_block": {
		"cost_info": {
			"query_cost": "1.20"
		},
		"table": {
			"access_type": "ref",
			"possible_keys": [
				"IDX_SUB_ID"
			],
			"key": "IDX_SUB_ID",
			"used_key_parts": [
				"SUB_ODR_ID"
			],
			"key_length": "98",
			"ref": [
				"const"
			],
			"cost_info": {
				"read_cost": "1.00",
				"eval_cost": "0.20",
				"prefix_cost": "1.20",
				"data_read_per_join": "1K"
			},
			"used_columns": [
				"ID",
				"LOG_ID",
				"ODR_ID",
				"SUB_ODR_ID",
				"CREATE_TIME",
				"CREATE_BY",
				"UPDATE_TIME",
				"UPDATE_BY"
			]
		}
	}
}

3. SELECT 実行プロセス

MySQL クエリのパフォーマンスを向上させるにはどうすればよいですか?まず、クエリ オプティマイザーによる SQL 処理のプロセス全体を理解する必要があります。次の図に示すように、SELECT SQL の実行プロセスを例に挙げます:

#クライアントはサーバーに SELECT クエリを送信し、サーバーは最初に SELECT クエリをチェックします。クエリキャッシュ。キャッシュがヒットすると、キャッシュに保存されている結果がすぐに返されます。それ以外の場合は、次の段階に進みます。

サーバーは SQL の解析と前処理を実行し、クエリ オプティマイザーは対応する実行プランを生成します。MySQL はストレージ エンジンの API を呼び出し、生成された実行プランに基づいてクエリを実行します。オプティマイザ。結果はクライアントに返され、同時にクエリ キャッシュに入れられます。

推奨学習:

mysql ビデオ チュートリアル

以上がMySQL インデックス オプティマイザーの仕組みについての深い理解の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。