インデックスモデルハッシュテーブル
順序付けられた配列: 等値クエリと範囲クエリの両方のシナリオでパフォーマンスは優れていますが、データの挿入と削除にはデータの移動が必要であり、コストがかかりすぎます。したがって、これは静的ストレージ エンジンにのみ適用されます。 二分バランス木: 各ノードの左の子は親ノードより小さく、親ノードは右の子より小さい。時間計算量は O(log(N)) です。 マルチブランチバランスツリー: インデックスはメモリに保存されるだけでなく、ディスクにも書き込まれます。クエリのディスク読み取りを最小限に抑えるには、クエリ プロセスがアクセスするデータ ブロックの数をできるだけ少なくする必要があります。したがって、「N 項」ツリーが使用されます。 B+ツリーB ツリーと B+ ツリー Bツリー B+ツリー InnoDB は B+ ツリー インデックス モデルを使用します。次に示すように、ID の主キー列、テーブル内のフィールド k、および k のインデックスを持つテーブルがあるとします。
予防
// データページが 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 | +--------------------------------------+--------------+
-- インデックス情報を再計算します。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 共同インデックスの設定の原則:
プレフィックスインデックス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 つの方法の欠点:
プレフィックス インデックスはカバー インデックスにどのような影響を与えますか? -- プレフィックス インデックスを使用する場合、クエリ パフォーマンスを最適化するためにカバー インデックスは必要ありません。select id,email from SUser where email='xxx'; ユニークインデックス通常のインデックスを使用することをお勧めします。ユニークインデックスは変更バッファを使用できず、メモリヒット率が低くなります。 インデックス失敗
要約するこれで、MySQL インデックスの選択と最適化に関するこの記事は終了です。MySQL インデックスの選択と最適化に関するより関連性の高いコンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。 以下もご興味があるかもしれません:
|
>>: elasticsearchを使用してインデックスデータを定期的に削除する
目次1. ストアドプロシージャ1.1. 基本構文1.2 実行権限を指定してストアドプロシージャを作成...
Vue $http get および post リクエストのクロスドメイン問題まずconfig/ind...
目次1. 需要2. 実装3. 結果1. 需要入力ボックスにデータを入力し、入力結果に基づいてデータベ...
この記事は、参考のためにMySQL 8.0.12のインストールグラフィックチュートリアルを記録してい...
MySQL ビュー簡単に言えば、MySQL ビューは SELECT コマンドを定義するためのショート...
同僚から、MySQL データ型 DECIMAL(N,M) の N と M の意味を尋ねられました。言...
目次1. 変数の概要1.1 変数のメモリへの保存1.2 変数の使用1. 変数を宣言する2. 譲渡3....
メインのオペレーティング システムを Windows から Ubuntu に切り替えたとき、最初に考...
データ表示は、常にあらゆる職業の人々が求めているものです。特にフロントエンド開発業界では、データを表...
3G の普及により、携帯電話を使ってインターネットにアクセスする人が増えています。モバイル デバイ...
目次導入厳密モードの使用厳格モードの新機能例外を強制的にスローする変数の使用を簡素化する議論を単純化...
他の人から「つまらない」とか「時代遅れ」というフィードバックを受けて、それを変更しようとしたのに、更...
この記事の例では、コンテンツの計算を実現するためのjsドラッグアンドドロップテーブルの具体的なコード...
1. 公式ウェブサイトにアクセスして、jdk-8u162-linux-x64.tar.gzなどのLi...
MySQL では、datetime 型は通常、時間を保存するために使用されますが、現在では多くのシス...