質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.42%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PDO

PDO(PHP Data Objects)はPHPのデータベース抽象化レイヤーです。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

Q&A

解決済

2回答

1130閲覧

UNIQUE な VARCHAR カラムを PK にしてもいいのか?

nikuatsu

総合スコア177

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PDO

PDO(PHP Data Objects)はPHPのデータベース抽象化レイヤーです。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

0グッド

0クリップ

投稿2023/04/10 07:42

実現したいこと

以下の状況において、UNIQUEVARCHARカラムをPKにしてもいいのか?
また、どういう不安や懸念が想定されるのか?
などを知りたいです。

前提

コメントテーブル、タグテーブル、リレーションテーブルというありがちな設計に取り組んでいます。

そしてコメントテーブルとタグテーブルはslugカラムを持っており、次のようにURLと紐づいています。
コメントのURL例:https://example.com/comment/nbF4pA
タグのURL例  :https://example.com/tag/Bmr63Z

slugカラムは、UNIQUEVARCHAR(6)です。)

疑問

ではこのようなとき、コメントテーブルとタグテーブルのPKAUTO_INCREMENTINTカラムである必要性は?と疑問に思いました。

つまり、PKslugカラムとし、AUTO_INCREMENTは不要なのではないか、という疑問です。

テスト結果

そこで下記テストコードで10万行のINSERTSELECTを試してみましたら、このような結果となりました。
どうやら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}

質問

改めまして質問ですが、以上のような状況で、UNIQUEVARCHARカラムをPKにしてもいいのでしょうか?

初心者なので、「このテスト方法に不備はないか?」とか、「このテスト結果では良さそうだけど、もし想定していない実装や対処によって、やっぱりダメだった。などということになったりしないか?」などなど、大変不安に感じています。

実際Twitterにはユニークなユーザー名がありますが、それとは別途UUIDでのカラムを設けているようですし…

また次のリンクのように10倍程度遅くなったとのご意見もございまして…
https://teratail.com/questions/195459

豊富なご経験の皆様から多様な視点でのアドバイスを頂戴できればと思い質問投稿させて頂きました。
ご意見、ご回答何卒よろしくお願い申し上げます。

補足情報(FW/ツールのバージョンなど)

MySQL 5.7.27
PHP 8.1

尚、想定しているレコード数は、コメントテーブルもタグテーブルも500万レコード程度とさせて頂きます。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

主キーはテーブル内で一意にデータを抽出するためだけのものですので、往々にして意味がない数値にしておいたほうが管理が楽だということ。逆に主キーの文字配列に意味合いを持たせてしまうと、それが制限になってコードがつけられない場合も出てきてしまいます。また数値だけにしておくとOと0(オーとゼロ)やlと1(エルと1)を読み違えたりするケースがなくなりますので無駄なチェックが不要になります。

とはいえ外部キー制約で参照用につける主キーなど連番ではなくきちんとコード化しておいたほうが管理しやすい場合もあるので一概にこうすべきと決めつけるのは危険です。

投稿2023/04/10 10:47

yambejp

総合スコア115495

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

UNIQUEである項目をPKとする場合に、先ず気にするべきは属性では無く、その項目が更新されるか否かです。
修正があるなら、PKではなくUNIQUE制約のままとしておいた方が良いかと思います。

また、属性によるレスポンスの優劣は当然発生します。
容量との兼ね合いを考えた場合、資源を圧迫するほど容量が大きく無ければ、性能優先で良いかと思います。

投稿2023/04/10 07:52

編集2023/04/10 14:15
sazi

総合スコア25256

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.42%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問