MySQL 最適化における 8 つの非常に良い経験

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

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

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

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

2. サブクエリ (サブクエリ) の代わりに結合 (JOIN) を使用する

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

DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);

サブクエリを使用すると、論理的に複数の手順を 1 回で完了する必要がある多くの 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;

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


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

クライアントから名前、電話番号を選択 UNION 作成者から名前、生年月日を選択 UNION 製品から名前、サプライヤーを選択


4.サブクエリ (Sub-Queries)、接続 (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'; トランザクションのもう 1 つの重要な役割は次のとおりです。複数のユーザーが同じデータ ソースを同時に使用する場合、データベースをロックする方法を使用してユーザーに安全なアクセス方法を提供し、ユーザーの操作が他のユーザーによって干渉されないようにすることができます。


5. テーブルのロック
トランザクションはデータベースの整合性を維持するための非常に優れた方法ですが、その排他性により、データベースのパフォーマンスに影響を与える場合があります。 . 特に大規模なアプリケーション システムで。トランザクションの実行中はデータベースがロックされるため、他のユーザーのリクエストはトランザクションが終了するまで待つことしかできません。データベース システムを少数のユーザーだけが使用する場合、トランザクションの影響は大きな問題にはなりませんが、電子商取引 Web サイトへのアクセスなど、数千のユーザーがデータベース システムに同時にアクセスすると、重大な問題が発生します。応答の遅れ。

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

在庫テーブルをロック 書き込み 在庫から数量を選択 WHERE item='book';
…
在庫を更新 数量=11 WHERE item='book'
テーブルのロックを解除
ここでは、SELECT ステートメントを使用して初期データを取得し、いくつかの計算を通じて UPDATE ステートメントを使用して新しい値をテーブルに更新します。 WRITE キーワードを含む LOCK TABLE ステートメントは、UNLO​​CK 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) REFERENCES customerinfo (CustomerID) ON DELETE CASCADE ) TYPE = INNODB;
例のパラメータ「ON DELETE CASCADE」に注意してください。このパラメータにより、customerinfo テーブル内の顧客レコードが削除されると、salesinfo テーブル内の顧客に関連するすべてのレコードも自動的に削除されます。 MySQL で外部キーを使用する場合は、テーブルの作成時にテーブル タイプをトランザクション セーフな InnoDB タイプとして定義することを忘れないでください。このタイプは、MySQL テーブルのデフォルトのタイプではありません。定義方法は、CREATE TABLE 文に TYPE=INNODB を追加します。例に示すように。

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

インデックスを使用すると、データベース サーバーがインデックスを使用しない場合よりもはるかに高速に特定の行を取得できるようになります。クエリ ステートメントに MAX()、MIN()、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)SELECT * FROM order WHERE OrderDate対数値でも同じ状況が発生します。

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

SELECT * FROM Books WHERE name LIKE 'MySQL%';

しかし、次のクエリを使用すると、返される結果は同じですが、速度ははるかに速くなります。 🎜 >SELECT * FROM Books WHERE name>='MySQL' AND name
最後に、MySQL がクエリ内で自動型変換を実行しないように注意する必要があります。インデックスを使用不能にします。