MySQL の (big)int 型とストレージの微妙な関係 - にぽたん研究所

December 02, 2004

このエントリーをはてなブックマークに追加
以前、MySQL について、こんな事を書いた事がありました。
MySQL の auto_increment が duplicate key になる恐れ
int(10) unsigned で、auto_increment だと、1 〜 4,294,967,295 までしかインクリメントしないから、仮にもし「1 日 100 万レコード」ずつ INSERT されたら 11 年と 9 ヶ月ちょっと経つと duplicate key が発生してしまう!!
だからと言って、int 型だと恐いよー!ってヒヨって bigint(20) unsignedauto_increment なんかしたとしても、1 〜 18,446,744,073,709,551,615 までしかインクリメントしないから、仮にもし「1 日 100 億レコード」ずつ INSERT された日にゃ 5,050,546 年 11 ヶ月と 3 週間ちょいぐらいで duplicate key が発生してしまう!!!
という現実味のない話をしましたが、このエントリを書くきっかけとなったエントリを書かれた方が、今日、某案件について「それだと int 型じゃダメなんじゃないの?」とか、またもや現実味のない事を言ってきたので、int だ bigint だなんだって、そんなアフォな話をしておりました。
そんな中でふと気になったのが、int 型で auto_increment なカラムだけを全部ファイルにリダイレクトしたとして、どのぐらいの記憶領域が必要なのかという疑問を持ちました。

まず
mysql> CREATE TABLE tablename (
-> id int(10) unsigned NOT NULL auto_increment PRIMARY KEY
-> );
Query OK, 0 rows affected (0.01 sec)
こんなんして、から
mysql> INSERT INTO tablename VALUES (), (), (), (), (), (), (), (), (), ();
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

こんなんして、コマンドラインから
% /usr/local/mysql/bin/mysql -uusername -p -e 'SELECT * FROM tablename' dbname > tablename.dump
とかした場合、こんなファイルが出来ます。
% cat tablename.dump
id
1
2
3
4
5
6
7
8
9
10
%
"id" だけの行からはじまり、1 〜 10 の数字と、各行末に LF 改行文字が 1 バイト。つまり、
  • "id" だけの行 … 英字 2 バイト + 改行 1 バイト = 3 バイト
  • 1 〜 9 の行 … 数字 1 バイト + 改行 1 バイト = 2 バイト x 9 行 = 18 バイト
  • 10 の行 … 数字 2 バイト + 改行 1 バイト = 3 バイト
この合計で 24 バイトのファイルが出来ます。

そんじゃ、これを、1 〜 int 型の限界値 (4,294,967,295) まで auto_increment したテーブルでやったら…

  • "id" だけの行 … 英字 2 バイト + 改行 1 バイト = 3 バイト
  • 1 〜 9 の行 … 数字 1 バイト + 改行 1 バイト = 2 バイト x 9 行 = 18 バイト
  • 10 〜 99 の行 … 数字 2 バイト + 改行 1 バイト = 3 バイト x 90 = 270 バイト
  • 100 〜 999 の行 … 数字 3 バイト + 改行 1 バイト = 4 バイト x 900 = 3,600 バイト
# 計算省略 ...


と、このように (どのように?) このファイルだけで、46,133,529,147 バイト (約 42.965 GB) の記憶領域が必要だという事です。
数字のカラムだけなのに、すごい量ですね。。。

「なーんだ、その程度なら全然余裕よ!」と言えちゃう、なかなか沢山保存出来るストレージをお持ちの方。
「ウチならテラバイト級のストレージがあるから余裕よ♪」とか言っちゃうような動画ファイル満載の方。

そんなあなたに、bigint 型のすさまじさをお伝えしたい気持ちを抑えることが出来ません。



えー、まず準備しますのは、
mysql> CREATE TABLE tablename (
-> id bigint(20) unsigned NOT NULL auto_increment PRIMARY KEY
-> );
Query OK, 0 rows affected (0.01 sec)
そう、そんな貴方にピッタリの bigint(20) unsigned auto_increment です。
「漢 (おとこ) は黙って bigint(20) unsigned auto_increment だろ」とか言う漢が、いずれこの 21 世紀をグイグイ引っぱって行くんだろうなという気がしてやみません。

そんな「漢テーブル」に、1 〜 bigint 型の限界値 (18,446,744,073,709,551,615) まで auto_increment させるという、恐いもの知らずな INSERT 攻撃を施してやってください。
数日後、はたまた数年後、duplicate が発生するまで INSERT 攻撃をした貴方は、先程と同じコマンド
% /usr/local/mysql/bin/mysql -uusername -p -e 'SELECT * FROM tablename' dbname > tablename.dump
を投下します。

このファイルの完成予想は、先程と同じ理論で
  • "id" だけの行 … 英 (ry
# 以下完全省略

で、できました完成品はといいますと…








376,270,514,436,789,472,827 バイト (約 326.363 EB)




三垓七千六百二十七京五百十四兆四千三百六十七億八千九百四十七万二千八百二十七バイト







EB ですよ EB





読みかたすら謎めき満点です。
正体は「キロ、メガ、ギガ、テラ、ペタ、エクサ…」の「エクサ」って奴です。


今個人で手軽に買えるハードディスクって、1.6TB (外付け) ぐらいが最大容量なんでしょうか。
アイ・オー・データ HDZ-UE1.6TS

そう考えると、1.6TB のハードディスクが最低でも 213,885,025 基必要っていう容量です。




二億一千三百八十八万五千二十五基




ですよ奥さん。

この HDZ-UE1.6TS の寸法を見ると、高さが 131mm なので、213,885,025 基積み上げたら、高さは約 28,019 km になります。
六本木ヒルズの 117,702 倍、東京タワーの 84,141 倍、富士山の 7,420 倍の高さ。





積み上げてどうしたいんだと…。


これほどまでに殺傷能力の高い bigint 型のご利用を生暖かく推奨致します。

以上、お得意の現実味のない話ですた。

nipotan at 15:49 | Comments(7) | TrackBack(1) | 技術 
このエントリーをはてなブックマークに追加

Trackback URL for this entry

Trackbacks

1. 時代はBIGINT  [ 浜村拓夫の世界 ]   May 22, 2011 01:46
MySQLのプライマリーキーで、オートインクリメントの数値を使っているテーブルがある。 頻繁に追加・更新するマスターテーブルで、ものすごい大量の追加が発生したら、INT型では数が足りなくなるんじゃないか?という不安が出てきた。 INT型にすべきか、BIGINT型にすべき

Comments

1. Posted by よしき   December 02, 2004 17:54
これをやってくれるネ申が降臨するまで待つか… (゚Д゚)y─┛~~
2. Posted by okakkii   December 02, 2004 19:23
つい、読んでしまった・・。
無駄なことなのについ計算してしまうよね・・。
UNIXタイムが128bitになったら
いつになったら使いきるかなど・・。
3. Posted by okakkii   December 02, 2004 19:30
いやいや、64bitで十分か・・。
4. Posted by nipotan   December 02, 2004 21:09
ちなみに、某氏に int と bigint の合計バイト数をすごい勢いで必死に計算したと思われてたみたいです。
実際、バイト数とか高さとかはちょっと電卓使いましたけど、int とか bigint は全然計算してないです。
下記のようなもので算出してます。
bigint 計算だけに bigint プラグマとやらを使ってみますた。

なんでこんなロジックで算出出来るのか、その某氏に細かく説明したんだけど、なんかイマイチ理解してもらえてない模様…。

てか、こんな計算まったく役に立たないね…。


#!/usr/local/bin/perl

use strict;
use bigint;

my $prefix = '3'; # "id" + LF
#my $arg = 4_294_967_295; # int(10) unsigned
my $arg = 18_446_744_073_709_551_615; # bigint(20) unsigned

print $prefix + increment_length($arg), "\n";

sub increment_length {
my $max = shift;
my $sum = 0;
$sum += $_ * (length($_) + 1) for map { 9 . '0' x $_ } (0 .. length($max) - 2);
my $last = $max - ('9' x (length($max) - 1));
$sum += $last * (length($max) + 1);
return $sum;
}

__END__
5. Posted by kenboo55   December 03, 2004 02:21
面白かったです。
パチパチパチ。
6. Posted by 兔牛牛+林檎   December 03, 2004 10:05
すごい。
7. Posted by peketamin   February 03, 2012 13:21
5
面白かったです。漢テーブル…プププ

Post a comment

Name:
URL:
  Remember info?: Rate: Face    Star