unused_indexes

Indexは参照処理を高速化する為には必須ですが、Insert, delete, updateにおいてはオーバーヘッドになるケースが多い為、利用されてないインデックスを確認して適宜削除した方がパフォーマンスは安定し、クラウドサービスにおいては不要なI/Oを削減する事が出来ます。

PostgreSQLの場合はpg_stat_user_indexes を利用

In case of PostgreSQL

rails_app=# \c development 
You are now connected to database "development" as user "postgres".
taskleaf_development=# SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes;
                indexrelname                 | idx_scan | idx_tup_read | idx_tup_fetch 
---------------------------------------------+----------+--------------+---------------
 schema_migrations_pkey                      |       30 |          195 |           195
 ar_internal_metadata_pkey                   |        3 |            2 |             2
 notes_pkey                                  |     1455 |         1769 |          1751
 users_pkey                                  |     2202 |         2202 |          2202
 index_users_on_email                        |      108 |           94 |            94
 index_tasks_on_user_id                      |      306 |         1455 |          1453
 active_storage_blobs_pkey                   |      112 |          112 |           112
 index_active_storage_blobs_on_key           |        0 |            0 |             0
 active_storage_attachments_pkey             |        0 |            0 |             0
 index_active_storage_attachments_on_blob_id |        0 |            0 |             0
 index_active_storage_attachments_uniqueness |      367 |           46 |            46
 kbis_pkey                                   |        0 |            0 |             0
(12 rows)

taskleaf_development=# 

※ 起動してからの統計です。

MySQLの場合はSYS Schemaを利用

In case of MySQL

起動してから利用されてないIndexを特定(schema_unused_indexes)

root@localhost [sys]> select * from sys.schema_unused_indexes limit 5;
+---------------+--------------------+-----------------------------+
| object_schema | object_name        | index_name                  |
+---------------+--------------------+-----------------------------+
| APP           | FTS_Tweets         | idx_mecab_text              |
| APP           | FTS_Tweets2        | idx_mecab_text              |
| APP           | FTS_Tweets20200518 | idx_mecab_text              |
| APP           | FTS_Tweets3        | idx_mecab_text              |
| APP           | password_resets    | password_resets_email_index |
+---------------+--------------------+-----------------------------+
5 rows in set (0.03 sec)

root@localhost [sys]> select * from sys.schema_index_statistics limit 1\G
*************************** 1. row ***************************
  table_schema: APP
    table_name: sessions
    index_name: sessions_id_unique
 rows_selected: 14111
select_latency: 654.74 ms
 rows_inserted: 0
insert_latency:   0 ps
  rows_updated: 705
update_latency: 252.13 ms
  rows_deleted: 6341
delete_latency: 127.89 ms
1 row in set (0.00 sec)

root@localhost [sys]> 
schema_unused_indexes

重複したインデックスの特定と削除 ( schema_redundant_indexes )

root@localhost [sys]> select * from  sys.schema_redundant_indexes\G
*************************** 1. row ***************************
              table_schema: confirm
                table_name: memo
      redundant_index_name: idx_text2
   redundant_index_columns: data
redundant_index_non_unique: 1
       dominant_index_name: idx_text1
    dominant_index_columns: data
 dominant_index_non_unique: 1
            subpart_exists: 1
            sql_drop_index: ALTER TABLE `confirm`.`memo` DROP INDEX `idx_text2`
1 row in set (0.01 sec)

root@localhost [sys]> 

※ 起動してからの統計です。

Categories:

Tags:

Comments are closed