MySQL のパフォーマンス検査と最適化の方法

1. インデックスが構築されていません。
3. マシンが負荷に耐えられません。はビルドされていません
Mysql が CPU を大量に消費していると表示される場合は、mysql クライアント ツールを使用して確認できます。
Linux で実行します
/usr/local/mysql/bin/mysql -hlocalhost -uroot -p
パスワードを入力してください。パスワードがない場合は、-p パラメータを指定せずにクライアント インターフェイスに入ることができます。
現在の実行ステータスを確認します
show full processlist
複数回実行できます
このコマンドは、現在実行中の SQL ステートメントを確認できます。実行された SQL、データベース名、および実行内容が通知されます。ステータス、クライアント IP、使用されているアカウント、実行時間、その他の情報
私のキャッシュ バックエンドでは、ほとんどの場合、SQL ステートメントが表示されませんが、これは比較的正常だと思います。多くの SQL ステートメントが表示される場合、この mysql にはパフォーマンスの問題があるはずです
パフォーマンスの問題が発生した場合は、次のことを分析できます。
1. スタックしている SQL ステートメントはありますか?
これ 多くの状況があります。データベースが myisam を使用している場合、データ テーブルをロックする書き込みスレッドが存在する可能性があります。このステートメントが終了しないと、他のステートメントは実行できません。
プロセスリストの時間項目をチェックして、実行に時間がかかるステートメントがあるかどうかを確認します。これらのステートメントに注意してください。
2. 同じ SQL 文が大量に実行されている
この場合は、SQL 文の実行効率が低い可能性がありますので、注意してください。
次に、疑わしいステートメントをすべて収集し、desc(explain) を使用してこれらのステートメントを確認します。
まず通常の desc 出力を見てみましょう:
mysql> desc select * from imgs where imgid=1651768337; ---- ------------- --- - --- ------- --------------- --------- --------- ------ - ------ -------
| キーの種類 | 追加の行 | ------- ------- ------- --------------- --------- ---- - ---- ------- -------
| プライマリ | ---- ------------- ------- ------- --------------- --- - ----- --------- ------- ------ -------
セット内の 1 行 (0.00 秒)
音符キーこのステートメントによって返される結果は、SQL がクエリに PRIMARY 主キー インデックスを使用することを示しています。Extra は表示されません。これは、並べ替えやその他の操作が行われていないことを示しています。使用済み。この結果から、mysql がインデックスからレコード imgid=1651768337 をクエリし、実際のテーブルからすべてのフィールドを取得することが推測できます。これは非常に単純な操作です。
key は、現在の SQL で使用されるインデックスを示します。Mysql は、単純なステートメントを実行するときに 1 つのインデックスのみを使用できます。行数は返される結果セットのサイズであり、結果セットはすべてです。検索にこのインデックスを使用して一致します。結果は通常、クエリと並べ替え方法を表示します。
キーが使用されない場合、または行が非常に大きくファイルソートが使用される場合、一般に効率が影響を受けます。例:
mysql> desc select * from imgs where userid="7mini" order by clicks説明制限 10
---- ------------- ------- ------ ------------- -- -- ---- --------- ------ ------- --------- ------
| テーブルのタイプ |
の行 | ----- - ------ ------ --------------- ------ --------- -- ---- -- ----------------------------------
| 画像 | | ALL | NULL | NULL | ファイルソートの使用 | - ----- ---------- ------ --------- ------ ------- ------ ---- ------------------
セット内の 1 行 (0.00 秒)
この SQL 結果セットには 12506 個のエントリがあるため、ファイルソートが使用されます。効率的に実行するには非常にコストがかかります。このとき、mysql が実行されると、テーブル全体をスキャンして userid="7mini" に一致するレコードを 1 つずつ見つけて、これらのレコードのクリックをソートします。その効率は想像できます。実際の実行中に比較的高速であることが判明した場合、それはサーバー メモリが 12506 個の比較的短いレコードをすべてメモリに読み込むのに十分であるため、それでも比較的高速であるためです。ただし、同時実行性が増加したり、テーブルが大きくなったりすると、効率の問題が深刻になるだろう。
この時点で、インデックスにユーザー ID を追加しました:
create Index userid on imgs (userid);
次に、もう一度確認します:
mysql> desc select * from imgs where userid="7mini" order byクリック数制限
---- ------------- ------- ------ ------------ -- - -------- --------- ------- ------ ------ -- ----------
| テーブルのタイプ | キー長 | -- ---- ------- ------ --------------- -------- -------- -- - ------ ------ ------------------------
| 1 | | ref | userid | 8 | ファイルソートの使用 | -- --------------- -------- --------- ------- ------ --- ------------------------
セット内の 1 行 (0.00 秒)
まあ、mysql が次の場所で使用されていることがわかります。今回は userid インデックスが検索されました。 userid インデックスを使用した検索の後、結果セットには 8 つの結果がありました。filesort を使用して 1 つずつ並べ替えましたが、結果セットには 8 項目しか含まれていなかったため、効率の問題は軽減されました。
ただし、別のユーザー ID でクエリを実行すると、結果は異なります:
mysql> desc select * from imgs where userid="admin" order by clicks desc limit 10; ---- -- ----------- ------- ------ --------------- -------- - -- ------ ------ ------ -----------------------------
| 選択タイプ | キー長 | - ------ --------------- ------ ------ ------ ---- -----------------------------
| イメージ ref | 51 | 2944 | where の使用
---- ------------- ------- -------- ------- -------- --------- ------- ------ ------------- ----------------
セット内の 1 行 (0.00 秒)
この結果は基本的に userid="7mini" の結果と同じですが、mysql は userid を使用します一度検索するインデックス 最終的な結果セットのサイズは 2944 に達します。これらの 2944 レコードはファイルソート用のメモリに追加されます。効率は 7mini よりもはるかに悪くなります。この問題を解決するには 2 つの方法があります。1 つ目は、クリック数に基づいて最大 10 個のデータを取得する必要があるため、大量のデータを取得する必要があるためです。並べ替えに追加する必要はまったくありません。たとえば、クリック数が 10 未満の場合は、これらのデータが大きな部分を占める可能性があります。
クリック数にインデックスを追加し、where 条件を追加して再度クエリします。
create Index clicks on imgs(clicks);
mysql> desc select * from imgs where userid="admin" order by clicks;説明制限 10
---- ------------- ------- ------ ------------- - - ------ ------ ------ ------ ------ ------
| テーブルのタイプ | キー長 | - ---- ------- ------ --------------- -------- --------- - - ------ ------ ------------------------
| 1 | ref | userid,clicks | 2944 | ファイルソートの使用 | --- --------------- -------- --------- ------- ------ - - -------------------------
セット内の 1 行 (0.00 秒)
この時点で、 possible_keys がUserid、clicks、および possible_keys はすべて、一致する可能性のあるインデックスです。MySQL は、ステートメントを実行するために、 possible_keys のインデックスの 1 つを判断して使用します。MySQL によって使用されるインデックスは最適化されていない可能性があることに注意してください。今回は、mysql が userid インデックスを使用してクエリされましたが、私の希望に従わなかったため、結果は変わりませんでした。 SQL を変更し、mysql でクリック数インデックスを使用するように use Index を追加します:
mysql> desc select * from imgs use Index (clicks) where userid='admin' and clicks>10 order by clicks desc limit 10
- -- -------------- ------- ------- --------------- ----- -- - --------- ------ ------ -------------
| テーブルの種類 | | キー | 参照行 | ---- ------------- ------- -- -------- -------- --------- ------ ------ ----------- --
| クリック数 | 5455 | -- -- ------- --------------- -------- --------- ------ - -- ----------------
セット内の 1 行 (0.00 秒)
この時点で、mysql はクエリにクリック インデックスを使用しますが、結果セットはより大きくなりますユーザー ID よりも制限する必要があります:
mysql> desc select * from imgs use Index (clicks) where userid='admin' and clicks>1000 order by clicks desc limit 10
---- ----- -------- ------- ------- --------------- -------- ----- ---- ------ ------ -------------
| 可能なキーの種類 | key_len | 追加の行 | -------- ------------- ------- -------- ----- -- -------- --------- ------ ------ ------------- セット内の 1 行 (0.00 秒)
1000 に追加すると、結果セットは 312 になり、ソート効率は許容範囲内になります。
ただし、インデックス変更最適化手法を使用するには、この例では数値 1000 などのサンプリング ポイントを取得する必要があり、この方法では userid の値ごとにサンプリング ポイントを見つける必要があり、これがプログラムにとって問題となります。非常に扱いが難しい。サンプリングが 1000 に基づいている場合、userid='7mini' の例では、得られる結果は 8 ではなく 2 となり、ユーザーは混乱します。
もちろん、二重インデックスを追加する別の方法もあります:
create Index userid_clicks on imgs (userid, clicks)
mysql> desc select * from imgs where userid="admin" order by clicks desc limit 10 ;
---- ------------- ------- ------ ---------------- - ----- --------------- ------ ------ ------ ------ - -----
| テーブルのタイプ | キー長 | ---- ------ ------ ----------------------------- --------------- - -------- ------- ------ -------------
| ユーザーID、ユーザーID_クリック | | userid_clicks | 2944 | ここでの使用法 -- ------------- ------- ----- - --------------- --------------- ------ - ------ -- ---- -------------
セットに 1 行 (0.00 秒)
この時点では、結果セットにはまだ 2944 個のエントリがあることがわかりますが、ファイルソートは余分なものがありません。現時点では、mysql は userid_clicks インデックスを使用してクエリを実行し、userid="admin" を持つすべてのレコードを迅速にクエリできるだけでなく、結果はクリック数に基づいて並べ替えられるため、この結果セットをメモリに読み込む必要はありません。一つずつ整理すると効率が上がります。
しかし、SQL クエリの種類が多い場合は、複数フィールドのインデックスを使用する際に問題が発生します。そうしないと、大量のインデックスが構築され、データの効率に影響を与えるだけではありません。挿入と更新だけでなく、データ テーブルも損傷を受ける傾向があります。
上記はインデックスを最適化する方法です。理由はより複雑になる可能性があるため、書くのに時間がかかります。一般に、インデックスを最適化すると、mysql の効率が n レベル向上します。問題を解決するには、さらにマシンを追加することを検討する必要があります。
ただし、mysql やすべてのデータベースでさえ、制限の問題を解決できない場合があります。 mysql では、インデックスが適切である限り制限 0 と 10 は問題ありませんが、制限 100000 と 10 は非常に遅くなります。これは、mysql がソートされた結果をスキャンしてから 100000 点を見つけ、10 レコードを取り出して返すためです。彼ら。 100,000 ポイントを見つけるには、100,000 レコードをスキャンする必要があります。このサイクルには比較的時間がかかります。このスキャン エンジンを最適化できる良いアルゴリズムがあるかどうか、一生懸命考えましたが、良い方法が思いつきません。制限に関しては、現在から比較的遠い将来まで、ビジネス、プログラム、データ テーブルの計画を通じてのみ最適化できると思います。私が考えた最適化方法はどれも確実な戦略ではありません。これについては後で説明します。
2. SQL の記述方法が複雑すぎる
SQL の記述方法に groupby や複数テーブルの結合クエリなどの特殊な関数が使用されている場合、mysql のクエリにはどのようなメソッドが使用されますか?複雑な SQL のケースはあまりないので、あまり分析しませんし、今のところ良い提案はありません。
3. 設定エラー
設定の主なパラメータは key_buffer、sort_buffer_size/myisam_sort_buffer_size です。これら 2 つのパラメータの意味は次のとおりです:
key_buffer=128M: すべてのテーブルのインデックスがこのメモリ領域に配置されます。インデックスが比較的大きい場合は、通常は 128M に設定して開くことができます。これにより、メモリを大幅に減らすことができます。 MySQL が占有されています。
sort_buffer_size=1M: 単一スレッドがソートに使用するメモリがこのメモリに格納されます。これより小さい場合、mysql はさらに数倍多くのメモリを格納します。重要なのは、大きすぎる結果セットを生成しないように、適切なインデックスとクエリ ステートメントを最適化することです。
その他の構成:
thread_concurrency=8: この構成には、CPU 数 x 2 が標準で付属しています。
interactive_timeout=30
wait_timeout=30: これら 2 つの構成は、10 ～ 30 秒間使用できます。これにより、メモリ リソースができるだけ早く解放されます。 注: この構成では、長期間アクティブでなかった接続のみが切断されます。
query_cache: この関数は使用しないでください。現在、多くの人が文字キャッシュを宝物を見ているかのように見ています。これは物質主義的ではありません。 MySQL の query_cache は、テーブルのデータが変更されるたびにテーブルに接続されているすべてのキャッシュを再クリアします。更新が頻繁に行われる場合、query_cache は役に立たないだけでなく、効率にも大きな影響を与えます。このパラメーターは読み取り専用データベースにのみ適しています。使用する必要がある場合は、query_cache_type=2 を使用して、SQL_CACHE を使用したキャッシュ用の SQL を指定することのみが可能です。
max_connections: デフォルトは 100 で、通常はこれで十分ですが、通常はより高く設定する必要があります。600 を超えると、一般に効率の問題が発生するため、他の対策を見つける必要があります。この数値を増やすだけでは解決策はありません。
個人的には、問題はそれほど大きくないと思います: 1. 構成は非常に重要ですが、ほとんどの場合、それは効率の問題の原因ではありません。 2. MySQL はデータベースです。データベースに関して考慮すべき最も重要なことは、効率ではなく、安定性とデータの正確さです。
4. マシンが本当に負荷に耐えられない
上記の調整を行ってもサーバーがまだ負荷に耐えられない場合は、アーキテクチャ レベルの調整を通じてのみ最適化することができます。
1.mysqlの同期。
mysql 同期関数を通じて複数のスレーブ データベースにデータを同期し、マスター データベースはスレーブ データベースに対して書き込みと読み取りを行います。
個人的には、mysql 同期を使用することにあまり満足していません。この方法を使用するとプログラムが複雑になり、データ エラーが頻繁に発生するためです。高負荷なサービスでは、クラッシュしてもすぐに再起動できますが、データが間違っている場合は復旧がさらに面倒になります。
2. キャッシュを追加します
キャッシュを追加すると、同時実行の問題を解決でき、その効果は明ら​​かです。リアルタイム システムの場合は、キャッシュを最新の状態に保つためにキャッシュを更新することを検討できます。
ヒット率の高いアプリケーションでは、基本的に問題を解決できる Squid をフロントエンド アーキテクチャに追加することをお勧めします。
キャッシュがプログラム ロジック層で行われる場合、非常に複雑になり、問題の解決がさらに難しくなります。このレベルで調整を行うことはお勧めできません。
3. 複数のデータベースへの同時接続をサポートするようにプログラム構造を調整します。
Web をキャッシュに追加しても問題が依然として深刻な場合、唯一の方法はプログラム構造を調整し、アプリケーションを解体して使用することです。同時にサービスを提供する複数のマシン。
解体された場合、ビジネスにわずかな影響を及ぼしますが、ビジネス内の一部の機能ですべてのデータを使用する必要がある場合は、1 つの完全なライブラリと n 個の分散ライブラリのアーキテクチャを使用できます。完全なライブラリと分散ライブラリ。一度実行するか、完全なライブラリを定期的に整理します。
もちろん、最も愚かな方法もあります。それは、データベースの完全なコピーを作成し、プログラムが毎回これらのライブラリで完全な SQL を実行し、アクセス中にアクセスをポーリングするというものだと思います。 mysql よりも同期性が高く安全です。
4. mysql プロキシを使用する
mysql プロキシは、プロキシを介してデータベース内のテーブルを複数のサーバーに分散できますが、人気のあるテーブルが複数のテーブルに分散している場合、問題は解決できません。という方法で比較的簡単に問題を解決できます。
私はこのソフトウェアを使用したことも、注意深く確認したこともありませんが、その機能について少し疑問があります。つまり、複数のテーブル間の結合クエリをどのように実装するのでしょうか?
>5. memcachedb を使用する
データベースを mysql をサポートする memcachedb に変更することは、実装方法と memcachedb のレベルの観点から、データに影響を与えず、問題を引き起こすことはありません。ユーザーへ。
データベースに関してはあまり問題がないので、このことはまだ試していません。ただし、MySQL の主要な構文のほとんどをサポートし、安定している限り、その使いやすさに疑いの余地はありません。