6.1 はじめに
それでは、理論から実践に移りましょう。
私たちは現実世界に住んでおり、すべてのソフトウェア製品は最終的には生きている人々のために作られています。そして、これらの生きている人々は、読み込みが遅いサイトや速度が遅いプログラムに非常に悩まされています。
また、データベース クエリに 1 秒以上かかる場合、これは容認できません。ユーザーは、ページや機能が非常に遅い製品を使用することはありません。
しかし、多くの場合、1 ページを表示するには、データベースに対して数十のクエリを実行する必要があります。また、それらが順番に実行される場合、2 番目の制限はなくなりますが、リクエストごとに 100 ミリ秒としましょう。
プログラマがデータベース クエリを高速化する上位 5 つの方法は次のとおりです。
- データベース内のテーブルにインデックスを追加します。
- クエリの書き換えと最適化。
- データベース側でキャッシュを有効化 (および構成) します。
- クライアント側でキャッシュを有効にします。
- データベースの非正規化を実行します。
皆さんはこれらすべてのことについてほとんどのことをすでによく知っているので、以下は実践的なアドバイスにすぎません。
6.2 指数
ほぼすべてのサイトで、データベースの操作が作業の大部分を占めることは周知の事実です。そして、Web アプリケーションのボトルネックとなることが多いデータベースも操作します。
この記事では、MySQL の使用に関する実践的なアドバイスを提供したいと思います。
私はすぐにこう言います。
- この記事は MySQL について書かれていますが、一般的なことはどの DBMS にも当てはまります。
- 記事に書かれている内容はすべて私の個人的な見解であり、究極の真実ではありません。
- このアドバイスは新しいことを装うものではなく、読んだ文献と個人的な経験を一般化した結果です。
- この記事の枠組み内では、MySQL の構成の問題については触れません。
MySQL 使用時の問題は、次の 3 つのグループ (重要度の順) に分類できます。
- インデックスの不使用または誤用。
- データベース構造が間違っています。
- 間違った \ 次善の SQL クエリ。
これらの各グループを詳しく見てみましょう。
インデックスの使用
インデックスを使用しなかったり誤用したりすると、クエリが遅くなることがよくあります。インデックスの仕組みをよく知らない方、またはまだマニュアルを読んでいない方には、ぜひ読んでいただくことをお勧めします。
インデックスの使用に関するヒント:
- すべてにインデックスを付ける必要はありません。多くの場合、意味を理解せずに、テーブルのすべてのフィールドにインデックスを付けるだけです。インデックスを使用するとフェッチは高速化されますが、行の挿入と更新は低速になるため、各インデックスの選択には意味がある必要があります。
- インデックスを特徴づける主なパラメータの 1 つは、インデックス内のさまざまな要素の数である選択性です。2 つまたは 3 つの値が考えられるフィールドにインデックスを付けるのは意味がありません。このようなインデックスからのメリットはほとんどありません。
- インデックスの選択は、特定のテーブルに対するすべてのクエリの分析から始める必要があります。多くの場合、このような分析の後、3 つまたは 4 つのインデックスの代わりに、1 つの複合インデックスを作成できます。
- 複合インデックスを使用する場合、インデックス内のフィールドの順序が重要です。
- インデックスをカバーすることを忘れないでください。クエリ内のすべてのデータをインデックスから取得できる場合、MySQL はテーブルに直接アクセスしません。このようなリクエストは非常に迅速に実行されます。たとえば、
SELECT name FROM user WHERE login='test'インデックス (ログイン名、名前) を含むクエリの場合、テーブルへのアクセスは必要ありません。場合によっては、複合インデックスにフィールドを追加することが合理的です。これにより、インデックスがカバーされ、クエリが高速化されます。 - 行インデックスの場合、多くの場合、行の一部のみにインデックスを付けるだけで十分です。これにより、インデックス サイズを大幅に削減できます。
%先頭にある場合、LIKE(SELECT * FROM table WHERE field LIKE '%test')インデックスは使用されません。- FULLTEXTインデックスは、 MATCH ... AGAINST構文でのみ使用されます。
6.3 データベース構造
適切に設計されたデータベースは、データベースを高速かつ効率的に操作するための鍵です。一方で、データベースの設計が不十分であると、開発者にとって常に頭の痛い問題となります。
データベース設計のヒント:
- 可能な限り小さいデータ型を使用してください。データ型が大きくなるほど、テーブルも大きくなり、データを取得するために必要なディスク アクセスも多くなります。非常に便利な手順を使用して、
SELECT * FROM table_name PROCEDURE ANALYSE();可能な最小限のデータ型を決定します。 - 設計段階で正規形状を観察します。多くの場合、プログラマはこの段階ですでに非正規化に頼っています。ただし、ほとんどの場合、プロジェクトの開始時点では、これがどのような結果をもたらすかは明らかではありません。テーブルの非正規化は、最適化されていない非正規化に悩まされるよりもはるかに簡単です。また、
JOIN場合によっては、誤って非正規化されたテーブルよりも高速に動作します。 NULL意識的に必要な場合を除き、列を使用しないでください。
6.4 SQL クエリ。
クエリをできるだけ高速にするために、すべてのクエリをネイティブ SQL で書き換えたいという要望もよくあります。これを行うことにした場合は、次のヒントを参考にしてください。
- ループ内のリクエストを避けてください。SQL はセットの言語であり、クエリの作成は関数の言語ではなくセットの言語で行う必要があります。
*クエリでは (アスタリスク)を避けてください。選択したフィールドを正確にリストしてください。これにより、フェッチおよび送信されるデータの量が削減されます。また、インデックスのカバーも忘れないでください。テーブル内のすべてのフィールドを選択する場合でも、それらをリストすることをお勧めします。まず、コードの可読性が向上します。アスタリスクを使用すると、テーブルを調べずにテーブル内にどのフィールドがあるかを見つけることはできません。次に、現在、テーブルには 5 つのINT列があり、1 か月後にさらに 1 つのTEXT 列とBLOBが追加されましたが、アスタリスクはそのまま残されました。- ページ分割されている場合、レコードの合計数を取得するには、 を使用し、 を使用
SQL_CALC_FOUND_ROWSすると、選択した行数 (LIMIT が適用される前) がキャッシュされ、使用される場合は、クエリを再実行することなく、このキャッシュされた値のみが返されます。SELECT FOUND_ROWS();SQL_CALC_FOUND_ROWS MySQLSELECT FOUND_ROWS() INSERT複数の挿入のための構文があることを忘れないでください。1 つのクエリは、ループ内の複数のクエリよりも桁違いに高速に実行されます。LIMITすべてのデータが必要ない場合に使用します。- およびまたは の選択の後
INSERT… ON DUPLICATE KEY UPDATE…に使用し、多くの場合 の代わりに使用します。INSERTUPDATEREPLACE - この素晴らしい機能を忘れないでください
GROUP_CONCAT。複雑なクエリに役立ちます。