PHP
1<?php2// 計測結果3/* 4 $count = 100000; の場合 5 array ( 6 'A: PKがidでslugも持つケース' => 7 array ( 8 'コメント投稿を計測' => '98.272729873657 秒', 9 'コメント取得を計測' => '11.397902011871 秒', 10 ), 11 'B: PKがslugのケース' => 12 array ( 13 'コメント投稿を計測' => '105.66755986214 秒', 14 'コメント取得を計測' => '11.046698093414 秒', 15 ), 16 ) 17*/18 19// 計測を実行20echo '<pre>';21try {22 $pdo = new PDO('mysql:host=localhost;dbname=database_name', 'username', 'password');23 $count = 100000;24 var_export([25 'A: PKがidでslugも持つケース' => [26 'コメント投稿を計測' => test_insert('A', $count),27 'コメント取得を計測' => test_select('A', $count)28 ],29 'B: PKがslugのケース' => [30 'コメント投稿を計測' => test_insert('B', $count),31 'コメント取得を計測' => test_select('B', $count)32 ]33 ]);34} catch (Exception $e) {35 var_export([36 'exception' => get_class($e),37 'message' => $e->getMessage()38 ]);39}40echo '</pre>';41 42// コメント投稿を計測43function test_insert($test_name, $count){44 // 計測を実行45 $start_time = microtime(true);46 for ($i = 0; $i < $count; $i++) {47 $comment_col = insert_comment($test_name);48 $tag_cols = insert_tags($test_name, rand(1, 5));49 insert_relations($test_name, $comment_col, $tag_cols);50 }51 $end_time = microtime(true);52 return ($end_time - $start_time) . " 秒";53}54 55// コメント取得を計測56function test_select($test_name, $count){57 // 現在のslugを取得58 $slugs = array_column(select_all_comments($test_name), 'slug');59 60 // 計測を実行61 $start_time = microtime(true);62 for ($i = 0; $i < $count; $i++) {63 select_comment($test_name, $slugs[$i]);64 }65 $end_time = microtime(true);66 return ($end_time - $start_time) . " 秒";67}68 69 70 * INSERT 71 72 73// コメントテーブルへ INSERT74function insert_comment($test_name){75 global $pdo;76 try {77 $slug = get_slug();78 $content = get_content();79 $stmt = $pdo->prepare("INSERT INTO comments{$test_name} (content, slug) VALUES (?, ?)");80 $stmt->execute([$content, $slug]);81 if ($test_name === 'A'){82 return $pdo->lastInsertId();83 } else if ($test_name === 'B'){84 return $slug;85 }86 } catch (PDOException $e) {87 // slug が重複したらもう一度88 if ($e->getCode() === '23000') {89 return insert_comment($test_name);90 } else {91 throw new PDOException($e->getMessage());92 }93 }94}95 96// タグテーブルへ INSERT97function insert_tags($test_name, $tag_count){98 $tag_cols = [];99 for ($i = 1; $i < $tag_count; $i++)100 $tag_cols[] = insert_tag($test_name);101 return $tag_cols;102}103 104function insert_tag($test_name){105 global $pdo;106 try {107 $slug = get_slug();108 $tag_name = get_tag_name();109 $stmt = $pdo->prepare("INSERT INTO tags{$test_name} (tag_name, slug) VALUES (?, ?)");110 $stmt->execute([$tag_name, $slug]);111 if ($test_name === 'A'){112 return $pdo->lastInsertId();113 } else if ($test_name === 'B'){114 return $slug;115 }116 } catch (PDOException $e) {117 // slug が重複したらもう一度118 if ($e->getCode() === '23000') {119 return insert_tag($test_name);120 } else {121 throw new PDOException($e->getMessage());122 }123 }124}125 126// リレーションテーブルへ INSERT127function insert_relations($test_name, $comment_col, $tag_cols){128 global $pdo;129 foreach($tag_cols as $tag_col){130 $stmt = $pdo->prepare("INSERT INTO relations{$test_name} (comment_col, tag_col) VALUES (?, ?)");131 $stmt->execute([$comment_col, $tag_col]);132 }133}134 135 136 * SELECT 137 138 139 function select_comment($test_name, $slug){140 global $pdo;141 $stmt = $pdo->prepare("SELECT * FROM comments{$test_name} WHERE slug = ?");142 $stmt->execute([$slug]);143 return $stmt->fetch();144}145 146function select_all_comments($test_name){147 global $pdo;148 $stmt = $pdo->query("SELECT * FROM comments{$test_name}");149 return $stmt->fetchAll();150}151 152 153 * その他の処理 154 155 156// content を生成157function get_content($num = 6){158 $hiragana = array('あ', 'い', 'う', 'え', 'お', 'か', 'き', 'く', 'け', 'こ', 'さ', 'し', 'す', 'せ', 'そ', 'た', 'ち', 'つ', 'て', 'と', 'な', 'に', 'ぬ', 'ね', 'の', 'は', 'ひ', 'ふ', 'へ', 'ほ', 'ま', 'み', 'む', 'め', 'も', 'や', 'ゆ', 'よ', 'ら', 'り', 'る', 'れ', 'ろ', 'わ', 'を', 'ん');159 $str = "";160 for($i = 0; $i < $num; $i++)161 $str .= $hiragana[array_rand($hiragana)];162 return $str;163}164 165// tag_name を生成166function get_tag_name($num = 6){167 $katakana = array('ア', 'イ', 'ウ', 'エ', 'オ', 'カ', 'キ', 'ク', 'ケ', 'コ', 'サ', 'シ', 'ス', 'セ', 'ソ', 'タ', 'チ', 'ツ', 'テ', 'ト', 'ナ', 'ニ', 'ヌ', 'ネ', 'ノ', 'ハ', 'ヒ', 'フ', 'ヘ', 'ホ', 'マ', 'ミ', 'ム', 'メ', 'モ', 'ヤ', 'ユ', 'ヨ', 'ラ', 'リ', 'ル', 'レ', 'ロ', 'ワ', 'ヲ', 'ン');168 $str = "";169 for($i = 0; $i < $num; $i++)170 $str .= $katakana[array_rand($katakana)];171 return $str;172}173 174// slug を生成175function get_slug($num = 6){176 return substr(str_shuffle('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'), 0, $num);177}
0 コメント