|
MySQL で遅い SQL ステートメントを見つけるにはどうすればよいでしょうか?これは、多くの人を困惑させる問題かもしれません。MySQL は、スロー クエリ ログを通じて、実行効率の低い SQL ステートメントを見つけます。--log-slow-queries[=file_name] オプションを指定して起動すると、mysqld は、実行時間が long_query_time 秒を超えるすべての SQL ステートメントを含むログ ファイルを書き込みます。このログ ファイルを表示することで、効率の低い SQL ステートメントを見つけることができます。 MySQLで遅いSQL文をクエリする方法 1. MySQLデータベースには、非効率的なSQL文をタイムリーに捕捉するのに役立ついくつかの設定オプションがあります。 1. スロークエリログ このパラメータを ON に設定すると、実行時間が一定値を超える SQL 文をキャプチャできます。 2. 長いクエリ時間 SQL文の実行時間がこの値を超えるとログに記録されます。1以下に設定することをお勧めします。 3. スロークエリログファイル ログファイルのファイル名。 4. ログクエリがインデックスを使用しない このパラメータを ON に設定すると、SQL ステートメントが非常に高速に実行される場合でも、インデックスを使用しないすべての SQL ステートメントをキャプチャできます。 2. MySQL で SQL 文の効率を検出する方法 1. ログを照会する （1）WindowsでMySQLスロークエリを有効にする WindowsシステムのMySQLの設定ファイルは通常my.iniです。[mysqld]配下で見つけて、 コードは次のとおりです ログスロークエリ = F:/MySQL/log/mysqlslowquery。ログ
長いクエリ時間 = 2 （2）LinuxでMySQLスロークエリを有効にする WindowsシステムのMySQLの設定ファイルは通常my.cnfです。[mysqld]配下で見つけて、 コードは次のとおりです log-slow-queries=/data/mysqldata/slowquery。ログ
長いクエリ時間=2 例示する ログスロークエリ = F:/MySQL/log/mysqlslowquery。 これは、スロー クエリ ログが保存される場所です。通常、このディレクトリには、MySQL 実行アカウントの書き込み権限が必要です。通常、このディレクトリは、MySQL データ ストレージ ディレクトリとして設定されます。
long_query_time=2 の 2 は、クエリが 2 秒を超えた場合にのみ記録されることを意味します。 2.show processlistコマンド WSHOW PROCESSLIST は実行中のスレッドを表示します。この情報を取得するには、mysqladmin processlist ステートメントを使用することもできます。 各列の意味と目的: ID列 ステートメントを強制終了する場合に便利なフラグです。コマンド /*/mysqladmin kill process number を使用してクエリを強制終了します。 ユーザー列 現在のユーザーを表示します。ルート ユーザーでない場合は、このコマンドは権限内の SQL ステートメントのみを表示します。 ホスト列 このステートメントが送信された IP とポートを表示します。問題のあるステートメントを作成したユーザーを追跡するのに役立ちます。 db列 このプロセスが現在接続されているデータベースを表示します。 コマンドライン 現在の接続によって実行されたコマンド (通常は sleep、query、または connect) を表示します。 時間欄 この状態の継続時間（秒単位）。 状態列 現在の接続を使用して SQL ステートメントのステータスを表示します。これは非常に重要な列です。すべてのステータスについては後で説明します。状態はステートメントの実行における特定の状態にすぎないことに注意してください。たとえば、クエリなどの SQL ステートメントは、完了する前に、tmp テーブルへのコピー、結果のソート、データの送信などの状態を経る必要がある場合があります。 情報欄 この SQL 文が表示されます。長さの制限があるため、長い SQL 文は完全には表示されませんが、問題文を判断するための重要な基準となります。 このコマンドの最も重要な部分は状態列です。MySQL によってリストされる状態は主に次のとおりです。 チェックテーブル
データ テーブルをチェックしています (これは自動です)。
テーブルを閉じる
テーブル内の変更されたデータはディスクにフラッシュされ、使用されていたテーブルは閉じられています。これは非常に迅速な操作ですが、そうでない場合は、ディスクがいっぱいになっていないか、ディスクに大きな負荷がかかっていないかを確認する必要があります。
接続アウト
レプリケーション スレーブ サーバーがマスター サーバーに接続しています。
ディスク上の tmp テーブルにコピーしています
一時結果セットが tmp_table_size より大きいため、メモリを節約するために一時テーブルがメモリ ストレージからディスク ストレージに変換されています。
一時テーブルを作成しています
部分的なクエリ結果を保持するための一時テーブルを作成します。
メインテーブルから削除
サーバーは複数テーブルの削除の最初の部分を実行しており、最初のテーブルを削除しました。
参照テーブルから削除する
サーバーは複数テーブル削除の 2 番目の部分を実行しており、他のテーブルからレコードを削除しています。
フラッシングテーブル
FLUSH TABLES が実行されており、他のスレッドがデータ テーブルを閉じるのを待機しています。
殺された
スレッドに kill 要求が送信された場合、スレッドは kill フラグをチェックし、次の kill 要求を破棄します。 MySQL は各メイン ループで kill フラグをチェックしますが、場合によってはスレッドが終了するまでに時間がかかることがあります。スレッドが他のスレッドによってロックされている場合、ロックが解除されると、kill 要求は直ちに有効になります。
ロックされています
別のクエリによってロックされています。
データの送信
SELECT クエリのレコードが処理され、結果がクライアントに送信されています。
グループの並べ替え
GROUP BY のソート。
順序の並べ替え
ORDER BY によるソート。
テーブルを開く
他の要因によって妨害されない限り、このプロセスは迅速に行われるはずです。たとえば、ALTER TABLE または LOCK TABLE ステートメントが完了するまで、他のスレッドはテーブルを開くことができません。テーブルを開こうとしています。
重複の削除
SELECT DISTINCT クエリが実行されていますが、MySQL は前の段階で重複レコードを最適化できませんでした。したがって、MySQL は重複レコードを再度削除し、その結果をクライアントに送信する必要があります。
テーブルを再度開く
テーブルのロックは取得されますが、ロックはテーブル構造が変更された後にのみ取得できます。ロックが解除され、テーブルが閉じられ、テーブルを再度開こうとしています。
分類による修復
インデックスを作成するためにソートされる命令を修正しました。
キーキャッシュで修復
修復命令は、インデックス キャッシュを使用して新しいインデックスを 1 つずつ作成します。ソートによる修復よりも遅くなります。
更新する行を検索しています
条件を満たすレコードが更新対象として検索されています。 UPDATE が関連レコードを変更する前にこれを実行する必要があります。
眠る
新しいクライアントのリクエストを待っています。
システムロック
外部システム ロックの取得を待機しています。同時に同じテーブルを要求する複数の mysqld サーバーを実行していない場合は、--skip-external-locking パラメータを追加して外部システム ロックを無効にすることができます。
ロックのアップグレード
INSERT DELAYED は、新しいレコードを挿入するためにテーブル ロックを取得しようとしています。
更新中
一致するレコードを検索して変更します。 ユーザーロック
GET_LOCK() を待機しています。
テーブル待ち
スレッドには、データ テーブル構造が変更され、新しい構造を取得するために再度開く必要があることが通知されます。その後、データ テーブルを再度開くには、他のすべてのスレッドがテーブルを閉じるまで待つ必要があります。この通知は、FLUSH TABLES tbl_name、ALTER TABLE、RENAME TABLE、REPAIR TABLE、ANALYZE TABLE、または OPTIMIZE TABLE の状況で生成されます。
ハンドラの挿入を待機中
INSERT DELAYED は保留中の挿入操作をすべて処理し、新しい要求を待機しています。
ほとんどの状態は非常に高速な操作に対応しています。スレッドが数秒間同じ状態のままである場合は、問題が発生している可能性があるため、確認する必要があります。
上記に記載されていないステータスもありますが、そのほとんどはサーバーにエラーがあるかどうかを確認する場合にのみ役立ちます。 たとえば、図に示すように: 
3. SQL実行のステータスを理解するために説明する EXPLAIN は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示します。より適切なインデックスを選択し、より最適化されたクエリ ステートメントを記述するのに役立ちます。 これを使用するには、select ステートメントの前に explain を追加するだけです。 例えば：
a.id=b.id の場合、a,b の形式で姓、名を選択します。 結果は図に示す通りである。 
EXPLAIN列の説明 テーブル このデータ行がどのテーブルに関するものかを示します タイプ これは、使用されている接続の種類を示す重要な列です。結合タイプは、最良から最悪の順に、const、eq_reg、ref、range、indexhe、ALLです。 可能なキー このテーブルに適用できる可能性のあるインデックスを表示します。空の場合、インデックスは作成できません。関連するフィールドの WHERE 句から適切なステートメントを選択できます。 鍵 実際に使用されるインデックス。 NULL の場合、インデックスは使用されません。まれに、MYSQL が最適化されていないインデックスを選択することがあります。この場合、SELECT ステートメントで USE INDEX (indexname) を使用してインデックスの使用を強制したり、IGNORE INDEX (indexname) を使用して MySQL にインデックスを無視するように強制したりできます。 キーの長さ 使用するインデックスの長さ。長さが短いほど、精度を失わずに良くなります。 参照 インデックスのどの列が使用されているか、または可能な場合は定数を表示します。 行 MYSQLが要求されたデータを返すためにチェックする必要があると考える行数 余分な MYSQL がクエリを解析する方法に関する追加情報。これについては表 4.3 で説明しますが、ここで見られる悪い例は、temporary の使用と filesort の使用です。これは、MYSQL がインデックスをまったく使用できないため、取得が遅くなることを意味します。 追加列に返される説明の意味 明確な MYSQL は行結合に一致する行を見つけると、それ以上検索を行いません。 存在しない MYSQL は LEFT JOIN を最適化します。LEFT JOIN 条件に一致する行が見つかると、再度検索されることはありません。 各レコードのチェック範囲（インデックスマップ:#） 理想的なインデックスが見つからなかったため、前のテーブルの行の組み合わせごとに、MYSQL は使用するインデックスをチェックし、それを使用してテーブルから行を返します。これはインデックスを使用する最も遅い接続の1つです ファイルソートの使用 このような場合は、クエリを最適化する必要があります。 MYSQL は、返される行をどのように順序付けるかを検出するために追加の手順を実行する必要があります。結合タイプに基づいてすべての行をソートし、条件に一致するすべての行のソートキー値と行ポインターを保存します。 インデックスの使用 列データは、実際に読み取ることなく、インデックスの情報のみを使用してテーブルから返されます。これは、テーブルに対して要求されたすべての列が同じインデックスの一部である場合に発生します。 一時的な使用 このような場合は、クエリを最適化する必要があります。ここで、MYSQL は結果を格納するための一時テーブルを作成する必要があります。これは通常、GROUP BY ではなく、異なる列セットに対して ORDER BY が実行されたときに発生します。 使用場所 WHERE 句は、どの行が次のテーブルと一致するか、またはユーザーに返されるかを制限するために使用されます。これは、テーブル内のすべての行を返す必要がなく、結合タイプが ALL またはインデックスの場合、またはクエリに問題がある場合に発生する可能性があります。さまざまな結合タイプの説明 (効率の順に並べ替え) 定数 このクエリに一致するテーブル内のレコードの最大値 (インデックスは主キーまたは一意のインデックスにすることができます)。行が 1 つしかないため、この値は実際には定数です。これは、MYSQL が最初に値を読み取ってから定数として扱うためです。 等価参照 接続では、MYSQL はクエリ時にレコード結合ごとに前のテーブルからレコードを読み取ります。これは、クエリがインデックスを主キーまたは一意のキーとして使用する場合に使用されます。 参照 この結合タイプは、クエリが一意キーや主キーではないキー、またはこれらのタイプのいずれかの一部 (たとえば、左端のプレフィックスを使用) を使用する場合にのみ発生します。前のテーブルとの各行の結合ごとに、すべてのレコードがテーブルから読み取られます。このタイプは、インデックスと一致するレコードの数に大きく依存します。少ないほど良いです。 範囲 この結合タイプは、インデックスを使用して行の範囲を返します。たとえば、何かを検索するために > または < を使用した場合などです。 索引 この結合タイプは、前のテーブルのすべてのレコードの完全スキャンを実行します（インデックスは一般にテーブルデータよりも小さいため、ALLよりも優れています）。 全て この結合タイプは、以前の各レコードの完全スキャンを実行しますが、これは一般的に好ましくないため、避けるべきです。 MySQL - 遅いSQLを表示
MySQLが低速SQLログファイルを有効にしているかどうかを確認する （１）スローSQLログが有効になっているか確認する mysql> 'log_slow_queries' のような変数を表示します。
+------------------+-------+
| 変数名 | 値 |
+------------------+-------+
| log_slow_queries | オン |
+------------------+-------+
セット内の 1 行 (0.00 秒) （２）指定した秒数より遅いSQL文がログファイルに何秒記録されるか確認する
mysql> 'long_query_time' のような変数を表示します。
+-----------------+-------+
| 変数名 | 値 |
+-----------------+-------+
| 長いクエリ時間 | 1 |
+-----------------+-------+
セット内の 1 行 (0.00 秒) ここで値=1は1秒を意味します 2. my.ini ファイル (Linux でのファイル名は my.cnf) を構成し、[mysqld] セクションを見つけて、次の例に示すようにログ構成を追加します。
[mysqld]
ログ="C:/temp/mysql.log"
log_slow_queries="C:/temp/mysql_slow.log"
長いクエリ時間=1
log はログファイルが保存されるディレクトリを示します。
log_slow_queries は、長い実行時間を記録する SQL ログ ディレクトリを示します。
long_query_time は、実行時間が長いと見なされる時間を秒単位で示します。
これらの設定項目は Linux にすでに存在しているはずですが、コメントアウトされています。コメントは削除できます。ただし、構成項目を直接追加することもできます。
非効率的な SQL ステートメントを見つけたら、EXPLAIN または DESC コマンドを使用して、テーブルの接続方法や SELECT ステートメントの実行中の接続順序など、MySQL が SELECT ステートメントを実行する方法に関する情報を取得できます。たとえば、2006 年のすべての会社の売上を計算する場合は、売上テーブルと会社テーブルを結合し、利益フィールドで合計演算を実行する必要があります。対応する SQL 実行プランは次のとおりです。
mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G;
************************** 1. 行 ****************************
id: 1
選択タイプ: シンプル
テーブル:
タイプ: すべて
可能なキー: NULL
キー: NULL
キー長さ: NULL
参照: NULL
行数: 12
追加: where の使用
************************** 2. 行 ****************************
id: 1
選択タイプ: シンプル
表: b
タイプ: すべて
可能なキー: NULL
キー: NULL
キー長さ: NULL
参照: NULL
行数: 12
追加: where の使用
セット内の 2 行 (0.00 秒)
各列の説明は次のとおりです。
•select_type: SELECT のタイプを示します。一般的な値には、SIMPLE (単純なテーブル、つまりテーブル結合やサブクエリは使用されません)、PRIMARY (プライマリ クエリ、つまり外部クエリ)、UNION (UNION 内の 2 番目以降のクエリ ステートメント)、SUBQUERY (サブクエリ内の最初の SELECT) などがあります。
•table: 結果セットを出力するテーブル。
•タイプ：テーブルの接続タイプは、最良のものから最悪の型（テーブルの1つの行、つまり一定のテーブルのみ）、const（最大のキーまたは一意のインデックスなど、1つの一致する行があります）、EQ_REF（前の行ごとに、このテーブルには1つのレコードのみがQUERIEDを使用しています。一意のインデックス、通常のインデックスが使用されます）、ref_or_null（refeと同様に、違いはnullのクエリが含まれることです）、index_merge（インデックスマージ最適化）、unique_subquery（inは主要なキーフィールドにクエリが続きます）、index_subquery（sing_subqueryは、sing field of a sing field ex a sing _subqueryに並んでいます（前の行ごとに、データはインデックスを照会することによって取得されます）、すべて（前の行ごとに、テーブル全体をスキャンすることでデータが取得されます）。
•possible_keys: クエリ時に使用できるインデックスを示します。
•key: 実際に使用されるインデックスを示します。
•key_len: インデックス フィールドの長さ。
•rows: スキャンする行数。
•追加: 実装に関するメモと説明。
上記の例では、テーブル a の完全なテーブルスキャンによって効率が低下することが確認できます。次に、次のようにしてテーブル a の year フィールドにインデックスを作成します。
mysql> sales(year) に idx_sales_year インデックスを作成します。
クエリは正常、12 行が影響を受けました (0.01 秒)
記録: 12 重複: 0 警告: 0
インデックスを作成した後、このステートメントの実行プランは次のようになります。
mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G;
************************** 1. 行 ****************************
id: 1
選択タイプ: シンプル
テーブル:
タイプ: ref
可能なキー: idx_sales_year
キー: idx_sales_year
キーの長さ: 4
参照: 定数
行数: 3
余分な：
************************** 2. 行 ****************************
id: 1
選択タイプ: シンプル
表: b
タイプ: すべて
可能なキー: NULL
キー: NULL
キー長さ: NULL
参照: NULL
行数: 12
追加: where の使用
セット内の 2 行 (0.00 秒) インデックスが確立された後、テーブル a のスキャンが必要な行数が大幅に減少していることがわかります (フルテーブルスキャンから 3 行に)。特にテーブルが非常に大きい場合、インデックスを使用するとデータベースのアクセス速度が大幅に向上することがわかります。この利点はより明白です。インデックスを使用して SQL を最適化することは、問題のある SQL を最適化するための一般的な基本的な方法です。次の章では、インデックスを使用して SQL を最適化する方法を具体的に紹介します。
この記事では主にMySQLの遅いクエリの分析方法を紹介します。以前、MySQLデータベースで1秒より遅いSQL文をクエリするレコードを設定しました。かなり特殊な設定方法がいくつかあったのを思い出し、いくつかのパラメータの名前が思い出せなかったので、整理し直して自分用にメモを残しました。
トラブルシューティングとパフォーマンスのボトルネックの発見に関して、最も簡単に見つけて解決できる問題は、遅い MySQL クエリとインデックスのないクエリです。 さて、MySQL で実行するのが「快適」ではない SQL ステートメントを探し始めましょう。
MySQL の遅いクエリ分析方法 1:
私はこの方法を使っています。笑、その即時性が気に入っています。
MySQL バージョン 5.0 以降では、実行速度が遅い SQL ステートメントの記録をサポートできます。
MySQL> 'long%' のような変数を表示します。
注: この long_query_time は、クエリが何秒遅い場合「遅いクエリ」と見なされるかを定義するために使用されます。
+-----------------+-----------+
| 変数名 | 値 |
+-----------------+-----------+
| 長いクエリ時間 | 10.000000 |
+-----------------+-----------+
セット内の 1 行 (0.00 秒)
MySQL> long_query_time を 1 に設定します。
注: これを 1 に設定しました。つまり、実行に 1 秒以上かかるクエリは遅いクエリと見なされます。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
MySQL> 'slow%' のような変数を表示します。
+---------------------+---------------+
| 変数名 | 値 |
+---------------------+---------------+
| 遅い起動時間 | 2 |
| slow_query_log | オン |
注: ログ記録は有効になっていますか? | slow_query_log_file | /tmp/slow.log |
注: どの位置に設定するか +---------------------+---------------+
セット内の 3 行 (0.00 秒)
MySQL> グローバル slow_query_log='ON' を設定します
注: ログ記録をオンにすると、slow_query_log 変数が ON に設定されると、MySQL はすぐにログ記録を開始します。
上記のMySQLグローバル変数の初期値は/etc/my.cnfで設定できます。
long_query_time=1 slow_query_log_file=/tmp/slow.log
MySQL の遅いクエリ分析方法 2:
MySQLdumpslow コマンド /path/MySQLdumpslow -sc -t 10 /tmp/slow-log
これにより、最も多くのレコードを含む上位 10 個の SQL ステートメントが出力されます。
-s はソート方法を示します。c、t、l、r は、それぞれレコード数、時間、クエリ時間、返されたレコード数でソートされます。ac、at、al、ar は、対応する逆ソートを示します。
-t は上位 n を意味し、返されるレコードの数を意味します。
-g に続いて正規表現のマッチングパターンが続きます (大文字と小文字は区別されません)。
たとえば、/path/MySQLdumpslow -sr -t 10 /tmp/slow-log
最も多くのレコードを返す上位 10 件のクエリを取得します。
/path/MySQLdumpslow -st -t 10 -g "左結合" /tmp/slow-log
時間順に並べられた左結合を含む最初の 10 個のクエリ ステートメントを取得します。
もっと簡単な方法:
my.ini を開き、[mysqld] を見つけて、その下に long_query_time = 2 を追加します。 log-slow-queries = D:/mysql/logs/slow.log # ログを書き込む場所を設定します。空にすることができ、その場合、システムはデフォルトのファイルを提供します #log-slow-queries = /var/youpath/slow.log host_name-slow.log (Linux の場合) log-queries-not-using-indexes long_query_time は、SQL が実行されてから記録されるまでにかかる時間 (秒単位) を示します。ここでの設定は 2 秒です。
以下は、mysqldumpslow の共通パラメータの説明です。詳細な情報については、mysqldumpslow -help を使用して照会できます。 -s はソート方法を示します。c、t、l、r は、レコード数、時間、クエリ時間、返されたレコード数 (大きい順) でソートされます。ac、at、al、ar は逆順にソートされます。 -t は上位 n を意味し、返されるレコードの数を意味します。 www.jb51.net -g の後に、大文字と小文字を区別しない正規表現一致パターンが続きます。 次のステップは、mysql に付属する低速クエリ ツールである mysqldumpslow を使用して分析することです (mysql の bin ディレクトリ内)。ログ ファイルの名前は host-slow.log です。 最も多くのレコードを持つ 10 個の SQL ステートメントを一覧表示します。mysqldumpslow -sc -t 10 host-slow.log 最も多くのレコード セットが返される 10 個の SQL ステートメントを一覧表示します。mysqldumpslow -sr -t 10 host-slow.log 時間に従って左結合を含む上位 10 個の SQL ステートメントを返します。mysqldumpslow -st -t 10 -g "left join" host-slow.log mysqldumpslow コマンドを使用すると、必要なさまざまなクエリ ステートメントを明確に取得できます。これは、MySQL クエリ ステートメントの監視、分析、および最適化に非常に役立ちます。
日常の開発では、ページの開きが極端に遅い状況によく遭遇します。排除した後、データベースの影響であることが判明しました。特定の SQL をすばやく見つけるには、MySQL ログ メソッドを使用できます。
-- SQL実行ログを有効にする
set global log_output='TABLE'; -- テーブルに出力します
set global log=ON; -- すべてのコマンド実行、すべてのステートメント（成功と失敗）に対して general_log を有効にします。
set global log_slow_queries=ON; -- スロークエリSQLログをオンにします slow_log、実行成功: スロークエリステートメントとインデックスを使用しないステートメント
set global long_query_time=0.1; -- スロークエリの時間制限（秒）
set global log_queries_not_using_indexes=ON; -- インデックスを使用しないSQL文をログに記録します
-- SQL実行レコードをクエリする
select * from mysql.slow_log order by 1; -- 正常に実行されました: 遅いクエリステートメントとインデックスを使用しないステートメント
select * from mysql.general_log order by 1; -- すべてのステートメント: 成功と失敗。
-- SQL実行ログを閉じる
グローバルログをOFFに設定します。
グローバル log_slow_queries を OFF に設定します。
-- long_query_time パラメータの説明
-- v4.0、4.1、5.0、v5.1 ～ 5.1.20 (含む): ミリ秒レベルの低速クエリ分析をサポートしません (1 ～ 10 秒の精度をサポートします)。
-- バージョン 5.1.21 以降: 0.1 などのミリ秒レベルの低速クエリ分析をサポートします。
-- 6.0 から 6.0.3: ミリ秒レベルの低速クエリ分析はサポートされません (1 ～ 10 秒の精度をサポートします)。
-- 6.0.4 以降: ミリ秒レベルでの低速クエリ分析をサポートします。
ログに記録された SQL を通じて、特定のファイルをすばやく見つけ、SQL を最適化して速度が改善されたかどうかを確認できます。
この記事では、MySQL データベース サーバーのクエリがどんどん遅くなる問題を分析し、対応するソリューションを提案します。具体的な分析とソリューションは次のとおりです。開発者は、インデックスのないステートメントや制限 n のないステートメントを頻繁にチェックします。これらのステートメントは、データベースに大きな影響を与えます...
この記事では、MySQL データベース サーバーのクエリがどんどん遅くなる問題を分析し、対応する解決策を提案します。具体的な分析と解決策は次のとおりです。
開発者は、インデックスや制限 n のないステートメントをチェックすることがよくあります。これらのステートメントは、データベースに大きな影響を与える可能性があります。たとえば、数千万件のレコードを含む大きなテーブルを完全にスキャンする必要がある場合や、ファイルソートを継続的に実行する必要がある場合は、データベースとサーバーの IO に影響します。ミラーライブラリの状況は次の通りです。
オンライン データベースに関しては、インデックスのないステートメントや制限のないステートメントに加えて、MySQL 接続が多すぎるという別の問題があります。ところで、まずはこれまでのモニタリングの実践を見てみましょう。
1. zabbixなどのオープンソースの分散監視システムを導入して、毎日のデータベースIO、CPU、接続数を取得します。
2. データ増加、iostat、vmstat、データサイズなどの週次パフォーマンス統計を展開する
3. MySQL スローログ収集、トップ 10 のリスト
以前は監視は完璧だと思っていましたが、MySQL ノード プロセス監視を導入した後、多くの欠点に気づきました。最初のアプローチの欠点: Zabbix が大きすぎるため、監視は MySQL 内で行われません。大量のデータはあまり準備されておらず、通常は履歴データの確認に使用されます。2 番目のアプローチの欠点: 週に 1 回しか実行されないため、多くの状況を検出して警告することはできません。3 番目のアプローチの欠点: ノードにスローログが大量にある場合、top10 は無意味になり、実行する必要がある定期的なタスク ステートメントが頻繁に表示されます。 。参照値は大きくないので、これらの問題をどのように解決し、クエリすればよいでしょうか? トラブルシューティングとパフォーマンスのボトルネックの検出において、最も簡単に見つけて解決できる問題は、MYSQL の遅いクエリとインデックスのないクエリです。
さて、MySQL で実行するのが「快適」ではない SQL ステートメントを探し始めましょう。 方法 1: 現在、この方法を使用しています。ハハ、この方法の即時性が気に入っています。 Mysql バージョン 5.0 以降では、実行速度が遅い SQL ステートメントの記録をサポートできます。 mysql> show variables like 'long%'; 注: この long_query_time は、クエリが何秒遅い場合に「遅いクエリ」と見なされるかを定義するために使用されます。
+-----------------+-----------+
| 変数名 | 値 |
+-----------------+-----------+
| 長いクエリ時間 | 10.000000 |
+-----------------+-----------+
セット内の 1 行 (0.00 秒)
mysql> set long_query_time=1; 注: これを 1 に設定しました。これは、実行に 1 秒以上かかるクエリは遅いクエリとみなされることを意味します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> 'slow%' のような変数を表示します。
+---------------------+---------------+
| 変数名 | 値 |
+---------------------+---------------+
| 遅い起動時間 | 2 |
| slow_query_log | ON | 注: ログ記録を開くかどうか
| slow_query_log_file | /tmp/slow.log | 注: 設定場所
+---------------------+---------------+
セット内の 3 行 (0.00 秒)
mysql> set global slow_query_log='ON' 注: ログ記録をオンにします
slow_query_log 変数を ON に設定すると、mysql はすぐにログ記録を開始します。
上記のMYSQLグローバル変数の初期値は/etc/my.cnfで設定できます。
長いクエリ時間=1
slow_query_log_file=/tmp/slow.log 方法 2: mysqldumpslow コマンド /path/mysqldumpslow -sc -t 10 /tmp/slow-log
これにより、最も多くのレコードを含む上位 10 個の SQL ステートメントが出力されます。
-s はソート方法を示します。c、t、l、r はそれぞれ、レコード数、時間、クエリ時間、返されたレコード数でソートされます。ac、at、al、ar は、対応する逆ソートを示します。
-t は上位 n を意味し、返されるレコードの数を意味します。
-g に続いて正規表現のマッチングパターンが続きます (大文字と小文字は区別されません)。
例えば
/path/mysqldumpslow -sr -t 10 /tmp/slow-log
最も多くのレコードを返す上位 10 件のクエリを取得します。
/path/mysqldumpslow -st -t 10 -g "左結合" /tmp/slow-log
時間順に並べられた左結合を含む最初の 10 個のクエリ ステートメントを取得します。
最後に、ノード監視の利点をまとめます。
1. 軽量な監視、リアルタイム、実際の状況に応じてカスタマイズおよび変更可能
2. 実行する必要があるステートメントをフィルタリングするためのフィルターが設定されます。
3. 未使用のインデックスや不正なクエリをタイムリーに検出します。これらの遅いステートメントの処理には時間がかかりますが、データベースのクラッシュを回避するには価値があります。
4. データベースへの接続が多すぎる場合、プログラムは現在のデータベースのプロセスリストを自動的に保存します。これは、DBA が原因を見つけるための優れたツールです。
5. mysqlbinlog を使用して分析すると、データベースの状態が異常な期間を明確に把握できます。mysql 構成ファイルを設定することを提案する人もいます。 tmp_table_sizeを調整するときに、他のパラメータがいくつか見つかりました
Qcache_queries_in_cache キャッシュに登録されたクエリの数
Qcache_inserts キャッシュに追加されたクエリの数
Qcache_hits キャッシュサンプルの数
Qcache_lowmem_prunes メモリ不足のためキャッシュから削除されたクエリの数。
Qcache_not_cached キャッシュされなかったクエリの数（キャッシュ不可能または QUERY_CACHE_TYPE が原因）
Qcache_free_memory クエリキャッシュ内の空きメモリの合計量
Qcache_free_blocks クエリキャッシュ内の空きメモリブロックの数
Qcache_total_blocks クエリキャッシュ内のブロックの総数
Qcache_free_memory は、よく使用されるクエリの一部をキャッシュできます。よく使用される SQL の場合は、メモリにロードされます。これによりデータベースアクセスの速度が向上します | 
