MYSQLデータベースの最適化段階を簡単に理解する

導入

面接官がこんな質問をしたことはありませんか？

データベースをどのように最適化しますか?

では、この質問にどう答えるべきでしょうか?実は、このトピックを書いた理由は、最近さまざまな公開アカウントから転送されたデータベースチューニングの知識に関する記事を見たからです（リンクは貼りません）。何度かめくってみたところ、データベースは水平分割する必要があると何度も書かれていました。読者の皆さんに聞きたいのですが、水平分割を経験した人はどれくらいいますか？最近の記事の多くは実用性に乏しく、純粋な理論分析としか言えません。

この記事はもともと Zhihu の質問から生まれたもので、それに基づいて改良したものです。

最初の段階ではSQLとインデックスを最適化します

これはチューニングの最初の段階ですが、なぜでしょうか?

このステップはコストが最も低く、ミドルウェアを必要としないためです。インデックス最適化や SQL 最適化をまったく行っていないのに、水平分割を行おうとしています。これは単なる不正行為ではないでしょうか?

手順はどのようなものですか? 大まかに説明しましょう。

（１）スロークエリログを使用して実行効率の低いSQL文を見つける

（２）explainを使用してSQL実行計画を分析する

（３）問題点を特定し、適切な最適化対策を講じ、インデックス等を作成する。

SQL を最適化する方法についての記事は非常に多く、読者がそれをすべて読むのは大変なので、ここでは例は挙げません。

2番目の段階はキャッシュを構築することです

SQL を最適化しても問題を解決できない場合にのみ、キャッシュの構築を検討してください。結局のところ、キャッシュを使用する目的は、複雑で時間がかかり、頻繁に変更されない実行結果をキャッシュして、データベース リソースの消費を削減することです。

ここで注意すべき点は、キャッシュを構築した後、システムの複雑さが増すということです。次のような多くの問題を考慮する必要があります。

キャッシュとデータベースの一貫性の問題ですか? (たとえば、キャッシュを追加するか、キャッシュを削除するか) については、私の記事「データベースとキャッシュのデュアル書き込み一貫性スキームの分析」を参照してください。
キャッシュの崩壊、キャッシュの侵入、キャッシュの雪崩の問題をどのように解決しますか?キャッシュを予熱する必要はありますか?しかし、中小企業のほとんどはおそらくそれを考慮したことがないと思います。

読み取りと書き込みの分離の第3段階

キャッシュが機能しない場合は、マスター/スレーブ レプリケーションと読み取り/書き込み分離を使用します。アプリケーション層では、読み取り要求と書き込み要求が区別されます。または、mycat や altas などの既製のミドルウェアを使用して、読み取りと書き込みを分離します。

マスタースレーブアーキテクチャを使用すると言うのであれば、次の 3 つの問題に備える必要があることに注意してください。

（１）主人と奴隷の関係にはどのような利点があるか？

回答: データベースのバックアップを実装し、データベースの負荷分散を実装し、データベースの可用性を向上させます。

（２）主人と奴隷の原理？

答え: 写真の通りです（この絵は自分で描いたものではありません、怠け者です）

マスターデータベースには、バイナリログをスレーブデータベースに渡すログダンプスレッドがあります。

スレーブ データベースには、I/O スレッドと SQL スレッドの 2 つのスレッドがあります。I/O スレッドは、マスター データベースからバイナリ ログの内容を読み取り、リレー ログに書き込みます。SQL スレッドは、リレー ログから内容を読み取り、スレーブ データベースに書き込みます。

（３）マスタースレーブ一貫性問題をどのように解決するか？

回答: この問題をデータベース レベルで解決することはお勧めしません。 CAP 定理によれば、マスター スレーブ アーキテクチャは、一貫性の要件を満たすことができない高可用性アーキテクチャです。同期レプリケーション モードや半同期レプリケーション モードを使用しても、強い一貫性ではなく弱い一貫性になります。したがって、この問題を解決するにはキャッシュを使用することをお勧めします。

手順は次のとおりです。

1. テストを通じてマスタースレーブ遅延時間を計算します。MySQL 5.7 以降では、より完全なマルチスレッドレプリケーション機能が搭載されており、遅延が 1 秒以内であることが一般的に保証されるため、MySQL バージョン 5.7 以降を使用することをお勧めします。しかし、MySQL は現在バージョン 8.x までありますが、まだバージョン 5.x を使用している人はいるでしょうか?

2. データベース書き込み操作では、最初にデータベースに書き込み、次にキャッシュに書き込みますが、有効期間は非常に短く、マスタースレーブ遅延よりもわずかに長くなります。

3. リクエストを読み取るときは、まずキャッシュを読み取ります。キャッシュが存在しない場合（この時点でマスターとスレーブの同期が完了している）、データベースを読み取ります。

第4段階ではパーティションテーブルを使用する

正直に言うと、面接中にこの段階をスキップすることは可能です。多くのインターネット企業がパーティション テーブルの使用を推奨していないため、私自身もパーティション テーブルの使用を推奨していません。このパーティション テーブルの使用には落とし穴が多すぎるからです。

以下は他の記事からの回答です:

MySQL のパーティション テーブルとは何ですか?

回答: すべてのデータは 1 つのテーブルに残りますが、物理的なストレージは特定のルールに従って異なるファイルに配置されます。これは MySQL でサポートされている機能であり、業務コードを変更する必要はありません。

ただし、SQL ステートメントを変更し、SQL 条件にパーティション列を含める必要があります。

欠点

（１）パーティションキーの設計は柔軟性に欠けており、パーティションキーを使用しないとテーブルロックが発生しやすい。

（２）パーティションテーブルに対してALTER TABLE ... ORDER BYを使用する場合、order byは各パーティション内でのみ実行できます。

（３）パーティションテーブルのパーティションキーにインデックスを作成すると、インデックスもパーティション化されます。パーティション キーにはグローバル インデックスというものはありません。

（４）データベースやテーブルを自分で分割し、業務シナリオやアクセスモードを自分で制御し、制御可能です。パーティション テーブルについては、R&D チームは SQL ステートメントを作成しましたが、どのパーティションをチェックすればよいか分からず、制御が困難でした。
...記載されていない、推奨されていない

ステージ5: 垂直分割

上記の 4 つの段階が完了しない場合は、垂直分割が実行されます。垂直分割の複雑さは、水平分割の複雑さよりもまだ小さいです。モジュールに応じてテーブルを異なる小さなテーブルに分割します。誰もが「大規模ウェブサイト アーキテクチャの進化」を読んだことがあるはずです。このタイプの記事や本では、基本的にこの段階について言及されています。
運よく通信事業者、銀行、その他の企業で働いている場合、1 つのテーブルに何百ものフィールドが存在するのが一般的であることに気付くでしょう。したがって分割する必要があり、分割の原則は一般的に次の 3 点です。

（１）あまり使用しないフィールドを別のテーブルに配置する。

（２）よく使うフィールドを別のテーブルに置く

（３）頻繁に組み合わせて検索される列を１つのテーブルに配置する（結合インデックス）。

ステージ6: 水平分割

はい、水平分割は最も面倒な段階です。分割後には多くの問題が発生します。水平分割は最後の選択肢である必要があることを再度強調します。ある意味、縦割りにした方が良いのかなと思います。垂直分割を使用して異なるモジュールに分割した後、単一のモジュールの圧力が大きすぎることが判明した場合、モジュールのマシン構成を改善するなど、モジュールを個別に完全に最適化できます。水平に 2 つのテーブルに分割する場合は、コードを変更する必要があり、その後、2 つのテーブルでは不十分であることが判明したため、再度コードを変更して 3 つのテーブルに分割しますか?水平分割モジュール間の結合が強すぎてコストが高すぎるため、特にお勧めできません。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。