はじめに
実務の照会問い合わせ対応で使用しているプログラムのSQLパフォーマンス改善をすることによって、工数削減の見込める箇所があり、その箇所を改善するために学習しておりました。学習中にカバリングインデックスを初めて知り、今後、有効活用できそうと思い、記事にしました。
カバリングインデックスとは
カバリングインデックスとは実行するSQLのカラム全てを含んだインデックスです。例えば以下のようなSQLで使用している「gender」、「emp_no」、「birth_date」に対して、インデックスを作成することです。それによって、カバリングインデックスになります。
セカンダリインデックスの場合、インデックスからROWIDを取得して、テーブルへアクセスしてデータを取得します。カバリングインデックスの場合、テーブルへのアクセスは発生せず、インデックスにアクセスすればデータを取得できるため、高速です。
SELECT emp_no, birth_date, gender FROM employees where gender = 'M';
Docker環境のMySQLで実際にカバリングインデックスを試してみました。
SELECT文の実行前に以下のように設定します。どれほどパフォーマンスが改善するのかを調べるための事前設定を行います。
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
インデックスを貼る前はselect文の実行に0.1479秒かかっておりました。
mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration, SQL_TEXT from performance_schema.events_statements_history_long order by event_id desc limit 1;
+----------+----------+---------------------------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT |
+----------+----------+---------------------------------------------------------------------+
| 143 | 0.1479 | SELECT emp_no, birth_date, gender FROM employees where gender = 'M' |
+----------+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
カバリングインデックス作成前の実行計画です。
mysql> EXPLAIN SELECT emp_no, birth_date, gender FROM employees where gender = 'M';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299423 | 50.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.02 sec)
mysql>
typeがALLになっており、フルスキャンになっております。また、ExtraはUsing whereとなっています。カバリングインデックスが有効になっている場合、Using indexとなります。
以下のようにカバリングインデックスを作成します。
CREATE INDEX idx_employees_gender_emp_no_birth_date ON employees(gender, emp_no, birth_date);
インデックス作成後の実行計画は以下です。
mysql> EXPLAIN SELECT emp_no, birth_date, gender FROM employees where gender = 'M';
+----+-------------+-----------+------------+------+----------------------------------------+----------------------------------------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------------------------------+----------------------------------------+---------+-------+--------+----------+--------------------------+
| 1 | SIMPLE | employees | NULL | ref | idx_employees_gender_emp_no_birth_date | idx_employees_gender_emp_no_birth_date | 1 | const | 149711 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+------+----------------------------------------+----------------------------------------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql>
typeがrefになっており、インデックスが有効になっております。また、ExtraはUsing indexとなり、カバリングインデックスが有効になっております。
パフォーマンスの方も確認したところ、0.1008秒になっておりました。約0.04秒の短縮になっております。
mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration, SQL_TEXT from performance_schema.events_statements_history_long order by event_id desc limit 1;
+----------+----------+---------------------------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT |
+----------+----------+---------------------------------------------------------------------+
| 310 | 0.1008 | SELECT emp_no, birth_date, gender FROM employees where gender = 'M' |
+----------+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
カバリングインデックスの注意点
保守性が悪い点
カバリングインデックスはSQLで使用しているカラム全てを含んだインデックスであるため、SELECT文にカラムを追加したりするとカバリングインデックスが無効になってしまいます。気付かぬうちにパフォーマンスが悪化してしまうことがあります。
1つのインデックスに含められるカラム数に上限がある点
DBMSによって、含められるカラム数に上限があるため、使用しているDBMSのマニュアルをチェックする必要があります。
また、インデックスに含めるカラム数が多くなると、インデックス情報がメモリに収まらなくなってしまいます。それによって、インデックス情報を取得するためにストレージへのI/Oが発生してしまい、パフォーマンス低下が発生します。
更新負荷が高い点
カバリングインデックスは検索が高速になるものの、更新処理は通常のインデックスよりも負荷がかかります。1つのSQLで使用しているカラム全てを含むインデックスのため、通常のインデックスよりも負荷がかかるためです。そのため、更新処理にも使用するテーブルに対するカバリングインデックスを貼る場合には注意が必要です。