MySQLで50個のIDからレコードを取得したいときに、プレースホルダで50回叩くのとINで一回で取るの、どっちが速いか。
今日はふぁぼったーのフロントエンドのDB処理まわりをリファクタリングしました。この辺りも、もう1年半も開発・拡張を続けている部分なのでかなり汚くなっており、今後のためにできるだけソースを短くし、保守性の高いコードに書き直すなどの作業です。
そのなかで、以前から気になっていた点があったので、せっかくなので検証してみました。
前提
ふぁぼったーでは、発言をデータベースから取得する際、最初に発言とそのユーザーをSTATUSテーブルとUSERテーブルからSELECTして、そこで得た発言のIDを用いて、右下に出る各発言のふぁぼりユーザーをFAVORITEテーブルとUSERテーブルから別途SELECTしています。二つに分けたのはその方が速かったからですが、今回検証したのは、後半のふぁぼりユーザー取得部分の書き方です。
FAVORITEテーブルから複数の既知のステータスIDのレコードをSELECTしてきたい訳ですが、大体以下の二つの取得方法が考えられます。
比較
人間が書きやすいのは多分Aなのですが、パフォーマンスの面ではどの程度差が出るのか気になったので、ベンチマークを取ってみました。
条件
- 2008/11/22の人気ふぁぼられページでテスト
- SQLキャッシュは無効
- プレースホルダを利用する関係上、SQL単体での比較は難しい。よって、各方式でふぁぼりユーザーを取得する関数を作成し、関数全体の実行時間を比較
A方式.
SELECT SQL_NO_CACHE f.id as status_id,screen_name,profile_image_url from FAVORITE f,USER u where f.id=? and f.user_id=u.id; /*以下のプレースホルダで逐次実行。 1424797395, 1429571494, 1425533647, 1424770030, 1424798850, 1429457857, 1427719403, 1424996482, 1427697348, 1425186009, 1425883861, 1425883445, 1424981533, 1424930159, 1430825300, 1431058634, 1425179905, 1424813902, 1430527920, 1425768797, 1424796665, 1430748456, 1424828035, 1430514385, 1430474389, 1427714952, 1424987539, 1424839614, 1431577764, 1427783898, 1425414359, 1427950164, 1425191835, 1424916362, 1430541056, 1429964773, 1428692993, 1427342901, 1425160690, 1425081143, 1430872949, 1425871198, 1424959392, 1430738299, 1430707134, 1430345316, 1430150316, 1427859488, 1427784934, 1427701659, 1424995649 */
B方式.
以下のSQL文で実行
SELECT SQL_NO_CACHE f.id as status_id,screen_name,profile_image_url from FAVORITE f,USER u where f.id IN ( 1424797395, 1429571494, 1425533647, 1424770030, 1424798850, 1429457857, 1427719403, 1424996482, 1427697348, 1425186009, 1425883861, 1425883445, 1424981533, 1424930159, 1430825300, 1431058634, 1425179905, 1424813902, 1430527920, 1425768797, 1424796665, 1430748456, 1424828035, 1430514385, 1430474389, 1427714952, 1424987539, 1424839614, 1431577764, 1427783898, 1425414359, 1427950164, 1425191835, 1424916362, 1430541056, 1429964773, 1428692993, 1427342901, 1425160690, 1425081143, 1430872949, 1425871198, 1424959392, 1430738299, 1430707134, 1430345316, 1430150316, 1427859488, 1427784934, 1427701659, 1424995649 ) and f.user_id=u.id;
結果
- A方式(プレースホルダ)
- 0.0757秒
- B方式(IN句)
- 0.0390秒
参考：ページあたりのfav数の少ない「新着fav」ページでテストした結果
- A方式(プレースホルダ)
- 0.0279秒
- B方式(IN句)
- 0.0053秒
結論
2倍から6倍ほどIN句の方が高速でした。なんとなく予想はしていましたが、思った以上に一回のクエリで処理できるというアドバンテージは大きいみたいですね。
出来るだけ高速にしたい局面では、複数の既知のIDからレコードを取得する際にはIN句を使った方がよさそうです。
参考
実際に使った関数を置いておきます。
350 private function getStatusesFavotters(){
351 $favSQL="SELECT SQL_NO_CACHE f.id as status_id,screen_name,profile_image_url from FAVORITE f,USER u where f.id=? and f.user_id=u.id;";
352 preprint($favSQL);
353 $favStmt=$this->pdo->prepare($favSQL);
354 foreach ($this->statuses as $key => $rev) {
355 $favotters = array();
356 $favStmt->execute( array( $rev['status_id'] ) );
357
358 while($favotter=$favStmt->fetch(PDO::FETCH_ASSOC)){
359 $url=$favotter['profile_image_url'];
360 $favotter['profile_image_url_mini']=preg_replace("/(bigger|normal).(jpg|jpeg|gif|png)/i","mini.$2",$url,1);
361 array_push($favotters,$favotter);
362 }
363 $this->statuses[$key]['favotters'] = $favotters;
364
365 }
366 }
367 private function getStatusesFavotters2(){
368 $sids = array();
369 $favotters = array();
370 foreach($this->statuses as $st){
371 array_push($sids,$st['status_id']);
372 $favotters[$st['status_id']] = array();
373 }
374
375 $favSQL="SELECT SQL_NO_CACHE f.id as status_id,screen_name,profile_image_url from FAVORITE f,USER u where f.id IN (".implode(',',$sids).") and f.user_id=u.id;";
376 preprint($favSQL);
377 $favStmt = $this->pdo->query($favSQL);
378 while($favotter = $favStmt->fetch(PDO::FETCH_ASSOC)){
379 $url=$favotter['profile_image_url'];
380 $favotter['profile_image_url_mini']=preg_replace("/(bigger|normal).(jpg|jpeg|gif|png)/i","mini.$2",$url,1);
381 array_push($favotters[ $favotter['status_id'] ],$favotter);
382 }
383 foreach( $this->statuses as $key => $rev ){
384 $this->statuses[$key]['favotters'] = $favotters[ $rev['status_id'] ];
385 }
386 }
