こんにちは。

"藤田" <t-fujita@xxxxxxxxxx>さんの
<002901c41196$ae581030$7c37fea9@fujita>
"[mysql 09061] Re: JOIN で結合すると遅くなります"


> 菅原さん、いそさん、松枝さん、ご返信ありがとうございます。
> 
> 現在インデックスを作成しなおそうとしたらえらく時間がかかって操作できない状態
> です・・・
> 終了したらご指摘いただいた点を試してみます。
> 
> 松枝さん＞　 インデックスは何処に張ってありますか。
> インデックスはdenpyouテーブルのDenpyouNo,T_Code,DenpyouDate、
> denpyoumeisaiテーブルのDenpyouNo,KikakuBangouに張ってあります。
> 
> よろしくお願いします。

　ふーむ。

　こちらではそれほど遅くは無いですけど。と言っても、藤田さんとボクとで、
テーブルの構造も違うでしょうし、データの内容も違うでしょうから、確定では
無いのですが。


　テーブル構造はこんな感じです。
    create table denpyou (
      DenpyouNo int not null primary key,
      T_Code int not null,
      DenpyouDate char(8),
      SomeData varchar(64),
      index (T_Code),
      index (DenpyouDate)
    );

    create table denpyoumeisai (
      DenpyouNo int,
      KikakuBangou char(16),
      UriageQty int,
      SomeData varchar(64),
      index (DenpyouNo),
      index (KikakuBangou)
    );

　denpyouのDenpyouNoには０からの連番、DenpyouNoにはrand()*1000を入れて、
160万レコードを作りました。denpyoumeisaiのKikakuBangouにはDenpyouNoを０
パッドした10桁文字列にダッシュ、連番を結合した文字列を、UriageQtyには
rand()*9+1を入れて、1600万レコードを作りました。

    create table serial (num int);

    insert into serial (num)
    values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

    insert into denpyou (DenpyouNo,T_Code)
    select s0.num+s1.num*10+s2.num*100+s3.num*1000+s4.num*10000
          +s5.num*100000+s6.num*1000000
          ,rand()*1000
    from serial as s0,serial as s1,serial as s2,serial as s3
        ,serial as s4,serial as s5,serial as s6
    limit 1600000;

    insert into denpyoumeisai (DenpyouNo,KikakuBangou,UriageQty)
    select DenpyouNo
          ,concat(right(concat('0000000000',DenpyouNo),10),'-',num)
          ,rand()*9+1
    from denpyou1,serial;

　このテーブル構造、データでの実行結果は以下の通り。ほとんど瞬時に結果が
返って来ます。

    mysql> select count(*) as cnt from denpyou inner join denpyoumeisai
        -> on denpyou.DenpyouNo = denpyoumeisai.DenpyouNo
        -> where T_Code = '407' and KikakuBangou = '0000100000-1';
    +-----+
    | cnt |
    +-----+
    |   1 |
    +-----+
    1 row in set (0.01 sec)

　explainの結果はこんな感じです。２分割の編集をしてあります。

+---------------+--------+------------------------+--------------+
| table         | type   | possible_keys          | key          |
+---------------+--------+------------------------+--------------+
| denpyoumeisai | ref    | DenpyouNo,KikakuBangou | KikakuBangou |
| denpyou       | eq_ref | PRIMARY,T_Code         | PRIMARY      |
+---------------+--------+------------------------+--------------+

+---------------+---------+-------------------------+------+------------+
| table         | key_len | ref                     | rows | Extra      |
+---------------+---------+-------------------------+------+------------+
| denpyoumeisai |      17 | const                   |    1 | where used |
| denpyou       |       4 | denpyoumeisai.DenpyouNo |    1 | where used |
+---------------+---------+-------------------------+------+------------+

−−

　ボクが作ったデータは主要なカラム以外にデータが入っていませんし、また規
則性のあるデータです。上で述べた様に、データ依存で時間が掛かっている可能
性も捨て切れません。テーブル構造やインデックスの張り方を再考してもパフォー
マンスが上がらないのであれば、denpyoumeisaiにT_Codeを持つ正規崩しをやっ
てinner joinを廃した方が良いかも知れません。

　denpyoumeisaiの一意条件が示されていないので、上のテーブル定義の様に重
複可のインデックスを付けてあります。ただこれだと、denpyoumeisaiを全部舐
める事になり、処理時間の増大があります。主キーあるいはユニークなインデッ
クスを張るための条件を考えた方が良いでしょう。


　　　　松枝知直　　　　<tomom@xxxxxxxxxx>
　　　　　　　　　　　　http://www.argus.ne.jp/~tomom/