DBを使うとやたらと重くなって仕方がない。。。
個別案件でヘルプが必要な方は
Linuxに関するアドバイスを行います！WEB・メール・DB・DNSサーバーどんなことでも！
そうお悩みのプログラム初心者の方も多いのではないでしょうか。
いろいろQ&A的なサイトを見ていても初心者の方の多い質問の一つが【重い】という内容です。
しかしながら、インデックスはかかっているのか？とか、メモリが足りないとか。。。確かに、そういった要件はあるかもしれませんが、もっともっと初歩的なところでつまづいていませんか?
そういう初心者のためにこの記事を書き残しています。
おそらく、バージョンやDB自体が何であるとか、そういった問題の前に基礎的なところができていない方が多いかもしれないということです。
ここに記載されていることをすべてやったにもかかわらず重い。。。とお悩みの方はどうぞQ&Aサイトなどでご質問ください。
MySQLパフォーマンスチューニングならこちらの書籍が参考になります!
私のMySQL青春のバイブルはこちら！
PHPは、非常にアバウトなコードでもうごくのですよ
ヘッドライン
表題にPHPと書いたのは、私自身ほかの言語ではどのくらいのパフォーマンスが出るのか、はたまた、どこまでPHP的なアバウトさでも動くのか、詳しく知らないためです。
逆に言えば、PHPとMySQLなどのDBに限って重い。。。という質問が多い気がしています。
それはなぜなのかといいますと、【PHPは、コードがアバウトでも動いてしまう】ことが原因なのです。
Perlだったら絶対動かないだろうな～というようなものでも実際は動いてしまうことが多いのです。しかも、大体望み通りに。。。
なので、【まぁいいや】でそのままにしてしまうことが多く、それが癖になっている人は、ちょっとデータ件数の多いDBや、複雑なSQL文を書くたびに重くなって行くことが多いのです。
クォートで囲むと囲まないとで劇的な差が出るのは知っていますか?
この記事は本当に初心者の方に向けた記事なので、【馬鹿にするな】と思われた方はもう読む必要はないと思います。
そもそも、クォートってなんだよっていうくらいの方が読むべき記事です。
また、こんなことも知らないで。。。と思うかもしれませんが、誰しもが生まれてきたときに言葉を知らなかったのと同様、始めたばかりの方、初心者の方は知らないので当然なのです。
ただ、PHPの怖いのは知っていても知らなくてもうごくには動くところなのです。。。
なので、ある程度いろいろなプログラムを組める方でも知らない！ということもあったりするのがPHPという言語です。怖いですね。
話は戻しますが、クォートというのは、【‘】のことです。
これは、シングルクォートなどとよく言われます。
シングルと付くのだから、ベッドと同様にダブルもありますダブルクォートは、【”】です。
そして、反対向きのバッククォートというものもあります。【`】です。
この3種は名称と使い方をしっかり覚えておかれることを”強く”お勧めします。
初心者～上級者に関係なく必ず覚えてください。それほど重要なものです。
クォートの使い方とは
まず、PHP側のお話ですが、PHPではクォートをたくさん使います。
以下にいくつか代表的な2つの使い方を示します。
- 関数内に文字列を記載するとき（例：include(“function.php”);)
- 配列名を記載するとき（例:$data[‘test’]）
の2つは最もよく使う使い方です。
シングルクォートとダブルクォート
よく質問があるのが、【シングルクォートとダブルクォートの使い分け、違いは何?】と聞かれることがありますが、基本的に同じです。
これらを使い分けるとしたら、HTML文を記載する際にテキストとして何を使うのかという程度のことで使い分けるくらいです。
HTML文にはよくダブルクォートを使います。
<div style=“color: red;”>
上記のようなHTML文をPHPから出力したいとしたらechoなどで表示指示を出します。
PHP分にするとこうなります。
<?php
echo “<div style=“color: red;”>“;
?>
となりますが、これではエラーを吐いてしまいます。
なぜなら、”ダブルクォートが混在してしまい、プログラム的にはどれが表示したいダブルクォートでどれが文字囲みのクォートなのか分別がつかないのです。
このような時に、シングルクォート・ダブルクォートを使い分けるのです。
<?php
echo ‘<div style=“color: red;”>‘;
?>
これであればプログラムは動作してくれます。
また、文中のシステムで使うような記号はエスケープ処理して使うことも可能です。
<?php
echo “<div style=\”color: red;\”>“;
?>
この書き方だと、エスケープしているものは単純に文字列としてあらわされるので、問題なく表示されるようになります。
もう一度まとめますと、シングルクォートとダブルクォートの用途に差はなく、文字列を囲む際の記号になります。
同じ文字列でも、ファイルパスを記載しているときなどがあります。
要は好きな方を使えばいいですし、自分なりにルールを決めて使い分ければいいということです。
私は、
- 文字列とファイルパスはダブルクォートで囲む
- 配列などの文字列はシングルクォートで囲む
という自分で決めたルールで使い分けしています。
これは、文字列をシングルクォートで囲むと、先述のHTML文のダブルクォートをエスケープしないというロスが発生します。
もちろんPHP的には表示してくれるのですが、それは、おそらくエスケープしなかっただけで文字列なんだろうなと、PHPがアバウトに処理してくれるからです。
重くなるのはそういったアバウト補完を繰り返すからなので、文字列をダブルクォートで囲むようにしていけば大体エスケープしなかった時点でエラーを出力し気づけます。
ただそれだけのためですが、それだけのために文字列はダブルクォートと決めています。
文字列を処理する際に必要なクォートをつけなければエラーを出力することがほとんどなので、このあたりでロスをすることはほとんどないですし、たかが知れています。
ロスの大きいのは配列などをループ処理する際などに発生するロスは膨大になります。
配列変数をクォートで囲んでいますか?
このあたりから、初心者は面倒なのでつけていなくて、動いたからいいやを繰り返し、結果として重いものになるのです。
どのくらいのロスがあるかといいますと、数十万件のデータを処理しようとして、クォートをつけるとつけないもので、6秒も10秒も反応速度が変わってきます(データ内容によってパフォーマンスが違うので確定的ではない）
どういったことかといいますと、
$data[test]
といった感じの配列変数などのカッコ内の文字を囲むか囲まないかという差です。
システム的に正しい書き方は
$data[‘test’]
$data[“test”]
などです。
コンピュータ内でどういう処理をしているかはここでは述べませんが、要はついていない場合、コンピュータ的にはクォートを補完して仮想的に付け足してくれる処理が発生すると考えてください。
もちろん処理が1回なら0.000000001秒とかという微々たる負荷ですが、これがループで何万回、何十万回と処理されると小数点の位置が桁上がりに上がってきます。
なので、ループ処理が発生してくると一気にパフォーマンスを落とす原因となります。
また、配列が複雑であればあるほどクォートの存在価値が出てきます。
早い話が四の五の言わずにつけましょう。ということです。
PHPは、7から体系が変わりましたので、PHP5のバイブルではなく、7用の書籍を読みましょう。
MySQLにおけるクォート
ほかのDBより、PHPとの組み合わせ的にはMySQLを使うことが多いので、MySQLに関して言及します。
クエリ文にもクォートを使うことが必要です。MySQLもやさしいDBサーバーで、クォートに関してはPHP同様に補完してくれます。
なので、クォートをつけなくても動くのですが、こちらは有るのと無いのとでは天地ほどの差が出ますので”絶対に”つけましょう。
つけるルールは2つ
DB名、テーブル名、フィールド名などDB側の名前にはバッククォート【`】、検索語や挿入したい文言はクォートもしくはダブルクォート【’OR”】というルールです。
クエリ文中、囲まないのは数値型の検索や挿入の際だけです。(囲んでもいいはず)
囲まない例
select * from databasename where id = test order by date DESC
囲んだ例
select * from `databasename` where `id` = ‘test’ order by `date` DESC
といった感じです。
クォートを入れたか入れないかだけの差ですが、これで5倍も10倍もパフォーマンスが違うのですから入れるべきです。
入れていない場合に、これは文章の切れ具合と、前後の文言を見ながらコンピュータ側で、これはフィールド名、これは検索語と判断しながら分けています。
バッククォートとクォートを入れることで、システム側の名前、入力した側の名前と分けることで処理が簡易化されるのです。
実際に、私が経験したことですが、
phpMyAdminで、ID検索をしたところ、40万件のデータの中から一意のID番号検索であれば0.1秒もかからずに出力できることを確認して、PHPに落とし込んでみました。
そうしたところ、データを呼び出すまでに6秒もかかってしまっていました。
その際のクエリ文は以下のものです。
select * from `data` where `id` = 1
です。
何らおかしいところがありません。
phpMyAdminで吐き出していたクエリは以下のものでした。
select * from `data` where `id` = ‘1’
でした。
ID番号になぜかシングルクォートを入れて検索していました。
先述したように、本来数字にはクォートはつけなくてよいという基本的なルールがあるのに、phpMyAdminではつけていたので、バグかな?と思ったのですが、私のミスでした。
フィールドの型がint型(数値型)になっておらず、varchar(文字列型)になっていました。
なので、入力された値が数値であってもフィールド的には文字列なので、シングルクォートがいる（あったほうが良い）のです。
なので、自分の書いたものにもシングルクォートをつけてみたところ、0.1秒で処理できるようになりました。
たった、’をつけただけでです。
40万件のレコードというのが多いのか少ないのかでいうと、MySQL的には決して多いほうではなく、5千万件を保有し問題なく稼働しているDBもあるそうです。
もちろん機械のスペック差はあれど、5千万件で問題なく動いているプログラムがあるのに、たった40万件でものすごく重くなるのはマシンスペックの前の話であるということですね。
PHPで、検索語や挿入語が変数だった場合にどう書くのかを以下に示しておきます。
select * from `data` where `id` = ‘{$_GET[‘id’]}’
などとなる。
{は、テキスト文中に変数を差し込みたい場合に変数間を大かっこで囲めばその中は変数として処理される。
echo “このページのIDは、”.$_GET[‘id’].”です”;
と書くより
echo “このページのIDは、{$_GET[‘id’]}です。”;
と書く方がわずかだが後者の方がパフォーマンスが良いのです。
PHPおよびMySQLは、やさしい言語とDBだからこそ、ちゃんと約束を守って書いてあげることでいいパフォーマンスを上げることができるということを忘れないでください。
無駄なフィールドを呼び出していませんか?
前項の処理はやっているけど、それでもやっぱり重い。。。
という方も多いはず。
もちろんケースによって様々ですし、いろいろやってもやっぱり重いということももちろんあります。
この記事では初歩的なことでつまづかないようにと書いた記事です。
次によくあるケースが＊(アスタリスク)で全フィールドをよびだしているから重いというケースです。
プログラム内でテーブルを呼び出し出力するときに思わず楽なので【*】アスタリスクで全フィールドを呼び出してしまいます。
フィールド数が少なく、そしてレコード数も少ないうちはフィールドを個別指定するのもアスタリスクもほとんど大差はありません。
しかし、レコード数が万を超えてくるあたりからパフォーマンスに差が出てきます。
たとえばですが、条件に合致するレコード件数（登録データ件数）をチェックするプログラムを書いていたとします。
select * from `data` where `id` >= 1000
ID番号が1000番以上のデータを呼び出すクエリ文です。
PHPであればこのクエリをmysql_num_rowsで処理すれば、データ件数がわかります。
かりに、1~1000000番までレコードがあったとした場合、999,000レコードあるわけです。
mysql_num_rows内の処理としては、条件に合致する999,000レコードがすべてのフィールドを使える状態にしたうえでぐるぐる呼び出されていると考えてください。全フィールドです。
全フィールドを呼び出すので1レコードあたりの容量はすべてのフィールドの容量です。それらを1回1回呼び出しながらカウントするわけですから当然重くなっていきます。
同じことなのですが、カウントするだけなのですから、呼び出すフィールドを1つに絞り込みます。
select `id` from `data` where `id` >=1000
上記のクエリであれば、1レコードで呼び出されるデータはID番号だけです。先ほどのものとは1レコードのサイズが全然違ってくることでしょう。
それだけ呼び出すものが少ないから処理も軽くなっていきます。
select count(`id`) as `count` from `data` where` id >= 1000
このクエリなら、SQL内で、カウントしてしまいます。
【count】というフィールドを作り、その中にidが入力されている分だけカウントした数字が格納されます。
PHPで呼び出すには、このクエリをmysql_fetch_arrayなどで呼び出し、配列変数countで、件数を出力できます。
こちらの方がおそらくパフォーマンス的にはいいと思います。MySQL上に無駄な処理がないからです。
select文は不必要なフィールドは呼び出さない
フィールドがたくさんあるときに面倒になったつい＊を使いたくなるのはよくわかりますが、全フィールドを使わないのであれば、使うフィールドだけを呼び出しましょう。
そうすることで少しでも呼び出し負荷を軽減できます。
最も負荷がかかりやすい処理が検索プログラム
DBを導入するときによくあるのが、キーワードなどを入力してもらって検索するプログラムを付けるケースです。
データが多くなれば、検索システムを作り、閲覧者にほしい情報を手に入れてもらいやすくするのは常套手段です。
この、検索システムを作った時からパフォーマンスが落ち、全体的に常に重くなることが多いのです。
まずは、ここまで書いてきた、【クォート】と、select分での＊をやめることである程度負荷は軽減できるはずです。
特に＊にかんしては、、検索プログラムで使うのはタイトルと簡単な説明文、ID程度いいのではないですか？使うフィールドと使わないフィールドをしっかりチェックしましょう。
次に、データ件数です。
これはデータが増えたときに気づく初心者の方が多いのですが、データ件数を指定しなければ基本的に無限に出力されます(条件に合致したものがなくなるまで出力される)
1,000,000件中、500,000件が条件に合致しているとしたら、500,000件出力しようとするのです。
もちろん、これをPHP側の処理で10件だけを表示するようにすることはできますが、MySQL的な処理としては、のこりの499,990件も処理しようとしてしまいます。
そこで出てくるのは、MySQLクエリのlimit文です。
select `id`,`title` from `data` where `id`>=1000 limit 0,10
と書いたりします。
limit 直後の0というのは、ポインタといわれるものの位置で、いわゆる1件目(プログラム上は1ではなく0から）から、カンマの後の行数分出力してくれます。
なので、0.10ということは、1件目(0件目)から、10件表示したら終わりですよ。という指示です。
2ページ目は、11件目からなので、10,10と書けば、11件目から10件出力するということになります。
これで、1,000,000件データがあったとしても10件で打ち止めなので、それなりに処理速度は向上します。
決して、10件で表示を止めるために、PHP側で10件表示したら止めるようにするのではなく、SQL側の出力としてそもそも10件までで止まるようにしておくのがパフォーマンス向上につながります。
ここまでの処理をしっかりと行うことで、インデックスやMySQL自体をチューニングしなくてもそれなりの速度をもって処理ができるはずです。
次からはド初心者から、初心者レベルへと移行します。
ワイルドカードの使い方
もちろん、検索プログラムを作ったらまずは全文検索を目指したいところですよね。
ただし、この全文検索も理解せずに使うとただただパフォーマンスが落ちます。
クエリでワイルドカードを使って検索するときにはじめて用語として出てくるのがこの全文検索や前方一致、後方一致という言葉でしょうか。
わかっていそうで実はわかっていない。そもそも常に全文検索すれば前方も後方もないじゃないかと思うかもしれません。
しかし、これらの指定によりパフォーマンスが大きく変わるのです。
全文検索や後方一致検索は重い
文字列検索をする際に、前方一致であればある程度良いパフォーマンスで検索できるということを知っていましたか?
前方一致というのは、頭の文字列が該当文字列で後方はどんなものでも構わないという検索方法です。
私たちから見たら、前方一致でも後方一致でもさして変わらないように見えますが、DB内ではまったく違うのです。
前方が決まっているものを探すのと、後方が指定されているものではパフォーマンスに大きく影響します。
なので、ほとんどの場合、前方一致タイプで検索させたいと考えるのがプログラマーさんの本心です。
大手検索エンジンではなく、自作、個人で作ったような検索エンジンなどの検索窓に、前方一致・後方一致・AND/OR検索などの選択肢を見かけたことがあるのではないでしょうか。
なるべく、前方一致で検索してほしいと願いつつも使い勝手が悪くなるとまずいので、それ以外の検索は指定してもらえれば検索できますよという作りにするのです。
理解していない方まで含めて全員にに全文検索などさせてしまうと、検索プログラムだけでサーバーがパンクしてしまう可能性があるからです。
よって、基本は前方一致で検索しきれない場合に限り、全文検索を認めるような書き方をするのです。
皆様はプログラムを書く際に意識していますか?
簡単に使えるワイルドカード
SQL文で、ワイルドカードを使えば自由に前方一致・後方一致・全文検索が行えます。
たとえば、
select * from `data` where `Name` like ‘山田太郎’
こう書きますと、完全一致タイプの検索になります。
select * from `data` where `Name` like ‘山田%’
こう書きますと、前方一致で、山田の後はどんな文字列が入っていてもOKという検索になります。
select * from `data` where `Name` like ‘%太郎’
これは後方一致、苗字はなんでもよく、【太郎】という名前の方を探します。
select * from `data` where `Name` like ‘%田%’
これは全文検索で、【田】が、文中どこでもいいのでは言っていたら検出されます。
【山田】も【田中】も検出されます。
【%】というのが、いわゆる『ワイルドカード』といわれるもので、文字列を補完する（文字数も文字タイプも関係なく）記号です。
これにより、完全一致しない部分一致の文字でも検索できるようになるのです。
この際の前方一致【文字列%】であれば、それなりのパフォーマンスで検出できますが、後方一致や全文検索での部分一致タイプでは、著しくパフォーマンスは落ちます。
細かいことは今この記事では述べませんが、そうだと思っておいてください。
なので、入力する文字や検索プログラムは前方一致で検出できるような文言や書き方で書くことを心掛けるべきなのです。
とはいえ、なかなか難しいのですが。。。
インデックスで早く処理できる
おそらく、ほとんどの場合、Q&AサイトなどにDBの処理が重いという質問を書くと、【インデックスは張っていますか?使えていますか?】といった答が返ってくることが多いのではないかと思います。
実際に、有効なインデックスを張ってクエリ発行の際にうまくインデックスを利用できればかなりパフォーマンスは上がるのは事実です。
しかし、理解せずにインデックスを張っても有効に使えない。また、使えているけどまだまだ遅いということにもつながります。
簡単に説明しておきます。
インデックスがないということはすべてのデータから毎回検索するのです
サブタイトルの通り、インデックスがないということは、格納されているデータすべての中から探し出すという作業になります。
もちろんインデックスを張っても、探すという事象に変わりはありませんが、インデックスを張ることで、探し出す範囲を限定して探すということにつながります。
結果、ディスクアクセスの回数が激減し、それだけ処理にかかる時間が早くなるというのがインデックスです。
ではインデックスは実際どのような物でしょう。
日本人が使う言葉としての「インデックス」とは付箋紙などでつける見出し
日本人であれば一度は見かけたことがあるものとして、ノートなどに、シールで見出しをつけてあるノートなど見かけたことはないだろうか。
よくあるのが電話帳です。
あ・い・う・え・おと、見出しシールがずらしてすべてが見えるように張ってあり、探したい人の頭文字のシールをもってめくればその頭文字から始まる方の電話番号が掲載されているリストにたどり着く。。。
こういったものを見かけたことはないでしょうか。
DBでのインデックスもまさにそのままで、頭から順番に同じものをまとめてあります。
コンピュータの場合は手書きの電話帳より精密に整理され、頭から順番に次の文字、次の文字とグループ化し分かれていくように作られています。
前項で前方一致が早いといったのはこの理由で、頭から追いかけるものに関しては、インデックスが貼ってあれば即座にたどり着けます。
後方一致や部分一致は結果としてインデックスをつかえないので、全データから精査するしかないのです。
細かいことは勉強してから
ここまでの記事を読み、一つでも分からない事、知らなかったことがある方はインデックスを勉強する前に知るべきことがまだまだたくさんあります。
もちろん、重くなるのでインデックスを張ってパフォーマンスを上げるというのはDBを使う以上絶対に必要なことです。
しかし、むやみやたらにインデックスを張ってパフォーマンスを落とす結果になったり、メモリが足りなくなったりすることも非常に多いのです。
やれる限りの負荷軽減設計を行い、さらにインデックスでパフォーマンスを上げるのがプログラマの仕事です。
インデックスだけに頼り、ほかの負荷軽減策を練らない方は、人気が出たWEBサイトを維持し続けるのは不可能でしょう。
なので、ここまでの記事を何度も読み直し、完全に理解して次のステップとしてインデックスを完全に理解してください。
まとめ
今回は、自分自身が実はつい最近、とんでもない重いプログラムを作ってしまい、悩みに悩んだ挙句、記事に書いた、SQL文の中でIDを検索する際にクォートをつけていなかったことが原因でした。たったそれだけですが、サーバーがパンク寸前のところから、ほぼ無負荷に近いところまで改善しました。
反応速度でいうて、囲む前は10秒～タイムアウトだったものが、遅くても1秒以内に表示するところまで改善できました。
数値はクォートで囲まなくてもよいという基本的ルールがあったので囲まなかったのですが、数値型になっていないフィールドに数値を入れていたことが原因でした。
文字列型にしてあった理由はあるのですが、やはり数値が入るものは数値型にしておけば今回引っかからなかったんだろうなと感じています。
これは、初心者の方なら、たくさんの方が悩んでいることだろうと感じたので記事に残しました。
どうやって一つ一つを解決すればいいのか
もちろんパフォーマンスだけではなく、ほかにもいろいろな問題が出てきて先に進めなくなることがあります。
かなりのスキルを持った方でも途中で止まってしまって先に進めなくなることも多いのです。
そんな時必ず私が述べるアドバイスは、PHPのエラーを出力させながら、すべてのエラーを解決しましょうといっています。
PHPは、この記事に書いた通りアバウトなものでも結構動いてくれます。
なので、本当に動かなくなってしまった時に原因がわからず悩むことがあります。
そういう時はすべての問題を解決すればもしかしたら見えるものが出てくるかもしれませんよということです。
ホスティングサービスなどで共有しているようなサーバーの場合、ログ出力が大きくなるので、PHPのエラーはWarningからしか出力しない設定になっていたりします。
また、画面上にエラーを出力しない設定になっていることが多いです。
あえてコードは書きませんが、（自分で調べてみましょう）PHPのエラーレベルや画面出力を変更できるコードがあります。
それらをうまく使い、開発段階ではNoticeも出力するように作り、Noticeもすべて解決することがハイパフォーマンスなプログラムを作ることにつながります。
PHPは、Warningエラー程度だと動いてしまうことがありますが、Warningも解決すれば、動かない直接的な原因がわかってきたり、NoticeとWarningを解決しただけでものすごく反応の良いプログラムになったりすることも多いです。
ぜひ、自分のプログラム1回の処理でNoticeをどれだけ吐いているのか見てみてください。
おそらく初心者の方はびっくりするくらい出力してきます。
それだけCPUやメモリに負担をかけているということを念頭に置きながらプログラム改善を行ってみてください。
今回はここまで。
具体的に質問したい方・助けてほしい方
ココナラでヘルプデスクやってます
Linuxに関するアドバイスを行います！WEB・メール・DB・DNSサーバーどんなことでも！
ぜひお問い合わせください!
Linuxに関するお問い合わせは、ココナラにて受け付けております。