プライベートな連絡先の最初の情報
送料メール:
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
MySQL ログの内容は非常に重要であり、面接でよく質問されます。同時に、ログ関連の知識を習得することは、MySQL の基礎となる原理を理解し、必要に応じて問題のトラブルシューティングや解決を行うのにも役立ちます。
MySQL の一般的なログ タイプには、主に次のカテゴリが含まれます (InnoDB ストレージ エンジン用)。
バイナリ ログ (binlog) とトランザクション ログ (REDO ログと UNDO ログ) はより重要であり、重点を置く必要があります。
低速クエリ ログは、実行時間が long_query_time (デフォルトは 10 秒、通常は 1 秒に設定されます) を超えるすべてのクエリ ステートメントを記録します。これは、SQL 低速クエリ (SQL 実行時間が長すぎる) 問題を解決するときによく使用されます。
SQL ステートメントのパフォーマンスを最適化するには、遅い SQL を見つけることが最初のステップです。次に、EXPLAIN コマンドを使用して、遅い SQL を分析し、実行計画に関する情報を取得します。
「slow_query_log」コマンドのような show 変数を使用して、スロー クエリ ログがデフォルトでオフになっているかどうかを確認できます。
SET GLOBAL throw_query_log=ON でオンにできます。
long_query_time パラメータは、クエリが低速クエリとして定義されるまでの時間を定義します。デフォルトは 10 秒です。これは SHOW VARIABLES LIKE'%long_query_time%' コマンドで表示できます。
これは変更することもできます: set global long_query_time = 12
実際のプロジェクトでは、スロー クエリのログは比較的大きい場合があり、それを直接分析するのは不便です。公式の MySQL スロー クエリ分析およびチューニング ツールを使用できます。 mysqldumpslow 。私のブログも mysqldumpslow ツールに単純に接続されています。
[MySQL] mysqldumpslow ツール -- スロー クエリ ログ ファイルの要約 - CSDN ブログ
MySQL には、現在の遅いクエリ ステートメントの数を記録する変数があります。'%slo のような show global status を使用できます。
w_queries%'; 表示するコマンド。
MySQL が提供するもの説明する実行計画に関する情報を取得するコマンド。
実行プランは、MySQL クエリ オプティマイザーによって最適化された後の SQL ステートメントの特定の実行方法を指します。実行プランは通常、SQL パフォーマンス分析や最適化などのシナリオで使用されます。 EXPLAIN の結果を通じて、データ テーブルのクエリ シーケンス、データ クエリ操作の操作タイプ、ヒットできるインデックス、実際にヒットするインデックス、各データのレコード行数などの情報を知ることができます。テーブルやその他の情報がクエリされます。具体的には、以下の一般的な方法で SQL を最適化できます。
1. SELECT * の使用を避ける
SELECT * はより多くの CPU を消費します。
SELECT *無駄なフィールド、特に大きなフィールド (varchar、
blob、テキスト)。
SELECT * MySQL オプティマイザーを使用してインデックスの最適化をカバーすることはできません (MySQL オプティマイザーに基づく「インデックスのカバー」戦略は非常に高速かつ効率的であり、業界で強く推奨されるクエリ最適化方法です)
SELECT <field list> を使用すると、テーブル構造の変更の影響を軽減できます。
2. ページネーションの最適化
通常のページングは、データ量が少ない場合には比較的短時間で完了します。
データ量が数百万、数千万に達すると、通常のページングでは非常に長い時間がかかります。
最適化するにはどうすればよいですか? 上記の SQL ステートメントをサブクエリに変更できます。
まず、limit の最初のパラメータに対応する主キー値をクエリし、次にこの主キー値に基づいてフィルタリングおよび制限を行うため、効率が向上します。ただし、この方法は ID が正の順序である場合にのみ機能します。
ただし、サブクエリの結果によって新しいテーブルが生成されるため、サブクエリの多用は避けてください。また、この方法は ID が正順の場合にのみ適用できます。複雑なページングのシナリオでは、フィルタリング条件によって条件を満たす ID を除外する必要があることがよくあります。このとき、ID は離散的で不連続です。
3. 結合を減らす
アリババ開発マニュアル:
Zhihu に関するディスカッションを読むことができます。
https://www.zhihu.com/question/68258877
https://www.zhihu.com/question/682588774. 外部キーとカスケードは使用しないことをお勧めします。
Alibaba Java 開発マニュアル:
5. 適切なフィールド タイプを選択します
6. UNION の代わりに UNION ALL を使用してみてください。
UNION は 2 つの結果セットのすべてのデータを一時テーブルに配置してから重複排除操作を実行しますが、これはより時間がかかり、より多くの CPU リソースを消費します。
UNION ALL は結果セットの重複を排除しなくなり、取得されたデータには重複した項目が含まれます。
ただし、実際のビジネス シナリオでデータの重複が許可されない場合でも、UNION を使用できます。
7. バッチ操作
データベースのデータ更新では、バッチ操作が使用できる場合は、できるだけバッチ操作を使用して、データベース リクエストの数を減らし、パフォーマンスを向上させます。
8. インデックスを正しく使用する
このセクションには多くの内容が含まれており、後ほど別のブログで紹介します。
binlog (バイナリ ログはバイナリ ログ ファイル) は主に、テーブル構造の変更 (CREATE、ALTER、DROP TABLE)、テーブル データを含む、MSQL データベース上で変更されたすべての操作 (データベースによって実行されるすべての DDL および DML ステートメント) を記録します。変更 (INSERT.UPDATE、DELETE..) が含まれますが、SELECT、SHOW、およびデータベースへの変更を引き起こさないその他の操作は含まれません。
show binary logs コマンドを使用すると、すべてのバイナリ ログのリストを表示できます。
バイナリ記録方式には次の 3 種類があります。
Row モードと比較して、ステートメント モードのログ ファイルは小さくなり、ディスク IO 負荷も小さくなり、パフォーマンスが向上します。ただし、その精度は行モードよりも劣ります。
MySQL 5.1.5 より前のバージョンでは、binlog の形式は STATEMENT のみでした。バージョン 5.1.8 から、MySQL は MIXED 形式の binlog をサポートし始めました。 MySQL 5.7.7 より前では、デフォルトでステートメント モードが使用されていました。 MySQL5.7.7 はデフォルトで Row モードを使用します。
「%binlog format%」のような show 変数を使用すると、binlog で使用される形式を表示できます。
binlog の主なアプリケーション シナリオは、マスターとスレーブ、マスターとマスター、およびマスターとスレーブのすべてが binlog から分離できず、データを同期してデータの一貫性を確保する必要があります。
マスター/スレーブ レプリケーションの原理を次の図に示します。
1. メイン ライブラリは、データベース内のデータの変更を binlog に書き込みます。
2. スレーブライブラリをメインライブラリに接続します
3. スレーブ ライブラリは I0 スレッドを作成して、メイン ライブラリから更新されたバイナリログを要求します。
4. メイン ライブラリは binlog ダンプ スレッドを作成して binlog を送信し、スレーブ ライブラリの I/0 スレッドが受信を担当します。 5. スレーブ ライブラリの I/0 スレッドは、受信した binlog をリレーに書き込みます。ログ。
6. ライブラリの SQL スレッドからリレー ログを読み取り、ローカルでデータを同期します (つまり、SQL を再度実行します)。
InnoDB ストレージ エンジンの場合、トランザクションの実行中に、ログは最初に binlogcache に書き込まれ、トランザクションが送信されるときのみ、binlogcache 内のログはディスク上の binlog ファイルに保存されます。メモリへの書き込みが高速になります。これは効率上の理由からも行われます。
トランザクションのバイナリログは分割できないため、トランザクションがどれほど大きくても一度に書き込む必要があるため、システムはメモリのブロックをバイナリログキャッシュとして各スレッドに割り当てます。 binlog_cache_size パラメーターを使用して単一スレッドの binlogcache サイズを制御できます。ストレージの内容がこのパラメーターを超える場合は、ディスクに一時的に保存する必要があります (スワップ)。
では、binlog はいつディスクにフラッシュされるのでしょうか? 値の範囲は 0 ～ N で、デフォルトは 0 です。
·0: 必須要件はありません。いつディスクに書き込むかはシステムが決定します。
·1: トランザクションが送信されるたびに、バイナリログがディスクに書き込まれる必要があります。
·N: N トランザクションごとにバイナリログがディスクに書き込まれます。損失のリスク
MySQL5.7 より前では、sync_binlog のデフォルト値は 0 でした。 MySQL5.7 以降、sync_binlog のデフォルト値は 1 です。一般に、sync_binlog の値を 0 に設定することは推奨されません。パフォーマンス要件が比較的高い場合、またはディスク IO ボトルネックが発生した場合は、sync_binlog の値を適切に増やすことができますが、これによりデータ損失のリスクが増加します。
次の 3 つの状況が発生した場合、MySQL は新しいログ ファイルを再生成し、ファイルのシリアル番号が増分されます。
InnoD8 ストレージ エンジンは、MySQL に挿入するデータは、最終的にはページ単位でストレージ スペースを管理することがわかっています。ディスク IO のオーバーヘッドを軽減するために、メモリ内にはバッファ プールと呼ばれる領域も存在します。データに対応するページがバッファ プールに存在しない場合、MSQL はまずディスク上のページをバッファ プールにキャッシュします。これにより、後でバッファ プール内のページを直接操作できるため、読み取りおよび書き込みのパフォーマンスが大幅に向上します。 。
トランザクションがコミットされた後、バッファー プール内の対応するページへの変更はディスクに保存されない場合があります。このとき、MySQL が突然クラッシュした場合、このトランザクションの変更は直接失われるのでしょうか?
明らかにそうではありません。そうであれば、トランザクションの耐久性に明らかに違反します。
MySQLInnoDB エンジンは REDO ログを使用してトランザクションの耐久性を確保します。 REDO ログの主な機能は、特定のページの特定のオフセットで変更されたバイト数や、特定の変更内容が何であるかなど、ページの変更を記録することです。 REDO ログの各レコードには、表スペース番号、データ ページ番号、オフセット、特定の変更データが含まれており、(REDO ログのタイプに応じて) 変更されたデータの長ささえ記録される場合があります。
トランザクションがコミットされると、フラッシュ戦略に従って REDO ログがディスクにフラッシュされます。これにより、MySQL がクラッシュした場合でも、再起動後にディスクに書き込めなかったデータを回復できるため、耐久性が確保されます。トランザクションの。言い換えれば、REDO ログは MySQL のクラッシュ回復機能を提供します。
REDO ログには、データベースに対するすべての変更操作が記録されます。データベースが書き込み操作 (INSERT、UPDATE、DELETE) を実行すると、これらの操作はまず REDO ログに記録され、次にデータ ファイルに適用されます。このようにして、データ変更操作がディスクに完全に書き込まれる前にシステムに障害が発生した場合でも、REDO ログによってデータが失われないことが保証されます。リカバリ中に、データベースはデータの一貫性を確保するために、REDO ログからこれらの未完了の変更操作をやり直します。
REDO ログは、システムのクラッシュや予期しない停電の後にデータベースを一貫した状態に回復するのに役立ちます。リカバリ プロセス中に、データベースは REDO ログ内のレコードをチェックし、送信されたものの永続化されていないすべてのデータ変更をデータ ファイルに再適用して、データをリカバリします。
書き込み操作のパフォーマンスを向上させるために、データベースでは多くの場合、キャッシュ メカニズム (バッファー プールなど) を使用して、変更操作をすぐにディスクに書き込むのではなく、一時的にメモリに保存します。 REDO ログの存在により、このキャッシュ メカニズムが可能になります。これは、REDO ログが確実に永続化されている限り、キャッシュ内のデータがディスクに書き込まれていなくてもデータ損失のリスクがないためです。
トランザクションをコミットすると、ログ バッファ内の REDO ログがディスクにフラッシュされます。innodb_flush_log_at を使用できます。
コミットパラメータ制御。 MySQL で構成されたフラッシュ戦略によっては、MySQL のダウン後に軽度のデータ損失の問題が発生する可能性があります。
innodb_flush_log_at_trx_commitこれは、MySQL InnoDB ストレージ エンジンの重要な構成パラメータであり、トランザクションの送信時のログのフラッシュ (フラッシュ) および書き込み (書き込み) 戦略を決定するため、データの耐久性とパフォーマンスに影響します。これには 0、1、2 の 3 つの値があります。各値は異なるブラッシング戦略を表します。
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
ブラシ攻略まとめ
1. REDO ログはログ バッファに書き込まれますが、ページ キャッシュにはまだ書き込まれていません。この時点でデータベースがクラッシュし、データ損失が発生します (このデータ損失は、フラッシュ ポリシー innodb_flush log_at trx_commit の値が 1 である場合に発生する可能性があります。 0);
2. REDO ログはページ キャッシュに書き込まれていますが、ディスクにはまだ書き込まれていません。オペレーティング システムがクラッシュし、データ損失が発生する可能性があります (このデータ損失は、フラッシュ ポリシー innodb2 flash log_at trx_commit の値が 1 である場合に発生する可能性があります。 2)。
Undo ログ (ロールバック ログ) は、データベース システムでデータ変更操作を記録するために使用されるログで、トランザクション実行中のデータに対するすべての変更操作の逆の操作 (つまり、元に戻す操作) を記録します。 Undo ログはトランザクションのロールバックで重要な役割を果たし、Undo ログを通じてデータをトランザクション開始前の状態に復元できるため、トランザクションのアトミック性が保証されます。
トランザクションのアトミック性とは、トランザクションのすべての操作がすべて実行されるか、何も実行されないかのいずれかであることを意味します。 Undo Log は、次のメカニズムを通じてトランザクションのアトミック性を保証します。
元に戻す操作を記録する トランザクションの実行中、データに対する変更操作は、実際の変更の前に、対応する元に戻す操作を元に戻すログに記録されます。たとえば、トランザクションがレコードの行の値を更新する場合、更新する前に古い値が Undo ログに記録されます。
トランザクションのロールバック 何らかの理由（エラーや明示的なロールバックなど）でトランザクションが失敗した場合、データベース システムは元に戻すログを読み取り、記録された元に戻す操作に従ってデータをトランザクション開始前の状態に復元します。このようにして、失敗したトランザクションがデータベースに影響を与えないことを保証できるため、トランザクションのアトミック性が保証されます。
引用：
彼は 30 年以上テクノロジーの研究に専念しており、java、linux、javascript、php、css などのさまざまな言語に堪能であり、オープンソース分野で多くの貢献を行っています。将来の参考のために技術開発におけるいくつかの問題を共有する開発者ドキュメント ステーション。
送料メール: