MySQLのインデックス選択と最適化の詳細な説明

MySQLのインデックス選択と最適化の詳細な説明

インデックスモデル

ハッシュテーブル

  • 等値クエリのみのシナリオに適用可能な、メモリエンジンのデフォルトインデックス
  • InnoDB は、介入できない適応ハッシュ インデックスをサポートします。エンジンが、それらを作成するかどうかを決定します。

順序付けられた配列: 等値クエリと範囲クエリの両方のシナリオでパフォーマンスは優れていますが、データの挿入と削除にはデータの移動が必要であり、コストがかかりすぎます。したがって、これは静的ストレージ エンジンにのみ適用されます。

二分バランス木: 各ノードの左の子は親ノードより小さく、親ノードは右の子より小さい。時間計算量は O(log(N)) です。

マルチブランチバランスツリー: インデックスはメモリに保存されるだけでなく、ディスクにも書き込まれます。クエリのディスク読み取りを最小限に抑えるには、クエリ プロセスがアクセスするデータ ブロックの数をできるだけ少なくする必要があります。したがって、「N 項」ツリーが使用されます。

B+ツリー

B ツリーと B+ ツリー

Bツリー

B+ツリー

InnoDB は B+ ツリー インデックス モデルを使用します。次に示すように、ID の主キー列、テーブル内のフィールド k、および k のインデックスを持つテーブルがあるとします。

  • 主キーインデックス: クラスター化インデックスとも呼ばれ、リーフノードはデータの行全体を保存します。
  • 非主キーインデックス: セカンダリインデックスとも呼ばれ、リーフノードの内容は主キーの値です。

予防

  • インデックスはデータ ページの整然とした保存に基づいているため、データ ページは分割されるか (ページ保存スペースが不足)、または結合される可能性があります (データの削除によりページ使用率が低下する)。
  • データの無秩序な挿入により、データの移動や、データ ページの分割が発生することがあります。
  • 主キーの長さが短いほど、共通インデックスのリーフ ノードが小さくなり、共通インデックスが占めるスペースも小さくなります。
  • インデックス フィールドが小さいほど、1 つのレイヤーに保存できるデータが多くなり、ディスク IO を削減できます。
// データページが 16KB、データ行が 1KB、インデックスポインタが 6 バイト、インデックスフィールドが bigint 型 (8 バイト) であると仮定します。

// インデックスの数 K = 16*1024/(8+6) = 1170

// 単一のリーフノード内のレコード数 N = 16/1 = 16

// レイヤー 3 B+ レコード番号 V = K*K*N = 21902400

MyISAM も B+Tree インデックスを使用します。違いは、主キー インデックスと非主キー インデックスを区別しないことです。どちらも非クラスター化インデックスです。リーフ ノードには、データ ファイルへのポインタが格納されます。

インデックスの選択

オプティマイザがインデックスを選択する目的は、最適な実行プランを見つけて、最低コストでステートメントを実行することです。データベースでは、スキャンされる行数は実行コストに影響を与える要因の 1 つです。スキャンされる行数が少ないということは、ディスク アクセスが少なくなり、CPU 消費も少なくなることを意味します。

もちろん、スキャンされた行数だけが判断基準ではありません。オプティマイザは、一時テーブルを使用するかどうか、ソートするかどうかなどの要素も考慮して総合的に判断します。

走査線の数を計算する方法

インデックスの値が多ければ多いほど、そのインデックスの識別力が高まります。インデックス内の異なる値の数はカーディナリティと呼ばれます。

-- 現在のインデックスベースを表示します。mysql> show index from test;
+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+
| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント |
+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+
| テスト | 0 | プライマリ | 1 | id | A | 100256 | NULL | NULL | | BTREE | | |
| テスト | 1 | index_a | 1 | a | A | 98199 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+--------------+--------------+------------+-------+---------+-----------+-----------+------------+------------+

パフォーマンスの観点から、InnoDB はサンプリング統計を使用します。デフォルトでは、N 個のデータ ページを選択し、これらのページ上の異なる値をカウントして平均値を取得し、それをこのインデックスのページ数で乗算して、このインデックスのカーディナリティを取得します。したがって、上記の 2 つのインデックスは同じカーディナリティを示しません。

データ テーブルは継続的に更新され、インデックス統計は固定されません。したがって、変更されたデータ行の数が 1/M を超えると (innodb_stats_persistent=on の場合はデフォルトは 10、それ以外の場合は 16)、統計の再インデックスが自動的にトリガーされます。

mysql> '%innodb_stats_persistent%' のような変数を表示します。
+--------------------------------------+--------------+
| 変数名 | 値 |
+--------------------------------------+--------------+
-- 統計の更新を自動的にトリガーするかどうか。変更されたデータが 10% を超えると、統計が再計算されます | innodb_stats_auto_recalc | ON |
-- 統計を再計算するときに削除マークの付いたレコードを考慮するかどうかを制御します | innodb_stats_include_delete_marked | OFF |
-- NULL 値の統計メソッド。変数が nulls_equal に設定されている場合、すべての NULL 値は同じと見なされます | innodb_stats_method | nulls_equal | 
-- メタデータを操作するときに統計の更新をトリガーするかどうか | innodb_stats_on_metadata | OFF |
--統計が永続的に保存されるかどうか | innodb_stats_persistent | ON |
-- innodb_stats_persistent=on、永続統計サンプリングのサンプル ページ数 | innodb_stats_persistent_sample_pages | 20 |
-- 非推奨、innodb_stats_transient_sample_pages に置き換えられました | innodb_stats_sample_pages | 8 |
-- 一時サンプル ページ番号 | innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+--------------+
  • サンプリングによる不正確な統計的基数に加えて、MVCC によっても不正確な基数統計が発生する可能性があります。たとえば、トランザクション A はトランザクション B より前に開始され、コミットされていません。トランザクション B は一部のデータを削除します。繰り返し読み取りでは、トランザクション A は削除されたデータを引き続き照会できます。現在、このデータには少なくとも 2 つのバージョンがあり、そのうちの 1 つは削除済みとしてマークされています。
  • 主キーは、テーブル内の行数に基づいて直接推定されます。オプティマイザーは、テーブル内の行数として、show table status の値 (「t」など) を直接使用します。
  • インデックス統計を手動でトリガーします。
-- インデックス情報を再計算します。mysql> analyze table t;

ソートによるインデックス選択への影響

-- テーブルを作成する mysql> CREATE TABLE `t` (
`id` int(11) NULLではない、
`a` int(11) デフォルト NULL,
`b` int(11) デフォルト NULL,
主キー (`id`)、
キー `a` (`a`),
キー `b` (`b`)
)ENGINE=InnoDB;

-- テストデータの保存手順を定義します。mysql> delimiter;
CREATE PROCEDURE idata() を作成する
始める

i INT を宣言します。
i = 1 に設定します。
(i <= 100000) の場合
 tに挿入
価値観
 (私、私、私) ;
i = i + 1 を設定します。
終わり
その間 ;
終わり;
区切り文字 ;

-- ストアド プロシージャを実行し、テスト データを挿入します。mysql> CALL idata ();

-- フィールド a のインデックスを使用して実行プランを表示します。mysql> explain select * from t where a between 10000 and 20000;
+----+-------------+-------+-------+---------------+------+--------+-------+------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+------+--------+-------+------------------------+
| 1 | SIMPLE | t | 範囲 | a | a | 5 | NULL | 10000 | インデックス条件を使用 |
+----+-------------+-------+-------+---------------+------+--------+-------+------------------------+

-- フィールド b はソートする必要があるため、インデックス b はより多くの行をスキャンする必要がありますが、順序付けされます。スキャンする行数とソートを考慮して、オプティマイザはコストが小さいと判断し、インデックス b を選択します。mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+------+------+------+------+------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+------+------+------+------+------------------------------------+
| 1 | SIMPLE | t | range | a,b | b | 5 | NULL | 50128 | インデックス条件の使用; where の使用 |
+----+-------------+-------+-------+---------------+------+------+------+------+------------------------------------+

-- 解決策 1: force index を使用して、インデックス a を強制的に使用し、オプティマイザの誤った選択を修正します。これは推奨されません (普遍的ではなく、インデックス名が変更された場合はステートメントを変更する必要があります)。
mysql> explain select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+------+------+------+----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t | range | a | a | 5 | NULL | 999 | インデックス条件の使用; where の使用; filesort の使用 |
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+

-- 解決策 2: MySQL が目的のインデックスを使用し、b、a でソートするように誘導します。オプティマイザは、a のソートのコストを考慮する必要があります。mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b、a limit 1;
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t | range | a,b | a | 5 | NULL | 999 | インデックス条件の使用; where の使用; filesort の使用 |
+----+-------------+-------+-------+-------+---------------+------+------+------+----------------------------------------------------+

-- 解決策3: シナリオによっては、より適切なインデックスを作成してオプティマイザに選択させるか、誤って使用されているインデックスALTER TABLE `t`を削除することができます。
インデックス「a」を削除します。
インデックス `b` を削除します。
インデックス `ab` (`a`,`b`) を追加します。

インデックスの最適化

インデックスの選択性

インデックスの選択性 = カーディナリティ / 行の総数

-- テーブル t のフィールド xxx の選択インデックス: select count(distinct xxx)/count(id) from t;

インデックスの選択性は、一意のインデックス値 (カーディナリティ) とテーブル レコードの数の比率を指します。選択性は、インデックスのスクリーニング能力を示す指標です。インデックス値の範囲は 0 ~ 1 です。選択性が高いほど、インデックス値は大きくなります。

通常のインデックス クエリを使用する場合、最初に通常のインデックスがロードされ、実際の行の主キーが通常のインデックスを介してクエリされ、次に主キーを使用してクラスター化インデックスを介して対応する行がクエリされ、ループ内のすべての行がクエリされます。クラスター化インデックス全体を直接検索する場合は、通常のインデックスとクラスター化インデックスを切り替える必要はありません。 2 つの操作の合計コストと比較すると、テーブル全体をスキャンする方が効率的である可能性があります。

実際の作業では、やはり業務状況に依存します。データの分布が不均一な場合、実際のクエリ条件では常にデータが少ない部分をクエリします。インデックス選択が低い列にインデックスを追加すると、良い効果が得られることもあります。

カバーインデックス

カバーリング インデックスを使用すると、ツリー検索の回数が減り、クエリのパフォーマンスが大幅に向上するため、カバーリング インデックスの使用は一般的なパフォーマンス最適化方法です。

-- ID の値を確認するだけで済み、ID の値は既に k インデックス ツリーに含まれているため、テーブルに戻らずにクエリ結果を直接提供できます。select ID from T where k between 3 and 5

-- フィールド V を追加します。V は各クエリで返される必要があります。k と v を結合インデックスにすることを検討できます。select ID,V from T where k between 3 and 5

左端プレフィックス原則 + インデックスプッシュダウン

-- id、name、age の 3 つの列に結合インデックスを作成します。 -- 左端プレフィックスの原則を満たし、name と age の両方にインデックスが付けられます。select * from T where name='xxx' and age=12

-- Mysql は自動的に名前と年齢の順序を最適化して調整します。名前と年齢の両方がインデックス化されます。select * from T where age=12 and name='xxx'

-- name が左端のプレフィックスの原則を満たす場合、インデックスが使用されます。MySQL 5.6 では、インデックス条件プッシュダウン最適化が導入されました。つまり、age=12 を満たさないレコードは、まずインデックスでフィルタリングされ、その後テーブルに返されます。select * from T where name like 'xxx%' and age=12

-- 左端プレフィックスの原則を満たしていないため、インデックスは使用されません。select * from T where name like '%xxx%' and age=12

-- 左端のプレフィックス原則を満たし、name はインデックスを使用します。select * from T where name='xxx'

-- 左端プレフィックスの原則を満たしていないため、インデックスは使用されません。select * from T where age=12

共同インデックスの設定の原則:

  • 順序を調整することで 1 つのインデックスのメンテナンスを軽減できる場合、この順序を優先させる必要がある場合が多くあります。
  • スペース: 名前や年齢などの小さなフィールドのインデックスを個別に作成することを優先します。(名前、年齢) 結合インデックスと (年齢) 単一フィールド インデックスを作成できます。

プレフィックスインデックス

mysql> テーブル SUser( を作成
ID bigint 符号なし主キー、
名前varchar(64)、  
電子メールvarchar(64)、
...
)エンジン=innodb;

-- 次のクエリ シナリオmysql> select name from SUser where email='xxx';

-- ソリューション 1: フルテキスト インデックス。返されるテーブルの数は、条件を満たすデータの量によって決まります。mysql> alter table SUser add index index1(email);

-- 解決策 2: プレフィックス インデックス。返されるテーブルの数は、プレフィックスの一致結果によって決まります。mysql> alter table SUser add index index2(email(6));

プレフィックス インデックスはスペースを節約できますが、プレフィックス長の定義に注意する必要があります。スペースを節約する一方で、クエリ コストをあまり増やさないようにする必要があります。つまり、テーブル リターン検証の数を減らす必要があります。

適切なプレフィックス長を設定するにはどうすればいいですか?

-- 許容可能な識別損失率を事前に設定し、条件を満たす最小のプレフィックス長を選択します。select count(distinct left(email,n))/count(distinct email) from SUser;

適切なプレフィックス長が長い場合はどうなりますか?

たとえば、ID カード番号が区別の要件を満たしている場合、12 桁を超えるプレフィックス インデックスが必要になることがあります。節約されるスペースは限られており、クエリ コストが増加します。したがって、プレフィックス インデックスを使用する必要はありません。この時点で、次の方法の使用を検討できます。

逆ストレージ

-- クエリ中の文字列反転クエリ mysql> select field_list from t where id_card = river('input_id_card_string');

ハッシュフィールドの使用

-- ID カード検証コードを格納する整数フィールドを作成し、このフィールドにインデックスを作成します。mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

-- ハッシュ フィールドを使用してインデックスで検索し、元のフィールドの精度を使用してフィルタリングします。mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

上記 2 つの方法の欠点:

  • 範囲クエリはサポートされていません
  • ハッシュフィールドを使用すると追加のスペースが必要になるため、新しいフィールドが追加されます
  • 読み取りと書き込みの際には、リバースやCRC32などの追加処理が必要です。

プレフィックス インデックスはカバー インデックスにどのような影響を与えますか?

-- プレフィックス インデックスを使用する場合、クエリ パフォーマンスを最適化するためにカバー インデックスは必要ありません。select id,email from SUser where email='xxx';

ユニークインデックス

通常のインデックスを使用することをお勧めします。ユニークインデックスは変更バッファを使用できず、メモリヒット率が低くなります。

インデックス失敗

  • インデックス値の順序を崩す可能性のある関数の使用を含む列操作は実行しないでください。
  • インデックスを無効にする%xxxクエリを避ける
  • or ステートメントは、インデックスを同時に使用しません。 or ステートメントの左側と右側のクエリ フィールドの 1 つだけがインデックスである場合、そのインデックスは無効になります。
  • 複合インデックス ABC 問題、最左接頭辞原理
  • 暗黙的な型変換
  • 暗黙的な文字エンコード変換
  • オプティマイザはインデックスを破棄します。テーブルの戻りやソート コストなどの要因がオプティマイザに影響します。オプティマイザは他のインデックスに切り替えるか、インデックス全体をスキャンします。

要約する

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

以下もご興味があるかもしれません:
  • この記事では、インデックスなしでMySQL結合を最適化する方法を説明します。
  • MySQL のインデックスの原理とクエリの最適化の詳細な説明
  • MySQL データの最適化 - 多層インデックス
  • MySQL インデックスの最適化: ページング探索の詳細な紹介
  • MySQL パフォーマンス最適化インデックス プッシュダウン
  • インデックスを使用して数千万のデータを持つ MySQL のクエリ速度を最適化する
  • MySQLの最適化とインデックス分析

<<:  Web デザイン TabIndex 要素

>>:  elasticsearchを使用してインデックスデータを定期的に削除する

推薦する

MySQL でのストアド プロシージャと関数の作成の詳細な説明

目次1. ストアドプロシージャ1.1. 基本構文1.2 実行権限を指定してストアドプロシージャを作成...

vue $http の get および post リクエストのクロスドメイン問題を解決する

Vue $http get および post リクエストのクロスドメイン問題まずconfig/ind...

Vue はファジークエリを実装します - MySQL データベースデータ

目次1. 需要2. 実装3. 結果1. 需要入力ボックスにデータを入力し、入力結果に基づいてデータベ...

MySQL 8.0.12 のインストールと設定方法のグラフィック チュートリアル (Windows10)

この記事は、参考のためにMySQL 8.0.12のインストールグラフィックチュートリアルを記録してい...

MySQLのビューとインデックスの使い方と違いの詳細な説明

MySQL ビュー簡単に言えば、MySQL ビューは SELECT コマンドを定義するためのショート...

MySQL データ型 DECIMAL(N,M) における N と M の意味の詳細な説明

同僚から、MySQL データ型 DECIMAL(N,M) の N と M の意味を尋ねられました。言...

JavaScript の基本変数

目次1. 変数の概要1.1 変数のメモリへの保存1.2 変数の使用1. 変数を宣言する2. 譲渡3....

Linuxでスクリーンショットを撮って編集するための最高のツール

メインのオペレーティング システムを Windows から Ubuntu に切り替えたとき、最初に考...

Vue で円形プログレスバーを実装する例

データ表示は、常にあらゆる職業の人々が求めているものです。特にフロントエンド開発業界では、データを表...

モバイルデバイスにおける適応レイアウトの問題に関する簡単な説明 (レスポンシブ、rem/em、Js ダイナミクス)

3G の普及により、携帯電話を使ってインターネットにアクセスする人が増えています。モバイル デバイ...

JavaScript の Strict モードの詳細な説明

目次導入厳密モードの使用厳格モードの新機能例外を強制的にスローする変数の使用を簡素化する議論を単純化...

面白いウェブサイトをデザインするための方法とテクニック(写真)

他の人から「つまらない」とか「時代遅れ」というフィードバックを受けて、それを変更しようとしたのに、更...

js ドラッグ アンド ドロップ テーブルでコンテンツ計算を実現する

この記事の例では、コンテンツの計算を実現するためのjsドラッグアンドドロップテーブルの具体的なコード...

Linux で JDK 環境を構成する方法

1. 公式ウェブサイトにアクセスして、jdk-8u162-linux-x64.tar.gzなどのLi...

MySQLストレージ時間タイプの選択に関する問題の説明

MySQL では、datetime 型は通常、時間を保存するために使用されますが、現在では多くのシス...