データベースカラムの文字数が191文字である理由

2021/05/20 09:29

Evan Tahler
オープンソースのデータフレームワーク「Grouparoo」のCTO兼共同創設者
この記事は、著者の許可を得て配信しています。
Why do database columns have a character length of 191?

データベースのスキーマを見ていると、次のようなテキストフィールドが定義されていることがあります。

email_address varchar(191) NOT NULL

これは、カラムが最大191文字の文字列に対応していて、NULLの使用ができないということを意味しています。191という数字は奇数ですが、一体なぜ191文字なのでしょうか?この記事では、ほとんどのリレーショナル・データベースで191文字の制限がデフォルトになっている歴史的な理由を解説します。

なぜtextではなくvarcharなのか?

まず、最初に疑問に思うが、「なぜデータベースに保存する文字列の長さを制限するのか」ということです。最近のモダンな一般的なリレーショナル・データベースは、text やblobタイプのカラムで(ほぼ)無制限でサイズの文字列に対応していますが、なぜそれを使わないのでしょうか?その理由は、インデックスにあります。

email_addressのようなカラムで検索する場合、次のような場合にスピードアップするために、インデックスを追加したいと思う人が多くいると思います。

select id from users where email = 'foo@example.com';

テーブルが大きくなると、データベースはすべての行をチェックして一致するものを探さなければならないため、検索に時間がかかります。しかし、検索インデックスを追加すると、人気のある検索パターンをツリーで「事前に計算」するようデータベースに指示することになり、次回の検索がより速くなります。要するに、インデックスは計算時間(と少しのディスクスペース)を費やしてデータベースへの書き込みを遅くし、後で読み込みを速くするのです。ほとんどのアプリケーションにとっては、これを利用すると「読み込みが多く」「書き込みが少ない」ため、最高のトレードオフとなります。

では、なぜvarcharを使うのでしょうか?インデックスは、保存するデータの種類を想定できると、パフォーマンスが向上します。インデックス内の文字列の長さを知ることで、高速化につながるのです。データベースによっては、この最適化ができないため、text型の列に検索インデックスを追加することが許されていないものもあれば、インデックスの性能が上がらないものもあります。実際、歴史的に見ても、データベースは、検索を最適化するためのインデックスの大きさや、ディスクへのデータ保存方法に制限を設けて構築されてきました。

その原因はMySQLにある

なるほど、インデックスは優れものですね。しかし、一般的には、どのようなサイズのインデックスでも機能すると思われています。またそれはそれで正しいのですが、それがいつもそうだとは限りません。次に、昔のデフォルトのカラムサイズについて見てみましょう。確か、255文字でした。

email_address varchar(255) NOT NULL

MySQLは2000年代初頭に最も普及したオープンソースデータベースです。MySQLはインデックスフィールドの文字数を255文字に制限していました。なぜMySQLが制限文字数を255文字にしたのか、その歴史は曖昧ですが(以下のリンク先の記事を参照)、最も有力な説は以下の通りです。

  • 256 という数は、8 ビットの整数で表現できる最大の数です。MySQLは速度とメモリ使用量を非常に重視しており、可能な限り軽量のデータ型で保存すべきだと考えられていた。
  • MySQLはそれ自体、さらに古いデータベース(Sybase/SAP)との互換性を保とうとしており、それらには255文字の制限があった。
  • MySQL は、インデックスファイルが古いファイルシステムの 1 ページブロック内に収まるようにすべきだと考えられていた。

256 文字の制限を念頭に置き、MySQL の開発者は、255 文字の制限に対してデータベースの多くの部分をさらに最適化することに安心感を覚えていました(これについては後で詳しく説明します)。この時期には、多くの人気オープンソースアプリケーションフレームワーク(Wordpress、Django、Rails など)が登場したため、postgres のように複数のデータベースタイプで実行できる場合でも、すべて MySQL のデフォルトに従いました。これにより、ほとんどのORM(オブジェクト関係マッピング - Wikipedia)では、使用しているデータベースに関わらず、varchar(255)を使用するという共通のデフォルトが形成されました。

🐟が原因

255という数字は191よりもずっと合理的です。では、なぜ191になったのでしょうか?私は絵文字のせいだと思っています😜。これはマジな話です。少なくともutf8mb4は、「国際的な1」文字を許容する文字セットで、それには最初の絵文字も含まれていました。2000年代前半のMySQLは、255文字をヴァーチャルカラムでサポートし、インデックスを作成することでよしとしていました。しかし、最も普及しているMySQLデータベースエンジン(innodb)の最も普及しているテキストエンコーディング(Latin1またはutf8)は、すべての文字2を格納するのに3バイトで十分だと想定していましたが、utf8mb4に𠼭3や🐟などの文字が追加されると、それぞれの文字を格納するのに4バイトが必要になりました。選択する文字数が増えたので、それを参照するにはより多くのバイトが必要だったのです。

innodb MySQLデータベースの仕組みでは、インデックスには767バイトしか使用できず、これは3バイト文字を255個(767÷3=255)保存するのに十分なサイズです。これは、インデックスを作成するデータのサイズを知った上で、インデックスを最適化した極端な例なのです!つまり、文字を格納するのに必要なスペースが増えれば、インデックスに登録できる文字数は少なくならざるを得ないのです。具体的には、767÷4=191文字です!より多くのソフトウェアが国際的な利用者に対応できるようになったため、varchar(255)に代わってvarchar(191)がデフォルトになりました。海外ユーザーをサポートする必要のないソフトウェアアプリケーションについても、2010年代初頭にスマートフォンの普及に伴い、ユーザーが絵文字に対応してほしいと期待するようになると、アップグレードが必要になりました。

今日では

最近のデータベースでは、「すべての」文字に対応可能なutf8mb4などの文字エンコーディングがデフォルトとなっており、固定長のインデックスは過去のものとなっています。しかし、多くのアプリケーションでは、互換性を確保するために、191文字がデフォルトとして残っています。それにもかかわらず、インデックスは比較する文字列のサイズが把握できているときに最もよく機能し、速度上の理由から列の長さに何らかの制限を設けるべきだと考えています。こういった経緯やこれまでの慣習からこの191文字というルールがまだ存在しているのです。

感謝

この記事をまとめる際に参考にした記事を以下にまとめています。心より感謝いたします。

これらの記事については、Hacker Newsでも素晴らしい議論が展開されていますので、ぜひご覧ください。

1. 世界の多くの人々が使用している言語、特に非ラテン文字を使用している言語を「インターナショナル(国際的な)」と言うのは少々奇妙です。しかし、オペレーティングシステムやデータベースの起源は徹底的に英語中心であり、その初期の頃の流れが今日のコードにも影響を及ぼしています。

2. この記事では、「文字(正確には表意文字:漢字のように、文字そのものが意味を持つもの)」という言葉を使っていますが、これは、読者の使っている言語によって、その文字が漢字のようなものであったり、英語のようなアルファベットであったり、絵文字のようなピクトグラムであったりするからです。

3.「ブーブー」(車のクラクションの音) - https://words.hk/zidin/%F0%A0%BC%AD

appstore
googleplay
会員登録

会員登録して、もっと便利に利用しよう

  • 1.

    記事をストックできる
    気になる記事をピックして、いつでも読み返すことができます。
  • 2.

    新着ニュースをカスタマイズできます
    好きなニュースフィードをフォローすると、新着ニュースが受け取れます。