MySQLのsysスキーマを利用して不要インデックスを断捨離する話 #MySQL

メリークリスマス!

この記事はナビタイムジャパンアドベントカレンダー 25日目の記事です。
2019年も残すところあと1週間切りましたね。年末に向けて大掃除をしないといけませんね。
この記事では、MySQLのいらないインデックスを断捨離して、心地よく2020年を迎える準備をする話です。

はじめに

こんにちは。ナビタイムジャパンで地点情報などを管理するデータベースについての運用/バッチ処理の開発をしているRNです。
当社では、経路検索サービスをユーザに提供しております。その経路検索には、経路検索の目的地とするための地点(POI)が必要不可欠です。
地点情報のデータストアには主にMySQL(とAurora MySQL)を利用しているのですが、日々、数万、数十万の地点データの更新を安定して、なるべく高速に行うためには工夫が必要です。
今回は「MySQLの更新高速化」のために行った、sysスキーマに基づくインデックス削除について、話していきます。

MySQLのインデックスについて

インデックスの基礎

MySQLを含む多くのリレーショナルデータベースは、参照の処理の高速化のために「インデックス」という機能を持ちます。
これは、よく「本の索引と似たようなものだ」と言われています。索引で目的の項目を探して、そのページを開く。そうすることにより、より早く目的の情報を取得することができますよね。
MySQLでも一つまたは複数のカラムに対してインデックスを作成しておくことで、参照パフォーマンスを向上させることが可能です。

インデックスを作成する

参照時のパフォーマンスを出すためにどのようにインデックスを作成すればよいでしょうか。
新規にテーブル設計を検討する場合には、よくwhere句やorder by句で指定されることの多いカラムや、外部テーブルとの結合キーとなるカラムにインデックスを作成すると良いです。
(今回は主題ではないので、詳細についてはお話いたしませんが、興味がありましたら、「MySQL インデックス設計」などで調べてみてください。)
既存のテーブルにインデックス追加を検討する場合、自分の場合は、以下のように実施しています。

スロークエリログを分析し、実行時間のかかるSQLを確認する
そのクエリの実行計画を確認する
効果のありそうなインデックスを検討する
インデックス作成し、効果を検証する
インデックスの採用可否を判断する

MySQLにはクエリの実行の際に使用するインデックスを最適に選択してくれる機能(オプティマイザ)が備わっています。
そのため、無駄なインデックス(検索キーにならないカラムへのインデックスや、他のインデックスで賄えてしまうインデックスなど)を追加したとしても、それによって、参照時のパフォーマンス劣化が起こることは稀です。

インデックスを断捨離しましょう

インデックスについて気をつけることが2点あります。

テーブルのレコードが更新(DELETE/INSERT/UPDATE)される場合、インデックスも更新される
- 本の例でも、本編が更新されたら索引も変える必要があるのと同じです。
- インデックスの数が多ければ、その分更新するデータ量が増えてしまいます。
インデックスが役にたたないこともある
- テーブルのレコード数や値の種類(例えば性別)が少ないと、インデックスの効果が薄くなる。
- 検索のキーとして指定されない場合、作成したインデックスが使われないこともある。

今回主に論じたいことは、1.についてです。
不必要なインデックスを「使うかもしれないから」といって、無闇矢鱈に作成していると、更新データサイズが肥大化し、更新時のパフォーマンスが劣化します。(劣化の程度についての実験は後述します。)
しかし、個人的には、インデックスを積極的に減らすことについて書かれている話は多くないかと思います。

インデックスの断捨離は難しい(?)

個人的にインデックスの断捨離は難しく感じます。その理由として、

参照パフォーマンス劣化を招きうる
- 使用されているインデックスを消してしまった場合、参照のパフォーマンスが大きく低下する可能性があります。
インデックスが使用されるかはMySQLのオプティマイザ次第
- 例えば、テーブルのレコード件数が大きく減った場合、インデックスが使用されなくなることもありえます。
不要になっても削除されにくい
- 更新のパフォーマンスやインデックスのデータサイズが問題にならない限り、自発的に断捨離はしない。

それでは、不要なインデックスを特定するために、MySQLの機能の1つである、sysスキーマを利用してみましょう。

`sys`スキーマについて

`performance_schema`について

MySQL5.5.3以降では、performance_schemaという機能があります。
これはMySQLのパフォーマンスチューニングのための機能であり、名前の通り、DBのパフォーマンスに関する統計情報を収集する機能です。
ちょっとややこしいのですが、performance_schemaというスキーマ名で予めDBに存在しています。
DBのperformance_schemaが有効化されているかを調べるには、以下のコマンドを打ってみてください。(MySQL5.6以降ではデフォルトで有効化されています。)

mysql> show variables like "performance_schema";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set, 1 warning (0.00 sec)

上記の例では有効化されています。

`sys`について

performance_schema自体は便利な機能ではあるのですが、情報が取り出しにくいという課題がありました。
そこで、MySQL5.7以降では、sysという機能が登場しました。スキーマ名であるためsysスキーマとも呼ばれるそうです。
これは、performance_schemaとinformation_schema(テーブルやカラム型などのDB内部メタ情報を持つスキーマ)を見やすくしたもので、実体はビューやプロシージャ、関数の集まりです。
そのためperformance_schemが有効化されていれば、sysの機能も有効となります。
MySQL5.6以前ではsysスキーマがないので、sysのリポジトリでクローンして、sqlを実行すれば作成可能です。
そのsysの中にschema_unused_indexesが存在しています。これは名前の通り使用されていないインデックスの一覧を表示できるビューです。

`sys.schema_unused_indexes`を使って断捨離をする

ちょっと実験してみます。

実験環境

MySQL Version : MySQL5.7.28
OS : Windows10
CPU : Intel Corei7-4790
メモリ: 16G

時間測定は各施行5回ずつ行って平均値としています。

準備

ダミーデータとして,以下SQLでテーブルを作成します.

テーブル作成とサンプルデータ投入

create database test;
create table sample(
    txt_1 varchar(5),
    txt_2 varchar(5)
);
insert into sample () values ();
-- 倍々に524288レコードになるまで増やす
insert into sample () select * from sample;
insert into sample () select * from sample;
insert into sample () select * from sample;
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample; 
insert into sample () select * from sample;
-- ランダム英数5文字の値を入れる
update sample 
set 
    txt_1 = SUBSTRING(MD5(RAND()), 1, 5),
    txt_2 = SUBSTRING(MD5(RAND()), 1, 5);

-- データの確認
select count(*) from sample;
select * from sample limit 3;

実験1 インデックスのない状態での参照パフォーマンスの確認

この状態での参照パフォーマンスの確認をしてみます。

実験1

select sql_no_cache count(*) from sample where txt_1 like "a%";

平均0.74secでした。

実験2 インデックスを作成している状態での参照パフォーマンスの確認

続いてインデックスをtxt_1カラムに作成して参照パフォーマンスの確認をしてみます。

実験2

alter table sample add index idx_txt1(txt_1);
select sql_no_cache count(*) from sample where txt_1 like "a%";

selectの実行時間を測定したところ、平均0.01secでした。速いですね。
このように文字列型では完全一致、前方一致ではインデックスによる効果が出ます。

実験3 インデックスを1つ作成している状態での更新パフォーマンスの確認

さて、この状態での更新パフォーマンスはどうなるでしょうか。

実験3

begin;
delete from sample where txt_1 like "a%"; 
rollback;

delete の処理では平均15.90secかかっていました。

実験4 インデックスを2つ作成している状態での更新パフォーマンスの確認

ここでtxt_2に対してインデックスを追加して同様に計測してみます。

実験4

alter table sample add index idx_txt2(txt_2);
begin;
delete from sample where txt_1 like "a%"; 
rollback;

今度は平均21.3secとなりました。インデックスを追加することで更新パフォーマンスが劣化するとお分かりいただけたかと思います。
(ちなみに、追加でidx_txt21(txt_2,txt_2) のように複合インデックスを作成すると更に更新処理が遅くなります。)

実験5 `sys.schma_unused_indexed`の確認

本題の使われていないインデックス情報について確認してみます。

実験5

select * from sys.schema_unused_indexes;

以下のような結果が得られると思います.

+---------------+-------------+------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------+
| test          | sample      | idx_txt2   |
+---------------+-------------+------------+

期待どおり、未使用のインデックスの情報が出てきています。
では断捨離をしましょう。

alter table sample drop index idx_txt2;

これにより、更新時のパフォーマンスが15.90secほどに戻ります.

(補足)`sys.schema_redundant_indexes` について

今回の実験では、sys.schma_unused_indexesを用いましたが、sysにはまた別のschema_redundant_indexというビューがあります。
こちらは他のインデックスと重複している冗長なインデックスを一覧で出せるビューです。
冗長なインデックスであるため、削除による影響が少ないので、こちらから断捨離するほうがよさそうです。
ただ、hint句などでこの冗長なインデックスが指定されていると影響が出てしまうので要注意です。そこはschema_unused_indexesと突き合わせて見ましょう.

schema_redundant_indexesはinformation_schemaからのみ作られるビューのため、performance_schemaやsysが有効になっていない場合でもgithubのSQLを少し組み替えると出力が可能です。

sys.schema_redundant_indexes

create table sample(
    txt_1 varchar(5),
    txt_2 varchar(5)
);
alter table sample add index idx_txt1(txt_1);  -- idx_txt12(複合インデックス)と重複しているため、idx_txt12で賄える
alter table sample add index idx_txt1_copy(txt_1); -- idx_txt1と同一
alter table sample add index idx_txt12(txt_1,txt_2);


select * from sys.schema_redundant_indexes;
+----------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+--------------------------------------------------------+
| table_schema   | table_name | redundant_index_name | redundant_index_columns | redundant_index_non_unique | dominant_index_name | dominant_index_columns | dominant_index_non_unique | subpart_exists | sql_drop_index                                         |
+----------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+--------------------------------------------------------+
| spoit_analysis | sample     | idx_txt1_copy        | txt_1                   |                          1 | idx_txt1            | txt_1                  |                         1 |              0 | ALTER TABLE `test`.`sample` DROP INDEX `idx_txt1_copy` |
| spoit_analysis | sample     | idx_txt1             | txt_1                   |                          1 | idx_txt12           | txt_1,txt_2            |                         1 |              0 | ALTER TABLE `test`.`sample` DROP INDEX `idx_txt1`      |
| spoit_analysis | sample     | idx_txt1_copy        | txt_1                   |                          1 | idx_txt12           | txt_1,txt_2            |                         1 |              0 | ALTER TABLE `test`.`sample` DROP INDEX `idx_txt1_copy` |
+----------------+------------+----------------------+-------------------------+----------------------------+---------------------+------------------------+---------------------------+----------------+--------------------------------------------------------+
3 rows in set (0.05 sec)

sql_drop_indexまで出てくるので非常に使い勝手が良さそうですね。

実施するうえでの注意

自分もschema_unused_indexesやschema_redundant_indexesを元にインデックスの断捨離を実施しています。
ただ、本番稼働しているもので実施するにはいくつか注意事項があります。

あくまでも断捨離の目安にする
- schema_unused_indexesで出てくるものはたまたま直近で使われていなかったインデックスという場合もあります。あくまでも目安として見て、クエリログと突き合わせてから断捨離しましょう。
performance_schemaの統計情報は、DB起動時にリセットされる
- 定期再起動などをを行っている場合は要注意です。
- 有効化してすぐに断捨離するのではなく、ある程度統計情報をためてから断捨離しましょう。
マスタ/スレーブ構成をとっている場合は双方のschema_unused_indexesを確認した上で断捨離する
- それぞれで実行されるSQLも異なるためです。
performance_schemaを有効化することで、メモリ/CPU/ストレージを専有する
- パオーマンスの懸念からかMySQLの派生であるMariaDBやAWSのAurora MySQLではデフォルトで無効化されています。
- 最近のMySQL(5.7以降)では改善されていると聞きますが(参考)、本番環境で有効化する場合は事前の負荷試験を実施することを推奨します。
- 使用メモリ上限サイズをチューニングすることも可能です。
断捨離は年始以降に
- 少なからずリスクはあるので、心穏やかに年末年始を過ごされたい場合は、年明け以降に実施しましょう。

まとめ

MySQLの機能のひとつであるperformance_schema及びsysスキーマの機能を使って不要なインデックスを断捨離してみました。
sysには他にもパフォーマンス改善に使えるものが多くあるそうなので、もっと使ってみようかと思います。公式ドキュメント

MySQLのsysスキーマを利用して不要インデックスを断捨離する話