プロが教えるわが家の防犯対策術!

先日問い合わせたSQLの高速化についてEXPLAINについて教えて頂きました。

そのEXPLAINについて調べながら試していくと、同じphpファイル内で動かしている
いくつかのSQL文に対してEXPLAINのtypeがALLとなっていたので対応すべき
SQLだとは分かったのですが具体的にどのようなSQL文に書き換えればいいか
分からなかったので再度質問しました。

前提としてWORDPRESSでSQLをPHPファイル内で$wpdb->get_results関数を使って
実行しています。

(例1) 条件一致した件数を求めるSQL

SELECT a_mst.a_id
FROM a_mst
WHERE a_mst.a_price >= 100
AND a_mst.a_price <= 200
AND a_mst.a_print_flg = 0

SQLのSELECT にCOUNT等は使わない方がいいようなので主キーのみを出力させて
count関数で件数を求めています。

このSQL文をEXPLAINでphpMyadminで実行すると

id=1       select_type=SIMPLE   table=a_mst
type=ALL    possible_keys=NULL   key=NULL
key_len=NULL  ref=NUL          rows=a_mstに登録してある件数
Extra=Using where

でした。SQL文自体はあるマスタにあるデータから値が100~200のもので、フラグが0のものを
出力するというシンプルなSQL文なのでどう改善すればよいのでしょうか?

(例2) 2つのテーブルを結合させて条件一致したデータをそれぞれテーブルから出力

SELECT a_mst.a_name, a_mst.a_price, b_mst.b_data
FROM a_mst, b_mst
WHERE a_mst.a_id = b_mst.b_id
AND a_mst.a_price >= 100
AND a_mst.a_price <= 200
AND a_mst.a_print_flg = 0
ORDER BY a_mst.a_price , a_mst.a_id asc LIMIT 20 OFFSET 0

です。a_mstとb_mstをidで結合させて条件一致したあとにそれぞれの
テーブルデータをSELECTしています。実際のSQL文はSELECT
させている項目数は多いです。

このSQL文をEXPLAINでphpMyadminで実行すると

<1行目>
id=1       select_type=SIMPLE    table=b_mst
type=ALL    possible_keys=PRIMARY key=NULL
key_len=NULL  ref=NUL          rows=a_mstに登録してある件数
Extra=Using temporary; Using filesort

<2行目>
id=1        select_type=SIMPLE    table=a_mst
type=eq_ref    possible_keys=PRIMARY  key=PRIMARY
key_len=NULL   ref=b_mst.b_id       rows=1
Extra=Using where

でした。
EXPLAINを使ったチューニングなどしたことないSQL低級者なのでどのようなSQL文にすればtypeがALLの場合や、ExtraからUsing temporary; Using filesortを消すことができるのか分からないので教えて下さる方がいましたらよろしくお願いします。

ちなみに、a_mstとb_mstはphpMyadminにてそれぞれのテーブルの「構造」を見ると
「インデックスサイズ」のところで、それぞれの主キーであるa_idとb_idがPLIMARY で登録させています。編集ボタンを押したら「インデックスを修正する」画面に変わるので一応それぞれインデックス登録はされているのかな?と思います。

A 回答 (2件)

運用方法を厳密にどうしたいかによりますね



基本的にはプライマリキーはインデックスに含めないのが好ましいので
a_idをプライマリとせずに、別途auto_incrementのプライマリキーを設定してみては?
つまりa_idを含むインデックスをつくるべきだということです。
(a_idが含まれない場合はrangeですが、含まれればtype=indexを得られます)

create table a_mst(pid int not null primary key auto_increment,a_id int not null unique key,a_name varchar(30),a_price int,a_print_flg tinyint);
alter table a_mst add index (a_id ,a_price,a_print_flg);
insert into a_mst(a_id,a_name,a_price,a_print_flg) values(1,'a1',200,0),(2,'a2',150,0),(3,'a3',100,0),(4,'a4',250,0),(5,'a5',50,0),(6,'a6',150,1),(7,'a7',50,1);

//データ抽出
EXPLAIN SELECT a_id,a_price,a_print_flg,b_data
FROM a_mst
INNER JOIN b_mst ON a_id=b_id
WHERE a_price >= 100 AND a_price <= 200 AND a_print_flg = 0;

また、a_nameが集計のキーにならないなら、a_idとの間に別途インデックスを作成して
自分自身を結合するか、別テーブルに正規化してしまうことです。

alter table a_mst add index (a_id ,a_name);
EXPLAIN SELECT a1.a_id,a2.a_name,a1.a_price,a1.a_print_flg,b_data
FROM a_mst as a1
INNER JOIN a_mst as a2 ON a1.a_id=a2.a_id
INNER JOIN b_mst ON a1.a_id=b_id
WHERE a1.a_price >= 100 AND a1.a_price <= 200 AND a1.a_print_flg = 0;

※どうしてもテーブルをわけたくないなら、出力項目全てに対してインデックスを
貼る必要がでてくるかもしれません。しかしあまり大量のインデックスを貼ると
オーバーヘッドが大きく、スピードアップできないかもしれません
    • good
    • 0
この回答へのお礼

回答ありがとうございます。書いていただいたことを参考にしながらちょっと考えてみます。

お礼日時:2013/06/19 17:24

>(例1) 



これは単純に「a_id,a_price,a_print_flg」の3つでインデックス貼っておくことです

create table a_mst(a_id int not null unique key,a_price int,a_print_flg tinyint);
alter table a_mst add index (a_id,a_price,a_print_flg);
insert into a_mst values(1,200,0),(2,150,0),(3,100,0),(4,250,0),(5,50,0),(6,150,1),(7,50,1);

//検証
EXPLAIN SELECT a_id FROM a_mst WHERE a_price >= 100 AND a_price <= 200 AND a_print_flg = 0;

>(例2)

これは命題が中途半端ですね
「a_name」というカラムがありますがこれは「a_id」ではないのでしょうか?
もし別の項目ならa_nameに対してもインデックスを貼る必要があるかもしれません
もちろんb_mstはb_idとb_dataを利用したインデックスが必要です

この回答への補足

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

例1についてですが、「a_price,a_print_flg」の2つでインデックスを追加してみました。「a_id」は主キーなのでphpMyadminですでにPRIMARYでインデックス登録されていましたので。

そうして試すと、ALLはrangeになりました。

例2についてですが、記載不足でした。確かにSELECT文の最初にa_idは必要なので

「a_mst.a_id, a_mst.a_name, a_mst.a_price, b_mst.b_data」の4つです。

例2の回答について再度質問なんですが、私はてっきり条件分岐で使われている項目に対してインデックスを貼る必要があるのではないかと思ったのですが、拝見する限り使われていなくてSELECTにて使われている項目もインデックスを貼る必要があると書いてますが、やはり必要なんでしょうか?

例2のSELECTは実際には14個ほど設定していますがこれらすべてをインデックス登録した方がいいということでしょうか?

また、インデックスの貼り方について例1の場合、私は
a_mstに対しては

[キー名][種別][ユニーク][圧縮][カラム][一意な値の数][照合順序]

PRIMARY:BTREE:はい:いいえ:a_id:1000:A

a_print_flg:BTREE:いいえ:いいえ:a_print_flg:2 :A
                  a_price  :100:A

と登録しています。
一行目はテーブル追加したときから存在してます。
この行は削除しない方が良いと思いますが、2行目の分は

a_id:BTREE:いいえ:いいえ:a_id    :100:A
                a_print_flg:100 :A
               a_price  :100:A

という3つで登録した方が良いのでしょうか?

補足日時:2013/06/17 13:46
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!