SELECT، WHERE، ORDER BY و LIMIT؛ و ترتیب واقعی اجرای کوئری

اولین جدول و اولین کوئری‌ها

در سراسر این فصل با جدول فرش‌های یک فروشگاه کار می‌کنیم. قیمت‌ها به ریال و با DECIMAL ذخیره می‌شوند (دلیلش را در فصل سوم می‌بینیم).

CREATE TABLE carpets (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sku         VARCHAR(20)  NOT NULL UNIQUE,
  title       VARCHAR(150) NOT NULL,
  city        VARCHAR(40)  NOT NULL,
  reeds       SMALLINT UNSIGNED NULL,        -- تراکم (شانه)
  width_cm    SMALLINT UNSIGNED NOT NULL,
  length_cm   SMALLINT UNSIGNED NOT NULL,
  price       DECIMAL(14,0) NOT NULL,         -- ریال
  stock       INT NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO carpets (sku, title, city, reeds, width_cm, length_cm, price, stock) VALUES
('KSH-1001', 'فرش دستباف کاشان لچک‌ترنج', 'کاشان', 50, 200, 300, 480000000, 2),
('TBZ-2040', 'فرش تبریز ماهی', 'تبریز', 60, 150, 225, 350000000, 1),
('NAN-0310', 'نائین ۹ لا ابریشم', 'نائین', NULL, 100, 150, 920000000, 0),
('KSH-M700', 'فرش ماشینی ۷۰۰ شانه', 'کاشان', 700, 250, 350, 65000000, 40),
('QOM-0120', 'قم تمام ابریشم', 'قم', 70, 100, 150, 1250000000, 1);

خواندن با شرط، مرتب‌سازی و محدود کردن

SELECT sku, title, price / 10 AS price_toman
FROM carpets
WHERE city = 'کاشان' AND stock > 0
ORDER BY price DESC, id
LIMIT 10;

-- صفحه‌ی سوم با ۲۰ ردیف در هر صفحه
SELECT id, title FROM carpets ORDER BY id LIMIT 20 OFFSET 40;

SELECT * را فقط برای کاوش دستی بزنید. در کد برنامه ستون‌ها را نام ببرید: هم داده‌ی کمتری جابه‌جا می‌شود، هم اضافه شدن ستون جدید برنامه را نمی‌شکند و هم (در فصل پنجم) امکان covering index فراهم می‌شود.

ترتیب منطقی اجرا

کوئری به ترتیبی که می‌نویسید اجرا نمی‌شود. ترتیب منطقی این است:

1. FROM و JOIN — کدام ردیف‌ها در دسترس‌اند
2. WHERE — فیلتر ردیف‌ها
3. GROUP BY و بعد HAVING
4. SELECT — محاسبه‌ی ستون‌ها و نام مستعار (alias)
5. ORDER BY و در پایان LIMIT

به همین دلیل نمی‌توانید در WHERE از aliasی که در SELECT ساخته‌اید استفاده کنید (هنوز ساخته نشده)، اما در ORDER BY می‌توانید. MySQL در HAVING هم alias را می‌پذیرد که یک توسعه‌ی غیراستاندارد است.

-- خطا: Unknown column 'price_toman' in 'where clause'
SELECT price / 10 AS price_toman FROM carpets WHERE price_toman > 1000000;
-- درست
SELECT price / 10 AS price_toman FROM carpets WHERE price > 10000000 ORDER BY price_toman;

صفحه‌بندی سریع با keyset

LIMIT 20 OFFSET 200000 یعنی سرور ۲۰۰٬۰۲۰ ردیف را بخواند و ۲۰۰٬۰۰۰ تا را دور بریزد؛ صفحه‌های آخر فهرست‌های بزرگ به همین دلیل کندند. روش keyset (یا seek) به‌جای شماره‌ی صفحه، آخرین کلید دیده‌شده را می‌گیرد:

SELECT id, title FROM carpets
WHERE id > 18340          -- آخرین id صفحه‌ی قبل
ORDER BY id
LIMIT 20;

نکته‌هایی که کمتر کسی می‌داند

• بدون ORDER BY، ترتیب نتیجه هیچ تضمینی ندارد؛ ممکن است امروز بر اساس id باشد و فردا بعد از اضافه شدن یک ایندکس عوض شود.
• اگر ORDER BY روی ستونی غیریکتا باشد (مثلاً price)، صفحه‌بندی با LIMIT ممکن است یک ردیف را دو بار نشان دهد و یکی را هرگز؛ همیشه یک ستون یکتا (id) را به‌عنوان مرتب‌سازی دوم اضافه کنید.
• در MySQL می‌توانید بنویسید ORDER BY 2 (ستون دوم SELECT)؛ کوتاه است اما با جابه‌جا شدن ستون‌ها بی‌صدا عوض می‌شود.
• نتیجه‌ی تقسیم price / 10 همیشه اعشاری است (div_precision_increment رقم اعشار)؛ برای تقسیم صحیح از price DIV 10 استفاده کنید.
• در کلاینت، SELECT ... \G برای جدول‌هایی با ستون فارسی طولانی خواناتر است، چون چینش راست‌به‌چپ در جدول ASCII به هم می‌ریزد.