MySQL インデックス

MySQL インデックスは、データベースクエリの速度とパフォーマンスを向上させるためのデータ構造です。

MySQL インデックスの作成は MySQL の効率的な動作にとって重要であり、インデックスは MySQL の検索速度を大幅に向上させることができます。

MySQL インデックスは書籍の索引に似ており、データ行を指すポインタを格納することで、テーブル内の特定のデータをすばやく特定してアクセスできます。

たとえるなら、適切に設計されインデックスが使用された MySQL がランボルギーニだとしたら、インデックスを設計・使用していない MySQL は人力三輪車のようなものです。

漢和辞典の目次ページ(索引)にたとえると、ピンイン、画数、部首などの順に並べられた目次(索引)から、必要な漢字をすばやく見つけることができます。

インデックスには、単一列インデックスと複合インデックスがあります:

  • 単一列インデックスとは、1つのインデックスが単一の列のみを含むものであり、1つのテーブルに複数の単一列インデックスを持つことができます。
  • 複合インデックスとは、1つのインデックスが複数の列を含むものです。

インデックスを作成するときは、そのインデックスが SQL クエリ文の条件(通常は WHERE 句の条件)に適用されていることを確認する必要があります。

実際には、インデックスも1つのテーブルであり、主キーとインデックスフィールドを保存し、実テーブルのレコードを指し示します。

インデックスはクエリのパフォーマンスを向上させることができますが、次の点にも注意する必要があります:

  • インデックスは追加のストレージ領域を占有します。
  • テーブルに対して挿入、更新、削除の操作を行うとき、インデックスのメンテナンスが必要であり、パフォーマンスに影響を与える可能性があります。
  • 過剰または不適切なインデックスはパフォーマンスの低下を引き起こす可能性があるため、インデックスは慎重に選択して計画する必要があります。

通常のインデックス

インデックスはクエリの速度を大幅に向上させることができ、特に大規模なテーブルで検索する場合に有効です。インデックスを使用することで、MySQL はクエリ条件を満たすデータ行を直接特定でき、テーブル全体を1行ずつスキャンする必要はありません。

インデックスの作成

使用CREATE INDEXステートメントで通常のインデックスを作成できます。

通常のインデックスは最も一般的なインデックス型であり、テーブル内のデータのクエリを高速化するために使用されます。

CREATE INDEXの構文:

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE INDEX: 通常のインデックスを作成するためのキーワード。
  • index_name: 作成するインデックスの名前を指定します。インデックス名はテーブル内で一意である必要があります。
  • table_name: どのテーブルにインデックスを作成するかを指定します。
  • (column1, column2, ...): インデックスを作成するテーブルの列名を指定します。1つまたは複数の列をインデックスの組み合わせとして指定できます。これらの列のデータ型は通常、数値、テキスト、または日付です。
  • ASCとDESC(オプション): インデックスの並べ替え順序を指定します。デフォルトでは、インデックスは昇順(ASC)で並べ替えられます。

次の例では、id、name、age 列を含む students という名前のテーブルがあると仮定し、name 列に通常のインデックスを作成します。

CREATE INDEX idx_name ON students (name);

上記のステートメントは、students テーブルの name 列に idx_name という名前の通常のインデックスを作成します。これにより、名前で検索するクエリのパフォーマンスが向上します。

注意すべき点として、テーブル内のデータ量が多い場合、インデックスの作成には時間がかかることがありますが、一度作成が完了すると、クエリのパフォーマンスは大幅に向上します。

テーブル構造の変更(インデックスの追加)

私たちは使用できますALTER TABLEコマンドを使用すると、既存のテーブルにインデックスを作成できます。

ALTER TABLE を使用すると、テーブルの構造を変更できます。これにはインデックスの追加、変更、削除が含まれます。

ALTER TABLE でインデックスを作成する構文:

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • ALTER TABLE: テーブル構造を変更するためのキーワード。
  • table_name: 変更するテーブルの名前を指定します。
  • ADD INDEX: インデックスを追加する句。ADD INDEX通常のインデックスを作成するために使用されます。
  • index_name: 作成するインデックスの名前を指定します。インデックス名はテーブル内で一意である必要があります。
  • (column1, column2, ...): インデックスを作成するテーブルの列名を指定します。1つまたは複数の列をインデックスの組み合わせとして指定できます。これらの列のデータ型は通常、数値、テキスト、または日付です。
  • ASCとDESC(オプション): インデックスの並べ替え順序を指定します。デフォルトでは、インデックスは昇順(ASC)で並べ替えられます。

以下は例です。既存の employees という名前のテーブルに通常のインデックスを作成します:

ALTER TABLE employees
ADD INDEX idx_age (age);

上記のステートメントは、employees テーブルの age 列に idx_age という名前の通常のインデックスを作成します。

テーブル作成時に直接指定

テーブルを作成するときには、CREATE TABLEステートメントでインデックスを直接指定して、テーブルとインデックスの組み合わせを作成できます。

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
  • CREATE TABLE: 新しいテーブルを作成するためのキーワード。
  • table_name: 作成するテーブルの名前を指定します。
  • (column1 data_type, column2 data_type, ...): テーブルの列名とデータ型を定義します。1つまたは複数の列をインデックスの組み合わせとして指定できます。これらの列のデータ型は通常、数値、テキスト、または日付です。
  • INDEX: 通常のインデックスを作成するためのキーワード。
  • index_name: 作成するインデックスの名前を指定します。インデックス名はテーブル内で一意である必要があります。
  • (column1, column2, ...): インデックスを作成するテーブルの列名を指定します。1つまたは複数の列をインデックスの組み合わせとして指定できます。これらの列のデータ型は通常、数値、テキスト、または日付です。
  • ASCとDESC(オプション): インデックスの並べ替え順序を指定します。デフォルトでは、インデックスは昇順(ASC)で並べ替えられます。

以下は例です。students という名前のテーブルを作成し、age 列に通常のインデックスを作成します。

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_age (age)
);

上記の例では、students テーブルの age 列に idx_age という名前の通常のインデックスを作成しました。

インデックス削除の構文

私たちは使用できますDROP INDEXステートメントを使用してインデックスを削除します。

DROP INDEX の構文:

DROP INDEX index_name ON table_name;
  • DROP INDEX: インデックスを削除するためのキーワード。
  • index_name: 削除するインデックスの名前を指定します。
  • ON table_name: どのテーブルからインデックスを削除するかを指定します。

使用ALTER TABLEステートメントでインデックスを削除する構文は次のとおりです:

ALTER TABLE table_name
DROP INDEX index_name;
  • ALTER TABLE: テーブル構造を変更するためのキーワード。
  • table_name: 変更するテーブルの名前を指定します。
  • DROP INDEX: インデックスを削除するための句。
  • index_name: 削除するインデックスの名前を指定します。

次の例では、employees という名前のテーブルがあり、age 列に idx_age という名前のインデックスがあると仮定します。このインデックスを削除します:

DROP INDEX idx_age ON employees;

または次を使用ALTER TABLEステートメント:

ALTER TABLE employees
DROP INDEX idx_age;

これらの2つのコマンドはどちらも、employees テーブルから idx_age という名前のインデックスを削除します。

そのインデックスが存在しない場合、コマンドを実行するとエラーが発生します。したがって、インデックスを削除する前に、そのインデックスが存在するかどうかを確認するか、起こり得るエラーに対処するためのエラーハンドリングメカニズムを使用することをお勧めします。


一意インデックス

MySQL では、次を使用できますCREATE UNIQUE INDEXステートメントを使用して一意インデックスを作成できます。

一意インデックスは、インデックス内の値が一意であることを保証し、重複する値を許可しません。

インデックスの作成

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE UNIQUE INDEX: 一意インデックスを作成するためのキーワードの組み合わせ。
  • index_name: 作成する一意インデックスの名前を指定します。インデックス名はテーブル内で一意である必要があります。
  • table_name: どのテーブルに一意インデックスを作成するかを指定します。
  • (column1, column2, ...): インデックスを作成するテーブルの列名を指定します。1つまたは複数の列をインデックスの組み合わせとして指定できます。これらの列のデータ型は通常、数値、テキスト、または日付です。
  • ASCとDESC(オプション): インデックスの並べ替え順序を指定します。デフォルトでは、インデックスは昇順(ASC)で並べ替えられます。

以下は一意インデックスを作成する例です: id と email 列を含む employees という名前のテーブルがあると仮定します。ここで、各従業員の電子メールアドレスが一意であることを保証するために、email 列に一意インデックスを作成します。

CREATE UNIQUE INDEX idx_email ON employees (email);

上記の例では、employees テーブルの email 列に idx_email という名前の一意インデックスを作成します。

テーブル構造の変更によるインデックスの追加

私たちは使用できますALTER TABLEコマンドを使用して一意インデックスを作成できます。

ALTER TABLEコマンドを使用すると、既存のテーブル構造を変更でき、新しいインデックスの追加も含まれます。

ALTER table table_name 
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);
  • ALTER TABLE: テーブル構造を変更するためのキーワード。
  • table_name: 変更するテーブルの名前を指定します。
  • ADD CONSTRAINT: これは、制約(一意インデックスを含む)を追加するためのキーワードです。
  • unique_constraint_name : 作成する一意インデックスの名前を指定します。制約名はテーブル内で一意である必要があります。
  • UNIQUE (column1, column2, ...): インデックスを作成するテーブルの列名を指定します。1つまたは複数の列をインデックスの組み合わせとして指定できます。これらの列のデータ型は通常、数値、テキスト、または日付です。

以下は使用例です。ALTER TABLE一意インデックスを作成するコマンドの例:employees という名前のテーブルがあり、id 列と email 列を含むとします。ここで、各従業員のメールアドレスが一意であることを保証するために、email 列に一意インデックスを作成したいとします。

ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);

上記の例では、employees テーブルの email 列に idx_email という名前の一意インデックスを作成します。

注意:テーブルにすでに重複した email 値がある場合、一意インデックスの追加は失敗します。一意インデックスを作成する前に、テーブルの email 列に重複する値がないことを確認する必要があります。

テーブル作成時に直接指定

テーブルを作成すると同時に、CREATE TABLEステートメントでUNIQUEキーワードを使用して一意インデックスを作成できます。

これにより、テーブル作成時に一意インデックス制約も同時に定義されます。

CREATE TABLEステートメントで一意インデックスを作成する構文:

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
  • CREATE TABLE: 新しいテーブルを作成するためのキーワード。
  • table_name: 作成するテーブルの名前を指定します。
  • (column1 data_type, column2 data_type, ...): テーブルの列名とデータ型を定義します。1つまたは複数の列をインデックスの組み合わせとして指定できます。これらの列のデータ型は通常、数値、テキスト、または日付です。
  • CONSTRAINT: 制約を追加するためのキーワード。
  • index_name: 作成する一意インデックスの名前を指定します。制約名はテーブル内で一意である必要があります。
  • UNIQUE (column1, column2, ...): インデックスを作成するテーブルの列名を指定します。
  • ASCとDESC(オプション): インデックスの並べ替え順序を指定します。デフォルトでは、インデックスは昇順(ASC)で並べ替えられます。

以下は、テーブル作成時に一意インデックスを作成する例です:id、name、email 列を含む employees という名前のテーブルを作成するとします。email 列の値を一意にしたいので、テーブル作成時に一意インデックスを定義します。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100) UNIQUE
);

この例では、email 列の後に UNIQUE キーワードが追加されているため、email 列は一意インデックスとして定義されています。

注意:UNIQUEキーワードを使用すると、インデックス名が自動的に生成されます。必要に応じてインデックス名を指定することもできます。


ALTER コマンドを使用したインデックスの追加と削除

データテーブルにインデックスを追加する方法は4つあります:

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):このステートメントは主キーを追加します。主キー列の値は一意である必要があります。主キーの列リストは1つまたは複数の列にすることができ、NULL 値を含めることはできません。 。
  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):このステートメントは、一意でなければならない値でインデックスを作成します(NULL を除きます。NULL は複数回出現する可能性があります)。
  • ALTER TABLE tbl_name ADD INDEX index_name (column_list):通常のインデックスを追加します。インデックス値は複数回出現できます。
  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):このステートメントは、インデックスを FULLTEXT として指定し、全文インデックスに使用します。

以下の例は、テーブルにインデックスを追加するものです。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

ALTER コマンドで DROP 句を使用してインデックスを削除することもできます。次の例を試してインデックスを削除してください:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

ALTER コマンドを使用した主キーの追加と削除

主キーは列に適用されます(1つの列または複数の列の複合主キーにできます)。主キーを追加するときは、その主キーがデフォルトで NULL ではない(NOT NULL)ことを確認する必要があります。例は次のとおりです:

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

ALTER コマンドを使用して主キーを削除することもできます:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

主キーを削除するときは PRIMARY KEY を指定するだけですが、インデックスを削除するときはインデックス名を知っている必要があります。


インデックス情報の表示

次のコマンドを使用してSHOW INDEXテーブル内の関連するインデックス情報を一覧表示できます。

次のものを追加することで\G出力情報をフォーマットできます。

SHOW INDEXステートメント::

mysql> SHOW INDEX FROM table_name\G
........
  • SHOW INDEX: インデックス情報を表示するためのキーワード。
  • FROM table_name: インデックス情報を確認するテーブルの名前を指定します。
  • \G: 出力情報をフォーマットします。

上記のコマンドを実行すると、指定したテーブル内のすべてのインデックスの詳細情報が表示されます。インデックス名(Key_name)、インデックス列(Column_name)、一意インデックスかどうか(Non_unique)、並べ替え方法(Collation)、インデックスの基数(Cardinality)などが含まれます。

その他の拡張