大規模なデータセットを扱う際のインデックス設計のベストプラクティス
MariaDB 10で大きなインデックスを有効にする方法
innodb_large_prefix 設定パラメータを使用する
MariaDB 10.2以降では、innodb_large_prefix 設定パラメータを使用して、インデックスプレフィックスの長さを制限できます。このパラメータのデフォルト値は 767 バイトですが、innodb_file_format 設定パラメータが Barracuda に設定されている場合は、最大 3072 バイトまで増やすことができます。
innodb_large_prefix 設定パラメータを増やすには、次の手順に従います。
- MariaDB サーバーを停止します。
/etc/my.cnfファイルを開きます。- 以下の行を追加します。
innodb_large_prefix=3072
innodb_file_format 設定パラメータは、InnoDB テーブルファイルのフォーマットを指定します。このパラメータを Barracuda に設定すると、インデックスプレフィックスの長さを最大 3072 バイトまで増やすことができます。
innodb_file_format=Barracuda
CREATE INDEX ステートメントで USING オプションを使用すると、インデックスのフォーマットを指定できます。USING オプションに btree または hash を指定すると、インデックスプレフィックスの長さを最大 3072 バイトまで増やすことができます。
USING オプションを使用するには、次の例のように CREATE INDEX ステートメントに USING オプションを追加します。
CREATE INDEX my_index ON my_table (my_column) USING btree;
ALTER TABLE my_table ALTER INDEX my_index USING btree;
注意事項
innodb_file_format設定パラメータをBarracudaに変更すると、古いバージョンの MariaDB サーバーと互換性がなくなります。USINGオプションを使用すると、インデックスのパフォーマンスが低下する可能性があります。
これらの注意事項を考慮した上で、最適な方法を選択する必要があります。
-- テーブル作成
CREATE TABLE my_table (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT NOT NULL,
PRIMARY KEY (id)
);
-- インデックス作成
CREATE INDEX my_index ON my_table (name) USING btree;
-- インデックスのフォーマット変更
ALTER TABLE my_table ALTER INDEX my_index USING hash;
-- インデックス削除
DROP INDEX my_index ON my_table;
- MariaDB 10.2 よりも前のバージョンを使用している場合は、
innodb_large_prefix設定パラメータを使用できません。 - InnoDB テーブルファイルフォーマットが
Antelopeの場合は、インデックスプレフィックスの長さは最大 767 バイトまでです。
MariaDB 10で大きなインデックスを有効にするその他の方法
InnoDB の圧縮機能を使用すると、インデックスファイルのサイズを小さくすることができます。これにより、大きなインデックスをより効率的に保存することができます。
InnoDB の圧縮機能を使用するには、次の手順に従います。
innodb_file_per_table=1
innodb_compression_algorithm=zlib
パーティショニングを使用するには、次の手順に従います。
innodb_file_per_table=1
innodb_partitioning_strategy=hash
別のインデックス方法を使用する
B-Tree インデックス以外にも、ハッシュインデックスや全文インデックスなどのインデックス方法があります。これらのインデックス方法は、特定の種類のデータに対して B-Tree インデックスよりも効率的な場合があります。
CREATE INDEX my_index ON my_table (my_column) USING hash;
注意事項
- パーティショニングを使用すると、テーブルの管理が複雑になる可能性があります。
- 別のインデックス方法を使用すると、インデックスの互換性がなくなる可能性があります。
mysql indexing utf-8
