元ネタは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 ;
ちなみに、どっちを実行しようが、以下のような同じ結果が返る。
結果は変わらない。だが、実行計画は大きく違う。
これはデータが少ないうちは気づきにくく、データ量が増えてから「あーーー！！！」となるパターン。
