InnoDBテーブルでunique keyがある場合、それをprimary keyにするべきかどうか

InnoDBなテーブルのお話です。

とあるテーブルで、いくつかあるカラムのうち、ただひとつのカラムがunique keyになり得るものだとします。

こういうときは、そのunique keyは無条件でprimary keyにしたほうがイイ!と思ってたんですが、調べてみると必ずしもそうでないようなので、今のところのモヤモヤをまとめてみます。

教えて! 偉い人!!

予備知識

High Performance MySQL

High Performance MySQL

  • 作者: Baron Schwartz,Peter Zaitsev,Vadim Tkachenko,Jeremy D. Zawodny,Arjen Lentz
  • 出版社/メーカー: Oreilly & Associates Inc
  • 発売日: 2008/06/01
  • メディア: ペーパーバック
  • 購入: 4人 クリック: 33回
  • この商品を含むブログ (8件) を見る
からの超抜粋。しかしこの本は神本ですね。

  • clustered indexとprimary keyの関係
    • InnoDBの場合、primary keyはclustered indexとなります。
    • primary keyが明示されていない場合、uniqueかつnot nullな制約がついているカラムがclustered indexとなります。
    • uniqueかつnot null制約なカラムがない場合は、MySQLが隠しprimary keyを作ってくれます。
  • clustered indexの特徴
    • B-Tree
    • 末端のリーフノードのページには、(データが格納されているページへのポインタではなく)データそのもの(インデックスカラム以外のカラムの値)が格納されています。
  • clustered indexのメリット
    • インデックスをひけばそこにはもうデータが!
  • clustered indexのデメリット
    • レコードの挿入やprimary keyの更新が重い。clustered indexの順に次々にinsertしていくのならば末尾に追加していけばいいが、そうではなくランダム性のある値(例:MD5とかSHA1ハッシュ値)をinsertする場合、ページのフラッシュや移動が発生するため。
    • secondary key (primaryではないただのインデックス) のリーフにはclustered indexの値が格納されるので、clustered indexが大きいと secondary key が占有する領域も付随して大きくなる。
    • secondary key を引く場合、データに辿り着くには 2 回インデックスを引かなければならない。(1) secondary keyを使って、値(= primary keyの値)を得て、(2)primary keyの値とclustered indexを使って、欲しい値を得る。

ぼくの考え

あんまし自信ないので、教えてください!偉い人!!

  • unique keyをprimary keyにした方がいい場合
    • 当該カラムの値が小さい
    • 当該カラムの値が大きめ(でかめの型とか複合キーとか)だけど、ほかのキー(secondary key) がない
    • 今後 insert していく当該カラム値がソートされている
  • unique keyをprimary keyにしない方がいい場合
    • 当該カラムの値が大きくて、secondary keyもいくつかある
    • 今後 insert していく当該カラム値がばらばら(ハッシュ値とか)である。

他、よくわからない点

unique not nullがない場合に MySQL が勝手に作る隠し primary keyは、行番号みたいな単調増加していくような値なのだろうか?

件の書籍では、隠し primary key を作るのではなく auto_increment なカラムを使う例が紹介されているが、auto_increment は発番時にテーブルロックがかかるので並列性を損なう恐れがありあまり使いたくない。

なので、もし隠し primary key が単調増加値 (= insertの処理の負荷が小さくて済む) ならば、auto_increment ではなく隠し primary key を使った方が効率的な気がするがどうなのだろうか?