この記事は クラウドワークス Advent Calendar 2023 シリーズ1 6日目の記事です。
こんにちは。crowdworks.jp SRE チームの田中（@kangaechu）です。 この記事では crowdworks.jp の SRE チームが2023年にやったことを記載していきます。 やっていることは色々で、まとまりはありませんが、そこら辺はご容赦ください。
去年までにどのようなことをしてきたかはこの記事を参照ください。
この記事は クラウドワークス Advent Calendar 2023 シリーズ 1の2日目の記事です。
こんにちは。crowdworks.jp でエンジニアをしている高橋です。
この記事は本業、副業、趣味でソフトウェア開発をしていて感じたことや経験を振り返ったポエムになります。技術的な要素はなく、働き方の話がメインになります。
はじめは、個人のブログや個人利用の文章共有サービスにて書き残そうと思いましたが、フリーランスや副業関連のサービスを提供している側の会社に所属していることもあり、現在の状況について、誰かの参考になればと思い、クラウドワークスのエンジニアブログに書き残すことにしました。
続きを読む
この記事は クラウドワークス Advent Calendar 2023 シリーズ2 2日目の記事です。
こんにちは。crowdworks.jp SRE チーム 田中（@kangaechu）です。 年末といえば大掃除ですね。 皆さんのデータベースにも使っていないインデックスが溜まっていませんか？ お掃除してきれいな新年を迎えましょう。
未使用のインデックスは sys.unused_indexes ビューで確認できます。
しかし、このビューの元データである performance_schema テーブルは起動時から終了時までのデータしか保持していません。
Tables in the Performance Schema are in-memory tables that use no persistent on-disk storage. The contents are repopulated beginning at server startup and discarded at server shutdown.
https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html
そのため、長期間起動した状態のDBインスタンスを準備しましょう。
-- 未使用のインデックスを確認 SELECT object_name, index_name FROM sys.schema_unused_indexes WHERE object_schema = 'test' ORDER BY object_name, index_name;
sys.unused_indexes ビューには、削除してはいけないインデックスも含まれています。
今回対象となったのは、FOREIGN KEY 制約を持つインデックスです。
MySQLのドキュメントにはFOREIGN KEY 制約とインデックスの関係について、以下のような記述があります。
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
つまり、
-- parent table CREATE TABLE parent(id int PRIMARY KEY, name varchar(20)); -- child table CREATE TABLE child(id int PRIMARY KEY, name varchar(20), parent_id int, INDEX index_child_parent_id(parent_id), FOREIGN KEY fk_child_parent_id(parent_id) REFERENCES parent(id) );
このようなテーブルがある場合、child テーブルの parent_id 列にはインデックスが必要です。
そのため、 index_child_parent_id を削除しようとすると、インデックスが未使用であってもエラーになります。
mysql> DROP INDEX index_child_parent_id ON child ALGORITHM=INPLACE LOCK=NONE; ERROR 1553 (HY000): Cannot drop index 'index_child_parent_id': needed in a foreign key constraint
そのため、これらのインデックスは削除対象から除外しましょう。
削除対象が決まったところで、インデックスを削除したいところです、がやっぱり怖いですよね。 そんなあなたに朗報です。
MySQL 8.0からInvisible Indexという機能が追加されました。 これはインデックスをInvisible（=不可視）にできるという機能です。 レコードの追加・変更・削除時にインデックスは更新されますが、オプティマイザはそのインデックスを使いません。
みたいなことがないように、試しにオプティマイザがインデックスを使わない設定を試すことができます。
ALTER TABLE child ALTER INDEX index_child_parent_id INVISIBLE, ALGORITHM=INPLACE, LOCK=NONE;
インデックスをinvisibleにして問題ないことが確認できたら、実際に削除しましょう。
DROP INDEX index_child_parent_id ON child ALGORITHM=INPLACE LOCK=NONE;
このクエリでは ALGORITHM=INPLACE を指定していますが、これは クエリ実行時に使用するアルゴリズムを指定しています。
指定しない場合、INSTANT → INPLACE → COPYの順に適用されます。
指定した場合、そのアルゴリズムが使えない場合には失敗します。
今回は意図しない処理およびロックを防ぐため、明示的に指定しています。
オペレーションごとに指定可能なアルゴリズムはドキュメントに記載があります。
今回はOnline DDL Operations - Dropping an indexを参照し、In Place が Yes だったため、 INPLACE を指定しています。
記述が重複するため詳細な説明は省きますが、LOCK もALGORITHM と同様に指定しています。
「テーブルを作ったし、とりあえずインデックスを張っておこう」という理由でインデックスを作成することはよくあるかと思います。 しかし、無駄に作成されたインデックスはレコード追加・変更・削除時にコストが発生し、リソースを消費します。
この対応により、crowdworks.jpではセカンダリインデックスの20%以上を削除することができました。
統計情報の変更やバージョンアップなどにより、実行計画は変化していくため、必要なインデックスは随時変化します。 不要なインデックスは削除し、必要なインデックスを作成しやすい環境を構築していきたいと思います。
この記事はクラウドワークス Advent Calendar 2023 シリーズ 1の1日目の記事です。
クラウドソーシングサービス「クラウドワークス」（以下 crowdworks.jp ）にてエンジニアをしております、フロントエンドの可能性をしつこく信じ続ける@okuto_oyamaです。
一昨年・去年と引き続き、今年もアドベントカレンダー初日の盛り上げ手としてやっていきます。よろしくお願いします。
一昨年・去年もフロントエンド活動の振り返りをしてみましたが、今年もやっていきます。
続きを読む© 2016 CrowdWorks, Inc., All rights reserved.
