LoginSignup
31
16

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@cohey0727(Kohei Okamoto)inhokan, Inc.

MySQLのVARCHAR型のLENGTHにおけるパフォーマンス比較

Last updated at Posted at 2021-03-14

はじめに

データベースの速度改善をしているときに、「MySQLのVARCHAR型のLENGTHは255以下では、どの値でもパフォーマンスは一緒」というのをどこかで聞いたことがあるをふと思い出しました。
実際にStackOverflowこの記事ではそれに近いやり取りがなされています。
Importance of varchar length in MySQL table

I have a MySQL table where rows are inserted dynamically. Because I can not be certain of the length of strings and do not want them cut off, I make them varchar(200) which is generally much bigger than I need. Is there a big performance hit in giving a varchar field much more length than necessary?

質問の内容は、varcharの長さを念の為に200にしているが、これはパフォーマンスにどう影響があるのか？というもの。

No, in the sense that if the values you're storing in that column are always (say) less than 50 characters, declaring the column as varchar(50) or varchar(200) has the same performance.

ベストアンサーの回答には50以下の長さしか扱わないならvarchar(50)とvarchar(200)もパフォーマンスは等しいというもの。

またもっともいいねの多い回答ではtemporaryテーブルやmemoryテーブルではパフォーマンスに影響がでると言っています。

There's one possible performance impact: in MySQL, temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. If you design VARCHAR columns much larger than the greatest size you need, you will consume more memory than you have to. This affects cache efficiency, sorting speed, etc.

意見が割れていますが、論理的に説明するのが難しそうです。
"百聞は一見にしかず"ということで、実際に検証してみました。

検証内容

環境

Docker ImageのMySQL8.0を利用します。

$ mysql --version
mysql  Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)

検証項目

LENGTHが15, 31, 63, 127, 255, 511, 1023のVARCHAR型のカラムを1つだけ持つテーブルを各々用意し、以下の処理における時間を計測します。実際に作成するデータの文字数は6〜12文字です。

  • INSERT系
    • データ作成

      各テーブルに1,000,000件のデータをINSERTしたのときの時間を計測する
  • UPDATE系
    • データ更新

      各テーブルに1,000,000件のデータをUPDATEしたのときの時間を計測する
  • SELECT系
    • 全件取得

      各テーブルに1,000,000件のデータに対してSELECTしたのときの時間を計測する
    • 完全一致

      各テーブルに1,000,000件のデータに対して完全一致文字列でSELECTしたのときの時間を計測する
    • 部分一致

      各テーブルに1,000,000件のデータに対してLIKEを使った部分一致でSELECTしたのときの時間を計測する
    • ソート

      各テーブルに1,000,000件のデータに対してSELECT、ORDER BYでソートしたのときの時間を計測する
  • DELETE系
    • データ削除

      各テーブルに1,000,000件のデータをDELETEしたのときの時間を計測する

検証

テーブル作成

以下のSQLで長さがLENGTHが15, 31, 63, 127, 255, 511, 1023のVARCHAR型のカラムも持つテーブルを作成します。

CREATE TABLE length_31 (name varchar(31));
CREATE TABLE length_63 (name varchar(63));
CREATE TABLE length_127 (name varchar(127));
CREATE TABLE length_255 (name varchar(255));
CREATE TABLE length_511 (name varchar(511));
CREATE TABLE length_1023 (name varchar(1023));

INSERT系検証

準備

今回はネットワークの速度が結果に影響しないようにProcedureを作成して、そのなかでループさせます。
以下で件数とTABLE名を指定してINSERTするProcedureを作成しています。

CREATE PROCEDURE insert_loop(in i int, in table_name varchar(32))
BEGIN
    DECLARE cnt int default 0;
    START TRANSACTION;
    WHILE cnt < i
        DO
            SET cnt = cnt + 1;
            SET @name_value = CONCAT('data_', cnt);
            SET @sql = CONCAT('INSERT INTO ', table_name, ' VALUES (\'', @name_value,'\')');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
        END WHILE;
    COMMIT;
END;

結果

TABLE NAME 実行時間
length_15 35 s 847 ms
length_31 36 s 120 ms
length_63 35 s 92 ms
length_127 34 s 361 ms
length_255 33 s 781 ms
length_511 34 s 327 ms
length_1023 34 s 664 ms

考察

いくぶんか差はありますが、誤差の範囲と言えるでしょうか。

UPDATE系検証

先程のINSERT系検証で作成した100万件のデータを更新していきます。
以下のSQLでデータを更新して時間を計測します。

UPDATE length_15 SET name = CONCAT(name, '#');
UPDATE length_31 SET name = CONCAT(name, '#');
UPDATE length_63 SET name = CONCAT(name, '#');
UPDATE length_127 SET name = CONCAT(name, '#');
UPDATE length_255 SET name = CONCAT(name, '#');
UPDATE length_511 SET name = CONCAT(name, '#');
UPDATE length_1023 SET name = CONCAT(name, '#');

結果

TABLE NAME 実行時間
length_15 10 s 979 ms
length_31 11 s 516 ms
length_63 10 s 773 ms
length_127 10 s 779 ms
length_255 10 s 849 ms
length_511 10 s 908 ms
length_1023 11 s 59 ms

考察

これもほとんど差がありませんでした。

SELECT系検証

  • 全行取得
SELECT * FROM length_15;
SELECT * FROM length_31;
SELECT * FROM length_63;
SELECT * FROM length_127;
SELECT * FROM length_255;
SELECT * FROM length_511;
SELECT * FROM length_1023;
  • 完全一致
CREATE PROCEDURE select_loop(in i int, in query varchar(128))
BEGIN
    DECLARE cnt int default 0;
    WHILE cnt < i
        DO
            SET cnt = cnt + 1;
            SET @query = REPLACE(query, '{cnt}', cnt);
            PREPARE stmt FROM @query;
            EXECUTE stmt;
        END WHILE;
END;


call select_loop(10, 'SELECT * FROM length_15 WHERE name = \'data_{cnt}\'');
call select_loop(10, 'SELECT * FROM length_31 WHERE name = \'data_{cnt}\'');
call select_loop(10, 'SELECT * FROM length_63 WHERE name = \'data_{cnt}\'');
call select_loop(10, 'SELECT * FROM length_127 WHERE name = \'data_{cnt}\'');
call select_loop(10, 'SELECT * FROM length_255 WHERE name = \'data_{cnt}\'');
call select_loop(10, 'SELECT * FROM length_511 WHERE name = \'data_{cnt}\'');
call select_loop(10, 'SELECT * FROM length_1023 WHERE name = \'data_{cnt}\'');
  • 部分一致
call select_loop(10, 'SELECT * FROM length_15 WHERE name like \'%data_{cnt}%\'');
call select_loop(10, 'SELECT * FROM length_31 WHERE name like \'%data_{cnt}%\'');
call select_loop(10, 'SELECT * FROM length_63 WHERE name like \'%data_{cnt}%\'');
call select_loop(10, 'SELECT * FROM length_127 WHERE name like \'%data_{cnt}%\'');
call select_loop(10, 'SELECT * FROM length_255 WHERE name like \'%data_{cnt}%\'');
call select_loop(10, 'SELECT * FROM length_511 WHERE name like \'%data_{cnt}%\'');
call select_loop(10, 'SELECT * FROM length_1023 WHERE name like \'%data_{cnt}%\'');
  • ソート
SELECT * FROM length_15 order by name;
SELECT * FROM length_31 order by name;
SELECT * FROM length_63 order by name;
SELECT * FROM length_127 order by name;
SELECT * FROM length_255 order by name;
SELECT * FROM length_511 order by name;
SELECT * FROM length_1023 order by name;

結果

TABLE NAME 全行取得 完全一致 部分一致 ソート
length_15 5 ms 1 s 693 ms 46 ms 1 s 667 ms
length_31 6 ms 1 s 724 ms 52 ms 1 s 723 ms
length_63 7 ms 1 s 694 ms 54 ms 1 s 723 ms
length_127 4 ms 1 s 712 ms 54 ms 1 s 735 ms
length_255 6 ms 1 s 692 ms 44 ms 1 s 714 ms
length_511 8 ms 1 s 710 ms 63 ms 1 s 578 ms
length_1023 7 ms 1 s 703 ms 51 ms 1 s 708 ms

考察

これもほとんど差がありませんでした。

DELETE系検証

  • データ削除
DELETE FROM length_15;
DELETE FROM length_31;
DELETE FROM length_63;
DELETE FROM length_127;
DELETE FROM length_255;
DELETE FROM length_511;
DELETE FROM length_1023;

結果

TABLE NAME 実行時間
length_15 2 s 360 ms
length_31 2 s 332 ms
length_63 2 s 364 ms
length_127 2 s 347 ms
length_255 2 s 281 ms
length_511 2 s 436 ms
length_1023 2 s 471 ms

考察

これも差がないと言って差し支えなさそうです。

結果まとめ

全検証をまとめると以下のようになりました。

INSERET系 UPDATE系 SELECT系 DELETE系
TABLE NAME データ作成 データ更新 全件取得 完全一致 部分一致 ソート データ削除
length_15 35 s 847 ms 10 s 979 ms 5 ms 1 s 693 ms 46 ms 1 s 667 ms 2 s 360 ms
length_31 36 s 120 ms 11 s 516 ms 6 ms 1 s 724 ms 52 ms 1 s 723 ms 2 s 332 ms
length_63 35 s 92 ms 10 s 773 ms 7 ms 1 s 694 ms 54 ms 1 s 723 ms 2 s 364 ms
length_127 34 s 361 ms 10 s 779 ms 4 ms 1 s 712 ms 54 ms 1 s 735 ms 2 s 347 ms
length_255 33 s 781 ms 10 s 849 ms 6 ms 1 s 692 ms 44 ms 1 s 714 ms 2 s 281 ms
length_511 34 s 327 ms 10 s 908 ms 8 ms 1 s 710 ms 63 ms 1 s 578 ms 2 s 436 ms
length_1023 34 s 664 ms 11 s 59 ms 7 ms 1 s 703 ms 51 ms 1 s 708 ms 2 s 471 ms

どの検証でもLENGTHによる差はないように見えます。

さいごに

データをただCRUDするだけであれば、LENGTHの長さによる速度の差はほとんどありませんでした。INDEXを指定した場合や、UNIQUE制約を付けた場合は結果が変わるかもしれません。またバリデーションという意味でも無駄にLENGTHを大きくする意味はないですが、ギリギリをせめて文字数がオーバーしてマイグレーションが必要になるということが起きるのであれば、少し大きめに設定しておくといいかもしれません。
いつかダンプサイズやメモリ使用量の差、INDEX、UNIQUEを指定したときの差なども調査したいです。

31
16
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
31
16

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?