MySQLのインデックスとカーディナリティ：範囲検索で高カーディナリティ列を最初にインデックス化する

2024-04-02

MySQLのインデックスとカーディナリティ：範囲検索で高カーディナリティ列を最初にインデックス化する

カーディナリティは、列内の異なる値の数を表します。カーディナリティが高い列は、多くの異なる値を持つ列です。例えば、gender列は、maleとfemaleという2つの値しか持たないため、カーディナリティが低くなります。一方、user_id列は、每个ユーザーに対して異なる値を持つため、カーディナリティが高くなります。

範囲検索とインデックス

範囲検索は、特定の範囲内の値を持つ行を見つける検索です。例えば、age列が20から30までの値を持つ行を見つける範囲検索を行う場合、age列にインデックスがあると、インデックスを使用して効率的に検索を行うことができます。

高カーディナリティ列を最初にインデックス化する

範囲検索を行う場合、高カーディナリティ列を最初にインデックス化することで、パフォーマンスを大幅に向上させることができます。これは、高カーディナリティ列を最初にインデックス化することで、検索条件に合致する行を絞り込むことができるためです。

例えば、user_id列とage列を使用して、user_idが10000から20000までの範囲で、ageが20から30までの値を持つ行を見つける範囲検索を行う場合、以下の2つのインデックスを作成することができます。

user_id, age
age, user_id

最初のインデックスは、user_id列を最初にインデックス化しています。このインデックスを使用すると、user_idが10000から20000までの範囲の行を効率的に絞り込むことができます。その後、age列を使用して、絞り込んだ行の中からageが20から30までの値を持つ行を見つけることができます。

2番目のインデックスは、age列を最初にインデックス化しています。このインデックスを使用すると、ageが20から30までの値を持つ行を効率的に絞り込むことができます。しかし、その後、user_id列を使用して、絞り込んだ行の中からuser_idが10000から20000までの範囲の行を見つける必要があり、これは最初のインデックスよりも効率が低くなります。

一般的には、範囲検索を行う場合、高カーディナリティ列を最初にインデックス化することで、パフォーマンスを大幅に向上させることができます。

例

以下は、高カーディナリティ列を最初にインデックス化する例です。

CREATE TABLE users (
  user_id INT NOT NULL AUTO_INCREMENT,
  age INT NOT NULL,
  gender VARCHAR(10) NOT NULL,
  PRIMARY KEY (user_id)
);

CREATE INDEX idx_user_id_age ON users (user_id, age);

この例では、usersテーブルにuser_id、age、gender列があります。user_id列は主キーであり、user_idとage列を使用してインデックスを作成しています。

このインデックスを使用すると、user_idが特定の範囲内の値を持つ行を見つける範囲検索を効率的に実行することができます。

MySQLのインデックスは、テーブル内のデータの特定部分へのアクセスを高速化するデータ構造です。範囲検索を行う場合、高カーディナリティ列を最初にインデックス化することで、パフォーマンスを大幅に向上させることができます。

-- テーブル作成
CREATE TABLE users (
  user_id INT NOT NULL AUTO_INCREMENT,
  age INT NOT NULL,
  gender VARCHAR(10) NOT NULL,
  PRIMARY KEY (user_id)
);

-- インデックス作成
CREATE INDEX idx_user_id_age ON users (user_id, age);

-- 範囲検索
SELECT * FROM users WHERE user_id BETWEEN 10000 AND 20000 AND age BETWEEN 20 AND 30;

このコードを実行すると、user_idが10000から20000までの範囲で、ageが20から30までの値を持つ行がすべて返されます。

説明

次に、idx_user_id_ageという名前のインデックスを作成します。このインデックスは、user_id列を最初にインデックス化しています。

この範囲検索は、idx_user_id_ageインデックスを使用して効率的に実行されます。

このサンプルコードは、MySQL 8.0を使用しています。
インデックスを作成する前に、テーブルに十分なデータがあることを確認してください。
インデックスは、テーブルのパフォーマンスを向上させることができますが、必ずしもすべてのクエリのパフォーマンスを向上させるわけではありません。

範囲検索を高速化する他の方法

複合インデックスは、複数の列を使用して作成するインデックスです。範囲検索を行う場合、検索条件に使用するすべての列を含む複合インデックスを作成することで、パフォーマンスを向上させることができます。

CREATE INDEX idx_user_id_age ON users (user_id, age);

この複合インデックスを使用すると、user_idとage列を使用して、効率的に検索を行うことができます。

カラム統計情報は、テーブル内の各列のデータ分布に関する情報を提供します。カラム統計情報を収集することで、MySQLはクエリをより効率的に実行することができます。

例えば、age列の最小値と最大値を収集することで、MySQLはage列が特定の範囲内に含まれているかどうかを効率的に判断することができます。

カラム統計情報は、ANALYZE TABLEコマンドを使用して収集することができます。

ANALYZE TABLE users;

クエリプランキャッシュは、MySQLが過去に実行したクエリプランを保存するキャッシュです。クエリプランキャッシュを使用することで、MySQLは同じクエリを繰り返し実行する際に、クエリプランを再作成する必要がなくなり、パフォーマンスを向上させることができます。

クエリプランキャッシュは、query_cache_sizeというシステム変数を使用して設定することができます。

SET GLOBAL query_cache_size = 1024M;

テーブルパーティショニング
ヒストグラムインデックス
Bloomフィルタ

これらの方法は、範囲検索だけでなく、その他のクエリのパフォーマンスも向上させることができます。

範囲検索を高速化する方法は、高カーディナリティ列を最初にインデックス化する以外にもいくつかあります。これらの方法を組み合わせて使用することで、パフォーマンスを大幅に向上させることができます。

mysql performance indexing

MySQLのインデックスとカーディナリティ：範囲検索で高カーディナリティ列を最初にインデックス化する