技術共有

【インタビューの質問】MySQL（その4）

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

1. MySQL ステートメントを実行する手順について詳しく説明します。

サーバー層が SQL を順番に実行する手順は次のとおりです。

クライアント要求 -> コネクタ (ユーザー ID を確認し、権限を付与) クエリ キャッシュ (キャッシュが存在する場合は直接返し、存在しない場合は後続の操作を実行) アナライザー (SQL の字句解析と構文解析を実行) オプティマイザー (主に実行 SQL 最適化メソッドを実行して、最適な実行プラン) エグゼキューター (実行時に、最初にユーザーに実行権限があるかどうかを確認し、次にこのエンジンによって提供されるインターフェイスを使用します) -> エンジン層に移動してデータの戻り値を取得します (クエリ キャッシュがオンになっている場合、クエリ結果をキャッシュします)

2.バッファプール

バッファ プールは、MySQL データベースの InnoDB ストレージ エンジンの重要な部分であり、主にテーブル データとインデックス データをキャッシュして、ディスク I/O 操作を削減し、データベース処理効率を向上させるために使用されます。以下は、バッファー プールの詳細な分析です。

1. 基本的な考え方

  • 意味: バッファ プールは InnoDB ストレージ エンジンのメモリ領域で、ディスクへの直接アクセスを減らすためにディスク上のデータ ページとインデックス ページをキャッシュするために使用されます。

  • 効果: キャッシュ メカニズムにより、データ アクセス速度が向上し、ディスク I/O コストが削減されます。

  • 構成 : バッファ プールは、キャッシュされたデータ ページ (ページ) と対応する制御ブロックで構成されます。制御ブロックには、キャッシュ ページが属するテーブル スペース、データ ページ番号、バッファ プール内のキャッシュ ページのアドレスなど、キャッシュ ページのメタデータ情報が格納されます。

2. サイズと構成

  • デフォルトのサイズ: MySQL のバッファ プールのデフォルト サイズは通常 128MB です (ただし、MySQL のバージョンや構成が異なると、デフォルト サイズが異なる場合があることに注意してください)。

  • 設定パラメータ：合格innodb_buffer_pool_sizeパラメータでバッファ プールのサイズを設定できます。通常は、システム メモリの 60% ～ 80% に設定することをお勧めします。

  • メモリ割り当て: バッファ プールは連続的なメモリ空間であり、MySQL が一定期間実行されると、このメモリ空間には空きキャッシュ ページと使用済みキャッシュ ページの両方が存在します。

3. データページの種類と管理

  • タイプ

    : バッファ プール内のデータ ページは、その状態に応じて、フリー ページ、クリーン ページ、ダーティ ページの 3 つのタイプに分類できます。

    • フリーページ: 使用されていないキャッシュページ。

    • クリーン ページ: 使用されているものの、データは変更されていないキャッシュ ページ。

    • ダーティ ページ: 使用され、データが変更されたキャッシュ ページ。そのデータはディスク上のデータと一致しません。

  • 管理

    : InnoDB は、次の 3 つのリンク リスト構造を通じてこれらのキャッシュ ページを管理します。

    • フリーリンクリスト：フリーページを管理し、フリーキャッシュページの制御ブロック情報を記録します。

    • LRU リンク リスト: クリーン ページとダーティ ページを管理し、改良された LRU アルゴリズムを使用し、キャッシュ ヒット率を最適化するために若い領域と古い領域に分割します。

    • フラッシュ リンク リスト: ディスクにフラッシュする必要があるダーティ ページを、変更時間順に並べて管理します。

4. 作動機構

  • データアクセス : データ ページにアクセスする必要がある場合、InnoDB はまずそのページが既にバッファ プールにあるかどうかを確認します。すでに存在する場合はそのページが直接使用され、存在しない場合はページがディスクからバッファ プールに読み取られ、対応するリンク リストが更新されます。

  • データ更新: データ ページが変更されると、そのページはダーティ ページとしてマークされ、バックグラウンド スレッドがページをディスクにフラッシュするのを待つためにフラッシュ リンク リストに追加される場合があります。

  • キャッシュの削除: バッファプールのスペースが不十分な場合、LRU アルゴリズムに従って最も最近使用されていないキャッシュ ページが削除されます。

5. 最適化と注意事項

  • サイズを適切に設定する: システムメモリとデータベースの負荷状況に基づいた合理的な設定innodb_buffer_pool_sizeパラメータ。

  • 監視と調整: バッファプールの使用状況とパフォーマンス指標を定期的に監視し、必要に応じて調整します。

  • フルテーブルスキャンを回避する: フル テーブル スキャンでは、大量のデータ ページがバッファ プールにロードされるため、キャッシュ ヒット率が低下します。

要約すると、バッファ プールは、MySQL データベースの InnoDB ストレージ エンジンの重要なコンポーネントの 1 つであり、適切な構成と管理を通じて、データベースのパフォーマンスと効率を大幅に向上させることができます。

3.MySQLプロセス

MySQL プロセスには、クライアントと MySQL サーバー間の接続から始まり、SQL ステートメントの実行、最適化、データ読み取り、結果の返しまでの複数のリンクが含まれます。以下は、MySQL プロセスの詳細な概要です。

1. 接続と認証

  1. コネクタ (接続マネージャー):

    • クライアント (アプリケーションやコマンド ライン ツールなど) が MySQL サーバーへの接続を要求すると、MySQL のコネクタがこれらの接続要求を処理します。

    • コネクタはクライアントの ID と権限を検証します。これには通常、ユーザー名とパスワードが一致するかどうかのチェックが含まれます。

    • 検証が成功すると、コネクタは後続の SQL 操作のためにクライアントにスレッド (またはセッション) を割り当てます。

2. クエリ処理

  1. クエリ キャッシュ (クエリ キャッシュ、注: このモジュールは MySQL 8.0 で削除されました):

    • SELECT クエリの場合、MySQL は最初に同じクエリとその結果がクエリ キャッシュに存在するかどうかを確認します。

    • 存在する場合、MySQL は結果をキャッシュに直接返し、実際のクエリ操作の実行を回避します。

    • ただし、クエリ キャッシュはデータの不整合を引き起こす可能性があるため (たとえば、キャッシュされたデータが他のトランザクションによって変更されている可能性があります)、クエリ キャッシュ機能は MySQL 8.0 では削除されました。

  2. パーサー:

    • クライアントによって送信された SQL ステートメントは、まずパーサーに送信されます。

    • パーサーのタスクは、SQL ステートメントを解析し、その構文が正しいかどうかを確認し、それを内部データ構造 (解析ツリーや構文ツリーなど) に変換することです。

    • SQL ステートメントに構文エラーがある場合、パーサーはクライアントにエラー情報を返します。

  3. プリプロセッサ：

    • 一部の MySQL バージョンまたは特定のシナリオでは、プリプロセッサ段階が存在する場合があります。

    • プリプロセッサは主に、テーブルまたはフィールドが存在するかどうかの確認、SELECT ステートメント内の * をテーブル内のすべての列に展開するなど、SQL ステートメントのさらなる処理を担当します。

  4. オプティマイザ：

    • オプティマイザーは、SQL ステートメントのさまざまな実行計画を評価し、最適な実行計画を選択する責任があります。

    • オプティマイザーは、使用可能なインデックス、結合方法の効率、クエリのコストなど、さまざまな要素を考慮します。

    • オプティマイザーは、インデックスの使用、クエリの並べ替え、クエリの結合などの操作を通じてクエリのパフォーマンスを大幅に向上させることができます。

  5. 執行者:

    • エグゼキュータは、オプティマイザによって生成された実行プランに基づいて実際のクエリ操作を実行します。

    • エグゼキュータは、ストレージ エンジン (InnoDB など) のインターフェイスを呼び出して、データ テーブル内のデータを読み取り、並べ替え、集計、フィルタリングなどの操作を実行します。

    • 最後に、エグゼキュータはクエリ結果をクライアントに返します。

3. データの保存と取得

  • ストレージ エンジン:

    • MySQL は複数のストレージ エンジンをサポートしており、各ストレージ エンジンには独自の固有のデータ ストレージおよび取得メソッドがあります。

    • InnoDB は MySQL のデフォルトのストレージ エンジンの 1 つであり、トランザクション処理、行レベルのロック、外部キーなどの高度なデータベース機能をサポートしています。

    • エグゼキュータがストレージ エンジンのインターフェイスを呼び出すと、ストレージ エンジンはディスクからのデータの読み取りまたはディスクへのデータの書き込みを行います。

  • バッファプール：

    • InnoDB ストレージ エンジンは、バッファ プールを使用してテーブル データとインデックス データをキャッシュし、ディスクへの直接アクセスを減らします。

    • バッファ プール内のデータ ページは、アクセス頻度と変更ステータスに基づいて管理され、キャッシュ ヒット率とクエリ パフォーマンスが向上します。

4. トランザクション処理

  • 取引：

    • MySQL はトランザクション処理をサポートしているため、複数の操作を全体としてコミットまたはロールバックできます。

    • トランザクションの実行中、MySQL はデータの整合性と一貫性を確保するために必要なログ情報 (REDO ログや UNDO ログなど) を記録します。

    • トランザクションの実行が成功した場合、すべての変更はデータベースに永続的に保存されます。トランザクションの実行が失敗した場合は、アンドゥ ログを使用してロールバック操作を実行し、データをトランザクション開始前の状態に復元できます。

5. まとめ

MySQL のプロセスには、接続と認証、クエリ処理、データの保存と取得、およびトランザクション処理が含まれます。これらのリンクの各ステップを最適化することで、MySQL データベースのパフォーマンスと信頼性を大幅に向上させることができます。同時に、MySQL の実行プロセスを理解することは、その内部動作メカニズムをより深く理解するのにも役立ち、それによってデータベースの設計と最適化をより適切に行うことができます。

4.MySQL接続プール

MySQL の接続プールは、データベース接続の管理と再利用に使用されるテクノロジーで、特に同時実行性の高い環境でのデータベース操作のパフォーマンスと効率を向上させるように設計されています。以下は、MySQL 接続プールについての詳細な説明です。

1.コンセプト

MySQL 接続プールは、プログラムの起動時に十分な数のデータベース接続を確立し、これらの接続を均一に管理して接続プールを形成します。プログラムがデータベースにアクセスする必要がある場合、操作ごとに接続を再作成して閉じるのではなく、接続プールから接続を動的に申請し、使用後に接続を接続プールに返します。

2. 接続プーリングを使用する理由は何ですか?

  1. リソース消費量の削減 : データベース接続の作成と終了は、TCP 接続の 3 方向ハンドシェイクと 4 方向ウェーブ、およびデータベース認証プロセスを伴う、比較的時間のかかるプロセスです。接続プーリングを通じて、既存の接続を再利用して、これらのオーバーヘッドを削減できます。

  2. 性能を上げる : 同時実行性が高いシナリオでは、リクエストごとに新しいデータベース接続が作成されると、サーバーのパフォーマンスが大幅に低下します。接続プールを使用すると、データベースの応答速度とスループットが大幅に向上します。

  3. 接続漏れを避ける注: 接続プールを使用しない場合、プログラムが接続を閉じるときに例外が発生すると、接続リークが発生する可能性があります。つまり、接続が正しく閉じられず、システム リソースが占有される可能性があります。接続プールは、タイムアウト リサイクル メカニズムを通じてこの状況を回避できます。

3. 接続プールの動作原理

  1. 初期化: プログラムが開始されると、接続プールは構成に従って一定数のデータベース接続を作成し、これらの接続をバッ​​クアップ用に接続プールに入れます。

  2. 接続を申請する : プログラムがデータベースにアクセスする必要がある場合、接続プールから接続を申請します。接続プールにアイドル状態の接続がある場合は、プログラムに直接返されて使用されます。アイドル状態の接続がない場合は、設定に従って一定時間待機するか、エラーを返します。

  3. 接続を使用する: プログラムは、要求された接続を使用してデータベース操作を実行します。

  4. 戻り接続 : 操作が完了すると、プログラムは接続を接続プールに返します。接続プールは接続に対して特定のチェックを実行し、接続がまだ有効な場合は接続プールに戻され、接続の有効期限が切れた場合は接続が閉じられ、接続プールから削除されます。

  5. 接続プールを閉じる: プログラムが終了すると、接続プール内のすべての接続が閉じられ、占有されていたシステム リソースが解放されます。

4. 接続プールプロバイダー

市場には多くの MySQL 接続プール プロバイダーがあり、その中で最も人気のあるプロバイダーは次のとおりです。

  • DBCP : これは、Apache プロジェクトの下でのオープンソースの接続プール実装であり、Tomcat に付属する接続プールです。他の接続プールよりも高速ですが、十分に安定していない可能性があります。

  • C3P0 : これは、データ ソースと JNDI バインディングを実装し、JDBC3 標準と JDBC2 標準拡張をサポートするオープン ソースの JDBC 接続プールです。 C3P0 の速度は比較的遅いですが、非常に安定しています。

  • ドルイド (Druid): Alibaba が提供するオープンソースの接続プールで、DBCP と C3P0 の利点を組み合わせ、強力な監視機能と拡張機能を提供します。 Druid は現在、最も一般的に使用されている MySQL 接続プールの 1 つです。

5. 接続プールの構成

接続プールの構成には通常、次の側面が含まれます。

  • 最大接続数: 接続プールが管理できる接続の最大数。

  • 最小接続数: 接続プールの開始時に作成される初期接続数。

  • 接続タイムアウトを取得する: 接続プールから接続を取得するときに待機する最大時間。

  • 接続の検証: 接続を取得する前、または接続を返すときに、接続の有効性を検証します。

  • 接続リサイクル戦略: アイドル時間と使用時間に基づいて接続をリサイクルします。

6. コネクションプールとスレッドプールの関係

接続プーリングとスレッド プーリングは 2 つの異なるリソース プーリング テクノロジですが、それらの間には一定の関係があります。スレッド プールは主にスレッド リソースの管理に使用され、接続プールはデータベース接続リソースの管理に使用されます。スレッド プール内のスレッドはデータベース操作を実行する必要がある場合、接続プールに接続を申請します。操作が完了すると、接続は接続プールに返されます。この関係は、リソースの効率的な利用と管理の簡素化を実現するのに役立ちます。

要約すると、MySQL 接続プールは重要なデータベース接続管理テクノロジであり、接続を再利用し、リソース消費を削減し、パフォーマンスを向上させることでデータベース操作を強力にサポートします。実際のアプリケーションでは、プロジェクトの特定のニーズとシナリオに応じて、適切な接続プール プロバイダーと構成パラメーターを選択できます。

MySQL ログに関連する面接の質問は、タイプ、役割、構成、ログの最適化、データ リカバリやデータ レプリケーションなどにおけるログのアプリケーションなど、多くの側面をカバーする可能性があります。以下は、MySQL ログ関連の一般的な面接の質問とその詳細な回答です。

5. MySQL の一般的なログは何ですか?それぞれの機能は何ですか?

MySQL の一般的なログには次のものが含まれます。

  • エラーログ : MySQL サーバーの起動、実行、停止時のエラー情報と、重大なエラー情報を記録します。これは問題の診断に役立ちます。

  • クエリログ（一般ログ） : ユーザーのログインアクティビティ、実行された SQL ステートメントなどを含む、MySQL サーバーが受信したすべてのクライアントのリクエストと応答を記録します。通常、監査またはデバッグに使用されます。

  • スロークエリログ ：実行時間が閾値を超えたSQL文と、その実行時間、アクセスしたテーブル、使用したインデックスなどを記録します。パフォーマンスのチューニングとクエリの最適化に使用されます。

  • バイナリログ（略してBinlog）: データベース データを変更するすべてのステートメント (SELECT や SHOW などのステートメントを除く) を記録します。主にレプリケーションとデータ リカバリに使用されます。

  • やり直しログ: InnoDB ストレージ エンジンでは、システム クラッシュが発生した場合でも、REDO ログを通じてデータを回復できるため、トランザクションの耐久性を確保するために使用されます。

  • アンドゥログ: InnoDB ストレージ エンジンでは、トランザクションの開始前にデータの状態を記録するために使用されます。これにより、トランザクションが失敗するかロールバックしたときに、データをトランザクションの開始前の状態に復元できます。

  • リレーログ: MySQL レプリケーション アーキテクチャでは、スレーブ サーバー上のリレー ログは、マスター サーバーから受信したバイナリ ログの内容を保存するために使用されます。

6. スロークエリログを有効にして設定するにはどうすればよいですか?

スロー クエリ ログは、MySQL 構成ファイル (my.cnf や my.ini など) を通じて開いて構成することも、SQL コマンドを通じて動的に設定することもできます。

  • 設定ファイルの方法:

    • MySQL 構成ファイルに次のパラメータを追加または変更します。

      [mysqld]  
slow_query_log = 1  
slow_query_log_file = /path/to/your/slow-query.log  
long_query_time = 2

      で、

      slow_query_log

      スロークエリログを有効にするために使用されます。

      slow_query_log_file

      スロークエリログファイルへのパスを指定します。

      long_query_time

      スロークエリログに記録される秒数を超えるSQL文の実行時間を設定します。

    • 構成ファイルを変更した後、MySQL サービスを再起動する必要があります。

  • SQLコマンドモード:

    • スロークエリログは SQL コマンドを通じて動的に有効にできますが、slow_query_log_fileそしてlong_query_time動的設定がサポートされていないか、機能しない可能性があるため、パラメータは構成ファイルを介して設定する必要がある場合があります。

    • スロークエリログを有効にします。

      sql复制代码
​
SET GLOBAL slow_query_log = 'ON';

    • なお、SQLコマンドを使用して動的にオープンしたスロークエリログは、システム再起動後に無効になる場合があるため、設定ファイルで設定することを推奨します。

7. バイナリログ (Binlog) にはいくつの形式がありますか?それらの違いは何ですか?

バイナリ ログ (Binlog) には 3 つの形式があります。

  • 声明 : SQL ステートメントベースのレプリケーション (ステートメントベースのレプリケーション、SBR)。この形式では、MySQL は実行された SQL ステートメントをバイナリログに記録します。その利点は、ログの量が小さいことですが、関数、トリガー、ストアド プロシージャなど、マスターとスレーブのデータの不整合を引き起こす可能性のあるレプリケーションの問題が発生する可能性があります。

  • 行 : 行ベースのレプリケーション (RBR)。この形式では、MySQL は変更された行のデータ変更を記録します。これには、レプリケーションの問題を回避できるという利点がありますが、ログの量が大きくなる可能性があります。

  • 混合 : 混合ベースのレプリケーション (MBR)。 MySQL は、状況に応じて STATEMENT 形式または ROW 形式の使用を自動的に選択します。混合モードはデフォルトのモードで、両方の長所を組み合わせるように設計されています。

8. REDO ログはトランザクションの耐久性をどのように保証しますか?

REDO ログは、次の方法で InnoDB ストレージ エンジンでのトランザクションの耐久性を保証します。

  • トランザクションが送信されると、InnoDB エンジンはまずトランザクションの REDO ログをメモリ内の REDO ログ バッファにキャッシュし、同時にメモリ内の対応するデータ ページを更新します。

  • 次に、適切なタイミングで、REDO ログ バッファ内の REDO ログをディスク上の REDO ログ ファイルに書き込みます。このプロセスは非同期ですが、ディスク ブラッシングのタイミングと頻度はパラメータを設定することで制御できます。

  • システム クラッシュが発生した場合、InnoDB エンジンは起動時に REDO ログ ファイルをチェックし、その中のレコードに基づいて最後に送信されたトランザクションによって行われた変更を復元することで、データの耐久性を確保します。

9. MySQL でログ ファイルを表示および削除する方法を簡単に説明します。

ログファイルを表示する：

  • エラーログ: これは通常、MySQL 構成ファイルを参照することで実行できます。log_errorパラメータを使用してファイル パスを指定し、エラー ログ ファイルを見つけ、テキスト エディタまたはコマンド ライン ツール (次のようなもの) を使用します。tail、catなど）その内容を表示します。

個人プロフィール

彼は 30 年以上テクノロジーの研究に専念しており、java、linux、javascript、php、css などのさまざまな言語に堪能であり、オープンソース分野で多くの貢献を行っています。将来の参考のために技術開発におけるいくつかの問題を共有する開発者ドキュメント ステーション。

私の連絡先情報

郵便