MySQL 文字列インデックスのより合理的な作成ルールに関する議論

MySQL 文字列インデックスのより合理的な作成ルールに関する議論

序文

MySQL インデックスの使用に関しては、これまでインデックスの最左接頭辞ルール、インデックス カバレッジ、ユニーク インデックスと共通インデックスの使用、オプティマイザによるインデックスの選択などの概念を紹介してきました。今日は、文字列のインデックスをより合理的に作成する方法について説明します。

文字列インデックスをより良く作成する方法

MySQL では、データとインデックスはすべて B+ ツリー上にあることがわかっています。インデックスを作成するときに、ツリーが占めるスペースが小さいほど、検索速度が速くなります。varchar 形式の文字列の中には非常に長いものもあります。では、効率が最優先される今日の世界では、文字列インデックスをより合理的に作成するにはどうすればよいでしょうか。
テーブルに電子メール フィールドがあり、電子メール フィールドのインデックスを作成するとします。電子メール フィールドの値の形式は、[email protected] です。

インデックスを作成するには 2 つの方法があります。

1. 電子メール フィールドのインデックスを直接作成します。alter alter table t add index index1(email);

インデックスツリー構造は次のとおりです。

2. 電子メールのプレフィックスインデックスを作成します。alter alter table t add index index2(email(6));

インデックスのデータ構造は次のとおりです。

現時点でのクエリ ステートメントは次のとおりです: select id,name,email from t where email='[email protected]';

index1 を使用する場合の実行手順は次のとおりです。

1. インデックス値が [email protected] である主キー値 ID1 を index1 インデックス ツリーから検索します。

2. ID1 に基づいてテーブルに戻り、データ行が実際に [email protected] であることを確認します。結果を結果セットに追加します。

3. index1 インデックス ツリーの次のインデックス値が [email protected] を満たすかどうかの検索を続けます。満たさない場合は、クエリを終了します。

index2 を使用する場合の実行手順は次のとおりです。

1. index2 インデックス ツリーからインデックス値 zhangs を持つ主キー値 ID1 を検索します。

2. ID1 に基づいてテーブルに戻り、データ行が実際に [email protected] であることを確認します。結果を結果セットに追加します。

3. index2 インデックス ツリーの次のインデックス値が zhangs を満たすかどうかの検索を続けます。満たしている場合は、テーブルに戻って行データが [email protected] であるかどうかを照会し続けます。満たしていない場合は、スキップして検索を続けます。

4. インデックス値が zhangs でなくなるまで、index2 インデックス ツリーの検索を続けます。

上記の分析から、フルフィールドインデックスはプレフィックスインデックスと比較してテーブルリターンの数を減らすことがわかります。ただし、プレフィックスを6から7または8に増やすと、プレフィックスインデックステーブルリターンの数は減少します。つまり、プレフィックスの長さが定義されている限り、スペースを節約し、効率を確保できます。

そこで疑問になるのが、プレフィックス インデックスの長さをどのように測定するかということです。

1. select count(distinct email) as L from t;

2. 異なるプレフィックス長を順番に選択して、異なる値の数を表示します。

選択
 count(distinct left(email,4))をL4として、
 count(distinct left(email,5))をL5として、
 count(distinct left(email,6))をL6としてカウントし、
 count(distinct left(email,7))をL7としてカウントし、
t から;

次に、実際の許容損失率に応じて、最も短い適切なプレフィックス長が選択されます。

プレフィックス長の問題は解決しましたが、プレフィックス インデックスを使用すると、インデックス カバレッジ機能が使用されなくなるという問題があります。
完全なフィールド インデックスを使用する場合、 select id,email from t where email='[email protected]';をクエリすると、テーブルに戻らずに id フィールドと email フィールドを直接見つけることができます。

ただし、プレフィックス インデックスを使用する場合、MySQL はプレフィックスが電子メール値全体をカバーするかどうかを認識しません。完全に含まれているかどうかに関係なく、主キー値に基づいてテーブルをクエリすることによって決定されます。

したがって、プレフィックス インデックスを使用すると、スペースを節約して効率を確保できますが、カバーリング インデックスの特性を活用できません。使用するかどうかは、具体的な考慮事項によって異なります。

文字列インデックスを作成する他の方法

実際には、プレフィックス切り捨てを使用してすべての文字列をインデックスできるわけではありません。たとえば、ID番号やIPアドレスなどの文字列にプレフィックスインデックスを使用することは合理的ではありません。ID番号の最初の数桁は、同じ地域の人であれば一般的に同じであるため、プレフィックスインデックスを使用することは合理的ではありません。実際には、通常、IP値を数値に変換して保存します。

ID カード番号の場合、逆ストレージを使用したり、プレフィックスを取得してインデックスを作成したり、crc32() 関数を使用してハッシュ チェック コード (int 値) をインデックスとして取得したりできます。

逆: select field_list from t where id_card = reverse('input_id_card_string');

crc32: select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

これら 2 つの方法は比較的効率的です。どちらも範囲検索はサポートしていませんが、等しい値の検索はサポートしています。

リバース方式ではリバース関数を使用する必要がありますが、返されるテーブルの数はハッシュ方式よりも多くなる場合があります。

ハッシュ モードでは、新しいインデックス フィールドを作成し、crc32() 関数を呼び出す必要があります。 (注: crc32() 関数によって取得された結果は一意であることが保証されません。重複する可能性がありますが、その可能性は低いです)。テーブルが返される回数は少なく、ほぼ 1 回で十分です。

やっと

一般に、文字列インデックスを作成するにはいくつかの方法があります。

1. 文字列が短いため、フィールド全体が直接インデックス化されます

2. 文字列が長く、プレフィックスの識別性が高いため、プレフィックスインデックスを作成します。

3. 文字列が長く、プレフィックスが区別できない場合は、逆順またはハッシュモードでインデックスを作成します(この方法は範囲クエリでは機能しません)

4. 実際の状況に応じて、ip などの特殊な文字列は特別に扱われます。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません:
  • MySQL インデックスの長所と短所、およびインデックス作成のガイドライン
  • MySQL コマンドを使用してインデックスを作成、削除、およびクエリする方法の紹介
  • MySQLインデックスを正しく作成する方法
  • MySQL の結合テーブルにインデックスを作成する方法
  • MySQLが全文インデックス共有を実現
  • MySQL インデックスの概要の詳細な説明 - MySQL インデックスの種類と作成
  • MySQL でインデックスを表示、作成、削除する方法
  • mysql インデックスの追加 mysql インデックスの作成方法
  • MySQLインデックスの作成について知っておくべきこと

<<:  Vue3を使用してjsで呼び出せるコンポーネントを実装する

>>:  Linux ファイル管理コマンド例の分析 [権限、作成、削除、コピー、移動、検索など]

推薦する

CSS プロパティ *-gradient の実用的な価値を探る

まず興味深い性質であるconic-gradientを紹介しましょう。円錐グラデーション!円グラフの作...

aタグ疑似クラスの機能と記述順序は何ですか?

a タグ疑似クラスの役割: 「:link」: 訪問されていないタグの状態。 「:visited」: ...

vue + Electron でデスクトップ アプリケーションを作成するためのサンプル コード

1.vueパッケージングここでは、vueネイティブパッケージングコマンドを使用してvueプロジェクト...

XHTML 入門チュートリアル: よく使われる XHTML タグ

<br />記事と同様に、Web ページにも明確な段落と重要度の異なるタイトルが必要です...

HTTPS の原則の説明

HTTPS ウェブサイトの構築コストが下がるにつれて、ほとんどのウェブサイトが HTTPS プロトコ...

HTML DOCTYPEの略語

DOCTYPE が次のとおりである場合:コードをコピーコードは次のとおりです。 <!DOCTY...

Webフロントエンド開発CSS関連チームコラボレーション

フロントエンド開発部門は成長し、スタッフも増加し、技術も向上しています。 CSSer はフロントエン...

MySql 範囲内の検索時にインデックスが有効にならない理由の分析

1 問題の説明この記事では、確立された複合インデックスをソートし、レコード内の非インデックス フィー...

MySQLで行を列に変換する方法

MySQL の行から列への操作いわゆる行から列への操作は、テーブルの行情報を列情報に変換することです...

MySQL の条件文で 1 つの情報しか読み取れない問題に対する 2 つの解決策

今日、私の同僚が MYSQL クエリ ステートメントの作成時に非常に奇妙な問題に遭遇しました。MyS...

WeChatアプレットがSMSログインを実装

目次1. インターフェース効果のプレビュー2.uViewのインストール3.uViewの設定3.1 m...

バッテリー残量が少なくなったときに Linux を自動シャットダウンする方法

序文最近、私の住居の電力事情が不安定で、突然の停電が頻繁に起こります。ノートパソコンを持っているので...

PDO を使用して SQL インジェクションを防ぐ原理の分析

序文この記事では、SQL インジェクションを回避するために pdo の前処理メソッドを使用します。詳...

WebWorkerはJavaScriptサンドボックスの詳細をカプセル化します

目次1. シナリオ2. IJavaScriptShadowboxを実装する2.1 メインスレッドの実...

Docker を使用した war パッケージ プロジェクトのデプロイの実装

Docker で war をデプロイするには、コンテナを使用する必要があります。ここでは tomca...