8 つの非常に優れた MySQL 最適化体験_PHP チュートリアル
- WBOYオリジナル
- 2016-07-21 15:53:46745ブラウズ
1. 最も適切なフィールド属性を選択します
MySQL は大量のデータへのアクセスを十分にサポートしますが、一般的に言えば、データベース内のテーブルが小さいほど、クエリの実行は速くなります。したがって、テーブルを作成するときに、パフォーマンスを向上させるために、テーブル内のフィールドの幅をできるだけ小さく設定できます。たとえば、郵便番号フィールドを定義する場合、CHAR(255) に設定すると、CHAR(6) で問題ないため、VARCHAR 型を使用しても明らかに冗長になります。同様に、可能であれば、整数フィールドを定義するには BIGINT ではなく MEDIUMINT を使用する必要があります。
効率を向上させるもう 1 つの方法は、可能であればフィールドを NOT NULL に設定して、今後クエリを実行するときにデータベースが NULL 値を比較する必要がないようにすることです。
「都道府県」や「性別」などの一部のテキストフィールドについては、ENUM タイプとして定義できます。 MySQL では ENUM 型は数値データとして扱われ、数値データはテキスト型よりもはるかに高速に処理されるためです。このようにして、データベースのパフォーマンスを向上させることができます。
2. サブクエリ (Sub-Queries) の代わりに結合 (JOIN) を使用します
MySQL は 4.1 から SQL サブクエリをサポートします。この手法を使用すると、SELECT ステートメントを使用してクエリ結果の単一列を作成し、この結果を別のクエリのフィルター条件として使用できます。例えば、基本顧客情報テーブルにある注文のない顧客を削除したい場合、サブクエリを使用して、まず売上情報テーブルから注文を行ったすべての顧客のIDを取得し、その結果を次のテーブルに渡すことができます。以下に示すメイン クエリ:
DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);
サブクエリを使用すると、論理的に複数の手順を一度に完了する必要がある多くの SQL 操作を完了でき、トランザクションやトランザクションを回避することもできます。テーブルロックも簡単に作成できます。ただし、場合によっては、サブクエリをより効率的な結合 (JOIN) に置き換えることができます。たとえば、注文レコードを持たないすべてのユーザーを抽出すると仮定すると、次のクエリを使用してこれを完了できます:
SELECT * FROM customerinfo WHERE CustomerID NOT IN (Select CustomerID FROM salesinfo);
接続を使用する場合このクエリ作業を完了するための (JOIN) の方がはるかに速くなります。特に salesinfo テーブルに CustomerID のインデックスがある場合、クエリは次のようになります:
SELECT * FROM customerinfo LEFT JOIN customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL; MySQL はこの論理的な 2 段階のクエリを完了するためにメモリ内に一時テーブルを作成する必要がないため、接続 (JOIN ) の方が効率的です。
3. UNION を使用して手動で作成した一時テーブルを置き換えます
MySQL は、バージョン 4.0 以降、一時テーブルの使用を必要とする 2 つ以上の SELECT クエリを 1 つのクエリ中間にマージできる UNION クエリをサポートします。クライアントのクエリ セッションが終了すると、データベースが整然と効率的に保たれるように、一時テーブルは自動的に削除されます。 UNION を使用してクエリを作成する場合、複数の SELECT ステートメントを接続するキーワードとして UNION を使用するだけで済みます。すべての SELECT ステートメントのフィールドの数が同じである必要があることに注意してください。次の例は、UNION を使用したクエリを示しています。
クライアントから名前、電話番号を選択 UNION 作成者から名前、生年月日を選択 UNION 製品から名前、サプライヤーを選択
4、トランザクション
サブクエリ (Sub-Query)、接続 (JOIN)、およびユニオン (UNION) を使用してさまざまなクエリを作成できますが、すべてのデータベース操作を 1 つまたはいくつかの SQL ステートメントだけで完了できるわけではありません。特定の種類の作業を完了するには、一連のステートメントが必要になることがよくあります。
しかし、この場合、このステートメントブロック内の特定のステートメントが誤って実行されると、ステートメントブロック全体の動作が不確実になります。特定のデータを 2 つの関連するテーブルに同時に挿入したいとします。最初のテーブルが正常に更新された後、データベースで予期しない状況が発生し、2 番目のテーブルの操作が完了しない場合があります。このようにして、データは不完全になり、データベース内のデータさえも破壊されます。
この状況を回避するには、トランザクションを使用する必要があります。その機能は、ステートメント ブロック内のすべてのステートメントが成功するか、すべてが失敗するかのどちらかです。つまり、データベース内のデータの一貫性と完全性を維持できます。トランザクションは BEGIN キーワードで開始され、COMMIT キーワードで終了します。この期間中に SQL 操作が失敗した場合、ROLLBACK コマンドによってデータベースを BEGIN が開始される前の状態に復元できます。
BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11 WHERE item='book';
COMMIT; トランザクションのもう 1 つの重要な役割は、複数のユーザーが同時に同じデータ ソースを使用する場合です。データベースをロックする方法を使用してユーザーに安全なアクセス方法を提供し、ユーザーの操作が他のユーザーによって干渉されないようにすることができます。
5. テーブルのロック
トランザクションはデータベースの整合性を維持するための非常に優れた方法ですが、その排他性により、特に大規模なアプリケーション システムでは、データベースのパフォーマンスに影響を与えることがあります。トランザクションの実行中はデータベースがロックされるため、他のユーザーのリクエストはトランザクションが終了するまで待つことしかできません。データベース システムを少数のユーザーだけが使用する場合、トランザクションの影響は大きな問題にはなりませんが、電子商取引 Web サイトにアクセスするなど、数千のユーザーがデータベース システムに同時にアクセスすると、重大な問題が発生します。応答の遅れ。
実際、テーブルをロックすることでパフォーマンスが向上する場合があります。次の例では、ロック テーブル メソッドを使用して、前の例のトランザクション関数を完了します。
LOCK TABLE inventory WRITE Select Quantity FROM inventory WHERE item='book';
…
UPDATE inventory SET Quantity=11 WHERE item='book';
UNLOCK TABLES
ここでは、SELECT ステートメントを使用して初期データを取得し、 UPDATE ステートメントを使用して、テーブルに新しい値を計算して更新します。 WRITE キーワードを含む LOCK TABLE ステートメントは、UNLOCK TABLES コマンドが実行される前に、インベントリを挿入、更新、または削除するための他のアクセスがないことを保証します。
6. 外部キーの使用
テーブルをロックする方法ではデータの整合性を維持できますが、データの関連性は保証できません。現時点では、外部キーを使用できます。たとえば、外部キーを使用すると、各販売レコードが既存の顧客を指していることを確認できます。ここで、外部キーは、customerinfo テーブルの CustomerID を salesinfo テーブルの CustomerID にマップできます。有効な CustomerID のないレコードは更新されず、salesinfo に挿入されません。
CREATE TABLE customerinfo(CustomerID INT NOT NULL, PRIMARY KEY (CustomerID)) TYPE = INNODB;
CREATE TABLE salesinfo (SalesID INT NOT NULL, CustomerID INT NOT NULL, PRIMARY KEY(CustomerID, SalesID), FOREIGN KEY (CustomerID) 参照customerinfo (CustomerID) ON DELETE CASCADE ) TYPE = INNODB;
この例のパラメータ「ON DELET CASCADE」に注目してください。このパラメータにより、customerinfo テーブル内の顧客レコードが削除されると、salesinfo テーブル内の顧客に関連するすべてのレコードも自動的に削除されます。 MySQL で外部キーを使用する場合は、テーブルの作成時にテーブル タイプをトランザクション セーフな InnoDB タイプとして定義することを忘れないでください。このタイプは、MySQL テーブルのデフォルトのタイプではありません。定義方法は、CREATE TABLE 文に TYPE=INNODB を追加します。例に示すように。
7. インデックスを使用する
インデックスは、特にクエリ ステートメントに MAX() が含まれる場合、データベース パフォーマンスを向上させる一般的な方法です。 ) および ORDER BY コマンドを使用すると、パフォーマンスの向上がより明白になります。それでは、どのフィールドにインデックスを付ける必要があるのでしょうか?一般的に、インデックスは 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 型フィールドで YEAR() 関数を使用すると、インデックスは正常に機能しません。したがって、次の 2 つのクエリは同じ結果を返しますが、後者の方が前者よりもはるかに高速です。
SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<'2001-01-01';
SELECT * FROM inventory WHERE Amount/7SELECT * FROM inventory WHERE Amount上記の 2 つのクエリも同じ結果を返しますが、後者のクエリは前のクエリよりもはるかに高速です。 3 番目に、文字フィールドを検索するときに、LIKE キーワードとワイルドカードを使用することがあります。このアプローチは単純ですが、システムのパフォーマンスも犠牲になります。たとえば、次のクエリはテーブル内のすべてのレコードを比較します。
SELECT * FROM Books WHERE name LIKE 'MySQL%';
ただし、次のクエリを使用すると、返される結果は同じですが、速度が大幅に速くなります。 ' AND name<'MySQM';
最後に、MySQL がクエリ内で自動型変換を実行しないように注意する必要があります。これは、変換プロセスによりインデックスが無効になるためです。