1. MySQL のインデックスの使用方法インデックスは、特定の列の値を持つ行をすばやく見つけるために使用されます。インデックスがない場合、MySQL は最初の行から開始し、テーブル全体を走査して関連する行を見つける必要があります。テーブルが大きくなればなるほど、費用も高くなります。テーブルの関連列にインデックスがある場合、MySQL はすべてのデータを調べなくても、データ ファイルの途中のどこを調べるべきかをすぐに判断できます。これは、各行を順番に読み取るよりもはるかに高速です。 ほとんどの MySQL インデックス (PRIMARY KEY、UNIQUE、INDEX、FULLTEXT) は B ツリーに格納されます。例外: 空間データ型のインデックスは R ツリーを使用します。MEMORY テーブルはハッシュ インデックスもサポートします。 InnoDB は FULLTEXT インデックスに逆リストを使用します。 MySQL はインデックスを使用して次のことを行います。
最後に、小さなテーブルに対するクエリではインデックスはそれほど重要ではありません。クエリがほとんどの行にアクセスする必要がある場合、インデックスを処理するよりも順次読み取りの方が高速です。 2. テーブル全体のスキャンを避けるMySQL がクエリを解決するためにフル テーブル スキャンを使用する場合、EXPLAIN からの出力では、type 列に ALL が表示されます。 これは通常、次の場合に発生します:
小さなテーブルの場合、テーブルスキャンは通常適切であり、パフォーマンスへの影響はごくわずかです。 大きなテーブルの場合、オプティマイザーが誤ってテーブルスキャンを選択するのを回避するために、次の手法を試すことができます。
3. 列インデックスB ツリー データ構造により、インデックスは WHERE 句内の =、>、≤、BETWEEN、IN などの演算子に対応する特定の値、値のセット、または値の範囲をすばやく見つけることができます。 各ストレージ エンジンは、テーブルあたりのインデックスの最大数と最大インデックス長を定義します。すべてのストレージ エンジンはテーブルごとに少なくとも 16 個のインデックスをサポートし、インデックスの合計長は少なくとも 256 バイトです。 インデックスプレフィックス列の最初の N 文字のみを使用してインデックスを作成するには、col_name(N) を使用します。 InnoDB テーブルでは、プレフィックスの最大長は 767 バイトです。 全文索引FULLTEXT インデックスは全文検索に使用されます。 FULLTEXT インデックスは、InnoDB および MyISAM ストレージ エンジンでのみサポートされ、CHAR、VARCHAR、および TEXT 列に対してのみサポートされます。インデックス作成は常に列全体に対して行われ、列プレフィックス インデックスはサポートされません。 空間インデックス空間オブジェクトの位置や形状、または空間オブジェクト間の特定の空間関係に基づいて、特定の順序で配置されたデータ構造を指します。 MEMORYストレージエンジンのインデックスデフォルトでは、MEMORY ストレージ エンジンは HASH インデックスを使用しますが、BTREE インデックスもサポートします。 4. 複数列インデックスMySQL は複合インデックス (つまり、複数の列のインデックス) を作成できます。 インデックスには最大 16 列を含めることができます。 次のように定義された表現があるとします。 CREATE TABLEテスト( id INT NOT NULL、 last_name CHAR(30) NOT NULL、 first_name CHAR(30) NOT NULL、 主キー (id)、 INDEX idx_name (姓、名) ); idx_name インデックスは、last_name 列と first_name 列に基づいて構築されるインデックスです。このインデックスは左端のプレフィックスと一致するため、last_name と first_name 値の組み合わせを指定するクエリ、または last_name 値のみを指定するクエリに使用できます。 したがって、idx_name インデックスは次のクエリに使用できます。 SELECT * FROM test WHERE last_name='Jones'; SELECT * FROM test WHERE last_name='Jones' AND first_name='John'; SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon'); SELECT * FROM test WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N'; ただし、idx_name インデックスは次のクエリには使用できません。 SELECT * FROM test WHERE first_name='John'; SELECT * FROM test WHERE last_name='Jones' OR first_name='John'; 次の SQL を考えてみましょう。 SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; col1 と col2 に複数列のインデックスが存在する場合は、適切な行を直接フェッチできます。 col1 と col2 に別々の単一列インデックスが存在する場合、オプティマイザーはインデックス マージ最適化を使用するか、どのインデックスがより多くの行を除外する必要があるかを判断して最も制限の厳しいインデックスを見つけ、そのインデックスを使用して行をフェッチします。 テーブルに複数列のインデックスがある場合、オプティマイザーはインデックスの左端のプレフィックスを使用して行を検索できます。たとえば、3 列のインデックス (col1、col2、col3) がある場合、(col1)、(col1、col2)、(col1、col2、col3) に対してインデックス検索機能を使用できます。 列がインデックスの左端のプレフィックスを形成しない場合、MySQL はインデックスを使用して検索を実行できません。 次の SQL ステートメントを見てください。 SELECT * FROM tbl_name WHERE col1=val1; SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; SELECT * FROM tbl_name WHERE col2=val2; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; (col1、col2、col3) に複合インデックスがある場合、最初の 2 つのクエリのみがそれを使用します。すると、最後の 2 つのクエリでは、(col2) と (col2, col3) が (col1, col2, col3) の左端のプレフィックスではないため、インデックスを使用して検索を実行しません。 5. Bツリーインデックスとハッシュインデックスの比較Bツリーインデックスの特性B ツリー インデックスは、=、>、>=、<、<=、および BETWEEN 演算子を使用する式での列比較に使用できます。 LIKE の引数がワイルドカード文字で始まらない定数文字列である場合、インデックスは LIKE 比較にも使用できます。 次の句ではインデックスは使用されません。 /* LIKE 値はワイルドカード文字で始まります */ SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; /* LIKE 値は定数ではありません */ SELECT * FROM tbl_name WHERE key_col LIKE other_col; WHERE 句内のすべての AND レベルをカバーしないインデックスは、クエリの最適化には使用されません。つまり、インデックスを使用できるようにするには、各 AND グループでインデックスのプレフィックスを使用する必要があります。 次の WHERE 句はインデックスを使用します。 ... WHERE index_part1=1 かつ index_part2=2 かつ other_column=3 /* インデックス = 1 または インデックス = 2 */ ... WHERE インデックス=1 または A=10 かつ インデックス=2 /* "index_part1='hello'" のように最適化されています */ ... index_part1='hello' かつ index_part3=5 の場合 /* index1 ではインデックスを使用できますが、index2 または index3 では使用できません */ ... WHERE index1=1 AND index2=2 または index1=3 AND index3=3; 次の WHERE 句ではインデックスは使用されません。 /* index_part1 は使用されません */ ... index_part2=1 かつ index_part3=2 の場合 /* インデックスは WHERE 句の両方の部分では使用されません */ ... インデックス=1 または A=10 /* すべての行にまたがるインデックスはありません */ ... index_part1=1 または index_part2=10 の場合 MySQL では、インデックスが使用可能であっても、インデックスを使用しない場合があります。これが発生する理由の 1 つは、インデックスを使用するとテーブル内の行の大部分にアクセスする必要があるとオプティマイザーが見積もっていることです。 (この場合、テーブル スキャンの方が検索回数が少ないため、高速になる可能性があります。) ただし、このようなクエリで LIMIT を使用して特定の行のみを取得する場合、MySQL では、返す行をより速く見つけることができるため、引き続きインデックスが使用されます。 ハッシュインデックスの特性ハッシュ インデックスには、先ほど説明したインデックスとは異なる特性がいくつかあります。
Bツリーデータベースのインデックス作成で広く使用されているツリー データ構造。構造は常に整然と保たれ、完全一致 (等号演算子) と範囲 (より大きい、より小さい、BETWEEN 演算子など) の高速検索が可能になります。 このようなインデックスは、InnoDB や MyISAM などのほとんどのストレージ エンジンで使用できます。 B ツリー ノードは多数の子を持つことができるため、各ノードが最大 2 つの子を持つことができるバイナリ ツリーとは異なります。 B ツリーという用語は、インデックス設計の一般的なクラスを指すために使用されます。 MySQL ストレージ エンジンで使用される B ツリー構造は、従来の B ツリー設計には存在しない複雑さのため、バリアントと見なすことができます。 ハッシュインデックス範囲演算子の代わりに等価演算子を使用するクエリ用に設計されたインデックスの種類。 MEMORY テーブルに使用できます。 歴史的な理由により、ハッシュ インデックスは MEMORY テーブルのデフォルトのインデックスですが、ストレージ エンジンは B ツリー インデックスもサポートしており、これは一般に汎用クエリに適した選択肢です。 6. データサイズを最適化するディスク上のスペースを最小限にとどめるようにテーブルを設計します。 これにより、ディスクに書き込まれるデータとディスクから読み取られるデータの量が削減され、大幅な改善が実現します。 通常、テーブルが小さいほど、クエリ実行中にその内容を処理する際に必要なメイン メモリが少なくなります。テーブル データのスペースが削減されると、インデックスが小さくなり、処理が高速化されます。 MySQL は、さまざまなストレージ エンジン (テーブル タイプ) と行形式をサポートしています。各テーブルごとに、使用するストレージとインデックス作成方法を決定できます。アプリケーションに適切なテーブル形式を選択すると、パフォーマンスが大幅に向上します。 表の列
行の形式テーブル データを圧縮形式で保存してスペースをさらに削減するには、InnoDB テーブルを作成するときに ROW_FORMAT=COMPRESSED を指定します。 インデックス
結合同じデータ型を持つ異なるテーブルで同一の情報を持つ列を宣言すると、対応する列に基づく結合が高速化されます。 正規化一般的に、すべてのデータを非冗長状態に保つようにしてください(データベース理論では第 3 正規形と呼ばれます)。 1 つの長い繰り返し値の代わりに一意の ID を割り当て、必要な数の小さなテーブルでそれらの ID を繰り返し、結合句で ID を参照してクエリでテーブルを結合します。 7. データ型を最適化する数値型行を一意に識別するには、文字列ではなく数値を使用する方が適切です。これは、大きな数値は対応する文字列よりもストレージのバイト数が少なくなるため、転送と比較が高速になり、メモリの消費も少なくなるためです。 文字と文字列の型
他の
上記は、MySQL インデックスを最適化する方法の詳細です。MySQL インデックスの最適化の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。 以下もご興味があるかもしれません:
|
<<: Dockerとiptablesとブリッジモードのネットワーク分離と通信操作の実装
この古くからある疑問は、数え切れないほどのフロントエンド開発者やバックエンドプログラマーを悩ませてき...
MySQL レプリケーションの詳細な説明と簡単な例マスタースレーブレプリケーション技術は、MySQL...
この記事では、VMware 環境下で Ubuntu と Windows 間でファイルを共有する方法を...
リンク A のセマンティクス、ライティング スタイル、およびベスト プラクティス。私は JavaEy...
目次前面に書かれた複数のストレージマウント方法1.バインドマウント2. 巻数3.tmpfsマウントス...
1. 設置環境1. HUAWEI mate x CPU i5 82500u、8g メモリ、独立グラフ...
最近、次のような効果を実装しました。再生ボタンをクリックするとタイムラインの再生が開始され、一時停止...
目次1. レンダリング2. 実施原則3. まとめ1. レンダリング 2. 実施原則幅と高さが等しい拡...
この記事の例では、ページング効果を実現するためのvue+Elementの具体的なコードを参考までに共...
目次序文直接充填方式for ループの push() メソッド配列コンストラクタメソッド配列コンストラ...
序文注: テストデータベースのバージョンはMySQL 8.0ですテーブルを作成し、ユーザー scot...
1. MySQL の権限の概要MySQL には、権限を制御する 4 つのテーブルがあります。user...
1. ワンストップソリューション1. 問題の分析と特定 # MySQL設定ファイルを見つけて、MyS...
目次1. マップを初期化する2. マップポイント3. 位置決めを有効にする4. マップの変更を監視す...
私はしばらく MGR と連絡を取り合ってきました。MySQL 8.0.23 の登場により、MySQL...