データベースの最適化：SQLにおけるインデックスの探索
データベース最適化：SQLのインデックス探索
ある特定のトピックを検索する際に、まずその本の索引ページ（その本の最初にある）を訪れ、興味のあるトピックが含まれているページ番号を見つけます。さて、索引ページなしで本の特定のトピックを見つけるのはどれほど不便か想像してみてください。そのためには、本のすべてのページを検索する必要がありますが、これは非常に時間がかかりイライラします。
同様の問題がSQL Serverでも発生します。データベースからデータを取得する際に、この問題を解消するためにSQL Serverもインデックスを使用します。この記事では、その部分をカバーします。インデックスがなぜ必要か、そして効果的にインデックスを作成および削除する方法について説明します。このチュートリアルの前提条件は、SQLコマンドの基本的な知識です。
- LAION AIは、Video2Datasetを紹介しますこれは、効率的かつスケールでビデオとオーディオのデータセットをキュレーションするために設計されたオープンソースツールです
- 学習トランスフォーマーコード第2部 – GPTを間近で観察
- 成功に導くデータチームの意思決定
インデックスとは何ですか？
インデックスは、データを取得するためのポインタを使用するスキーマオブジェクトであり、データの位置を特定するためのI/O（入出力）時間を短縮します。インデックスは、検索したい1つ以上の列に適用することができます。これらはBツリーと呼ばれる別のデータ構造に列を保存します。Bツリーの主な利点の1つは、データをソート順に保存することです。
データがソートされていると、なぜデータをより速く取得できるのか疑問に思っている場合は、線形検索と二分探索について読んでみてください。
インデックスは、SQLクエリのパフォーマンスを向上させるための最も有名な方法の1つです。それらは小さく、高速で、リレーショナルテーブルに対して非常に最適化されています。インデックスがない状態で行を検索する場合、SQLは完全なテーブルスキャンを直線的に実行します。つまり、条件に一致する行を見つけるために、SQLはすべての行をスキャンする必要があります。これは非常に時間がかかります。一方、先述したように、インデックスはデータをソートされた状態に保持します。
ただし、注意が必要です。インデックスは別のデータ構造を作成するため、追加のスペースが必要です。これはデータベースが大きい場合に問題となることがあります。良い実践として、インデックスは頻繁に使用される列に対してのみ効果的であり、まれに使用される列では避けることができます。以下に、インデックスが役立つ可能性があるいくつかのシナリオを示します。
- 行数が（> 10000）である場合。
- 必要な列には多数の値が含まれている場合。
- 必要な列には多数のNULL値が含まれていない場合。
- 特定の列を基準にデータを頻繁にソートまたはグループ化する場合、インデックスが役立ちます。インデックスはフルスキャンを実行するのではなく、ソートされたデータを迅速に取得します。
また、次の場合はインデックスを避けることができます。
- テーブルが小さい場合。
- または、列の値がほとんど使用されない場合。
- または、列の値が頻繁に変更される場合。
最適化プログラムが、インデックステーブルよりもフルテーブルスキャンの方が時間がかからないと検出した場合、存在していてもインデックスは使用されない場合があります。これは、テーブルが小さい場合や列が頻繁に更新される場合に発生する可能性があります。
サンプルデータベースの作成
始める前に、MySQL WorkbenchをPCに設定しておく必要があります。チュートリアルに簡単に従うために、このYouTubeビデオを参照できます。
Workbenchを設定した後、クエリを実行できるランダムなデータを作成します。
テーブルの作成：
-- ランダムなデータを保持するためのテーブルを作成
CREATE TABLE employee_info (id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
age INT, email VARCHAR(100));
データの挿入：
-- テーブルにランダムなデータを挿入
INSERT INTO employee_info (name, age, email)
SELECT CONCAT('User', LPAD(ROW_NUMBER() OVER (), 5, '0')),
FLOOR(RAND() * 50) + 20,
CONCAT('user', LPAD(ROW_NUMBER() OVER (), 5, '0'), '@xyz.com')
FROM information_schema.tables
LIMIT 100;
これにより、名前、年齢、メールの属性を持つemployee_infoという名前のテーブルが作成されます。
データの表示：
SELECT *
FROM employee_info;
出力：
インデックスの作成と削除
インデックスを作成するためには、次のようにCREATEコマンドを使用することができます。
構文：
CREATE INDEX index_name ON TABLE_NAME (COLUMN_NAME);
上記のクエリでは、index_nameはインデックスの名前、table_nameはテーブルの名前、column_nameはインデックスを適用したい列の名前です。
例：
CREATE INDEX age_index ON employee_info (age);
同じテーブル内の複数の列に対してもインデックスを作成することができます。
CREATE INDEX index_name ON TABLE_NAME (col1,
col2,
col3, ....);
ユニークインデックス：特定の列に対して重複した値を格納することを許さないユニークインデックスも作成することができます。これによりデータの整合性が維持され、パフォーマンスもさらに向上します。
CREATE UNIQUE INDEX index_name ON TABLE_NAME (COLUMN_NAME);
注意：PRIMARY_KEYとUNIQUEの列には自動的にインデックスが作成されます。手動で作成する必要はありません。
インデックスの削除：
特定のインデックスをテーブルから削除するには、DROPコマンドを使用することができます。
DROP INDEX index_name ON TABLE_NAME;
インデックスを削除するには、インデックスとテーブルの名前を指定する必要があります。
インデックスの表示：
テーブルに存在するすべてのインデックスを表示することもできます。
構文：
SHOW INDEX
FROM TABLE_NAME;
例：
SHOW INDEX
FROM employee_info;
出力：
インデックスの更新
以下のコマンドは既存のテーブルに新しいインデックスを作成します。
構文：
ALTER TABLE TABLE_NAME ADD INDEX index_name (col1, col2, col3, ...);
注意：ALTERはANSI SQLの標準コマンドではありません。そのため、他のデータベース間で異なる場合があります。
例：
ALTER TABLE employee_info ADD INDEX name_index (name);
SHOW INDEX
FROM employee_info;
出力：
上記の例では、既存のテーブルに新しいインデックスを作成しました。ただし、既存のインデックスを変更することはできません。そのため、まず古いインデックスを削除し、その後で新しい修正済みのインデックスを作成する必要があります。
例：
DROP INDEX name_index ON employee_info;
CREATE INDEX name_index ON employee_info (name, email);
SHOW INDEX
FROM employee_info ;
出力：
まとめ
この記事では、SQLのインデックスについて基本的な理解をカバーしました。より多くのインデックスはパフォーマンスに悪影響を及ぼすため、インデックスを狭く保つ（つまり、数列に限定する）ことが推奨されています。インデックスはSELECTクエリとWHERE句の速度を向上させますが、挿入および更新ステートメントの速度を遅くします。そのため、頻繁に使用される列にのみインデックスを適用することが良い方法です。
それまで、読み続けて学び続けてください。アーヤン・ガーグは、電気工学の学士号を持つ学生であり、現在大学の最終学年です。彼の興味はウェブ開発と機械学習の分野にあります。彼はこの興味を追求し、これらの方向性でさらに活動することを熱望しています。
We will continue to update VoAGI; if you have any questions or suggestions, please contact us!
Was this article helpful?
93 out of 132 found this helpful
Related articles
