意外と忘れがちな1テーブルあたりの文字数最大値、インデックスを貼れる最大値

おさらい

utf8mb4環境下でのVARCHAR(X)のXは文字数を表し、バイト数は1文字4バイトまでを扱う。

つまり

VARCHAR(16383)が65532バイト
VARCHAR(16384)が65536バイト

なので、VARCHAR指定だと1カラムに入る文字数は16383文字が指定の限界の数になる。

そういえばVARCHARのサイズって767バイトで制限なかったっけ？と思ったが、MySQLのバージョン次第では無縁となった。

MySQL6以降であれば関係する設定がデフォルトになってたりする。

innodb_file_format はデフォルトで Barracuda (5.7.7 以降)、

innodb_default_row_format はデフォルトで DYNAMIC (5.7.9 以降)

innodb_large_prefix はデフォルトで ON (5.7.7 以降)で、deprecated で、じき項目が消える予定

MySQL(InnoDB) で charset を utf8mb4 にする注意点の現在
https://dev.to/seizans/mysqlinnodb–charset–utf8mb4–1451

単独では上記の上限であることがわかったが組み合わせでどうなるか。見てみる。

環境
NGパターン
1. 1テーブルの最大バイト数が65535を超える
NGパターン2
1. 1テーブルの最大バイト数を65535を1カラムで超える
NGパターン3
1. TEXT or BLOB以外で最大値を超える
OKパターン1
VARCHARとTEXTの共存パターン
NGパターン4
1. VARCHAR(16381)とTEXT
巨大カラムにインデックスは貼れるのか
まとめ
1. VARCHARにインデックスを効かせられる最大値
2. VARCHARの最大値
参考

環境

mariadb: 10.2
Collation: utf8mb4_unicode_ci
Sequel Ace

NGパターン

1テーブルの最大バイト数が65535を超える

create table user(
    user_message VARCHAR(5000) not null,
    user_message1 VARCHAR(5000) not null,
    user_message2 VARCHAR(5000) not null,
    user_message3 VARCHAR(5000) not null
);

4個作ろうとした時点でエラーが出た。

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

65535バイトを超えるならTEXTかBLOB型を指定しろ、とのこと。

1テーブルでも65535バイトの壁は超えられないようだ。

NGパターン2

1テーブルの最大バイト数を65535を1カラムで超える

create table user(
    user_message VARCHAR(16384) not null
);

Column length too big for column 'user_message' (max = 16383); use BLOB or TEXT instead

確認の意味でやってみた。今度はエラーメッセージが文字数の16383になった。

カラム単体でも当然超えることができない

NGパターン3

TEXT or BLOB以外で最大値を超える

create table user(
    user_message1 VARCHAR(16383) not null,
    user_message2 INT(1) not null
);

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

VARCHARとINTを組み合わせてみた。

しかしこういうことも出来ない。どちらにせよTEXT or BLOB 以外では1テーブル65535バイトの壁がある。

OKパターン1

VARCHARとTEXTの共存パターン

create table user(
　　　　user_message VARCHAR(16380) not null,
　　　　user_message2 TEXT not null
);

VARCHAR(16380)とTEXTの組み合わせパターン。

これはOK。

NGパターン4

VARCHAR(16381)とTEXT

create table user(
　　　　user_message VARCHAR(16381) not null,
　　　　user_message2 TEXT not null
);

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

VARCHAR(16381)とTEXTを組み合わせてみた。しかしこれはNG。

16380ではいけたのに16381ではエラー。

VARCHAR(16380)は65520バイト
VARCHAR(16381)は65524バイト

最大値が65535なので11バイト空きだとNGで15バイト空きだとOKらしい。

TEXTカラムを持つために15バイト必要なのだろうか？謎。詳しい人説明求む。

create table user(
　　　　user_message VARCHAR(16380) not null,
　　　　user_message2 TEXT not null,
　　　　user_message3 TEXT not null
);

ちなみにこれもエラー。

巨大カラムにインデックスは貼れるのか

カラムの組み合わせはここまで。

今度はインデックスを貼ることを試してみる。

OKパターン: VARCHAR(500)カラム

create index user_messageINDEX on `user`(`user_message1`);

VARCHAR(500)カラムにインデックスを貼った場合、普通に貼れる。これは当然問題なし。

NGパターン: VARCHAR(16383)カラム

一方VARCHAR(16383)カラムに貼った場合、インデックスのSub_partが768になっている。

貼れてるのか？貼れてないのか？

インデックスのSub_partは文字数を表す

もしこのフィールドがインデックスに一部分だけ使用している場合、そのインデックスに使用しているキャラクター数をしめす。もしキー全体がインデックスされているなら NULL 。

MySQL のインデックスを試してみる / @umi
https://qiita.com/umi/items/d3917951a127b3696a43

どうやら全体がインデックスされてないみたい。768と書かれていて768文字制限がある模様。

つまり

巨大カラムにインデックスを貼ることは出来るが、意図通りに効かせることは不可能

ということな模様。

最大でどれくらいまでいけるか、というのはこれまた行フォーマット設定次第。最大でも3072バイトになる(VARCHAR(768))。

DYNAMIC または COMPRESSED の行形式を使用する InnoDB テーブルでは、インデックスキーの接頭辞の長さの制限は 3072 バイトです。

REDUNDANT または COMPACT の行形式を使用する InnoDB テーブルのインデックスキー接頭辞の長さ制限は 767 バイトです。

15.22 InnoDB の制限 / mysql
https://dev.mysql.com/doc/refman/8.0/ja/innodb-limits.html

行フォーマットを確認するクエリは以下。