2011年2月26日

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

(2011/02/24 12:48), shin-1@xxxxx wrote:
> みなさんこんにちは、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)
>
> このあたりを見ろなど、アドバイスをどうぞよろしくお願いいたします。
>
>
SQLにヒントを与えて

explain select count(*)」
from tableB USING index(b_code1_2)
left join
tableC USING index(c_code1_2)
on(c_code1=b_code1 and c_code2=b_code2) where c_code1 is null;

にして実行してみてください。

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




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