技術関係

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

おさらい

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

 

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

スポンサーリンク
スポンサーリンク

環境

  • 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: 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: 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: VARCHARとTEXTの共存パターン

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

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

これはOK。

NG: 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

 

 

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

SELECT @@innodb_default_row_format;

今回はDYNAMICだったので3072バイト(768文字)だったというわけ。

まとめ

VARCHARにインデックスを効かせられる最大値

  • VARCHAR(191) or VARCHAR(768)

が最大値になる。(行フォーマット設定依存)

 

効かせられる ≠ 貼れる ということなのでインデックスを貼ってエラーにならなくても上記最大値までしか効かないので注意。

今回の環境だとVARCHAR(768)がインデックスを効かせられる限界。

つまりインデックスを貼れるカラムを最大限用意しようとすると、 1テーブルで85個まで作れる。

VARCHARの最大値

  • VARCHAR(16383)

がutf8mb4での1カラムの最大値でありテーブル単位での最大値

 

カラムをどの型にするのか、インデックスを貼って効果は出るのか、VARCHAR or TEXT(MEDIUMTEXT,LONGTEXT)どっちを使うのか、設計する際に気にしながらやってみよう

参考

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

【MySQL】インデックスを張ることのできる VARCHAR の長さには限界がある? / 猫でもわかるWebプログラミングと副業
https://www.utakata.work/entry/mysql/index-length

varcharとtextの違い(mysql innodb) / lxyuma BLOG
https://lxyuma.hatenablog.com/entry/2015/08/15/131309

なぜutf8mb4を使ったときにインデックスが足りなくなるのか?発生原因と対策について / @TanakanoAnchan
https://qiita.com/TanakanoAnchan/items/a6459b44557f2bdd5726

[DBデザイン#47] VARCHARとTEXTどちらを使う?
https://blog.msyk.net/?p=1548

コメント

タイトルとURLをコピーしました