DBの処理を速くするアイデアや考え方の紹介 #SQL高速化
処理を速くしないとやばい
速度にこだわると楽しくなるのがサーバー。
いくらサーバーに余裕があろうとも速度を追求したい。
そんなところのコツを語る。
※DBを使う場合
１、基本：全体で考える
全ての処理の時間で考える。
当たり前のことですが、ここが抜けている人が多いよう思える。
DBの処理だけを速くしても意味がない。
PHPの処理など全体を合わせた時間を短くしないといけない。
また、速くなったけどメモリを1GB使うとかもダメです。
PHP、DB、それぞれ速くする方法はありますが、
「DBだけ」というのは限界がある。
「PHPと連携して処理をする」とした方が速いことが多いです。
そんなところを意識して次へいく。
２、DBの処理を速くする方法
そのJOIN本当に必要？
二つのテーブルをJOINして使うことはとても多い。
しかし、ほとんどの場合JOINする必要は無い。
別途にデータを作っておき、それを利用すればいい。
例えば、「記事ID.txt」というファイルを作り、
そこにカテゴリ名などを書いておく。
そのファイルを取得すればカテゴリ名がわかるので、JOINの必要が無くなる。
ただIDと照合するだけのテーブルはテキストファイル化するのが良いです。
PHPで読み込んで変数として処理するほうが圧倒的に速い。
DBでJOINすると便利で楽ですが、処理速度を考えると甘えた行為です。
できるだけPHP等で変数として使える設計にしよう。
３、データファイルはできるだけ小分けする
ファイルのサイズが大きいほど遅くメモリも消費する。
例えば、全記事のデータを一つのテキストファイルに書くする。
そうすると数十MBになるため、かなりの負荷になります。
1記事ごとにファイルを分ければ数十KBくらいになる。
そのため、面倒ではありますがファイルは増やします。
とにかく小分けする。小分けすればするほど処理は速くメモリも消費しない。
関数ファイルなんかもできるだけ小分けに。
ワードプレスはこれができていないからメモリ消費量が8MB程度と多い。
普通に必要な分だけ読み込むようにすると記事表示に300KBくらいしか使いません。
４、インポート処理はまとめてやる
都度インポートすると遅く負荷も大きい。
何かしらのデータをインサートしていくのがDBですが、
できる限り1時間に1回などまとめてやるようにしたい。
そうすると多量にインデックスが貼ってあっても問題無い。
まとめてインサートするとなると処理が増えて面倒になりますが、
そこから逃げずに戦わないといけない。
アクセスログ的なものなら、テキストファイルにCSV形式で追記していけばいい。
そのCSVをインポートすれば速いです。
ただ集計して別テーブルにインサートする場合でも、
データが多い場合は一度CSVにして、CSVとしてインポートする方が速い。
毎回インサートするのが楽ですが、甘えてはいけない。
５、キャッシュ前提で設計する
キャッシュで表示できる設計こそ至高。
リアルタイムで表示する必要がないコンテンツはキャッシュを表示する。
どんなコンテンツも1分更新なら問題ないことは多いだろう。
1分間のキャッシュだけでも同時アクセスにかなり強くなる。
絶対にリアルタイムじゃないとダメなものは何か？
そこを最初に考え、それ以外は全部キャッシュする。
在庫数表示だけリアルタイム、その他はキャッシュ、という感じです。
ほとんどのWEBサイトはキャッシュで表示できます。
したがって、DBへアクセスすることなんてほとんど無い。
毎回DBへアクセスする設計は失敗と考えてもいいくらいです。
キャッシュ前提の設計なら、安いVPSでも秒間500の処理くらい余裕です。
６、便利な関数に甘えない
できるだけ変数だけで対応したい。
都度関数を使ったりifで判別すると重たくなります。
一つ一つは軽いのですが、積み重なると大きいのがコード。
どうにか関数を減らせないか？と意識して設計したいところです。
例えば、画像サイズを取得する関数を使う場合、
「画像ファイル名.txt」とかのファイルに記載しておけばいい。
そのファイルを読み込むだけでサイズを取得できる。
関数で都度取得するより圧倒的に速いです。
ファイル名にサイズ等の情報を書いておくなど、
工夫すれば関数が必要なくなることばかりです。
極めるとほぼ変数だけで処理できるようになる。
cpuをできるだけ使わないコードを。
７、集計処理は高性能パソコンでやる
くそ重たい処理はデスクトップパソコンでやればいい。
ほとんどの場合、サーバーマシンのスペックはしょぼい。
自宅のcorei9-13900kでやるほうが圧倒的に速いです。
DBを丸ごとダウンロードさえしてしまえば、どこでも集計できる。
可用性等に問題はありますが、ベストに近い手法です。
現代の自宅サーバーとして一番の使い道ではないかと思う。
個人の場合、どうしても低スペックVPSしか借りられないので、
自宅のデスクトップパソコンを活用するのは最適です。
これが無理なら、時間単位で借りられるクラウドを使う手もある。
さくらのクラウドなら停止中はサーバーのみお金がかからないので、
集計処理の間だけ20コアマシンを起動すればいい。
低スペックマシンでの処理は限界があるので、
視野を広くいろいろなマシンを使うことを考えたい。
８、MYSQLから処理を実行してみる
PHPから実行するとシングルコアしか使わないことがある。
これはハッキリしていないので各自確認してほしいのですが、
自分がやっている処理の場合はそうなっています。
MYSQLコマンドでやると全てのコアを使うので爆速に。
そのため、重たい処理をする場合はTOPコマンドで「1」を押して、
各cpuの使用率を見てみてください。
1個しか使っていない場合はMYSQLから実行してみてほしい。
ただもちろん、全部のコアを使うと別の処理はできないので注意。
９、データ件数を減らす
うん、これが一番。
100万行の処理とか重くて当然です。限界がある。
本当にそんなにデータが必要か？と見直してみてほしい。
多少のユーザーは切り捨てるなどして、10万件くらいに減らしたい。
理想の設計と、妥協の設計がある。
速度を求めるなら妥協の連続になると思います。
これができないと日に日に苦しくなっていく。
1%のユーザーは捨てるような考えで良いと思う。
需要あるものだけ提供するようにすればスッキリします。
これができない責任者ではいけない。
まとめ
全体像を把握し、全体を最適化する。
要らないものは捨てて、必要なものだけある状態にする。
その中で最適な設計をして維持する。
こうして全体を考えないと速度は上がらない。
DBのSQL最適化はいくらでもありますが、
突き詰めるとそんな難しいことをやる必要がないとわかったりする。
単純なセレクト文で処理できることがほとんどです。
特に大事なのは「データ量」と「PHP等のプログラミング設計」。
これができていないとDB設計はどんどん苦しくなる。
結局、SQL最適化なんてのはクソ設計の尻拭いだと思います。
こんなところを意識して速度アップを考えてみてほしい。
