MYSQLのクエリの最適化について

質問

最近データベースというものを知り、色々試しているのですが、意外と時間がかかっています。
どのようにすると、より短時間で値を調べることができますでしょうか?
行いたい操作は、下記tbleから、
「cntのうち最も小さい数字を選び出す。同一値のcnt中から、最も大きなidの、id,name,cntを選び出す。」
「次に、途中perlでid,name,cntをprint。」
「次に、呼び出したcntに+1を行い保存です。」
です。

ネット上で調べ色々試したところ下記の方法で値は調べられたのですが想像以上に時間がかかっています。
是非アドバイス頂けないでしょうか。
宜しくお願い致します。

MySQL ver:4.1

SELECT id,name,cnt FROM tble ORDER BY cnt ASC,id DESC LIMIT 1;
perlでid,name,cntを取得してprint "${id} ${name} ${cnt}";

UPDATE tble SET cnt = cnt + 1 WHERE id=${cnt};

------tble-----
id　name　　　cnt
1　mojiretsu1　0
2　mojiretsu2　5
3　mojiretsu3　4
4　mojiretsu4　1
以下、1000万行続きます。

回答No.5

denbee · ベストアンサー

>せめて、1秒以内に結果が表示されれば助かるのですが・・・

1000万件の中からの抽出であれば、ハード性能が高くないと相当に厳しいですね。

>何と無く想像では、MYSQLを使えば、1000行でも数億行でも基本的に行数には関係なく
>yahooの様に素早く検索できる方法だとだと思っていたので・・・

データベースは魔法ではありません。
大手のサイトは、ハードにもお金をかけていますし、高速化のための様々な工夫を凝らしています。
ちなみに、お使いのマシンのスペックはどれくらいですか？

>MYSQLでできる範囲内で最適化する方法はありますでしょうか？
・メモリの設定
　こちらを参照のこと。
http://allabout.co.jp/internet/database/closeup/CU20040722A/
　key_bufferを、お使いのマシンのメモリの30～40％くらいに設定してみてください。
　（それほどは効果はないとは思いますが）

・SQL文

とりあえず、
SELECT MAX(id) FROM tble WHERE cnt=(SELECT MIN(cnt) FROM tble);
は成功しますか？

＃どうしても高速化したいのであれば、テーブルを複数に分割し、
＃１テーブル当たりのレコード数を減らすことも考えて方がよいでしょう。

お礼

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

＞大手サイトは、ハードにもお金をかけていますし・・・
あれから、色々調べてみると、そうみたいですね。
サーバ100台、専任のPGを雇ったりとなかなか凄いようです。

ただ、簡単な操作については、Excelよりはかなり早く面白いですね。
これから、もう少し研究してみようと思います。

また、マシンスペックについては非公開のため全く分かりません。
なお、SQL文については、動作しませんでした。
詳しくは分かりませんが、色々制限が掛けられているようです。

高速化のアイディアありがとうございます。
検討してみようと思います。

長らくお付き合いいただきありがとうございました。

回答No.4

LuzBlue

No.3です。

cntとidの複合キーによるインデックスを追加すると早くなる可能性があります。
が、後の処理で、cntの更新を行うため、そこのパフォーマンスが悪化することも考えられます。

お礼

回答いただきありがとうございます。
複合キーについて研究してみます。

回答No.3

LuzBlue

No.2の方もいっていますが、副問い合わせで、cntの最小値をとり、その値に合致するidの最大値を取得するといいと思います。
副問い合わせがわからないのであれば、
１．cntの最小値を取得するＳＱＬ
２．そのcntを持つデータのidの最大値を取得するＳＱＬ
３．取得したidから、cntとnameを取得するＳＱＬ
と分けてみるのもいいと思います。

補足

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

キャッシュの関係で速くなっているかどうか分かりませんが、とりあえず、先ほどよりも大幅に改善されました。ありがとうございます！
ただ、それでも、思っていたよりも大分遅くMYSQLではこれが限界なのでしょうか？
せめて、1秒以内に結果が表示されれば助かるのですが・・・
何と無く想像では、MYSQLを使えば、1000行でも数億行でも基本的に行数には関係なくyahooの様に素早く検索できる方法だとだと思っていたので・・・
MYSQLでできる範囲内で最適化する方法はありますでしょうか？

また、副問い合わせはできないらしく、
SELECT MIN(cnt) FROM tble;
perlで$cntを取得
SELECT MAX(id) FROM tble WHERE cnt=$cnt
perlで$idを取得
SELECT id,name,cnt FROM tble WHERE id=$id
と、途中何度かperlで処理をしています。

SELECT id,name,cnt FROM tble WHERE id=(SELECT MAX(id) FROM tble WHERE cnt=(SELECT MIN(cnt) FROM tble));
と書いてみましたが、下記、文法エラーがでてうまくいきませんでした。
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(id) FROM tble WHERE cnt=(SELECT MIN(cnt) FROM tble))

回答No.2

denbee

>以下、1000万行続きます。
何気に書かれていますが、１０００万行もあれば性能に影響が出て当然です。

特に、このSQL文ではORDER BYを使っていて、全データ（１０００万件）を一旦昇順、降順に
並び変えていますからかなり時間がかかるはずです。

あと、ちょっとうろ覚えですが、MySQLでは１つのテーブルにインデックスを２つ貼ってあって
SQL文でその両方が条件項目に入っている場合、どちらか一方のインデックスしか
有効にならないはずです（MySQLの仕様）。

ちょっと文法を忘れたのですが、副問い合わせでmin、max関数を使って目的に合致する行のidを
発見させた方が早くなりそうな気がします。
（この手のSQL文のチューニングはやってみないとわからないので断言はできませんが）

補足

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

キャッシュの関係で速くなっているかどうか分かりませんが、とりあえず、先ほどよりも大幅に改善されました。ありがとうございます！
ただ、それでも、思っていたよりも大分遅くMYSQLではこれが限界なのでしょうか？
せめて、1秒以内に結果が表示されれば助かるのですが・・・
何と無く想像では、MYSQLを使えば、1000行でも数億行でも基本的に行数には関係なくyahooの様に素早く検索できる方法だとだと思っていたので・・・
MYSQLでできる範囲内で最適化する方法はありますでしょうか？

また、副問い合わせはできないらしく、
SELECT MIN(cnt) FROM tble;
perlで$cntを取得
SELECT MAX(id) FROM tble WHERE cnt=$cnt
perlで$idを取得
SELECT id,name,cnt FROM tble WHERE id=$id
と、途中何度かperlで処理をしています。

SELECT id,name,cnt FROM tble WHERE id=(SELECT MAX(id) FROM tble WHERE cnt=(SELECT MIN(cnt) FROM tble));
と書いてみましたが、下記、文法エラーがでてうまくいきませんでした。
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(id) FROM tble WHERE cnt=(SELECT MIN(cnt) FROM tble))

回答No.1

qaz_qwerty_me

SQLの最適化も良いですが・・・インデックスなどのｄｂの最適化を行っていますか？

インデックスなどのｄｂの最適化を行っていないなら、インデックスを新設するだけで高速化しますよ。

補足

tbleを作成する際、下記操作をしていますので、自身はありませんがインデックスはあるのではないかと思います。

CREATE TABLE tble (
id int(10) NOT NULL,
name text NOT NULL,
cnt int(10) NOT NULL,
PRIMARY KEY (id),
KEY k_cnt (cnt)
);

また、cntを後から追加した場合は、cntを追加した後、インデックスの作成も同時に行っているので多分あると思います。
ただ、インデックスサイズの表示が下記で、
キー名　種別 一意な値の数　操作　フィールド
k_cnt　INDEX　なし　編集　削除　cnt
一意な値の数が「なし」になっているのが気にはなります。
ALTER TABLE tble2 ADD (cnt int(10) NOT NULL);
ALTER TABLE tble2 ADD INDEX k_cnt (cnt);