【Mysql】invisibleにしたインデックスを削除するとレスポンスが悪化する

Question

不要なインデックスの削除をするため、対象のインデックスを一度invisibleに変更し
一週間程度様子を見、レスポンスが悪化していないことを確認した上で削除をしました。

しかし、実際に参照系のクエリの実行時間を確認したところ
visibleからinvisibleに変更した場合は、影響はありませんでしたが
invisibleにしたインデックスを削除すると、参照系のクエリの実行時間が大幅に伸びました。

上記の現象の後、削除したインデックスと同様のインデックスを再度作成したところ、
対象のクエリの実行時間が再びインデックス削除前と同様の時間まで短縮されました。

質問させていただきたいのですが、
invisibleにしているインデックスを削除することで
参照系のクエリの実行時間に影響することはあり得るのでしょうか。

追記
対象のSQL、実行計画、インデックス、テーブル構造を追記いたしました。

-- 対象のSQL

SELECT msd.groups as p_name,CHAR_LENGTH(msd.groups)+(LENGTH(msd.groups)-CHAR_LENGTH(msd.groups))/2 as namelen, COUNT(*) as cnt,SUM(af.gross) as hassei,msd.disp
FROM  `mail_settings_detail` msd , af_result af
WHERE
 msd.category IN ('AAA','BBB','CCC','DDD') 
 AND af.tag_time BETWEEN '2020-05-01 00:00:00' AND '2020-05-15 23:59:59'
 AND msd.pgid = af.pgid
 AND msd.status = 1
 AND af.status NOT IN ('XX','YY')
GROUP BY msd.groups ORDER BY category , disp;

問題のインデックス：mail_settings_detailのstatus

statusがvisibleの場合の実行計画
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|:--|:--:|--:|
|1|SIMPLE|af||range|pgid,tag_time,status|tag_time|4||35196|60|Using index condition; Using where; Using temporary; Using filesort|
|1|SIMPLE|msd||ref|pgid,status|pgid|4|DB.af.pgid|1|40|Using where|

statusがinvisibleの場合の実行計画
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|:--|:--:|--:|
|1|SIMPLE|af||range|pgid,tag_time,status|tag_time|4||35196|60|Using index condition; Using where; Using temporary; Using filesort|
|1|SIMPLE|msd||ref|pgid|pgid|4|DB.af.pgid|1|13.33|Using where|

statusのインデックスを削除した場合の実行計画
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|:--|:--:|--:|
|1|SIMPLE|msd||ALL|pgid||||651|4|Using where; Using temporary; Using filesort|
|1|SIMPLE|af||ref|pgid,tag_time,status|pgid|4|DB.msd.pgid|1178|0.72|Using where|

対象テーブルのインデックス

CREATE INDEX pgid ON mail_settings_detail(pgid);
CREATE INDEX userid ON mail_settings_detail(userid);
CREATE INDEX mail_setid ON mail_settings_detail(mail_setid);
CREATE INDEX status ON mail_settings_detail(status);

statusのインデックスがvisibleの場合の実行時間
0.302秒

statusのインデックスがinvisibleの場合の実行時間
0.215秒

statusのインデックス削除後の場合の実行時間
10.115秒

mail_settings_detailテーブル
レコード数：651
rowid : int11 primarykey
pgid : int11
category : varchar30
groups : varchar30
disp : int11
status : int11
他、本SQLに出てこないものが6カラム

af_resultテーブル
レコード数：290万
retstid : bigint20 primarykey
pgid : int11
gross : int11
tag_time : timestamp
status : enum
他、本SQLに出てこないものが27カラム

補足情報（FW/ツールのバージョンなど）

MySQL 8.0.17

Comments

[sazi]

invisible/visibleで実行計画は変わらなかったのですね？

[Rio_1201]

EXPLAINで実行計画を確認しましたが、visible/invisibleでの違いは一点のみで
visibleの場合のみpossible_keysに対象のインデックスが上がってきております
但し、keyには別のインデックスのみが上がってきているため、
visibleであっても対象のインデックスは使われていないと判断しております。

[sazi]

実行計画に違いが出るなら、実行時間に相違が出るのは当然です。

[Rio_1201]

それに関してはご指摘いただいている通りだと思うのですが、
対象のインデックスのvisible/invisibeを変えたときには実行時間は一切変わりませんでした。
実行時間が変わったのはインデックスを削除した場合のみでした。

[sazi]

具体的な、テーブルやインデックスの定義および対象のSQLを質問に追記可能ですか？
先ずは前後の実行計画も。

[Rio_1201]

テーブルおよびインデックス、対象のSQL、実行計画を追記させていただきました。

[sazi]

対象テーブルのインデックスは、インデックスの定義文(create index)にしてもらえませんか？
それから見辛いので、<コードの挿入>で括って下さい。

[sazi]

実行計画もずれているように見受けらられます。

[sazi]

実行計画は同じ環境で取得したものですか？
インデックスの削除後だけ別環境のような結果ですね。

[Rio_1201]

申し訳ございません。インデックスおよび実行結果を修正いたしました。

実行結果に関しては同一の環境での結果になります。
1.インデックスがvisibleの状態で実行結果を取得
2.インデックスをinvisibleに変更し、実行結果を取得
3.インデックスを削除し、実行結果を取得
の順番で同一環境で行った結果になります。

Answer 1

実行計画を行う都度キャッシュはクリアして下さい。

RESET QUERY CACHE;

MySQLクエリキャッシュのクリア方法

Comments

[Rio_1201]

上記のSQLを実行してみましたが、シンタックスエラーとなりました。
少々調べてみたところmysql8.0からクエリキャッシュが削除されているものによるかと思われます。

SHOW STATUS LIKE'Qcache%';

で確認したところクエリキャッシュは残っていないようでしたので
実行計画にクエリのキャッシュは影響してはいないのではないかと思います。

[sazi]

SQLがgroup byを明確にしていないことが影響していそうですね。
(ORDER BY category , disp はgroup by項目ではありませんから保証されていません）
それから、何故マルチカラムインデックスにしてないのでしょうか？
また、そもそも性能重視で、何でインデックスを削除するのでしょう？

[Rio_1201]

経緯といたしましては前任者が全テーブルの全カラムに対してインデックスを
貼っておりまして、更新系の処理が走るときの無駄をなくすためにインデックスの整理を以下の手順で進めておりました。

1.不要なインデックスの洗い出し
2.不要なインデックスをinvisibleに変更し、経過観察
3.処理速度が遅くなっていないことを確認した後、不要インデックスを削除
4.スロークエリを確認し、足りていないインデックスを追加

こちらの3.まで実施したところで本件の問題が発生したかたちになります。
インデックスの細かな修正については今後行う予定でございました。

[sazi]

ANALYZE TABLEで統計情報も実行計画前に更新してみて下さい。

それから、不要なインデックスの洗い出しよりも先に、本来必要であるインデックスを作成して、不要になる部分を削除するように、アプローチを変更した方が良いですね。

質問の例だと、マルチカラムインデックスを作成してあげれば、性能が改善しそうであり、それなら従来のインデックスを削除しても影響が出ないと思われますし、インデックス数も減らすことが可能だと思います。

[Rio_1201]

アドバイスいただきましてありがとうございます。
方針を変更し、そちらで進めていこうと思います。

[sazi]

統計情報を取り直して実施してみましたか？
実行計画の件数が違う事から、インデックスを削除したタイミングで統計情報が最新になったのだと推測します。

方針変更しても、手順は変わりませんので、確認取られた方が良いかと思います。