MySQL のパフォーマンス検査と最適化の方法_PHP チュートリアル

1. インデックスが構築されていない
2. SQL の記述方法が間違っている
4. インデックスが構築されていない。 mysql は CPU を大量に消費します。mysql クライアント ツールを使用して確認できます。
Linux で実行します
/usr/local/mysql/bin/mysql -hlocalhost -uroot -p
パスワードがない場合は、-p パラメータなしでクライアント インターフェイスに入ることができます。
現在の実行ステータスを確認します
完全なプロセスリストを表示します
複数回実行できます
このコマンドは、現在実行されているSQLステートメントを確認でき、実行されたSQL、データベース名、実行ステータス、使用されているクライアントIPを通知します。 、実行時間、その他の情報
私のキャッシュ バックエンドでは、ほとんどの場合、SQL ステートメントが表示されませんが、これは比較的正常だと思います。多くの SQL ステートメントが表示される場合、この mysql にはパフォーマンスの問題があると考えられます。パフォーマンスの問題が発生した場合は、次のことを分析できます。
これは、データベースが使用している場合によくある状況です。 myisam では、データ テーブルをロックする書き込みスレッドが存在する可能性があります。このステートメントが終了しないと、他のステートメントは実行できません。
processlist の time 項目をチェックして、実行に時間がかかるステートメントがあるかどうかを確認します。これらのステートメントに注意してください。
2.同じSQL文が大量に実行されている場合は、SQL文の実行効率が低い可能性があります。
次に、疑わしいステートメントをすべて収集し、desc(explain) を使用してこれらのステートメントを確認します。
まず通常の desc 出力を見てみましょう:
mysql> desc select * from imgs where imgid=1651768337;
+----+-------------+------ - +----------+----------+----------+----------+--- - ---+------+------+
| キーの種類 | キーの種類 | - -----------+----------+----------+---------------------+----- - ---+-------+------+------+
| 初期 |プライマリ |
+--------+-------+---- - ----------+--------+----------+------+------+--- - ---+
セット内の 1 行 (0.00 秒)
このステートメントによって返される結果は、SQL がクエリに PRIMARY 主キー インデックスを使用することを示しています。結果セットは 1 です。追加は表示されませんが、並べ替えやその他の操作が使用されていないことを示しています。この結果から、mysql がインデックスからレコード imgid=1651768337 をクエリし、実際のテーブルからすべてのフィールドを取得することが推測できます。これは非常に単純な操作です。
key は、現在の SQL で使用されるインデックスを示します。Mysql は、単純なステートメントを実行するときに 1 つのインデックスのみを使用できます。行数は返される結果セットのサイズであり、結果セットはすべて一致するものであることに注意してください。このインデックスを使用した検索結果には、通常、クエリと並べ替え方法が表示されます。
キーが使用されていない場合、または行が大きくファイルソートが使用されている場合は、一般に効率が影響を受けます。例:
mysql> desc select * from imgs where userid="7mini" order by clicks desc limit 10; +-- --+---------------+------+-----+------ +-- ----+-------+------+------+------ --- --------+ テーブル | キー長 |
--- --+----------+-----+------+-----+------ --- +-----+----------+----------------------------+
| 1 | NULL | 12506 を使用します。 --- +------+---------------+------+----------+----- -+-- -+--------------------------------+
セット内の 1 行 (0.00 sec)
この SQL の結果 アセンブリには 12506 個のエントリがあり、ファイルソートが使用されているため、実行は非常に非効率になります。このとき、mysql が実行されると、テーブル全体をスキャンして userid="7mini" に一致するレコードを 1 つずつ見つけて、これらのレコードのクリックをソートします。その効率は想像できます。実際の実行中に比較的高速であることが判明した場合は、サーバーのメモリが 12506 個の比較的短いレコードをすべてメモリに読み込むのに十分であるため、それでも比較的高速ですが、同時実行性が増加したり、テーブルが大きくなったりすると、効率の問題が深刻になるだろう。
この時点で、インデックスにユーザー ID を追加しました:
imgs にインデックス ユーザー ID を追加します (userid);
次に、もう一度確認します:
mysql> desc select * from imgs where userid="7mini" order by clicks desc limit
+- -- -+-------------+------+------+--------------+- -- ------+--------+------+-----+------ -------+
| テーブルのタイプ | キー長 | -- ---+----------+-----+------+----------+---- -- ---+------+------+---------------------- | +
| ユーザーID |
+--------+--を使用します。 -- ---+------+---------------+----------+----------+- --- ---+------+--------------------------------+
1 行in set (0.00 秒)
まあ、mysql が userid インデックスを使用して検索を行った後、結果セットには 8 つの結果が含まれていることがわかります。filesort を使用して 1 つずつ並べ替えましたが、結果セットには 8 項目しか含まれていなかったため、効率の問題は軽減されました。
ただし、別のユーザー ID でクエリを実行すると、結果は異なります:
mysql> desc select * from imgs where userid="admin" order by clicks desc limit 10; -------+------+------+------+----------+ -- --------+----------+-----+-------------------------- --- -+
| テーブルのタイプ | キー長 |
+--------+-- --+------+------+----------+----------+-- --- --+------+----------------------------+
| 画像 |ユーザー ID | 2944 | ファイルソートの使用 | -+--------------+--------+--------+----------+--- --- ----------------------------------+
セット内の 1 行 (0.00 秒)
この結果は同じですas userid= 「7mini」の結果は基本的に同じですが、userid インデックスを使用した MySQL 検索後の結果セットのサイズは 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 desc limit 10; - ---+-------------+------+------+--------------+ - -------+-------+-------+------+----- - ------------+ テーブルのタイプ | キー長 | ------+----------+------+---------------------+--------+--- ------+------+------+---------------------------- - +
| imgs | クリック数 2944 | + -------+------+------+----------+--------- -+ -------+------+--------------------------------+
セット内の 1 行 (0.00 秒)
この時点で、 possible_keys が userid に変更されていることがわかります。 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
+---- +- ------------+----------+------+------+--- -- ---+---------+------+------+-------------+
|テーブル | キーの数 | 追加の行 | -- +--------------+----------+----------+------+----- -+ -------------+ 1 | クリック数 | 5455 | -- -------+----------+----------+---------------------+-------- +- --------+------+------+---------------+
セット内の 1 行 (0.00 秒)
MySQLこの時点では、クリック数インデックスがクエリに使用されますが、結果セットはユーザー ID よりも大きいようです。さらに制限があるようです:
mysql> desc select * from imgs use Index (clicks) where userid='admin' and clicks> ;1000 クリックによる注文の制限 10
+----+-------------+-----------+----------+----- ----- ---+--------+----------+------+------+------ ----- --+ テーブル | キー長 |
+----+---------- +-------- ---+----------+--------+----------+-------- -+--- ---+------+----------+ 1 | クリック数 | where |
+ ----+---------------+----------+----------+---------- を使用する--- +--------+--------+------+------+------------- +
セット内の 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; +-------------+------+------+--------------------- -- -+--------------+----------+------+-----+----- -- ------+ テーブルのタイプ | キー長 |
-------+------+-----------+---------- -- ---+--------+-------+----------+
| | ref | userid_clicks | 2944 | を使用します。 ---+----------------------+---------------+----- -- --+-------+------+---------------+
セット内の 1 行 (0.00 秒)
この時点で、結果セットにはまだ 2944 個のエントリがありますが、Extra のファイルソートがありません。現時点では、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 や複数テーブルの結合クエリなど、いくつかの特殊な関数を使用しています。また、複雑なクエリを使用することもできます。ここの 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 番号です。使用中の接続は切断されません。この構成では、長期間アクティブでなかった接続のみが切断されます。
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 をキャッシュに追加した後も問題が解決しない場合は、プログラム アーキテクチャを調整し、アプリケーションを解体して使用するしかありません。同時にサービスを提供する複数のマシン。
解体された場合、ビジネスにわずかな影響を及ぼしますが、ビジネス内の一部の機能がすべてのデータを使用する必要がある場合は、完全なライブラリ + n 個の分散ライブラリのようなアーキテクチャを使用できます。ライブラリと分散ライブラリを一度または定期的に完全なライブラリをコンパイルします。もちろん、最も愚かな方法もあります。これは、データベースの完全なコピーを作成し、プログラムが毎回これらのライブラリで完全な SQL を実行し、アクセス中にアクセスをポーリングするという方法です。 MySQL の同期方法。
4. mysql proxy プロキシを使用する
mysql proxy はプロキシを介してデータベース内のテーブルを複数のサーバーに分散できますが、人気のあるテーブルが複数のテーブルに分散している場合は、この方法を使用します。問題を解決するのは比較的簡単です。
このソフトウェアを使用したことも、注意深く確認したこともありませんが、その機能について少し疑問があります。つまり、複数のテーブル間の結合クエリをどのように実装するのでしょうか? 実装できる場合、どの程度効率的ですか? memcachedb
mysql をサポートする memcachedb にデータベースを変更することは、memcachedb の実装とレベルの観点から、データに影響を与えず、ユーザーに迷惑をかけることもないので、試してみるのも良いでしょう。
データベースに関してはあまり問題がないので、このことはまだ試していません。ただし、MySQL の主要な構文のほとんどをサポートし、安定している限り、その使いやすさに疑いの余地はありません。

http://www.bkjia.com/PHPjc/320161.html

www.bkjia.comtru​​ehttp://www.bkjia.com/PHPjc/320161.html技術記事 1. インデックスが構築されていない; 2. SQL の記述方法が複雑すぎる; 3. マシンが負荷に耐えられない; 1. mysql が大量に消費していることがわかる。 CPU の数が少ない場合は、mysql の Clie を使用できます...