shell # localhostのMySQLサーバに接続する場合 $ mysql -u [ユーザー名] -p # 外部MySQLサーバに接続する場合 $ mysql -u [ユーザー名] -p-h [host名] -P [ポート番号] -p オプションはパスワード設定しているときのみ付与する < … コメント:StaticURI = '/.shared-asp13/'; テーブルにインデックス(index)を作成. MYSQLの処理で重くなっている部分を見直したり、ネットで高速化についての勉強をしていまして, 一番良いのは処理の時間が長くなってしまう「like %%」を全文検索で 3 / クリップ Ruby on RailsではActiveRecord内部で文字列のエスケープを行うため、, まずはMySQLでの文字列のエスケープ/クォートでの囲み方の一部について例をあげます。, 上記のSQLではテーブル some_table からすべてのレコードのnameカラムを出力しています。, 文字中にバッククォートが含まれる場合はバッククォート「`」をふたつ続けてかきます。, 上記のSQLではテーブル some_tableからnameカラムが「%abc_」で前方一致するレコードのnameカラムを出力しています。, パーセント記号「%」やアンダースコア「_」はそのまま書くとワイルドカードとして機能します。, 上記のSQLではテーブル some_tableからすべてのレコードのnameカラムをnameカラムの昇順で出力しています。, カラム名に続く ASC or DESCについては定数のようなもので、エスケープやクォーティングは行いません。, ASC or DESCに文字列を入力する場合は入力する文字列がASCかDESCのいずれかのみを入力させるよう注意する必要があります。, 以上複雑なようですが、エスケープ・クォートの方法は以下4パターンになると思います。, 別名中にバッククォートが含まれる場合はバッククォート「`」をふたつ続けてかきます。. パフォーマンス1000%UP!PHPでMySQLのDB処理を行うと重いときに行うパフォーマンス施策~基礎編~, Linuxに関するアドバイスを行います!WEB・メール・DB・DNSサーバーどんなことでも!, APIの接続制限対策その2 接続できなくてもDBにデータがあったらそちらを表示するように【PHP】【API】【ビッグデータ】, WEBサーバーパフォーマンスチューニング・どんなサーバーを選ぶ?~トラフィック制限について~. 前方一致というのは、頭の文字列が該当文字列で後方はどんなものでも構わないという検索方法です。 私たちから見たら、前方一致でも後方一致でもさして変わらないように見えますが、DB内ではまったく違うのです。 前方が決まっているものを探すのと、後方が指定されているものではパフォーマンスに大きく影響します。 なので、ほとんどの場合、前方一致タイプで検索させたいと考えるのがプログラマーさんの本心です。 大手検索エンジンではなく、自作、個人で作ったような検索エンジンなどの検索窓に、前方一致・後方一致・AND/OR検索などの選択肢を見かけたことがあるのではないでしょうか。 なるべく、前方一致で検索してほしいと願いつつも使い勝手が悪くなるとまずいので、それ以外の検索は指定してもらえれば検索できますよという作りにするのです。 理解していない方まで含めて全員にに全文検索などさせてしまうと、検索プログラムだけでサーバーがパンクしてしまう可能性があるからです。 よって、基本は前方一致で検索しきれない場合に限り、全文検索を認めるような書き方をするのです。 皆様はプログラムを書く際に意識していますか? この記事は公開から1年以上が経過しています。情報が古い可能性がありますのでご注意ください。, rack-lineprofを改造して管理画面からファイル指定&ログ追跡出来るように. そういう初心者のためにこの記事を書き残しています。 おそらく、バージョンやDB自体が何であるとか、そういった問題の前に基礎的なところができていない方が多いかもしれないということです。 ここに記載されていることをすべてやったにもかかわらず重い。。。とお悩みの方はどうぞQ&Aサイトなどでご質問ください。, 表題にPHPと書いたのは、私自身ほかの言語ではどのくらいのパフォーマンスが出るのか、はたまた、どこまでPHP的なアバウトさでも動くのか、詳しく知らないためです。 逆に言えば、PHPとMySQLなどのDBに限って重い。。。という質問が多い気がしています。 それはなぜなのかといいますと、【PHPは、コードがアバウトでも動いてしまう】ことが原因なのです。 Perlだったら絶対動かないだろうな~というようなものでも実際は動いてしまうことが多いのです。しかも、大体望み通りに。。。 なので、【まぁいいや】でそのままにしてしまうことが多く、それが癖になっている人は、ちょっとデータ件数の多いDBや、複雑なSQL文を書くたびに重くなって行くことが多いのです。, この記事は本当に初心者の方に向けた記事なので、【馬鹿にするな】と思われた方はもう読む必要はないと思います。 そもそも、クォートってなんだよっていうくらいの方が読むべき記事です。 また、こんなことも知らないで。。。と思うかもしれませんが、誰しもが生まれてきたときに言葉を知らなかったのと同様、始めたばかりの方、初心者の方は知らないので当然なのです。 ただ、PHPの怖いのは知っていても知らなくてもうごくには動くところなのです。。。 なので、ある程度いろいろなプログラムを組める方でも知らない!ということもあったりするのがPHPという言語です。怖いですね。, 話は戻しますが、クォートというのは、【‘】のことです。 これは、シングルクォートなどとよく言われます。 シングルと付くのだから、ベッドと同様にダブルもありますダブルクォートは、【”】です。 そして、反対向きのバッククォートというものもあります。【`】です。 この3種は名称と使い方をしっかり覚えておかれることを”強く”お勧めします。 初心者~上級者に関係なく必ず覚えてください。それほど重要なものです。, まず、PHP側のお話ですが、PHPではクォートをたくさん使います。 以下にいくつか代表的な2つの使い方を示します。, よく質問があるのが、【シングルクォートとダブルクォートの使い分け、違いは何?】と聞かれることがありますが、基本的に同じです。 これらを使い分けるとしたら、HTML文を記載する際にテキストとして何を使うのかという程度のことで使い分けるくらいです。 HTML文にはよくダブルクォートを使います。, 上記のようなHTML文をPHPから出力したいとしたらechoなどで表示指示を出します。 PHP分にするとこうなります。, となりますが、これではエラーを吐いてしまいます。 なぜなら、”ダブルクォートが混在してしまい、プログラム的にはどれが表示したいダブルクォートでどれが文字囲みのクォートなのか分別がつかないのです。 このような時に、シングルクォート・ダブルクォートを使い分けるのです。, これであればプログラムは動作してくれます。 また、文中のシステムで使うような記号はエスケープ処理して使うことも可能です。, この書き方だと、エスケープしているものは単純に文字列としてあらわされるので、問題なく表示されるようになります。 もう一度まとめますと、シングルクォートとダブルクォートの用途に差はなく、文字列を囲む際の記号になります。 同じ文字列でも、ファイルパスを記載しているときなどがあります。 要は好きな方を使えばいいですし、自分なりにルールを決めて使い分ければいいということです。 私は、, という自分で決めたルールで使い分けしています。 これは、文字列をシングルクォートで囲むと、先述のHTML文のダブルクォートをエスケープしないというロスが発生します。 もちろんPHP的には表示してくれるのですが、それは、おそらくエスケープしなかっただけで文字列なんだろうなと、PHPがアバウトに処理してくれるからです。 重くなるのはそういったアバウト補完を繰り返すからなので、文字列をダブルクォートで囲むようにしていけば大体エスケープしなかった時点でエラーを出力し気づけます。 ただそれだけのためですが、それだけのために文字列はダブルクォートと決めています。, 文字列を処理する際に必要なクォートをつけなければエラーを出力することがほとんどなので、このあたりでロスをすることはほとんどないですし、たかが知れています。 ロスの大きいのは配列などをループ処理する際などに発生するロスは膨大になります。, このあたりから、初心者は面倒なのでつけていなくて、動いたからいいやを繰り返し、結果として重いものになるのです。 どのくらいのロスがあるかといいますと、数十万件のデータを処理しようとして、クォートをつけるとつけないもので、6秒も10秒も反応速度が変わってきます(データ内容によってパフォーマンスが違うので確定的ではない) どういったことかといいますと、, などです。 コンピュータ内でどういう処理をしているかはここでは述べませんが、要はついていない場合、コンピュータ的にはクォートを補完して仮想的に付け足してくれる処理が発生すると考えてください。 もちろん処理が1回なら0.000000001秒とかという微々たる負荷ですが、これがループで何万回、何十万回と処理されると小数点の位置が桁上がりに上がってきます。 なので、ループ処理が発生してくると一気にパフォーマンスを落とす原因となります。 また、配列が複雑であればあるほどクォートの存在価値が出てきます。 早い話が四の五の言わずにつけましょう。ということです。, PHPは、7から体系が変わりましたので、PHP5のバイブルではなく、7用の書籍を読みましょう。, ほかのDBより、PHPとの組み合わせ的にはMySQLを使うことが多いので、MySQLに関して言及します。 クエリ文にもクォートを使うことが必要です。MySQLもやさしいDBサーバーで、クォートに関してはPHP同様に補完してくれます。 なので、クォートをつけなくても動くのですが、こちらは有るのと無いのとでは天地ほどの差が出ますので”絶対に”つけましょう。, 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秒もかかってしまっていました。 その際のクエリ文は以下のものです。, です。 何らおかしいところがありません。 phpMyAdminで吐き出していたクエリは以下のものでした。, でした。 ID番号になぜかシングルクォートを入れて検索していました。 先述したように、本来数字にはクォートはつけなくてよいという基本的なルールがあるのに、phpMyAdminではつけていたので、バグかな?と思ったのですが、私のミスでした。 フィールドの型がint型(数値型)になっておらず、varchar(文字列型)になっていました。 なので、入力された値が数値であってもフィールド的には文字列なので、シングルクォートがいる(あったほうが良い)のです。 なので、自分の書いたものにもシングルクォートをつけてみたところ、0.1秒で処理できるようになりました。 たった、’をつけただけでです。 40万件のレコードというのが多いのか少ないのかでいうと、MySQL的には決して多いほうではなく、5千万件を保有し問題なく稼働しているDBもあるそうです。 もちろん機械のスペック差はあれど、5千万件で問題なく動いているプログラムがあるのに、たった40万件でものすごく重くなるのはマシンスペックの前の話であるということですね。, select * from `data` where `id` = ‘{$_GET[‘id’]}’, などとなる。 {は、テキスト文中に変数を差し込みたい場合に変数間を大かっこで囲めばその中は変数として処理される。, PHPおよびMySQLは、やさしい言語とDBだからこそ、ちゃんと約束を守って書いてあげることでいいパフォーマンスを上げることができるということを忘れないでください。, 前項の処理はやっているけど、それでもやっぱり重い。。。 という方も多いはず。 もちろんケースによって様々ですし、いろいろやってもやっぱり重いということももちろんあります。 この記事では初歩的なことでつまづかないようにと書いた記事です。 次によくあるケースが*(アスタリスク)で全フィールドをよびだしているから重いというケースです。, プログラム内でテーブルを呼び出し出力するときに思わず楽なので【*】アスタリスクで全フィールドを呼び出してしまいます。 フィールド数が少なく、そしてレコード数も少ないうちはフィールドを個別指定するのもアスタリスクもほとんど大差はありません。 しかし、レコード数が万を超えてくるあたりからパフォーマンスに差が出てきます。 たとえばですが、条件に合致するレコード件数(登録データ件数)をチェックするプログラムを書いていたとします。, 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上に無駄な処理がないからです。, フィールドがたくさんあるときに面倒になったつい*を使いたくなるのはよくわかりますが、全フィールドを使わないのであれば、使うフィールドだけを呼び出しましょう。 そうすることで少しでも呼び出し負荷を軽減できます。, 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自体をチューニングしなくてもそれなりの速度をもって処理ができるはずです。 次からはド初心者から、初心者レベルへと移行します。, もちろん、検索プログラムを作ったらまずは全文検索を目指したいところですよね。 ただし、この全文検索も理解せずに使うとただただパフォーマンスが落ちます。, クエリでワイルドカードを使って検索するときにはじめて用語として出てくるのがこの全文検索や前方一致、後方一致という言葉でしょうか。 わかっていそうで実はわかっていない。そもそも常に全文検索すれば前方も後方もないじゃないかと思うかもしれません。 しかし、これらの指定によりパフォーマンスが大きく変わるのです。. たとえば、バイナリデータ「0x95 0x5c 0x6e」を latin1 としてエスケープすると、「0x95 0x5c 0x5c 0x6e」になりますが、接続の文字コードがシフトJISだと、MySQL サーバーは「表\n」と解釈して、「0x95 0x5c 0x0a」として扱われてしまいます。, 今回は MySQL の C API を例に使用しましたが、どのプログラミング言語であっても同様に注意しなければいけません。 続いて「クォート」と「クォーテーション」の違いについてですが、正直よくわかりませんでした。 調べても確たる由来や歴史が見当たりませんでしたし、辞書を見てもさっぱり。たぶん言語に詳しい専門家でもないと、答えられないと思います。ご存知の方、いらっしゃればぜひ教えてください! mysql table追加時 Incorrect default value '0000-00-00... mysqlにおけるvarchar型カラムへのシングルクオートを含む文のINSERTについて, 回答 【*】アスタリスクで全フィールドを呼び出してしまいます。 Emoticon.Pallet.captionText = 'シフトキー+クリックで連続入力'; http://app.mitelog.jp/t/trackback/459161/34183784, 【MySQL】カラムの中のカンマ区切りのコードチェックは FIND_IN_SET 関数を使用する, 【ActiveReports 2.0J】複数フォーマットを合体させて1個のレポートを作成する方法, 【MySQL】テーブル作成時にカラムにAUTO_INCREMENTを設定する方法について, BHT-BASIC:DENSOのハンディターミナルの開発言語のBHT-BASIC4.0を使ってみた, « 【MySQL】既に存在するTABLEに複数カラムを位置指定で追加する方法について(ALTER TABLE), 【MySQL】既に存在するTABLEに複数カラムを位置指定で追加する方法について(ALTER TABLE), 【MySQL】UNIONMINUSが無いので、代わりにNOTEXISTSを使う方法について.