MySQLでTEXT型が避けられる理由とは

[![表紙](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3967563/dcb46cbe-c7b0-4153-89f1-2712f1db6821.png)](https://leapcell.io/?lc_t=q_mysqlnotext)

## 背景

データベースに不確定な長さのシリアライズされたデータを保存する必要がある場合、多くの人はテーブル設計でこのフィールドを `VARCHAR(2000)` 型にします。

長さが不確定であるにもかかわらず、なぜ `TEXT` 型を使わないのでしょうか？
ある人は言います：「`TEXT` はクエリ性能に影響を与えるから。」

本当にその理由でしょうか？この記事ではそれを検証します。

## TEXT とは何か

MySQL における `TEXT` は可変長データ型であり、`TINYTEXT`、`TEXT`、`MEDIUMTEXT`、`LONGTEXT` があります。これらは通常、大量のテキストデータの保存に使用され、それぞれの保存可能な長さは以下の通りです：

- `TINYTEXT`：0 - 255 バイト
- `TEXT`：0 - 65,535 バイト
- `MEDIUMTEXT`：0 - 16,777,215 バイト
- `LONGTEXT`：0 - 4,294,967,295 バイト

## TEXT の保存方法

各 `BLOB` または `TEXT` データは、内部的には個別に割り当てられたオブジェクトによって表されます。一方、他の型ではテーブルを開いたときに各カラムのために一度だけ記憶領域が割り当てられます。

文字列型データの保存に関して、InnoDB は長さが 768 バイト以上の固定長フィールドを可変長としてエンコードし、「オーバーフローページ」に保存します。768 バイト未満のデータはデータ行に直接保存されます。
そのため、他の文字列型を使用する際は、768 バイト以上のデータを保存することは避けるべきです。

## TEXT の使用制限

- `TEXT` にはデフォルト値を設定できません。
- `TEXT` にインデックスを付ける場合、インデックスのプレフィックス長を指定する必要があります。
- インデックスエントリを比較する際、末尾に空白が詰められます。インデックスがユニークである必要がある場合、重複キーエラーが発生する可能性があります。
- `TEXT` フィールドは非常に長くなる可能性があるため、ソート時には `max_sort_length` バイト（デフォルトは 1024）のみが使用されます。この変数を設定することで調整可能です。

```sql
-- max_sort_length を確認
SELECT @@max_sort_length;

-- max_sort_length を設定
SET max_sort_length = 2048;
```

- 一時テーブルで処理される際、サーバーはメモリ上のテーブルではなくディスク上のテーブルを使用します。なぜなら、`MEMORY` ストレージエンジンは `TEXT` 型をサポートしていないからです。

- `TEXT` オブジェクトのサイズはその型によって決まりますが、実際に送受信できる最大値は利用可能な内容と通信バッファサイズによって決まります。このバッファサイズは `max_allowed_packet` 変数を調整することで変更可能です。

```sql
-- max_allowed_packet を確認
SELECT @@max_allowed_packet;

-- max_allowed_packet を設定
SET max_allowed_packet = 67108864;
```

## 結論

`TEXT` は大量のテキストデータを保存するのに適しています。しかし、以下の理由により、`TEXT` の使用は推奨されません：

### パフォーマンスの問題

- `TEXT` は内部的に個別に割り当てられたオブジェクトであり、保存・取得時に追加の操作やリソース消費が発生します。
- `TEXT` フィールドが非常に大きい場合、読み取り時にメモリへの負荷が増大し、システム全体の性能に影響を与えます。
- `MEMORY` ストレージエンジンは `TEXT` 型をサポートしておらず、一時テーブルで処理する際は `TEXT` データはディスクから読み込まれ、メモリから直接読み込まれることはありません。

### インデックス制限

インデックスはクエリ性能を向上させる手段ですが、`TEXT` フィールドに対するインデックスにはいくつかの制限と複雑さがあります：

- ユニークインデックスとして使用する場合、重複キーエラーが発生する可能性があります。
- フルテキストインデックスを作成するには追加の計算やスペースが必要であり、`TEXT` フィールドのデータが大きすぎるとパフォーマンスに影響を与える可能性があります。

そのため、データベースのテーブル設計時には `TEXT` 型の使用をできるだけ避けるべきです。使用する場合は、以下のような対策を検討してください：

- `TEXT` フィールドを可能な限り別テーブルに分離し、主キーでメインテーブルと関連付ける。
- 必要がなければ `TEXT` フィールドを読み込まない（例：`SELECT *` の使用を禁止する）。
- 大きなフィールドは OSS（オブジェクトストレージ）に保存するのが望ましい。

---

### [私たちはLeapcell、バックエンド・プロジェクトのホスティングの最適解です。](https://leapcell.io/?lc_t=q_mysqlnotext)

[![Leapcell](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3967563/5d9a7224-0556-4e73-8048-b0a2db491065.png)](https://leapcell.io/?lc_t=q_mysqlnotext)

[Leapcell](https://leapcell.io/?lc_t=q_mysqlnotext)は、Webホスティング、非同期タスク、Redis向けの次世代サーバーレスプラットフォームです：

**複数言語サポート**

- Node.js、Python、Go、Rustで開発できます。

**無制限のプロジェクトデプロイ**

- 使用量に応じて料金を支払い、リクエストがなければ料金は発生しません。

**比類のないコスト効率**

- 使用量に応じた支払い、アイドル時間は課金されません。
- 例: $25で6.94Mリクエスト、平均応答時間60ms。

**洗練された開発者体験**

- 直感的なUIで簡単に設定できます。
- 完全自動化されたCI/CDパイプラインとGitOps統合。
- 実行可能なインサイトのためのリアルタイムのメトリクスとログ。

**簡単なスケーラビリティと高パフォーマンス**

- 高い同時実行性を容易に処理するためのオートスケーリング。
- ゼロ運用オーバーヘッド — 構築に集中できます。

[ドキュメントで詳細を確認！](https://docs.leapcell.io/)

[![Try Leapcell](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3967563/94be0303-39b3-43a8-b60f-1adce15a7c82.png)](https://leapcell.io/?lc_t=q_mysqlnotext)

Xでフォローする：[@LeapcellHQ](https://x.com/LeapcellHQ)

---

[ブログでこの記事を読む](https://leapcell.io/blog/why-varchar-field-instead-of-text-in-mysql)