MariaDBの設定でインデックスの使用に関する動作を変更する方法
MariaDB が 1 列の自己結合でインデックスを使用しない理由
MariaDB は、MySQL と互換性のあるオープンソースのデータベース管理システムです。インデックスは、テーブル内のデータの高速検索に使用されます。しかし、MariaDB は、1 列の自己結合でインデックスを使用しない場合があります。これは、結合される列の値がすべて NULL であるため、「選択性」が低いと考えられるからです。
選択性とは、インデックスを使用して検索する際に、実際にヒットするデータの割合を表します。選択性が高いほど、インデックスは効果的に機能します。逆に、選択性が低い場合、インデックスを使用しても検索速度が向上しない可能性があります。
1 列の自己結合における選択性
1 列の自己結合では、結合される列の値がすべて同じである必要があります。しかし、すべての値が NULL である場合、どの行もどの行とも一致することになります。つまり、すべての行がヒットすることになり、選択性が 100% になることになります。
MariaDB は、インデックスを使用する際に、コストとメリットを比較します。選択性が 100% の場合、インデックスを使用しても検索速度が向上しない可能性があります。むしろ、インデックスを使用することで、オーバーヘッドが発生し、検索速度が低下する可能性があります。
解決策
1 列の自己結合でインデックスを使用したい場合は、結合される列に NULL 以外の値を設定する必要があります。また、結合される列にインデックスを作成する必要があります。
例
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO users (name) VALUES ('John Doe'), ('Jane Doe');
SELECT *
FROM users u
INNER JOIN users v
ON u.id = v.id;
この例では、users テーブルには id 列と name 列があります。id 列は主キーであり、name 列は NULL を許容しません。この場合、id 列にインデックスが作成されます。
上記のクエリは、users テーブルを自己結合します。id 列は NULL ではないため、選択性は 100% ではなくなります。そのため、MariaDB はインデックスを使用して検索を実行します。
-- テーブル作成
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
-- データ挿入
INSERT INTO users (name) VALUES ('John Doe'), ('Jane Doe');
-- 1列の自己結合 (インデックスなし)
SELECT *
FROM users u
INNER JOIN users v
ON u.id = v.id;
-- インデックス作成
CREATE INDEX idx_name ON users (name);
-- 1列の自己結合 (インデックスあり)
SELECT *
FROM users u
INNER JOIN users v
ON u.name = v.name;
usersテーブルを作成します。- インデックスなしで 1 列の自己結合を実行します。
name列にインデックスを作成します。
4 番目のクエリと 5 番目のクエリを実行すると、インデックスありの方が検索速度が速いことが確認できます。
注意:
このサンプルコードは、MariaDB 10.6.5 で動作確認しています。他のバージョンの MariaDB では動作が異なる場合があります。
1 列の自己結合でインデックスを使用するその他の方法
結合条件を変更することで、選択性を高めることができます。例えば、以下のクエリでは、name 列だけでなく、id 列も結合条件に使用しています。
SELECT *
FROM users u
INNER JOIN users v
ON u.id = v.id
AND u.name = v.name;
このクエリでは、id 列にインデックスが作成されているため、インデックスを使用して検索を実行することができます。
サブクエリを使用することで、結合条件を複雑にすることができます。例えば、以下のクエリでは、name 列が NULL ではないユーザーのみを結合しています。
SELECT *
FROM users u
INNER JOIN (
SELECT *
FROM users
WHERE name IS NOT NULL
) v
ON u.id = v.id;
結合の種類を変更することで、検索速度を向上させることができます。例えば、以下のクエリでは、INNER JOIN ではなく、HASH JOIN を使用しています。
SELECT *
FROM users u
HASH JOIN users v
ON u.id = v.id;
HASH JOIN は、INNER JOIN よりも高速に実行できる場合がありますが、メモリ使用量が多くなります。
テーブルの構造を変更することで、インデックスの効果を高めることができます。例えば、以下のクエリでは、name 列を先頭に配置しています。
CREATE TABLE users (
name VARCHAR(255) NOT NULL,
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);
インデックスは、テーブルの先頭に配置されている列に対して最も効果的に機能します。
MariaDB の設定を変更することで、インデックスの使用に関する動作を変更することができます。例えば、以下の設定では、選択性が低い場合でもインデックスを使用するようにしています。
[mysqld]
innodb_use_index_for_group_by = 1
この設定は、GROUP BY 句で使用されるインデックスにも適用されます。
mysql indexing innodb
