MySQL 2級コンピュータ試験共通テストポイント 8つのMySQLデータベース設計最適化方法

MySQLデータベース設計の8つの最適化方法の詳細は次のとおりです。

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

MySQL は大量のデータの保存とアクセスを適切にサポートできますが、一般的に、データベース内のテーブルが小さいほど、そのテーブルで実行されるクエリは高速になります。したがって、テーブルを作成するときに、より良いパフォーマンスを得るために、テーブル内のフィールドの幅をできるだけ小さく設定することができます。たとえば、郵便番号フィールドを定義するときに、CHAR(255) に設定すると、データベースに不要なスペースが追加されることは明らかです。CHAR(6) でもタスクを十分に完了できるため、VARCHAR 型を使用しても冗長です。同様に、可能であれば、整数フィールドを定義するには BIGIN ではなく MEDIUMINT を使用する必要があります。

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

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

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

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

顧客情報から削除
WHERE CustomerID が含まれない (SELECT CustomerID FROM salesinfo)

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

顧客情報から*を選択
WHERE CustomerID が含まれない (SELECT CustomerID FROM salesinfo)

このクエリを完了するために JOIN を使用すると、速度が大幅に向上します。特に、salesinfo テーブルの CustomerID にインデックスがある場合は、パフォーマンスが向上します。クエリは次のようになります。

顧客情報から*を選択
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo。
顧客ID
salesinfo.CustomerID が NULL の場合

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

3. 手動で作成した一時テーブルの代わりにUNIONを使用する

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

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

4. 事務

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

始める;
salesinfo に CustomerID=14 を挿入します。
在庫を更新 数量=11 に設定
ここで、item='book';
専念;

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

5. テーブルをロックする

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

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

LOCK TABLE 在庫書き込み
在庫から数量を選択
WHEREItem='本';
  ...
在庫を更新 数量=11 に設定
WHEREItem='本';
テーブルのロックを解除

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

6. 外部キーを使用する

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

  テーブル顧客情報を作成する
  （

  顧客ID INT NOT NULL、
  主キー (顧客ID)
  ) タイプ = INNODB;

  テーブル salesinfo を作成する

  （
  販売ID INT NULLではありません。
  顧客ID INT NOT NULL、
  主キー(顧客ID、販売ID)
  外部キー (CustomerID) 参照 customerinfo
  (顧客ID) ON DELETECASCADE

  ) タイプ = 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 タイプのフィールドで YEAE() 関数を使用すると、インデックスが正しく機能しなくなります。したがって、次の 2 つのクエリは同じ結果を返しますが、後者は前者よりもはるかに高速です。

SELECT * FROM order WHERE YEAR(OrderDate)＜2001;
SELECT * FROM order WHERE OrderDate＜"2001-01-01";

数値フィールドを計算するときにも同じ状況が発生します。

SELECT * FROM inventory WHERE Amount/7＜24;
SELECT * FROM inventory WHERE 金額＜24*7;

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

書籍から*を選択
「MySQL%」のようなWHERE名

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

書籍から*を選択
WHERE name＞="MySQL"かつ name＜"MySQM"

最後に、変換プロセスによってインデックスが無効になる可能性があるため、クエリ内で MySQL が自動型変換を実行しないよう注意する必要があります。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。