404 motivation not found | t_ishidaのブログ

10月/08

5

更新してなかった間にしてたこと

概略

DBで全文検索を作ったりMySQLが遅くなったりしたのをチューニングする方法考えたり、色々してた。

MySQLが遅い場合

概要

以下の理由が考えられる。

  • インデックスがちゃんと効いてない
    • 予想外の場所でインデックスが使われて、効いて欲しい場所で効いてない。実はorder by でもインデックス使いたいんだよね。order byでインデックス使いたくて、where のインデックスが効かないなんて事もある。
    • そのクエリがデータ取れすぎ。確か、テーブルの30%以上のデータの取得が予測されるとフルスキャンしちゃうと思う。
  • 大量に突っ込む時に遅い
  • 大量に突っ込んだは良いけどインデックスの再構築時間かかりすぎ
  • 検索は早かったけど、order byに時間かかりすぎ
インデックスの話

MySQLは1クエリに1インデックスしか使えません。なので予想外に遅くなったりします。5以降ならインデックス結合と言うので複数インデックスが働いているように見える場合もありますが、まあ、基本的に一個しか使えないと思ってて下さい。っていうか、インデックス結合はマニュアル読んでもさっぱり意味が分からなかったし、恩恵にまだ預かった事無い。が、多分こういうこと

  • インデックスが使えるフィールドをandで繋いだ時(インデックス結合共通集合アルゴリズム)
  • インデックスが使えるフィールドをorで繋いだ時(インデックス結合ユニオンアクセスアルゴリズム)
  • インデックスが使えるフィールドをorで繋いだけど、↑が使えなくてフルソートしてから使うとき(インデックス結合ソートユニオンアクセスアルゴリズム)

後者二つについて言うと、内部で、

(select * from tbl where fld_with_index1 = 1)
union
(select * from tbl where fld_with_index2 = 2)

っていうインデックスが効いてるサブクエリをunionで繋いで無理やり繋いでるんだと思われ。

とは言えMySQLっていう代物は、恐らく、

  • ガーっと大量データ突っ込めば良いよ。
  • 大量のデータからインデックスが効くフィールド一個で、ちょっとだけに絞ってデータ取得すれば良いよ
  • 複雑なSQLは書いちゃ駄目よ

と言うアホだけどバカ力なDBだと認識しているので、こういう風に使ってもらいたがってると思う。

-- 一発目で絞れるだけ絞ってね
create temporaly table tmp1 select * from tbl where fld_with_index = 1;
/** 
  絞りきれてないならインデックス貼っちゃえば良いよ
  alter table tmp1 add index ( fld_with_index2 );
 **/

-- さらに絞ってね。ここではインデックス使わなくて良いようにする。
create temporaly table tmp2 select * from tmp1 where fld_with_index2 = 2;

-- 結果セット取れば良いよ。 order by
select * from tmp2 order by fld_with_index3
大量データのinsert

もう、mysql_dumpの中見てもらえれば良いと思うけど、

-- 以下みたいに書くより
insert into x ( ... );
insert into x ( ... );
insert into x ( ... );
insert into x ( ... );

-- この方が良いよ
insert into x values( ... ),( ... ),( ... ),( ... );
indexの再構築が遅い

一度に大量に突っ込むとindexの再構築が遅いです。

そんな一度に大量のデータ突っ込んだりとかするようなジャンクな使い方する場合には、MyISAM使ってると思うので*1、MyISAMの場合にはこうしましょう。

alter table x disable keys;
insert into x values( ... ),( ... ),( ... ),( ... );
insert into x values( ... ),( ... ),( ... ),( ... );
insert into x values( ... ),( ... ),( ... ),( ... );
-- ( 以下ry 多分100万回ループな感じで)
alter table x enable keys;

で、enable keys したタイミングで3日待たされたとかになっちゃう可能性が有ります。

id:ichii386 (いちい君) がここで言及してますが

http://d.hatena.ne.jp/ichii386/20070924/1190610994 

myisam_max_sort_file_size と言う項目を広げてやるとソートにかかるコストが減るので速くなります。

まとめ

show processlist

explain [使いたいselect文]

この二つしっかり見るところから始めよう。話はそれからだ。

その間に読んだ本

この本は良い。5系の話は出てきませんが役に立ちます。まさにバイブル。

何の役にも立ちませんでした。金返せな気分です。

フォローとしては

  • SQL初学者には便利かも。
  • ググれば十分。ググれない環境でMySQLを使う場合には役に立つかも。

です。

*1:そうなじゃない場合にはMyISAMを勧めます

Share and Enjoy:
  • Digg
  • del.icio.us
  • Google Bookmarks
  • Tumblr
  • email
  • Facebook
  • FriendFeed

RSS Feed

コメントはまだありません。

Leave a comment!

<< Web情報アーキテクチャ

viっぽく見るグリモン(ちゃんとリリース) >>

Find it!

Theme Design by devolux.org

Tag Cloud