0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

SQLが遅い時の対処法

Last updated at Posted at 2022-12-25

背景

勤務先の自社システムが最近アクセス過多(sqlの発行回数が多くて)
サーバーダウンしてしまった。
流石にこれはよろしくない。
sqlの書き方ならわかるが、処理速度などに関しては知識がほぼ0の自分であるが、
調べたことをアウトプットのため、書くことにする。

まだほぼ無知な状態なのでそれはこうだよ!ここ違うよ!ということありましたら
コメントに書いていただけますとありがたいです。

対処法

①インデクスを貼る

インデクスを貼った方がいいよ、とはよく言われてることだろう。
もうよく知られたことかもしれないけれど、
インデクスとは、本に例えると索引、目次みたいなものだろう。
例えば投資に関する本を買って、
特にNISAについてピンポイントで知りたいとする。

本を最初からざっと毎ページ目を通しているとめちゃくちゃ時間がかかる。
でも、最初から索引ページを見て「NISA」の文字を調べて本のどこに
書いてあるか、が分かればそのページまでひとっ飛びでいくことができる。

つまりsqlでもインデクスとは処理速度を早くしてくれる機能のことである。

例えば以下のusersテーブルで名前検索しようとする。
単純に
select * from users where name ='山田花子';
スクリーンショット 2022-12-25 18.23.18.png

とでも書こうならば、テーブルフルスキャンと言って
テーブルを全行検索することになりデータが多いほど時間がかかる。

これを防ぐため、今回を例にインデクスを貼るのなら
「name」にはることになる。
インデクスを貼ることによてインデクステーブル(索引テーブル)が別途作られる。

name = 山田花子について検索するとき、
まず索引を確認して、usersテーブルのどこにあるかを探す。
それをすることで、ピンポイントで検索することができて、
処理速度を速めることができる。

②sql実行計画を確認

①のインデクスをつけても、あんまり速度が改善されないな〜となったらば
次に「sql実行計画」を確認する。

実行計画とはなんぞや?というと、
まずsqlは実行しても実際に処理が行われるまで以下の図の段階を踏んでいる。

・パーサー : 文法的エラーないか?対象のテーブルがそもそも存在するか?とか等をチェック
・オプティマイザー : 発行したsqlを実行するのに最も効率的な方法はないか?と候補を複数作成
・実行計画 : 最終的に結論付いた計画1つを実行する

スクリーンショット 2022-12-25 18.38.37.png

このように実際どんなsql処理が実行されたのか?というのが実行計画というものである。

そして実行計画はSQL分の先頭にexplain を書くことによって確認できる。

詳細は今回は省略させていただく。
↓の記事に詳しく説明されてあります!

mysql> explain select * from users where company_id = 2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 30543
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

③パーティションを貼る

そして、最後、実行計画を見ても
なんかまだだな〜と思った時はデータベースのパーティションというものを確認する。

パーティションとは何か?というと
テーブルを内部的になんらかのルールに振り分けて、そこにデータを分割する仕組み
のことである。

下の図は「リストパーティション」と言って、
キーカラム(今回は都道府県)を固定値で管理するやり方で、
47パーティションが作成され、
usersテーブルid = 1 と 2はprefecture_id = 1(北海道)
なので「1: 北海道」のパーティションにレコードが入ることになる。
usersテーブル id = 3 はprefecure_id = 2なので パーティション2:東京にレコードが入る。

select * from users where prefecture_id = 2 という
sqlが発行されたら、prefecture_id = 2(東京)のパーティションだけを
確認よくて、テーブルを全件検索(フルスキャン)する必要がなく
処理時間が早くできる、という仕組みである。

スクリーンショット 2022-12-25 18.50.10.png

参考文献等

以下参考にさせていただきました!

https://atmarkit.itmedia.co.jp/ait/articles/0901/29/news152_2.html
https://youtu.be/Aur62VsPrHg

0
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?