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

## はじめに
データベースの速度改善をしているときに、「MySQLのVARCHAR型のLENGTHは255以下では、どの値でもパフォーマンスは一緒」というのをどこかで聞いたことがあるをふと思い出しました。
実際にStackOverflowこの記事ではそれに近いやり取りがなされています。
[Importance of varchar length in MySQL table](https://stackoverflow.com/questions/1962310/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を利用します。

```shell
$ 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系
  - データ作成<br>
    各テーブルに1,000,000件のデータをINSERTしたのときの時間を計測する
- UPDATE系
  - データ更新<br>
    各テーブルに1,000,000件のデータをUPDATEしたのときの時間を計測する
- SELECT系
  - 全件取得<br>
    各テーブルに1,000,000件のデータに対してSELECTしたのときの時間を計測する
  - 完全一致<br>
    各テーブルに1,000,000件のデータに対して完全一致文字列でSELECTしたのときの時間を計測する
  - 部分一致<br>
    各テーブルに1,000,000件のデータに対してLIKEを使った部分一致でSELECTしたのときの時間を計測する
  - ソート<br>
     各テーブルに1,000,000件のデータに対してSELECT、ORDER BYでソートしたのときの時間を計測する
- DELETE系
  - データ削除<br>
    各テーブルに1,000,000件のデータをDELETEしたのときの時間を計測する


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

```sql
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を作成しています。

```sql
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系検証](#INSERT系検証)で作成した100万件のデータを更新していきます。
以下のSQLでデータを更新して時間を計測します。

```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系検証
- 全行取得

```sql
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;
```

- 完全一致

```sql
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}\'');
```

- 部分一致

```sql
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}%\'');
```

- ソート

```sql
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系検証

- データ削除

```sql
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 |

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

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

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

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


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