MySQLにおけるvarchar型とchar型の違い

2022-01-23 09:09:45

前置き

VARCHARとCHARは、最も支配的な2つの文字列型です。残念ながら、これらの値がディスクやメモリにどのように格納されるかを正確に説明することは困難です。これは、ストレージエンジンの特定の実装に関係するためです。 以下の説明では、使用するストレージエンジンが InnoDB および/または MyISAM であると仮定しています。 これら以外のストレージエンジンを使用している場合は、使用するストレージエンジンのドキュメントを参照してください。

まず、VARCHAR と CHAR 値が一般的にどのようにディスクに保存されるかを見てみましょう。ストレージエンジンが CHAR または VARCHAR 値を保存する方法は、メモリ上とディスク上では同じではないため、MySQL サーバがストレージエンジンから読み取った値を別の保存形式に変換する必要がある場合があることに注意してください。

VARCHAR型

VARCHAR型は可変長の文字列を格納するために使用され、最も一般的な文字列データ型です。 必要な分だけスペースを使うので、固定長型よりもスペース効率が良い（例えば、文字列が短ければ短いほど、使用するスペースが少なくなる）。ただし、MySQL のテーブルを ROW_FORMAT=FIXED で作成した場合は、すべての行が固定長ストレージを使用することになり、スペースの無駄遣いになる可能性があります。

VARCHARは、文字列の長さを記録するために1〜2バイトの余分なバイトが必要です。 : カラムの最大長が255バイト以下の場合は1バイトのみで表現し、それ以外の場合は2バイトを使用します。latin1 文字セットと仮定すると、VARCHAR(10) カラムは 11 バイトのストレージを必要とします。VARCHAR(1000) カラムは 1002 バイトを必要とし、これは長さ情報を格納するために 2 バイトが必要となるためです。

VARCHAR はストレージスペースを節約するので、パフォーマンスに役立ちます。しかし、行の長さが可変であるため、UPDATE時に行を長くしてしまい、余分な作業が発生する可能性があります。行が占めるスペースが大きくなり、ページ内に保存できるスペースがなくなった場合、このケースではストレージエンジンによって対処が異なります。例えば、MyISAM は行を分割して保存しますし、 InnoDB は行をページ内に収めるためにページを分割する必要があります。他のストレージエンジンの中には、元のデータの場所で決してデータを更新しないものもあります。

VARCHARが適用される場所

VARCHARの適用が適切なのは、次のような場合です。

文字列カラムの最大長が平均長よりはるかに大きい場合
カラムはほとんど更新されないので、フラグメンテーションは問題にならない
UTF-8 のような複雑な文字セットを使用し、各文字が異なるバイト数で格納される。

CHARタイプ

CHAR型は固定長です。MySQLは常に、定義された文字列の長さに基づいて十分なスペースを確保します。 CHAR 値を格納する際、MySQL は末尾のスペースをすべて削除し、CHAR 値は比較を容易にするために必要に応じてスペースでパッドされます。

CHARは、非常に短い文字列や、すべての値が同じ長さに近いものを格納するのに適しています。例えば、CHARは固定長の値であるため、パスワードのMD5値を格納するのに適しています。また、頻繁に変更されるデータに対しては、VARCHARよりもCHARの方が優れています。なぜなら、固定長のCHAR型は断片化する可能性が低いからです。非常に短いカラムの場合、CHAR は VARCHAR よりも記憶容量が効率的です。例えば、CHAR(1)を使ってYとNだけの値を格納する場合、シングルバイトの文字セットでは1バイトしか必要ありませんが、VARCHAR(1)ではレコード長に1バイト追加されるので2バイトが必要になります。

テスト

CHARとVARCHARの動作の違いを例として明示するために、まず、CHAR(10)フィールドを1つだけ持つテーブルを作成し、そこにいくつかの値を挿入してみます。

CREATE TABLE char_test
(
    char_col CHAR(10)
);
 
INSERT INTO char_test 
VALUES
    ('string1').
    (' string2 ').
    ('string3 ');

これらの値を取得すると、string3 の末尾のスペースが切り捨てられることがわかる。

SELECT CONCAT("'", char_col, "'")
FROM char_test;

実装結果

同じ値をVARCHAR(10)フィールドに格納すると、次のような結果になります。

CREATE TABLE varchar_test
(
    varchar_col VARCHAR(10)
);
 
INSERT INTO varchar_test 
VALUES
    ('string1').
    (' string2 ').
    ('string3 ');
 
SELECT CONCAT("'", varchar_col, "'")
FROM varchar_test;

実行結果

VARCHAR(5)とVARCHAR(200)の違いについて

VARCHAR(5) と VARCHAR(200) を使って 'hello' を格納する場合、スペースのオーバーヘッドはどちらも同じであることが分かっています。では、VARCHARの長さを常に大きくしておくことはできるのでしょうか？短いカラムを使うメリットはあるのでしょうか？

大きな利点があることがわかりました。MySQL は通常、内部値を保持するために固定サイズのメモリブロックを割り当てるため、長いカラムはより多くのメモリを消費します。これは、ソートや操作のためにメモリ内のテンポラリテーブルを使用する場合に特に問題となります。ディスクテンポラリテーブルをソートに使用する場合も同様に悪いです。

ですから、本当に必要な領域だけを確保するのが最善の策です。

まとめると

文字列型のフィールドの型を選ぶとき、VARCHARとCHARのどちらを選ぶかを判断するには、次のようになります。

そのフィールドのデータセットの平均長が、最大長からほとんど変わらないかどうか。差が小さい場合はCHAR型を優先し、その逆はVARCHAR型を検討する。
フィールドがMD5以降のハッシュ値、または何らかの固定長の値を格納する場合、CHAR型が好ましい。
フィールドを頻繁に更新する必要がある場合は、固定長であるため断片化が起こりにくいCHAR型を優先します。
性別のようなフィールド値に関する非常に小さな情報を格納するフィールドでは、VARCHAR型は文字列長情報を保持するために余分なバイトを消費するため、CHAR型が好まれる。

一言で言えば、CHAR型を選べるとき、あるいは消費電力があまり影響しないときは、他の部分で多かれ少なかれメリットがあるので、CHAR型を選ぶようにしようということです。スペース消費が大きな要因になる場合は、VARCHARの利用を検討する。

MySQLのvarchar型とchar型の違いについては、この記事が全てです。MySQLのvarchar型とchar型の詳細については、スクリプトハウスの過去記事を検索するか、以下の関連記事を引き続き参照してください。