MySQL 最適化に関する考慮事項

データベースを運用する場合、mysqlを最適化する必要があります。最適化の注意点について説明します。

最初のポイントは、ハードウェアが古すぎるということです
ここでは、ハードウェアについて主に CPU、メモリ、ディスクの 3 つの側面について説明します。記事が長いため、ネットワーク カード、コンピュータ ルームのネットワークなどのいくつかの要素もあります。 , いちいち紹介はしません、機会があればまた紹介させていただきます。
まず、MySQL の CPU 使用率の特性を見てみましょう。
5.1 は 4 コア、5.5 は 24 コア、5.6 は 64 コアを使用できます。たとえば、MySQL5.6 は正常に動作する場合、48 コア以上を使用できます。 , 64 CORE が使用可能です (48CORE ～ 64CORE の間、公式発表は 48 CORE、私が実際にテストしたところ、64 CORE まで到達可能です)。
MySQL 5.6 は 48 コア +
*MySQL 5.1 は以前は 4 コアまで使用可能 **
現在、一般的な本番環境サーバーは 32CORE 以上です。
したがって、会社のサーバーが 4 コアまたは 1 コアのみの非常に古いサーバーを使用している場合を除き、ここにいる皆さんにはできるだけ MySQL5.5 または MySQL5.6 を使用することをお勧めします。
5.1 より前 (5.0 と同じ) は内部コードでハードコードされており、innobase ストレージ エンジンに基づいていたため、データベースのハードウェア使用率が低かったためです。 InnoDB エンジンに進化してからは、さらに優れたものになりました。
各接続はスレッド (スレッド プールではない) であり、各クエリは 1 つのコアのみを使用できます。
さらに、MySQL では、各クエリは 1 つの CPU のみを使用できます。
Oracle は並列 SQL と並列クエリを使用します。この種の関数は MySQL には存在しません。
実行プランのキャッシュがありません (SQL 実行プランのプリコンパイルがありません)
次に、MySQL 内には SQL のプリコンパイルがありません。したがって、Oracle のメモリ構造にはライブラリ キャッシュのような構造はありません。したがって、MySQL にはハード解析のみがあり、ソフト解析はおろか、ソフト解析もありません。
MySQL は接続数が増えるとパフォーマンスが低下します
これは MySQL の欠陥でもありますが、MySQL バージョンの進化に伴い、多くの解決策が登場しました。
例: TP と呼ばれる、正式に開始されたスレッド プール。同時接続数が多すぎる問題を解決するためのものですが、これは MySQL の追加コンポーネントであり、公式 TP を購入するには追加のお金が必要です。
さらに、同様の問題を解決するために OneSQL ミドルウェアを開発した Lou Fangxin という名前の人物が中国にいます。
結果キャッシュがありますが、役に立ちません
MySQLにもクエリキャッシュというOracleと似た結果キャッシュがありますが、比較的役に立たない機能であり、ほとんど使用されません。
実際の運用環境のほとんどは更新や変更が頻繁に行われる OLTP システムであるため、データが頻繁に更新や変更される環境でこのクエリ キャッシュを使用すると MySQL のパフォーマンスが著しく低下するため、一般的にはほとんど使用されません。
現在は MySQL を使用していますが、基本的には InnoDB ストレージ エンジンを使用しています。以前の MyISAM エンジンはほとんど使用されていません。 (ストレージ エンジンとは何ですか? これを知らない場合は、gg してください)
InnoDB エンジンは、それ自体がトランザクション ストレージ エンジンであり、InnoDB を使用することにより、このクエリ キャッシュをオンにする必要はまったくありません。トランザクション処理機能では、データの更新と変更が頻繁に発生します。
MySQL のメモリ使用率の特性をもう一度見てみましょう
64 ビット オペレーティング システムを搭載したサーバーは、メモリ ((2^64-1)/1024/1024/1024)G を使用できます
高速な同時実行環境では、メモリキャッシュは基本的に、ディスク IO への影響を減らすために使用されます。通常、メモリは実際のデータの 15% ～ 20% に基づいて計画されます。データが特にホットな場合は、より大きな割合を考慮してデータをキャッシュする必要があります。
このデータの 15% ～ 20% は通常、ホット データと呼ばれます。 (これもよくある経験値です)
例えば、MySQL の総データ量が約 500G だと見積もると、MySQL が提供するメモリが 75G (5000.15) となると、約 128G のメモリが必要になる可能性がありますサーバ。
さらに、QQ Farm などの一部の企業には、特にホットで大量のホット データ (15% ～ 20% の範囲を大幅に超える可能性があります) が存在します。
QQファームやハッピーファームなど、食べ物を盗むゲームは誰もがプレイしたことがあると思います。 (チケットを予約するための 12306 ウェブサイトもあります)。
このタイプのビジネスは、私たちの業界で非常に注目を集めています。このタイプのビジネスの特徴は、データがホットなときは、基本的に 100% ホットなデータであることです。たとえば、全員が QQ ファームでプレイすると、全員がそこに集まります。毎日遊んでいて、夜中にトイレに行くときに、時々現れて、一握りの野菜を盗んでいました。
そのため、この種のビジネスでは MySQL データベースのメモリ構成を増やす必要があります。 15〜20％では十分ではありません。
概要: ****一般的なビジネスでは、ユーザー センター、注文、その他の一般的なビジネスなどのホット データの計画に 15% ～ 20% が使用されます。その他の特殊な事業については、具体的な状況を詳細に分析する必要がある。
クエリの応答時間に基づいてガイドの割り当てを行うことができます
この種の大規模なオンライン アーキテクチャ、つまり大規模なデータベースの計画と設計を行う場合、
SQL クエリの応答時間も非常に重要な指標です。
この種の大規模システムでは、オンラインでビジネスを行うために同時に数百万、さらには数千万のユーザーを運ぶ必要があり、SQL クエリ (クエリ) の応答時間は厳密に制御される必要があります。システムはどのくらいの時間以内に制御する必要があります。
たとえば、コア ライブラリの場合、クエリの応答時間 (平均応答) が 30 ミリ秒未満であることが必要です。 30msを超える場合は、データベースが負荷制限に達している可能性があるため、データベースを拡張する必要があると考えられます。
さらに、このクエリ応答時間の長期的な指標監視が必要です。
これがコア ライブラリです。ログを保存するライブラリや、パフォーマンス要件がそれほど高くないライブラリなど、他のそれほど重要ではない補助ライブラリがある場合は、クエリの応答時間を 1 秒または 2 秒以内に緩和できます。
ビジネスの重要性に基づいて、このクエリ応答時間のしきい値を設定します。
これは非常に重要な指針であり、クエリの応答時間に基づいてパフォーマンス容量を計画してください。
容量にはパフォーマンス容量とスペース容量の2種類があります。 スペースの容量は非常にシンプルで、SIZE データをどれだけ配置し、T を何個置くかです。
パフォーマンス能力の方が重要であり、ビジネスのプレッシャーと負荷に対処できるかどうかが決まります。
誰もが覚えておくべきこと: 対処したいビジネスが数百人のユーザーではなく、数百万人のアクティブ ユーザーである場合、パフォーマンスが最も重要であり、ビジネスのニーズを満たすことが最も重要です。あなたの機能がどれほど優れていても、製品がどれほど優れていても、パフォーマンスが比類のないものであれば、他のすべてが無意味になってしまい、数十万人があなたのシステム全体とプロジェクトを数秒でダウンさせ、そしてあなたの会社をダウンさせる可能性があります。目がくらんでしまいます。
頑張ってきたユーザーも大量に失われ、損失は大きいでしょう。
パフォーマンスが基礎です。アーキテクチャ全体は、パフォーマンスがそれに耐えられる場合にのみ意味を持ちます。パフォーマンスが満足できない場合は、後から高可用性を検討しても意味がありません。
MySQL のディスク使用率の特性
Binlog、REDO ログ、UNDO ログのシーケンシャル IO
MySQL にはさまざまな IO タイプがあります。
Binlog、redolog、undolog、これらはすべてシーケンシャル IO 書き込みです。
このようなものを SSD に置く必要はありません。従来のメカニカルディスクへのシーケンシャル書き込みも非常に高速です。また、SSD には書き込み損失と書き込み寿命の問題もあります。 SSDに置く必要があります。従来の SAS ディスクに置くだけで十分です。 SSDを搭載する必要はありません。
SSDはデータファイルの保存に使用されます。データファイルで発生する IO のほとんどはランダム IO であるため、SSD がランダム IO を実行することは非常に有利です。ストレージには、SSD ソリッド ステート ディスク + 従来のディスク SAS ディスクが混在しています。また、SSD をバックアップディスクとして使用しないでください。
データファイルのランダム IO とシーケンシャル IO の組み合わせ
シーケンシャル IO の方が常に高速です。データベース設計において、あなたが優れた DBA であるか優れたアーキテクトであるかを決定するのは、ランダム IO を削減しながら、可能な限りシーケンシャル IO を実現するビジネスを設計できるかどうかによって決まります。例: 友人関係のビジネスを設計するとき、クエリがシーケンシャル IO を通じて友人関係を取り出せることを望みます。では、どのように設計すればよいでしょうか。
MySQL の InnoDB では、InnoDB の機能であるクラスター化インデックス テーブルを利用できます。 (Oracle の IOT に似ています)。
この機能を使用すると、ユーザーの友人データを可能な限り 1 つのページまたは隣接する複数のページに収集できます。読み取り時にシーケンシャル読み取り IO を実行できるようになり、パフォーマンスが大幅に向上しました。
友人関係テーブルの構造は次のとおりです (前提条件テーブルは InnoDB エンジンです):
owner_id friends_id (友人 ID)
上記 2 つのフィールドは主キーとして使用されます。InnoDB の主キーはクラスター化インデックスです。この場合、これら 2 つのフィールドを読み取る順序は IO でなければなりません。
これまでは、どのデータベース設計の本でも、各テーブルに自動インクリメント主キーの仕様を追加する必要があると記載されていました。実際、その仕様は無効であり、上で例に挙げたフレンド関係は使用されていません。自動インクリメント。主キーを主キーとして使用する代わりに、ビジネス属性を持ち、頻繁に読み取られる 2 つのビジネス フィールドが主キーとして使用されるため、パフォーマンスが向上します。
したがって、勉強するときは、これらの書籍に記載されている規範や規則を暗記するのではなく、InnoDB の内部原理を学び、実際の作業でサポートを受けながら、何かの原理を実際に理解する必要があります。原則を使用して、類推して学習します。
InnoDB の原則は膨大な知識であり、時間をかけて学習する必要があります。私の公式アカウントに注目していただければ、InnoDB に関する記事が続々と公開される予定です。
OLTP ビジネスでは、より多くのランダム IO が必要です
メモリをキャッシュに使用できるため、ランダム IO が削減されます
OLAP ビジネスでは、より多くのシーケンシャル IO が必要です
メモリ キャッシュはあまり役に立ちません
MySQL 5.6 より前では、ページの変更はサポートされておらず、デフォルトは 16K でした。
このパラメータは innodb_page_size ですが、MySQL5.6 では 8K または 4K にのみ変更でき、MySQL5.7 以降になるまでは 32K または 64K に変更できません。
OLAP システムの場合、OLAP システムには比較的大きなクエリがあり、大量のデータをスキャンするため、ページを大きくするとパフォーマンスの向上に役立ちます。
2点目: データベース設計が良くない
例えば、トリガー、パーティション、多数のストアドプロシージャ、関数など、多数のデータベース機能が使用されています。
私たちはよく「小さいことは美しい」と言いますが、これはシンプルであることが最高であることを意味します。データベースのすべての機能を使用すると、当然データベースのパフォーマンスが低下し、バグや根本的な障害が発生する可能性が高くなります。
したがって、優れたデータベースプロジェクトの設計とは、小さく、美しく、簡潔で簡潔であることを誰もが理解する必要があります。さらに、データベースはプロジェクト全体の一部にすぎません。トリガーやストアド プロシージャなどは、プロジェクト全体でアプリケーション コードを使用して確実に実装できます。
そのため、MySQL を使用するときは、すべての機能を使用するのではなく、テーブル、インデックス、トランザクションなどの強力な機能のみを使用します。
もう 1 つのポイントは、MySQL 5.6 より前は、運用環境のメイン データベースではサブクエリが許可されていなかったことです。
MySQL 5.6 より前のサブクエリのパフォーマンスは特に悪かったです。 (構文はサポートされていますが、SQL のパフォーマンスは非常に悪いです)。
たとえば、現在 Oracle を使用していて、Oracle を MySQL に移行したい場合は、MySQL 5.6 バージョンを使用することをお勧めします。MySQL 5.6 では、サブクエリのサポートとパフォーマンスが大幅に向上しています。
MySQL 5.6 サブクエリのパフォーマンスが大幅に向上します。
ポイント 3: プログラムの書き方が不十分です

DBAを経験した学生なら経験があると思いますが、中小企業ではプログラマーのレベルが異なります。
特に、業界に入ったばかりのプログラマー（新卒）を多く見かけると、業界に入ったばかりのプログラマーも緊急性の高いニーズを抱えている可能性が高くなります。 このような環境でプログラムを開発することを考えるのは困難です。
もちろん、それはプログラマーのせいではありません。彼らを責めることはできません。
上記の現象の主な原因は、国内の開発環境にあります。開発ニーズが緊急であり（製品が毎日アクティブ化されているため）、プログラマーは仕事に追われています（長時間の残業です）。 ）彼らはビジネス プログラムの実装に忙しいだけで、オプティマイザーの時間がまったくありません。
もちろん、この環境では、私たち DBA にとってはチャンスです。プログラマが作成した不適切な SQL や複雑な SQL により、システムが遅くなったり、クラッシュしたりすることがありました。その後、DBA が介入して、これらの不適切な SQL や遅い SQL を最適化して変換したところ、システムは正常な状態に戻り、ますます安定しました。 これは非常にやりがいのあることであり、同僚やリーダーからも尊敬されるでしょう。
同時に、DBA はプログラマーのトレーニングを強化し、優れた SQL を迅速に作成する能力を強化することもできます。時間を短縮して、より優れたパフォーマンスとスムーズなパフォーマンスで SQL ステートメントを作成できるようにします。 これにより、DBA の負担も軽減されます。
私は個人的にプログラマーとトレーニングについて話すことを好みます。第一に、技術を交換することで誰もが何かを得ることができます。第二に、将来的に交渉する必要がある問題がある場合、それは良好な関係を築くのに役立ちます。議論しやすい。これは彼らを夕食に招待するよりも良いことです。
不適切に書かれたプログラムに対しては、主に次の解決策があります:
アプリケーションにデータベース接続プールを使用させます。特に、JAVA に基づいて開発された大規模で同時実行性の高いアプリケーションでは、接続プールを使用する必要があります。
接続プールを使用する利点は、アプリケーション内の接続の数を制限できることです。さらに、新しい接続を作成するため、MySQL の接続を作成するコストも大きくなります。接続は、MySQL がスレッドを作成することと同等です。
接続数が増えると MySQL のパフォーマンスが低下することにも先ほど触れました。
プログラム コードを書いたことがある学生は、通常の PC ノートブック (通常は 4CORE) で 400 のスレッドを作成し、各スレッドが 1+1+1+1+... 単純な作業を実行し、スリープ中にチェックすることも知っておく必要があります。 PC がスタックしているかどうか。 PC の CPU がほぼいっぱいであることがわかります。あえて 600 個のスレッドを作成すると、マシンはすぐに再起動されます。これは、スレッドのオーバーヘッドにより CPU が完全に占有されているためです。
複雑な SQL ステートメント
先ほども述べたように、プログラマーが作成する SQL には通常、多忙のため、この SQL のパフォーマンスや操作を考慮する余裕はありません。場合によっては、プログラマが接続した SQL がシステム全体を直接ダウンさせる可能性があります。
簡単な例を示します。アプリケーションの 1 つがデータベースに 10 個の接続を作成します (最大接続数 = 10)。これらの 10 個の接続のそれぞれが同じ複雑な SQL を同時に実行します。これには少なくとも 10 分かかります。この複雑な SQL を実行すると、これら 10 個の接続はこの複雑な SQL を 10 分以内にのみ実行でき、それ以降の他のすべての SQL はブロックされます。
ほとんどのアプリケーションは 10 分間使用できなくなりますよね?そして、雪崩を引き起こしてシステムを崩壊させる可能性があります。
複雑なSQLの最適化もDBAにとって非常に重要な仕事です。これらの複雑なSQL、遅いSQL、不良SQLを監視手法で発見し、プログラマに最適化の提案を行う必要があります（DBAは性能比較テストを行う必要があります）。コードを変更するだけで、渋滞のない高速道路のように、システムを真にスムーズかつ並行して実行できるようになります。
それでは、うちの会社のプログラマは本当にすごいのではないか、死んでもSQLコードを変えない、死んでも最適化しない、そしてコミュニケーションも取れない、と言う人もいるかもしれません。だから何をすべきか？
それを処理するための専用のスレーブ ライブラリ (Slave library) を構築することもできます。クエリするライブラリを変更することもできます。
たとえば、当社を例にとると、レポートを作成するバックグラウンド システムはクエリ用のスレーブ データベースに接続されており、メイン データベースには接続されていません。
無効なロジック
フルテーブルスキャン
例: update t set a = a + 1; where 条件を追加するのを忘れました。
システムで数百万のオンライン ユーザーをサポートしたい場合は、SQL レビュー システムを追加して、無効なロジックを含む SQL とフル テーブル スキャンを含む SQL を排除する必要があります。
SQL は、DBA による審査と承認後にのみオンラインでリリースできます。
さらに、この種の大規模な更新 SQL はバッチで更新する必要があり、大きな SQL タスクは小さなタスクに分割して実行する必要があります。 MySQL では、これには特別な注意が必要です。
なぜバッチで更新するのですか?
**理由 1. **上で述べたように、MySQL クエリは 1 つの CORE のみを使用できます。 SQL トランザクションは大規模かつ複雑すぎるため、実行に時間がかかり、輻輳が発生しやすくなります。
理由 2. オンライン環境では、MySQL は通常、マスター/スレーブ アーキテクチャを採用しています。100 万行の大規模な更新トランザクションがマスターで発生すると、スレーブは単一であるため、そこでスタックする可能性があります。 -スレッド構造のため、同期遅延が発生します。
MySQL は SQL を記述し、迅速に実行され、迅速に送信される小規模なトランザクション SQL を作成します。各クエリがより速く完了し、接続がより速く解放されるようにします。

この記事では、MySQL の最適化に関する注意事項について説明します。関連知識については、php 中国語の Web サイトを参照してください。

関連おすすめ:

Discuz!X/データベースDB::関数の操作方法

ThinkPHPフレームワークのStringクラスの詳しい説明

JSの基礎 - Math Array Date

以上がMySQL 最適化に関する考慮事項の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。