2011年2月24日

[mysql 15516] 2項目でJOINすると極端に遅くなる

みなさんこんにちは、nomotoです。
お知恵を貸してください。

CentOS環境のMySQL5.0.77で、片方のテーブルからもう片方のテーブルに
一致するレコードが存在しないレコードだけを抽出したく、
2つのテーブルをJOINして、合致しないレコードをNULL判定して
抜き出そうとしています。

ただ、2項目で比較しないとレコード特定できないデータなため
2項目のANDでJOINしようとしているのですが、1項目のJOINなら良いの
ですが、2項目を比較してJOINしようとするとIndexを使ってくれなく
なり、処理に10分以上かかってしまいます。
1項目だけでJOINすれば数秒で終わるので、同程度の速度を期待して
いるのですが、2項目だとあまりに遅すぎて実用に耐えられません。

複合Indexを張らなきゃダメという情報も読んでIndexは貼ってあるの
ですが、何が原因でIndexが使われなくなるのでしょうか。
何かアドバイスいただきたく、よろしくお願いいたします。

データ構成的にはMyISAMで、tableB(約 84,000件)とtableC(約 5,000件)
があり、それぞれVARCHAR(20)のcode1,code2という項目に文字データが
入っていて、その2項目の文字データを両方つき合わせて、はじめて
レコードの特定ができるデータ内容です。
※code1,code2は各テーブルでb_code1,b_code2という具合に少し名前が
違います。

tableBは20フィールド程度で1レコードあたり数百バイト程度の
文字データのみ。tableCは数個の文字フィールドと、6〜8MB程度の
バイナリデータの入ったmidiumblobの項目1個です。

tableBのcode1,code2はほぼ全件バラバラ、code1もcode2もそれぞれ
重複は数百件程度です。

tableCのcode1はほぼ全件バラバラ、code2は空文字''で登録されている
データが9割以上で、code2に値が入っているレコードはごく少数しか
ありません(今後、code2に値の付いたデータが増える予定)
なので、現状ではcode1,code2で2テーブルをJOINすると一致するのは
数十件で、ほとんどが不一致になります。


原因究明の第一歩としてEXPLAINをとりました。

1項目でのJOINなら両テーブルともUsing Indexが出ていますが、
2項目のANDでJOINするとtableCにはIndexが使われてないようです。

両テーブルのcode1,code2は、複合Indexが必要だろうと
(code1),(code2),(code1,code2)という3パターンのIndexをどちらにも
貼ってあります。

1項目だけのJoinならIndexが使われます。
mysql> explain select count(*) from tableB left join tableC
on(c_code1=b_code1) where c_code1 is null;
+----+-------------+--------+-------+-------------------+-----------+---------+-----------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-------------------+-----------+---------+-----------------+-------+-------------+
| 1 | SIMPLE | tableB | index | NULL | b_code1 | 63 | NULL | 84147 | Using index |
| 1 | SIMPLE | tableC | ref | c_code1,c_code1_2 | c_code1 | 63 | test.tableB.b_code1 | 1 | Using Where; Using index |
+----+-------------+--------+-------+-------------------+-----------+---------+-------------------+-------+-------------+
2 rows in set (0.00 sec)

2項目AND条件でJOINするとtableCでIndexが使われません

mysql> explain select count(*) from tableB left join tableC
on(c_code1=b_code1 and c_code2=b_code2) where c_code1 is null;
+----+-------------+--------+-------+---------------------------+-----------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------------+-----------+---------+---------------------+-------+-------------+
| 1 | SIMPLE | tableB | index | NULL | b_code1_2 | 126 | NULL | 84147 | Using index |
| 1 | SIMPLE | tableC | ref | c_code1,c_code2,c_code1_2 | b_code1 | 63 | test.tableB.c_code1 | 1 | Using Where |
+----+-------------+--------+-------+---------------------------+-----------+---------+---------------------+-------+-------------+
2 rows in set (0.00 sec)

このあたりを見ろなど、アドバイスをどうぞよろしくお願いいたします。


投稿者 xml-rpc : 2011年2月24日 12:48
役に立ちました?:
過去のフィードバック 平均:(0) 総合:(0) 投票回数:(0)
本記事へのTrackback: http://hoop.euqset.org/blog/mt-tb2006.cgi/102223
トラックバック
コメント
コメントする




画像の中に見える文字を入力してください。