MySQLクエリ速度が遅く、パフォーマンスが低下する原因と解決策

1. データベースクエリの速度に影響を与えるものは何ですか?

1.1 データベースクエリ速度に影響を与える4つの要因

1.2 リスク分析

QPS: QueriesPerSecond は「1 秒あたりのクエリ レート」を意味し、サーバーが 1 秒あたりに応答できるクエリの数です。これは、特定のクエリ サーバーが指定された時間内に処理するトラフィックの量の測定値です。

TPS: TransactionsPerSecond の略で、1 秒あたりのトランザクション数を意味します。ソフトウェアテストの結果を測定する単位です。クライアントはリクエストを送信したときに計測を開始し、サーバーから応答を受信した後に計測を終了し、経過時間と完了したトランザクションの数を計算します。

ヒント: 大規模なイベントの前に、メイン データベースでデータベースをバックアップせず、そのような計画をキャンセルすることをお勧めします。

非効率的な SQL: 非常に高い QPS と TPS。同時実行数が多い: データ接続数がいっぱいです (max_connection のデフォルト値は 100 で、接続数は通常もっと大きく設定されます)。 同時実行性: データベース サーバーが同時に処理する要求の数。CPU 使用率が非常に高い: CPU リソースが使い果たされ、サーバーがクラッシュします。ディスク IO: ディスク IO パフォーマンスが突然低下し、スケジュールされたタスクによってディスク パフォーマンスが大量に消費されます。解決策: ディスク デバイスの高速化、スケジュールされたタスクの調整、ディスクのメンテナンスの実行。

1.3 ネットワークカードのトラフィック: データベースに接続できない状況を回避する方法

スレーブサーバーの数を減らす（スレーブサーバーはマスターサーバーからログをコピーします）階層的なキャッシュを実行する（フロントエンドでの大規模なキャッシュ障害を回避するため）クエリにselect*を使用しないようにするビジネスネットワークとサーバーネットワークを分離する

1.4 大きなテーブルによって生じる問題（重要）

1.4.1 大きなテーブルの特徴

レコード数は膨大で、1つのテーブルに1000万行以上あります。データファイルは巨大で、10Gを超えています。

1.4.2 大きなテーブルの危険性

1. 遅いクエリ: 短時間で必要なデータをフィルタリングすることが困難です。クエリワードの識別性が低い -> 大きなデータテーブルから一部のデータをフィルタリングすると、大量のディスクIOが発生します -> ディスク効率が低下します

2. DDLへの影響:

インデックスの構築には長い時間がかかります。

MySQL-v<5.5 インデックスを作成するとテーブルがロックされます MySQL-v>=5.5 インデックスを作成するとマスタースレーブ遅延が発生します (mysql は最初にグループにインデックスを作成し、次にデータベースにインデックスを作成します)

テーブル構造を変更するには長いテーブルロックが必要です。これにより、マスタースレーブ間の長い遅延が発生します (「480 秒の遅延」)

1.4.3 データベース内の大きなテーブルの扱い方

大きなテーブルを複数の小さなテーブルに分割する

困難：

シャードテーブルの主キーの選択 シャーディング後のクロスパーティションデータのクエリと統計

1.5 大規模トランザクションの問題（重要*）*

1.5.1 トランザクションとは何ですか?

1.5.2 トランザクションのACID特性

1. 原子性: すべて成功し、すべてのロールバックは失敗します。銀行預金と引き出し。

2. 一貫性: 銀行振込の合計金額は変更されません。

3. 隔離:

分離レベル:

非コミット読み取り (READ UNCOMMITED) はダーティ読み取りです。2 つのトランザクションは相互に可視です。コミット読み取り (READ COMMITED) は、分離の基本概念に準拠しています。トランザクションが進行中の場合、他のコミットされたトランザクションはそのトランザクションに可視であり、つまり、他のトランザクションによってコミットされたデータを取得できます。繰り返し読み取り (REPEATABLE READ) InnoDB のデフォルトの分離レベル。トランザクションが進行中の場合、他のすべてのトランザクションはトランザクションからは見えません。つまり、複数の読み取りによって得られる結果は同じです。 SERIALIZABLE は読み取られたデータの各行をロックするため、多数のロック タイムアウトとロック要求が発生する可能性があります。厳密なデータ一貫性と同時実行なしで使用できます。

システムのトランザクション分離レベルを表示します: '%iso%' のような変数を表示します。

新しいトランザクションを開始します: begin;

トランザクションを送信します: commit;

トランザクションの分離レベルを変更します: setsession tx_isolation='read-committed';

4. 耐久性: データベースの観点から見ると、ディスクが破損している場合は永続性が維持できません。

redologメカニズムはトランザクション更新の一貫性と永続性を保証します。

1.5.3 大規模トランザクション

実行に長い時間がかかり、大量のデータを必要とするトランザクション。

リスク: ロックされたデータが多すぎる、ロールバック時間が長い、実行時間が長い。

ロックするデータが多すぎると、ブロックやロック タイムアウトが頻繁に発生し、ロールバックに長い時間がかかり、データはロックされたままになります。実行時間が長いと、マスター サーバーがすべてのログ書き込みを完了したときにのみスレーブ サーバーが同期を開始するため、マスター スレーブ間の遅延が発生し、遅延が発生します。

解決：

データをバッチ処理して一度に大量のデータを処理しないようにします。不要な SELECT 操作を削除して、必要な書き込み操作のみがトランザクションに含まれるようにします。 2. MySQL のパフォーマンスに影響を与えるもの (非常に重要)

2.1 パフォーマンスに影響を与えるいくつかの側面

サーバーハードウェア。サーバーシステム（システムパラメータの最適化）。ストレージ エンジン。 MyISAM: トランザクションまたはテーブルレベルのロックはサポートされません。 InnoDB: トランザクション、行レベルのロック、トランザクション ACID をサポートします。データベースパラメータの構成。データベース構造の設計と SQL ステートメント。 （キー最適化）

2.2 MySQLアーキテクチャ

3つのレイヤーに分かれています: クライアント->サービスレイヤー->ストレージエンジン

MySQL はプラグイン ストレージ エンジンであり、多くの種類があります。 MySQL ストレージ エンジンに準拠したインターフェイスを実装すれば、独自のストレージ エンジンを開発できます。ストレージ エンジン間の機能はすべてサービス レイヤーに実装されています。 MySQL のストレージ エンジンはライブラリ用ではなく、テーブル用です。つまり、1 つのデータベースで異なるストレージ エンジンを使用できます。しかし、これはお勧めできません。

2.3 InnoDB ストレージエンジン

MySQL 5.5 以降のバージョンのデフォルトのストレージ エンジンは InnoDB です。

2.3.1 InnoDB はデータ保存にテーブルスペースを使用します。

'innodb_file_per_table'のような変数を表示する

innodbfileper_table が ON の場合、独立したテーブルスペースが作成され、ファイルは tablename.ibd になります。

innodbfileper_table が OFF の場合、データはシステムの共有テーブルスペースに保存され、ファイルは ibdataX (X は 1 から始まる整数) になります。

.frm: サーバー レベルで生成されるファイルで、サーバー レベルのデータ ディクショナリに似ており、テーブル構造を記録します。

2.3.2 (MySQL 5.5 のデフォルト) システム テーブルスペースと (MySQL 5.6 以降のデフォルト) 独立テーブルスペース

1.1 システム テーブルスペースではファイル サイズを単純に縮小することはできないため、スペースが無駄になり、大量のディスク断片化が発生します。 1.2 独立した表領域では、サーバーを再起動せずに、また表への通常のアクセスに影響を与えることなく、opize table コマンドを使用してシステム ファイルを縮小できます。 2.1 複数のテーブルが更新される場合は、実際には順番に実行されるため、IO ボトルネックが発生します。 2.2 独立した表領域は、複数のファイルに同時にデータを更新できます。

最適化をより便利かつ制御しやすくするために、Innodb には別のテーブルスペースを使用することを強くお勧めします。

2.3.3 システム表領域内のテーブルを独立した表領域に転送する方法

1. mysqldump を使用してすべてのデータベース データをエクスポートします (ストアド プロシージャ、トリガー、スケジュールされたタスクは一緒にエクスポートする必要があります)。これはスレーブ サーバーで実行できます。 2. MYSQL サーバーを停止し、パラメータを変更し (innodbfileper_table を my.cnf に追加)、Inoodb 関連ファイルを削除します (データ ディレクトリを再構築できます)。 3. MYSQL を再起動し、Innodb システム テーブルスペースを再構築します。 4. データを再インポートします。

または、Altertable も転送できますが、システム テーブルスペースによって占有されている領域を再利用することはできません。

2.4 InnoDBストレージエンジンの機能

2.4.1 機能 1: トランザクション ストレージ エンジンと 2 つの特別なログ タイプ: Redo ログと Undo ログ

Innodb はトランザクション ストレージ エンジンです。トランザクションの ACID 特性を完全にサポートします。トランザクションに必要な 2 つの特別なログ タイプ (RedoLog と UndoLog) をサポートします。

Redo ログ: トランザクションの永続性 (コミットされたトランザクション) を実現します。 UNDO ログ: テーブルスペースから独立したコミットされていないトランザクションはランダム アクセスを必要とし、高性能 IO デバイスに保存できます。

Undo ログは、特定のデータが変更される前の値を記録し、トランザクションが失敗した場合にロールバックするために使用できます。Redo ログは、特定のデータ ブロックが変更された後の値を記録し、データ ファイルに書き込まれていない、成功したトランザクションによって更新されたデータを回復するために使用できます。

2.4.2 機能2: 行レベルロックのサポート

InnoDB は行レベルのロックをサポートしています。行レベルのロックにより同時実行性のサポートを最大化できます。行レベルのロックは、ストレージ エンジン レイヤーによって実装されます。

2.5 ロックとは何ですか?

2.5.1 ロック

2.5.2 ロックの種類

2.5.3 ロックの粒度

MySQL トランザクション サポートは、MySQL サーバー自体にバインドされているのではなく、ストレージ エンジンに関連しています。

テーブルレベルのロック コマンドを table_name に追加します: locktable table_name write; 書き込みロックは、書き込みロックが解除されるまで、テーブルに対する他のユーザーの「読み取りおよび書き込み」操作をブロックします: unlock tables;

ロックのオーバーヘッドが大きいほど、粒度は小さくなり、同時実行性が高くなります。テーブルレベルのロックは通常、サーバーレベルで実装されます。行レベルのロックはストレージ エンジン レイヤーで実装されます。 InnoDBのロック機構はサーバー層には知られていない

2.5.4 ブロッキングとデッドロック

（１）閉塞とは、リソース不足により発生する待ち行列現象である。 （２）デッドロックは、２つのオブジェクトがすでに別のリソースを所有している状態でリソースを要求し、その別のリソースが両方のオブジェクトによって保持されている場合に発生し、その結果、２つのオブジェクトは操作を完了できず、保持しているリソースを解放できなくなります。

2.6 適切なストレージエンジンを選択する方法

基準条件:

トランザクションバックアップ（Innobd無料オンラインバックアップ）クラッシュリカバリストレージエンジン固有の機能

要約: Innodb は素晴らしいです。

注意: 混合ストレージ エンジンを使用しないようにしてください。たとえば、ロールバックによりオンライン ホット スタンバイで問題が発生します。

2.7 設定パラメータ

2.7.1 メモリ構成関連パラメータ

使用できるメモリの上限を決定します。

メモリ使用量の上限は物理メモリを超えることはできません。超えると、メモリ オーバーフローが発生しやすくなります。(32 ビット オペレーティング システムの場合、MySQL は 3G 未満のメモリしか試すことができません。)

各 MySQL 接続で使用されるメモリを個別に決定します。

sort_buffer_size #各スレッドのソート バッファのサイズを定義します。MySQL は、クエリがあり、ソート操作を実行する必要がある場合にのみ、各バッファにメモリを割り当てます (このパラメータのすべてのメモリを直接割り当てます) join_buffer_size #各スレッドで使用される接続バッファのサイズを定義します。クエリが複数のテーブルに関連付けられている場合、MySQL は各テーブルに接続バッファを割り当てるため、1 つのクエリに対して複数の接続バッファが生成されます read_buffer_size #MyISAM でフル テーブル スキャンが実行されるときに割り当てられる読み取りバッファ プールのサイズを定義します。MySQL は、クエリで必要なときにメモリを割り当てます。メモリは 4k の倍数である必要があります。 read_rnd_buffer_size #インデックス バッファ サイズ。MySQL は、クエリで必要なときにメモリを割り当てます。必要なサイズのみを割り当てます。

注: 上記の 4 つのパラメータは 1 つのスレッドに割り当てられます。接続が 100 個ある場合は、×100 が必要です。

MySQL データベースインスタンス:

①MySQLはシングルプロセス・マルチスレッド（Oracleはマルチプロセス）であり、MySQLインスタンスはシステム上のサービスプロセス、つまりプロセスであることを意味します。

②MySQLインスタンスはスレッドとメモリで構成されており、実際にデータベースファイルの操作に使用されます。

通常、1 つのインスタンスが 1 つ以上のデータベースを操作します。クラスターでは、複数のインスタンスが 1 つ以上のデータベースを操作します。

キャッシュ プールにメモリを割り当てる方法:

Innodb_buffer_pool_size は、Innodb が使用するバッファ プールのサイズを定義します。これはパフォーマンスにとって非常に重要であり、十分な大きさでなければなりません。ただし、大きすぎると、Innodb が閉じられたときに、バッファ プールからディスクにダーティ ページをフラッシュするのに時間がかかります。

合計メモリ - (各スレッドに必要なメモリ * 接続数) - システム予約メモリ

key_buffer_size は、MyISAM が使用するキャッシュ プールのサイズを定義します。データはストレージ オペレーティング システム キャッシュに依存するため、オペレーティング システム用にさらにメモリ領域を予約する必要があります。

engine='myisam' の場合、information_schema.talbes から sum(index_length) を選択します。

注意: 開発に使用されるすべてのテーブルが Innodb テーブルであっても、MySQL システムで使用されるテーブルは依然として MyISAM テーブルであるため、MyISAM 用にメモリを予約する必要があります。

max_connections は、許可される接続の最大数を制御します。通常は 2000 以上です。

データの整合性を確保するために外部キー制約を使用しないでください。

2.8 パフォーマンス最適化の順序

上から下へ: