MySQL データベースのパフォーマンスを 8 つの側面から完全に最適化する方法を説明します。 _PHP チュートリアル

1. 最も適切なフィールド属性を選択します

MySQL は大量のデータへのアクセスを非常に適切にサポートできますが、一般的に言えば、データベース内のテーブルが小さいほど、テーブル上で実行されるクエリは高速になります。したがって、テーブルを作成するときに、パフォーマンスを向上させるために、テーブル内のフィールドの幅をできるだけ小さく設定できます。たとえば、郵便番号フィールドを定義する場合、CHAR(255) に設定すると、CHAR(6) で問題ないため、VARCHAR 型を使用しても明らかに冗長になります。同様に、可能であれば、整数フィールドを定義するには BIGIN の代わりに MEDIUMINT を使用する必要があります。

効率を向上させるもう 1 つの方法は、可能であればフィールドを NOT NULL に設定し、今後クエリを実行するときにデータベースが NULL 値を比較する必要がないようにすることです。

「都道府県」や「性別」などの一部のテキスト フィールドについては、ENUM タイプとして定義できます。 MySQL では ENUM 型は数値データとして扱われ、数値データはテキスト型よりもはるかに高速に処理されるためです。このようにして、データベースのパフォーマンスを向上させることができます。

2. サブクエリの代わりに JOIN を使用します

MySQL は 4.1 以降、SQL サブクエリをサポートします。この手法を使用すると、SELECT ステートメントを使用してクエリ結果の単一列を作成し、この結果を別のクエリのフィルター条件として使用できます。例えば、基本顧客情報テーブルにある注文のない顧客を削除したい場合、サブクエリを使用して、まず売上情報テーブルから注文を行ったすべての顧客のIDを取得し、その結果を次のテーブルに渡すことができます。以下に示すメインクエリ:

CustomerID が含まれていない customerinfo から削除 (salesinfo から CustomerID を選択)

サブクエリを使用すると、論理的に複数のステップを一度に完了する必要がある多くの SQL 操作を完了でき、トランザクションやテーブルのロックも回避でき、記述も簡単です。ただし、場合によっては、サブクエリをより効率的な結合 (JOIN) に置き換えることができます。たとえば、注文レコードを持たないすべてのユーザーを取得したいと仮定すると、次のクエリを使用してそれを完了できます:

SELECT * FROM customerinfo WHERE CustomerID NOT (Salesinfo から CustomerID を選択)

接続 (JOIN).. を使用してこのクエリを完了すると、速度が大幅に速くなります。特に salesinfo テーブルに CustomerID のインデックスがある場合、クエリは次のようになります:

SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL

接続 (JOIN)。この方が効率的である理由は、MySQL がこの論理的な 2 段階のクエリを完了するためにメモリ内に一時テーブルを作成する必要がないためです。

3. UNION を使用して手動で作成した一時テーブルを置き換えます

MySQL はバージョン 4.0 以降、UNION クエリをサポートします。これにより、一時テーブルの使用を必要とする 2 つ以上の SELECT クエリを 1 つのクエリに結合できます。クライアントのクエリ セッションが終了すると、データベースが整然と効率的に保たれるように、一時テーブルは自動的に削除されます。 UNION を使用してクエリを作成する場合、複数の SELECT ステートメントを接続するキーワードとして UNION を使用するだけで済みます。すべての SELECT ステートメントのフィールドの数が同じである必要があることに注意してください。次の例は、UNION を使用したクエリを示しています。

クライアントから名前、電話番号を選択 UNION 著者から名前、生年月日を選択
ユニオン
製品から名前、サプライヤーを選択してください

4. 事務

サブクエリ、接続 (JOIN)、ユニオン (UNION) を使用してさまざまなクエリを作成できますが、すべてのデータベース操作を 1 つまたはいくつかの SQL ステートメントだけで完了できるわけではありません。特定の種類の作業を完了するには、一連のステートメントが必要になることがよくあります。ただし、この場合、このステートメント ブロック内の特定のステートメントが誤って実行されると、ステートメント ブロック全体の動作が不確実になります。特定のデータを 2 つの関連するテーブルに同時に挿入したいとします。最初のテーブルが正常に更新された後、データベースで予期しない状況が発生し、2 番目のテーブルの操作が完了しない場合があります。このようにして、データは不完全になり、データベース内のデータさえも破壊されます。この状況を回避するには、トランザクションを使用する必要があります。その機能は、ステートメント ブロック内のすべてのステートメントが成功するか失敗するかのいずれかです。つまり、データベース内のデータの一貫性と完全性を維持できます。物事は BEGIN キーワードで始まり COMMIT キーワードで終わります。この期間中に SQL 操作が失敗した場合、ROLLBACK コマンドによってデータベースを BEGIN が開始される前の状態に復元できます。

始めてください;

Salesinfo に挿入 SET CustomerID=14;

在庫設定を更新 数量=11

WHERE item='本';

コミット;

トランザクションのもう 1 つの重要な役割は、複数のユーザーが同じデータ ソースを同時に使用するときに、データベースをロックする方法を使用してユーザーに安全なアクセス方法を提供し、ユーザーの操作が他のユーザーによって干渉されないようにできることです。ユーザー。

5. ロックテーブル

トランザクションはデータベースの整合性を維持するための非常に優れた方法ですが、その排他性により、特に大規模なアプリケーション システムでは、データベースのパフォーマンスに影響を与えることがあります。トランザクションの実行中はデータベースがロックされるため、他のユーザーのリクエストはトランザクションが終了するまで待つことしかできません。データベース システムのユーザーが数人しかいない場合

トランザクションの影響は大きな問題ではありませんが、電子商取引 Web サイトにアクセスするなど、数千人のユーザーがデータベース システムに同時にアクセスすると、重大な応答の遅延が発生します。

実際、場合によっては、テーブルをロックすることでパフォーマンスが向上することがあります。次の例では、ロック テーブル メソッドを使用して、前の例のトランザクション関数を完了します。

テーブルインベントリ書き込みをロック
在庫から数量を選択してください
WHEREItem='本';
...

在庫設定を更新 数量=11
WHEREItem='本';
テーブルのロックを解除

ここでは、SELECT ステートメントを使用して初期データを取得し、いくつかの計算を通じて UPDATE ステートメントを使用して新しい値をテーブルに更新します。 WRITE キーワードを含む LOCK TABLE ステートメントは、UNLO​​CK TABLES コマンドが実行される前に、インベントリを挿入、更新、または削除するための他のアクセスがないことを保証します。

6. 外部キーを使用する

テーブルをロックする方法ではデータの整合性を維持できますが、データの関連性は保証できません。現時点では、外部キーを使用できます。たとえば、外部キーを使用すると、各販売レコードが既存の顧客を指していることを確認できます。ここで、外部キーは、customerinfo テーブルの CustomerID を salesinfo テーブルの CustomerID にマップできます。有効な CustomerID のないレコードは更新されず、salesinfo に挿入されません。

顧客情報テーブルを作成します
(
CustomerID INT NOT NULL 、
主キー (顧客ID)
) タイプ = INNODB;
CREATE TABLE 販売情報
(
SalesID INT が NULL ではありません、
CustomerID INT が NULL ではありません、
PRIMARY KEY(CustomerID, SalesID),
外部キー (顧客 ID) の参照顧客情報
(顧客ID) 削除カスケード
) タイプ = INNODB;

例のパラメータ「ON DELETE CASCADE」に注目してください。このパラメータにより、customerinfo テーブル内の顧客レコードが削除されると、salesinfo テーブル内の顧客に関連するすべてのレコードも自動的に削除されます。 MySQL で外部キーを使用する場合は、テーブルの作成時にテーブル タイプをトランザクション セーフなテーブル InnoDB タイプとして定義することを忘れないでください。このタイプは、MySQL テーブルのデフォルトのタイプではありません。これを定義するには、CREATE TABLE ステートメントに TYPE=INNODB を追加します。例に示すように。

7. インデックスを使用する

インデックス付けはデータベースのパフォーマンスを向上させる一般的な方法で、特にクエリ ステートメントに MAX()、MIN()、ORDERBY などのコマンドが含まれている場合、データベース サーバーはインデックスを使用しない場合よりもはるかに高速に特定の行を取得できます。明らか。それでは、どのフィールドにインデックスを付ける必要があるのでしょうか?一般的に、インデックスは JOIN、WHERE 判定、ORDER BY ソートに使用されるフィールドに構築する必要があります。多数の重複値を含むデータベース内のフィールドにインデックスを作成しないようにしてください。 ENUM タイプのフィールドの場合、customerinfo の "province".. フィールドなど、重複する値が多数存在する可能性が非常に高くなります。そのようなフィールドにインデックスを作成しても、逆に役に立たない可能性があります。データベースのパフォーマンスを低下させます。テーブルの作成時に適切なインデックスを同時に作成することも、ALTER TABLE または CREATE INDEX を使用して後でインデックスを作成することもできます。さらに、MySQL

全文インデックス作成と検索は、バージョン 3.23.23 以降でサポートされます。フルテキスト インデックスは MySQL では FULLTEXT 型のインデックスですが、MyISAM 型のテーブルでのみ使用できます。大規模なデータベースの場合、FULLTEXT インデックスを使用せずにデータをテーブルにロードし、ALTER TABLE または CREATE INDEX を使用してインデックスを作成すると非常に高速になります。ただし、すでに FULLTEXT インデックスがあるテーブルにデータをロードすると、実行プロセスが非常に遅くなります。

8. 最適化されたクエリステートメント

ほとんどの場合、インデックスを使用するとクエリの速度が向上しますが、SQL ステートメントが適切に使用されないと、インデックスは本来の役割を果たせなくなります。以下に、注意すべきいくつかの側面を示します。まず、同じ型のフィールド間で比較演算を実行するのが最善です。 MySQL バージョン 3.23 より前では、これは必須条件でさえありました。たとえば、インデックス付き INT フィールドは BIGINT フィールドと比較できませんが、特殊な場合として、CHAR 型フィールドと VARCHAR 型フィールドが同じサイズの場合は比較できます。次に、インデックス付きフィールドを操作するために関数を使用しないようにしてください。

たとえば、DATE 型フィールドに対して YEAE() 関数を使用すると、インデックスは正常に機能しません。したがって、次の 2 つのクエリは同じ結果を返しますが、後者の方が前者よりもはるかに高速です。

SELECT * FROM order WHERE YEAR(OrderDate) SELECT * FROM order WHERE OrderDate
数値フィールドを計算するときにも同じ状況が発生します:

SELECT * FROM 在庫 WHERE 数量/7 SELECT * FROM 在庫 WHERE 金額
上記 2 つのクエリも同じ結果を返しますが、後者のクエリは前のクエリよりもはるかに高速になります。 3 番目に、文字フィールドを検索するときに、LIKE キーワードとワイルドカードを使用することがあります。このアプローチは単純ですが、システムのパフォーマンスも犠牲になります。たとえば、次のクエリはテーブル内のすべてのレコードを比較します。

本から * 選択してください
「MySQL%」のような WHERE 名

ただし、代わりに次のクエリを使用すると、返される結果は同じになりますが、速度ははるかに速くなります:

本から * 選択してください
WHERE 名前>="MySQL" および名前
最後に、変換プロセスによってインデックスも役に立たなくなるため、MySQL がクエリ内で自動型変換を実行しないように注意する必要があります。

http://www.bkjia.com/PHPjc/631857.htmlwww.bkjia.comtru​​ehttp://www.bkjia.com/PHPjc/631857.html技術記事 1. 最も適切なフィールド属性を選択します。MySQL は大量のデータへのアクセスを十分にサポートしますが、一般的に、データベース内のテーブルが小さいほど、テーブル上で実行されるクエリが高速になります。だから…