テーブルやカラムのCollationは揃えよう

元ネタはMySQL Casualのslack

この辺以降

それを見て、

• そういや自分も2年前(DB移行時)にこれ調べたなー
• 社内メンバーに改めてちゃんと周知しよう
• なんか記事書くか

という軽い備忘録的な感じ。

ちなみに、揃えないとどうなるか？

slackにもあったように、

インデックスが効かない！(抽出はできる)

試しにテストテーブルを作る(個人の趣味が多分に含まれているのは気にしないこと)

使った環境はMySQL8.0.13

CREATE TABLE `test1` (
   `test1_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'testID1',
   `test1_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '名前',
   `test1_country` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '出身国',
   PRIMARY KEY (`test1_id`),
   KEY `i_test1_1` (`test1_name`),
   KEY `i_test1_2` (`test1_country`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='test1';


CREATE TABLE `test2` (
   `test2_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'testID2',
   `test2_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '名前',
   `test2_age` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '年齢',
   PRIMARY KEY (`test2_id`),
   KEY `i_test2_1` (`test2_name`),
   KEY `i_test2_2` (`test2_age`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='test2';
;

CREATE TABLE `test3` (
   `test3_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'testID3',
   `test3_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '名前',
   `test3_age` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '年齢',
   PRIMARY KEY (`test3_id`),
   KEY `i_test3_1` (`test3_name`),
   KEY `i_test3_2` (`test3_age`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='test2';
;

insert into test1 (test1_name,test1_country) values ('ジョルノ・ジョバァーナ','Repubblica Italiana');
insert into test1 (test1_name,test1_country) values ('ブローノ・ブチャラティ','Repubblica Italiana');
insert into test1 (test1_name,test1_country) values ('レオーネ・アバッキオ','Repubblica Italiana');
insert into test1 (test1_name,test1_country) values ('グイード・ミスタ','Repubblica Italiana');
insert into test1 (test1_name,test1_country) values ('ナランチャ・ギルガ','Repubblica Italiana');
insert into test1 (test1_name,test1_country) values ('パンナコッタ・フーゴ','Repubblica Italiana');
insert into test1 (test1_name,test1_country) values ('トリッシュ・ウナ','Repubblica Italiana');

insert into test2 (test2_name,test2_age) values ('ジョルノ・ジョバァーナ','15');
insert into test2 (test2_name,test2_age) values ('ブローノ・ブチャラティ','20');
insert into test2 (test2_name,test2_age) values ('レオーネ・アバッキオ','21');
insert into test2 (test2_name,test2_age) values ('グイード・ミスタ','18');
insert into test2 (test2_name,test2_age) values ('ナランチャ・ギルガ','17');
insert into test2 (test2_name,test2_age) values ('パンナコッタ・フーゴ','16');
insert into test2 (test2_name,test2_age) values ('トリッシュ・ウナ','15');

insert into test3 (test3_name,test3_age) values ('ジョルノ・ジョバァーナ','15');
insert into test3 (test3_name,test3_age) values ('ブローノ・ブチャラティ','20');
insert into test3 (test3_name,test3_age) values ('レオーネ・アバッキオ','21');
insert into test3 (test3_name,test3_age) values ('グイード・ミスタ','18');
insert into test3 (test3_name,test3_age) values ('ナランチャ・ギルガ','17');
insert into test3 (test3_name,test3_age) values ('パンナコッタ・フーゴ','16');
insert into test3 (test3_name,test3_age) values ('トリッシュ・ウナ','15');

上のSQLの解説

• test1テーブルには名前と出身国が、test2とtest3テーブルには名前と年齢が入る。

• そして、test2とtest3テーブルに入れるデータは全く同じものを使う。

この後行うのは

test1とtest2,test3テーブルとは名前でJoinするが、この時Collationが異なるとどうなるか？

という簡単な検証である。

そしてEXPLAINしてみると・・・。

Collationがそろってる場合

select test1_name,test1_country,test2_age from test1
inner join test2
on test1_name = test2_name
where test1_id = 1
;

Collationがそろってない(utf8mb4とutf8)場合

select test1_name,test1_country,test3_age from test1
inner join test3
on test1_name = test3_name
where test1_id = 1
;

ちなみに、どっちを実行しようが、以下のような同じ結果が返る。

結果は変わらない。だが、実行計画は大きく違う。

これはデータが少ないうちは気づきにくく、データ量が増えてから「あーーー！！！」となるパターン。

まとめ

• SQLが作られたら必ず実行計画を確認しよう
• インデックスが張られているのに、Full Table Scanが発生しているのであればカラムやテーブルのCollationを疑おう
• この事例については別インスタンスであればまだしも、同一インスタンス内では結合したい場合もあり得るので注意しよう
• ましてや、同一スキーマ内でやるのはご法度ですよ！？