SQL#10 インデックス管理と最適化

はじめに
学習の概要
1. 含まれる操作
実践
テストデータの準備
課題
まとめ

はじめに

こんにちは、シルです。
SQLの学習にあたり、学んだことメモしておきます。
学習にお役立てください。

前回までの内容です
SQL#1 環境準備と基本操作
 SQL#2 テーブルとデータベースの管理
 SQL#3 ユーザー管理と権限の設定
 SQL#4 サーバーステータスとインデックス管理
 SQL#5 データベースのサイズ管理と構造確認
 SQL#6 基本的なデータ操作
 SQL#7 データ検索と削除の拡張
 SQL#8 ウィンドウ関数を使ったデータ分析
 SQL#9 集計と結合

学習の概要

この記事では、データベースパフォーマンスを向上させるためにインデックスの管理と最適化について学びます。
インデックスは、大量のデータを扱う際に検索速度を劇的に向上させるために使用されます。
ここでは、インデックスの作成や削除、ユニークインデックスや複合インデックスの使用方法、さらにフルテキスト検索や空間データの管理にも触れ、インデックスの効果的な使い方を理解します。

含まれる操作

1. CREATE INDEX – インデックスの作成

2. DROP INDEX – インデックスの削除

3. UNIQUE INDEX – ユニークインデックスの作成

4. COMPOSITE INDEX – 複合インデックスの作成

5. SHOW INDEX – インデックスの確認

6. ALTER TABLE … ADD INDEX – 既存テーブルへのインデックス追加

7. ALTER TABLE … DROP INDEX – インデックスの削除

8. FULLTEXT INDEX – フルテキストインデックスの作成

9. SPATIAL INDEX – 空間インデックスの作成

10. ANALYZE TABLE – インデックスの統計情報更新

11. OPTIMIZE TABLE – インデックスの最適化

実践

1. CREATE INDEX – インデックスの作成

商品名に対してインデックスを作成し、検索速度を向上させます。

CREATE INDEX idx_product_name ON Products(ProductName);

2. DROP INDEX – インデックスの削除

不要になったインデックスを削除します。

DROP INDEX idx_product_name ON Products;

3. UNIQUE INDEX – ユニークインデックスの作成

商品のSKU（商品コード）に対してユニークインデックスを作成し、重複を防ぎます。

CREATE UNIQUE INDEX idx_sku_unique ON Products(SKU);

4. COMPOSITE INDEX – 複合インデックスの作成

商品名とカテゴリーに対して複合インデックスを作成し、複数列での検索を最適化します。

CREATE INDEX idx_product_name_category ON Products(ProductName, Category);

5. SHOW INDEX – インデックスの確認

テーブルに存在するすべてのインデックスを確認します。

SHOW INDEX FROM Products;

6. ALTER TABLE … ADD INDEX – 既存テーブルへのインデックス追加

テーブルに後からインデックスを追加します。

ALTER TABLE Products ADD INDEX idx_price (Price);

7. ALTER TABLE … DROP INDEX – インデックスの削除

不要になったインデックスを既存のテーブルから削除します。

ALTER TABLE Products DROP INDEX idx_price;

8. FULLTEXT INDEX – フルテキストインデックスの作成

商品説明の中でフルテキスト検索を行えるようにインデックスを作成します。

CREATE FULLTEXT INDEX idx_fulltext_description ON Products(ProductDescription);

9. SPATIAL INDEX – 空間インデックスの作成

位置情報を格納した列に対して空間インデックスを作成します。

CREATE SPATIAL INDEX idx_location ON Products(GeoLocation);

10. ANALYZE TABLE – インデックスの統計情報更新

インデックスの統計情報を更新し、クエリの最適化を促進します。

ANALYZE TABLE Products;

11. OPTIMIZE TABLE – インデックスの最適化

テーブル全体を最適化して、インデックスの断片化を解消します。

OPTIMIZE TABLE Products;

テストデータの準備

まず、テーブル「Products」を作成し、大量のサンプルデータを挿入します。
このテーブルを使用して、インデックス操作の効果を体験します。

CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    SKU VARCHAR(50),
    ProductName VARCHAR(100),
    ProductDescription TEXT,
    Category VARCHAR(50),
    Price DECIMAL(10, 2),
    GeoLocation GEOMETRY
);

-- 大量のサンプルデータを挿入（仮データ）
INSERT INTO Products (SKU, ProductName, ProductDescription, Category, Price)
VALUES
('A123', 'Smartphone', 'Latest model with cutting-edge features', 'Electronics', 699.99),
('B456', 'Laptop', 'High-performance laptop for professionals', 'Computers', 1299.99),
('C789', 'Wireless Headphones', 'Noise-cancelling wireless headphones', 'Audio', 199.99),
('D101', 'Smartwatch', 'Waterproof smartwatch with fitness tracking', 'Wearables', 299.99),
('E112', 'Phone Case', 'Shockproof phone case', 'Accessories', 29.99);

課題

1. CREATE INDEX
　商品名に対するインデックスを作成し、検索速度の向上を確認してください。

2. COMPOSITE INDEX
　商品名とカテゴリーに複合インデックスを作成し、検索のパフォーマンスをテストしてください。

3. FULLTEXT INDEX
　商品説明に対してフルテキストインデックスを作成し、特定のキーワードで検索を行ってください。

4. OPTIMIZE TABLE
　テーブルを最適化し、インデックスの断片化を解消してください。

まとめ

この記事では、インデックスの作成・削除や、複合インデックス、ユニークインデックス、フルテキストインデックスの作成方法について学びました。
インデックスはパフォーマンスを大幅に向上させる重要な要素ですが、適切に管理することが必要です。
また、インデックスが不要な場合は削除し、テーブル全体の最適化を行うことで、データベース全体の効率を高めることができます。
次回は、データベースのバックアップとリカバリ方法について学び、システムの安定性とデータ保護を強化していきましょう。