بهره وری دیتابیس پروژه لاراولی - مرتب سازی سریع تر با ایندکس های ترکیبی

در این بخش خواهیم دید که چگونه ایندکس های ترکیبی می توانند بهره وری کوئری های مرتب سازی را بهبود دهند. برای نشان دادن این موضوع یک صفحه که لیست کاربر ها را نشان می دهد به همراه صد هزار کاربر از قبل آماده کردیم.

در حال حاضر نتیجه ها را به صورت صفحه بندی شده نشان می دهیم اما هنوز مرتب سازی انجام نشده است. تغییری در این صفحه ایجاد می کنیم که کاربر ها بر اساس نام خانوادگی و نامشان مرتب شوند.

قبل از انجام این کار بهتر است اشاره کنم که صفحه بندی کردن نتیجه ها بدون مرتب سازی، کار خوبی نیست. دیتابیس ها همیشه نتایج را با یک ترتیب خاص بر نمی گردانند. در یکی از پروژه های من، مشتری نمی توانست همه رکورد های داخل دیتابیس را ببیند. چرا که ترتیب نتیجه ها از صفحه ای به صفحه دیگر فرق می کرد. دیباگ بار هم در این مورد به ما اخطار می دهد.

پس هر زمان که صفحه بندی انجام می شود حتما از order by استفاده کنید تا نتیجه ها مرتب سازی شوند.

اکنون به سراغ ویرایش برنامه می رویم. به کنترلر user رفته و به کوئری موجود order by اضافه می کنیم.

<?php

namespace App\Http\Controllers;

use App\User;

class UsersController extends Controller
{
    public function index()
    {
        $users = User::query()
            ->orderBy('last_name')
            ->orderBy('first_name')
            ->paginate();

        return view('users', ['users' => $users]);
    }
}

در حال حاضر مجموع زمان کوئری ما 12 میلی ثانیه است. اگر صفحه را دوباره بارگذاری کنیم این زمان به 110 میلی ثانیه تبدیل می شود. این افزایش بیش از حد زیاد است. باید به ستون های جدولمان ایندکس اضافه کنیم.

به مایگریشن جدول کاربر ها می رویم و ستون های نام و نام خانوادگی را ایندکس می کنیم.

<?php


use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('first_name')->index();
            $table->string('last_name')->index();
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }

}

با دستور زیر مایگریشن ها و سیدر ها را دوباره اجرا می کنیم.

php artisan migrate:fresh --seed

حال می توانیم به صفحه کاربر ها بر گردیم تا تاثیر این تغییر را در بهره وری کوئری ببینیم. در حال حاضر مجموع زمان اجرا 100 میلی ثانیه است است.

همانطور که مشاهده می کنید بهبود چندانی صورت نگرفت! چرا این تغییر کمکی نکرد؟

کوئری را کپی می کنیم و به phpMyAdmin می رویم تا دلیل این اتفاق را پیدا کنیم.

Explain کوئری را اجرا می کنیم.

اگر نگاهی به ستون های possible keys و keys بیاندازیم می بینیم که این کوئری از ایندکس های جدیدمان استفاده ای نمی کند. همچنین با نگاه به ستون extra و توجه به عبارت using filesort متوجه می شویم که MySQL قادر نیست از ایندکس برای مرتب سازی استفاده کند. به جای استفاده از ایندکس، MySQL همه ردیف ها را خوانده است تا بتواند مرتب سازی را انجام دهد. این عملیات بسیار کند است. اما چرا کوئری ما از ایندکس های نام و نام خانوادگی استفاده نکرده است؟

برای بررسی این موضوع یکی از ستون هایی که بر اساس آن مرتب سازی انجام می شود را از کوئری حذف می کنیم.

SELECT
    *
FROM
    `users`
ORDER BY
    `last_name` ASC
LIMIT 15 OFFSET 0

با اجرای دوباره کوئری می بینیم که از ایندکس ستون نام خانودگی استفاده می شود.

برعکس این کار را هم امتحان می کنیم. این بار مرتب سازی بر اساس نام را نگه می داریم.

SELECT
    *
FROM
    `users`
ORDER BY
    `first_name` ASC
LIMIT 15 OFFSET 0

می بینیم که از ایندکس ستون نام هم استفاده می شود. بنابراین هنگامی که تنها بر اساس یکی از ستون ها مرتب سازی می کنیم مشکلی وجود ندارد. اما زمانی که ستون ها را باهم مرتب می کنیم MySQL نمی تواند از ایندکس ها بهره ببرد.

در این شرایط می توان از ایندکس ترکیبی استفاده کرد. با ایندکس های ترکیبی می توانیم چندین ستون را باهم ایندکس کنیم. 

به مایگریشن کاربر ها می رویم و ایندکس هایی که قبل تر اضافه کرده بودیم را حذف می کنیم تا یک ایندکس ترکیبی به جای آن قرار دهیم.

<?php


use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('first_name');
            $table->string('last_name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
            
            $table->index(['last_name', 'first_name']);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }

}

حال دوباره مایگریشن ها و سیدر ها را اجرا می کنیم.

php artisan migrate:fresh --seed

به صفحه کاربر ها می رویم تا تاثیر این تغییر را بررسی کنیم. در حال حاضر مجموع زمان کوئری ها 100 میلی ثانیه است. 

بعد از بارگذاری دوباره صفحه می بینیم که زمان اجرای کوئری ها به 7 میلی ثانیه رسیده است.

قبل از پایان این بخش می خواهم نکته مهمی را به شما یادآوری کنم.

به کنترلر user رفته و ترتیب order by هایی که نوشتیم را عوض می کنیم.

<?php


namespace App\Http\Controllers;

use App\User;

class UsersController extends Controller
{
    public function index()
    {
        $users = User::query()
            ->orderBy('first_name')
            ->orderBy('last_name')
            ->paginate();

        return view('users', ['users' => $users]);
    }

}

حال اگر به صفحه کاربر ها برگردیم و دوباره آن را بارگذاری کنیم، زمان اجرای کوئری ها به 90  میلی ثانیه بر می گردد. بنابراین در نظر داشته باشید که ترتیب ستون ها در ایندکس ترکیبی مهم است.

پروژه‌ی این بخش را می‌توانید از اینجا دانلود کنید.