MySQLデータベース最適化技術の簡単な紹介

成熟したデータベースアーキテクチャは、最初から高可用性、高スケーラビリティなどの機能を備えて設計されているわけではなく、ユーザー数が増えるにつれてインフラストラクチャが徐々に改善されていきます。この記事では主に、MySQL データベースの開発サイクルで直面する問題と最適化ソリューションについて説明します。フロントエンドアプリケーションは別として、大きく次の 5 つの段階に分けることができます。

フェーズ 1: データベーステーブルの設計

プロジェクトが承認されると、開発部門は製品部門のニーズに応じてプロジェクトを開発します。
開発エンジニアは、開発プロジェクトの開始時にテーブル構造を設計します。データベースにとって、テーブル構造の設計は非常に重要です。適切に設計されていないと、ユーザーが Web サイトにアクセスする速度に直接影響し、ユーザーエクスペリエンスが低下します。この状況に影響を与える具体的な要因としては、遅いクエリ (非効率的なクエリステートメント)、不適切なインデックス作成、データベースの輻輳 (ロック) などが挙げられます。もちろん、テスト部門には製品のテストを行ってバグを見つけるチームがあります。
開発エンジニアはそれぞれ異なることに焦点を当てているため、初期段階ではデータベース設計が合理的かどうかを検討するのではなく、機能の実装と納品をできるだけ早く完了させます。プロジェクトがオンラインになり、一定量のトラフィックが発生すると、隠れた問題が明らかになり、この時点で修正するのはそれほど簡単ではありません。

フェーズ2: データベースの展開

運用エンジニアが参加してプロジェクトを開始する時が来ました。
プロジェクトの初期段階では、通常、アクセス数は非常に少ないです。この段階では、Web + データベースの単一の展開で、約 1000 QPS (1 秒あたりのクエリレート) を処理するのに十分です。単一障害点を考慮して、高可用性を実現する必要があります。MySQL マスタースレーブレプリケーション + Keepalived を使用すると、デュアルマシンのホットスタンバイを実現できます。主流の HA ソフトウェアには、Keepalived (推奨) と Heartbeat が含まれます。

フェーズ3: データベースパフォーマンスの最適化

MySQL を通常の X86 サーバーにデプロイした場合、最適化を行わない場合、MySQL は理論上約 1500 QPS を処理できます。最適化後は、約 2000 QPS まで増加する可能性があります。そうでない場合、同時接続数が約 1,500 に達すると、データベース処理のパフォーマンスの応答が遅くなる可能性があり、ハードウェアリソースが比較的豊富です。この時点で、パフォーマンスの最適化の問題を考慮する必要があります。では、データベースのパフォーマンスを最大化するにはどうすればよいでしょうか?主にハードウェア構成、データベース構成、アーキテクチャに焦点を当てており、具体的には次のように分類されます。

3.1 ハードウェア構成

条件が許せば、SAS 機械式ハードドライブの代わりに SSD ソリッドステートドライブを使用し、RAID レベルを RAID1 や RAID5 よりも読み取りおよび書き込みパフォーマンスが優れている RAID1+0 に調整する必要があります。結局のところ、データベースへの負荷は主にディスク I/O から生じます。
Linux カーネルには、ホットデータを格納するためのキャッシュ領域 (システムキャッシュとデータキャッシュ) を物理メモリから分離する機能があります。ファイルシステムの遅延書き込みメカニズムにより、条件が満たされた場合 (キャッシュ領域のサイズが一定の割合に達した場合や、sync コマンドが実行された場合など) のみディスクに同期されます。つまり、物理メモリが大きいほど、割り当てられるバッファ領域が大きくなり、キャッシュされるデータも多くなります。もちろん、サーバー障害が発生すると、キャッシュされた特定のデータが失われます。物理メモリは少なくとも 50% の余裕を持たせることが推奨されます。

3.2 データベース構成の最適化

MySQL で最も広く使用されているストレージエンジンは 2 つあります。1 つは MyISAM で、トランザクション処理をサポートせず、読み取りパフォーマンスが速く、テーブルレベルのロックを使用します。もう 1 つは、トランザクション処理 (ACID プロパティ) をサポートし、大規模なデータ処理と行レベルのロック用に設計された InnoDB です。
テーブルロック: オーバーヘッドが低く、ロックの粒度が大きく、デッドロックの可能性が高く、同時実行性が比較的低い。
行ロック: オーバーヘッドが高く、ロックの粒度が小さく、デッドロックの可能性が低く、相対的な同時実行性が高い。
テーブルロックと行ロックはなぜ発生するのでしょうか?主にデータの整合性を確保するためです。たとえば、あるユーザーがテーブルを操作していて、他のユーザーもそのテーブルを操作したい場合、最初のユーザーが操作を終えるまで他のユーザーが操作をすることはできません。これがテーブルロックと行ロックの機能です。そうしないと、複数のユーザーが同時にテーブルを操作すると、データの競合や異常が必ず発生します。
これらの側面に基づくと、InnoDB ストレージエンジンを使用するのが最善の選択であり、MySQL 5.5 以降のデフォルトのストレージエンジンでもあります。各ストレージエンジンには、関連する動作パラメータが多数あります。次に、データベースのパフォーマンスに影響を与える可能性のあるパラメータを示します。
共通パラメータのデフォルト値:

最大接続数 = 151
# 同時に最大接続数を処理します。最大接続数は接続上限数の80%程度に設定することを推奨します。sort_buffer_size = 2M
# 並べ替えクエリを実行する際のバッファサイズは、order by と group by のみで機能しますが、16M に増やすことを推奨します。
オープンファイル制限 = 1024 
# 開いているファイルの数を制限します。show global status like 'open_files' で表示される値が open_files_limit 値以上である場合、プログラムはデータベースに接続できなくなるか、フリーズします。

MyISAM パラメータのデフォルト値:

キーバッファサイズ = 16M
# インデックスバッファサイズは、通常、物理メモリの30～40%に設定されます
読み取りバッファサイズ = 128K 
# 読み取り操作バッファサイズ。16Mまたは32Mに設定することを推奨します。
クエリキャッシュタイプ = オン
# クエリキャッシュ機能を有効にする query_cache_limit = 1M 
# クエリキャッシュの制限。結果データがキャッシュプールを上書きするのを防ぐため、1M 未満のクエリ結果のみがキャッシュされます。query_cache_size = 16M 
# SELECT クエリの結果をキャッシュするために使用されるバッファ サイズを確認します。次に同じ SELECT クエリを実行すると、結果はキャッシュ プールから直接返されます。この値は倍数で増やすことができます。

InnoDB パラメータのデフォルト値:

innodb_buffer_pool_size = 128M
# インデックスとデータバッファのサイズは、物理メモリの約 70% に設定することをお勧めします innodb_buffer_pool_instances = 1  
# バッファプールインスタンスの数。4または8に設定することを推奨します innodb_flush_log_at_trx_commit = 1 
# キー パラメータ。0 は、ログが約 1 秒ごとにディスクに書き込まれ、同期されることを意味します。データベース障害が発生すると、約 1 秒分のトランザクション データが失われます。 1. 各 SQL ステートメントが実行されると、ログに書き込まれ、ディスクに同期されます。これにより、I/O オーバーヘッドが大きくなります。SQL ステートメントを実行した後、ログの読み取りと書き込みが完了するまで待機する必要があり、非効率的です。 2 は、ログがシステム キャッシュにのみ書き込まれ、1 秒ごとにディスクに同期されることを意味します。これは非常に効率的です。サーバーに障害が発生すると、トランザクション データは失われます。データセキュリティに対する要件がそれほど高くない場合は、設定 2 をお勧めします。パフォーマンスが高く、変更後の効果は明らかです。
innodb_file_per_table = オフ 
# テーブルスペースを共有するかどうか。バージョン 5.7 以降のデフォルト設定は ON です。共有テーブルスペース内の idbdata ファイルは大きくなり続け、I/O パフォーマンスに一定の影響を与えます。独立テーブルスペース モードを有効にすることをお勧めします。各テーブルのインデックスとデータは独自の独立したテーブルスペースに保存されるため、単一のテーブルを異なるデータベース間で移動できます。
innodb_log_buffer_size = 8M 
# ログ バッファ サイズ。ログは最大で 1 秒に 1 回更新されるため、通常は 16 MB を超える必要はありません。

3.3 システムカーネルパラメータの最適化

ほとんどの MySQL サーバーは Linux システムに導入されているため、オペレーティングシステムの一部のパラメータも MySQL のパフォーマンスに影響します。以下は、Linux カーネルパラメータの適切な最適化です。

ネット.ipv4.tcp_fin_timeout = 30
#TIME_WAIT タイムアウト、デフォルトは60秒
ネット.ipv4.tcp_tw_reuse = 1  
# 1 は再利用を有効にすることを意味し、TIME_WAIT ソケットを新しい TCP 接続に再利用できるようにします。0 は無効にすることを意味します。net.ipv4.tcp_tw_recycle = 1  
# 1 は TIME_WAIT ソケットの高速リサイクルを有効にすることを意味し、0 は無効にすることを意味します net.ipv4.tcp_max_tw_buckets = 4096  
# システムは TIME_WAIT ソケットの最大数を維持します。この数を超えると、システムはランダムに TIME_WAIT をクリアし、警告メッセージを出力します。net.ipv4.tcp_max_syn_backlog = 4096
# SYN キューの最大長を入力します。キューの長さを増やすと、より多くの待機接続に対応できます。Linux システムでは、プロセスによって開かれたファイル ハンドルの数がシステムのデフォルト値の 1024 を超えると、「開いているファイルが多すぎます」というメッセージが表示されるため、開いているファイル ハンドルの制限を調整する必要があります。
永続的にするには再起動してください:
# vi /etc/security/limits.conf 
* ソフトノーファイル 65535
* ハードnofile 65535
現在のユーザーに対して即時有効:
# ulimit -SHn 65535

フェーズ4: データベースアーキテクチャの拡張

ビジネス量が増大するにつれて、単一のデータベースサーバーのパフォーマンスではビジネスニーズを満たせなくなり、サーバー拡張アーキテクチャの追加を検討する時期になります。主なアイデアは、単一のデータベースの負荷を分解し、ディスク I/O パフォーマンスを突破し、ホットデータをキャッシュに保存し、ディスク I/O アクセスの頻度を減らすことです。

4.1 キャッシュを増やす

ホットデータをメモリにキャッシュするために、データベースにキャッシュシステムを追加します。要求されたデータがキャッシュ内にある場合、MySQL は要求されなくなり、データベースの負荷が軽減されます。キャッシュ実装には、ローカルキャッシュと分散キャッシュの 2 種類があります。ローカルキャッシュは、ローカルサーバーのメモリまたはファイルにデータをキャッシュします。分散キャッシュは大量のデータをキャッシュでき、スケーラビリティに優れています。主流の分散キャッシュシステムは、memcached と redis です。memcached はパフォーマンスが安定しており、データはメモリにキャッシュされ、速度が非常に高速です。理論上の QPS は約 80,000 に達します。データの永続性が必要な場合は、Redis を選択してください。パフォーマンスは memcached よりも劣りません。
作業工程:

4.2 マスタースレーブレプリケーションと読み取り書き込み分離

実稼働環境では、ビジネスシステムは通常、読み取りが多く、書き込みが少ないため、マスタースレーブアーキテクチャを導入できます。マスターデータベースは書き込み操作を担当し、デュアルマシンのホットスタンバイを実行し、複数のスレーブデータベースは負荷分散を実行し、読み取り操作を担当します。主流のロードバランサー: LVS、HAProxy、Nginx。
読み取りと書き込みの分離を実現するにはどうすればよいでしょうか?ほとんどの企業では、コードレベルで読み取りと書き込みの分離を実装しており、非常に効率的です。もう 1 つの方法は、プロキシプログラムを介して読み取りと書き込みの分離を実現することですが、これは企業ではあまり使用されておらず、ミドルウェアの消費量が増加します。主流のミドルウェアプロキシシステムには、MyCat、Atlas などがあります。
この MySQL マスター/スレーブレプリケーショントポロジアーキテクチャでは、単一マシンの負荷が分散され、データベースの同時実行機能が大幅に向上します。 1 台のスレーブサーバーが 1500 QPS を処理できる場合、3 台で 4500 QPS を処理でき、水平方向に簡単に拡張できます。
大量の書き込み操作を実行するアプリケーションの場合、単一のマシンの書き込みパフォーマンスではビジネス要件を満たせないことがあります。双方向レプリケーション（デュアルマスター）を行うことができますが、注意すべき問題が 1 つあります。両方のマスターサーバーが外部に読み取りおよび書き込み操作を提供する場合、データの不整合が発生する可能性があります。その理由は、プログラムが 2 つのデータベースを同時に操作する可能性があるためです。同時更新操作により、2 つのデータベースのデータに競合または不整合が発生します。
各テーブル ID フィールドを自動増分および一意 (auto_increment_increment および auto_increment_offset) に設定したり、ランダムな一意の番号を生成するアルゴリズムを記述したりすることもできます。
過去 2 年間に公式によって開始された MGR (マルチマスターレプリケーション) クラスターも検討できます。

4.3 データベースシャーディング

データベースシャーディングとは、データベース内の関連テーブルを、Web、BBS、ブログ、その他のデータベースなど、業務に応じて異なるデータベースに分割することです。業務量が多い場合は、分離されたデータベースをマスター/スレーブレプリケーションアーキテクチャとして使用して、単一のデータベースへの過度の負荷をさらに回避できます。 4.4 サブテーブル

データ量が劇的に増加しています。データベースのテーブルには数百万のデータがあり、クエリと挿入に時間がかかりすぎます。単一のテーブルの負荷をどう解決すればよいでしょうか?単一のテーブルにかかる負荷を軽減し、処理効率を向上させるには、このテーブルを複数の小さなテーブルに分割することを検討する必要があります。この方法は、テーブルシャーディングと呼ばれます。
テーブルシャーディングテクノロジはより複雑です。プログラムコード内の SQL ステートメントを変更し、他のテーブルを手動で作成する必要があります。また、マージストレージエンジンを使用してテーブルシャーディングを実装することもできます。これは比較的簡単です。テーブルが分割された後、プログラムはマスターテーブルで動作します。このマスターテーブルにはデータは保存されず、サブテーブル間の関係とデータの更新方法のみが保存されます。マスターテーブルは、さまざまなクエリに応じてさまざまな小さなテーブルに負荷を分散し、同時実行性とディスク I/O パフォーマンスを向上させます。
分割テーブルは垂直分割と水平分割に分かれています。
垂直分割: 多くのフィールドを含む元のテーブルを複数のテーブルに分割して、テーブル幅の問題を解決します。あまり使用されないフィールドを別のテーブルに配置したり、大きなフィールドを別のテーブルに配置したり、密接に関連するフィールドをテーブルに配置したりすることができます。
水平分割: 元のテーブルを、それぞれ同じ構造を持つ複数のテーブルに分割し、単一のテーブルに大量のデータが存在する問題を解決します。

4.5 パーティション分割

パーティショニングとは、テーブル構造内のフィールド (範囲、リスト、ハッシュなど) に応じて、テーブルのデータを複数のブロックに分割することです。これらのブロックは、1 つのディスク上にあっても、異なるディスク上にあってもかまいません。パーティショニング後も、表面上はテーブルのままですが、データは複数の場所にハッシュされます。このようにして、複数のハードディスクが異なる要求を同時に処理できるため、ディスク I/O の読み取りおよび書き込みパフォーマンスが向上します。
注: キャッシュ、サブライブラリ、サブテーブル、パーティションの追加は、主にプログラマーまたは DBA によって行われます。

フェーズ5: データベースのメンテナンス

データベースのメンテナンスは、システムの監視、パフォーマンス分析、パフォーマンスのチューニング、データベースのバックアップとリカバリなどの主要なタスクを含む、データベースエンジニアまたは運用保守エンジニアの仕事です。

5.1 パフォーマンスステータスの主要指標

技術用語: QPS (1 秒あたりのクエリ数) と TPS (1 秒あたりのトランザクション数)
show status を通じて実行ステータスを確認すると、300 を超えるステータス情報レコードがあり、その中には QPS と TPS を計算するのに役立つ次のような値がいくつかあります。

稼働時間: サーバーが実際に稼働している時間（秒単位）
質問: データベースに送信されたクエリの数
Com_select: クエリ数、実際のデータベース操作
Com_insert: 挿入数
Com_delete: 削除数
Com_update: 更新回数
Com_commit: トランザクション回数
Com_rollback: ロールバック回数

計算方法は、質問に基づいてQPSを計算することです。

mysql> 'Questions' のようなグローバル ステータスを表示します。
mysql> 「Uptime」のようなグローバルステータスを表示します。
QPS = 質問数 / 稼働時間

Com_commit と Com_rollback に基づいて TPS を計算します。

mysql> 'Com_commit' のようなグローバル ステータスを表示します。
mysql> 'Com_rollback' のようなグローバル ステータスを表示します。
mysql> 「Uptime」のようなグローバルステータスを表示します。
TPS = (Com_commit + Com_rollback) / 稼働時間

別の計算方法:

Com_select、Com_insert、Com_delete、Com_update に基づいて QPS を計算します。  
mysql> グローバル ステータスを表示します。Variable_name in('com_select','com_insert','com_delete','com_update');
1 秒待ってから再度実行し、間隔の差を取得します。QPS は、2 回目の各変数の値から 1 回目の対応する変数の値を引いた値です。

TPS計算方法：

mysql> グローバル ステータスを表示します。Variable_name in('com_insert','com_delete','com_update');
TPS を計算するときは、クエリ操作をカウントせず、挿入、削除、更新の 4 つの値のみを計算すれば済みます。

ネットユーザーがこれら 2 つの計算方法をテストした結果、データベースに MyISAM テーブルが多数ある場合は、Questions 計算を使用する方が正確であるという結論に達しました。データベース内に InnoDB テーブルが多数ある場合は、計算に Com_* を使用する方が正確です。

5.2 スロークエリログを有効にする

MySQL では、遅いクエリログを有効にして、どの SQL ステートメントが遅いかを分析できます。動的な有効化がサポートされています。

mysql> グローバルスロークエリログをオンに設定 
# スロークエリログを有効にするmysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log'; 
# スロークエリログファイルの場所を指定しますmysql> set global log_queries_not_using_indexes=on;  
# インデックスを使用しないクエリを記録しますmysql> set global long_query_time=1;  
# 処理時間が 1 秒を超える遅いクエリのみを記録します。遅いクエリのログを分析するには、MySQL に付属の mysqldumpslow ツールを使用できます。分析されるログは比較的単純です。
mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log  
# 最も遅い 3 つのクエリを表示するには、包括的なログ分析機能を備え、スロー ログ、バイナリ ログ、および一般ログを分析できる Percona の pt-query-digest ツールを使用することもできます。
スロークエリログを分析します: pt-query-digest /var/log/mysql/mysql-slow.log
binlog ログを分析する: mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql 
pt-クエリダイジェスト --type=binlog mysql-bin.000001.sql 
一般ログを分析する: pt-query-digest --type=genlog localhost.log

5.3 データベースのバックアップ

データベースのバックアップは最も基本的なタスクであり、最も重要なタスクです。そうしないと、結果は深刻になります。高頻度のバックアップ戦略では、安定した高速なツールを選択することが重要です。データベースのサイズが 2G 未満の場合は、公式の論理バックアップツール mysqldump を使用することをお勧めします。サイズが 2G を超える場合は、Percona の物理バックアップツール xtrabackup を使用することをお勧めします。そうしないと、速度が非常に遅くなります。どちらのツールも、ビジネスの読み取りおよび書き込み操作に影響を与えることなく、InnoDB ストレージエンジンでのホットスタンバイをサポートします。

5.4 データベースの修復

MySQL サーバーの電源が突然切れたり、異常終了したりすることがあり、その結果テーブルが破損し、テーブルデータを読み取れなくなることがあります。この時点で、MySQL に付属する 2 つのツール、myisamchk と mysqlcheck を使用して修復できます。前者は MyISAM テーブルを修復してデータベースを停止することしかできませんが、後者は MyISAM テーブルと InnoDB テーブルの両方をオンラインで修復できます。
注意: 修復する前にデータベースをバックアップすることをお勧めします。

myisamchk 共通パラメータ:
 -f --force 強制修復、古い一時ファイルを上書きします。通常は使用されません -r --recover リカバリ モード -q --quik クイック リカバリ -a --analyze テーブルを分析 -o --safe-recover 古いリカバリ モード。-r で修復できない場合は、このパラメータを試すことができます -F --fast 正常に閉じられていないテーブルのみをチェックします 例: myisamchk -r -q *.MYI

mysqlcheck の共通パラメータ:
 -a --all-databases すべてのデータベースをチェック -r --repair テーブルを修復 -c --check テーブルをチェック（デフォルトオプション） -a --analyze テーブルを分析 -o --optize テーブルを最適化 -q --quik 最速チェックまたはテーブルを修復 -F --fast 正常に閉じられていないテーブルのみをチェック 例：mysqlcheck -r -q -uroot -p123456 weibo

5.5 MySQLサーバーのパフォーマンス分析

集中：
id: CPU 使用率。平均 60% 未満であれば正常ですが、すでにかなり忙しい状態です。
wa: ディスク IO 応答に対する CPU 待機時間。通常、5 より大きい値は、ディスクの読み取りおよび書き込みトラフィックの量が多いことを示します。

KB_read/s、KB_wrtn/s 1 秒あたりに読み書きされるデータの量。主に、ディスクの 1 秒あたりの最大読み取りおよび書き込み速度に基づいて評価されます。

r/s、w/s: 1 秒あたりの読み取りおよび書き込み要求の数は、IOPS (1 秒あたりの入出力) として理解でき、ディスクパフォーマンスを測定するための主な指標の 1 つです。
await: 1 秒あたりの平均 IO 応答時間。5 より大きい場合は、ディスクの応答が遅く、ディスク自体のパフォーマンスを超えていることを意味します。
util: ディスク使用率。平均 60% 未満であれば正常ですが、すでにかなり使用中です。

まとめ

リレーショナルデータベースは、元々の設計上の制限により、大規模なデータ処理には対応できません。そのため、NoSQL（非リレーショナルデータベース）が普及しました。NoSQL は本質的に魅力的で、分散性、高性能、高信頼性などの特徴を備えています。リレーショナルデータベースの特定の固有の欠陥を補い、非構造化データの保存に非常に適しています。主流の NoSQL データベースには、MongoDB、HBase、Cassandra などがあります。

データベースレベルでの最適化効果の向上は、それほど顕著ではありません。主にビジネスシナリオに応じて適切なデータベースを選択することが必要です。

これで、MySQL データベースの最適化手法を簡単に理解するこの記事は終わりです。より関連性の高い MySQL データベースの最適化手法については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: