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 の深夜に一斉に注文する必要がなくなります；
2. ダウングレード戦略：双 11 の深夜に、あなたはその日の注文を確認したいかもしれませんが、確認に失敗することがあります；さらには、支付宝の小鳥の餌も遅れて配布されることがあります。これはダウングレード戦略であり、重要でないサービスの計算リソースを集結させ、現在の最もコアなビジネスを保証します；
3. 双 11 の際、支付宝は花呗での支払いを強く推奨し、銀行カードでの支払いを避けるようにしています。これは一部はソフトウェアの粘着性を高めるためですが、もう一方では、余额宝を使用することで実際に阿里内部のサーバーを使用し、アクセス速度が速くなりますが、銀行カードを使用する場合は銀行インターフェースを呼び出す必要があり、比較すると操作が遅くなります。