بیایید دنیا را از چشمان sql ببینیم!

همیشه وقتی کوئری می نوشتم علامت سوال های زیادی دور سرم می چرخیدند. یکی از اون علامت سوال ها این بود :

رفتار پایگاه داده های sql در برابر کوئری که بهشون می دیم چگونه هست؟!

همیشه دنبال راهی بودم که کوئری ها رو به اصطلاح دیباگ کنم و اگه مشکلی در کوئری وجود داره پیدا و درنهایت رفعش کنم.

گاهی اوقات کوئری که می نویسیم کار می کنه اما همین که تنوع داده ها بالا میره ممکنه کوئری ما به کندی عمل کنه.
شاید شنیده باشید که می گن کوئری ای استاندارده که زیر ۳۰ ثانیه طول بکشه.

در نوشته امروز می خوام به معرفی روشی بپردازم که باهاش می تونیم دنیا رو از چشمان sql ببینیم!
این مبحث جزئی از مباحث high level در مفاهیم sql محسوب می شه.

من برای این نوشته از پایگاه داده sqlite استفاده کردم. چراکه نه تنها حجم پایینی داره بلکه شما حتی با تلفن همراهتون هم می تونید از این پایگاه داده استفاده کنید.

از همراهیتون صمیمانه سپاسگزارم...

معمولا آنالیز کوئری برای SELECT ها بیشتر به کار می ره.

به منظور آنالیز کوئری قبل از کوئری مورد نظرمون، دستور EXPLAIN QUERY PLAN رو درج می کنیم.

در حاشیه : مفهوم آنالیز کوئری در همه پایگاه داده های رابطه ای وجود داره و تنها تفاوتشون سر نحوه تعامل کاربر سیستمی با ابزار های مدیریت پایگاه داده ای هست. به عنوان مثال در دیتابیس postgresql از دستور explain analyze استفاده می شه یا در mssql همین آنالیز به صورت گرافیکی به نمایش درمیاد.

بیایید برای فهم بیشتر موضوع، چند مثال رو بررسی کنیم.

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SCAN TABLE t1

نتیجه QUERY PLAN در مثال بالا نشون می دهد به منظور fetch نمودن اطلاعات، sql جدول t1 رو به طور کامل اسکن می کنه تا به نتیجه مورد نظر شما یعنی همون شرط where برسه.

لازم می دونم اینو هم بگم که یکی از متداول ترین روش ها برای افزایش سرعت واکشی اطلاعات و رکورد ها، استفاده از تکنیک ایندکس گذاری هست.

حالا اگه توی مثال بالا من ستون a رو به عنوان ایندکس معرفی کنم، در نتیجه :

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH TABLE t1 USING INDEX i1 (a=?)

همونطور که در نتیجه QUERY PLAN مشاهده می کنید، sql از ایندکس برای عملیات جستجو استفاده می کنه و همین امر عامل بالا رفتن سرعت در SELECT هاست.

تا این جای کار ما از یک جدول برای مثال هامون استفاده کردیم اما اگه دو تا جدول بخوان با هم join بشن، sql چه رفتاری رو انجام می ده؟

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH TABLE t1 USING INDEX i2 (a=? AND b>?)
`--SCAN TABLE t2

نتیجه مثال بالا از دوحلقه for بیرونی و درونی تشکیل می شه. حلقه فور بیرونی شامل جستجو در تیبل t1 از طریق ایندکس هست و چنانچه رکورد موافق با شرط جلو where باشه، وارد حلقه for درونی شده و در نهایت روی جدول t2 ، به صورت کامل عملیات اسکن انجام می شه.

حال این سوال ممکنه پیش بیاد :

اگر جای جدول t1 با t2 تعویض بشه، sql همان رفتار گذشته رو انجام خواهد داد یا نه؟

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH TABLE t1 USING INDEX i2 (a=? AND b>?)
`--SCAN TABLE t2

همانطور که مشاهده می کنید، رفتار sql همان رفتار قبلی است.

یه سوال!

رفتار sql با ORDER BY ، GROUP BY یا DISTINCT به چه صورت است؟

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
QUERY PLAN
|--SCAN TABLE t2
`--USE TEMP B-TREE FOR ORDER BY

از نتیجه QUERY PLAN می فهمیم که برای اعمال سه دستور فوق از درخت b-tree استفاده می کند. اگر نمی دانید که درخت b-tree چیست به درس ساختمان داده مراجعه کنید.

در انتهای نوشته امروز توجه شما رو به مثالی اندک پیچیده تر از قبلی ها جلب می کنم.

در مثال زیر نتیجه هر subquery در یک temp table نگهداری شده و درنهایت دو جدول باهم join می شن.

sqlite> SELECT * FROM
      >   (SELECT * FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x,
      >   (SELECT * FROM t2 WHERE c=1 ORDER BY d LIMIT 2) AS y;
QUERY PLAN
|--MATERIALIZE 0x18F06F0
|  `--SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
|--MATERIALIZE 0x18F80D0
|  |--SEARCH TABLE t2 USING INDEX i4 (c=?)
|  `--USE TEMP B-TREE FOR ORDER BY
|--SCAN SUBQUERY 0x18F06F0 AS x
`--SCAN SUBQUERY 0x18F80D0 AS y

امیدوارم نوشته امروز براتون مفید بوده باشه.

نویسنده : ابوالفضل وکیلی