بهره وری دیتابیس پروژه لاراولی - اجرای مستقل کوئری ها با UNION

در قسمت قبل دیدیم که چگونه گاهی اوقات اجرای بیشتر کوئری می تواند به بهبود بهره وری کمک کند. با تغییر قسمت قبلی، زمان اجرای کوئری از 150 میلی ثانیه به حدود 15 میلی ثانیه رسید. در این قسمت بررسی می کنیم که چگونه می شود با اجرای تنها یک کوئری همچنان خوبی اجرای کوئری های جداگانه را داشته باشیم. دیتابیس ها در حال حاضر راه مناسبی برای اجرای چند کوئری به صورت یک جا و ایزوله دارند. این کار با UNION انجام می شود. به PhpMyAdmin می رویم و با کوئری ای که بین نام و نام خانوادگی جست و جو انجام می دهد آزمایش انجام می دهیم.

همانطور که می بینید این کوئری بسیار سریع و در حدود یک میلی ثانیه اجرا می شود. EXPLAIN این کوئری را در تصویر زیر مشاهده می کنید.

می توان دید که این کوئری از ایندکس ستون های first_name و last_name استفاده می کند. اکنون کوئری دیگری می نویسیم که کاربر ها را بر اساس نام شرکتشان پیدا می کند.

EXPLAIN آن را اجرا می کنیم.

می بینیم که از ایندکس ستون name جدول شرکت ها استفاده می شود. همانطور که در قسمت قبل آموختیم اجرای جداگانه این کوئری ها باعث می شود بتوانیم از ایندکس های موجود استفاده کنیم و سرعت کوئری را بیشتر کنیم.

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

SELECT
    *
FROM
    users
WHERE
    first_name LIKE 'bill%' OR last_name LIKE 'bill%'
UNION
SELECT
    users.*
FROM
    users
INNER JOIN companies ON companies.id = users.company_id
WHERE
    companies.name LIKE 'microsoft%'

این کوئری همچنان با سرعت بالایی اجرا می شود. EXPLAIN آن به صورت زیراست.

از تمام ایندکس هایی که میخواستیم، استفاده می شود. اما چگونه این کوئری را در پروژه خود استفاده کنیم وبه کوئری ای که در برنامه داریم آن را اضافه کنیم؟ می توانیم با نوشتن WhereIn این کار را انجام دهیم. ابتدا در PhpMyAdmin آزمایش می کنیم.

SELECT
    *
FROM
    users
WHERE
    id IN(
    SELECT
        id
    FROM
        users
    WHERE
        first_name LIKE 'bill%' OR last_name LIKE 'bill%'
    UNION
SELECT
    users.id
FROM
    users
INNER JOIN companies ON companies.id = users.company_id
WHERE
    companies.name LIKE 'microsoft%'
)

تمام کوئری ای که با عبارت UNION نوشته بودیم را به عنوان sub query در whereIn نوشتیم و select های آن را به ستون Id محدود کردیم تا تنها مقدار مورد نیاز whereIn برگردانده شود. با اجرای این کوئری متوجه می شویم که سرعت آن کم تر شده است. اگر EXPLAIN این کوئری را ببینیم دلیل آن مشخص است.

کوئری جدید ما از هیچ ایندکسی استفاده نمی کند! اگر به ستون select_type نگاه کنید می بینید که یک subquery و دو union وابسته داریم. (DEPENDENT UNION و DEPENDENT SUBQUERY)  این اتفاق خوبی نیست زیرا به این معنی است که کوئری های ما به صورت ایزوله اجرا نمی شوند. بلکه به کوئری والدشان وابسته شده اند. در چنین شرایطی من ترجیح می دهم از derived table ها استفاده کنم. Derived table به ما کمک می کند تا وابستگی بین کوئری داخلی و بیرونی را از بین ببریم و استفاده از آن بسیار ساده است.  برای استفاده از derived table کافی است sub query ای که در whereIn داریم را در یک کوئری دیگر بنویسیم.

SELECT
    *
FROM
    users
WHERE
    id IN(
    SELECT
        *
    FROM
        (
        SELECT
            id
        FROM
            users
        WHERE
            first_name LIKE 'bill%' OR last_name LIKE 'bill%'
        UNION
    SELECT
        users.id
    FROM
        users
    INNER JOIN companies ON companies.id = users.company_id
    WHERE
        companies.name LIKE 'microsoft%'
    ) AS matches
)

توجه کنید که انتهای کوئری جدید عبارت as matches اضافه شده است تا MySQL بتواند کوئری را به درستی اجرا کند. یک derived table به زبان ساده کوئری ای است که در بخش from آن به جای نام جدول یک sub query نوشته می شود. اکنون EXPLAIN کوئری جدید را اجرا می کنیم.

همانطور که می بینید مشکل وابسنگی حل شد و اکنون کوئری ما از ایندکس ها استفاده می کند. یکی از خوبی های این روش این است که می توان چندین عبارت را جست و جو کرد. برای این کار کافی است یک whereIn دیگر به کوئری اضافه کنیم و مقدار جست و جو شده را تغییر دهیم. حال این کوئری را در کد های پروژه می نویسیم.

ابتدا باید یک whereIn بنویسیم که تمام کاربر های مرتبط با جست و جو را پیدا می کند. سپس داخل آن به derived table نیاز داریم تا وابستگی کوئری داخلی و بیرونی را از بین ببرد. سپس باید کاربر ها را بر اساس نام، نام خانوادگی و نام شرکت پیدا کنیم.

اسکوپ جست و جوی ما به شکل زیر می شود.

public function scopeSearch($query, string $terms = null)
{
    collect(str_getcsv($terms, ' ', '"'))->filter()->each(function ($term) use ($query) {
        $term = $term . '%';
        $query->whereIn('id', function ($query) use ($term) {
            $query->select('id')
                ->from(function ($query) use ($term) {
                    $query->select('id')
                        ->from('users')
                        ->where('first_name', 'like', $term)
                        ->orWhere('last_name', 'like', $term)
                        ->union(
                            $query->newQuery()
                            ->select('users.id')
                            ->from('users')
                            ->join('companies', 'companies.id', '=', 'users.company_id')
                            ->where('companies.name', 'like', $term)
                        );
                }, 'matches');
        });
    });
}

در قسمت بعدی می بینیم که چگونه می شود نتایج بدست آمده را در بهترین حالت ممکن مرتب سازی کرد.