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

実現したいこと

以下の状況において、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のケースの方が投稿においては１割ほど遅いようですが、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件

ベストアンサー

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

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

投稿2023/04/10 10:47