رفتن به نوشته‌ها

Explain در MySQL و بهبود کوئری- قسمت اول

بهبود کارایی یک App بسته به شرایط می‌تواند راهکار‌های متفاوتی داشته باشد و در شرایط مختلف نیازمند روش‌های تحلیل مختلف است اما یکی از رایج‌ترین سناریوها برای بهبود سرعت پاسخگویی app ها بهینه‌سازی کوئری‌هایی است که از دیتابیس می‌پرسند، روشی که من معمولا برای این گونه بهینه‌سازی استفاده می‌کنم معمولا چنین گام‌هایی رو شامل میشه:

  1. مشاهده نیاز به بهبود کارایی، (سری که درد نمی‌کنه رو دستمال نمیبندند…نه؟)
  2. پیدا کردن کوئریی که زمانش قابل کاهشه
  3. تحلیل کوئری و لیست کردن راهکار‌های بهبود کوئری
  4. تست کردن راهکارها به ترتیب با کمترین هزینه اجرا و بیشترین تاثیر

در این مجموعه از پست‌ها میخوایم راجع به مرحله سوم صحبت کنیم، یعنی یکی از روش‌های تحلیل کوئری که استفاده از EXPLAIN در MYSQL است.(البته تمام RDBMS‌ها تا جایی که اطلاع دارم دستور مشابه‌ای دارند)

در این پست قصد داریم روی چند تا کوئری مختلف Explain رو اجرا کنیم و خروجی رو بررسی کنیم. منبع مطالبی که عنوان می‌کنم در داک رسمی MySQL در آدرس اینجا و اینجا است، در صورت نیاز به توضیحات دقیق‌تر و بیشتر توصیه می‌کنم حتما داک رو با دقت مطالعه کنید.

دستور EXPLAIN در MySQL وجود داره تا شما بتونید از MySQL بپرسید که کوئری شما رو چطور اجرا می‌کنه، در واقع برای تحلیل یک کوئری باید بدونیم اون RDBMS خاص چه Execution planای رو استفاده می‌کنه تا کوئری رو اجرا کنه.این قابلیت خیلی مهمیه که RDBMS در اختیار شما می‌گذاره و اجازه میده دید بهتری به کوئری‌هاتون داشته باشید.خیلی وقت‌ها شما جدول و کوئری خودتون رو خیلی با دقت طراحی کردید اما باز هم کوئری در زمانی که توقع دارید اجرا نمیشه، در این جور موارد با استفاده از این دستور می‌تونید از خود MySQL بپرسید که چطوری داره کوئری شما رو تحلیل و اجرا می‌کنه، از چه اندیس‌هایی توی هر کدوم از کوئری‌ها استفاده کرده و به قول خارجی‌ها با RDBMSاتون on the same page هستید یا دچار سوتفاهم شده(شدید)؟

فرض کنید برای شروع این جداول رو با این ساختار و ایندکس‌ها داریم:

CREATE TABLE tbl_job (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  job_name NVARCHAR(32) NOT NULL
);

CREATE TABLE tbl_user (
  id         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  first_name NVARCHAR(32),
  last_name  NVARCHAR(32),
  age,
  job_id     INT,
  job_id     INT NOT NULL,
  FOREIGN KEY (job_id) REFERENCES tbl_job (id)

);

CREATE INDEX tbl_user_last_name
  ON tbl_user (last_name);

CREATE INDEX tbl_user_full_name
  ON tbl_user (first_name, last_name);

دو تا جدول داریم به برای کاربر و شغل که جدول افراد یک FK داره به جدول شغل به علاوه جدول کاربر هم دو تا index داره یکی فقط روی last_name و دیگری روی first_name,last_name و می‌خواهیم خروجی Explain روی کوئری‌های مختلف رو بررسی کنیم.
ساختار جداول

اولین کوئری یک کوئری ساده است روی جدول کاربر و می‌خواهیم بپرسم کاربران بالای ۲۰ سال رو برامون لیست کنه:

SELECT * 
FROM tbl_user 
where age > 20;

اول از mysql در مورد این کوئری توضیح بخوایم:

EXPLAIN SELECT * FROM tbl_user where age > 20;

و mysql پاسخ میده:

اسکن جدول

این یک جدول هست با یک ردیف و ۱۲ تا ستون اما معنی هر کدام از این ستون‌ها چیه؟ستون‌هایی که معنیش رو می‌دونم رو تک‌تک توضیح میدم:

  • id
    به ازای هر Selectای که داخل کوئری مورد نظر باشه یک ردیف وجود داره و هر ردیف به طور خودکار یک عدد میگیره.
  • select_type
    ما چندین مدل SELECT داریم که از روی کوئری به راحتی قابل تشخیص هستند، مثلا توی این مورد بخصوص چون SELECT ای که زدیم داخل بدنه‌اش فاقد Sub-Query یا UNION هست بهش میگیم SIMPLE SELECT، توی مثال‌های بعدی انواع دیگه‌اش رو هم میبینیم.
  • table
    اسم جدولی که ردیف‌های خروجی این کوئری از اونجا میان، مثلا چون الان داریم روی جدول tbl_user کوئری میزنیم، اسم اون جدول اومده توی explain، شاید براتون سوال پیش بیاد اگر join زده بودیم چی؟جلو‌تر این مثال هم هست بهش میرسیم.join درواقع دو تا SELECT هستش.
  • type
    این فیلد نشون دهنده نوع Join هستش، ولی ما که Join نزده بودیم و در ظاهر یک SELECT ساده است، اما از نظر MySQL این SELECT با صفر SELECT دیگه Join شده.
    حالا ALL یعنی چی؟
    بدترین استراتژی ممکن برای Join همین ایشون هستند که مشاهده می‌کنید: ALL ، توی این استراتژی تمام ترکیب‌های ممکن بین این SELECT و SELECT های قبلی داره چک میشه و هیچ ایندکسی وجود نداشته
  • possible_keys
    توی این جدول MySQL به key ها و Index ها میگه Key، این ستون لیستی از تمام indexهایی هستش که توی این کوئری قابل استفاده هستند.چون ما شرط WHERE رو روی age گذاشتیم و age هیچ ایندکس یا Keyای نداره پس این ستون خالیه.
  • rows
    این تعداد ردیف‌هایی هستش که MySQL باید بررسی کنه تا نتیجه کوئری رو بتونه به شما بده، طبیعتا هر چی تعداد کمتر باشه سریع‌تر جواب میده
  • filtered
    این فیلد میگه که چند درصد از سطر‌ها جستوجو نشده و فیلتر شده، که عموما هر چی بیشتر باشه نشون میده که استفاده از ایندکس‌ها موثر‌تر بوده، البته filtered بهترین معیار نیست وقتی میشه فیلد type رو مثلا برای مقایسه دو تا کوئری در نظر گرفت.
  • key
    این ستون نشون میده MySQL از بین ایندکس‌های ممکن کدوم یکی رو انتخاب کرده، اینجا چون هیچ ایندکسی نداشتیم خب طبیعتا خالیه

خب این کوئری خیلی ساده بود و خیلی از ستون‌های EXPLAIN خالی مونده بود بذارید یک کوئری دیگه رو امتحان کنیم:

EXPLAIN SELECT * FROM tbl_user where first_name="Mahdi" and last_name="Zareie";

 

و خروجی این هستش:

Two possible index

همون طور که مشاهده می‌کنید اینجا ما توی possible_keys دو تا ایندکس داریم که کاما جدا شدند، یکی اونی که فقط روی last_name بود و یکی اونی که روی first_name و last_name بود و توی فیلد key میبینیم که MySQL فکراش رو کرده و تصمیم گرفته از اونی که فقط روی last_name هستش استفاده کنه.
اما چرا؟بهتر نبود از هر دو(ایندکسی که روی first_name و last_name گذاشتیم) استفاده میکرد؟خب ما میتونیم MySQL رو رو از طریق FORCE INDEX مجبور کنیم حالت دیگه رو هم بررسی کنه:

 

EXPLAIN 
SELECT * 
FROM tbl_user 
FORCE INDEX (tbl_user_full_name) 
WHERE first_name="Mahdi" and last_name="Zareie";

که جواب میشنویم:

Force Indexاینجا چون مجبورش کردیم از tbl_user_full_name استفاده کنه فقط همون توی possible_keys اومده و به عنوان key هم انتخاب شده، دقت کنید که تعداد rowهایی که بررسی کرده بین این دو حالت یکی بوده اما تعداد rowهایی که filtered شدند متفاوته، در واقع توی کوئری دوم ردیف‌های بیشتری فیلتر شدند و باید بهتر باشه، نه؟
خب نکته توی key_len هستش، key_len طول index به بایت هستش، وقتی از ایندکس last_name استفاده کرد طول ایندکس ۹۹ بود و توی ایندکسی که طول کمتری داشت نسبت به ۱۹۸ الان و پیدا کردن مقادیر سریع‌تر اتفاق می‌افتاد پس میبینید که MySQL ترجیح داده از ایندکسی استفاده کنه که طول کمتری داره، البته شاید به موارد دیگه‌ای هم بستگی داشته مثلا تعداد رکوردی که تخمین میزده باید روی first_name یک به یک مقایسه بشه.

ما اینجا FORCE INDEX رو روی کوئریی که به EXPLAIN دادیم استفاده کردیم، در واقع FORCE INDEX محدودیتی نداره و می‌تونید توی کوئری‌های SELECT استفاده کنید اما خب معمولا MySQL خودش بهتر میفهمه کدوم ایندکس رو استفاده کنه.

توی پست بعدی در مورد انواع join (یعنی فیلد type صحبت می‌کنیم) که یکی از مهمترین پارامتر‌های مقایسه نحوه اجرای کوئری هاست.

منتشر شده در OptimizationSoftware