SQLの達人への道: MySQLでの高速・効率的クエリ作成術

概要

この記事では、MySQLでのSQLクエリのパフォーマンスを最大限に引き出すための効率的な書き方を解説します。アプリケーションの応答速度を向上させることは、ユーザーエクスペリエンスの大幅な改善に直結します。この記事を通じて、初心者から中級者のデータベース管理者や開発者は、SQLクエリの基本から高度な最適化テクニックまで、幅広い知識を習得できることを目指しています。

MySQL 8.0での検証を基にしていますが、その他のバージョンでの動作は保証されません。この記事は継続的に更新されます。

主な内容

このセクションでは、検証データの作成手順を含め、インデックスの利用、JOIN操作の最適化、サブクエリとビューの利用、クエリキャッシュの活用など、効率的なクエリの書き方について解説します。

検証データの作成

MySQLサーバーへの接続方法から始め、テスト用データベースとテーブルの作成、ダミーデータの生成に至るまで、パフォーマンスチューニングを検証するための環境設定を詳細に説明します。

MySQLサーバーへの接続

コマンドラインからMySQLクライアントを起動してサーバーに接続します。

mysql -u ユーザー名 -p

データベースとテーブルの作成

テスト用のデータベースtestdbを作成し、パフォーマンスチューニングを検証するためのcompanyおよびpersonテーブルを定義します。

CREATE DATABASE testdb;
USE testdb;

CREATE TABLE company (
  company_id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE person (
  person_id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT,
  person_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (company_id) REFERENCES company(company_id)
);

ダミーデータの生成

companyおよびpersonテーブルに対して、それぞれ10万レコードのダミーデータを生成するストアドプロシージャの例を紹介します。

-- companyテーブル用ダミーデータ生成
DELIMITER $$
CREATE PROCEDURE `InsertCompanyData`()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 100000 DO
    INSERT INTO company (company_name) VALUES (CONCAT('Company ', i));
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL InsertCompanyData();
DROP PROCEDURE IF EXISTS `InsertCompanyData`;

-- personテーブル用ダミーデータ生成
DELIMITER $$
CREATE PROCEDURE `InsertPersonData`()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE maxCompanyID INT;
  SELECT MAX(company_id) INTO maxCompanyID FROM company;
  WHILE i < 100000 DO
    INSERT INTO person (company_id, person_name, email) VALUES (FLOOR(1 + RAND() * maxCompanyID), CONCAT('Person ', i), CONCAT('email', i, '@example.com'));
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL InsertPersonData();
DROP PROCEDURE IF EXISTS `InsertPersonData`;

実行計画

効率的なSQLクエリの作成には、その実行計画を理解することが不可欠です。実行計画を確認することで、クエリがどのように実行されるか、どのインデックスが利用されるかなど、パフォーマンスに影響を与える要因を把握できます。
実行計画の詳細について、以下のドキュメントの参照をお願い致します
クエリー実行プランの理解

適切にインデックスを活用する

WHERE句やORDER BY句にある列に対してインデックスを作成する

1. インデックス未作成時の全表スキャンケース

mysql> explain select person_id, person_name from person where person_name = '123';
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | person | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99784 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

この実行計画では、以下の点が確認できます。

• type: ALL となっており、全表スキャンが行われることを意味します。これは、テーブルの全行がスキャンされる非効率的な処理です。
• rows: 約 99,784 行がスキャンされると推定されます。データ量が多い場合、処理に時間がかかり、パフォーマンスが低下します。
• key: NULL となっており、インデックスが使用されていないことを示します。インデックスは、特定の行を効率的に検索するための仕組みです。インデックスが使用されていない場合、テーブル全体を検索する必要があり、処理速度が遅くなります。
• filtered: 10.00% の行が条件に一致すると推定されます。条件に一致する行が少ない場合でも、全表スキャンを行うと、処理に時間がかかります。

上記の通り、この実行計画は非効率であり、パフォーマンスが良くありません。

1. インデックス利用ケース

mysql> explain select person_id, person_name from person where person_id = 123;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | person | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

この実行計画では以下の点が確認できます：

• type: constとなり、クエリが主キーまたはユニークキーによる検索で1行のみを返すときに使用され、非常に効率的です。
• key：PRIMARYとなり、主キーが使用されています。
• rows：1となり、非常に効率的なアクセスを意味します。
• filtered：100.00となり、全ての行が条件に一致すると推定されます。

この結果は、personテーブルに対する非常に効率的なクエリの実行計画を示しており、主キーまたはユニークキーを使用して正確に1行を直接参照していることを意味します。このアクセス方法は、パフォーマンスが非常に良好です。

部分一致検索のパフォーマンス改善方法

指定された列で部分一致検索を行うとき、通常のインデックスでは効果が限定的になりがちです。そこで、以下の手順に従って部分一致検索のパフォーマンスを改善する方法をご紹介します。

1. 検索対象の文字列を格納するための新しい列をテーブルに追加します。
2. この新しい列にインデックスを作成します。

-- テーブルpersonに対して、person_name列を用いたインデックス作成が前提条件です。
-- 新しい列を追加します。
ALTER TABLE person ADD COLUMN string_exist BOOL AS (INSTR(person_name, '10') > 0) STORED;

-- インデックスを作成します。
CREATE INDEX idx_stringexist ON person(string_exist);

-- 新しい列を用いて検索を行います。
SELECT person_id, person_name FROM person WHERE string_exist = 1;

この方法を用いることで、100000レコードを対象とした場合、改善後のSQLの実行時間は約0.01秒になります。一方、改善前のSQLでは実行時間が約0.05秒でした。

SELECT person_id, person_name FROM person WHERE person_name LIKE '%10%';

このアプローチは効果的ですが、検証対象となる文字列ごとにインデックスを作成する必要があるため、実装にあたっては慎重に検討し、最適な対応方法を選択することが推奨されます。

関数型インデックスの利用

MySQL 8.0以降では、列値ではなく、列値に対して実行される式に基づいてインデックスを作成することができます。これは、列の一部をインデックス化したり、列値を変換した結果に基づいてインデックスを作成したりする際に特に有用です。

• person_name列に関するインデックスを作成する例：

CREATE INDEX idx_person_name ON person(person_name);

1. 関数操作を含むWHERE文でのインデックスの効果が限定的なケース

mysql> explain select person_id, person_name from person where upper(person_name) = 'TEST';
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+-------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key             | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | person | NULL       | index | NULL          | idx_person_name | 767     | NULL | 99784 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

この実行計画では以下の点が確認できます：

• type: indexとなり、インデックス全体をスキャンすることを意味しますが、このコンテキストではインデックススキャンの効率が一概に良いとは言えません。keyがidx_person_nameとして使用されていますが、UPPER(person_name) = 'TEST'の条件に基づいているため、インデックスの効果は限定的です。
• rows:99,784となり、全行がスキャンされると推定されます。
• filtered：100.00となり、全行が条件に合致すると推定されます。この実行計画は、関数を適用した結果と比較するためにインデックスを使用していますが、関数の適用によりインデックスの効率が低下しています。したがって、インデックス全体をスキャンする必要があるかもしれません。

1. 関数型インデックスの活用

関数型インデックスを利用して、列の値に関数を適用した結果に基づく検索を最適化することができます。以下は、person_name列にUPPER関数を適用するための関数型インデックスの作成例です。

-- `person_name`に`UPPER`関数を適用する関数型インデックスを作成
CREATE INDEX idx_upper_person_name ON person ((UPPER(person_name)));

mysql> explain select person_id, person_name from person where upper(person_name) = 'TEST';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | person | NULL       | ref  | idx_upper_person_name | idx_upper_person_name | 768     | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

このインデックスを活用することで、以下の利点が得られます：

• type: refとなり、インデックスを使用して特定の値に一致する行、または一致する複数の行を効率的に検索できます。このケースでは、'TEST'という特定の定数値にマッチする行を見つけるために関数型インデックスが利用されています。
• key: idx_upper_person_nameが使用され、これはperson_name列にUPPER関数を適用した結果に基づく関数型インデックスです。
• rows: 推定される検索対象行数は1行で、このクエリが非常に効率的に行われていることを示しています。
• filtered: スキャンされた行の100%が条件に合致すると推定されます。

この実行計画は、idx_upper_person_nameという関数型インデックスが効果的に使用され、UPPER(person_name) = 'TEST'という条件に基づいて特定の行を効率良く検索していることを示しています。このように、関数型インデックスは、関数を適用した列の値に基づく検索のパフォーマンスを大幅に向上させることができます。

複数カラムインデックスを利用する際のカラムの順序への考慮

複数カラムインデックスは、クエリがインデックスに含まれるカラムの順序に従ってフィールドを利用する際に最も効果を発揮します。適切なカラムでインデックスを構築することにより、クエリのパフォーマンスを大幅に向上させることが可能です。以下に例を示します。

検証データの作成

--データ作成
-- 10000レコードあるテーブル生成
DELIMITER $$
CREATE PROCEDURE `InsertTest10000Data`()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE maxCompanyID INT;
  WHILE i < 10000 DO
    INSERT INTO Test10000 (person_id, person_name, email,age) VALUES (i, CONCAT('Person ', i), CONCAT('email', i, '@example.com'),1 + RAND() * 100 );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL InsertTest10000Data();
DROP PROCEDURE IF EXISTS `InsertTest10000Data`;

--person_name, email, ageの順序でインデックスを作成します。
mysql> CREATE INDEX idx_name_age ON Test10000(person_name, email, age);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

検証

• person_nameとageに対し、検索を行います

mysql> explain select person_name, age from Test10000 where person_name > 'test' and age > 20;
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | Test10000 | NULL       | range | idx_name_age  | idx_name_age | 767     | NULL |    1 |    33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

• ageに対し、検索を行います

mysql> explain select person_name, age from Test10000 where age > 20;
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | Test10000 | NULL       | index | idx_name_age  | idx_name_age | 1539    | NULL | 9852 |    33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

• person_nameに対し、検索を行います

mysql> explain select person_name, age from Test10000 where person_name > 'test';
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | Test10000 | NULL       | range | idx_name_age  | idx_name_age | 767     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

実行計画の分析

これらの実行計画の結果から、Test10000テーブルに作成された複数カラムのインデックスidx_name_ageが、後続のクエリ実行計画にどのように影響しているかを評価します。このインデックスはperson_name, email, ageの3カラムに対して作成されています。それぞれのクエリに対する実行計画を通じて、インデックスの効果を評価しましょう。

• クエリ1: person_nameとageによる検索

  • type: range。インデックスを使用して特定の範囲のデータを効率的に検索しています。
  • rows: 1。極めて効率的なクエリで、推定される検索行数が非常に少ないことを示しています。
  • Extra: Using where; Using index。インデックスオンリースキャンを実行しており、テーブルの行を直接読み込む必要がありません。

• クエリ2: ageのみによる検索

  • type: index。インデックス全体をスキャンしていますが、特定の範囲ではなくインデックスの全データを見る必要があります。
  • rows: 9852。多くの行をスキャンする必要があり、検索条件に合致するデータの範囲が広いため、多くのデータを処理します。
  • Extra: Using where; Using index。インデックスを使用していますが、範囲が広いために多くのデータをスキャンします。

• クエリ3: person_nameのみによる検索

  • type: range。特定の範囲のデータを検索するためにインデックスが効率的に使用されています。
  • rows: 1。極めて効率的で、推定される検索行数が非常に少ないことを示しています。
  • Extra: Using where; Using index。インデックスオンリースキャンを示し、テーブルの行を直接読み込む必要がありません。

複数カラムインデックスの効果は、クエリがインデックスに含まれるカラムの順序に従って使用される場合に最も顕著です。person_nameでの検索は最も効率的であり、person_nameとageを組み合わせた検索も効率的です。しかし、ageのみを検索条件とすると、インデックスの効率が落ちます。これは、インデックスの先頭カラムをクエリで使用しない場合、インデックスの効率が低下するためです。

インデックスの使用すべきシナリオ

例外もありますが、インデックスの使用を検討すべきシナリオを以下にまとめました。実行計画を確認することを推奨します。

1. WHERE句で頻繁に使用される列

WHERE句で頻繁に検索条件として使用される列に対してインデックスを作成することで、検索速度を大幅に向上させることができます。

2. 外部キー

外部キーは、他のテーブルとの関連付けに使用される重要なフィールドです。外部キーにインデックスを作成することで、結合操作を高速化できます。

3. 集約関数の列

min()、max()などの集約関数で頻繁に使用される列に対してインデックスを作成することで、集約クエリの処理速度を向上させることができます。

インデックスの使用を検討すべきその他のシナリオ

• ソート操作で頻繁に使用される列
• 一意性の制約がある列
• 大量のデータを含むテーブル

インデックスを使用しないほうがよいシナリオ

以下の場合、インデックスを作成しても効果が得られない可能性があります。

1. 頻繁に追加、削除、変更操作が行われるカラム

頻繁な更新操作が行われるカラムにインデックスを作成すると、インデックスの更新に伴うオーバーヘッドが発生し、パフォーマンスが低下する可能性があります。

2. 重複値が多いカラム

ある列に大量の重複値が存在する場合、インデックスを作成しても検索性能が向上しない可能性があります。

3. データ量が少ないテーブル

テーブルのレコード数が非常に少ない場合、インデックスを作成しても検索性能が向上することはありません。

4. 主キーと複合主キー

主キーと複合主キーは、デフォルトでインデックスが作成されているため、通常は追加でインデックスを作成する必要はありません。

インデックスを作成する前に、以下の点を考慮する必要があります。

• テーブルの構造
• データ量
• アクセスパターン
• クエリのパターン

インデックスは適切に使用することで、データベースのパフォーマンスを大幅に向上させることができます。しかし、すべての状況で有効というわけではありません。上記のシナリオを参考に、インデックスが必要かどうかを判断してください。

結合について

複数の表を結合して検証作業を効率的に進めるためには、結合方法を適切に選択することが重要です。ここでは、一般的によく使用されるハッシュ結合とネステッドループ結合について説明します。

ハッシュ結合

MySQL 8.0.18 以降では、等価結合条件を持つ各結合に対して、適用可能なインデックスが存在しない場合にハッシュ結合が使用されます。以下はハッシュ結合の具体例と最適化方法です。

データ作成

--1000件レコードがあるテーブルを作成します。
create table company_1000 as select * from company order by company_id desc limit 1000
--5000件レコードがあるテーブルを作成します。
create table person_5000 as select * from person order by person_id desc limit 5000;

ハッシュ結合にての実行計画を確認

mysql> explain select * from person_5000 inner join company_1000 on person_5000.company_id = company_1000.company_id;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | company_1000 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL                                       |
|  1 | SIMPLE      | person_5000  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5145 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

この実行計画は、company_1000 と person_5000 テーブルの結合にインデックスが使用されていないことを示しています。結果としてフルテーブルスキャンが実行され、特に person_5000 テーブルでは、結合バッファ（ハッシュ結合）を使用して処理が行われています。データ量が多い場合、この方法は効率が低下する可能性があります。そのため、パフォーマンスを改善するためには適切なインデックスの作成が推奨されます。

ハッシュ結合の最適化

ハッシュ結合を使用する場合、メモリまたはディスク上のファイルが利用されます。最適化のための二つの方法は以下の通りです。

• join_buffer_size を増やす
  ハッシュ結合によるメモリ使用量は、join_buffer_size システム変数で制御できます。この変数で設定された量を超えるメモリは使用できません。

• open_files_limit を増やす
  ハッシュ結合に使用するファイルの上限数は open_files_limit で制御されます。必要なメモリが使用可能な量を超える場合、MySQLはディスク上のファイルを使用して処理を行います。この際、open_files_limit を適切に設定することが重要です。

ネストループ結合

ネストループ結合は、最も基本的な結合アルゴリズムの一つです。この結合では、ループ内の最初のテーブルから行を1つずつ読み取り、各行をネストしたループに渡します。ネストしたループは、結合する次のテーブルを処理し、最初のテーブルの行と一致する行を検索します。このプロセスは、結合するテーブルが残っている回数だけ繰り返されます。

検証用データ作成

mysql> create index person_5000_cid_idx on person_5000(company_id);

上記のコマンドは、person_5000テーブルのcompany_id列にインデックスを作成します。

実行計画確認

mysql> explain select * from person_5000 inner join company_1000 on person_5000.company_id = company_1000.company_id;

上記のコマンドは、person_5000とcompany_1000テーブルをcompany_id列で結合するクエリの実行計画を表示します。

mysql> explain select * from person_5000 inner join company_1000 on person_5000.company_id = company_1000.company_id;
+----+-------------+--------------+------------+------+---------------------+---------------------+---------+--------------------------------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys       | key                 | key_len | ref                            | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------------+---------------------+---------+--------------------------------+------+----------+-------+
|  1 | SIMPLE      | company_1000 | NULL       | ALL  | NULL                | NULL                | NULL    | NULL                           | 1000 |   100.00 | NULL  |
|  1 | SIMPLE      | person_5000  | NULL       | ref  | person_5000_cid_idx | person_5000_cid_idx | 5       | testdb.company_1000.company_id |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------------+---------------------+---------+--------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

実行計画の解説

上記の出力から、以下のことが分かります。

• company_1000テーブルに対してフルテーブルスキャンが行われる。
• person_5000_cid_idxインデックスを使用して、person_5000テーブル内でcompany_1000.company_idに一致する行が検索される。

このクエリでは、person_5000テーブルにインデックスを使用することで、効率的に結合を実行しています。

• ネストループ結合は、データ量が小さい場合に効率的な結合アルゴリズムです。
• データ量が多い場合、他の結合アルゴリズム（ハッシュ結合、ソートマージ結合など）の方が効率的な場合があります。

結合方法の比較

• 結合条件

  • ネストループ結合：制限なし。
  • ハッシュ結合：等価結合条件に限定されます。

• 使用リソース

  • ネストループ結合：主にCPUとディスクI/Oを使用します。
  • ハッシュ結合：主にメモリと一時的なディスクスペースを使用します。

• メリット

  • ネストループ結合：インデックスを効果的に使用できる場合、高いパフォーマンスを発揮します。
  • ハッシュ結合：インデックスに依存しないため、大量のレコードを持つテーブル間の結合で効率的に動作します。

• デメリット

  • ネストループ結合：適切なインデックスがない場合、パフォーマンスが低下します。
  • ハッシュ結合：ハッシュテーブルの構築に大量のメモリを消費する可能性があります。

インデックスの非効率的な使用方法

where句での前方一致以外の部分一致検索についての注意点

where句で指定する列に対する部分一致検索を行う際、前方一致を除く検索方法はインデックスの利用効率を低下させるため、慎重な扱いが求められます。前方一致以外の部分一致検索を実行すると、インデックスを通じた検索のパフォーマンスが著しく制限されることがあります。以下に示す三つのSQLクエリの実行計画を比較することで、前方一致検索がインデックスを効果的に利用するのに対し、部分一致や後方一致検索ではフルテーブルスキャンが発生し、インデックスの効果が限られる事例が確認できます。

mysql> explain select person_id, person_name from person where person_name like '%123%';
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+-------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key             | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | person | NULL       | index | NULL          | idx_person_name | 767     | NULL | 99766 |    11.11 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select person_id, person_name from person where person_name like '123%';
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | person | NULL       | range | idx_person_name | idx_person_name | 767     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select person_id, person_name from person where person_name like '%123';
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+-------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key             | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | person | NULL       | index | NULL          | idx_person_name | 767     | NULL | 99766 |    11.11 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

• %123%（部分一致）および%123（後方一致）の検索では、インデックスを使用しつつも全行スキャンが行われるため、インデックスの効果が低下します。
• 123%（前方一致）の検索では、インデックスを用いた範囲検索が可能となり、効率的なデータアクセスが実現します。
  このため、データベースクエリのパフォーマンスを最適化するためには、可能な限り前方一致検索を利用し、インデックスの利用効率を高めることが推奨されます。

インデックス利用に関する誤解

!=または<>オペレーターを避けるべきではありません。

!=または<>オペレーターを使用した場合でも、インデックスは利用され、効率的な範囲スキャンが行われる可能性があります。以下の例を参照してください。

explain select person_id, person_name from person where person_id <> 1;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | person | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 49893 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

この実行計画では以下が確認できます：

• type: rangeとなり、特定の範囲内の行を選択するためにインデックスが使用されています。
• key: PRIMARYとなり、主キーインデックスが使用されています。
• rows: 49,893となり、約49,893行がスキャンされると推定されます。
• filtered：100.00となり、検索条件に一致する行が全ての推定行数であることを意味します。

この実行計画は、personテーブルのPRIMARYキーを使用して範囲検索が行われるクエリに対するものであり、person_id <> 1という条件に基づいて、インデックスを利用しつつも、テーブル内の多くの行が検査されることになります。このような範囲検索において、インデックスが効率的に利用されていることが示されています。

ORを使用することによるパフォーマンス低下の誤解

ORを使用しても、インデックスは効果的に利用され、パフォーマンスが向上することがあります。

explain select person_id, person_name from person where person_id = 1 or person_id = 2;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | person | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

この実行計画では以下が確認できます：

• type: rangeとなり、指定された範囲内の行を選択するためにインデックスが使用されています。
• key：PRIMARYとなり、主キーインデックスが使用されています。
• rows: 2となり、2行がスキャンされると推定されます。
• filtered：100.00となり、全ての行が条件に一致すると推定されます。

この実行計画は、クエリがPRIMARYインデックスを使用して非常に効率的に行を検索していることを示しています。person_idが1または2である2行のみが検索され、これらの行はPRIMARYキーによって直接アクセスされています。このように、PRIMARYキーを使用した検索はパフォーマンスが非常に高いと言えます。

INまたはNOT INの使用に関する誤解

INまたはNOT INを使用しても、適切にインデックスが設定されていれば、効率的な範囲スキャンが行われ、良好なパフォーマンスが期待できます。

EXPLAIN SELECT person_id, person_name FROM person WHERE person_id IN (1,2);
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | person | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

この実行計画では以下が確認できます：

• type: rangeとなり、インデックスを使用して特定の範囲内の行を効率的に検索します。
• key：PRIMARYとなり、主キーインデックスが使用されています。
• rows: 2となり、スキャンされる行数は2行と推定されます。
• filtered：100.00となり、条件に一致する行が全ての推定行数であることを意味します。

これらの例は、適切なインデックス設計があれば、!=、<>、OR、IN、NOT INを使用したクエリでも、MySQLはインデックスを効果的に利用して高速にデータを検索できることを示しています。

IS NULLまたはIS NOT NULLに関して、インデックスを効果的に使用することできない

IS NULLまたはIS NOT NULLを使用する際にも、適切にインデックスが設定されていれば、良好なパフォーマンスが期待できることがあります。

検証データ準備

以下のSQLを実行し、person_5000テーブルのperson_nameカラムに対してインデックスを作成します。

CREATE INDEX person_5000_pname_idx ON person_5000(person_name);

検証

person_name IS NULLの条件でクエリを実行した場合の実行計画：

EXPLAIN SELECT * FROM person_5000 WHERE person_name IS NULL;

結果:

+----+-------------+-------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table       | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | person_5000 | NULL       | ref  | person_5000_pname_idx | person_5000_pname_idx | 768     | const |    1 |   100.00 | Using index condition |
+----+-------------+-------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+

person_name IS NOT NULLの条件でクエリを実行した場合の実行計画：

EXPLAIN SELECT * FROM person_5000 WHERE person_name IS NOT NULL;

結果:

+----+-------------+-------------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | person_5000 | NULL       | ALL  | person_5000_pname_idx | NULL | NULL    | NULL | 4951 |   100.00 | Using where |
+----+-------------+-------------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

実行計画分析

• 最初のクエリ（person_name IS NULL）は、person_5000_pname_idxインデックスを使用して非常に効率的に行を見つけることができます。これは、NULL値を持つ行が少ない、またはインデックスがNULL値の検索を最適化していることを示しています。

• 2番目のクエリ（person_name IS NOT NULL）では、インデックスが使用されず、フルテーブルスキャンが発生しています。これは、person_nameが非NULLの行がテーブルの大部分を占めているため、インデックスを使用するよりもフルテーブルスキャンの方が効率的であるとMySQLが判断したためです。しかし、これは大量のデータに対してはパフォーマンスの問題になる可能性があります。

最適化の観点からは、フルテーブルスキャンを避けるために、特定のクエリパターンに基づいてインデックス戦略を見直すことが重要です。IS NOT NULL条件の場合、インデックスを効果的に使用することは困難な場合がありますが、データの特性やクエリの使用頻度に応じて異なる最適化手法を検討する必要があります。