MySQLのパフォーマンスは、インデックスを使用して説明し、説明します-PHPチュートリアル-php.cn

MySQL Performance Boosting with Indexes and Explain

キーポイント

MySQLのEXPLAINコマンドを使用して、クエリ実行計画を分析および最適化し、接続タイプやインデックス使用量などの重要な情報を表示することにより、より効率的なデータベース操作を保証します。
コマンドからのフィードバックに基づいて適切なインデックスを追加し、EXPLAIN句で使用される列に焦点を当て、データの取得を高速化し、クエリパフォーマンスを改善します。 WHERE
オペレーターを使用する場合は、フルテキストインデックスを使用してパフォーマンスを最適化することを検討してください。 LIKE 特にインデックスが効果的に使用されていない場合は、結果を制限する結果のパフォーマンスの利点を相殺する可能性があるため、
と組み合わせて使用することに注意してください。 ORDER BY LIMIT

シンプルで効果的なツールはクエリ分析です。分析を有効にすることで、クエリの実行時間のより正確な推定値が可能になります。これは2段階のプロセスです。最初に、分析を有効にします。

データベースに次の挿入操作が存在すると仮定します（およびユーザー1とギャラリー1が作成されていると仮定します）：show profiles

少量のデータは問題を引き起こしませんが、簡単な分析に使用できます。次のクエリを検討してください

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

ログイン後にコピー

写真エントリが多い場合、このクエリは将来問題になる可能性があります。

このクエリの正確な実行時間を取得するには、次のSQLを使用できます。

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

ログイン後にコピー

結果は次のとおりです

show profiles;コマンドは、元のクエリの時間だけでなく、他のすべてのクエリの時間も表示されるため、クエリを正確に分析できるようにします。

クエリを改善する方法は？

SQLの知識に頼って改善するか、MySQLのEXPLAINコマンドに依存して、実際の情報に基づいてクエリパフォーマンスを改善できます。

EXPLAINは、クエリ実行計画を取得するために使用されます。つまり、MySQLがクエリを実行する方法です。これは、オプティマイザーによるステートメント実行計画に関する情報とSELECT、DELETE、INSERT、REPLACE> UPDATE>>>>>>>>> <公式のドキュメントでは、EXPLAINどのように

どのように役立つかについて説明しています：

EXPLAINでは、インデックスを使用して行を使用して行を見つけることでステートメントがより速く実行できるように、インデックスを追加するテーブルを確認できます。また、EXPLAINを使用して、オプティマイザーがテーブルに最適な順序で結合しているかどうかを確認することもできます。

EXPLAINの使用を説明するための例を示すために、クエリを使用してユーザーメールをUserManager.php：

で見つけます。

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

ログイン後にコピー

EXPLAINコマンドを使用するには、SELECTタイプクエリの前に追加するだけです：

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

ログイン後にコピー

結果は次のとおりです（すべてを見るために右スクロール）：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	const	UNIQ_1483A5E9E7927C74	UNIQ_1483A5E9E7927C74	182	const	1	100.00	NULL

これらの結果は最初に理解するのが簡単ではありません。それぞれを詳しく見てみましょう。

id：これは、各クエリのシーケンシャル識別子です。 SELECT
：select_typeクエリのタイプ。このフィールドは複数の異なる値をとることができるため、最も重要な値に焦点を当てます：SELECT
- ：サブクエリや組合のない簡単なクエリSIMPLE
- ：PRIMARY接続の最も外側のクエリにありますselect
- ：DERIVEDは中性子クエリの一部selectです from
- SUBQUERY：selectは、連合の2番目またはその後の声明です。
- UNION select select_type
：このフィールドは、MySQL接続で使用されるテーブルのタイプを表します。これはおそらく、table出力で最も重要な分野です。欠落しているインデックスを示したり、クエリがどのようにオーバーライドされたりするかを示すことができます。このフィールドの可能な値は次のとおりです（ベストから最悪のタイプから最悪のタイプまで並べ替えられます）：
type：テーブルにはゼロ行または1列があります。 EXPLAIN
- ：テーブルには行に一致する行が1つだけで、行がインデックス付けされています。これが最速の接続タイプです。 system
- ：インデックスのすべての部分は結合で使用され、インデックスはconstまたは
- eq_refPRIMARY_KEY：前のテーブルからの各行の組み合わせについて、インデックス列のすべての一致する行が読み取られます。このタイプの結合は通常、UNIQUE NOT NULLまたは演算子を使用して比較されたインデックス付き列で発生します。
- ref：テーブルの全文インデックスに参加します。 =
- ：fulltextと同じですが、列の
- ref_or_nullref：接続はインデックスリストを使用して結果セットを生成します。 NULLの列には、使用されるキーが含まれます。
- index_merge：EXPLAINsubqueryは、テーブルから1つの結果のみを返し、主キーを使用します。 KEY
- ：インデックスを使用して、特定の範囲内で一致する行を見つけます。 unique_subquery IN
- range
- index
- ALL：MySQLがテーブルから行を見つけるために使用できるキーを表示します。これらのキーは、実際に使用される場合と使用できない場合があります。
にリストされていないが、より良いキーが見つかる場合があります。 possible_keys
：クエリオプティマイザーで使用されるインデックスの長さを示します。 keys possible_keys
列の指定されたインデックスと比較される列または定数を表示します。
rows：チェックされたレコードの数をリストして出力を生成します。これは非常に重要な指標です。
Extra：他の情報が含まれています。この列のUsing filesortまたはUsing temporary等価物は、問題のクエリを示している場合があります。

EXPLAIN出力形式の完全なドキュメントは、公式のMySQLページにあります。

シンプルなクエリに戻る：SIMPLEタイプの接続を備えたselectタイプconstです。これは私たちが持っているかもしれない最高のクエリケースです。しかし、より大きく複雑なクエリが必要な場合はどうなりますか？

アプリケーションモードに戻ると、すべてのギャラリー画像を取得する必要があります。また、説明に「猫」という言葉のある写真のみを含めたいかもしれません。これは間違いなくプロジェクトの要件で見つけることができる状況です。クエリを見てみましょう：

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

ログイン後にコピー

このより複雑な状況では、分析するには

でより多くの情報を取得する必要があります。 EXPLAIN

これにより、次の結果が得られます（すべてのセルを確認するために右スクロール）：

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

ログイン後にコピー

詳細を確認して、クエリで何を改善できるか見てみましょう。

前述のように、最初に表示する必要があるメイン列は、

列とtype列です。目標は、rows列でより良い値を取得し、type列の値を最小化することです。 rows

最初のクエリの結果は

であり、これはまったく良い結果ではありません。これは、私たちがそれを改善できるかもしれないことを意味します。 index

クエリを見ると、それを解決する方法は2つあります。まず、

テーブルは使用されません。クエリを拡張して、ユーザーをターゲットにしていることを確認するか、クエリのユーザー部分を完全に削除する必要があります。全体的なパフォーマンスの複雑さと時間を増やすだけです。 Users

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

ログイン後にコピー

それで、まったく同じ結果が得られます。見てみましょう

：EXPLAIN

私たちに残されているのはALLタイプです。 ALLはおそらく最悪の種類の接続ですが、それが唯一のオプションである場合があります。要件に応じて、すべてのギャラリー画像が必要なため、galleriesテーブル全体を検索する必要があります。テーブル内のすべての情報が必要な場合、テーブル内で特定の情報を見つけようとすると、インデックスが優れていますが、それらは私たちを助けません。この状況に遭遇したとき、キャッシュなどの他の方法に頼らなければなりません。

LIKEに取り組んでいるため、最後に改善できるのは、descriptionフィールドに全文インデックスを追加することです。このようにして、LIKEをmatch()に変更し、パフォーマンスを向上させることができます。フルテキストインデックスの詳細については、こちらをご覧ください。

また、2つの非常に興味深い状況を確認する必要があります。アプリケーションの最新および関連する機能です。これらはギャラリーに適用され、注意すべき極端な状況を伴います。

上記は関連ギャラリーです。

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

ログイン後にコピー

上記は最新のギャラリーです。

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

ログイン後にコピー

一見すると、これらのクエリは

を使用するため、非常に高速でなければなりません。これは、ほとんどのクエリで

を使用している場合です。残念ながら、当社と当社のアプリケーションにとって、これらのクエリはLIMITも使用しています。クエリを制限する前にすべての結果を並べ替える必要があるため、LIMITを使用するという利点が失われます。 ORDER BY LIMIT

はトリッキーである可能性があることがわかっているので、信頼できる

を適用しましょう。 ORDER BY EXPLAIN

および

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	IDX_F70E6EB7A76ED395	NULL	NULL	NULL	1	100.00	Using where; Using filesort
1	SIMPLE	u	NULL	eq_ref	PRIMARY,UNIQ_1483A5E9BF396750	PRIMARY	108	homestead.gal.id	1	100.00	NULL

両方のクエリで、最悪の接続タイプがあることを見ることができます：ALL。

歴史的に、MySQLの実装は、特にORDER BYで使用される場合、MySQLのパフォーマンスの問題の原因でした。この組み合わせは、大規模なデータセットを備えたほとんどのインタラクティブなアプリケーションでも使用されています。新しい登録ユーザーや人気のあるタグなどの機能は、この組み合わせをよく使用します。 LIMIT

これは一般的な問題であるため、パフォーマンスの問題を解決するためにいくつかの一般的なソリューションを適用する必要があります。

は良い候補です。このようにして、完全な結果セットをスキャンしてソートすることなく、created_atおよびORDER BYを行うことができます。 LIMIT
が結合順序の最初のテーブルではないフィールドでソートされている場合、インデックスは使用できません。 ORDER BY
がインデックスを使用することはできません。 ORDER BY
の大きな値に注意を払ってください。大規模な値は、より多くの行を並べ替えるように強制されます。これはパフォーマンスに影響します。 LIMIT LIMIT ORDER BY

の両方を使用する場合、これらはパフォーマンスの問題を最小限に抑えるために必要な測定値の一部です。

LIMITORDER BY結論

私たちが見たように、は、できるだけ早くクエリの問題を識別するのに非常に役立ちます。私たちのアプリケーションが生産されているときにのみ注目される多くの問題があり、データベースにアクセスするために多くのデータまたは多くの訪問者があります。これらの問題をできるだけ早く検出するために

を使用できる場合、将来のパフォーマンスの問題の可能性ははるかに小さくなります。

EXPLAINアプリケーションには必要なすべてのインデックスがあり、高速ですが、パフォーマンスの向上を確認する必要があるときはいつでも、いつでもEXPLAINとインデックスに頼ることができることがわかります。

MySQLパフォーマンスインデックス（FAQ）

EXPLAINに関するFAQ

MySQLパフォーマンスインデックスの重要性は何ですか？

データベースパフォーマンスを最適化するには、MySQLパフォーマンスインデックスが重要です。インデックス付き列の値に基づいて、データテーブルの行に迅速にアクセスすることにより、データ検索操作を大幅に高速化します。インデックスがなければ、MySQLは、特に大きなデータベースでは、非常に時間がかかる可能性のある関連する行を見つけるために、テーブル内のすべての行を通過する必要があります。

説明コマンドはMySQLのパフォーマンスを改善するのにどのように役立ちますか？

mysqlの

コマンドは、mysqlがクエリを実行する方法に関する情報を提供する強力なツールです。読み取りテーブルの順序、実行される読み取り操作のタイプ、選択できるインデックス、およびチェックする行の推定数を示します。この情報は、開発者がクエリを最適化し、データベースのパフォーマンスを改善するのに役立ちます。

なぜMySQLが可能なキーを使用しないのですか？

MySQLは、いくつかの理由で可能なキーを使用しません。理由の1つは、オプティマイザーがインデックスを使用するにはテーブルのほとんどのスキャンが必要であり、テーブルスキャンがより速くなると判断することを推定することです。もう1つの理由は、WHERE句の列がインデックスの列と一致しないためかもしれません。

mysqlクエリを最適化する方法は？

mysqlクエリを最適化する方法はいくつかあります。 1つの方法は、インデックスを効果的に使用することです。インデックスは、データの取得を大幅に高速化できます。ただし、INSERT、UPDATE、DELETEなどのデータ変更操作が遅くなります。したがって、バランスポイントを見つけることが非常に重要です。もう1つの方法は、EXPLAINコマンドを使用して、MySQLがクエリを実行し、潜在的なボトルネックを見つける方法を理解することです。

mysqlの主要なキーとインデックスの違いは何ですか？

MySQLの主キーはインデックスです。主キーは、テーブル内の行の一意の識別子です。列または列の組み合わせの一意性を強制し、列または列の組み合わせにNULL値が含まれていないことを保証します。一方、インデックスは、データ検索操作の速度を上げることができるデータ構造です。列または列の組み合わせに適用できます。

mysqlでインデックスを作成する方法は？

CREATE INDEXステートメントを使用して、MySQLにインデックスを作成できます。構文は次のとおりです。これにより、指定されたテーブルの指定された列にインデックスが作成されます。 CREATE INDEX index_name ON table_name (column1, column2, …);

mysqlの複合インデックスは何ですか？

マルチコラムインデックスとも呼ばれる

複合インデックスは、複数の列を含むインデックスです。 MySQLでは、複合インデックスには最大16列を含めることができますが、インデックス付き列の合計サイズは767バイトを超えることはできません。

mysqlでインデックスを削除する方法は？

ステートメントを使用して、MySQLのインデックスを削除できます。構文は次のとおりです。これにより、指定されたテーブルから指定されたインデックスが削除されます。 DROP INDEX DROP INDEX index_name ON table_name; MySQLのクラスターインデックスと非クラスター化されたインデックスの違いは何ですか？