MySQL アーキテクチャの研究とパフォーマンスの最適化-mysql チュートリアル-php.cn

MySQL アーキテクチャの研究とパフォーマンスの最適化

WBOY

リリース： 2024-08-11 20:32:36

オリジナル

788 人が閲覧しました

まとめ

MySQL は、C および C プログラミング言語で開発された関係データベース管理システムで、David Axmark、Allan Larsson、Michael Widenius によって設立された MySQL AB によって作成されました。最新バージョンは MySQL 9.0 です。

MySQL は、GNU ライセンスを持つオープンソースプロジェクトです。

MySQL は、オープンソースであり、フル機能を備え、高いパフォーマンスを備えているため、現在最も人気があり便利なデータベース管理システムです。

建築

MySQL アーキテクチャ図: ソース

Research for MySQL architecture and Optimize performance

クライアント層

MySQL システムアーキテクチャの最初の層。
この層の重要なサービスは、接続処理、認証、セキュリティです。
- 接続処理: クライアントからの接続を管理します。クライアントが MySQL サーバーに接続したい場合、接続用に新しいスレッドが作成されます。新しい接続が作成されると、サーバーはスレッドをキャッシュします。
- 認証: クライアントがサーバーに接続するときは、接続を確立するための認証情報を提供する必要があります。通常、クライアントはユーザー名とパスワードを使用して認証します。
- セキュリティ: MySQL は、データベースを保護し、認証後にユーザーの権限を確認するための多くの機能を提供します。

サーバー層

MySQL の次の層は、クエリステートメントの処理と多くのユーティリティの提供を担当します。
主な機能:
- パーサー: クライアントがクエリステートメントをリクエストすると、サーバーはそれを分析してクエリツリーを作成し、クエリを書き換えて、クエリステートメントの実行に使用する順序とインデックスを定義します。
- オプティマイザー: 実行前にクエリステートメントを最適化します。オプティマイザーはどのエンジンが使用されるかを気にしませんが、ストレージエンジンは特定のクエリの最適化に影響を与える可能性があります。
- クエリキャッシュ: クエリキャッシュには、クエリステートメントの結果が保存されます。クライアントが同じクエリを再度リクエストすると、サーバーは解析と最適化の手順をバイパスして、キャッシュされた結果を返します。この機能はボトルネックの問題のため、バージョン 8.0 以降では非推奨になりました。
- サービスとユーティリティ:
  - バックアップと復元
  - セキュリティ: プロバイダーユーザーと権限システム
  - レプリケーション: これは、メインサーバーから多くの子サーバーにデータをコピーして同期するプロセスです。
  - クラスター
  - パーティショニング: 特定のロジックを使用してテーブルを多くの部分に分割します。
  - ワークベンチ: MySQL と対話して利用するために設計された視覚的なツールです。

ストレージ層

これは、データベースにデータを保存する方法に対する責任の層です。
デフォルトでは、MySQL は InnoDB ストレージエンジンを使用します。
MySQL はさまざまなストレージエンジンをサポートしています。
- InnoDB (デフォルト)
- MyISAM
- 記憶
- CSV
- アーカイブ
- ブラックホール
- マージ
- 連邦

MySQL のパフォーマンスを最適化する

SQL文の最適化

ステートメントの説明を説明します

実行を伴わないクエリ予測

explain sql_statememnt

ログイン後にコピー

クエリ実行後の説明

explain analyze sql_statement

ログイン後にコピー

インデックス作成手法

クエリと検索時のパフォーマンスが向上します。
複数の列のインデックスを作成する場合、最初の列は非常に重要です。これにより、クエリステートメントのパフォーマンスが決まります。たとえば、(名前, 誕生日) にインデックスを作成した場合、「名前」列を使用するクエリはこのインデックスを使用しますが、「誕生日」列を使用するクエリは使用しません。
新しいインデックスを作成するための構文:

create index idx_birthday on customers(birthday);

ログイン後にコピー

このコマンドは、指定されたインデックスをクエリオプティマイザーにアクセスできないようにするため、クエリ実行プランの対象として考慮されなくなります。

alter table customers alter index idx_birthday invisible;

ログイン後にコピー

パーティション

データベースのサイズが 2GB を超える (または 1,000 万レコードを超える) 場合は、そのデータベースに対してパーティショニングを使用する必要があります。
パーティショニングを使用すると、パフォーマンスが向上します。
パーティションプランは、WHERE 句で頻繁に使用される列に基づいている必要があります。

データベースパラメータの最適化

バッファキャッシュヒット

システムが物理ストレージではなくバッファキャッシュからロードするデータの割合です。
パーセンテージが 90% より低い場合は、最適化する必要があります。
2 つのパラメータに依存します:
- Innodb_buffer_pool_read_requests: バッファプールに送信されたリクエストの合計。コマンド: '%Innodb_buffer_pool_read_requests% のようなグローバルステータスを表示します。
- Innodb_buffer_pool_reads: メモリ内のバッファプールに見つからないリクエストの合計は、ディスクから読み取る必要があります。コマンド: '%Innodb_buffer_pool_reads% のようなグローバルステータスを表示
計算式:

(Innodb_buffer_pool_read_requestss - Innodb_buffer_pool_reads) x100 / Innodb_buffer_pool_read_requests

ログイン後にコピー

テーブルキャッシュヒット

MySQL はテーブルをキャッシュし、メモリ内で高速にクエリを実行します。
パーセンテージが 80% より低い場合は、最適化する必要があります。
2 つのパラメータに依存します:
- Open_tables: キャッシュ内でオープンされているテーブルの合計数。コマンド: 「Open_tables」のようなグローバルステータスを表示
- Opened_tables: オープンされているテーブルの合計。コマンド: 「Opened_tables」のようなグローバルステータスを表示
計算式: Open_tables/Opened_tables

テーブル定義キャッシュヒット

クエリを実行するとき、MySQL はテーブルの名前、列数、行数などのテーブルに関する情報を必要とします。この情報はテーブル定義と呼ばれます。
パーセンテージが 80% より低い場合は、最適化する必要があります。
2 つのパラメータに依存します:
- Open_table_settings: 定義テーブルの合計がキャッシュにあります。コマンド: 'Open_table_settings' のようなグローバルステータスを表示します。
- Opened_tables: 定義テーブルの合計が要求されます。コマンド: 「Opened_tables」のようなグローバルステータスを表示
計算式: Open_table_settings/Opened_table_settings

メモリ内の一時テーブル

Order、Group By などの多くの SQL ステートメントは一時テーブルを使用する必要があります。
パーセンテージが 80% より低い場合は、最適化する必要があります。
2 つのパラメータに依存します:
- Created_tmp_disk_tables: 合計一時テーブルがディスク上に作成されます。コマンド: '%Created_tmp_disk_tables% のようなグローバルステータスを表示
- Created_tmp_tables: 一時テーブルの合計数が作成されます。コマンド: '%Created_tmp_tables% のようなグローバルステータスを表示
計算式: (Created_tmp_tables - Created_tmp_disk_tables)/ Created_tmp_tables

結論

このブログでは、MySQL に関する研究に基づいた知識を提供します。 MySQL は、低コスト、広範なユーティリティ、高性能で知られるリレーショナルデータベース管理システムであり、幅広いプロジェクトに適しています。このブログでは MySQL の多くの機能と側面を取り上げていますが、探索すべき点はさらにあります。今後のブログで追加の機能や知識についてさらに詳しく掘り下げていきます。

読んでいただきありがとうございます。また次のブログでお会いしましょう。