限界までMySQLを使い尽くす!!

どこまで出来るか?!やれるところまでやってやるぜ!!と、威勢が良いのは若い間だけの話。オトナのオトコは、攻めるときはとことん攻めるが自らの限界もわきまえて賢く振る舞うのがスマートってものである。というわけで、今日はMySQLのいろいろな限界についてまとめてみる。皆さんも是非MySQLの限界を知り、MySQLをもっとスマートに使って頂きたい。


SQL文の最大長

MySQLサーバーが実行出来るSQL文の最大長は、max_allowed_packetシステム変数で表される。max_allowed_packetの最大値は1GBである。max_allowed_packetの値はセッションごとにも設定可能なので、デフォルトではそこそこの値（16MBなど）に設定しておいて、必要に応じて大きな対を使うと良いだろう。

データベースの個数

データベースオブジェクトの個数に制限はない。データベースオブジェクトは、MySQLのデータディレクトリ（datadir）のサブディレクトリとして格納される。（データベースの属性は、各サブディレクトリのdb.optというファイルに格納されている。）従って、データベースの個数は「datadirにいくつディレクトリを作成することができるか？」で決まり、これはOSそのものやファイルシステムから制限を受ける。ちなみにLinuxの場合、ext2/ext3の場合は32768個までであり、ext4ではその制限は撤廃される。Solarisの場合、UFSでは32768個の制限があり、ZFSにはそのような制限はない。

データベースあたりのテーブル数

テーブル数についても、データベースの場合と同じようにOSそのものやファイルシステムから制限を受ける。しかし、ファイル数はサブディレクトリ数と違って制限はほとんどない。ext2の場合でも130兆個以上である。しかし、実質的には一つのディレクトリにあまりに大量のファイルを作成すると、ファイル操作が遅くなってしまうので性能上の限界が自ずと生じる。だいたい1万ファイルぐらいまでが関の山である。MyISAMの場合、.frm、.MYI、.MYDという3つのファイルを作成するので、3000テーブル程度にとどめておくのがいいだろう。パーティショニングを利用する場合、.parというファイルと、さらにパーティションごとに.MYIおよび.MYDが作成されるので、使用するファイル数は格段に増える。（パーティション数x2+2）InnoDBの場合、デフォルトではデータベースディレクトリに作成されるのは.frmファイルだけである。innodb_files_per_tableオプションを使用すると、.ibdファイルも作成される。

MySQLサーバー全体でのテーブル数

これはストレージエンジンごとの制限による。MyISAMやInnoDBでは特に制限はない。例えばMyISAMの場合、データベースを1024個つくり、データベースごとに1024個のテーブルを作れば、トータルで100万個のテーブルを作成することが可能だ。（そんな使い方をする人は居ないだろうが。）他のストレージエンジンには制限が存在するものもある。例えばMySQL Clusterでは、MaxNoOfTablesというパラメーターで設定する。MaxNoOfTablesの最大値は1600。

テーブルごとのカラム数

MySQLの制限値は4096個までである。さらに、ストレージエンジンごとにそれより小さい制限があればそれに従う。InnoDBの場合は1000カラムまでで、MyISAMは特に制限はなく4096個というMySQL自体の制限が適用される。MySQL Clusterではカラムとインデックスを合わせて128個までという制限がある。

システム全体でのカラム数

こちらもストレージエンジンごとの制限が適用されるが、MyISAMやInnoDBには制限はない。

MySQL ClusterではMaxNoOfAttributesパラメーターで設定することができる。MaxNoOfAttributesの最大値は2^32であるが、システム全体で保持することができるオブジェクト（テーブル、カラム、インデックス、データベースすべてを含む）が20320個までという制限があり、そちらが適用されることになる。

テーブルごとのインデックス数

ストレージエンジンには最低16個以上のインデックスをサポートすることが定められているが、上限は定義されていない。従って、ストレージエンジンごとの制限がそのまま適用される。MyISAMは64個、InnoDBは上限なし、MySQL Clusterはカラムとインデックスを合わせて128個までである。

インデックス長

MySQL自体の制限値は3072バイト（1024 x 3）までである。それぞれストレージエンジンごとの制限が適用されるが、InnoDBは内部的には3500バイトまでサポートしているので、インデックス長の制限値は適用されない。MyISAMは1000バイトまでである。MySQL Clusterにはインデックス長の制限はない。4.1以前のバージョンのMySQLでは1024バイトまでという制限があるので注意しよう。

インデックスあたりのカラム数

マルチカラムインデックスを作る場合、インデックス長も気にしなければいけないが、カラム数にも制限が存在する。MyISAMの場合は16まで、InnoDBの場合は制限なし、MySQL Clusterは32までである。

行サイズ

MySQL自体の上限は、64KBまでである。ただし、これにはBLOB（およびTEXT）は含まれない。BLOBは他の領域に格納されるからだ。さらに、ストレージエンジンごとに制限が存在する場合がある。

InnoDBは、テーブルスペースのページサイズの約半分で、ページサイズのデフォルトは16KBなのでデフォルトの行サイズは約8KBである。ページサイズを変更するには、MySQLをソースからコンパイルして、さらにテーブルスペースとログファイルを再作成する必要がある。ページサイズの上限は64KBであるので、その場合の1行の最大サイズは32KBとなる。MySQL Clusterは1行あたり最大8KB。

テーブルあたりの行数

MySQL自体にはテーブルごとの行数の制限はない。ただし、PRIMARYやUNIQUEインデックスが格納できる行数には限界があるので、だいたいはそちらの制限を受けることになる。例えばPRIMARY KEYのデータ型がINTの場合は-2147483648〜2147483647、INT UNSIGNEDの場合は0〜4294967295までという値の範囲をとるので、4294967295行以上を格納することは出来ないわけである。

テーブルサイズ

こちらもMySQL自体には制限はない。

InnoDBの場合は、テーブルスペースの最大サイズである64TBが実質の上限となる。それ以前に、64TBのファイルを作成できる必要があるのだが、ext3などのファイルシステムでは、ファイルサイズの上限が2TBなので、それより大きなテーブルを作成したい場合には、テーブルスペースを複数のファイルで構成する必要がある。

MyISAMの場合も、.MYIまたは.MYDファイルがOSまたはファイルシステムがサポートしているファイルの最大サイズの制限を受ける。Linuxの場合、ext2/ext3では2TB、ext4では16TB、XFSでは8エクサバイトまでである。SolarisではUFSの場合1TB、ZFSの場合16エクサバイトである。ただし、MyISAMはファイルサイズの他に、内部で利用しているポインタサイズによってもテーブルサイズの制限を受ける。デフォルトでは256TBまで（ただし5.0.6以前は4GBまで）のテーブルが作成可能である。ポインタサイズはCREATE TABLE実行時にMAX_ROWSおよびAVG_ROW_LENGTHオプションで指定可能である。（MAX_ROWS x AVG_ROW_LENGTHが希望するテーブルサイズ程度になるようにする。）

MySQL Clusterの場合はDataMemoryやIndexMemoryのサイズ、およびディスク型テーブルの場合はテーブルスペースのサイズが上限となる。

テンポラリテーブルサイズ

テンポラリテーブルは、メモリベースのものとディスクベースのものが存在する。メモリベースのもののサイズが限界に達すると、MyISAMに変換される。変換の処理が結構重いので、変換があまり発生しないようにテンポラリテーブルのサイズやクエリをチューニングする必要がある。

メモリベースのテンポラリテーブルのサイズは、tmp_table_sizeおよびmax_heap_table_sizeオプションで設定することができる。ディスクベースのテンポラリテーブルは、tmpdirの領域を使い尽くすまで作成可能である。

CHARのサイズ

255文字まで。バイトではなく文字で計算するのがミソである。マルチバイト文字を使うと、1文字辺りの最大バイト数x255バイトになる。例えばUTF-8の場合、1文字あたり3バイトまで消費するのでCHAR(255)は765バイトとなるので注意しよう。MySQL 6.0では4バイトUTF-8がサポートされるので、CHAR(255)は1020バイトになる。

VARCHARのサイズ

65535文字までである。ただし、1文字の最大サイズは3バイトなので、65535文字だと軽く1行あたりの最大サイズの上限である64KBを超えてしまう。そのような場合、自動的に（MEDIUM）TEXTなどに変換される。また、スペック上はシングルバイトの文字コードだとVARCHAR(65535)まで作成できることになっているが、実際には65529〜65535バイトの場合はエラーになってしまう。その場合は代わりにTEXTを使おう。

BLOB/TEXTのサイズ

通常のBLOB（TEXT）は64KBまで、TINYBLOB、MEDIUMBLOB、LONGBLOBはそれぞれ256バイト、16MB、4GBまでである。こちらはCHAR/VARCHARとは違って文字数ではなくバイト数なので気をつけよう。

INTの最大値

-2147483648〜2147483647、INT UNSIGNEDの場合は0〜4294967295まで。（4バイトだから。）TINYINTは1バイトなので-127〜128、SMALLINTは2バイトだから-32768〜32767、MEDIUMINTは3バイトだから-8388608〜8388607、BIGINTは8バイトだから-9223372036854775808〜9223372036854775807。UNSIGNEDはそれぞれ、TINYINTが255まで、SMALLINTが65535、MEDIUMINTが16777215、BIGINTが18446744073709551615。

DECIMALの最大値

DECIMALは最大で65桁まで定義することが可能である。小数点以下の桁数は30桁まで。従って、全体で65桁で小数点以下がない場合、つまりDECIMAL(65)は-99999999999999999999999999999999999999999999999999999999999999999〜99999999999999999999999999999999999999999999999999999999999999999まで格納することが出来る。全体で65桁で小数点以下が30の場合、つまりDECIMAL(65,30)は-99999999999999999999999999999999999.999999999999999999999999999999〜99999999999999999999999999999999999.999999999999999999999999999999まで格納することが出来る。

必要に応じて桁数を割り当てよう。

DATETIMEとTIMESTAMPの最大値

DATETIMEは1000-01-01 00:00:00〜9999-12-31 23:59:59、1970-01-01 00:00:01〜2038-01-09 03:14:07（ただし時間帯はUTC）まで格納することが出来る。TIMESTAMPは2038年問題にぶち当たるわけである。

テーブルあたりのパーティション数

1024（サブパーティション含む）

カラム名、テーブル名、データベース名などの識別子

MySQL自体の制限値は64文字までである。寿限無寿限無・・・と長いテーブル名をつけてもOKであるが、一般的には短くてわかり易い名前をつけることが多いので、テーブル名の最大長が問題になることはないだろう。

ただしMySQL Clusterは、カラム・インデックス名は31文字まで、テーブル名とデータベース名は合わせて122文字までという制限がある。

ユーザー数

制限なし。何名でも登録可能。ただし権限テーブルはメモリ内にキャッシュされるので、あまりに多すぎるとメモリを消費しすぎることになるので注意が必要だ。

ユーザー名

16文字まで。

パスワード長

制限なし。パスワードは、内部的にはPASSWORD()関数によってハッシュ値が計算され格納されるだけである。ただし、mysqlコマンドを-pオプションつきで実行した場合などに表示されるパスワードの入力を求めるプロンプトに対しては、80文字までしか入力出来ないという制限があるので注意しよう。

shell> mysql -uroot -p
Enter password:    ←コレは80文字まで

パスワード長が長ければ長いほどセキュアなパスワードであると言えるが、あまりに長すぎると覚えにくいので気をつけよう。

同時接続数

max_connectionsパラメーターで調整可能。max_connectionsオプションの最大値は100000（MySQL 5.1.17以降）である。ただし、MySQLは一つのコネクションに対して一つのスレッドを作成する仕様になっている。そのため、OSが作成出来るスレッド数（プロセスあたり/システム全体）によって制限される。OSが作成することができるスレッド数は、システム起動時に自動的に調整されたり、スタック領域のサイズによって制限される場合が多い。各OSにおけるスレッド数の調整については、MySQL Practice Wikiにまとめてあるので参照して欲しい。

また、コネクションごとにOSがTCP/IPソケットを一つ作成するが、その度にファイルディスクリプタを一つ消費する。従って、一つのプロセスが同時に開くことができるファイル数にも制限を受けることになる。（下記、同時にオープンできるテーブル数参照）

同時実行クエリ数

制限なし。接続している全てのセッションがクエリを実行することができる。ただし、ロックの競合などが発生して待たされる場合が多々あるので、真に同時に（内部的に）実行されるクエリ数はもっと少ない。

また、ストレージエンジンには同時に実行出来るトランザクション数に限界があるので、そちらの制限に引っかかるだろう。InnoDBの場合は1023トランザクションまで、MySQL ClusterはMaxNoOfConcurrentTransactionsパラメーターで調整できる。MaxNoOfConcurrentTransactionsの最大値は2^32弱である。

同時にオープンできるテーブル数

MySQL自体には制限はなく、ストレージエンジンごとに制限が存在する。

MyISAMの場合、テーブルをセッションが開く度にファイルディスクリプタを消費する。.MYIは全てのセッションで共有されるが、.MYDはセッションごとに一つのファイルディスクリプタが必要になる。従って、多くのファイル、多くの接続を同時に使用する場合には、同時にオープンできるファイル数を増やさなければならない。同時にopenできるファイル数は、open_files_limitパラメーターで設定することができ、内部的にはsetrlimit（ulimitコマンド相当のシステムコール）で調整することができるが、カーネルがさらに上限値を持っていることが多い。Linuxの場合は、sys.fs.file-maxカーネルパラメーターで、Solarisの場合はrlim_fd_max、rlim_fd_curカーネルパラメーターで調整する。

InnoDBは、デフォルトではテーブルスペースを共有しているのでこのような制限はない。ただしinnodb_files_per_tableオプションを使用している場合は同様に一つのプロセスが開くことができるファイル数に制限を受けることになる。

JOINできるテーブル数

61

サブクエリのネスト数

31回まで。（最外部クエリを合わせると全体で32階層になる。）

テーブルに設定出来るトリガ数

それぞれBEFORE/AFTERとINSERT/UPDATE/DELETEの組み合わせで6つまで。同じ種類のトリガを重複して設定することは出来ない。

レプリケーションスレーブ数

スレーブ数はserver-idの制限を受ける。server-idは2^32-1=65535が最大値なので、65535個のスレーブを設置することが理論的には可能。ただし、スレーブはマスターへ接続する必要があるので、マスターにおいて同時接続数が先に限界を迎えるだろう。それ以前に、ネットワークポート数やネットワーク帯域数が先に限界を迎えることになるだろう。

バイナリログサイズ

max_binlog_sizeで調整可能。max_binlog_sizeの最大値は1GB。

バイナリログファイル数

2147483647まで。それ以上のバイナリログを作成することはできずにエラーになるが、ここまでバイナリログを作成しなければならない状況は滅多にないだろう。

利用可能CPU数

利用可能なCPUコア数/ソケット数などに制限はない。また、商用版のライセンス形態もCPU数ではなくサーバー単位である。ただし、CPUが利用可能なことといくつまでスケールするかは別の話。スケーラビリティを重視している人はMySQL 5.4を使おう。

同時に起動できるMySQLサーバーインスタンス数

データディレクトリやTCP/IPポート番号、UNIXソケット、PIDファイルなどの資源が被らなければいくつでも起動可能。ただし、OSが同時に起動出来るプロセス数や、同時に開くことができるファイル数、TCP/IPポート数などの制限を受ける。

利用可能メモリ量

MySQLはシングルプロセス/マルチスレッドのプログラムなので、32ビット版のOSの場合は2GB〜3GBの制限がある。いくらマシンに搭載しているメモリがそれより多くても関係ない。LinuxやSolarisでは3GBまで、Windowsの場合はデフォルトでは2GBまで利用可能である。ただし、Windowsの場合も/3GBスイッチを設定することで、一つのプロセスが3GBまで利用することが可能である。64ビット版のOSおよびMySQLを使えば、そのような制限はない。システムが搭載しているメモリを全て余すことなく使うことが出来るだろう。（ただしメモリを割り当てすぎてスワップが発生するのは禁物だ!!）