mysqlのプレフィックスインデックスとは何ですか

mysql では、プレフィックス インデックスはテキストの最初の数文字にインデックスを付ける特別なインデックス タイプであり、このインデックス タイプによりインデックスのサイズをある程度まで削減でき、一部のデータをより効率的に処理できます。クエリ操作。通常、インデックスを短くするとインデックスのサイズが小さくなる可能性があるため、インデックスの長さはできるだけ短くする必要がありますが、インデックスの長さが短すぎるとインデックスの失敗が発生し、クエリ効率が低下する可能性があるため、プレフィックス インデックス これを行う場合、特定の状況に応じて適切なインデックス長を選択する必要があります。

このチュートリアルの動作環境: Windows7 システム、mysql8 バージョン、Dell G3 コンピューター。

1. プレフィックス インデックスとは

MySQL は広く使用されているリレーショナル データベース管理システムであり、データの保存と取得に高い効率性と柔軟性を備えています。 MySQL では、インデックスはクエリ操作を高速化するために使用されるデータ構造であり、クエリの速度と効率を大幅に向上させることができます。プレフィックス インデックスは MySQL の特殊なインデックス タイプで、特定のクエリ操作をより効率的に処理できます。

いわゆるプレフィックス インデックスは、率直に言うと、テキストの最初の数文字 (具体的には、インデックス作成時に指定される文字数 ) にインデックスを付けることです。たとえば、次のようになります。製品名の最初の 10 桁 インデックスを構築する場合、この方法で作成されたインデックスは小さくなり、クエリ効率が向上します。

プレフィックス インデックスとは、文字列全体ではなく、文字列のプレフィックス部分のみにインデックスを付けることを指します。このインデックス タイプでは、インデックスのサイズをある程度削減できます。たとえば、1,000 万個の文字列を含むテーブルがある場合、各文字列の長さは 100 文字になります。完全な文字列をインデックスとして使用すると、インデックスのサイズが小さくなります。非常に大きくなり、クエリ効率が非常に低くなります。ただし、文字列の最初の 10 文字のみにインデックスを作成すると、インデックス サイズが大幅に削減され、クエリ効率が大幅に向上します。

これは、Oracle のフィールドで Left 関数を使用して関数インデックスを作成するのと似ていますが、MySQL のプレフィックス インデックスはクエリ時に内部で自動的に照合を完了し、Left 関数を使用する必要がない点が異なります。

MySQL では、プレフィックス インデックスを使用するには、インデックスの長さを指定する必要があります。インデックスを短くするとインデックスのサイズが小さくなる可能性があるため、通常、インデックスの長さはできるだけ短くする必要がありますが、インデックスの長さが短すぎるとインデックスの失敗が発生し、クエリ効率が低下する可能性があります。したがって、プレフィックス インデックスを使用する場合は、特定の状況に応じて適切なインデックス長を選択する必要があります。

2. 接頭辞インデックスを使用する理由

学生の中には、なぜフィールド全体にインデックスを付けないのかと疑問に思う人もいるかもしれません。

一般に、特定のフィールドのデータ量が多すぎてクエリが頻繁に行われる場合、プレフィックス インデックスを使用するとインデックス ファイルのサイズが効果的に削減され、各インデックス ページでより多くのインデックスを保存できるようになります。値が増加するため、インデックス クエリの速度が向上します。

たとえば、顧客ストア名には非常に長い名前もあれば、非常に短い名前もあります。インデックスがフル カバレッジに基づいて構築されている場合、インデックスのストレージ スペースが非常に大きくなる可能性があります。インデックス付き 多くのインデックスが作成され、インデックスの記憶領域でさえ、データ テーブルの記憶領域よりもはるかに大きいため、このような長いテキストを含むフィールドの場合、最初の数文字をインターセプトしてインデックスを構築できます。データのクエリ効率の要件をある程度満たすだけでなく、インデックスの保存スペースも節約できます。 しかし、その一方で、プレフィックス インデックスには欠点もあります。プレフィックス インデックスは、MySQL の ORDER BY および GROUP BY に使用できません。また、カバレッジ スキャンにも使用できません。文字列自体が比較的長く、最初の数文字はまったく同じです。現時点では、プレフィックス インデックスの利点は明らかではないため、プレフィックス インデックスを作成する必要はありません。

これは、

の選択性に関する概念に戻ります。 データベース テーブルのインデックスの選択性については、別の記事で説明しますが、覚えておくべきことは 1 つだけです:

選択性が高いため、インデックスにより MySQL は検索時により多くの行を除外できるようになり、データ クエリの速度が速くなります。

。 それでは、プレフィックス インデックスをどのように作成するかが問題になります。

プレフィックスインデックスの作成方法は非常に簡単で、以下の方法で作成できます。

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

このパラメータはプレフィックス長を意味します。通常は次の方法で確認します。手順は次のとおりです。ステップでは、まずフィールドのすべての列の識別を計算します。

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

2 番目のステップは、列全体の識別に最も類似したプレフィックスの長さを計算することです

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

最后，不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是我们想要的值。

下面以某个测试表为例，数据体量在 100 万以上，表结构如下！

CREATE TABLE `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

测试一下正常的带name条件查询，效率如下：

select * from tb_test where name like '1805.59281427%'

我们以name字段为例，创建前缀索引，找出最合适的prefix_length值。

首先，我们大致计算一下name字段全列的区分度。

可以看到，结果为 0.9945，也就是说全局不相同的数据率在99.45%这个比例。

下面我们一起来看看，不同的prefix_length值下，对应的数据不重复比例。

当prefix_length为5，区分度为0.2237

当prefix_length为10，区分度为0.9944

当prefix_length为11，区分度为0.9945

通过对比，我们发现当prefix_length为11，最接近全局区分度，因此可以为name创建一个长度为11的前缀索引，创建索引语句如下：

alter table tb_test add key(name(11));