SREチームの金澤です。
Lancers（ランサーズ） Advent Calendar 2018 5日目の記事になります。
昨日は、inamuuさんのElasticCloudからAmazon Elasticsearch Serviceへの移行して良かったこと大変だったことでした。
今年は、Lancers以外のAdvent Calendarも参加させていただいてます。(以下)
- PHP Advent Calendar 1日目
- CakePHP Advent Calendar 5日目
- SRE Advent Calendar 5日目
- PHP Advent Calendar 7日目（予定）
- CakePHP Advent Calendar どこか（予定）
本日の記事は、CakePHP Advent Calendar 5日目との掛け持ちにしようと思っていたのですが、それができないことが今更ながらわかりました。なんという失態。。
なので、お蔵入りしてした社内用ドキュメント（SQLチューニングポリシー）を公開することにしました。（なぜそうなるのかの考察をもっとしたいものではありましたが。。。）
なお、この記事は
- CakePHP Advent Calendar 5日目
- SRE Advent Calendar 5日目
と関連する記事でもあります。
イントロダクション
このドキュメントは、アプリエンジニアが新機能を実装する際の資料として用意したものです。
過去、新規機能実装時に、新規テーブルに適切なインデックスが付与されていなかったり、不用意に負荷のかかるSQLを発行していたことが多かったため、実装前の参考資料として書いていました。
※ドキュメント作成時のMySQLのバージョンは5.6です。
本番DBでの事前検証
EXPLAIN文での確認
実装時に、負荷のかかりそうなSQLを発行する場合は、本番DBのレプリカで事前に実行計画を確認しておいてください。
mysql> EXPLAIN SELECT …;
EXPLAIN文については、以下のページに詳しい解説があります。
漢(オトコ)のコンピュータ道：MySQLのEXPLAINを徹底解説!!
レスポンス確認
同様に、実際のレスポンスタイムを本番DBのレプリカで事前に確認しておいてください。
SQLをチューニングして、EXPLAIN上は改善していても、実際のレスポンスは改善しないパターンもあります。その場合は、レスポンスタイムの早いほうを選択してください。
SQL発行時の注意点
日付型カラムでのインデックス範囲検索は1ヶ月以内が目安
1ヶ月以上広範囲になると、インデックスが採用されずフルスキャンがかかる可能性が高くなります。
※データ量や統計情報に依存します。開発環境で適切にインデックスが採用されていても本番環境で同じ動作をするとは限らないので、事前に本番DBのレプリカでも検証しておいてください。
※例：proposalsテーブルのcreated範囲検索にインデックスが採用されない例
mysql> EXPLAIN SELECT `Proposal`.`id`, `Proposal`.`string` FROM `proposals` AS `Proposal` WHERE Proposal.created > '2014-11-01 00:00:00'; +----+-------------+----------+-------+------------------------+------------------------+---------+------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+------------------------+------------------------+---------+------+--------+-----------------------+ | 1 | SIMPLE | Proposal | range | proposal_index_created | proposal_index_created | 6 | NULL | 221634 | Using index condition | +----+-------------+----------+-------+------------------------+------------------------+---------+------+--------+-----------------------+ 1 row in set (0.01 sec)
FORCE INDEX（USE INDEX）を使うときの注意点
MySQLがどうしても適切なインデックスを選択してくれない場合、FORCE INDEX（USE INDEX）を使うことで、強制的にインデックスを選択させる方法があります。
ただし、FORCE INDEX（USE INDEX）を使うと、将来的に以下のようなデメリットが生じるので、あくまでも一時的な手段にしておいてください。
- インデックスチューニングをしても、FORCE INDEXを指定しているクエリは改善されない
- FORCE INDEXに指定したインデックスを削除するとエラーを引き起こす可能性がある
- FORCE INDEX(PRIMARY)は大丈夫
- FORCE INDEX(インデックス名)のインデックス名が削除されるとエラーになる
理想的なのは、FORCE INDEXを使わず、MySQLが自然に適切なインデックスを選択できるようなインデックス構成やSQLにしておくことです。
上記の注意点を理解した上で、一時的なパフォーマンス対策の手段として利用してください。
テーブル作成時のインデックス付与の指針
やみくもにインデックスを付与しない
インデックスを付与すると、データ更新時の負荷が増えます。
マスターDBに負荷をかけることになりますので計画的に付与する必要があります。
特に更新頻度の多いテーブルは注意が必要です。
パフォーマンスが悪いからと、やみくもにインデックスを付与してしまうと、MySQLの実行計画が狂い、他のSQLにも悪影響を与える可能性があります。
createdでソートする目的でインデックスを付与しない
PRIMARY KEYであるidカラムでも同様のソートが可能です。
※抽出目的でcreatedに付与する場合は、ソートもcreatedで統一します。
カーディナリティの低いカラムにはインデックスを付与しない
例えば、各テーブルのdeletedカラムは、カーディナリティが2しかない（0か1の値しか持たない）ため、インデックスの付与はほとんど効果がありません。
むしろ、MySQLが誤ってこのインデックスを採用してしまい、パフォーマンスが落ちることもあります。
※ただし、0より1の割合が圧倒的に少数であるカラムに対し、かつ1での検索が大半を占める場合などは、カーディナリティが低くても有効に機能しますのでその条件を満たす場合は付与できます。
複合インデックスの採用条件
MySQLでは、原則1テーブルにつき1つしかインデックスを使わないため、複数カラムにインデックスを効かせるためには複合インデックスを付与する必要があります。
しかしながら、MySQLの場合は複合インデックスを作成すると、既存のSQLの実行計画が変更され、場合によっては既存SQLのパフォーマンスが悪化する可能性があります。
パフォーマンス検証スクリプト（※独自に用意した、サービス全画面のSQLを実行するスクリプト）を実行し、大幅にパフォーマンスが悪化するSQLがないことを確認しておきましょう。
また、複合インデックスは更新負荷が大きくなりますので、採用頻度と合わせて慎重に検討が必要です。
複合インデックスを採用する場合、先頭カラムの単一インデックスは削除する
複合インデックスの先頭カラムは単一インデックスとしても作用するので、複合インデックスを付与する場合は、先頭カラムの単一インデックスが存在していたら削除しておくことで、インデックス量が減り、マスターDBの更新負荷が軽減されます。
ただし、単一インデックスのときよりもパフォーマンスが落ちる場合もあるので、事前検証が必要です。
複合インデックスは、カーディナリティの高いカラム順に並べる
複合インデックスを付与する場合は、できるだけカーディナリティの高い順に並べてください。
発行するSQLによるので、どんなときも適用できるルールではありませんが、極端にカーディナリティの低いカラムを先頭にすると効果が出にくくなります。 （MySQLが優先的に採用してしまい、効果が落ちることもある）
以下、categorizationsテーブルの（よくない）例
mysql> SHOW INDEX FROM categorizations; +-----------------+------------+------------------------------------------------------------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+------------------------------------------------------------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | categorizations | 0 | PRIMARY | 1 | id | A | 1295306 | NULL | NULL | | BTREE | | | | categorizations | 1 | category_id | 1 | category_id | A | 164 | NULL | NULL | | BTREE | | | | categorizations | 1 | categorization_type | 1 | categorization_type | A | 6 | NULL | NULL | YES | BTREE | | | | categorizations | 1 | categorization_id | 1 | categorization_id | A | 1295306 | NULL | NULL | YES | BTREE | | | | categorizations | 1 | category_id_categorization_type_categorization_id | 1 | category_id | A | 154 | NULL | NULL | | BTREE | | | | categorizations | 1 | category_id_categorization_type_categorization_id | 2 | categorization_type | A | 398 | NULL | NULL | YES | BTREE | | | | categorizations | 1 | category_id_categorization_type_categorization_id | 3 | categorization_id | A | 1295306 | NULL | NULL | YES | BTREE | | | +-----------------+------------+---------------------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 7 rows in set (0.01 sec)
お知らせ
12/15（土）にPHPカンファレンスに登壇いたします。
http://phpcon.php.gr.jp/2018/
13:00 ～ 13:25 Track 6 3F特別会議室
ランサーズのCakePHP1.3→Cake2.8移行
ランサーズでは、約1年かけて、CakePHP1.3をCakePHP2.8への移行を進めており、現在の進捗は99%です。（今年中には完了させたい）
カンファレンスでは、移行で得たノウハウを詳細にお話したいと思います。
明日は、odrum428さんの「slackで動くピアボーナス機能を実装した話」になります。