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つの方法)

推薦する

Nginx の純粋な構成でリアルタイム ログ レポートを実現するためのアイデアと方法

目次序文実装のアイデア実装手順1. Nginxをコンパイルする2. 設定ファイルは以下のとおりです要...

Linux で nohup ログ出力が大きすぎる問題の解決方法の詳細な説明

最近、hadoop テスト クラスターで spark ストリーミング プログラムを実行し、その後、n...

Mysqlトランザクション操作の失敗を解決する方法

Mysqlトランザクション操作の失敗を解決する方法トランザクションの原子性: トランザクションは、デ...

MYSQL フルバックアップ、マスタースレーブレプリケーション、カスケードレプリケーション、および半同期の概要

MySQL フルバックアップ1. バイナリログを有効にし、データベースから分離して別々に保存する v...

MySQLカーソルの使い方と機能の詳細な説明

[mysqlカーソルの使い方と機能]例:現在、テーブル A、B、C の 3 つのテーブルがあります。...

JavaScript の手ぶれ補正とスロットリングの詳細な説明

目次デバウンススロットル要約するデバウンス定義: スクロール イベントなど、短時間に連続してトリガー...

単一テーブルのMySQLバックアップとリストアに関する簡単な説明

A. MySQLバックアップツールxtrabackupのインストール1. Percona 公式 xt...

DIV と画像の水平および垂直の中央揃えは複数のブラウザと互換性があります

最初のタイプ: 完全な CSS コントロール、レイヤーフローティング (ログインページに適しています...

MySQLでインデックスエラーが発生する状況について簡単に説明します

以下に、トレーニング機関からのヒントと私自身の要約をいくつか示します。以下のインデックスの内容を説明...

WeChatアプレット開発によりホームページポップアップボックスアクティビティガイダンス機能が実現

目次1. 需要2. データベース設計3.Javaバックグラウンド構成の実装4. WeChatアプレッ...

MySQL の InnoDB ストレージ ファイルの詳細な説明

物理的に言えば、InnoDB テーブルは、共有テーブルスペース ファイル (ibdata1)、排他テ...

Tomcat で JNDI データ ソースを構成する 3 つの方法

これまでの仕事では、開発サーバーは一般的にTomcatでした。データソースは、多くの場合、appli...

MySQLデータベースはMMM高可用性クラスタアーキテクチャを実装します

コンセプトMMM (Mysql のマスター マスター レプリケーション マネージャー) は、Perl...

MySQL での Join の使用に関する詳細な説明

前の章では、1 つのテーブルからデータを読み取る方法を学習しました。これは比較的簡単ですが、実際のア...

携帯電話向けウェブページ作成のヒント

現在では多くの人がスマートフォンを使用していることを考慮すると、モバイル Web ページの書き方は、...