comments にインデックスを貼る
https://scrapbox.io/files/67397862493299018f3f2b30.png
code:sql
explain SELECT * FROM comments WHERE post_id = 9997 ORDER BY created_at DESC LIMIT 3;
alter table comments add index post_id_index (post_id);
comments テーブルの検索時に post_id のインデックスが貼られておらず、全検索になっていた。
そのため、 post_id にインデックスを貼る。
これで、4000 → 22000 ぐらい
created_at に対してインデックスを貼る
code:sql
explain SELECT * FROM comments WHERE post_id = 9997 ORDER BY created_at DESC LIMIT 3;
alter table comments add index post_id_index (post_id);
上記のSQLクエリにおいて、 order by が含まれておりソートが含まれている。
そのため、 created_at を含むインデックスを作成することでソートを避けられる。
code:sql
alter table comments drop index post_id_index, add index post_id_index (post_id, created_at);
code:sql
mysql> explain select * from comments where post_id = 100 order by created_at desc limit 3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: comments
partitions: NULL
type: ref
possible_keys: post_id_index
key: post_id_index
key_len: 4
ref: const
rows: 5
filtered: 100.00
Extra: Backward index scan
1 row in set, 1 warning (0.00 sec)
上記のように created_at についてもインデックスが使えていることがわかる。
逆順インデックスはどうやら遅いので（HDDなど？シーケンシャルなほうが取得しやすいはず）、 降順インデックスを作成する。
code:sql
alter table comments drop index post_id_index, add index post_id_index (post_id, created_at desc);
mysql> explain select * from comments where post_id = 100 order by created_at desc limit 3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: comments
partitions: NULL
type: ref
possible_keys: post_id_index
key: post_id_index
key_len: 4
ref: const
rows: 5
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
これによって、 Extra がなくなり もっとも効率よく取得できるようになった。
セカンダリインデックスのみで返せるようにする
select count(*) from comments where user_id = 123
上記に対して 以下を実行する。
code:sql
alter table comments add index user_id_index (user_id);
mysql> explain select count(*) from comments where user_id = 123\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: comments
partitions: NULL
type: ref
possible_keys: user_id_index
key: user_id_index
key_len: 4
ref: const
rows: 100
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
このとき count(*) であればセカンダリインデックスのみで件数の計算が行える。
Using index とひょうきされている場合、 Covering Index, つまりセカンダリインデックスのみで計算が行えており
全文検索を行う
code:sql
mysql> explain select * from comments where comment like "%hoge%"\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: comments
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 99764
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
LIKE でインデックスを使えるのは前方検索の場合のみ。 %hoge の時点でインデックスが使えない。
形態素解析や N-gram によって転置インデックスを作成し、テキスト検索できる。
mysql でもおこなえる。
code:sql
alter table comments add fulltext index comments_full_index (comment) with parser ngram;
mysql> explain select * from comments where match(comment) against ('hoge' in boolean mode)\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: comments
partitions: NULL
type: fulltext
possible_keys: comments_full_index
key: comments_full_index
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: no_ranking
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
上記のように index が使えていることがわかる。
ただし、できるだけ ElasticSearch のような専用エンジンをつかったほうがいい。
N+1を解決する
対策方法としてはおもに３パターン
1. キャッシュを使う
2. クエリでプリロードする
3. JOIN を使う
1 はキャッシュに依存するため最終手段。
2 がもっとも楽な方法。
上記 30 ユーザの Post 投稿一覧を取得する場合「上位 30 ユーザを取得する」→「上位 30 ユーザの uid を go で配列化する」→「IN句を使って Posts を取る」のように 2 クエリにする。
実装も楽なので最適化の最初のステップとしてよい。
3 は JOIN を行う必要がある。
クエリが１回で済むため最も効率的だが「ちゃんとインデックスが効いているか？」「クエリが複雑になる」という面倒さがある。
が、SQL に慣れていればこれでよい。
プリペアドステートメントを無効化する
プリペアドステートメントによって
SQL クエリをあらかじめ発行して DB 側でキャッシュしておく
SQLクライアントからは変数だけを送信して、DB側でクエリを実行する
ことによって、
実行計画がキャッシュされて同じクエリを何度も実行する場合に効率がいい
セキュリティ対策になる
メリットがある。
その場合は、プリペアドステートメントを無効化する。
有効にすべきか、無効にすべきかは実際にベンチマークを動かすのがよさそう。
コネクションを永続化させる
App と DB で毎回 TCP コネクションを貼るのは無駄であるため、コネクションを永続化して使い回す。
SetMaxOpenConns
SetMaxIdleConns
を設定して、コネクションを使い回すのがよい。
MySQL 側に許容できるコネクション数が SELECT @@max_connections で確認できる。
こちらも my.cnf で設定できるため、 mysql 側も修正するのがよい。