こんにちは。
コンちゃんこと佐々木です。
MySQLのインデックスの知見を得たので書き下しておきます。
DB高速化のためには、インデックスを張るのが有効です。
紙の辞書には「あで始まる単語はここから」「いで始まる単語はここから」「うで始まる～」というような索引がありますが、これを用意するのと同じ感じ。
検索(SELECT文)が早くなります。
テーブルごとに用意します。
Userテーブルはuser_idカラムにインデックスを張る、といったように。
注意点ですが、インデックス情報を持つためにDBの容量を使うはずなので、必要なものだけに張りましょう。
実行環境は以下。
- MySQL8.0系
- Python3.7系
- SQLAlchemy1.3系
インデックスを張るかどうかの検討手順
遅いクエリを見つけ、ステージング環境で改善検証し、本番実施の流れです。
1. DBに飛んでいるSELECTクエリ文の把握
以下の手段で把握します。
- DBにクエリを飛ばしているプログラムのコードを読む
- ORMapper(以下ORM)を利用している場合はORMのデバッグ出力等をONにしてORMが作るクエリ文を出力させる
- PythonでORMであるSQLAlchemyを使用している場合は、ログレベルをデフォルトのWARNからINFOに設定変更すると出力される
- https://docs.sqlalchemy.org/en/14/core/engines.html#configuring-logging
- スロークエリログを出力させ確認する
- MySQL自体が持っている機能で、DBに対して設定用クエリを飛ばすか、AWSのRDSを使用しているならマネジメントコンソールから(ほぼ)GUIで設定できる
どのクエリの速度を改善したいかを決めましょう。
2. 実行計画を見る
先のクエリの先頭に EXPLAIN を付与すると実行計画なるものを見られます。
例えば、SELECT * FROM user WHERE user_id=1;
というクエリの実行速度を見るなら、EXPLAIN SELECT * FROM user WHERE user_id=1;
とします。
以下は、私が実際に見たEXPLAIN結果をちょいとぼかしたものです。
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 1383610 | 100.00 | Using where |
| 1 | PRIMARY | t | NULL | eq_ref | PRIMARY | PRIMARY | 8 | hogedb.a.huga_id | 1 | 10.00 | Using where |
| 1 | PRIMARY | NULL | ref | 15 | hogedb.a.huga_id2,hogedb.a.fuga_id3 | 10 | 100.00 | Using index | |||
| 2 | DERIVED | テーブル名 | NULL | ALL | NULL | NULL | NULL | NULL | 1383610 | 11.11 | Using where; Using temporary |
ざっくり確認するなら以下3つ。
- keys: クエリ実行時に選択されたインデックス名が入るのでここがNULLだと改善余地あり
- rows: クエリ実行時に検索する行数(おおまかなもの)なのでここがテーブルの全行数に近かったりすると改善余地あり
- Extra: Using indexがない場合はインデックスが使われていない(あるいは張られていない)ので改善余地あり
インデックスが張られている/いない だけ確認したい場合はSHOW INDEX FROM テーブル名 でOKです。
これも結果例を載せておきます。
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| テーブル名 | 0 | PRIMARY | 1 | id | A | 1383610 | NULL | NULL | BTREE | YES | NULL |
3. 現状の時間計測
WorkbenchやSequel Ace等のDBクライアントからクエリを飛ばすと実行時間が出るのでそれをメモしておきます。
書きながら思ったけど、別にクライアント使わず直にログインしてクエリ飛ばしても実行時間出ますね…。
インデックスを張る前後でクエリ実行結果に差異がないことも確認したい場合はここで実行結果をエクスポートしておきます。
4. ステージング環境でインデックスを張る
いきなり本番環境にインデックスを張らずに、まずはステージングで検証しましょう。
ステージング環境が本番環境とデータ量が同じであればあるほど検証が正確で嬉しいですね。
本番環境の当該テーブルに1億レコードあるのにステージング環境だと1000レコード、だとそもそもステージング環境はインデックス張らずとも早そうですし…。
クエリ文に本番固有の情報が入っている場合(WHERE句でuser_id=1000000を指定しているがステージング環境にそんなデータがないとか)は、そこだけステージング環境用に書き換えるなどする必要があります。
インデックスを張るには、まずどのカラムに張るかを決める必要があります。
当該クエリのWHERE句で指定されているカラムに張るのが良いみたいです。SELECT * FROM user WHERE user_id=1; だとuser_idに張る、といった感じ。
CREATE INDEX文、またはALTER TABLE文でインデックスを張ることができます。
先の例でいくと、ALTER TABLE user ADD INDEX user_id_index(index_id) といった感じ。
インデックス名を決めて指定する必要があります。
あとはインデックスを張るだけですが、ミスった時や効果なかった時にすぐ戻せるように、トランザクションを張っておきましょう。begin;
さらに、自動コミット機能も切っておきましょう。set autocommit = 0;
切れたかどうかの確認はshow variables like 'autocommit';
です。
ここまできたら、先ほどのALTER TABLE文でインデックスを張りましょう！
SHOW INDEX文での確認も忘れずに。
張れたら、EXPLAINで実行計画を確認。
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | NULL | ALL | NULL | NULL | NULL | NULL | 7632 | 100.00 | Using where | |
| 1 | PRIMARY | a | NULL | ref | インデックス名 | インデックス名 | 6 | b.hoge_id1 | 4 | 10.00 | Using where |
| 1 | PRIMARY | t | NULL | eq_ref | PRIMARY | PRIMARY | 8 | hogedb.a.hoge_id2 | 1 | 10.00 | Using where |
| 2 | DERIVED | テーブル名 | NULL | range | インデックス名 | インデックス名 | 6 | NULL | 7632 | 100.00 | Using index condition; Using MRR; Using temporary |
rowsが減って、keyにインデックス名があってインデックスが使われそうですね！
では問題の遅かったクエリの速度確認。
3. と同じことをします。
改善しましたか…？
私の実例では、5.8sのクエリが0.04sになりました。
インデックスを張る前後でクエリ実行結果に差異がないことも確認したい場合はここで実行結果をエクスポートして、前にエクスポートしたものと差分を取りましょう。
DBのサイズやインデックスを張ったテーブルのサイズも見ておきましょう。
以下クエリで確認できます。
-- DBsize
SELECT
table_schema, sum(data_length+index_length) /1024 /1024 AS MB FROM information_schema.tables
WHERE
table_schema = データベース名;
-- table size
SELECT
table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length+index_length)/1024/1024) AS all_mb, floor((data_length)/1024/1024) AS data_mb, floor((index_length)/1024/1024) AS index_mb
FROM
information_schema.tables
WHERE
table_schema=database() AND table_name=テーブル名 ORDER BY (data_length+index_length) DESC;
最後にトランザクションの締めくくり処理。
戻さなくてよい時は commit; を最後に、戻す時は rollback; を最後に打てばOKです。
まあ、インデックス削除文もある(ALTER TABLE テーブル名 DROP INDEX インデックス名;)ので、トランザクションが必須かといわれるとそうでもないのですが、練習ってことで。
(トランザクション使用例を全然見ないので、一般的にはやらないのかな…)
5. 本番実施
改善が確認されたら、同じことを本番環境のDB宛に実施します。
その他
そもそも遅いクエリが1つあるケースよりも、ミリ秒で終わるクエリを大量に(数百～数千以上)飛ばした結果遅くなるケースの方が多いかも、そうなると改善すべきはDBではなくDBに接続するプログラム側となります。
いわゆるN+1問題をイメージしています(これはこれでまた記事書きたい)。
また、PythonでSQLAlchemy+マイグレーションツールにalembicを用いている場合、以下1行を当該テーブルのモデルclassに書いておきます。Index(インデックス名, カラム名)
すると、alembicの通常の手順に従ってマイグレーションスクリプトを作って適用するとインデックスが張られますので便利です。
最後に登場したクエリをまとめておきます。
-- インデックスを張る手順
begin;
set autocommit = 0;
show variables like 'autocommit';
-- add index
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);
show indexes from テーブル名;
-- DBsize
SELECT
table_schema, sum(data_length+index_length) /1024 /1024 AS MB FROM information_schema.tables
WHERE
table_schema = データベース名;
-- table size
SELECT
table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length+index_length)/1024/1024) AS all_mb, floor((data_length)/1024/1024) AS data_mb, floor((index_length)/1024/1024) AS index_mb
FROM
information_schema.tables
WHERE
table_schema=database() AND table_name=テーブル名 ORDER BY (data_length+index_length) DESC;
-- explain, check speed
-- 実行計画を見たり速度を確認したりしましょう
-- commit
set autocommit = 1;
show variables like 'autocommit';
--rollback;
--commit;
お疲れさまでした。
参考リンク