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 の他の関連記事に注目してください。

以下もご興味があるかもしれません: