効率の良いSQLを生成したい【基本】

こんにちは、ishiyamaです。
規模の大きいシステムの開発において、データベースのパフォーマンスは重要ですよね。

今回は、Laravel の クエリビルダー、MySQLを利用し、SQL生成時に基本的に意識すべきことについて解説します。
※考慮すべき点はSQLと相違ないので、書き方の例として利用します。

インデックスの活用

結合操作を行う際、インデックスを適切に設定しておくことでクエリのパフォーマンスが向上します。
具体的には、JOINの条件となるフィールドにはインデックスを設定しておくことを推奨します。

設定方法としては、マイグレーションファイルに書き込むだけです。

<?php

...

return new class  extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->index('customer_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropIndex(['customer_id']);
        });
    }
}

up()メソッド内で、ordersテーブルのcustomer_id列にインデックスを追加しています。
（インデックスを削除するときはdown()メソッド内のように記述します）。

必要なデータのみを取得する

これは基本ですね。
SELECT *を用いると、全ての列を取得しますが、実際は必要ない場合が多いです。必要なデータだけを指定することで、ネットワークの負荷やデータベースの負荷を軽減し、パフォーマンスを向上に繋がります。

customersテーブルの name、ordersテーブルの created_at を取得する場合を考えて、例を示します。

• 悪い例

$customers = DB::table('customers')
             ->leftJoin('orders', 'customers.id', '=', 'orders.customer_id')
             ->get();

この例では、customersテーブルとordersテーブルを左結合していますが、get()メソッドに何も指定せずに全てのフィールドを取得しています。必要ないデータまで取得してしまい、非効率的です。

• 良い例

$customers = DB::table('customers')
             ->join('orders', 'customers.id', '=', 'orders.customer_id')
             ->select('customers.name', 'orders.created_at as order_date')
             ->where('orders.created_at', '>', '2023-01-01')
             ->get();

この例では、customersテーブルとordersテーブルを内部結合し、必要なフィールド（nameとcreated_at）のみを選択しています。また、whereメソッドを使ってcreated_atが2023年1月1日より後のデータだけを取得するようにフィルタリングしています。

まずは要件を満たしてからですが、なるべくヒット数を少なくする意識は持っていると良さそうです、

where句の順序

MySQLの場合、WHERE句の条件の評価順序は通常、左→右です。しかし、MySQLのクエリオプティマイザーは、インデックスの存在や条件の選択性などを考慮して最も効率的な実行計画を生成してくれます。そのため、通常はWHERE句の条件の順序がパフォーマンスに影響を及ぼすことは少ないようです。

しかし、確実に特定の条件が最初に評価されるようにしたい場合や、特定の条件の評価によって他の条件の評価が不要になるような場合には、WHERE句の条件の順序を調整することが有効な場合があります。やっておいて損はないので、意識しておきたいですね。

• パフォーマンスが悪い（かもしれない）例

$orders = DB::table('orders')
             ->where('created_at', '>', '2023-01-01')
             ->where('customer_id', 1)
             ->get();

この例では、created_atでのフィルタリングが先に行われ、次にcustomer_idでのフィルタリングが行われます。created_atフィールドにインデックスがない場合、全てのレコードがスキャンされてからcustomer_idでフィルタリングが行われます。

• パフォーマンスが改善される（かもしれない）例

$orders = DB::table('orders')
             ->where('customer_id', 1)
             ->where('created_at', '>', '2023-01-01')
             ->get();

この例では、customer_idでのフィルタリングが先に行われ、次にcreated_atでのフィルタリングが行われます。customer_idフィールドにインデックスがある場合、まず少ない数のレコードに対してフィルタリングが行われ、その結果に対してcreated_atでのフィルタリングが行われます。

ただし、これらは例であり、実際のパフォーマンスはテーブルの規模や設計などに準じるため、状況にあった対策をしましょう。

まとめ

今回は、効率の良いSQLを生成する上での基本的な内容をまとめてきました。
ふわっと知っているつもりではいましたが、執筆して改めて理解が深まったように思います。
ここまで読んでいただき、ありがとうございました。