質問: **悩んでいる点** 「tテーブルcカラムに格納されている文字数の合計」を取得したい。 結果自体は取得できるが、下記何れで実装した方が重くならないか、知りたい ・MySQL ・PHP --- **MySQL** 下記を試したら、TypeがALLでした ``` explain SELECT sum(CHAR_LENGTH(c)) FROM `t` WHERE `a_id`=1 ``` > > ALL フルスキャンなので一番重い。改善必須 > > > と書かれていたのでMySQLではなくPHPで実装しようかと思ったのですが、 試しに下記を実行したらTypeは同じくALLでした(インデックスの問題??) ``` explain SELECT c FROM `t` WHERE `a_id`=1 ``` --- **Q1** explain で TypeがALLだからと言って必ずしも改善する必要はない? **Q2** `SELECT sum(CHAR_LENGTH(c)) FROM`t`WHERE`a_id`=1`を見て何か思うことはありますか? sumやCHAR_LENGTHは(重くなりそうなので)なるべく使用しない方が良い? **Q3** ・MySQLでもPHPでも実装できる場合は、どう判断? ・環境に依存するので両方実装して速度測定するしかない? ・その場合、レコード数が増加する度に、計測しなおす??
回答: 確認しなければならない変数が多い質問なので非常に回答は付きづらいものと思います。 例えばリソース(CPUやメモリ)が十分予算も潤沢にあるのであればあまり気にせず動くことだけに注力できますし、非常に限られたリソースで実装する場合はかなり厳密に検査を実施する必要があります。 またアプリケーション的に頻繁に動かないバッチ処理でかつ実行時間に制約がないものであれば無理に改修する必要はないはずです。しかしながら厳密な性能(たとえば一プロセスは0.5secを超えないとか)が設定されている場合は厳密に見る必要があります。 さらにはアプリケーションの仕様で書き込みが多いのかそれとも参照が多いのかといったことでもインデックスを貼るべきかどうかの判断がわかれます。 その上で考慮した回答をさせていただきます。 > > Q1 explain で TypeがALLだからと言って必ずしも改善する必要はない? > > > A1 アプリケーションの作りによります。 explainでALLはインデックスを貼ったほうが良いよというメッセージになります。 ですがインデックス自体デメリットがあります。 インデックスを貼ると INSERTやDELETEもしくはインデックスを張った行のUPDATEな更新が重くなります。 そのため日に100万回更新するデータに対して月一回のSELECTと比べた場合は明らかにINDEXははらないほうが良いといえるでしょう。 アプリケーションの作りを確認してトータルでどちらが良いか判断してください。 > > Q2 SELECT sum(CHAR_LENGTH(c)) FROM t WHERE a_id=1を見て何か思うことはありますか? > > sumやCHAR_LENGTHは(重くなりそうなので)なるべく使用しない方が良い? > > > これもサーバの状況やアプリケーションの作りによります。 サーバとしてMySQL側に負荷がかけれないのであればPHPで実装するべきでしょう。 しかしながらアプリケーションとしてtテーブルのデータ行が100万とかだった場合はPHPに展開するだけでかなりのメモリを消費してしまいます。 Q3 > > MySQLでもPHPでも実装できる場合は、どう判断? > > > これもサーバの状況や求められている性能(非機能要件)やアプリケーションによります。 (1)性能の話 WEBサーバとDBサーバどちらがスケールアップ&スケールアウトしやすいかを考えると、断然Webの方が停止開始のハードルが低い、ロードバランサーによる分散構成が取りやすいWeb側であるPHPの方に負荷をかけるようにしておく意識が高いと思います。いざとなったらスケールアウトで分散できる方に重い処理を書くようにするのです。 しかし現在提供されている様々なクラウドやマネージド・サービスですとリードレプリカやオートスケールの構築もそれほど難しくはなくなってきています。 (2)アプリケーションの話 PHPで実装するにしても100万行もあるデータを実際に全部取り出してstrlenを実行すると100万行のデータをPHPに展開する必要がありそれなりにメモリが必要になると思います。そうなるとMYSQL側でやったほうがいいという判断もできます。 しかし、たった数行程度だったりすでにデータとして取得している場合などはPHP側で実装したほうが良いと思います。 > > 環境に依存するので両方実装して速度測定するしかない? > > その場合、レコード数が増加する度に、計測しなおす?? > > > もちろん非機能要件として ・一定の性能が求められている場合 ・特定のデータ量で性能を保証しなければならない場合 このような状況であれば環境依存になるので実際に測定を確認するしかないでしょう。 更に考えなければ行けないのは 速度性能として CPU>メモリ>ディスク>ネットワーク となるのでそれを考慮したアーキテクチャ設計が必要になります。 indexやSQLの速度を気にするあまりSQLの呼び出しを増えてしまうとネットワークという最遅のレイヤーに負荷をかけてしまい、結果的には総合的なスループットは落ちてしまう結果が生まれてしまうこともあります。 ぜひまずはご自身のプロダクトの非機能要件と仕様を確認していただき総合的に判断をしていただければと思います。