SQL چیست و چه کاری انجام می دهد؟ راهنمای زبان پرس و جو

پاسخ کوتاه: SQL چیست و چه کاری انجام می دهد؟ زبانی که با آن از پایگاه داده سوال می پرسیم و داده را می خوانیم، اضافه می کنیم، تغییر می دهیم یا پاک می کنیم. نامش کوتاه شده Structured Query Language است، یعنی زبان پرس و جوی ساختاریافته. خودش داده ای ذخیره نمی کند؛ دستور می دهد و نرم افزار دیگری مثل MySQL یا SQL Server آن را اجرا می کند. اگر سایت وردپرسی دارید، همین زبان پشت پنل مدیریت شما در حال خواندن و نوشتن داده است، حتی اگر تا امروز نامش را نشنیده باشید.

این راهنما آموزش خشک نیست. سه پرسشی که بیشتر از همه از ما پرسیده می شود اینهاست: SQL دقیقا چه کاری انجام می دهد، تفاوتش با دیتابیس و MySQL چیست، و چرا بعضی وقت ها سایت کند می شود در حالی که سرور و اینترنت همه چیز سالم است. هر سه را با مثال واقعی و با عددهایی که خودمان اندازه گرفتیم جواب می دهیم، و در پایان یک جدول تصمیم می گذاریم: چه کاری را خودتان انجام بدهید و چه کاری را سفارش بدهید.

SQL چیست؟ تعریف دقیق و سه چیزی که SQL نیست

SQL یک زبان استاندارد است، نه یک نرم افزار و نه یک فایل. در اوایل دهه ۱۹۷۰ در آزمایشگاه های آی بی ام ساخته شد و در سال ۱۹۸۶ به عنوان استاندارد بین المللی پذیرفته شد. ویژگی مهمش این است که «توصیفی» است: شما می گویید چه داده ای را می خواهید، و نمی گویید سرور چطور آن را پیدا کند. همین ویژگی باعث می شود یک دستور روی MySQL، روی PostgreSQL و روی SQL Server تقریبا یک شکل نوشته شود.

سه چیز است که SQL نیست و این سه، بیشترین اشتباه در گفت و گوهای روزمره است. اول، SQL خود پایگاه داده نیست؛ پایگاه داده جایی است که داده نشسته و SQL راه حرف زدن با آن است. دوم، SQL یک زبان کامل برنامه نویسی برای ساخت سایت نیست؛ با آن نمی توانید فرم تماس یا صفحه محصول بسازید. سوم، SQL روش ذخیره سازی نیست؛ فایل، جدول، ایندکس و ساختار داخلی همه در اختیار موتور پایگاه داده است. برای اینکه جای این مفاهیم کنار هم روشن شود، تعریف دیتابیس چیست را یک بار بخوانید و بعد به این صفحه برگردید.

چهار خانواده دستورها؛ کار هر دستور در یک جدول

دستورهای SQL را معمولا در پنج خانواده دسته بندی می کنند، ولی برای کار روزمره همین چهار خانواده کافی است. جدول زیر کار هر خانواده و یک مثال واقعی از کسب و کار را نشان می دهد.

یک خانواده پنجم هم هست به نام کنترل تراکنش با دستورهای COMMIT و ROLLBACK. کارش این است که چند تغییر را مثل یک بسته واحد تایید یا کامل برگشت بدهد. در انتقال پول همین خانواده است که نمی گذارد پول از یک حساب کم شود و به حساب دیگر نرسد. اگر روزی با این واژه روبرو شدید، معنایش همین است و نه چیز پیچیده تر.

تفاوت SQL با دیتابیس، با MySQL و با کد سایت

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

یک جمله که در جلسه ها زیاد لازم می شود: MySQL و SQL دو چیز جدا هستند. SQL زبان است و MySQL یکی از موتورهایی است که این زبان را می فهمد. پس وقتی می پرسند «دیتابیس سایت شما چیست؟»، جواب درست نام موتور و نسخه آن است، مثل MySQL نسخه ۸.

یک نمونه واقعی: خواندن، نوشتن و تغییر داده

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

SELECT id, customer, city, total
FROM orders
WHERE city = 'shiraz'
ORDER BY total DESC
LIMIT 10;

این دستور چهار کار را پشت سر هم انجام می دهد و ترتیبش اتفاقی نیست. اول با FROM می گوید از کدام جدول بخوان، بعد با WHERE فقط ردیف های شیراز را نگه می دارد، بعد با ORDER BY از بزرگ به کوچک مرتب می کند، و آخر با LIMIT فقط ده ردیف اول را می آورد. نتیجه شبیه یک جدول است: هر ردیف یک سفارش، هر ستون یک ویژگی. همین که نتیجه جدولی است، کار را ساده می کند؛ می شود آن را در اکسل هم بررسی کرد.

خواندن تنها نیمی از کار است. کد زیر نوشتن، تغییر و پاک کردن یک رکورد را نشان می دهد و همان جایی است که باید احتیاط کرد.

INSERT INTO orders (customer, city, total)
VALUES ('c1024', 'shiraz', 4500000);

UPDATE orders SET total = 4600000
WHERE id = 1024;

DELETE FROM orders WHERE id = 1024;

به WHERE در دو دستور آخر دقت کنید. اگر UPDATE یا DELETE را بدون WHERE اجرا کنید، همه ردیف های جدول تغییر می کند یا پاک می شود و راه برگشتی جز پشتیبان نیست. این ساده ترین اشتباه گران قیمت در کار با SQL است و تقریبا همه، یک بار دچارش شده اند.

در پروژه های واقعی، برنامه نویس همه روز این دستورها را دستی نمی نویسد. در لاراول، لایه الکوئنت در لاراول همین دستورها را می سازد و به پایگاه داده می فرستد؛ کار شما خواندن کدی است که می گوید چه داده ای لازم است، نه نوشتن SQL خام در هر خط. وردپرس هم همین کار را می کند، فقط با توابعی مثل wpdb.

چرا بعضی پرس و جوها کند است؟ ایندکس و عددهای اندازه گیری شده

کندی پایگاه داده معمولا یک علت مشخص دارد: پرس و جویی که مجبور است همه ردیف ها را یکی یکی بخواند. راه حل هم مشخص است: ایندکس، که مثل فهرست انتهای کتاب عمل می کند و به موتور می گوید مستقیم سراغ ردیف مورد نظر برود. برای اینکه این حرف شعار نماند، روی همین میزکار یک جدول با ۲۰۰ هزار ردیف سفارش ساختیم و یک پرس و جوی واقعی را هفت بار اجرا کردیم. کد زیر همان سنجش را با دو دستور انجام می دهد.

EXPLAIN QUERY PLAN
SELECT id, city, total FROM orders WHERE customer = 'c150000';

CREATE UNIQUE INDEX idx_customer ON orders(customer);

دستور EXPLAIN نقشه اجرای پرس و جو را نشان می دهد: پیش از ایندکس نوشت SCAN orders، یعنی پیمایش همه جدول، و پس از ساخت ایندکس نوشت SEARCH orders USING INDEX idx_customer. عددها هم همین را تایید می کند: میانه زمان پرس و جو از ۲۰٫۱ میلی ثانیه به ۰٫۰۱۵ میلی ثانیه رسید، یعنی حدود هزار و سیصد برابر سریع تر. ساخت ایندکس هم ۱۳۲ میلی ثانیه وقت برد؛ هزینه ای یک بار پرداختنی در برابر سودی همیشگی.

ردیف دوم جدول را از قصد گذاشتیم، چون درس مهمی دارد. وقتی پرس و جو بخش بزرگی از جدول را می خواند و فقط می خواهد جمع و شمارش بگیرد، ایندکس کمک چندانی نمی کند؛ چون هزینه خواندن مانده است. پس ایندکس درمان همه دردها نیست و بی حساب هم سود نمی دهد: هر ایندکس روی سرعت نوشتن و حجم دیسک اثر می گذارد. برای کاهش کلی زمان پاسخ سایت، سراغ همان کارهایی بروید که در افزایش سرعت سایت با عدد فهرست شده اند و ایندکس را آخر بگذارید.

پرس و جوی کند را از کجا بفهمیم؟

پیش از هر بهینه سازی باید بفهمید کدام پرس و جو کند است، و برای این کار لازم نیست ابزار بخری. دو راه ساده وجود دارد. راه اول لاگ پرس و جوهای کند در خود MySQL است: با دستورهای SET GLOBAL slow_query_log = 'ON' و SET GLOBAL long_query_time = 1; سرور هر پرس و جوی بیش از یک ثانیه را ثبت می کند تا فهرست واقعی کندها را ببینید. راه دوم، گوش دادن به پرس و جوهای خود برنامه است. کد زیر یک شنونده ساده در لاراول است که هر پرس و جوی بیش از نیم ثانیه را در لاگ می نویسد.

DB::listen(function ($query) {
    if ($query->time > 500) {
        Log::warning($query->sql, $query->bindings);
    }
});

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

سه اشتباه امنیتی که به داده ضربه می زند

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

$sql = "SELECT * FROM users WHERE email = '$_GET[email]'";

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);

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

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

مسیر یادگیری SQL: ترتیب درست، نه فهرست بلند

اگر می خواهید خودتان یاد بگیرید، ترتیب زیر در چند هفته به نتیجه می رسد. اول ساختار را بفهمید: جدول، ردیف، ستون و کلید اصلی. بعد یک دستور خواندن با WHERE و ORDER BY و LIMIT بنویسید و روی داده واقعی سایت خودتان اجرا کنید. در پله سوم نوشتن و تغییر را یاد بگیرید و همان جا قاعده احتیاط با WHERE را تمرین کنید. پله چهارم اتصال جدول ها و جمع زدن سطرها است، مثل گزارش فروش ماهانه به تفکیک شهر. بهترین تمرین این است که هر گزارش مالی که امروز در اکسل می سازید، یک بار با SQL بسازید؛ همان کار، بیشترین مهارت را در کمترین وقت می دهد.

خودتان انجام بدهید یا سفارش بدهید؟

تصمیم در بیشتر پروژه ها روشن است، به شرطی که اندازه کار را درست ببینید. جدول زیر مرز را نشان می دهد.

معیار سرانگشتی ما این است: اگر کار به داده مشتریان دست می زند یا باید ساختار جدول عوض شود، آن را نسپارید به آزمون و خطا. این کارها بخشی از ساخت نرم افزار اختصاصی و نگه داری فنی سامانه است و در کار ما با پشتیبان گیری، اجرای آزمایشی روی نسخه کپی و گزارش پیش و پس تحویل داده می شود.

معیار پذیرش کار پایگاه داده

هر سفارشی در این حوزه باید با چند معیار روشن بسته شود، وگرنه معلوم نیست کار تمام شده یا نه. این پنج مورد را در قرارداد یا در گفت و گوی اول بخواهید: پشتیبان کامل پیش از هر تغییر و یک بار آزمون بازیابی آن؛ اجرای تغییر روی نسخه کپی و گزارش نتیجه؛ فهرست دقیق جدول ها و ستون هایی که عوض شده؛ زمان پاسخ پرس و جوهای اصلی پیش و پس از تغییر؛ و یک سطر درباره اینکه اگر چیزی خراب شد، برگشت به حالت قبل چطور انجام می شود.

و یک مرز صریح: ما دوره آموزشی SQL نمی فروشیم و در این صفحه هم آموزش برنامه نویسی نمی دهیم. کاری که انجام می دهیم نگه داری و اصلاح پایگاه داده سایت شما، سبک سازی پرس و جوها، مهاجرت داده و طراحی امنیت آن است. اگر جایی از سامانه شما کند است و نمی دانید از پایگاه داده است یا از جای دیگر، همان اندازه گیری اول را برایتان انجام می دهیم و بعد تصمیم می گیریم.

جمع بندی

SQL زبان سوال پرسیدن از داده است: خواندن، نوشتن، تغییر، حذف و کنترل دسترسی، همه با چند دستور ساده که در هر موتور پایگاه داده تقریبا یک شکل اند. کاری که از شما لازم است و امروز شدنی است این است: فهرست پرس و جوهای کند سایت خودتان را بگیرید و روی کندترین شان یک بار EXPLAIN اجرا کنید. اگر پاسخش SCAN بود و جدول بزرگ است، علت کندی را پیدا کرده اید و لازم نیست بیشتر بگردید.