MySQL データベースを最適化する 8 つの方法の詳細な説明 (必読の定番)

導入：

インターネット上にはデータベースの最適化に関する情報や方法が数多くありますが、その多くは品質にばらつきがあり、要約が不十分で内容が冗長なものもあります。

この記事はたまに見つけました。アクセス数の多い定番の要約です。そこで、自分の要約集に取り入れ、質の高い記事を蓄積し、個人の能力を高めました。今後の発展にも皆様のお役に立てれば幸いです。

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

MySQL は大量のデータの保存とアクセスを適切にサポートできますが、一般的に、データベース内のテーブルが小さいほど、そのテーブルで実行されるクエリは高速になります。したがって、テーブルを作成するときに、より良いパフォーマンスを得るために、テーブル内のフィールドの幅をできるだけ小さく設定することができます。

たとえば、郵便番号フィールドを定義するときに、CHAR(255) に設定すると、データベースに不要なスペースが追加されることは明らかです。CHAR(6) でもタスクを十分に完了できるため、VARCHAR 型を使用しても冗長です。同様に、可能であれば、整数フィールドを定義するには BIGIN ではなく MEDIUMINT を使用する必要があります。

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

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

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

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

顧客情報から削除
CustomerID が含まれない場所 (salesinfo から CustomerID を選択)

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

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

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

顧客情報から*を選択
LEFTJOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID
salesinfo.CustomerIDISNULL の場合

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

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

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

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

4. 事務

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

始める;
 INSERTINTOsalesinfoSET顧客ID=14;
 UPDATEinventorySETQuantity=11WHEREitem='book';
専念;

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

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

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

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

LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem='book';
...
UPDATEinventorySETQuantity=11WHEREItem='book';UNLOCKTABLES

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

6. 外部キーを使用する

ロックテーブル方式ではデータの整合性は維持できますが、データの関連性を保証することはできません。このとき、外部キーを使用できます。

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

CREATETABLE customerinfo( CustomerID INTNOTNULL, PRIMARYKEY(CustomerID)) TYPE=INNODB;
CREATETABLE salesinfo( 販売IDINTNOTNULL, 顧客IDINTNOTNULL,
PRIMARYKEY(顧客ID、販売ID)、
FOREIGNKEY(CustomerID)REFERENCEScustomerinfo(CustomerID)ONDELETECASCADE)TYPE=INNODB;

例のパラメータ「ONDELETECASCADE」に注意してください。このパラメータにより、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 ステートメントが適切に使用されていない場合、インデックスは本来の役割を果たしません。

注目すべき点がいくつかあります。

a. まず、同じタイプのフィールドを比較するのが最適です。

MySQL 3.23 より前では、これは必須でした。たとえば、インデックス付き INT フィールドと BIGINT フィールドを比較することはできませんが、特別なケースとして、フィールド サイズが同じ場合は CHAR 型フィールドと VARCHAR 型フィールドを比較できます。

b. 次に、インデックス付きフィールドを操作する関数を使用しないようにしてください。

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

c. 3 番目に、文字フィールドを検索するときに、LIKE キーワードとワイルドカードを使用することがあります。このアプローチは単純ですが、システム パフォーマンスも犠牲になります。

たとえば、次のクエリはテーブル内のすべてのレコードを比較します。

書籍から*を選択
WHERE 名前類似体 "MySQL%"

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

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

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

上記は、私が皆さんに紹介したMySQLデータベースを最適化する8つの方法です（定番必読）。皆さんのお役に立てれば幸いです。ご質問があれば、メッセージを残してください。すぐに返信いたします。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。