実現したいこと
以下の状況において、UNIQUE
なVARCHAR
カラムをPK
にしてもいいのか?
また、どういう不安や懸念が想定されるのか?
などを知りたいです。
前提
コメントテーブル、タグテーブル、リレーションテーブルというありがちな設計に取り組んでいます。
そしてコメントテーブルとタグテーブルはslug
カラムを持っており、次のようにURLと紐づいています。
コメントのURL例:https://example.com/comment/nbF4pA
タグのURL例 :https://example.com/tag/Bmr63Z
(slug
カラムは、UNIQUE
なVARCHAR(6)
です。)
疑問
ではこのようなとき、コメントテーブルとタグテーブルのPK
がAUTO_INCREMENT
なINT
カラムである必要性は?と疑問に思いました。
つまり、PK
はslug
カラムとし、AUTO_INCREMENT
は不要なのではないか、という疑問です。
テスト結果
そこで下記テストコードで10万行のINSERT
とSELECT
を試してみましたら、このような結果となりました。
どうやらB: PKがslugのケース
の方が投稿においては1割ほど遅いようですが、id
カラムがない分データ量は少ないはずなので、悩んでいます。
PHP
1array ( 2 'A: PKがidでslugも持つケース' => 3 array ( 4 'コメント投稿を計測' => '98.272729873657 秒', 5 'コメント取得を計測' => '11.397902011871 秒', 6 ), 7 'B: PKがslugのケース' => 8 array ( 9 'コメント投稿を計測' => '105.66755986214 秒', 10 'コメント取得を計測' => '11.046698093414 秒', 11 ), 12)
テストコード
SQL
1-- -------------------------- 2-- A: PKがidでslugも持つケース 3-- -------------------------- 4CREATE TABLE commentsA ( 5 id INT AUTO_INCREMENT PRIMARY KEY, 6 slug VARCHAR(6) NOT NULL, 7 content VARCHAR(768) NOT NULL, 8 INDEX (content), 9 UNIQUE (slug) 10); 11CREATE TABLE tagsA ( 12 id INT AUTO_INCREMENT PRIMARY KEY, 13 slug VARCHAR(6) NOT NULL, 14 tag_name VARCHAR(255) NOT NULL, 15 UNIQUE (slug) 16); 17CREATE TABLE relationsA ( 18 comment_col INT NOT NULL, 19 tag_col INT NOT NULL, 20 PRIMARY KEY (comment_col, tag_col), 21 FOREIGN KEY (comment_col) REFERENCES commentsA(id) ON DELETE CASCADE, 22 FOREIGN KEY (tag_col) REFERENCES tagsA(id) ON DELETE CASCADE 23); 24 25-- -------------------------- 26-- B: PKがslugのケース 27-- -------------------------- 28CREATE TABLE commentsB ( 29 slug VARCHAR(6) PRIMARY KEY, 30 content VARCHAR(768) NOT NULL, 31 INDEX (content) 32); 33CREATE TABLE tagsB ( 34 slug VARCHAR(6) PRIMARY KEY, 35 tag_name VARCHAR(255) NOT NULL, 36 INDEX (tag_name) 37); 38CREATE TABLE relationsB ( 39 comment_col VARCHAR(6) NOT NULL, 40 tag_col VARCHAR(6) NOT NULL, 41 PRIMARY KEY (comment_col, tag_col), 42 FOREIGN KEY (comment_col) REFERENCES commentsB(slug) ON DELETE CASCADE, 43 FOREIGN KEY (tag_col) REFERENCES tagsB(slug) ON DELETE CASCADE 44);
PHP
1<?php 2// 計測結果 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// コメントテーブルへ INSERT 74function 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// タグテーブルへ INSERT 97function 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// リレーションテーブルへ INSERT 127function 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}
質問
改めまして質問ですが、以上のような状況で、UNIQUE
なVARCHAR
カラムをPK
にしてもいいのでしょうか?
初心者なので、「このテスト方法に不備はないか?」とか、「このテスト結果では良さそうだけど、もし想定していない実装や対処によって、やっぱりダメだった。などということになったりしないか?」などなど、大変不安に感じています。
実際Twitterにはユニークなユーザー名がありますが、それとは別途UUIDでのカラムを設けているようですし…
また次のリンクのように10倍程度遅くなったとのご意見もございまして…
https://teratail.com/questions/195459
豊富なご経験の皆様から多様な視点でのアドバイスを頂戴できればと思い質問投稿させて頂きました。
ご意見、ご回答何卒よろしくお願い申し上げます。
補足情報(FW/ツールのバージョンなど)
MySQL 5.7.27
PHP 8.1
尚、想定しているレコード数は、コメントテーブルもタグテーブルも500万レコード程度とさせて頂きます。

回答2件
あなたの回答
tips
プレビュー
下記のような回答は推奨されていません。
このような回答には修正を依頼しましょう。
また依頼した内容が修正された場合は、修正依頼を取り消すようにしましょう。