search
LoginSignup
3

posted at

updated at

Organization

不要なインデックスを削除して、パフォーマンスをチューニング

データベースを利用する時、効率的にクエリを処理する必要があります。

今回は、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)される場合、インデックスも更新されますよね。


書籍を例にとると、本の本編の内容変更があれば、目次が変わりますね。 でも、読まないページの目次が多いと、目次の効果が全く意味をなさなくなりますよね。。 目次がいっぱいで、目次を見てから必要な箇所だけ本を読もうとしたら、必要な箇所を読むスピードって、落ちますよね。

故に、インデックスの数が多ければ、その分更新するデータ量が増え、更新時のパフォーマンスが劣化します。 よって、目次であるインデックスを整理するとパフォーマンスがあがります。

データベースには、オプティマイザというのがあり、最適化しているようですが、 それでも、パフォーマンスチューニングしきれない所があり、こういった処理が有効となってくるケースがあるようです。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
3