در قسمت قبلی، scope ای نوشتیم که کاربر ها را بر اساس نام، نام خانوادگی و نام شرکت فیلتر می کرد. سپس عبارت bill gates Microsoft را جست و جو کردیم و دیدیم که نتیجه مورد نظر نشان داده شد. همچنین دیدیم که مجموع زمان اجرای کوئری ها حدود 400 میلی ثاینه بود.
400 میلی ثاینه کمی کند به نظر می رسد. در این مطلب index هایی را به دیتابیس اضافه می کنیم تا بهره وری این کوئری را بهبود دهیم.
برای شروع، به طور معمول Index را به ستون هایی که روی آن ها عملیاتی انجام می شود، اضافه می کنیم. در پروژه ما ستون های نام و نام خانوادگی جدول users و ستون نام در جدول company این ویژگی را دارند.
به migration ها می رویم و متد index را روی ستون های مورد نظر صدا می زنیم.
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->foreignId('company_id')->constrained('companies');
$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();
});
}
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('companies', function (Blueprint $table) {
$table->id();
$table->string('name')->index();
$table->timestamps();
});
}اکنون Migration ها و seeder پروژه را دوباره اجرا می کنیم.
php artisan migrate:fresh
php artisan db:seedحال به مروگر می رویم تا تاثیر این کار روی بهره وری کوئری ها را ببینیم.
همانطور که مشاهده می کنید تغییری در زمان کوئری ها ایجاد نشده. بهتر است اطمینان پیدا کنیم که index ها اضافه شده باشند.
همانطور که مشاهده می کنید index مورد نظر با نام companies_name_index اضافه شده است. برای جدول کاربر ها هم به همین شکل index ها اضافه شده اند. پس به چه دلیل تاثیری در زمان کوئری ها ایجاد نشد؟ در چنین شرایطی من کوئری ایجاد شده توسط لاراول را در phpMyAdmin یا برنامه های مشابه آن، کمی بررسی می کنم.
EXPLAIN SELECT
*
FROM
`users`
WHERE
(
`first_name` LIKE '%bill%' OR `last_name` LIKE '%bill%' OR EXISTS(
SELECT
*
FROM
`companies`
WHERE
`users`.`company_id` = `companies`.`id` AND `name` LIKE '%bill%'
)
) AND(
`first_name` LIKE '%gates%' OR `last_name` LIKE '%gates%' OR EXISTS(
SELECT
*
FROM
`companies`
WHERE
`users`.`company_id` = `companies`.`id` AND `name` LIKE '%gates%'
)
) AND(
`first_name` LIKE '%microsoft%' OR `last_name` LIKE '%microsoft%' OR EXISTS(
SELECT
*
FROM
`companies`
WHERE
`users`.`company_id` = `companies`.`id` AND `name` LIKE '%microsoft%'
)
)
LIMIT 15 OFFSET 0اولین کاری که انجام می دهیم، اضافه کردن عبارت EXPLAIN به ابتدای کوئری است. پس از اجرای کوئری، نتیجه ای مانند تصویر زیر را مشاهده می کنیم.
به ستون های key و possible_keys دقت کنید. مقدار ستون possible_keys شامل index هایی است که احتمال دارد در این کوئری استفاده شوند. مقدار ستون key شامل index هایی است در حقیقت استفاده شدند.
همانطور که می بینید index های ما برای نام، نام خانوادگی و نام شرکت به هیچ وجه استفاده نشده اند. تنها index استفاده شده مربوط به کلید اصلی جدول companies است.
یکی از دلایل این موضوع، وجود عملگر % قبل از عبارت جست و جو شده در کوئری است. اگر قبل از عبارت جست و جو % یا هر عملگر wildcard ای وجود داشته باشد، MySQL نمیتواند از index استفاده کند. به همین دلیل % هایی که قبل از عبارت جست و جو هستند را پاک می کنیم و دوباره کوئری را اجرا می کنیم.
از آن جایی که نام index ها در possible_keys آمده اند متوجه می شویم که تغییر ایجاد شده کمک کننده بوده اما هنوز از index ها استفاده نشده است. پس چه چیزی مانع استفاده کوئری ما از index ها می شود؟ هنگام بهینه سازی کوئری ها، چنین سوالی می تواند چالش بر انگیز و خسته کننده باشد. به نظر من بهترین کار در چنین شرایطی، آزمون و خطا است.
به عنوان گام بعدی، sub query مربوط به شرکت را حذف می کنیم تا ببینیم کمکی می کند یا خیر.
EXPLAIN
SELECT
*
FROM
`users`
WHERE
(
`first_name` LIKE '%bill%' OR `last_name` LIKE 'bill%'
) AND(
`first_name` LIKE 'gates%' OR `last_name` LIKE 'gates%'
) AND(
`first_name` LIKE 'microsoft%' OR `last_name` LIKE 'microsoft%'
)
LIMIT 15 OFFSET 0بعد از اجرای کوئری نتیجه زیر دریافت می شود.
می توان دید که index های نام و نام خانوادگی استفاده شده اند. برای این که مطمئن شویم تمام مشکل از sub query مربوط به جدول companies نبوده، پیشوند % را به عبارت های جست و جو اضافه می کنیم و کوئری را دوباره اجرا می کنیم.
می بینیم که از هیچ index ای استفاده نمی شود. بنابراین در حال حاضر دو مشکل وجود دارد که باید برطرف کنیم تا کوئری ما بتواند از index ها استفاده کند.
باید پیشوند % را حذف کنیم و راهی برای بهبود sub query جدول companies پیدا کنیم. چرا که نمی خواهیم آن بخش از کوئری را به طور کامل حذف کنیم.
ابتدا به سراغ حل مشکل % می رویم. به مدل User رفته و % قبل از عبارت جست و جو را حذف می کنیم. باید دقت کنیم که انجام این کار، جست و جوی ما را به نتیجه هایی که با عبارت جست و جو شده ما شروع می شوند محدود می کند. این اتفاق مشکلی ندارد. چرا که جست و جو ها به طور معمول به همین شکل اند. البته یک سناریو عجیب ایجاد می شود. دیگر نمی توانیم برای نام های چند کلمه ای مانند Microsoft corp جست و جو کنیم. زیرا کوئری ما سعی می کند شرکتی که نام آن با کلمه Microsoft یا Corp شروع می شود را پیدا کند در حالی که باید شرکتی که نام آن با Microsoft corp شروع می شود را جست و جو کند.
به نظر من، برای حل این مشکل می توان عبارت مورد نظر را بین double quotation قرار داد. برای نمونه، اگر بخواهیم Microsoft corp را به عنوان یک عبارت جست و جو کنیم به این صورت می نویسیم : bill gates “Microsoft corp”
پیاده سازی این قابلیت در PHP با کمک تابع str_getcsv بسیار راحت است. به scope ای که داخل مدل User نوشتیم بر میگردیم و از این تابع استفاده می کنیم.
public function scopeSearch($query, string $terms = null)
{
collect(str_getcsv($terms,' ','"'))->filter()->each(function ($term) use ($query) {
$term = '%' . $term . '%';
$query->where(function ($query) use ($term) {
$query->where('first_name', 'like', $term)
->orWhere('last_name', 'like', $term)
->orWhereHas('company', function ($query) use ($term) {
$query->where('name', 'like', $term);
});
});
});
}همانطور که می بینید تابع str_getcsv را صدا زدیم و عبارت جست و جو شده را در ورودی اول، فاصله به عنوان جدا کننده در ورودی دوم و کاراکتر " را به عنوان محصور کننده در ورودی سوم به آن پاس دادیم.
دوباره درخواست جست و جو می دهیم تا این تغییر را آزمایش کنیم.
همانطور که مشاهده می کنید عبارت Microsoft corp در کوئری به صورت یکجا قرار داده شده است.
البته زمان اجرای کوئری ما به این خاطر که sub query جدول companies اجازه نمی دهد تا کوئری از Index ها استفاده کند، هنوز در حدود 400 میلی ثانیه است. در قسمت بعدی تلاش می کنیم تا این مشکل را برطرف کنیم.
