MySQLデータベースのパフォーマンス最適化を解析する6つのテクニック
4067 ワード
データベース・テーブルの表面にはインデックスとエラー防止メカニズムがありますが、簡単なクエリーでは時間がかかります.Webアプリケーションは開発環境でよく機能しているかもしれませんが、製品環境では同じように悪いです.データベース管理者であれば、ある段階で上記の状況に遭遇した可能性があります.そこで、MySQLのパフォーマンスを最適化するためのテクニックとコツを紹介します.
1.ストレージエンジンの選択データテーブルにトランザクションが必要な場合は、ACID特性に完全に合致するため、InnoDBを使用することを考慮する必要があります.トランザクションが不要な場合は、デフォルトのストレージエンジンMyISAMを使用するのが賢明です.この2つのストレージエンジンを同時に使用しないでください.考えてみてください.1つのトランザクションでは、一部のデータテーブルはInnoDBを使用し、残りはMyISAMを使用します.結果は?subject全体がキャンセルされ、トランザクション内のデータのみが元の状態に戻され、残りのコミットされたデータが転送され、データベース全体の競合が発生します.しかしながら、2つのストレージエンジンの利点を同時に利用できる簡単な方法がある.現在、ほとんどのMySQLスイートにはInnoDB、コンパイラ、チェーンテーブルが含まれていますが、MyISAMを選択すると、InnoDBを単独でダウンロードし、プラグインとして使用することができます.簡単な方法ですね.
2.カウントの問題データテーブルで使用されているストレージエンジンがInnoDBなどのトランザクションをサポートしている場合は、COUNT(*)を使用してデータテーブルのロー数を計算する必要はありません.これは、製品クラスデータベースでCOUNT(*)を使用しているため、特定の時間にトランザクションが常に実行されているため、最大1つの近似値が返されます.COUNT(*)を使用すると明らかにバグが発生し、このようなエラー結果が発生します.
3.クエリーの繰り返しテストで最も厄介な問題は、どんなに注意してもエラーが発生し、バグが発生することではありません.逆に、問題は、ほとんどの場合、バグが発生したときにアプリケーションまたはデータベースがオンラインになったことです.テストサンプルをアプリケーションまたはデータベース上で実行しない限り、この問題に対する確実な解決策は存在しません.データベース・クエリーは、数千のレコードの大量のサンプル・テストを経てのみ承認されます.
4.全テーブルスキャンを回避通常、MySQL(または他のリレーショナル・データベース・モデル)がデータ・テーブルで特定のレコードを検索またはスキャンする必要がある場合、全テーブル・スキャンが使用されます.さらに、通常最も簡単な方法は、インデックステーブルを使用して、テーブル全体のスキャンによる低効率の問題を解決することです.しかし、その後の問題で見たように、これは誤った部分があります.
5.「EXPLAIN」を使用してクエリーを行います.デバッグが必要な場合、EXPLAINは良いコマンドです.以下、EXPLAINについて深く検討します.
まず、簡単なデータテーブルを作成します.
このデータテーブルは一目瞭然で、5列あり、最後の列「deleted」はBooleanクラス変数flagで、アカウントがアクティブか削除されたかをチェックします.次に、このテーブル(従業員100人など)にサンプルレコードを入力する必要があります.ご覧のように、メインキーは「emp_id」です.したがって、Eメールアドレスとパスワードフィールドを使用すると、次のようなログイン要求を検証または拒否するためのクエリーを簡単に作成できます(インスタンス1).
前述したように、COUNT(*)の使用は避けましょう.コード修正は次のとおりです(例2).
次に、インスタンス1では、コードクエリが「email_id」と「password」が所定の値に等しい行数を位置決めして返すことを思い出す.インスタンス2では、「emp_id」が与えられた基準を満たすすべての値を明確にリストする必要があるのとは異なり、同様のクエリが行われる.どのクエリーがもっと時間がかかりますか?
この2つのインスタンスは、無意識に2つのインスタンスクエリが全テーブルスキャンされたため、同じ時間のデータベースクエリであることは明らかです.命令をよりよく理解するために、次のコードを実行します.
出力時には、最後から2番目の列「rows」に集中します.テーブルに100個のレコードが埋め込まれていると仮定すると、最初のローに100が表示されます.これは、MySQLがクエリーの結果を計算するためにスキャンする必要があるローの数です.これは何を説明していますか.これは全テーブルスキャンが必要です.この弊害を克服するためには、インデックスを追加する必要があります.
6.インデックスを追加するには、まず重要なことから言えば、発生する可能性のある副次的な問題ごとにインデックスを作成するのは賢明ではありません.インデックスが多すぎると、効率が低下し、リソースが消費されます.さらに説明する前に、インスタンスにサンプルインデックスを作成します.
次に、クエリーを再度実行します.
運転後の値に注意してください.100ではなく、1です.したがって、クエリの結果を示すために、MySQLは以前に作成したインデックスのおかげで1行しかスキャンされませんでした.インデックスはEメールアドレスフィールドのみで作成され、クエリーは他のフィールドも検索されていることに気づくかもしれません.これはMySQLが先にcros-checkを実行し、WHERE句で定義された値にインデックス指定があるかどうかを確認し、そのような値があれば対応する操作を実行することを示しています.
しかし、繰り返すたびに1つに減少するわけではありません.たとえば、employee names列のような一意のインデックスフィールドでない場合、インデックスを作成しても複数のレコードが残ります.しかし、全表スキャンよりも優れています.また,WHERE句で指定された列の順序はこの過程では機能しない.たとえば、上記のクエリでフィールドの順序を変更して最後にEメールアドレスを表示すると、MySQLはインデックス列を巡回します.では、インデックスに頭を悩ませ、大量の全テーブルスキャンを回避し、より良い結果を得る方法に注意します.しかし、これは長い過程を経験する必要があります.
1.ストレージエンジンの選択データテーブルにトランザクションが必要な場合は、ACID特性に完全に合致するため、InnoDBを使用することを考慮する必要があります.トランザクションが不要な場合は、デフォルトのストレージエンジンMyISAMを使用するのが賢明です.この2つのストレージエンジンを同時に使用しないでください.考えてみてください.1つのトランザクションでは、一部のデータテーブルはInnoDBを使用し、残りはMyISAMを使用します.結果は?subject全体がキャンセルされ、トランザクション内のデータのみが元の状態に戻され、残りのコミットされたデータが転送され、データベース全体の競合が発生します.しかしながら、2つのストレージエンジンの利点を同時に利用できる簡単な方法がある.現在、ほとんどのMySQLスイートにはInnoDB、コンパイラ、チェーンテーブルが含まれていますが、MyISAMを選択すると、InnoDBを単独でダウンロードし、プラグインとして使用することができます.簡単な方法ですね.
2.カウントの問題データテーブルで使用されているストレージエンジンがInnoDBなどのトランザクションをサポートしている場合は、COUNT(*)を使用してデータテーブルのロー数を計算する必要はありません.これは、製品クラスデータベースでCOUNT(*)を使用しているため、特定の時間にトランザクションが常に実行されているため、最大1つの近似値が返されます.COUNT(*)を使用すると明らかにバグが発生し、このようなエラー結果が発生します.
3.クエリーの繰り返しテストで最も厄介な問題は、どんなに注意してもエラーが発生し、バグが発生することではありません.逆に、問題は、ほとんどの場合、バグが発生したときにアプリケーションまたはデータベースがオンラインになったことです.テストサンプルをアプリケーションまたはデータベース上で実行しない限り、この問題に対する確実な解決策は存在しません.データベース・クエリーは、数千のレコードの大量のサンプル・テストを経てのみ承認されます.
4.全テーブルスキャンを回避通常、MySQL(または他のリレーショナル・データベース・モデル)がデータ・テーブルで特定のレコードを検索またはスキャンする必要がある場合、全テーブル・スキャンが使用されます.さらに、通常最も簡単な方法は、インデックステーブルを使用して、テーブル全体のスキャンによる低効率の問題を解決することです.しかし、その後の問題で見たように、これは誤った部分があります.
5.「EXPLAIN」を使用してクエリーを行います.デバッグが必要な場合、EXPLAINは良いコマンドです.以下、EXPLAINについて深く検討します.
まず、簡単なデータテーブルを作成します.
CREATETABLE'awesome_pcq'(
'emp_id'INT(10)NOTNULL
DEFAULT'0',
'full_name'VARCHAR(100)NOTNULL,
'email_id'VARCHAR(100)NOTNULL,
'password'VARCHAR(50)NOTNULL,
'deleted'TINYINT(4)NOTNULL,
PRIMARYKEY('emp_id')
) COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
このデータテーブルは一目瞭然で、5列あり、最後の列「deleted」はBooleanクラス変数flagで、アカウントがアクティブか削除されたかをチェックします.次に、このテーブル(従業員100人など)にサンプルレコードを入力する必要があります.ご覧のように、メインキーは「emp_id」です.したがって、Eメールアドレスとパスワードフィールドを使用すると、次のようなログイン要求を検証または拒否するためのクエリーを簡単に作成できます(インスタンス1).
SELECTCOUNT(*)FROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0
前述したように、COUNT(*)の使用は避けましょう.コード修正は次のとおりです(例2).
SELECTemp_idFROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0
次に、インスタンス1では、コードクエリが「email_id」と「password」が所定の値に等しい行数を位置決めして返すことを思い出す.インスタンス2では、「emp_id」が与えられた基準を満たすすべての値を明確にリストする必要があるのとは異なり、同様のクエリが行われる.どのクエリーがもっと時間がかかりますか?
この2つのインスタンスは、無意識に2つのインスタンスクエリが全テーブルスキャンされたため、同じ時間のデータベースクエリであることは明らかです.命令をよりよく理解するために、次のコードを実行します.
EXPLAINSELECTemp_idFROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0
出力時には、最後から2番目の列「rows」に集中します.テーブルに100個のレコードが埋め込まれていると仮定すると、最初のローに100が表示されます.これは、MySQLがクエリーの結果を計算するためにスキャンする必要があるローの数です.これは何を説明していますか.これは全テーブルスキャンが必要です.この弊害を克服するためには、インデックスを追加する必要があります.
6.インデックスを追加するには、まず重要なことから言えば、発生する可能性のある副次的な問題ごとにインデックスを作成するのは賢明ではありません.インデックスが多すぎると、効率が低下し、リソースが消費されます.さらに説明する前に、インスタンスにサンプルインデックスを作成します.
ALTERTABLE'awesome_pcq'ADDINDEX'LoginValidate'('email_id')
次に、クエリーを再度実行します.
EXPLAINSELECTemp_idFROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0
運転後の値に注意してください.100ではなく、1です.したがって、クエリの結果を示すために、MySQLは以前に作成したインデックスのおかげで1行しかスキャンされませんでした.インデックスはEメールアドレスフィールドのみで作成され、クエリーは他のフィールドも検索されていることに気づくかもしれません.これはMySQLが先にcros-checkを実行し、WHERE句で定義された値にインデックス指定があるかどうかを確認し、そのような値があれば対応する操作を実行することを示しています.
しかし、繰り返すたびに1つに減少するわけではありません.たとえば、employee names列のような一意のインデックスフィールドでない場合、インデックスを作成しても複数のレコードが残ります.しかし、全表スキャンよりも優れています.また,WHERE句で指定された列の順序はこの過程では機能しない.たとえば、上記のクエリでフィールドの順序を変更して最後にEメールアドレスを表示すると、MySQLはインデックス列を巡回します.では、インデックスに頭を悩ませ、大量の全テーブルスキャンを回避し、より良い結果を得る方法に注意します.しかし、これは長い過程を経験する必要があります.