より高速なサイトのためにSQLクエリを最適化する方法

画像の最適化とデータベースクエリの最適化：WordPress Webサイトの速度を改善するための実用的なガイド

この記事はもともとDelicious Brainsブログに掲載され、許可を得てここで再現されています

高速のウェブサイトは、より幸せなユーザー、より良いGoogleランキング、より高い変換率を意味することを知っています。 WordPressサイトは十分に速いと思うかもしれません。ベストサーバーのセットアッププラクティスからコードのトラブルシューティングを遅くすることや、CDNへのオフロードまで、サイトのパフォーマンスを確認しましたが、それはすべてですか？

WordPressのようなダイナミックのデータベース駆動型Webサイトの場合、問題に直面する可能性があります。データベースクエリは、Webサイトの速度を遅くします。

この投稿では、ボトルネックを引き起こすクエリを識別する方法、これらのクエリの問題を理解する方法、および迅速な修正とスピードを上げる方法を識別する方法について説明します。私たちが最近解決した実際のクエリを使用して、DeliciousBrains.comの顧客ポータルを遅くします。

クエリと識別

スローSQLクエリを修正する最初のステップは、それらを見つけることです。 Ashleyは以前、ブログのクエリモニターデバッグプラグインを称賛し、プラグインのデータベースクエリ機能により、SQLクエリが遅いことを識別するための貴重なツールになりました。このプラグインは、ページリクエスト中に実行されるすべてのデータベースクエリを報告します。コードまたはコンポーネント（プラグイン、テーマ、またはWordPressコア）でそれらをフィルタリングし、それらを呼び出し、重複したクエリとスロークエリを強調表示できます。

プロダクションサイトにデバッグプラグインをインストールしたくない場合（パフォーマンスオーバーヘッドの追加が心配なかもしれません）、MySQLスロークエリログを有効にすることを選択できます。 。これは、クエリのロギング場所を構成して設定するのが比較的簡単です。これはサーバーレベルのチューニングであるため、パフォーマンスの影響はサイト上のデバッグプラグインよりも少なくなりますが、使用していない場合はオフにする必要があります。

クエリの質問を理解する

改善するための高価なクエリを見つけた後、次のステップは、クエリが遅くなる原因を理解しようとすることです。最近、当社のウェブサイトを開発するとき、クエリが実行に約8秒かかることがわかりました！

WooCommerceおよびWooCommerceソフトウェアを使用して、プラグインのカスタムバージョンをサブスクライブしてプラグインストアを実行します。このクエリの目的は、顧客番号を知っている顧客にすべてのサブスクリプションを取得することです。 WooCommerceには、注文がカスタムポストタイプとして保存されている場合でも、顧客のID（各顧客がWordPressユーザーを作成するストア）がPost_authorとしてはなく、Post Metadataの一部として保存されます。ソフトウェアサブスクリプションプラグインは、いくつかのカスタムテーブル接続も作成します。クエリをさらに詳しく見てみましょう。

<code class="language-sql">SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date</code>

mysqlツールを使用

を使用します

mysqlは、列、データ型、デフォルト値など、テーブルの構造に関する情報を出力するために使用できる便利なDESCRIBEステートメントを提供します。したがって、DESCRIBE wp_postmeta;を行うと、次の結果が表示されます。

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20) unsigned	NO	PRI	NULL	auto_increment
post_id	bigint(20) unsigned	NO	MUL	0
meta_key	varchar(255)	YES	MUL	NULL
meta_value	longtext	YES		NULL

これはクールですが、おそらくすでに知っているでしょう。しかし、

ステートメントプレフィックスを実際にDESCRIBE、SELECT、INSERT、UPDATE、REPLACE、およびDELETEステートメントに使用できることをご存知ですか？これは、その同義語EXPLAINと呼ばれることが多いため、ステートメントの実行方法に関する詳細情報が提供されます。

以下は、スロークエリの結果です：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	pm2	ref	meta_key	meta_key	576	const	28	Using where; Using temporary; Using filesort
1	SIMPLE	pm	ref	post_id,meta_key	meta_key	576	const	37456	Using where
1	SIMPLE	p	eq_ref	PRIMARY,type_status_date	PRIMARY	8	deliciousbrainsdev.pm.post_id	1	Using where
1	SIMPLE	l	ref	PRIMARY,order_id	order_id	8	deliciousbrainsdev.pm.post_id	1	Using index condition; Using where
1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	8	deliciousbrainsdev.l.key_id	1	NULL

一見すると、これは簡単に説明できません。幸いなことに、SitePointの友人は、声明を理解するための包括的なガイドを書いています。

最も重要な列は

です。これは、テーブルの結合方法を説​​明しています。 typeが表示されている場合、MySQLがディスクからテーブル全体を読み取り、I/Oレートを上げてCPU負荷を増加させていることを意味します。これは「フルテーブルスキャン」と呼ばれます（詳細については、後で詳しく説明します）。 ALL また、

列は、結果を見つけるために見ている行の数を示すため、mysqlが行う必要があるという適切な兆候でもあります。 rows

最適化のために利用可能な詳細情報も提供されています。たとえば、EXPLAINテーブル（pm2）は、ステートメントのwp_postmeta句を使用して結果をソートする必要があるため、filesortを使用していることを示しています。クエリもグループ化すると、実行のオーバーヘッドが増加します。 ORDER BY

視覚分析

mysqlワークベンチは、そのような調査に使用できるもう1つの便利で無料のツールです。 MySQL 5.6以降で実行されているデータベースの場合、

の結果はJSONとして出力される可能性があります。MySQLWorkbenchがステートメントの視覚的実行計画に変換します。 EXPLAIN

クエリの部分をコストで色付けすることで、自動的に注意を引きます。

（エイリアスL）テーブルへの結合に深刻な問題があることがすぐにわかります。

wp_woocommerce_software_licences

solution

フルテーブルスキャンはクエリの一部で実行されています。

>テーブルと

テーブルの間の接続として非インデックス列order_idを使用するため、これを避けるようにしてください。 。これは、遅いクエリの一般的な問題であり、簡単に解決できます。 wp_woocommerce_software_licences wp_posts

index

を追加します

はテーブル内の非常に重要な識別データの一部であり、このような照会を照会する場合は、実際にその列にインデックスを追加する必要があります。そうしないと、MySQLは目的の行が見つかるまで徐々にスキャンします。インデックスを追加して、それが何をするか見てみましょう：

order_id

<code class="language-sql">SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date</code>

すごい、このインデックスを5秒以上追加することでクエリを正常に削減しました。

クエリを学習します

クエリコネクトを1つずつチェックし、1つずつサブクエリします。それは不要な操作を実行しましたか？どのような最適化を行うことができますか？

この場合、ステートメントをポストタイプのorder_id>に制限しながら、shop_orderを使用してライセンステーブルをポストテーブルに接続します。これは、データの整合性を強制して、正しい注文レコードのみを使用するようにするためです。ただし、実際にはクエリの冗長な部分です。 PHPプラグインコードで施行されているため、ポストテーブルのWooCommerce順序に関連するソフトウェアライセンスの行をテーブルに持つことは安全な賭けであることがわかっています。接続を削除して、これが状況を改善するかどうかを確認しましょう：order_id

これは大きな節約ではありませんが、クエリは3秒未満になりました。

キャッシュ

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

Query Monitorは、クエリがページロードで4回実行されていることを発見し、MySQLクエリキャッシュを有効にすることは良いことでありながら、1つの要求でデータベースを繰り返し読むことは実際には完全に回避する必要があります。 PHPコードでの静的キャッシュは、この問題を解決するためのシンプルで非常に効率的な方法です。基本的に、データベースクエリの結果を初めてリクエストしてクラスの静的プロパティに保存するときにデータベースからそれらを取得し、その後の呼び出しは静的プロパティから結果を返します：

<code class="language-sql">SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date</code>

キャッシュの寿命は、リクエストの寿命、より具体的にはインスタンス化されたオブジェクトの寿命です。リクエスト間でクエリの結果を永続化する場合は、永続的なオブジェクトキャッシングを実装する必要があります。ただし、コードは、基礎となるデータが変更されたときにキャッシュを設定し、キャッシュエントリを無効にする責任を負う必要があります。

その他の方法

クエリ実行をスピードアップするために他の方法をとることができます。これには、クエリを調整したり、インデックスを追加するよりも多くの作業が必要です。クエリの最も遅い部分の1つは、顧客IDから製品IDまでのテーブル接続作業です。これは、各顧客に対して行う必要があります。すべての接続を一度だけ実行した場合、必要なときに顧客データを取得する必要がある場合にはどうすればよいですか？

すべてのライセンスのライセンスデータとユーザーIDと製品IDを保存するテーブルを作成することにより、データを削除することができます。特定の顧客のテーブルを照会するだけです。 mysqlトリガーを使用する場合は、 同様に、多くの接続がMySQLのクエリを遅くする場合、クエリを2つ以上のステートメントに分割してPHPで個別に実行し、コードで結果を収集してフィルタリングする方が速くなる可能性があります。 Laravelは、雄弁に関係を積極的にロードすることにより、同様の操作を実行します。 INSERT

大量のデータがあり、さまざまなカスタムポストタイプがある場合、WordPressはwp_postsテーブルのクエリが遅くなる傾向があります。投稿タイプを照会するのが遅い場合は、カスタムポストタイプのストレージモデルを放棄し、カスタムテーブルを使用することを検討してください。

result

これらのクエリ最適化方法を使用して、クエリ時間を8秒からわずか2秒に短縮し、コールの数を4から1に減らすことができました。これらのクエリ時間は開発環境で記録されており、生産環境ではより速くなることに注意してください。

このガイドが、スロークエリを追跡して修正するのに役立つことを願っています。クエリの最適化はひどい作業のように思えるかもしれませんが、すぐに成功しようとすると、それに魅了され、さらに改善することを望んでいます。

以上がより高速なサイトのためにSQLクエリを最適化する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。