1. 最も適切なフィールド属性を選択します。MySQL
は大量のデータへのアクセスを十分にサポートしますが、一般的に、データベース内のテーブルが小さいほど、テーブル上で実行されるクエリが高速になります。したがって、テーブルを作成するときに、パフォーマンスを向上させるために、テーブル内のフィールドの幅をできるだけ小さく設定できます。たとえば、郵便番号フィールドを定義する場合、CHAR(255) に設定すると、CHAR(6) で問題ないため、VARCHAR 型を使用しても明らかに冗長になります。同様に、可能であれば、整数フィールドを定義するには BIGIN の代わりに 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)
サブクエリを使用すると、論理的に複数のステップを一度に完了する必要がある多くの SQL 操作を完了でき、トランザクションやテーブルのロックも回避でき、記述も簡単です。ただし、場合によっては、サブクエリをより効率的な結合 (JOIN) に置き換えることができます。たとえば、注文レコードを持たないすべてのユーザーを取得したいとすると、次のクエリを使用できます:
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
このクエリを完了するために接続 (JOIN).. を使用すると、速度が大幅に速くなります。特に salesinfo テーブルに CustomerID のインデックスがある場合、クエリは次のようになります。
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
顧客ID
WHERE salesinfo.CustomerID IS NULL
connection (JOIN).. これが効率的である理由は、MySQL がこの論理 2 段階のクエリを完了するためにメモリ内に一時テーブルを作成する必要がないためです。
3. ユニオン (UNION) を使用して、手動で作成した一時テーブルを置き換えます。MySQL
は、バージョン 4.0 以降、一時テーブルの使用を必要とする 2 つ以上の SELECT クエリを 1 つのクエリにマージできる UNION クエリをサポートします。クライアントのクエリ セッションが終了すると、データベースが整然と効率的に保たれるように、一時テーブルは自動的に削除されます。 UNION を使用してクエリを作成する場合、複数の SELECT ステートメントを接続するキーワードとして UNION を使用するだけで済みます。すべての SELECT ステートメントのフィールドの数が同じである必要があることに注意してください。次の例は、UNION を使用したクエリを示しています。
クライアントからの名前、電話番号を選択します
連合
著者から名前、生年月日を選択
連合
SELECT Name, Supplier FROM product
4. トランザクション
サブクエリ、接続 (JOIN)、ユニオン (UNION) を使用してさまざまなクエリを作成できますが、すべてのデータベース操作を 1 つだけで実行できるわけではありません。いくつかの SQL ステートメント。特定の種類の作業を完了するには、一連のステートメントが必要になることがよくあります。ただし、この場合、このステートメント ブロック内の特定のステートメントが誤って実行されると、ステートメント ブロック全体の動作が不確実になります。特定のデータを 2 つの関連するテーブルに同時に挿入したいとします。最初のテーブルが正常に更新された後、データベースで予期しない状況が発生し、2 番目のテーブルの操作が完了しない場合があります。このようにして、データは不完全になり、データベース内のデータさえも破壊されます。この状況を回避するには、トランザクションを使用する必要があります。その機能は、ステートメント ブロック内のすべてのステートメントが成功するか失敗するかのいずれかです。つまり、データベース内のデータの一貫性と完全性を維持できます。物事は BEGIN キーワードで始まり COMMIT キーワードで終わります。この期間中に SQL 操作が失敗した場合、ROLLBACK コマンドによってデータベースを BEGIN が開始される前の状態に復元できます。
始める;
Salesinfo SET CustomerID=14 に挿入します。
在庫設定を更新 数量=11
WHERE item='本';
COMMIT;
トランザクションのもう 1 つの重要な役割は、複数のユーザーが同時に同じデータ ソースを使用するときに、データベースをロックする方法を使用して、ユーザーの操作がブロックされないように安全なアクセス方法を提供できることです。他のユーザーによって妨害されました。
5. テーブルのロック
トランザクションはデータベースの整合性を維持するための非常に優れた方法ですが、その排他性により、特に大規模なアプリケーション システムでは、データベースのパフォーマンスに影響を与えることがあります。トランザクションの実行中はデータベースがロックされるため、他のユーザーのリクエストはトランザクションが終了するまで待つことしかできません。データベース システムを少数のユーザーだけが使用する場合、トランザクションの影響は大きな問題にはなりませんが、電子商取引 Web サイトへのアクセスなど、数千のユーザーがデータベース システムに同時にアクセスすると、重大な問題が発生します。応答の遅れ。
実際、場合によっては、テーブルをロックすることでパフォーマンスが向上することがあります。次の例では、ロック テーブル メソッドを使用して、前の例のトランザクション関数を完了します。
LOCK TABLE インベントリ書き込み
在庫から数量を選択
WHEREItem='本';
...
在庫設定を更新 数量=11
WHEREItem='本';
UNLOCK TABLES
ここでは、SELECT ステートメントを使用して初期データを取得し、いくつかの計算を通じて、UPDATE ステートメントを使用して新しい値をテーブルに更新します。 WRITE キーワードを含む LOCK TABLE ステートメントは、UNLOCK TABLES コマンドが実行される前に、挿入、更新、または削除のためのインベントリへの他のアクセスがないことを保証します。
6.
外部キーを使用してテーブルをロックする方法では、データの整合性は維持できますが、データの関連性は保証できません。現時点では、外部キーを使用できます。たとえば、外部キーを使用すると、各販売レコードが既存の顧客を指していることを確認できます。ここで、外部キーは、customerinfo テーブルの CustomerID を salesinfo テーブルの CustomerID にマップできます。有効な CustomerID のないレコードは更新されず、salesinfo に挿入されません。
テーブルの作成顧客情報
(
CustomerID INT NOT NULL 、
主キー (顧客ID)
) TYPE = INNODB
CREATE TABLE salesinfo
;
(
SalesID INT が NULL ではありません。
CustomerID INT が NULL ではありません。
主キー(顧客ID、販売ID)、
外部キー (顧客 ID) の参照 customerinfo
(CustomerID) ON DELETECASCADE
) TYPE = 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)<2001;
SELECT
* FROM order WHERE OrderDate<"2001-01-01";
SELECT
* FROM inventory WHERE Amount/ 7<24;
* FROM inventory WHERE Amount<24*7;
上記の 2 つのクエリも同じ結果を返しますが、後者のクエリは前のクエリよりもはるかに高速です。 3 番目に、文字フィールドを検索するときに、LIKE キーワードとワイルドカードを使用することがあります。このアプローチは単純ですが、システムのパフォーマンスも犠牲になります。たとえば、次のクエリはテーブル内のすべてのレコードを比較します。
本から * を選択してください
「MySQL%」のような WHERE 名
ただし、次のクエリを使用すると、返される結果は同じですが、速度ははるかに速くなります:
SELECT * FROM Books
WHERE name>="MySQL" and name<"MySQM"
最後に、MySQL がクエリ内で自動型変換を実行しないように注意する必要があります。変換プロセスによってインデックスも無効になるためです。