インデックスでデータベースパフォーマンスを上げる方法

背景
この記事では、MySQLデータベースを使用したラーラーブまたはPHPアプリケーションのデータベースクエリーを最適化する方法を見ていきます.アットExpenseNG , 我々はナイジェリアのオープン財務省のウェブサイトからより多くの政府の費用を記録として我々のデータベースは急速に成長し続けます.ブートキャンプで始まったアプリのために、我々は成長しているデータベースがウェブサイトに持つ影響に備えていませんでした.
これらの量の記録で、我々は最も重要な影響を持って、正しい情報を通過する方法で我々のウェブサイトでこれらのデータを視覚化する責任を持ちました.
あなたが訪問するならばcontractors Expensengのページでは、政府は、最後に完成した月の中で最もお金を得た契約者によって分類された政府からお金を受け取っている人のリストが表示されますし、また、その月のために得た総額を見ることができます.データは次のようになります.

Contractor name: "",
Total Amount Received: "",
Month Ended: ""

挑戦
我々が必要とするデータがどのようなものであるかを知っているので、データベースからこのデータを取得するのにかかるものを見てみましょう.
つのテーブルがこの操作に関係していますcontractors and payments 表.請負業者のテーブルは、政府からお金を受け取っているすべての人のレコードを保持し、支払い表は、これまでに行われたすべての支払いの記録を保持し、その受取人への参照.
テーブルスキーマは次のようになります.

payments:
    - amount (double)
    - date (DATE)
    - recipient (string)

contractors:
    - name (string)
    ...

過去の月から請負業者によって受け取られた総額によって請負業者表からのすべての記録を得ることから結果を分類するために、それはすでに2つのテーブルを含んでいる質問のように聞こえます.

SELECT contractors.*, (SELECT SUM(amount) from payments WHERE 
recipient = contractors.name) as total from contractors 
order by total desc

上のクエリから、我々はすべての受信者によって受信された総お金を計算するのに役立つサブSELECT文を持っています、そして、我々は我々が我々の最終的な結果をソートするために使うことができる我々の質問で新しいコラムをつくるためにそのサブ質問から結果を使うことができます.このサブクエリによって作成された新しい列にはtotal .
上記のクエリには、月から終了した結果のみを取得するロジックが含まれていないので、以下のクエリを変更します.

SELECT contractors.*, (SELECT SUM(amount) from payments 
WHERE recipient = contractors.name 
AND date BETWEEN(2020-09-01, 2020-09-31) ) as total 
from contractors 
order by total desc

上記のクエリでは、サブクエリに新しい制約を追加しましたBETWEEN . ご存知のように、最初の値と2番目の値の末尾から始まる結果のみを選択できます.

新しい問題
あなたのデータベースにいくつかのレコードを持っているとき、上記の質問はうまくいきます、しかし、何千もの記録によるデータベースで働くとき、これはサーバーがあまりにも長い間レスポンスを受けることができないので、数分を実行するか、サーバーのリクエストタイムアウトエラーで結果として起こることに簡単に結果をもたらすことができます.
この問題を解決するには、上記の問い合わせからインデックスを追加する列を指定します.

contractors.name

payments.recipient

payments.date

payments.amout

インデックスを列に追加するクエリは次のようになります.

ALTER TABLE contractors ADD INDEX name_index (name)

またはララーブの移行を使用している場合

//database/migrations/2020_09_20_102101_add_index_to_payments_table.php
Schema::table('payments', function (Blueprint $table) {
    $table->index('recipient');
    $table->index('amount');
    $table->index('date');
});

インデックスを追加することで、クエリ実行時間を大幅に減らすことができます.Expensengでは、私たちのクエリは、いくつかの分後に約1ページあたり20の結果のページ化と約0.02秒にタイミングから行った.
必要な列を選択するだけで、クエリ実行時間をさらに向上させることができます.

SELECT contractors.name, contractors.date, (SELECT SUM(amount) from payments 
....

Laravelの雄弁を使う
我々は、我々が上記のように、同じ質問を達成することができますaddSelect オリジナルクエリにサブクエリを作成する方法

Contractor::select(['name', 'shortname', 'id'])
        ->addSelect(['total' => Payment::selectRaw('SUM(amount)')
            ->whereColumn('recipient', 'contractors.name')
            ->whereBetween('date', [$monthStart, $monthEnd])
    ])->orderBy('total', 'desc')->paginate(20);

ここで私たちは私たちのモデルから結果を取得するための素晴らしいと包括的なAPIを与えるElququentの高度なサブクエリを使用しています.我々が欲しかったならば、我々は怠惰な負荷を我々のContractor SELECT法によるモデル関係with('payments')->paginate(20) .
そして、それはあなたが大規模なMySQLデータベースのデータベース質問を最適化する方法です.

Adding an index to a table makes UPDATE queries run for a longer time, so you should not use them on tables that will get update frequently.

資源

https://dzone.com/articles/how-to-optimize-mysql-queries-for-speed-and-perfor

https://laravel.com/docs/8.x/migrations#indexes

https://laravel.com/docs/8.x/eloquent#advanced-subqueries