MySQLデータベース最適化の詳しい説明

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

プロジェクトが確立された後、開発部門は製品部門のニーズに従ってプロジェクトを開発し、開発エンジニアの仕事の一部はテーブル構造を設計することです。データベースの場合、これは非常に重要です。適切に設計されていないと、アクセス速度とユーザー エクスペリエンスに直接影響します。遅いクエリ、非効率なクエリ ステートメント、不適切なインデックス作成、データベースの輻輳 (デッドロック) など、影響を与える要因は数多くあります。もちろん、ストレス テストを行ってバグを発見するテスト エンジニアのチームが存在します。テストエンジニアがいないチームの場合、開発エンジニアは初期段階ではデータベース設計が妥当かどうかあまり考えず、ある程度の訪問数を経てからできるだけ早く機能の実装と納品を完了させます。隠れた問題が明らかになり、それを再度修正するのはそれほど簡単ではありません。

2. データベースのデプロイ

運用保守エンジニアが登場 プロジェクトの初期段階では訪問数はそれほど多くないため、QPS (クエリレート) を処理するには 1 回のデプロイで十分です。毎秒）約1500。高可用性を考慮して、MySQL マスター/スレーブ レプリケーション + Keepalived を使用してダブルクリック ホット バックアップを実行できます。 一般的なクラスター ソフトウェアには、Keepalived と Heartbeat が含まれます。

デュ​​アルマシンホットバックアップブログ投稿: http://www.php.cn/

3. データベースパフォーマンスの最適化

MySQL が最適化なしで通常の X86 サーバーにデプロイされている場合、理論的には、MySQL通常は約 2,000 QPS を処理できますが、最適化後は約 2,500 QPS に増加する可能性があります。そうしないと、アクセス数が約 1,500 の同時接続に達すると、データベースの処理パフォーマンスが低下し、ハードウェア リソースが依然として非常に多くなります。この時点で、ソフトウェアの問題を検討します。では、データベースのパフォーマンスを最大化するにはどうすればよいでしょうか?サーバーのパフォーマンスを最大化するために複数の MySQL インスタンスを 1 台のサーバー上で実行できる一方で、オペレーティング システムとデータベースのデフォルト構成は比較的保守的であることが多く、これには特定の制限があります。これらの構成は、できるだけ多くの接続を処理できるように適切に調整できます。

具体的な最適化には次の 3 つのレベルがあります:

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

MySQL には一般的に使用される 2 つのストレージ エンジンがあり、1 つはトランザクション処理をサポートせず、高速な読み取りパフォーマンスとテーブル レベルを備えた MyISAM です。ロック中。もう 1 つは、トランザクション処理 (ACID) をサポートする InnoDB です。設計目標は、大量のデータを処理するためのパフォーマンスと行レベルのロックを最大化することです。

テーブル ロック: オーバーヘッドが低く、ロックの粒度が大きく、デッドロックの可能性が高く、同時実行性が比較的低い。

• 行ロック: オーバーヘッドが高く、ロックの粒度が小さく、デッドロックの可能性が低く、同時実行性が比較的高くなります。

テーブルロックと行ロックはなぜ発生するのでしょうか?主な目的は、データの整合性を確保することです。たとえば、ユーザーがテーブルを操作していて、他のユーザーもそのテーブルを操作したい場合、他のユーザーが操作できるようになる前に、最初のユーザーが操作を完了するまで待つ必要があります。テーブルのロックと行 これがロックの役割です。そうしないと、複数のユーザーが同時にテーブルを操作すると、データの競合や異常が確実に発生します。

上記に基づくと、InnoDB ストレージ エンジンを使用することが最良の選択であり、MySQL 5.5 以降のバージョンのデフォルトのストレージ エンジンでもあります。各ストレージ エンジンには多くのパラメータが関連付けられており、主にデータベースのパフォーマンスに影響を与えるパラメータを以下に示します。

パブリック パラメータのデフォルト値:

max_connections = 151
#同时处理最大连接数，推荐设置最大连接数是上限连接数的80%左右
sort_buffer_size = 2M
#查询排序时缓冲区大小，只对order by和group by起作用，可增大此值为16M
query_cache_limit = 1M
#查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖
query_cache_size = 16M
#查看缓冲区大小，用于缓存SELECT查询结果，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值
open_files_limit = 1024
#打开文件数限制，如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或卡死

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

key_buffer_size = 16M
#索引缓存区大小，一般设置物理内存的30-40%
read_buffer_size = 128K
#读操作缓冲区大小，推荐设置16M或32M

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

innodb_buffer_pool_size = 128M
#索引和数据缓冲区大小，一般设置物理内存的60%-70%
innodb_buffer_pool_instances = 1
#缓冲池实例个数，推荐设置4个或8个
innodb_flush_log_at_trx_commit = 1
#关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。
innodb_file_per_table = OFF
#默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。
innodb_log_buffer_size = 8M
#日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M

3.2 システム カーネルの最適化

ほとんどの MySQL は Linux システムにデプロイされているため、オペレーティング システムの一部のパラメータはLinux カーネルは、以下で適切に最適化されます。

net.ipv4.tcp_fin_timeout = 30
#TIME_WAIT超时时间，默认是60s
net.ipv4.tcp_tw_reuse = 1
#1表示开启复用，允许TIME_WAIT socket重新用于新的TCP连接，0表示关闭
net.ipv4.tcp_tw_recycle = 1
#1表示开启TIME_WAIT socket快速回收，0表示关闭
net.ipv4.tcp_max_tw_buckets = 4096
#系统保持TIME_WAIT socket最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息
net.ipv4.tcp_max_syn_backlog = 4096
#进入SYN队列最大长度，加大队列长度可容纳更多的等待连接

Linux システムでは、プロセスによって開かれたファイル ハンドルの数がシステムのデフォルト値である 1024 を超えると、「開いているファイルが多すぎます」というメッセージが表示されるため、開いているファイル ハンドルの制限を調整する必要があります。

# vi /etc/security/limits.conf #加入以下配置，*代表所有用户，也可以指定用户，重启系统生效
* soft nofile 65535
* hoft nofile 65535
# ulimit -SHn 65535 #立刻生效

3.3 ハードウェア構成

物理メモリを増やし、ファイルシステムのパフォーマンスを向上させます。 Linux カーネルは、ファイル システムの遅延書き込みメカニズムを通じて、ホット データを保存するためにメモリからキャッシュ領域 (システム キャッシュとデータ キャッシュ) を割り当てます。同期は、条件 (キャッシュ領域が特定の割合に達するか同期するなど) が満たされた場合にのみ行われます。コマンドが実行されています)。つまり、物理メモリが大きいほど、割り当てられるキャッシュ領域も大きくなり、より多くのデータがキャッシュされます。もちろん、サーバーに障害が発生すると、キャッシュされたデータの一部が失われます。

SSD ハードドライブは SAS ハードドライブを置き換え、RAID レベルは RAID1 や RAID5 よりも優れた読み取りおよび書き込みパフォーマンス (IOPS) を持つ RAID1+0 に調整されます。結局のところ、データベースへの負荷は主にディスク I/O から発生します。 O.

4. データベース アーキテクチャの拡張

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

4.1 マスター/スレーブレプリケーションと読み書き分離

実稼働環境では、ほとんどのデータベースが読み取り操作であるため、 1 つのマスターと複数のスレーブ アーキテクチャをデプロイします。マスター データベースは書き込み操作とダブルクリック ホット バックアップを担当します。複数のスレーブ データベースは負荷分散とホット バックアップを担当します。読み取り操作には、LVS、HAProxy、Nginx が含まれます。 読み書きを分離するにはどうすればよいですか?ほとんどの企業は、コード レベルで読み取りと書き込みを分離しているため、より効率的です。もう 1 つの方法は、プロキシ プログラムを通じて読み取りと書き込みの分離を実現することです。これは、企業ではほとんど使用されませんが、MySQL Proxy や Amoeba などがあります。このようなデータベース クラスター アーキテクチャでは、データベースの高い同時実行機能が大幅に向上し、単一マシンのパフォーマンスのボトルネック問題が解決されます。 1 つのスレーブ データベースが 2000 QPS を処理できる場合、5 つのスレーブ データベースは 10,000 QPS を処理でき、データベースの水平方向の拡張性も非常に簡単です。

大量の書き込み操作を行うアプリケーションに直面すると、単一ユニットの書き込みパフォーマンスではビジネス要件を満たせない場合があります。デュアル マスターがある場合、データベース データの不整合が発生します。これは、アプリケーション内の異なるユーザーが 2 つのデータベースを操作する可能性があり、同時に更新操作を行うと、2 つのデータベースのデータベース データに競合または不整合が発生するためです。単一データベースを使用する場合、MySQL はストレージ エンジン メカニズムのテーブル ロックと行ロックを使用して、データの整合性を確保します。複数のメイン データベースを使用する場合、この問題を解決するにはどうすればよいでしょうか。 MySQL-MMM (Mysql 用マスター-マスター レプリケーション マネージャー、Mysql マスター-マスター レプリケーション マネージャー) と呼ばれる、Perl 言語に基づいて開発されたマスター/スレーブ レプリケーション管理ツールのセットがあります。このツールの最大の利点は、これが提供する機能のみであることです。同時に 1 つのデータベース書き込み操作を実行して、データの一貫性を効果的に確保します。

4.2 キャッシュを追加します

データベースにキャッシュシステムを追加します。ホットデータをメモリにキャッシュします。メモリキャッシュに要求されるデータがある場合、結果は返されなくなります。データベースに返されるため、読み取りパフォーマンスが向上します。キャッシュの実装には、ローカル キャッシュと分散キャッシュが含まれます。ローカル キャッシュは、データをローカル サーバーのメモリまたはファイルにキャッシュします。これは高速です。分散型は大量のデータをキャッシュでき、拡張が容易です。主流の分散型キャッシュ システムには、安定したパフォーマンスがあり、QPS は約 8 ワットに達します。 データの永続性が必要な場合は、redis を使用してください。パフォーマンスは memcached よりも低くありません。 作業プロセス:

4.3 サブデータベース

サブデータベースは、Web、BBS、ブログ、その他のライブラリなど、さまざまなビジネスに応じて、関連するテーブルを異なるデータベースに分割します。ビジネス量が大きい場合は、分割されたライブラリをマスター/スレーブ アーキテクチャとして使用して、単一ライブラリへの過度の負荷をさらに回避することもできます。

4.4 テーブルの分割

データベース内の特定のテーブルに数百万のデータがあり、クエリと挿入に時間がかかりすぎています。どうすれば単一のテーブルの負荷を解決できますか?単一テーブルの負荷を軽減し、処理効率を向上させるために、このテーブルを複数の小さなテーブルに分割するかどうかを検討する必要があります。この方法はテーブル分割と呼ばれます。

テーブル分割テクノロジーはさらに面倒です。プログラム コード内の SQL ステートメントを変更し、他のテーブルを手動で作成する必要があります。マージ ストレージ エンジンを使用してテーブル分割を実装することもできます。これは比較的簡単です。テーブルが分割された後、プログラムはマスター テーブル上で動作します。このマスター テーブルにはデータが格納されず、マスター テーブルはデータを更新する方法のみを保持します。さまざまなクエリに基づいて実行されるため、同時実行性とディスク I/O パフォーマンスが向上します。

テーブルの分割は垂直分割と水平分割に分けられます:

垂直分割: テーブル幅の問題を解決するために、多くのフィールドを含む元のテーブルを複数のテーブルに分割します。使用頻度の低いフィールドを別のテーブルに配置したり、大きなフィールドを別のテーブルに配置したり、密接に関連するフィールドを 1 つのテーブルに配置したりできます。

水平分割: 元のテーブルを複数のテーブルに分割します。各テーブルは同じ構造を持ち、単一のテーブル内の大量のデータの問題を解決します。

4.5 パーティション化

パーティション化とは、テーブルのデータを複数のブロックに分割することです。これらのブロックは、パーティション化後も表面上は 1 つのテーブルのままですが、データはハッシュされます。これにより、複数のハードディスクが異なるリクエストを同時に処理できるようになり、ディスク I/O の読み取りおよび書き込みパフォーマンスが向上し、実装が比較的簡単になります。

注: キャッシュ、サブライブラリ、サブテーブル、パーティションの追加は主にプログラマによって実装されます。

5. データベースのメンテナンス

データベースのメンテナンスは、運用保守エンジニアまたは DBA の主な作業であり、パフォーマンスの監視、パフォーマンス分析、パフォーマンスのチューニング、データベースのバックアップとリカバリなどが含まれます。

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

QPS、Queries Per Second: 1 秒あたりのクエリ数、データベースが 1 秒あたりに処理できるクエリの数

TPS、Transactions Per Second: 1 秒あたりに処理されるトランザクションの数

show status 実行ステータスをチェックします。300 を超えるステータス情報レコードがあり、そのうちのいくつかは次のように QPS と TPS の計算に役立ちます:

稼働時間: サーバーが実際に実行されていた時間 (秒単位)

質問:クエリのためにデータベースに送信済みです カウント

Com_select: クエリの数、データベースの実際の操作

Com_insert: 挿入の数

Com_delete: 削除の数

Com_update: 更新の数

Com_commit : トランザクションの数

Com_rollback: ロールバックの数

そして、計算方法は次のとおりです さて、QPS は質問に基づいて計算されます:

mysql> show global status like 'Questions';
mysql> show global status like 'Uptime';

QPS = Questions / Uptime

基于Com_commit和Com_rollback计算出TPS：

mysql> show global status like 'Com_commit';
mysql> show global status like 'Com_rollback';
mysql> show global status like 'Uptime';
TPS = (Com_commit + Com_rollback) / Uptime

另一计算方式：基于Com_select、Com_insert、Com_delete、Com_update计算出QPS

mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update');

等待1秒再执行，获取间隔差值，第二次每个变量值减去第一次对应的变量值，就是QPS

TPS计算方法：

mysql> show global status where Variable_name in('com_insert','com_delete','com_update');

计算TPS，就不算查询操作了，计算出插入、删除、更新四个值即可。

经网友对这两个计算方式的测试得出，当数据库中myisam表比较多时，使用Questions计算比较准确。当数据库中innodb表比较多时，则以Com_*计算比较准确。

5.2 开启慢查询日志

MySQL开启慢查询日志，分析出哪条SQL语句比较慢，使用set设置变量，重启服务失效，可以在my.cnf添加参数永久生效。

mysql> set global slow-query-log=on #开启慢查询功能
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; #只记录处理时间1s以上的慢查询

分析慢查询日志，可以使用MySQL自带的mysqldumpslow工具，分析的日志较为简单。

# mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log #查看最慢的前三个查询

也可以使用percona公司的pt-query-digest工具，日志分析功能全面，可分析slow log、binlog、general log。

分析慢查询日志：pt-query-digest /var/log/mysql/mysql-slow.log

分析binlog日志：mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql

pt-query-digest –type=binlog mysql-bin.000001.sql

分析普通日志：pt-query-digest –type=genlog localhost.log

5.3 数据库备份

备份数据库是最基本的工作，也是最重要的，否则后果很严重，你懂得！但由于数据库比较大，上百G，往往备份都很耗费时间，所以就该选择一个效率高的备份策略，对于数据量大的数据库，一般都采用增量备份。常用的备份工具有mysqldump、mysqlhotcopy、xtrabackup等，mysqldump比较适用于小的数据库，因为是逻辑备份，所以备份和恢复耗时都比较长。mysqlhotcopy和xtrabackup是物理备份，备份和恢复速度快，不影响数据库服务情况下进行热拷贝，建议使用xtrabackup，支持增量备份。

Xtrabackup备份工具使用博文：http://www.php.cn/

5.4 数据库修复

有时候MySQL服务器突然断电、异常关闭，会导致表损坏，无法读取表数据。这时就可以用到MySQL自带的两个工具进行修复，myisamchk和mysqlcheck。

myisamchk: myisam テーブルのみを修復できます。データベースを停止する必要があります。

共通パラメータ:

-f –force 強制修復、古い一時ファイルを上書きします。通常は使用されません

-r –recover リカバリモード

-q –quik fast 復元

-a –analyze 分析テーブル

-o –safe-recover 古いリカバリモード、-r が修復できない場合は、このパラメータを試すことができます

-F –fast 閉じられていないテーブルのみをチェックします通常、

高速に weibo データベースを修復します:

# cd /var/lib/mysql/weibo

# myisamchk -r -q *.MYI

mysqlcheck: myisam テーブルと innodb テーブルの両方を使用でき、実行する必要はありません。単一のテーブルを修復するには、データベースの後にスペースで区切ってテーブル名を追加します

共通パラメータ:

-a –all-databases すべてのライブラリを確認します

-r –repair テーブルを修復します

- c –check テーブルをチェックします。デフォルトのオプションです

-a –分析テーブルを分析します

-o –最適化テーブルを最適化します

-q –quik 最も速いテーブルのチェックまたは修復

-F –fast は、正常に閉じられていないテーブルのみをチェックします

weiboデータベースを素早く修復:

mysqlcheck -r - q -uroot -p123 weibo

5.5 さらに、CPUとI/Oパフォーマンスメソッドを確認してください

#CPUパフォーマンスを表示

#パラメータ - P は CPU の数を表示し、ALL はすべての CPU を表示するか、最初の数個だけを表示することもできます

#I/O パフォーマンスの表示

#パラメータ -m は M に表示されます単位、デフォルトは K

#%util: 100% に達すると、I/O が非常にビジーであることを意味します。

#await: リクエストがキュー内で待機する時間。読み取り時間に直接影響します。

I/O 制限: IOPS (r/s+w/s)、通常は約 1200。 (IOPS、1 秒あたりの読み取りおよび書き込み (I/O) 操作の数)

I/O 帯域幅: シーケンシャル読み取りおよび書き込みモードでは、SAS ハードディスクの理論値は約 300M/s、理論値はSSDハードディスクは約600M/sです。

上記は、MySQL を 3 年間使用した後にまとめた主な最適化ソリューションの一部です。機能は限られており、包括的ではないものもありますが、これらは基本的に中小企業のデータベース ニーズを満たすことができます。リレーショナル データベースの元の設計の制限により、一部の BAT 企業は大量のデータをリレーショナル データベースに配置しており、大規模なデータのクエリと分析でより優れたパフォーマンスを達成できていません。そのため、非リレーショナル データベースは、データ量が多く、パフォーマンスが高いため、リレーショナル データベースの欠点を補うことができるようになり、徐々に、ほとんどの企業が MongoDB や HBase などのビジネス データベースを保存するようになりました。 HDFS、GFS などの分散ファイル システムはデータ ストレージに使用されます。大量のデータの計算と分析には、Hadoop、Spark、Storm などが使用されます。これらは運用と保守に関連する最先端のテクノロジーであり、ストレージの主な学習対象でもあります。より良い最適化計画を持っているブロガーがいたら、ぜひ共有してください。

上記は MySQL データベース最適化の詳細な説明です。さらに関連する内容については、PHP 中国語 Web サイト (www.php.cn) に注目してください。