データベースを利用する時、効率的にクエリを処理する必要があります。
今回は、MySQLを例に取り、
本の目次のようなインデックス、
本の本編のようなデータベースを例に取り、
パフォーマンスのチューニングを考えてみたいと思います。
最初に、MySQL sys スキーマを読み解きます。
MySQLデータベースの使用状況を把握するのに役立つビュー、関数、プロシジャーの集まりで、
使用状況を確認出来るようです。
概要を確認しました。
パフォーマンススキーマによって収集されたデータを解釈するのに役立つ一連のオブジェクトである。
sys スキーマオブジェクトは、チューニング、診断に使える。
①パフォーマンススキーマデータをわかりやすい形式に要約するビュー。
②パフォーマンススキーマの構成や診断レポートの生成などの操作を実行するストアドプロシージャー。
③パフォーマンススキーマ構成をクエリーして、フォーマットサービスを提供するストアドファンクション。
と、次のサイトに記載がありました。
⇒チューニングに使えるようです。
使用方法を確認しました。
sys スキーマをデフォルトスキーマにすると、そのオブジェクトへの参照をスキーマ名で修飾する必要がなくなる。
mysql> USE sys;
Database changed
mysql> SELECT * FROM version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 2.0.0 | 8.0.13-debug |
+-------------+---------------+
host_summary_by_file_io ビューでは、ホスト別にグループ化されたファイル I/O が要約され、
ピコ秒からより読みやすい値 (単位付き) に変換された待機時間が表示される。
mysql> SELECT * FROM sys.host_summary_by_file_io;
+------------+-------+------------+
| host | ios | io_latency |
+------------+-------+------------+
| localhost | 67570 | 5.38 s |
| background | 3468 | 4.18 s |
+------------+-------+------------+
と、次のサイトに記載がありました。
⇒待機時間が確認出来るようです。
用途を確認しました。
使われていないインデックス情報について確認し、削除。
select * from sys.schema_unused_indexes;
alter table 「object_name」 drop index 「index_name」;
インデックスを削除する理由を整理しました。
データベースでは、最初にインデックスという目次をみてから、効率的にデータベース本編を処理しようと試みます。
テーブルのレコードが更新(DELETE/INSERT/UPDATE)される場合、インデックスも更新されますよね。
書籍を例にとると、本の本編の内容変更があれば、目次が変わりますね。 でも、読まないページの目次が多いと、目次の効果が全く意味をなさなくなりますよね。。 目次がいっぱいで、目次を見てから必要な箇所だけ本を読もうとしたら、必要な箇所を読むスピードって、落ちますよね。
故に、インデックスの数が多ければ、その分更新するデータ量が増え、更新時のパフォーマンスが劣化します。 よって、目次であるインデックスを整理するとパフォーマンスがあがります。
データベースには、オプティマイザというのがあり、最適化しているようですが、 それでも、パフォーマンスチューニングしきれない所があり、こういった処理が有効となってくるケースがあるようです。