A社では `VARCHAR(1000)` にインデックスを貼れるのに、B社では貼れません

Question

実現したいこと

VARCHAR(1000) にインデックスが貼れない原因を知り、貼りたいです。

前提

レンタルサーバーをA社からB社に移行したところ、該当のソースコードのCREATEがB社ではできず困りました。

発生している問題・エラーメッセージ

phpmyAdminで該当のソースコードのCREATEを実行するとこちらのエラーメッセージが表示される状況です。

MySQL のメッセージ: 
#1071 - 索引のキーが長すぎます。最大 3072 バイトまでです。

該当のソースコード

問題のCREATEがこちらで、今までA社ではできていました。

CREATE TABLE IF NOT EXISTS tbl_names (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL,
	INDEX idx_names(name)
);

試したこと（VARCHAR を減らす）

下記のように VARCHAR を 768 に減らすとできました。（769だと同様のエラーです。）

CREATE TABLE IF NOT EXISTS tbl_names (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(768) CHARACTER SET utf8mb4 NOT NULL,
	INDEX idx_names(name)
);

試したこと（索引キーのサポート状況の確認）

下記を実行すると1が返りました。

SELECT @@innodb_large_prefix;

下記を実行するとONが返りました。

SHOW VARIABLES LIKE 'innodb_large_prefix';

補足情報（FW/ツールのバージョンなど）

〇A社もB社も MySQL 5.7 でした。
〇A社は解約済みなのでクエリを試すことはできません。

どうすればVARCHAR(1000) にインデックスを貼れるようになりますでしょうか？

Answer 1

• 文字セットをutf8mb4としているカラムでは、1文字あたり4バイト使います。つまりVARCHAR(1000)のカラムは4000バイトの長さです。
• 一方、InnoDBストレージエンジンを用いている場合、「インデックスキーの接頭辞」の長さは最大で 3072 バイトに制限されます ("InnoDB Limits" を参照)。

ですので、ご質問のような場合はインデックスの長さが制限を超えてしまい、インデックスを作成できません。

作成する方法としては次のようなものがあります。

1. カラムを短くする。
   問題のカラムをVARCHAR(768)以下にすればいいです。当然768文字を超えるデータは格納できません。

2. インデックス接頭辞を使う
   インデックス作成の際にカラムの先頭から指定したバイト数までだけを使います。ご質問の場合

   CREATE INDEX idx_names ON tbl_names name(768);
   

   などとすればインデックスを作成できます。ただしカラムの768文字 (3072バイト) 以降はインデックスの対象になりません。

3. 文字セットに utf8mb4 を使わない
   カラムの文字セットに utf8 (utf8mb3) を使うと、1文字あたり3バイト使います。つまりutf8の場合、VARCHAR(1024)以下ならインデックスを張れるということになります。ただし、utf8はユニコードの一部の文字しか格納できない (漢字や絵文字などで格納できないものがある) ですし、今後は非推奨となります。これまでutf8を使っていたデータベースを移行する場合以外は使わないほうがいいでしょう。

4. ストレージエンジンにInnoDBを使わない
   MyISAMストレージエンジンなら、最初に説明したようなインデックス長の制限はありません。いくらでも長いカラムにインデックスを作成できます。ただ、MyISAMはトランザクション、外部キー制約、行ロックなどといった現代的な機能をサポートしませんし、クラッシュリカバリの機能もありません。過去に構築したシステムを塩漬け状態で移行したいような場合以外は使わないほうがいいでしょう。

これまでA社ではできていたとのことです。もしもA社で作成したデータベースのダンプが残っていればテーブルの作成方法を確認できるので、これらのどれかに当てはまるかわかるかもしれませんね。

Comments

[zappy]

パターンの列挙を誠にありがとうございます。さらに詳細なご説明もご記載頂いているおかげで、「できるじゃん」と安易にパターン4などに飛びつかずに済みました。
今後同じような事象が起きた際はこちらを参照すればばっちりですね。大変参考になりました。

さて今回はどうやら CHERRY様のご回答にある STRICT_TRANS_TABLES が設定されていなかったせいで、ikeda様のパターン2に該当していたのかなと思われます。

多様なデータベースがある中でさらに細かい状況や設定などに応じて使いこなすのは大変すぎますね…。

[ikedas]

SQLモードで制御されていたのですね。

どっちかというと私のは、「移行したら以前できていたことができなくなった」というパターンです。utf8を使っているものはまだあるかもしれませんが、MyISAMはもうなかなか出会えないと思います。

Answer 2

発生しているのは、InnoDBインデックスの最大キー長について の「utf8mb4の場合」に書かれている内容ですね。

補足として、最大バイト数を超えたときにエラーが発生してインデックス作成に失敗するのは、sql_modeパラメータにSTRICT_TRANS_TABLESが設定されている場合です。

このSTRICT_TRANS_TABLESが設定されていない場合は、エラーではなくワーニングが発生し、3072バイトに切り詰められたデータでインデックスが作成されます。

と記載されているように　A社とB社が提供している MySQL で、「STRICT_TRANS_TABLES」パラメータの設定が異なるのだと思います。

会社名もサービス名もわからない状況では、設定変更できるか判断できませんので、問題が生じている B社 のサポートに問い合わせるしかないと思います。

レンタルサーバの場合は、my.conf で設定するパラメータはユーザー側では設定変更できない可能性が高いと思います。

Comments

[zappy]

なるほど、なるほど。理解できました。仰る通りだと思います。
A社はそういえば STRICT_TRANS_TABLES が設定されていなかった記憶があります。
B社は設定されておりました。

しかしまさか、A社は「VARCHAR(1000) のインデックスを貼れていた」のではなく、「VARCHAR(1000) のインデックスを指定したが、実は 3072バイトに切り詰められて VARCHAR(768) のインデックスが貼られていただけ」だったとは…。STRICT_TRANS_TABLES が未設定というのは恐ろしいですね。
これは、ikedas様のご回答にある「インデックス接頭辞」が使われているのと同じことだったわけですね。

ありがとうございます。大変参考になりました。

Answer 3

そもそもインデックスは前方・後方のいずれかしか有効ではなく（AAA%や%ZZZなど）、長いデータを保持したいというのはたとえばキーワードを複数設定したいということだと思いますので、あまりインデックスが有効につかわれることはないでしょう。つまり大抵の場合長い文字列にインデックスを貼ること自体意味がないということです。
textの全文検索にするか、キーワードを個別で保持するかのどちらかで対応するのがよろしいかと。