MariaDBのインデックス選択のトラブルシューティング：非最適なインデックスを特定して修正する方法

2024-07-27

MariaDB インデックス選択：なぜ MariaDB は非最適なインデックスを選択するのか？

このブログ記事では、MariaDB がクエリに対して非最適なインデックスを選択する可能性がある理由と、それを回避する方法について説明します。

パフォーマンス、最適化、インデックス

データベースのパフォーマンスを向上させるためには、適切なインデックスを選択することが重要です。インデックスは、データベース内のデータを高速に検索できるようにするデータ構造です。しかし、すべてのインデックスが同じように作成されるわけではありません。場合によっては、MariaDB がクエリに対して非最適なインデックスを選択することがあります。

非最適なインデックスを選択する理由

MariaDB が非最適なインデックスを選択する理由はいくつかあります。

インデックス統計情報が古くなっている。MariaDB は、インデックスを選択するときに、インデックス統計情報を使用します。これらの統計情報は、インデックス内のデータの分布に関する情報を提供します。しかし、統計情報が古くなっている場合、MariaDB は非最適なインデックスを選択することがあります。
クエリが複雑である。複雑なクエリの場合、MariaDB が最適なインデックスを選択することが難しい場合があります。
テーブルのスキーマが頻繁に変更される。テーブルのスキーマが頻繁に変更される場合、MariaDB はインデックス統計情報を更新する時間がありません。これにより、MariaDB が非最適なインデックスを選択することがあります。

非最適なインデックスを回避するには、次の手順を実行します。

インデックス統計情報を定期的に更新する。ANALYZE TABLE コマンドを使用して、インデックス統計情報を更新できます。
クエリを簡素化する。クエリを簡素化することで、MariaDB が最適なインデックスを選択しやすくなります。
EXPLAIN コマンドを使用する。EXPLAIN コマンドを使用して、クエリの実行計画を表示できます。実行計画は、MariaDB がクエリを実行するために使用するインデックスに関する情報を提供します。

例

次のクエリを考えてみましょう。

SELECT * FROM customers WHERE customer_name = 'Smith';

このクエリに対して、MariaDB は customer_name インデックスを選択する可能性があります。しかし、customer_id インデックスの方が効率的である可能性があります。

EXPLAIN SELECT * FROM customers WHERE customer_name = 'Smith';

EXPLAIN コマンドを実行すると、次の出力が表示されます。

+----+-------------+-------------------+-------+---------------------+-------------+---------+------------------+
| id  | select_type | table             | type  | possible_keys       | key          | rows     | used_index_rows |
+----+-------------+-------------------+-------+---------------------+-------------+---------+------------------+
| 1  | SIMPLE      | customers          | index | customer_name,email | customer_name | 1000    | 1               |
+----+-------------+-------------------+-------+---------------------+-------------+---------+------------------+

この出力は、MariaDB が customer_name インデックスを選択していることを示しています。しかし、used_index_rows 列は、インデックスが 1 行しか使用されていないことを示しています。これは、customer_id インデックスの方が効率的である可能性があることを示唆しています。

MariaDB が非最適なインデックスを選択する可能性があることを理解することが重要です。非最適なインデックスを回避するには、インデックス統計情報を定期的に更新し、クエリを簡素化し、EXPLAIN コマンドを使用してクエリの実行計画を分析する必要があります。

CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

INSERT INTO customers (customer_name, email) VALUES
  ('Smith, Steve', '[email protected]'),
  ('Jones, Larry', '[email protected]'),
  ('Williams, Jessica', '[email protected]');

-- customer_name インデックスを作成する
CREATE INDEX idx_customer_name ON customers (customer_name);

-- customer_id と customer_name の複合インデックスを作成する
CREATE INDEX idx_customer_id_customer_name ON customers (customer_id, customer_name);

-- customer_name で顧客を検索する
SELECT * FROM customers WHERE customer_name = 'Smith';

-- customer_id で顧客を検索する
SELECT * FROM customers WHERE customer_id = 1;

この例では、次のことを行います。

customers という名前のテーブルを作成します。このテーブルには、customer_id、customer_name、email という 3 つの列があります。
customer_id 列は、プライマリキーであり、自動的にインクリメントされます。
customer_name 列と email 列は、NOT NULL 制約があります。
3 件の顧客レコードをテーブルに挿入します。
customer_name 列にインデックスを作成します。
customer_id 列と customer_name 列の複合インデックスを作成します。
customer_name で顧客を検索するクエリを実行します。

説明

この例では、2 つのインデックスを作成します。最初のインデックスは customer_name 列にあり、2 番目のインデックスは customer_id 列と customer_name 列の複合インデックスです。

customer_name で顧客を検索する場合、MariaDB は customer_name インデックスを使用します。これにより、MariaDB はテーブル全体をスキャンする必要がなくなり、パフォーマンスが向上します。

customer_id で顧客を検索する場合、MariaDB は customer_id と customer_name の複合インデックスを使用します。これにより、MariaDB は customer_id 列でレコードをすばやく検索し、次に customer_name 列を使用して正しいレコードを取得できます。

EXPLAIN コマンドを使用して、クエリの詳細な実行計画を分析できます。実行計画には、MariaDB がクエリを実行するために使用するインデックスに関する情報が含まれています。この情報を使用して、クエリのパフォーマンスのボトルネックを特定し、インデックス選択を改善することができます。

EXPLAIN SELECT * FROM customers WHERE customer_name = 'Smith';

ヒントを使用する

インデックスヒントを使用して、MariaDB に特定のインデックスを使用するように強制することができます。これは、クエリのパフォーマンスを向上させるために役立つ場合がありますが、注意して使用する必要があります。インデックスヒントが誤って使用されると、クエリのパフォーマンスが低下する可能性があります。

SELECT /*+ INDEX(customers idx_customer_name) */ * FROM customers WHERE customer_name = 'Smith';

カバリングインデックスを使用する

カバリングインデックスは、クエリで使用されるすべての列を含むインデックスです。カバリングインデックスを使用すると、MariaDB はテーブルデータをスキャンせずにクエリを実行できます。これにより、パフォーマンスが大幅に向上する場合があります。

CREATE INDEX idx_customer_covering ON customers (customer_name, email);

パーティショニングを使用する

パーティショニングは、大きなテーブルを論理的により小さなサブテーブルに分割するテクニックです。パーティショニングを使用すると、MariaDB はクエリを実行するために必要なパーティションのみをスキャンできます。これにより、パフォーマンスが向上する場合があります。

統計情報を更新する

MariaDB は、インデックスを選択するときにインデックス統計情報を使用します。これらの統計情報は、インデックス内のデータの分布に関する情報を提供します。統計情報が古くなっている場合、MariaDB は非最適なインデックスを選択することがあります。ANALYZE TABLE コマンドを使用して、インデックス統計情報を定期的に更新する必要があります。

最新の MariaDB バージョンを使用する

MariaDB の新しいバージョンには、インデックス選択を改善するための機能が多数含まれています。最新のバージョンの MariaDB を使用していることを確認してください。

専門家に相談する

パフォーマンスのチューニングに問題がある場合は、MariaDB の専門家に相談することを検討してください。彼らは、ワークロードに最適なインデックスを選択するお手伝いをすることができます。

performance optimization indexing