Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL 結局、索引の最大長っていくつなの?を考える Specified key was too long; max key length is 3072 bytes

f:id:hit10231023:20180911165611p:plain

MySQL8.0の話ですので、以前のバージョンの話とはちょっと変わってしまうかもしれませんが、テーブルの作成で、ものすごくしょうもないことでハマってしまいました。

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

まず、前提として、索引(インデックス)の最大長は、3072バイトです。ってところから始めます(笑)

これがいけると思っていたところからが地獄の始まり(笑)都合よく、私は、varchar(1024)を1024バイトだと思ってました(汗)

CREATE TABLE `table1` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Url` varchar(1024) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ix01` (`Url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
;
----------------------------------------
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

varchar(1024)はどういう意味???

これ、たぶん、私以外はしっていると思うのですが、1024バイトじゃなくて、1024文字なんですよね!! じゃーバイト数はなんなの?っことなのですが、これは文字コードセットによって変わってきます。

utf8mb4

昨今ですと、絵文字とかも入る、文字コードセットなのですが、varchar(1)の場合のバイト数は、4バイトです。 つまり4倍なわけでして。

`Url` varchar(1024) NOT NULL,

の場合、1024 X 4 = 4096バイトになりますよね。3072バイトの制約を超えているので、エラーになるわけです。

utf8(utf8mb3)

では、utf8の場合はどうでしょうか?

CREATE TABLE `table1` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Url` varchar(1024) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ix01` (`Url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
------------------------------------------
mysql> show warnings ;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                      |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

なんかワーニング出てるけど今度は、うまくいきました。ワーニングは、utf8ってutf8mb3の別名なんだけど、将来的に、utf8mb4の別名になるよん!ってことかな?って思います。

どちらにしても、うまく索引は作られたようです。

つまり、

utf8(utf8mb3)のvarchar(1)の場合のバイト数は、3バイトです。 つまり3倍の計算になると思うのですが

の場合、1024 X 3 = 3072バイトになりますよね。3072制約にぴったりなので、大丈夫!って話です。

。。で、書いていて発見したのですが(いまさらか!!)

utf8mb3,utf8mb4のお尻の、3,4とかでバイト数を換算すればいいのか!!って

でもどーしても索引が貼りたい!!

utf8mb4の話で進めますが、どーしても索引を貼りたいんだ!ということはあると思います。でも、現状、3072バイトの壁を破ることはできないです。では、どうするか?という話になるのですが、分割と妥協が必要になってくるかと思います。 私でしたら、さきほどエラーだった、CREATE文を以下のように変更すると思うのです。。

エラーだったCREATE 文

Urlのカラムが、3072バイトを超えるので、エラーになってやつです。

CREATE TABLE `table1` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Url` varchar(1024) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ix01` (`Url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
;

こーいう風に変えます

CREATE TABLE `table1` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Url` varchar(1024) NOT NULL,
  `Url1` varchar(512) GENERATED ALWAYS AS (left(`Url`,512)) STORED NOT NULL,
  `Url2` varchar(512) GENERATED ALWAYS AS (substr(`Url`,513)) STORED,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ix01` (`Url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

GENERATED ALWAYSについては、下記で解説しました

hit.hateblo.jp

簡単に説明しますと。

Urlのカラムに入ったカラムは自動的にUrl1Url2に分割される(分割) 512文字でユニークだったら、まず問題ねーだろう?(妥協)

です。

根本的に、長い文字を索引にするのってどーなの?というところのツッコミもあるとは思いますが。

f:id:hit10231023:20180309104332j:plain