mysqlでleft joinでselectしたときにindexがきかない

Question

いつもお世話になります。
外部結合でのMySQLのインデックスについてご質問させてください。

以下のようなテーブル構成で、テーブル:T_COMPANYのCON_NO が存在しないテーブル:T_COMPANY_2のレコードを抽出したいと思っています。
既存のT_COMPANYテーブルが6000件で、今回新規で追加したのがT_COMPANY_2で、こちらが11万件ほどあります。

left joinを使用して取得しようとしたのですが、Explainの結果で、T_COMPANYの方がtypeがALL、keyがNULLになってしまいました。
※T_COMPANY_2では、type がindex 、keyがCON_NO　と想定した結果です。

クエリ、もしくはテーブル構成・インデックスの設定のおかしいところについてご指摘いただけますでしょうか。
MYSQLのバージョンは5.7です。

テーブル構成

CREATE TABLE `T_COMPANY` (
  `COMPANYID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `AT_ID` varchar(6) NOT NULL DEFAULT '',
  `CON_NO` varchar(6) NOT NULL DEFAULT '',
  `COMPANY_NAME` varchar(250) NOT NULL DEFAULT '',
  `PRS_NAME` varchar(250) DEFAULT NULL,
  `COMPANY_ZIP` varchar(12) DEFAULT NULL,
  `COMPANY_SGBCD` varchar(5) DEFAULT NULL,
  `COMPANY_ADDRESS` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`COMPANYID`),
  UNIQUE KEY `AT_ID` (`AT_ID`),
  KEY `idx_t_mpco` (`MPCOID`,`AT_ID`),
  KEY `index_name` (`CON_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=6550 DEFAULT CHARSET=utf8mb4 

CREATE TABLE `T_COMPANY_2` (
  `COMPANY2ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `CON_NO` varchar(6) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `CORP_INDI` varchar(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `COMPANY_NUMBER` varchar(13) COLLATE utf8mb4_bin DEFAULT NULL,
  `COMPANY_NAME` varchar(200) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `PRS_NAME` varchar(200) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `COMPANY_ADDRESS_ALL` varchar(200) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`COMPANY2ID`),
  UNIQUE KEY `CON_NO` (`CON_NO`),
) ENGINE=InnoDB AUTO_INCREMENT=110964 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 

クエリ

select T_COMPANY_2.CON_NO  from T_COMPANY_2 left join T_COMPANY on T_COMPANY.CON_NO = T_COMPANY_2.CON_NO where T_COMPANY.CON_NO is null;

Answer 1

クエリ、もしくはテーブル構成・インデックスの設定の
おかしいところについてご指摘いただけますでしょうか。

T_COMPANY, T_COMPANY_2 両テーブルのDDLを拝見して気になったのは
ひょっとするとCollation設定が両テーブルで異なっているのではないか
という点です。

T_COMPANYのCOLLATEが明示されていないため
サーバーやデータベースに対するDEFAULT COLLATEの設定次第にはなりますが
もし、T_COMPANYの照合順序がCOLLATE=utf8mb4_general_ciだったりすると

• T_COMPANY_2　--->　ケース・センシティブ
• T_COMPANY　　--->　ケース・インセンシティブ

というミスマッチになります。

MySQL5.7で、フィールドの型は同じ、照合順序は異なるというフィールド同士
( utf8mb4_bin と utf8mb4_general_ci )
の結合を試してみたところ、エラーにはなりませんでしたけど
高いパフォーマンスをご希望なのでしたら
精度が高いほうのCOLLATEに統一して値を比較する必要があると思います。

結合方法は以下のどちらかでいかがでしょうか。

explain
select
    T_COMPANY_2.CON_NO  
from
    T_COMPANY_2 
left join 
    T_COMPANY
on 
    T_COMPANY_2.CON_NO = T_COMPANY.CON_NO
collate
    utf8mb4_bin
where
    T_COMPANY.CON_NO is null;

-- 或いは

explain
select
    T_COMPANY_2.CON_NO
from
    T_COMPANY_2 
left join
    T_COMPANY
on
    T_COMPANY_2.CON_NO = binary T_COMPANY.CON_NO
where 
    T_COMPANY.CON_NO is null;

Comments

[A.S9999]

ご返答が遅くなりすみません。
上記の対応で処理時間が0.082secまで早くなりました。
照合順序のことがすっかり頭から抜けておりました。ご指摘、本当に感謝いたします。

Answer 2

CON_NOは2つ共NOT NULL制約が付いているから、NULLのデータはinsertできない。
なのに、

where T_COMPANY.CON_NO is null;
NULLの判断をしているのが不要では？

B*TreeインデックスではNULLの判断をする時はインデックスを使わなかったのでは？

CREATE TABLE `T_COMPANY` (
...
  `CON_NO` varchar(6) NOT NULL DEFAULT '',
...
  UNIQUE KEY `CON_NO` (`CON_NO`),
...

CREATE TABLE `T_COMPANY_2` (
...
  `CON_NO` varchar(6) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
...
  UNIQUE KEY `CON_NO` (`CON_NO`),
...

where T_COMPANY.CON_NO is null;

Comments

[A.S9999]

回答ありがとうございます。

NULL判定のときはINDEXがつかないのですね・・・、勉強不足でした。

なお、このwhere句でのNULL判定は、結合した時に、T_COMPANY側のCO_NOを持たないという意味で、つけています。
not in にもしてみたのですが、これはT_COMPANY側にwhere句を指定していないのでindexでの速度アップは期待できないので、left joinでやっていました。
※not inでのクエリ
select T_COMPANY_2.CON_NO from T_COMPANY_2 where T_COMPANY_2.CON_NO not in(select distinct CON_NO from T_COMPANY);

left joinでもnot inでも処理時間は変わらず、3分ほどでした。
データ表示側のT_COMPANY_2　のデータ件数が10万件あるので、これ以上処理速度をあげる方法はないでしょうか・・・？

[Orlofsky]

なんでこんなSQLが必要なのか？T_COMPANY, T_COMPANY_2 はどう違うデータが入っているテーブルでしょうか？それぞれのデータがが作られるタイミングは？
同じ PRIMARY KEY ならひとつのテーブルとしてまとめるべきデータではないか？って疑問。

[sazi]

@Orlofskyさん
> CON_NOは2つ共NOT NULL制約が付いているから、NULLのデータはinsertできない。
これが理由で、
> where T_COMPANY.CON_NO is null; NULLの判断をしているのが不要では？
にはなりませんよ。

[A.S9999]

回答が遅くなりすみません。
01と０２の違いは、データが連携されてくる元のデータシステムが異なるため、格納先のテーブルを分けているという理由です。
確かにテーブル構成も見直す必要があるかなと感じています。
今回は照合順序を見直すことで対策できましたが、今後のシステム運用を見据えて、いまいちどテーブル構成を見直すことも検討したいと思います。
どうもありがとうございました。