データベースを最適化する方法

データベースの動作が遅すぎる理由

1つ目のポイントは、ハードウェアが古すぎることです

ハードウェアについては主にCPU、メモリ、ディスクの3つの側面から説明しますが、その他にも次のような要因があります。ネットワークカード、コンピュータルームネットワークなどについては、記事が長くなるので、一つずつ紹介することはしません。また、別の機会にお話します。

まず、MySQL の CPU 使用率の特性を見てみましょう:

5.1 は 4 コアを使用でき、5.5 は 24 コアを使用でき、5.6 は 64 コアを使用できます

たとえば、MySQL5.6 は 48 コアを使用できます。動作は良好で、64 コアを使用できます (48 コアから 64 コアの間、公式発表は 48 コアですが、実際にテストしたところ、64 コアに達する可能性があります)。

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 (500*0.15) になる可能性があるため、128G が必要になる可能性があります。左右のメモリを備えたサーバー。

さらに、QQ Farm などの一部の企業には、特にホットで大量のホット データ (15% ～ 20% の範囲を超える可能性があります) が存在します。

QQファームやハッピーファームなど、食べ物を盗むゲームは誰もがプレイしたことがあると思います。 (チケットを予約するための 12306 ウェブサイトもあります)。

このタイプのビジネスの特徴は、データがホットなときは基本的に 100% ホットなデータであることです。たとえば、全員が QQ Farm をプレイするときは、毎回プレイしに来ます。そうです、彼らは時々現れて、夜中にトイレに行くときに起きて、一握りの野菜を盗みます。

そのため、この種のビジネスでは MySQL データベースのメモリ構成を増やす必要があります。 15〜20％では十分ではありません。

概要: 一般ビジネスの 15% ～ 20% は、ユーザー センター、注文、その他の一般的なビジネスなどのホット データの計画に使用されます。その他の特殊な事業については、具体的な状況を詳細に分析する必要がある。

クエリの応答時間に基づいて割り当てをガイドできます

この種の大規模なオンライン アーキテクチャ、つまり大規模なデータベースの計画と設計を行っている場合、

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 つのフィールドを読み取ることは間違いなく Sequential IO によって行われます。

これまでは、どのデータベース設計の本でも、各テーブルに自動インクリメント主キーの仕様を追加する必要があると記載されていました。実際、その仕様は無効であり、上で例に挙げた友人関係はそうではありません。自動インクリメントを使用します。主キーとして使用する代わりに、ビジネス属性を持ち、頻繁に読み取られる 2 つのビジネス フィールドが主キーとして使用されるため、パフォーマンスが向上します。

したがって、勉強するときは、これらの本の規範や規則を暗記するのではなく、InnoDB の内部原理を学ぶなど、何かの原理を実際に理解し、実際の作業でサポートを受ける必要があります。原則とその原則の使用 1 つの例から推論を導き出すため。

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 個の接続は 10 分以内にのみこの複雑な SQL を実行でき、それ以降の他のすべての SQL はブロックされます。

その結果、ほとんどのアプリケーションは 10 分間利用できなくなりますよね?そして、雪崩を引き起こしてシステムが崩壊する可能性があります。

複雑なSQLの最適化もDBAにとって非常に重要な仕事です。これらの複雑なSQL、遅いSQL、悪いSQLを監視手法によって見つけ出し、プログラマーに最適化の提案を与える必要があります(DBAは性能比較テストを行う必要があります)。 )、そのため、プログラマーがコードを変更した場合にのみ、渋滞のない高速道路のように、システムが真にスムーズかつ並行して実行できるようになります。

それでは、うちの会社のプログラマは本当にすごいのではないか、死んでもSQLコードを変えない、死んでも最適化しない、そしてコミュニケーションができない、と言う人もいるかもしれません。だから何をすべきか？

それを処理するための専用のスレーブ ライブラリ (スレーブ ライブラリ) を構築することもできます。いつでもクエリするライブラリを変更できます。

たとえば、当社を例にとると、レポートを作成するバックグラウンド システムはクエリ用のスレーブ データベースに接続されており、メイン データベースには接続されていません。

無効なロジック

フルテーブルスキャン

例: update t set a = a + 1; where 条件を追加するのを忘れました。

システムで数百万のオンライン ユーザーをサポートしたい場合は、SQL レビュー システム (SQL レビュー) を追加して、無効なロジックを含む SQL とテーブル全体のスキャンを含む SQL を排除する必要があります。

SQL は、DBA による審査と承認後にのみオンラインでリリースできます。

さらに、この種の大規模な更新 SQL はバッチで更新する必要があり、大きな SQL タスクは小さなタスクに分割して実行する必要があります。 MySQL では、これには特別な注意が必要です。

なぜバッチで更新するのですか?

理由 1. 前述したように、MySQL クエリは 1 つの CORE のみを使用できます。 SQL トランザクションは大規模かつ複雑すぎるため、実行に時間がかかり、輻輳が発生しやすくなります。

理由 2. オンライン環境では、MySQL は通常、マスター/スレーブ アーキテクチャを採用しています。マスターで 100 万行の大きな更新トランザクションが発生すると、スレーブはそこでスタックする可能性があります。シングルスレッド構造のため、同期遅延が発生します。

MySQL は SQL を記述し、迅速に実行され、迅速に送信される小さなトランザクション SQL を作成します。各クエリがより速く完了し、接続がより速く解放されるようにします。

上記の共有に基づいて最適化した後、データベースは高速になりましたか?

以上がデータベースを最適化する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。