MySQL 分離列とプレフィックスインデックスの使用の概要

MySQL 分離列とプレフィックスインデックスの使用の概要

データ列を分離する

MySQL がインデックスを使用できないようにするクエリがよく見つかります。 MySQL は、クエリ内で独立して使用されない限り、列のインデックスを使用しません。 「分離」とは、インデックス付けされた列が式の一部またはクエリ関数本体に含まれないことを意味します。たとえば、次の例では actor_id インデックスにヒットしません。

`actor_id` を `actor` から選択します。`actor_id` + 1 = 2;

クエリ条件が実際には actor_id = 4 であることは人間にとっては簡単にわかりますが、MySQL ではそのようには処理されません。そのため、WHERE 決定条件を簡略化する習慣を身に付けてください。つまり、比較演算子の片側にはインデックス列だけが存在することになります。よくある間違いのもう一つの例を次に示します。

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

プレフィックスインデックスとインデックスの選択性

非常に長い文字を含む列にインデックスを作成する必要がある場合もありますが、これによりインデックスが多くのスペースを占有し、クエリの速度が低下します。 1 つの戦略はハッシュ インデックス シミュレーションを使用することですが、これが十分でない場合もあります。このような場合はどうすればよいでしょうか。

通常、インデックス列の前にある一部の文字にインデックスを付けて、フィールド全体のインデックスを置き換えることで、パフォーマンスを向上させ、スペースを節約できます。しかし、このアプローチでは選択性が低下します。インデックスの選択性とは、独立したインデックス値によってフィルタリングされたデータがデータセット全体に対して占める割合を指します。選択性の高いインデックスにより、MySQL は関連性のないデータをさらに除外できるようになります。たとえば、一意のインデックスの選択性は 1 です。 通常、列のプレフィックスは選択性の点で十分なパフォーマンスを提供します。 BLOB または TEXT 列、または非常に長い VARCHAR 列を使用する場合、MySQL ではフルレングスのインデックスが許可されないため、プレフィックス インデックスを定義する必要があります。

選択性を高めるために長いプレフィックスを使用することと、ストレージ スペースを節約するために十分に短いプレフィックスを使用することの間でバランスを取る必要があります。適切なプレフィックス長を決定するには、最も頻繁に使用される値を見つけて、それを最も頻繁に使用されるプレフィックスと比較します。たとえば、都市データ テーブルを例にとると、次のステートメントを使用してカウントできます。

SELECT COUNT(*) as cnt, `name` FROM `common_city` GROUP BY `name` ORDER BY cnt DESC LIMIT 10

これらの都市名がより頻繁に出現していることがわかります。これで、1 語の接頭辞を使用して、最も頻繁に使用される都市名の接頭辞を見つけることができます。

SELECT COUNT(*) as cnt, LEFT(`name`, 1) as pref FROM `common_city` GROUP BY pref ORDER BY cnt DESC LIMIT 10

1 つの単語に対してより多くのデータ セットが見つかると、独立して選択される可能性が低くなるため、プレフィックスの長さを調整する必要があることがわかります。たとえば、3 語に調整します。

SELECT COUNT(*) as cnt, LEFT(`name`, 3) as pref FROM `common_city` GROUP BY pref ORDER BY cnt DESC LIMIT 10

これは全長とあまり変わらないので、実際には 3 文字のプレフィックスで十分であることがわかります (元のテキストでは、より多くの文字数を持つ英語の都市データ テーブルが使用されています)。もう 1 つの方法は、異なる長さのプレフィックスの数と完全なフィールドの数の比率を使用して、それがどの程度適切であるかを評価することです。例えば:

選択 
  COUNT(DISTINCT LEFT(`name`, 1)) / COUNT(`name`) を pref1 として、 
  COUNT(DISTINCT LEFT(`name`, 2)) / COUNT(`name`) を pref2 として、 
  COUNT(DISTINCT LEFT(`name`, 3)) / COUNT(`name`) を pref3 として、 
  COUNT(DISTINCT LEFT(`name`, 4)) / COUNT(`name`) を pref4 として 
`common_city` から

値が 1 に近いほど効果は高くなりますが、プレフィックス長が長くなるにつれて改善の余地が小さくなることがわかります。平均値だけを見るのは得策ではなく、最悪のシナリオも確認する必要があります。 3 ~ 4 語で十分だと思うかもしれませんが、データが非常に不均一に分散している場合は、落とし穴がある可能性があります。そのため、プレフィックス数が少ないプレフィックスに対応するデータが他に比べて極端に大きい状況がないかどうかも確認する必要があります。最後に、指定した列にプレフィックス インデックスを追加できます。

ALTER TABLE `common_city` に KEY (name(3)) を追加します。

プレフィックス インデックスは、スペースの節約と効率性の向上という点では優れていますが、インデックスを ORDER BY および GROUP BY で使用できないという欠点もあります (実際の検証は、MySQL バージョン 5.7 以上でも役立ちます)。もう 1 つの一般的なシナリオは、保存された sessionId などの長い 16 進文字列で、最初の 8 桁のプレフィックスをインデックスとして使用すると、無関係なデータが大量にフィルター処理されるため、非常に効果的です。

上記は、MySQL 分離データ列とプレフィックス インデックスの使用の概要の詳細内容です。MySQL 分離データ列とプレフィックス インデックスの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL 独立インデックスと共同インデックスの選択
  • MySQL の最適化: 高品質の SQL 文を書く方法
  • カスタム変数を使用した MySQL クエリの最適化
  • MySQL 論理バックアップとリカバリ テストの概要
  • MySQLスケーラブル設計の基本原則
  • MySQL マスタースレーブ構築(複数のマスターと 1 つのスレーブ)の実装アイデアと手順
  • MySQLでテーブルインデックスを構築する方法
  • MySQL のインデックスとデータ テーブルを管理する方法
  • MySQLのSeconds_Behind_Masterの詳細な説明

<<:  HTMLにおける絶対パスと相対パスの違いの分析

>>:  CSSでプロセスナビゲーション効果を実現する(3つの方法)

推薦する

スタックメニューを実装するためのjQueryプラグイン

jQueryプラグインの毎日の積み重ねメニュー、参考までに、具体的な内容は次のとおりです。スタックメ...

VueはTeleportをベースにModalコンポーネントを実装します

目次1. テレポートについて知る2. テレポートの基本的な使い方3. 最初のステップの最適化4. 第...

Vueカスタムカプセル化ボタンコンポーネント

Vueボタンコンポーネントのカスタムカプセル化コードは参考用です。具体的な内容は次のとおりです。ボタ...

vue3を使用してAppleシステムのサイドメッセージプロンプト効果を模倣する

目次アニメーションプレビューその他のUIライブラリ始めるコンポーネントディレクトリ構造トーストおおよ...

MACでMYSQLデータベースのパスワードを忘れた場合の解決策

Mac オペレーティングシステムで MYSQL データベースのパスワードを忘れた場合の簡単な解決策1...

MySQL での limit の使用方法は何ですか (推奨)

SELECT * FROM テーブル名制限m,n; SELECT * FROM テーブル LIMI...

Vue3.0 における Ref と Reactive の違いの詳細な分析

目次参照と反応参照反応的RefとReactiveの違いshallowRef と shallowRea...

個人履歴書を作成するための HTML の簡単な実装

履歴書コード: XML/HTML コードコンテンツをクリップボードにコピー<!DOCTYPE ...

pt-heartbeat を使用して MySQL レプリケーションの遅延を監視する方法の詳細な説明

pt-ハートビートデータベースがマスターとスレーブ間で複製される場合、複製ステータスとデータ遅延は非...

jquery-multiselect を使用した IE6 のバグの解決方法

jquery-multiselect (ドロップダウン ボックスをチェックボックス付きの複数選択コン...

TeamCenter12 にログインする際の 404/503 問題の解決方法

TeamCenter12はアカウントのパスワードを入力し、ログインをクリックすると、404または50...

React Nativeでaxiosを使用してネットワークリクエストを行う方法

フロントエンド開発では、Ajax、jQuery ajax、axios、fetch など、データ要求を...

MySQL 8.0.16 winx64 のインストールと設定方法のグラフィックチュートリアル (win10 の場合)

この記事では、MySQL 8.0.16 winx64のインストールと設定の具体的な方法を記載します。...

MySQL8の再帰メソッドの使い方を教える

以前、カスタム関数を使用して MySQL でツリー構造を再帰的にクエリする方法についての記事を書きま...

Linux でユーザーをグループに追加する 4 つの方法の概要

序文Linux グループは、Linux でユーザー アカウントを管理するために使用される組織単位です...