[解決済み] すべてのテキストベースのフィールドに汎用的なvarchar(255)を使用することに不利な点はありますか？

質問

私は contacts テーブルがあり、そこには以下のようなフィールドがあります。 postcode , first name , last name , town , country , phone number などがあり、これらはすべて VARCHAR(255) と定義されているにもかかわらず、これらのフィールドの文字数は255文字にも満たないのです。 (不思議に思うかもしれませんが、Ruby on Railsのマイグレーションでは、Stringフィールドを VARCHAR(255) にマップされ、それを上書きする必要がなかったからです)。

VARCHAR はフィールドの実際の文字数だけを (フィールドの長さと共に) 保存するので、例えば、以下を使用する明確な利点 (パフォーマンスまたはその他) はあるでしょうか? VARCHAR(16) よりも VARCHAR(255) ?

さらに、これらのフィールドのほとんどは、それらにインデックスを持ちます。 フィールドの VARCHAR サイズが大きくなると、インデックスのサイズやパフォーマンスにまったく影響を与えないのでしょうか？

参考までに、私は MySQL 5 を使用しています。

どのように解決するのですか？

ストレージに VARCHAR(255) とは異なり、与えられた行に必要な長さだけを保存するのに十分賢いです。 CHAR(255) とすると、常に255文字が格納されます。

しかし、この質問に MySQL のタグを付けたので、MySQL 固有のヒントを挙げます。行がストレージエンジン層から SQL 層にコピーされるとき。 VARCHAR フィールドは CHAR に変換され、固定幅の行で作業する利点が得られます。 そのため、メモリ上の文字列は の長さになるようにパディングされます。 となり、宣言された VARCHAR カラムの最大長になります。

クエリが暗黙のうちにテンポラリテーブルを生成する場合、例えばソート中や GROUP BY のように、クエリが暗黙的にテンポラリ・テーブルを生成する場合、多くのメモリを消費する可能性があります。 もし、多くの VARCHAR(255) フィールドを多く使用すると、テンポラリテーブルが非常に大きくなります。

また、この "padding out" の動作は、utf8 文字セットで宣言された文字列は、1 バイトの内容で保存する文字列 (例: ascii または latin1 文字) であっても 1 文字あたり 3 バイトにパッドアウトすることを意味していることをご存知でしょうか？ また、同様に utf8mb4 文字セットでは、文字列はメモリ内で 1 文字あたり 4 バイトになるようにパッドアウトされます。

ですから VARCHAR(255) のような短い文字列を格納する utf8 では、ディスク上では 11 バイト (10 文字の低文字コードと長さのための 1 バイト) ですが、メモリ上では 765 バイトになり、したがって一時テーブルやソートした結果では、このようなバイト数が必要になります。

私は、無意識のうちに 1.5GB の一時テーブルを頻繁に作成し、ディスク領域をいっぱいにしていた MySQL ユーザーを支援したことがあります。 彼らはたくさんの VARCHAR(255) カラムがたくさんあり、実際には非常に短い文字列を保存していました。

保存しようとするデータの種類に基づいてカラムを定義するのが最善です。 他の人々が言及したように、アプリケーション関連の制約を強制するための利点があります。 しかし、私が上で説明したメモリの浪費を避けるための物理的な利点があります。

もちろん、最も長い郵便物の住所が何であるかを知ることは難しいので、多くの人は長い VARCHAR という、どの住所よりも確実に長いものを選ぶ人が多いのです。 そして255が慣習となっているのは、それが最大長の VARCHAR の最大長で、1バイトでエンコードできるためです。 それはまた、最大 VARCHAR の最大長でもあります。