MySQL でコレーションの違うフィールドで照合しようとして Range checked for each record が出た話
2024-07-24 20:30 (4ヶ月前)
ytyng
MySQL で、インデックスがあるフィールドに対してサブクエリをしているのにインデックスが効かなくて検索に膨大な時間がかかる問題が発生した。
FORCE INDEX で強制的にインデックスを使おうとしても適用されない。
EXPLAIN を見てみると、 possible_keys にインデックスが該当しているのに、 key は NULL になっていて、使われないことがわかる。
rows にはレコード全数が出ており、EXTRA には
Range checked for each record (index map: 0x10)という見慣れないメッセージが出ていた。
改めてテーブル定義を確認したところ、 フィールドのコレーション が違っていた。
コレーションというか文字コードがそもそも違っていた。
utf8mb3 (COLLATE utf8mb3_general_ci ) と utf8mb4 ( COLLATE utf8mb4_bin ) を比較していた。
この要件の場合、そもそもインデックスを使って検索することはできない。
なので、片方のフィールドを ALTER TABLE CHNAGE COLUMN して合わせるしかない。はず。
今回は、ID系のフィールドなので utf8mb3 に合わせました。
ALTER TABLE new_table
CHANGE COLUMN some_id some_id varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
CHANGE COLUMN next_some_id next_some_id varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL;ちなみに utf8mb3 に合わせるのは将来性が無いため本来は良くない。デプリケーションウォーニングも出る。けど今回はしょうがなかった。
[HY000][1287] 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead
[HY000][3778] 'utf8mb3_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.あと、本来であればコレーションは utf8mb3_general_ci より utf8mb3_bin のほうが効率的なんだろうと思うが、他のフィールドが utf8mb3_general_ci なので合わせた。
コメント