MySQL 256バイト以上あるカラムに索引を貼る

mariadb のテーブルで、256バイト以上あるカラムに対して索引を生成する場合の手順です。MySQL5.5以上もこれでいけるとは思うのですが、試していないので、ここでは割愛します。ここでは、MariaDBを再起動しないで実現する方法を記載します。また、いつか再起動した時に設定がリセットされてしまうのも泣けると思いますので、my.cnf (mariaですと、server.cnf）の記述例も載せておきます。

f:id:hit10231023:20180309104332j:plain

設定変更前の状況

なんの変哲もない普通のテーブルですが、こちらのval カラムに索引を貼ってみます。ちなみにまだ、設定は変更しておりません

CREATE TABLE `test_table` (
  `id` int(11) DEFAULT NULL,
  `val` varchar(700) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

索引を貼ってみます

とまあ、インデクスは貼れたことは貼れたのですが、なにやらワーニングが出ていますよね。

CREATE INDEX ix01_test_table ON test_table (val) ;
Query OK, 0 rows affected, 1 warning (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 1

show warnings ;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+

うまくいっているのかとおもいきや再度テーブル定義を見ると、２５６でぶっちぎられております（汗あれまー・・。

CREATE TABLE `test_table` (
  `id` int(11) DEFAULT NULL,
  `val` varchar(700) DEFAULT NULL,
  KEY `ix01_test_table` (`val`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

256バイト以上あるカラムに索引を貼る！！

そこで、256 BYTE以上のカラムに索引をどーしても貼りたい場合、innodb_large_prefixというグローバル変数を利用します。でも、それだけではだめで、他にもいろいろと直さないと実現できないので、そちらも含めて以降、手順を説明していきます。

長い索引を晴れるように設定

SET GLOBAL innodb_large_prefix=1 ;

デフォルトのフォーマットエンジンを変更する

ちょっと補足しますと、innodb のデフォルトフォーマットって、Antelope(レイヨウって動物？）というフォーマットを利用しており、こちらでは、圧縮機能がサポートされておりません。なので、まずはここのフォーマットを、Barracuda（カマス？）に変更する必要があります

SET GLOBAL innodb_file_format=Barracuda ;
SET GLOBAL innodb_file_format_max=Barracuda ;

対象テーブルの、ROW_FORMATを設定（変更）する

256バイトを超えるインデクスを扱えるようにするためには、ROW_FORMATをDynamicやCompressedに変更必要がありますここでは、DynamicやCompressedの説明は割愛します。

ALTER TABLE test_table ROW_FORMAT=Compressed ;

再度索引を作ってみる

先ほど生成したインデクスがあるので、まずはそれをDROPしてからCREATEします

DROP INDEX ix01_test_table ON test_table ; CREATE INDEX ix01_test_table ON test_table (val) ;                 
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

結果確認

結果が先ほどと変わってますよね。ix01_test_table (val(255))とかでていないです。完璧！！！

CREATE TABLE `test_table` (
  `id` int(11) DEFAULT NULL,
  `val` varchar(700) DEFAULT NULL,
  KEY `ix01_test_table` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED ;

補足（再起動したら設定が元に戻らないように・・・）

server.cnf (my.cnf)の記述例です

# デフォルトだと、256Byte以上の索引は生成できないが
# InnodbのROW_FORMATをDynamicやCompressedに変更したテーブルでのみ3072バイトまで扱えるようになる
innodb_large_prefix=1

# innodb_large_prefix=1の場合、ROW_FORMATがDynamicもしくは、Compress になるので、デフォルトをBarracudaにしておく
innodb_file_format=Barracuda
innodb_file_format_max=Barracuda

Database JUNKY

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