BlackFlame33

MySQL の最適化は一般的にクエリ操作に関するもので、まず SQL クエリがどのような操作を経る必要があるかを理解する必要があります。

一、接続設定の最適化#

1.1 サーバー側の最適化#

デフォルトのサーバーの最大接続数（システムのデフォルトは一般的に 151）とデフォルトのクライアントのタイムアウト時間（デフォルトは一般的に 8 時間）を変更しますが、必要がなければ変更する必要はありません。以下はそれらのシステムデフォルト値を確認する方法です：

1.2 クライアント側の最適化#

クライアントの最適化では、クライアントが頻繁に接続を作成および破棄することを望んでいません。このため、データベース接続プールを使用して接続を再利用し、データベースリソースのコストを削減する必要があります。現在最も人気のあるデータベース接続プールは Druid と HikariCP です。DBCP と C3P0 はすでに。。。

もちろん、接続プールのサイズ設定は大きければ良いというわけではありません。各接続にはスレッドが必要であり、スレッド数が CPU の数を超えると、スレッドのコンテキストを頻繁に切り替える必要があり、パフォーマンスのコストが増大します。

二、アーキテクチャの最適化#

2.1. キャッシュの使用#

クエリとデータベースの間にキャッシュを追加し、あまり頻繁に変化しないデータを保存します。

2.2 読み書き分離#

この方法は主にサーバーの負荷を軽減することを目的としています。サーバークラスターを使用し、一台のサーバーをマスターノード、他のサーバーをスレーブノードとして使用します。マスターサーバーのデータが変更された場合、他のサーバーも同期する必要があります。マスターノードは追加、削除、変更の操作のみを担当し、スレーブノードはクエリを担当します。この方法はサーバーの負荷を大幅に軽減します。

2.3 データベースの分割とテーブルの分割#

2.3.1 垂直分割#

モノリシックデータベースの基盤の上で垂直に数回切り、ビジネスロジックに従って異なるテーブルを異なるデータベースに分割し、これらのデータベースは異なるサーバー上に存在します。これが垂直分割です。

2.3.2 垂直分表#

垂直分表は、単一のテーブルの基盤の上で垂直に切り（または数回切り）、一つのテーブルの複数のフィールドをいくつかの小さなテーブルに分割します。この操作は具体的なビジネスに基づいて判断する必要があり、通常は頻繁に使用されるフィールド（ホットフィールド）を一つのテーブルに分け、あまり使用されないまたは即座に使用されないフィールド（コールドフィールド）を別のテーブルに分けます。これにより、単一のテーブルのデータ量が過大になる問題を解決し、クエリ速度を向上させます。

上の図を例にとると、通常、商品の詳細情報は比較的長く、商品リストを表示する際には商品詳細を即座に表示する必要はありません（通常は詳細ボタンをクリックして表示されます）。そのため、商品のより重要な情報（価格など）を表示します。このビジネスロジックに従って、元の商品のテーブルを垂直に分割しました。

2.3.3 水平分割#

垂直分割の後、データベースのパフォーマンス問題はある程度解決されます。しかし、ビジネス量の増加に伴い、単一のデータベースに保存されているデータが予想を超えて増加し、単一のサーバーでは支えきれなくなります。しかし、ビジネスの観点から見ると、この時点で垂直分割は不可能です。この時、一定のルールに従って水平分割を試みることができます。例えば、id が奇数の商品の情報と id が偶数の商品の情報をそれぞれ二つのデータベースに分けることができます。

2.3.4 水平分表#

単一のテーブルのデータを一定のルール（業界用語で分片ルールと呼ばれる）に従って複数のデータテーブルに保存し、横にデータテーブルを一刀（または数刀）切ることが水平分表です。

2.3.5 まとめ#

水平分表は主にストレージのボトルネックを解決するためのものであり、垂直分表は主に同時実行の負荷を軽減するためのものです。

2.4 メッセージキューによるピークの緩和#

通常、ユーザーのリクエストは直接データベースにアクセスしますが、同時にオンラインユーザーの数が非常に多い場合、データベースが圧倒される可能性があります（有名人の不倫や恋愛発表時の微博の状態を参照）。

このような場合、メッセージキューを使用してデータベースの負荷を軽減することができます。同時に何人のユーザーがリクエストをしても、まずメッセージキューに保存し、その後システムが秩序正しくメッセージキューからリクエストを消費します。

三、オプティマイザー ——SQL 分析と最適化#

この段階に達すると、パーサーとオプティマイザーの領域に入ります。一般的に、SQL 構文に問題がなければ、パーサーに問題は発生しません。また、あなたが書いた SQL の実行効率が低くならないように、オプティマイザーは自動的にいくつかの最適化を行いますが、もし本当にひどい場合、オプティマイザーもあなたを救うことはできず、あなたの SQL クエリが遅いクエリに陥るのをただ見守ることしかできません。

3.1 遅いクエリ#

その名の通り、遅いクエリは非常に遅いクエリです。以下のコマンドを使用して MySQL の遅いクエリの状態を確認できます：

以下のコマンドを出力して遅いクエリの基準を確認します：

3.2 実行中のスレッドの確認#

show full processlist を実行して MySQL のすべてのスレッドを確認します。

その中で、

• Id： スレッドの一意の識別子で、Id を使用して特定のスレッドを終了できます；
• User： このスレッドを起動したユーザーで、通常のアカウントは自分のスレッドのみを表示できます；
• Host： どの IP とポートが接続を開始したか；
• db： スレッドが操作するデータベース；
• Command： スレッドのコマンド；
• Time： 操作の持続時間、単位は秒；
• State： スレッドの状態；
• Info： SQL 文の最初の 100 文字。

3.3 サーバーの実行状態の確認#

SHOW STATUS を使用して MySQL サーバーの実行状態を確認します。session と global の二つのスコープがあります。

上記のコードは select の回数を確認するものです。

3.4 ストレージエンジンの実行情報の確認#

SHOW ENGINE INNODB STATUS;

3.5 EXPLAIN 実行計画#

いわゆる計画とは、MySQL のオプティマイザーが私たちが書いた SQL 文を最適化するかどうか（例えば外部結合を内部結合に変更する、サブクエリを結合クエリに最適化するなど）を指します。オプティマイザーはこの SQL の実行に対してどのインデックスがコスト見積もりを行ったかを評価し、最終的にどのインデックスを使用するか（または最終的にインデックスを使用せず、全表スキャンを選択するか）を決定します。オプティマイザーが単一テーブルの実行戦略をどのようにするかなどです。

explain 実行計画は以前は通常クエリ文に使用されていましたが、現在は挿入、削除、更新の文にも使用できます。

3.6 SQL とインデックスの最適化#

3.6.1 SQL の最適化#

• 小さなテーブルで大きなテーブルを駆動する；join を使用してサブクエリ（ネストされたクエリ）を再構成する；または union に変更する；
• 結合クエリでは、駆動テーブルのファンアウト（レコード数）をできるだけ減らし、駆動テーブルへのアクセスコストをできるだけ低くし、駆動テーブルの結合列にインデックスをできるだけ構築してアクセスコストを下げる；駆動テーブルの結合列は、そのテーブルの主キーまたは一意の二次インデックス列であることが望ましく、そうすれば駆動テーブルのコストがさらに低くなります；
• 大きなオフセットの limit は、先にフィルタリングしてからソートします。

3.6.2 インデックスの最適化#

1. 回表しない方が良い場合は回表しない、回表する場合は回表回数を減らす（回表にはコストがかかり、外部ストレージからデータページをロードする可能性があります）；
2. インデックスカバレッジを使用します。

四、ストレージ構造とテーブル構造#

4.1 ストレージエンジンの選択#

異なるビジネスに応じて異なるストレージエンジンを選択することをお勧めします。例えば：

• クエリ操作や挿入操作が多いビジネステーブルには、MyISAM を推奨；
• 一時テーブルには Memory を使用；
• 同時実行数が多く、更新が多いビジネスには InnoDB を選択；
• 何を選ぶか分からない場合はデフォルトを使用。

4.2 フィールドの最適化#

原則：データを正しく保存できる最小のデータ型を使用する。

4.2.1 整数型#

MySQL は 6 種類の整数型を提供しています。それぞれは

• tinyint
• smallint
• mediumint
• int
• integer
• bigint

4.2.2 文字型#

フィールドの長さが不確定な場合は、無条件に varchar を選択します。なぜなら varchar はそのフィールドの長さを記録するための追加のスペースを持つからです。しかし、フィールドの長さが固定されている場合は、できるだけ char を選択することで、かなりのメモリスペースを節約できます。

4.2.3 非 NULL#

非 NULL フィールドはできるだけ NOT NULL に設定し、デフォルト値を提供するか、特別な値で NULL を代替します。なぜなら、NULL 型のストレージと最適化はパフォーマンスが悪くなる問題を引き起こすからです。

4.2.4 外部キー、トリガー、ビュー機能を使用しない（特に大規模データの場合、触れると G になる）#

これは「アリババ開発マニュアル」で言及されている原則です。理由は三つあります：

1. 可読性が低下し、コードをチェックする際にデータベースのコードも確認する必要がある；
2. 計算の作業をプログラム（ビジネス層）に任せ、データベースはストレージの作業をしっかりと行う；
3. 一般的に大規模データの場合、ストレージのボトルネックを解決するために水平分表を行う；
4. トリガーはデータの変化を即座にキャッチし、対応する処理を行うことを理解できますが、設計の初衷は非常に良いものの、欠点が利点を覆い、追加、削除、変更操作が頻繁にトリガーを引き起こし、リソース消費が特に大きくなります；
5. データの完全性検証の作業は開発者が行うべきであり、外部キーに依存すべきではありません。一旦外部キーを使用すると、テスト時にゴミデータを削除するのが非常に難しくなります（外部キー制約はテーブル構造を非常に混乱させ、循環制約を引き起こす可能性があります）。

4.2.5 画像、音声、動画のストレージ#

それらのアドレスを保存するだけで済みます。

4.2.6 大フィールドの分割とデータ冗長性#

大フィールドの分割は、前述の垂直分表と同じで、あまり使用されないフィールドやデータ量が大きいフィールドを分割し、列数が多すぎたりデータ量が大きすぎたりするのを避けます。特に SELECT * を頻繁に書く場合、列数が多くデータ量が大きいことによる問題が深刻化します！

フィールドの冗長性は原則としてデータベース設計の正規化に反しますが、迅速な検索に非常に有利です。例えば、契約テーブルに顧客 id を保存する際に、顧客名を冗長に保存することができます。これにより、クエリ時に顧客 id に基づいてユーザー名を取得する必要がなくなります。したがって、ビジネスロジックに応じて適度な冗長性を持たせることも良い最適化技術の一つです。（通常はクエリが多く、変更が少ないシナリオで使用されます）

五、ビジネスの最適化#

厳密に言えば、ビジネス面の最適化は MySQL の調整手段とは言えませんが、ビジネスの最適化はデータベースアクセスの負荷を非常に効果的に軽減することができます。この分野の典型的な例は淘宝（タオバオ）であり、以下にいくつかの簡単な例を挙げて皆さんに考え方を提供します：

1. 予約販売の分流：以前は双 11 の夜に買い物を始めるスタイルでしたが、最近数年では双 11 の予約販売がどんどん長くなり、半月以上前から始まり、さまざまなデポジットやクーポンのモデルが登場しています。この方法は予約販売の分流と呼ばれます。これにより、顧客のサービスリクエストを分流し、双 11 の午前 0 時に一斉に注文する必要がなくなります；
2. ダウングレード戦略：双 11 の午前 0 時にその日の注文を確認したいと思っても、確認に失敗することがあります；さらには、支付宝の小鳥の餌も遅れて配布されることがあります。これはダウングレード戦略であり、重要でないサービスの計算リソースを集結させ、現在の最も重要なビジネスを保証します；
3. 双 11 の際、支付宝は花呗での支払いを強く推奨し、銀行カードでの支払いを避けるようにしています。これは一部の考慮がソフトウェアの粘着性を高めるためですが、他方では、残高宝を使用することで実際に阿里内部のサーバーを使用し、アクセス速度が速くなります。一方、銀行カードを使用する場合、銀行のインターフェースを呼び出す必要があり、操作が遅くなります。