複合インデックスの落とし穴に注意！MySQLのパフォーマンスを最大限に引き出すためのヒント

2024-06-16

MySQLで複合インデックスを使用するタイミング

複合カラムで絞り込み条件として頻繁に使用する

複合インデックスの最も基本的な使用タイミングは、複数のカラムを組み合わせて絞り込み条件として頻繁に使用する場面です。例えば、顧客情報テーブルにおいて、顧客IDと注文日による検索を頻繁に行う場合、customer_idとorder_dateカラムで複合インデックスを作成することで、検索処理を大幅に高速化できます。

例：

SELECT * FROM customers
WHERE customer_id = 123 AND order_date = '2023-11-14';

上記のようなクエリの場合、customer_idとorder_dateカラムで複合インデックスが張られていれば、インデックスを使用して効率的に検索対象を絞り込むことができます。

範囲検索に利用する

複合インデックスは、範囲検索にも効果を発揮します。例えば、商品情報テーブルにおいて、価格が1000円以上3000円未満の商品を検索する場合、priceカラムで複合インデックスを作成することで、効率的に検索処理を実行できます。

SELECT * FROM products
WHERE price >= 1000 AND price < 3000;

LIKE句を含む検索を高速化する

複合インデックスは、LIKE句を含む検索を高速化する場合にも役立ちます。例えば、顧客情報テーブルにおいて、顧客名の一部で検索を行う場合、customer_nameカラムの先頭部分で複合インデックスを作成することで、検索処理を効率化できます。

SELECT * FROM customers
WHERE customer_name LIKE '%山田%';

結合クエリの処理速度を向上させる

複合インデックスは、結合クエリにおいても処理速度の向上に貢献します。例えば、顧客情報テーブルと注文情報テーブルを結合し、特定の顧客の注文履歴を取得する場合、customer_idカラムで両テーブルを結合することで、効率的に処理を実行できます。

SELECT c.customer_name, o.order_date, o.product_id
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE c.customer_id = 123;

複合インデックスは便利な機能ですが、使い方を誤るとかえってパフォーマンスを低下させてしまう可能性があります。以下は、複合インデックスを使用する際の注意点です。

不要な複合インデックスは作成しない: 使用頻度の低いクエリや、単一カラムで絞り込む方が効率的なクエリに対しては、複合インデックスを作成しない方が良い場合があります。
インデックスのカラム順序を意識する: 複合インデックスのカラム順序は、インデックスの効果に大きく影響します。頻繁に使用される条件を最初のカラムに配置するようにしましょう。
インデックスの種類を検討する: MySQLには、BTreeインデックスやハッシュインデックスなど、様々な種類のインデックスがあります。クエリのパターンに合わせて適切なインデックスを選択することが重要です。

複合インデックスは、適切に使用することで、MySQLのパフォーマンスを大幅に向上させることができます。複合インデックスの利用タイミングと注意点について理解し、効果的に活用することで、データベース操作を効率化しましょう。

【MySQL】

-- 顧客情報テーブルを作成
CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INT NOT NULL
);

-- 注文情報テーブルを作成
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 商品情報テーブルを作成
CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL
);

-- 複合インデックスを作成
-- 顧客IDと注文日で複合インデックス
CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

-- 価格範囲検索用の複合インデックス
CREATE INDEX idx_product_price ON products (price);

-- 顧客名の一部で検索用の複合インデックス (先頭部分)
CREATE INDEX idx_customer_name_prefix ON customers (customer_name(50));

-- 結合クエリ用の複合インデックス
CREATE INDEX idx_order_customer_id ON orders (customer_id);