こんにちは。バックエンドエンジニアの米山です。
私個人はJava+Oracleでお仕事をする期間が長かったのですが、昔はよくOracle側のパフォーマンスチューニングを行っていました。
弊社では、まだ採用ケースは少ないのですがDBにMySQLを採用し、経年によるデータ増加に対応する場合にどうするべきか？を考えてみようと思います。
準備
まずデータ増加した(ということにする)DBを用意します。
本当は100万件・・・と言いたいところですが、時間がかかりそうなので10万件程度にしておきます。
以下のような構成で作成しました。
- MySQLのバージョンは8(8.0.22)
- テーブルを3つ用意する(samples,cities,join_data)
- samplesにデータを10万件insertしておく(カラム数や中のデータは適当に散らばせる)
- samplesにINDEXを作成(SAMPLES_IDX_01、SAMPLES_IDX_02)
今回は、データはLaravelのSeederを使って作成しました。
チューニングしてみる
準備ができたら、実行計画を取ります。
MySQLで実行計画を取る場合は「EXPLAIN」句を使います。
データ量が大したことないのですぐ返却されましたが(笑)テーブルのJOINはB(cities)->A(samples)->C(join_data)の順に結合されていること、Cへの結合はHASH JOINで行われていることなどがわかります。
INDEXもほど良く使われていますね。
Oracleではお馴染みのオプティマイザヒントですが、MySQLでも使えるようです。
試しにJOINの順番を変えてみましょう。
JOIN順番を指示するには「JOIN_ORDER」ヒントを指定します。
実行計画が変わったことが確認できました。
実務でよくあるのがINDEXヒントの導入です。
ビッグデータ且つデータの分布率が思わしくなく、INDEXを作成したのに有効に効かない時にクエリで指定してあげる、というケースが多いです。
MySQLでのINDEXヒントはOracleとは違い、FROM句の中で行います。
どうでしょうか？実行計画が変わったのがわかりますか？
USE INDEX(SAMPLES_IDX_02)を指定することで、意図的にそちらのINDEXを使用するようになりました。
但し、INDEXヒントはデータの状況や選択したINDEXによっては、逆にパフォーマンスを悪化させることもありますのでINDEXの選定と併せて、使用はご注意下さい。
最後にSQL実行時間の測定です。
実行計画を見ながら、実際にSQLが早くなったのかを確認していきます。
mysqlコマンドに-vvvオプションをつけて実行します。
今回紹介した、JOIN_ORDERとUSE INDEXを両方適用してSELECT文を実行しました。
本当に僅かですが実行時間が変わったことと思います。
プログラムコードを弄るほどではないけど、画面表示のパフォーマンスを改善したい！という時には効果的です。
最後に
Wizではエンジニアを募集しております。
興味のある方、ぜひご覧下さい。
