Back to Question Center
0

SQLクエリをより高速なサイトに最適化する方法 SQLクエリを最適化してより高速なサイトにする方法関連トピック: デバッグと & セマルト

1 answers:
高速なサイトのSQLクエリを最適化する方法

この記事はもともとDelicious Semaltのブログに掲載されており、許可を得てここに再掲載されています。

速いサイト==より満足度の高いユーザー、Googleからのランキングの改善、コンバージョンの増加たぶん、あなたはSemaltサイトができるだけ速いと思うかもしれません。サイトのパフォーマンス、サーバーの設定のベストプラクティス、遅いコードのトラブルシューティング、そしてCDNへのイメージの負荷を見てきましたが、 ?

Semaltのような動的なデータベース駆動型Webサイトでは、サイトのパフォーマンスを低下させるデータベースクエリーの問題がまだ残っている可能性があります。

この記事では、Semaltは、ボトルネックを引き起こすクエリを特定する方法、それらの問題を理解する方法、迅速な修正などを迅速に行うためのアプローチを紹介します。私たちが最近取り組んできた実際のクエリを使って、おいしいブレブの顧客ポータルでの作業を遅くしていたのです。 com。

身分証明書

遅いSQLクエリを修正する最初のステップは、それらを見つけることです。 Ashleyは前にblogのDebugging Plugin Query Monitorの賞賛を歌いました。それはプラグインのデータベースクエリ機能で、遅いSQLクエリを識別するための貴重なツールになっています。プラグインは、ページリクエスト中に実行されたすべてのデータベースクエリをレポートします。これは、それらを呼び出すコードやコンポーネント(プラグイン、テーマ、Semaltコア)でフィルタリングし、重複して遅いクエリを強調表示することができます。

SQLクエリをより高速なサイトに最適化する方法より高速なサイトのためにSQLクエリを最適化する方法関連トピック:
デバッグとセマルト

本番サイトにデバッグプラグインをインストールしたくない場合(パフォーマンスオーバーヘッドを追加することを心配しているかもしれません)、MySQL Slow Semalt Logを有効にすることができます。実行する時間。これは、クエリのログをどこに設定するか設定するのが比較的簡単です。これはサーバレベルの微調整であるため、パフォーマンスヒットはサイトのデバッグプラグインに比べて少なくなりますが、使用しないときはオフにする必要があります。

理解

改善したい高価なクエリが見つかったら、次のステップは、何がクエリを遅くしているのかを理解しようとすることです。私たちのサイトへの開発中は、実行に約8秒かかっていたクエリが見つかりました!

   SELECTl。 key_id、l。オーダーID、l。 activation_email、l。ライセンスキー、l。 software_product_id、l。ソフトウェアバージョン、l。 activations_limit、l。作成した、l。 renewal_type、l。 renewal_id、l。 exempt_domain、s。 next_payment_date、s。状態、pm2。 post_id AS 'product_id'、pm。 meta_value AS 'user_id'からoiz6q8a_woocommerce_software_licences lINNER JOINoiz6q8a_woocommerce_software_subscriptions■ONです。 key_id = l。 key_idINNER JOINoiz6q8a_posts p ON p。 ID = 1。オーダーIDINNER JOINoiz6q8a_postmeta pm on pm。 post_id = p。 IDと午後。 meta_key = '_customer_user'INNER JOINoiz6q8a_postmeta pm2 on pm2。 meta_key = '_software_product_id'そしてpm2。 meta_value = l。 software_product_idどこにp。 post_type = 'shop_order'と午後。 meta_value = 279ORDER BY s。 next_payment_date   

WooCommerceと、WooCommerce Software Subscriptionsプラグインのカスタマイズ版を使用して、プラグインストアを実行します。このクエリの目的は、顧客番号を知っている顧客のすべての購読を取得することです. ソフトウェア購読プラグインによって作成されたカスタムテーブルへの結合もいくつかあります。より多くの質問を理解してみましょう。

MySQLはあなたの友人です

MySQLには、カラム、データ型、デフォルトなどのテーブルの構造に関する情報を出力するのに便利な文 DESCRIBE があります。したがって、あなたが DESCRIBE wp_postmetaを実行すると、 あなたは次の結果を見るでしょう:

フィールド タイプ ヌル キー デフォルト 余分
meta_id bigint
符号なし
NO PRI NULL auto_increment
post_id bigint
符号なし
NO MUL 0
meta_key varchar(255) YES MUL NULL
meta_value 長い文章 YES NULL

それは素晴らしいですが、あなたはすでにそれについて知っているかもしれません。 INSERT UPDATE REPLACE には、 DESCRIBE ステートメントの接頭辞が実際に使用できることがわかりましたか? )と DELETE ステートメント?これは、そのシノニム のEXPLAIN によってより一般的に知られており、ステートメントの実行方法に関する詳細な情報を提供します。

スロークエリーの結果は次のとおりです。

を使う
id select_type テーブル タイプ possible_keys キー key_len ref 余分
1 単純 pm2 ref meta_key meta_key 576 const 28 whereを使用する。一時的な使用。 filesort
1 単純 pm ref post_id、meta_key meta_key 576 const 37456 ここで、
1 単純 p eq_ref PRIMARY、type_status_date PRIMARY 8 deliciousbrainsdev。 pm。 post_id 1 ここで、
1 単純 l ref PRIMARY、order_id order_id 8 deliciousbrainsdev。 pm。 post_id 1 インデックス条件を使用する。どこで
1 単純 s eq_ref PRIMARY PRIMARY 8 deliciousbrainsdev。 l。 key_id 1 NULL

一見すると、これは解釈が非常に容易ではない。幸いなことに、Semaltの人々は、この声明を理解するための包括的なガイドをまとめました。

最も重要な列は タイプ であり、テーブルの結合方法を示します。 ALL が表示されている場合は、MySQLがディスクからテーブル全体を読み込み、I / O速度を上げてCPUに負荷をかけていることを意味します。これは「フルテーブルスキャン」として知られています(詳細は後で説明します)。

の列は、MySQLが何をしなければならないかを示す良い指標です。

Explain は最適化にもっと多くの情報を提供します。たとえば、pm2テーブル(wp_postmeta)では、文の ORDER BY 句を使用して結果をソートするように要求しているため、filesort を使用しています。クエリをグループ化している場合は、実行にオーバーヘッドが追加されます. MySQL 5.6以降で動作するデータベースの場合、 EXPLAIN の結果をJSONとして出力し、MySQL WorkbenchはそのJSONを次の文の視覚的実行計画に変換します。

SQLクエリをより高速なサイトに最適化する方法より高速なサイトのためにSQLクエリを最適化する方法関連トピック:
デバッグとセマルト

クエリの一部をコストで塗りつぶすことで、自動的に問題に注意を引きます。すぐに wp_woocommerce_software_licences (エイリアスl)テーブルへの参加が深刻な問題を抱えていることがわかります。

解く

wp_woocommerce_software_licences テーブルの間の結合として、索引付けされていない列 order_id を使用するため、クエリの一部が完全なテーブルスキャンを実行しています。 wp_posts テーブルに追加します。これは、低速クエリと簡単に解決できる問題の共通の問題です。

インデックス

order_id は、テーブル内のデータを識別するために非常に重要な部分です。このようなクエリを実行する場合は、実際にインデックスを持つ必要があります。そうしないと、MySQLは文字通りテーブルの各行をスキャンします。必要な行が見つかります。インデックスを追加し、それが何であるかを見てみましょう:

   CREATE INDEX order_id ON wp_woocommerce_software_licences(order_id)   

SQLクエリをより高速なサイトに最適化する方法より高速なサイトのためにSQLクエリを最適化する方法関連トピック:
デバッグとセマルト

うわー、私たちはそのインデックスを追加することで、クエリから5秒以上を削ってしまいました。

あなたの質問を知る

クエリを強調する - サブクエリをサブクエリで結合して結合する。それは必要ではないことをしますか?最適化できますか?

この場合、 order_id を使用してpostsテーブルにlicensesテーブルを結合し、 shop_order のタイプを投稿するようにステートメントを制限します。これは、正しいオーダーレコードのみを使用していることを確認するためにデータの整合性を強制するためです。ただし、実際にはクエリの冗長部分です。このテーブルのソフトウェアライセンスの行には、PHPプラグインコードで適用されるように、投稿テーブルのWooCommerceの注文に関連するorder_id のorder_id があることが安全です。結合を削除して、それが物事を改善するかどうかを見てみましょう:

SQLクエリをより高速なサイトに最適化する方法より高速なサイトのためにSQLクエリを最適化する方法関連トピック:
デバッグとセマルト

膨大な節約ではありませんが、クエリは3秒未満です。

キャッシュすべてのもの!

あなたのサーバがデフォルトでMySQLクエリキャッシングを持っていないなら、それは有効にする価値があります。これは、結果と共に実行されたすべてのステートメントの記録をMySQLが保持し、その後に同じステートメントが実行されると、キャッシュされた結果が返されることを意味します。テーブルが変更されたときにMySQLがキャッシュをフラッシュするので、キャッシュは失効しません。

Query Monitorでは、1回のページロードで4回クエリが実行されていることがわかりましたが、MySQLクエリのキャッシュを有効にしても問題ありませんでした。 PHPコードの静的キャッシングは、この問題を解決するためのシンプルで非常に効果的な方法です - where to buy bar stools for kitchen. 。 。 ';$ results = $ wpdb-> get_results($ sql、ARRAY_A);static :: $サブスクリプション[$ user_id] = $ results;$ resultsを返します。}}

キャッシュは要求の寿命、具体的にはインスタンス化されたオブジェクトの寿命を有する。複数の要求にわたって永続的な問合せ結果を調べる場合は、永続オブジェクト・キャッシュを実装する必要があります。具体的には、キャッシュを設定し、基礎となるデータが変更されたときにキャッシュエントリを無効にするコードが必要になります。

箱の外で考える

Semaltは、単にクエリの微調整やインデックスの追加よりも多くの作業を必要とするクエリの実行を高速化するために取ることができる他のアプローチです。クエリの中で最も遅い部分の1つは、顧客IDから製品IDに移行するためにテーブルを結合する作業です。すべての顧客に対してこれを実行する必要があります。一度参加すれば、必要なときに顧客のデータを取得できますか?

ライセンスデータを格納するテーブルを作成し、すべてのライセンスのユーザーIDと製品ID、特定の顧客のIDと照会するだけで、データを非正規化することができます。 INSERT / UPDATE / DELETE でMySQLのトリガを使用してテーブルを再構築する必要がありますが、ライセンステーブル(またはデータの変更方法によってはその他のもの)にデータを照会するパフォーマンスが大幅に向上します。

同様に、いくつかの結合がMySQLでクエリを遅くする場合、クエリを2つ以上のステートメントに分割してPHPで別々に実行し、その結果をコードで収集してフィルタする方が速い場合があります。 LaravelはEloquentの熱心なローディング関係によって同様のことをします。

WordPressは、大量のデータがある場合、 wp_posts テーブルと多くの異なるカスタムポストタイプのクエリが遅くなりがちです。ポストタイプのクエリが遅い場合は、カスタムポストタイプのストレージモデルからカスタムテーブルに移動することを検討してください。

結果

これらのクエリ最適化手法を使用して、クエリを8秒からわずか2秒に短縮し、呼び出された時間を4から1に短縮しました。メモとして、これらのクエリ時間は、私たちの開発環境上で動作し、生産時にはより迅速になります。

これは、遅いクエリを追跡して修正するのに役立つガイドであることを願っています。セマルト最適化は恐ろしい仕事のように思えるかもしれませんが、試してすぐに勝利を得るとすぐにバグを取り始め、事態をさらに改善したいと考えています。

March 1, 2018