MySQLでインデックスを使用する方法

2年 ago

海斗, 葵

12 minutes

著者は「Write for Donations」プログラムの一環として、寄付金を受け取るためにDiversity in Tech Fundを選びました。

イントロダクション

関係データベースは、数百万行を有する大規模なデータベースを含む、あらゆるサイズのデータを扱うことができます。構造化クエリ言語（SQL）は、特定の基準に基づいてデータベーステーブル内の特定の行を簡潔で直感的な方法で検索する手段を提供します。データベースのサイズが大きくなると、それら内の特定の行を見つけることはますます困難になり、まるで干し草の中から針を探すような感じです。

データベースが様々なクエリ条件を受け入れる能力は、データベースエンジンにとって、どのクエリが最も一般的であるかを予測することを難しくします。エンジンは、データベーステーブルで効率的に行を見つける準備をしなければなりませんが、データ量が増えると検索パフォーマンスに影響が出ることがあります。データセットが大きければ大きいほど、データベースエンジンはクエリに一致するドキュメントを素早く見つけることが難しくなります。

データベース管理者は、インデックスを使用してデータベースエンジンをサポートし、そのパフォーマンスを改善することができます。

このチュートリアルでは、インデックスとは何か、どのように作成するか、そしてデータベースのクエリに使用されるかについて学びます。

前提条件

このガイドに従うためには、SQLベースの関係データベース管理システム（RDBMS）が動作するコンピュータが必要です。このガイドの手順と例は、以下の環境で検証されました。

A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.
Basic familiarity with executing SELECT queries to retrieve data from the database as described in our How To SELECT Rows FROM Tables in SQL guide.

Note

注意：多くのRDBMSは独自のSQLの実装を使用していることに注意してください。このチュートリアルで説明されているコマンドは、ほとんどのRDBMSで動作しますが、インデックスは標準のSQL構文の一部ではないため、MySQL以外のシステムでテストする場合は、正確な構文や出力が異なる可能性があります。

以下のようなサンプルデータがロードされたテーブルを含んだデータベースも必要です。インデックスの使用方法を練習するためには、MySQLに接続し、このガイド全体で使用されるテストデータベースを作成する詳細について、以下の「MySQLへの接続」と「サンプルデータベースの設定」セクションを参照してください。

MySQLに接続し、サンプルデータベースのセットアップを行う。

このセクションでは、MySQLサーバーに接続し、サンプルデータベースを作成して、このガイドの例に従うことができます。

もしSQLデータベースシステムがリモートサーバーで動作している場合、ローカルマシンからサーバーにSSH接続してください。

ssh sammy@your_server_ip

その後、MySQLサーバープロンプトを開き、あなたのMySQLユーザーアカウントの名前で” sammy “を置き換えてください。

mysql -u sammy -p

インデックスという名前のデータベースを作成してください。

CREATE DATABASE indexes;

データベースが正常に作成された場合、次のような出力が表示されます。

Output

Query OK, 1 row affected (0.01 sec)

インデックスデータベースを選択するには、以下のUSE文を実行してください。

USE indexes;

以下の出力を受け取るでしょう。

Output

Database changed

データベースを選択した後、その中にサンプルテーブルを作成することができます。このガイドでは、架空の従業員データベースを使用して、現在の従業員とその勤務デバイスの詳細を保存します。

従業員テーブルには、データベース内の従業員に関する簡略化されたデータが格納されます。次のカラムが含まれます。

employee_id: This column holds the employee identifier, represented by the int data type. This column will become the table’s primary key, with each value becoming a unique identifier for its respective row.
first_name: This column holds the first name of each employee, expressed using the varchar data type with a maximum of 50 characters.
last_name: This column holds the last name of each employee, expressed using the varchar data type with a maximum of 50 characters.
device_serial: This column holds the serial number of the computer assigned to the employee, expressed using the varchar data type with a maximum of 15 characters.
salary: This column holds each employee’s salary, expressed using the int data type storing numerical data.

以下のコマンドを使用して、サンプルテーブルを作成してください。

CREATE TABLE employees (
employee_id int,
first_name varchar(50),
last_name varchar(50),
device_serial varchar(15),
salary int
);

もし以下の出力が表示されたら、テーブルが作成されています。

Output

Query OK, 0 rows affected (0.00 sec)

その後、次のINSERT INTO操作を実行して、サンプルデータを従業員テーブルにロードします。

INSERT INTO employees VALUES
(1, ‘John’, ‘Smith’, ‘ABC123’, 60000),
(2, ‘Jane’, ‘Doe’, ‘DEF456’, 65000),
(3, ‘Bob’, ‘Johnson’, ‘GHI789’, 70000),
(4, ‘Sally’, ‘Fields’, ‘JKL012’, 75000),
(5, ‘Michael’, ‘Smith’, ‘MNO345’, 80000),
(6, ‘Emily’, ‘Jones’, ‘PQR678’, 85000),
(7, ‘David’, ‘Williams’, ‘STU901’, 90000),
(8, ‘Sarah’, ‘Johnson’, ‘VWX234’, 95000),
(9, ‘James’, ‘Brown’, ‘YZA567’, 100000),
(10, ‘Emma’, ‘Miller’, ‘BCD890’, 105000),
(11, ‘William’, ‘Davis’, ‘EFG123’, 110000),
(12, ‘Olivia’, ‘Garcia’, ‘HIJ456’, 115000),
(13, ‘Christopher’, ‘Rodriguez’, ‘KLM789’, 120000),
(14, ‘Isabella’, ‘Wilson’, ‘NOP012’, 125000),
(15, ‘Matthew’, ‘Martinez’, ‘QRS345’, 130000),
(16, ‘Sophia’, ‘Anderson’, ‘TUV678’, 135000),
(17, ‘Daniel’, ‘Smith’, ‘WXY901’, 140000),
(18, ‘Mia’, ‘Thomas’, ‘ZAB234’, 145000),
(19, ‘Joseph’, ‘Hernandez’, ‘CDE567’, 150000),
(20, ‘Abigail’, ‘Smith’, ‘FGH890’, 155000);

データベースは成功メッセージで応答します。 (Deetabesu wa seikou messēji de ōtō shimasu.)

Output

Query OK, 20 rows affected (0.010 sec) Records: 20 Duplicates: 0 Warnings: 0

Note

注意：データセットは指標のパフォーマンスへの影響を直接的に示すほど大きくありません。ただし、このデータセットはMySQLがクエリを実行し結果を取得するためにトラバースする行の数を制限するためにインデックスを使用する方法を示します。

それでは、あなたはこのガイドの残りを追いながらMySQLでインデックスを使用し始める準備ができています。

インデックスへの導入

通常、MySQLデータベースにクエリを実行する場合、データベースはテーブルの全ての行を一つずつ確認しなければなりません。例えば、「Smith」と一致する従業員の姓を検索したり、「月給が10万ドル以上の従業員をすべて検索したい場合があります。テーブルの全ての行が条件に合致するか確認するため、一つずつ調べていきます。条件に合致する場合は、その行は返される行のリストに追加されます。合致しない場合は、MySQLはテーブル全体を見るまで次の行をスキャンします。

この行を一致させるための手法は効果的ですが、テーブルのサイズが大きくなるにつれて遅くなり、リソースを消費する可能性があります。その結果、大きなテーブルや頻繁なデータアクセスが必要なクエリには適していないかもしれません。

大きなテーブルやクエリのパフォーマンス問題を解消するには、インデックスを使用することができます。インデックスは、テーブルの行とは別に、データのソートされたサブセットのみを格納するユニークなデータ構造です。特定のフィールドまたはフィールドのセットに対して値を検索したり、順序を付ける際に、データベースエンジンがより迅速かつ効率的に動作することが可能になります。

従業員テーブルを例に挙げると、典型的なクエリの1つは従業員の姓で検索するものです。インデックスがない場合、MySQLはテーブルからすべての従業員を取り出し、姓がクエリと一致するか確認します。しかし、インデックスを使用すると、MySQLは姓の別々のリストを保持し、メインテーブル内の対象従業員へのポインターのみを含みます。そのインデックスを使用して、テーブル全体をスキャンせずに結果を取得します。

インデックスは電話帳の類似物と考えることができます。たとえば、本の中で名前が「John Smith」という人を見つけるためには、まず「S」で始まる名前の人々がリストされている正しいページにめくり、そして「Sm」で始まる名前の人々がリストされているページを探します。このロジックに従えば、求めている人とは一致しないことがわかっているエントリーを迅速に削除することができます。この方法が機能するのは、電話帳のデータがアルファベット順に並べられているからであり、データベースに直接格納されているデータの場合はほとんどありません。データベースエンジンのインデックスは、電話帳に似た役割を果たし、データへのアルファベット順の参照を保持することで、必要な行を迅速に見つけるのを支援します。

MySQLのインデックスを使用することには、複数の利点があります。最も一般的な利点は、WHERE条件や比較に基づくクエリの処理を高速化すること、ORDER BY句を使用してデータをより速くソートすること、そして値の一意性を強制することです。

ただし、特定の状況ではインデックスの使用はデータベースのピークパフォーマンスを低下させる場合があります。インデックスはデータの取得を高速化するために設計されており、テーブルデータと一緒に保存される追加のデータ構造を使用して実装されます。これらの構造はデータベースの変更ごとに最新の状態に保たれる必要があり、そのためにINSERT、UPDATE、DELETEクエリのパフォーマンスが低下することがあります。頻繁に変更される大規模なデータセットでは、SELECTクエリの速度向上による利点よりも、データベースへのデータ書き込みクエリの明らかに遅いパフォーマンスの方が重視されることがあります。

インデックスは、アプリケーションのパフォーマンスが低下し始めた場合などの明確な必要性があるときにのみ作成することが推奨されます。作成するインデックスの選択に際しては、最も頻繁に実行され、時間が最もかかるクエリを考慮し、それらに最も恩恵をもたらすクエリ条件に基づいてインデックスを構築してください。

Note

注意：このガイドは、MySQLにおけるデータベースインデックスのトピックを紹介し、一般的な応用事例やインデックスの種類を説明することを目的としています。データベースエンジンは、データベースのパフォーマンスを向上させるためにインデックスを使用するさまざまな複雑なシナリオをサポートしていますが、それはこのガイドの範囲外です。より詳しいデータベースの機能については、公式のMySQLドキュメントのインデックスに関する章をご参照ください。

以下の手順では、さまざまなシナリオに対して異なるタイプのインデックスを作成します。クエリでインデックスが使用されているかどうかを確認する方法も学びます。最後に、必要な場合にはインデックスの削除方法も学びます。

単一カラムインデックスを使用

単一列インデックスは、クエリのパフォーマンスを最適化するために使用できる最も一般的で直感的なインデックス形式です。このタイプのインデックスは、データベースが単一列に基づいてデータセットをフィルタリングするクエリの処理速度を向上させるのに役立ちます。単一列に作成されたインデックスは、= 演算子を使用した正確な一致や、> または < 演算子を使用した比較を含む多くの条件付きクエリの処理を高速化することができます。

前のステップで作成したサンプルデータベースにはインデックスがありません。インデックスを作成する前に、従業員テーブルへのSELECTクエリでWHERE句を使用してテーブルからデータのサブセットのみを要求した場合、データベースの処理方法をまずテストします。

給与がちょうど$100,000である従業員を見つけたい場合は、次のクエリを実行してください。

SELECT * FROM employees WHERE salary = 100000;

WHERE句は、給与がリクエストされた値と一致する従業員の正確な一致を要求します。この例では、データベースは以下のように応答します。

Output

Note

注意：上記の出力に示されているように、データベースは発行されたクエリにほとんど即座に応答しました。データベースにサンプルの行がほんのわずかしかない場合、インデックスを使用してもクエリのパフォーマンスに目に見える影響はありません。しかし、大規模なデータセットの場合、クエリを実行した後にデータベースから報告されるクエリの実行時間には大きな変化が観察されます。

クエリの出力から判断すると、データベースエンジンがテーブル内の一致する行を見つけるための問題にどのようにアプローチしたのかはわかりません。ただし、MySQLにはクエリプラン（エンジンがクエリを実行する方法）に対する洞察を得る方法が提供されています：EXPLAIN文。

SELECT クエリのクエリプランにアクセスするには、以下を実行してください。

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

EXPLAINコマンドはMySQLに対してSELECTクエリを実行するよう指示しますが、結果を返す代わりに、データベースエンジンがクエリを内部的にどのように処理したかについての情報を表示します。

以下に示す実行計画は、次のようになる予定です（テーブルによって若干異なる場合もあります）。

Output

この表の出力では、列はクエリの実行の多くの側面を説明しています。ご使用のMySQLのバージョンによっては、追加の列が含まれている場合もありますが、このチュートリアルでは以下の最も重要な情報が表示されます。

possible_keys lists the indexes that MySQL considered for use. In this case, there are none (NULL).
key describes the index that MySQL decided to use when performing the query. In this case, no index was used (NULL).
rows shows the number of rows that MySQL had to analyze individually before returning the results. Here, it’s 20, which corresponds to the number of all possible rows in the table. This means MySQL had to scan every row in the employees table to find the single one returned.
Extra shows additional, descriptive information on the query plan. In this example, the Using where annotation means that the database filtered results directly from within the table using the WHERE statement.

インデックスが存在しないため、データベースは1つの行を取得するために20行をスキャンしなければなりませんでした。テーブルに何百万もの行が含まれている場合、MySQLは1つずつそれらを通過しなければならず、クエリのパフォーマンスが低下します。

Note

注意：新しいMySQLのバージョンでは、EXPLAINを使用する際に出力に1行のセットと1つの警告が表示されますが、古いMySQLのバージョンやMySQL互換のデータベースでは単に1行のセットが表示されることがあります。警告は問題の兆候ではありません。MySQLは、クエリプランに関するさらに詳細な情報を提供するために警告メカニズムを使用しています。このチュートリアルの範囲外でこれらの追加情報を使用することがあります。MySQLのドキュメントの「拡張EXPLAIN出力形式」ページでその動作についてさらに学ぶことができます。

さっき実行したSELECTクエリでは、クエリ条件としてexact条件が使用されました。つぎは、データベースが比較条件でも同様に動作するかどうかを確認しましょう。給与が70000未満の従業員を取得してみてください。

SELECT * FROM employees WHERE salary < 70000;

今回のデータベースでは、ジョン・スミスとジェーン・ドーの2つの行が返されました。

Output

+————-+————+———–+—————+——–+ | employee_id | first_name | last_name | device_serial | salary | +————-+————+———–+—————+——–+ | 1 | John | Smith | ABC123 | 60000 | | 2 | Jane | Doe | DEF456 | 65000 | +————-+————+———–+—————+——–+ 8 rows in set (0.000 sec)

ただし、以下のクエリの実行を理解するためにEXPLAINを使用する場合

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

前のクエリとほぼ同じテーブルになっていることに気づくでしょう。

Output

前回と同様に、MySQLはクエリのWHERE句を通じて要求された行を見つけるためにテーブルの全20行をスキャンしました。テーブル全体の行数に比べて返された行の数は少ないですが、データベースエンジンはそれらを見つけるために多くの作業を行わなければなりません。

その問題を解決するために、従業員テーブルの給与データに特化した、さらに最適化されたデータ構造をMySQLに作成することができます。それには、以下のクエリを実行してください。

CREATE INDEX salary ON employees(salary);

CREATE INDEX文の構文では、以下が必要です。

The index name, which in this case is salary. The name must be unique within a single table but can repeat across different tables in the same database.
The table name the index is created for. In this case, it is employees.
The list of columns for which the index is created. Here, you’re using a single column called salary to build the index.

Note

注意：MySQLユーザーの権限によっては、CREATE INDEXコマンドの実行時にエラーが発生することがあります：ERROR 1142 (42000): INDEXコマンドは、テーブル’employees’に対して’user’@’host’ユーザーに拒否されました。ユーザーにINDEXの権限を付与するには、MySQLにrootとしてログインし、以下のコマンドを実行してください。必要に応じてMySQLのユーザー名とホストを置き換えてください：
GRANT INDEX on *.* TO ‘sammy’@’localhost’;
FLUSH PRIVILEGES;

ユーザーの権限を更新したら、rootとしてログアウトし、ユーザーとして再ログインし、CREATE INDEXステートメントを再実行してください。

データベースは、インデックスが正常に作成されたことを確認します。

Output

Query OK, 0 rows affected (0.024 sec) Records: 0 Duplicates: 0 Warnings: 0

インデックスが設定されている場合、以前のクエリを繰り返して何か変更があるかどうかを確認してみてください。まず、給与が100000である単一の従業員を取得してください。

SELECT * FROM employees WHERE salary = 100000;

結果は以前と同様で、James Brownのみが返されます。

Output

ただし、MySQLにクエリのアプローチ方法を説明するように依頼すると、以前とは異なる違いが見られます。以下のようにEXPLAINクエリを実行してください。

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

今回、出力はこのように表示されます。

Output

MySQLは、possible_keysに表示される1つの可能なキーから、作成したsalaryという名前のインデックスを使用することを宣言しました。行の列は20ではなく1を表示しています。インデックスを使用するため、データベースは全ての行をスキャンする必要がなく、即座に要求された単一の行を返すことができました。Extra列は、「WHEREを使用する」とは記載されていません。なぜなら、メインのテーブルを反復し、クエリ条件に対して各行をチェックする必要がなかったからです。

小さなサンプルデータセットでは、インデックスを使用しても影響はあまり目立ちません。しかし、データベースが結果を取得するための作業量が少なくなり、この変更の効果は大きなデータセットでは重要になります。

同じインデックスが使用されるかどうかを確認するために、給与が70000未満の従業員を取得する2番目のクエリをもう一度実行してみてください。

以下のクエリを実行してください。

SELECT * FROM employees WHERE salary < 70000;

ジョン・スミスとジェーン・ドウのために同じ2行が返されます。

Output

ただし、次のようにEXPLAINを使用する場合は、

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

同じクエリの前回の実行とは異なるテーブルになります。

Output

キーカラムには、MySQLがクエリを実行するためにインデックスを使用したことが示されています。行数では、結果を返すために分析された行は2行だけでした。今回、Extraカラムには「Using index condition」と表示されており、これはこの特定の場合においてMySQLがインデックスを使用してフィルタリングし、既に一致した行を取得するために主テーブルを使用したことを意味しています。

Note

注意： MySQLでは、たとえインデックスが存在し使用できる状態であっても、時折その使用を選択しないことがあります。たとえば、以下のクエリを実行した場合：

EXPLAIN SELECT * FROM employees WHERE salary < 140000;

salaryがpossible_keysにリストされているにもかかわらず、key列が空（NULL）であることから、MySQLはインデックスを使用しないことを決定したことがわかります。これは20行のスキャンが行われていることで確認できます。データベースのクエリプランナーは、クエリごとに可能なインデックスを分析し、最速の実行パスを決定します。インデックスへのアクセスコストがその使用による利点を上回る場合（たとえば、クエリが元のテーブルデータの重要な部分を返す場合）、データベースはフルテーブルスキャンの方が実際にはより速いと判断することがあります。
同様に、Extra列の注釈（Using index conditionやUsing whereなど）は、データベースエンジンがクエリを実行した方法についての詳細を示します。文脈によっては、データベースはクエリの実行方法を別の方法に選択することがあり、Using index conditionの注釈がない場合や、他の注釈がある場合があります。これはインデックスが適切に使用されていないことを意味するのではなく、単にデータベースが行へのアクセス方法をより効率的と判断したということです。

このセクションでは、単一列のインデックスを作成し使用して、単一列に対するフィルタリングを必要とするSELECTクエリのパフォーマンスを向上させました。次のセクションでは、インデックスが与えられた列の値の一意性を保証するためにどのように使用できるかを探索します。

重複データを防止するために、ユニークインデックスを使用する。

前のセクションで探求した通り、インデックスの一般的な使用法の一つは、データベースエンジンが同じ結果を得るために行う作業を少なくすることにより、データの高効率な取得です。また、インデックスが定義されているテーブルの一部分にデータが繰り返し出現しないようにすることも目的としています。これがユニークインデックスの役割です。

重複した値を避けることは、データの整合性を保証する上でしばしば必要とされます。論理的または技術的な観点からともにです。例えば、同じ社会保障番号を使用する異なる人々が存在してはいけませんし、オンラインシステムでは同じユーザー名やメールアドレスで複数のユーザーが登録できないようにするべきです。

このガイドの従業員テーブルの例では、割り当てられたデバイスのシリアル番号は重複してはならないフィールドです。もし重複してしまった場合、それは二人の従業員が同じコンピュータを与えられたことを意味します。しかしながら、この段階では容易に同じシリアル番号を持つ新しい従業員を挿入することができます。

すでに使用されているデバイスシリアル番号を持つ別の従業員を挿入してみてください。

INSERT INTO employees VALUES (21, ‘Sammy’, ‘Smith’, ‘ABC123’, 65000);

データベースが応じて行を挿入し、成功を通知します。

Output

Query OK, 1 row affected (0.009 sec)

ただし、あなたが現在、ABCD123コンピュータを使用してデータベース内の従業員を検索する場合、

SELECT * FROM employees WHERE device_serial = ‘ABC123’;

結果として、2つの異なる人物になります。

Output

+————-+————+———–+—————+——–+ | employee_id | first_name | last_name | device_serial | salary | +————-+————+———–+—————+——–+ | 1 | John | Smith | ABC123 | 60000 | | 21 | Sammy | Smith | ABC123 | 65000 | +————-+————+———–+—————+——–+ 2 rows in set (0.000 sec)

従業員データベースを有効な状態に保つ予定外の行動です。新しく作成された行を削除して、この変更を元に戻しましょう。

DELETE FROM employees WHERE employee_id = 21;

以前のSELECTクエリを再実行することで、それを確認できます。

SELECT * FROM employees WHERE device_serial = ‘ABC123’;

再び、シリアル番号がABC123のデバイスはJohn Smithだけが使用しています。

Output

そのようなミスからデータベースを保護するために、device_serial列にユニークインデックスを作成することができます。

それをするために、実行してください。

CREATE UNIQUE INDEX device_serial ON employees(device_serial);

インデックスを作成する際にUNIQUEキーワードを追加すると、データベースはdevice_serial列の値が繰り返されないように確認します。ユニークインデックスでは、テーブルに追加されるすべての新しい行は、列の値が制約を満たしているかどうかを確認するためにインデックスと照合されます。

データベースはインデックスの作成を確認します。

Output

Query OK, 0 rows affected (0.021 sec) Records: 0 Duplicates: 0 Warnings: 0

さて、テーブルに重複したエントリを追加できるか確認しましょう。以前成功したINSERTクエリを再度実行してみてください。

INSERT INTO employees VALUES (21, ‘Sammy’, ‘Smith’, ‘ABC123’, 65000);

今回は、エラーメッセージが表示されます。

Output

ERROR 1062 (23000): Duplicate entry ‘ABC123’ for key ‘device_serial’

再度SELECTクエリを使用して新しい行がテーブルに追加されていないかを確認できます。

SELECT * FROM employees WHERE device_serial = ‘ABC123’;

今は一つの行が返されています。 (Ima wa hitotsu no gyō ga kaesareteimasu.)

Output

ユニークインデックスは、重複したエントリーを防ぐだけでなく、クエリの高速化のための完全な機能的なインデックスでもあります。データベースエンジンは、ユニークインデックスを前のステップと同じように使用します。以下のコマンドを実行することで、それを確認することができます。

EXPLAIN SELECT * FROM employees WHERE device_serial = ‘ABC123’;

次の実行計画は、以下のようになります（テーブルは少し異なる場合もあります）。

Output

問い合わせを実行する際、possible_keysおよびkeyカラムの両方にdevice_serialのインデックスが表示されているため、インデックスが使用されたことが確認されます。

データベース内の重複データを防ぐために、ユニークインデックスを使用しました。次のセクションでは、複数の列にまたがるインデックスを使用します。

複数の列にインデックスを使用する

これまでのセクションで作成したすべてのインデックスは、選択した列の値に関連する一つの列名を使用して定義されていました。ほとんどのデータベースシステムは、複数の列にまたがるインデックスをサポートしています。このようなインデックスは、マルチカラムインデックスと呼ばれ、複数の列の値を単一のインデックスに格納する方法を提供し、データベースエンジンがセットの列を一緒に使用してクエリをより迅速かつ効率的に実行できるようにします。

性能を最適化すべき頻繁に使用されるクエリでは、WHEREフィルタリング句で複数の条件を使用する必要があります。このようなクエリの例として、データベースに対して名前と姓の両方で人物を検索するように要求するものがあります。

SELECT * FROM employees WHERE last_name = ‘Smith’ AND first_name = ‘John’;

このクエリをインデックスを使って最適化する最初の考えは、last_nameという列に1つ、first_nameという列にもう1つの個別のインデックスを作成することです。しかし、この状況には最適な選択肢ではありません。

このように2つの別々のインデックスを作成した場合、MySQLはSmithという名前のすべての従業員を見つける方法を知っています。また、Johnという名前のすべての従業員を見つける方法も知っています。しかし、John Smithという名前の人を見つける方法を知ることはありません。

２つの個別の索引を持つ問題を説明するために、姓で整理された１つの電話帳と名前で整理されたもう１つの電話帳を持っていると想像してください。両方の電話帳はそれぞれ姓と名前の列で作成された索引に似ています。電話帳の利用者として、John Smithを見つける問題には以下の３つの方法があります。

Use the phone book ordered by last names to find all people named Smith, ignore the second phone book, and manually traverse all Smith people one by one until you find John Smith.
Do the opposite: use the phone book ordered by first names to find all people named John, ignore the second phone book, and manually traverse all John people one by one until you find John Smith.
Try to use both phone books together: find all people named John and separately all people named Smith, write the interim results down, and try to manually intersect the two subsets of data looking for people that are on both individual lists.

これらの手法のいずれも理想的ではなく、MySQLには複数の非連続インデックスと複数のフィルタリング条件を要求するクエリに対して似たような選択肢が利用可能です。

もう一つの方法は、1つの列ではなく複数の列を考慮に入れたインデックスを使用することです。これは、もう一つの電話帳の中に電話帳があると想像してみてください。まず、姓がSmithのエントリーを検索し、それによって名前でアルファベット順に並べられたSmithさんのすべての人々のセカンドカタログにたどり着くことができます。このカタログを使用してJohnを素早く見つけることができます。

Note

注意：MySQLでは、クエリで使用されるテーブルごとに1つのインデックスしか使用できないとよく言われています。MySQLは、クエリを実行する際に複数のインデックスを共同して使用するためのインデックスマージ最適化をサポートしているため、これは常に真ではありません。ただし、この制限はインデックスを構築する際の一般的な指針となります。MySQLは複数のインデックスを使用しないことを決定することもあります。たとえ使用していても、多くのシナリオでは、専用のインデックスと同様に役立たない場合があります。

MySQLで従業員テーブルの姓と名に対してマルチカラムインデックスを作成するには、以下のコマンドを実行します。

CREATE INDEX names ON employees(last_name, first_name);

この場合、CREATE INDEXステートメントは少し異なります。テーブル名（employees）の後のカッコ内に、last_nameとfirst_nameの2つの列がリストされます。これにより、両方の列に対して複数列インデックスが作成されます。インデックスの定義で列がリストされる順序が重要です。すぐにわかるようになります。

データベースは、次のメッセージを表示してインデックスの作成が成功したことを確認します。

Output

Query OK, 0 rows affected (0.024 sec) Records: 0 Duplicates: 0 Warnings: 0

いま、SELECTクエリを発行して、名前がJohn、姓がSmithに一致する行を見つけてみてください。

SELECT * FROM employees WHERE last_name = ‘Smith’ AND first_name = ‘John’;

結果は、従業員の名前がジョン・スミスである一つの行です。

Output

Now use the EXPLAIN query to check whether the index was used:
インデックスが使用されたかどうかを確認するために、EXPLAINクエリを使用してください。

EXPLAIN SELECT * FROM employees WHERE last_name = ‘Smith’ AND first_name = ‘John’;

以下の実行計画は（テーブルは少し異なる場合もありますが）次のようになります。

Output

データベースは名前のインデックスを使用しました。1つの行がスキャンされ、表は必要最小限のトラバースのみで処理されました。Extra列には「インデックス条件を使用中」と表示され、MySQLがフィルタリングを完全にインデックスのみで行うことができました。

名前の先頭と末尾のフィルタリングを行い、これらの2つの列にわたるマルチカラムインデックスを使用することで、データベースは目的の結果を直接かつ迅速に見つける方法を提供します。

両方の列に定義されたインデックスの場合、名前がSmithでフィルターが名前以外で行われない場合、どうなりますか？修正されたクエリを実行してください。

SELECT * FROM employees WHERE last_name = ‘Smith’;

以下の結果が返されます。

Output

+————-+————+———–+—————+——–+ | employee_id | first_name | last_name | device_serial | salary | +————-+————+———–+—————+——–+ | 20 | Abigail | Smith | FGH890 | 155000 | | 17 | Daniel | Smith | WXY901 | 140000 | | 1 | John | Smith | ABC123 | 60000 | | 5 | Michael | Smith | MNO345 | 80000 | +————-+————+———–+—————+——–+ 4 rows in set (0.000 sec)

四人の従業員が姓をスミスとしています。

もう一度、クエリの実行計画にアクセスしてください。

EXPLAIN SELECT * FROM employees WHERE last_name = ‘Smith’;

次に示す実行計画は、以下（テーブルは若干異なる場合もありますが）と類似する予定です。

Output

今回は、同じ姓を持つ従業員が複数いたため、4行が返されました。しかし、実行計画のテーブルによると、データベースはこのクエリを実行するために、複数列インデックスの名前を使用し、返された正確な数である4行のみをスキャンしました。

前のクエリでは、結果を絞り込むために使用されるカラム（last_name）が最初にCREATE INDEXステートメントで指定されました。今回は、複数列インデックスのカラムリストで2番目に指定されていたfirst_nameでemployeesテーブルを絞り込みます。次のクエリを実行してください。

SELECT * FROM employees WHERE first_name = ‘John’;

出力は以下のように返されます:

Output

クエリの実行計画にアクセスしてください。

EXPLAIN SELECT * FROM employees WHERE first_name = ‘John’;

出力は以下のように戻ります。

Output

再度、返された結果には1人の従業員が含まれていますが、今回はインデックスが使用されていませんでした。データベースはテーブル全体をスキャンし、Extra列の「Using where」の注釈や20行のスキャン結果によって示されています。

この場合、データベースは、最初にインデックスを作成したときに使用された列の順序のために、インデックスを使用しませんでした：姓、名。クエリが最初の列または最初の列と2番目の列の両方を使用する場合にのみ、データベースはインデックスを使用することができます。インデックス定義の最初の列が使用されないインデックスに対するクエリをサポートすることはできません。

複数列にインデックスを作成することで、データベースはインデックスを使用して、インデックス化されたすべての列か、左側から順に増えるインデックス化された列のプレフィックスが関与するクエリの処理を高速化できます。例えば、列（a、b、c）を含む複数列インデックスは、すべての三つの列を関与するクエリや最初の二つの列を関与するクエリ、さらに最初の列のみを関与するクエリの処理を高速化することができます。しかし、最後の列cのみや、最後の二つの列bとcのみを関与するクエリでは、インデックスは効果を発揮しません。

インデックスに含まれる列とその順序を慎重に選ぶことで、同じテーブル上のさまざまなクエリを高速化するために単一の多列インデックスを使用することができます。この例では、従業員の検索が名前と姓の両方または姓のみによって行われる場合、名前のインデックスで提供される列の順序は、関連するすべてのクエリを高速化することを保証します。

このセクションでは、マルチカラムインデックスを使用し、そのようなインデックスを指定する際の列の順序について学びました。次のセクションでは、既存のインデックスの管理方法について学びます。

既存のインデックスのリスト化と削除

前のセクションで、新しいインデックスを作成しました。インデックスには名前があり、特定のテーブルに定義されているため、必要なときにはリストアップして操作することもできます。

このチュートリアルで作成した従業員テーブルのすべてのインデックスをリストするには、次のステートメントを実行してください。

SHOW INDEXES FROM employees;

下記の出力は同じようなものになります。

Output

あなたのMySQLのバージョンによって多少異なるかもしれませんが、出力には全てのインデックスが含まれます。それにはインデックスの名称、インデックスを定義するために使用される列、一意性の情報、およびその他のインデックス定義の詳細が広範に含まれます。

既存のインデックスを削除するには、DROP INDEX SQL文を使用することができます。device_serial列で一意性を強制する必要がなくなった場合を想像してください。そのため、device_serialインデックスは不要になります。次のコマンドを実行してください。

DROP INDEX device_serial ON employees;

デバイスシリアルはインデックス名で、従業員はそのインデックスが定義されたテーブルです。データベースは、インデックスの削除を確認します。

Output

Query OK, 0 rows affected (0.018 sec) Records: 0 Duplicates: 0 Warnings: 0

時折、典型的なクエリのパターンは変化することがありますし、新しいクエリの種類が注目されることもあります。その場合、使用されているインデックスを再評価したり、新しいインデックスを作成したり、使われていないインデックスを削除したりすることで、データベースのパフォーマンスの低下を防ぐため、常に最新の状態に保つ必要があります。

CREATE INDEX と DROP INDEX のコマンドを使用することで、既存のデータベースのインデックスを管理することができます。必要で有益と判断された場合にインデックスを作成するためのベストプラクティスに従っています。

結論

このガイドに従うことで、インデックスとは何か、そしてMySQLで最も一般的なタイプのインデックスを使用して、条件付きSELECTクエリを通じてデータ検索を高速化する方法を学びました。インデックスを使用して列データの一意性を維持し、複数の列がフィルタリング条件に使用されるクエリにどのように影響するかを学びました。

よく実行されるクエリのタイプに応じて、データベースのパフォーマンスを調整するためにインデックスを使用することができます。一般的な使用ケースの読み取りパフォーマンスと書き込みパフォーマンスの適切なバランスを取ることができます。このチュートリアルでは、その目的のためにインデックスの基礎のみをカバーしています。MySQLがどのインデックスを使用するか、いつ使用するかを理解することで、より複雑なクエリをサポートすることができます。詳細については、MySQLのインデックスに関するドキュメントを参照してください。

もしSQL言語に関する異なるコンセプトやその使い方についてもっと学びたいのであれば、他の「SQLの使い方」シリーズのガイドをチェックすることをおすすめします。