کلید اصلی: AUTO_INCREMENT، UUID و اهمیت ترتیب درج

کلید اصلی در InnoDB فقط یک شناسه نیست

در InnoDB جدول خودش یک B-Tree است که بر اساس کلید اصلی مرتب شده (clustered index، فصل پنجم). یعنی کلید اصلی ترتیب فیزیکی ذخیره‌ی ردیف‌ها را تعیین می‌کند و در تک‌تک ایندکس‌های دیگر هم کپی می‌شود. سه پیامد مستقیم:

1. کلید اصلی کوچک یعنی همه‌ی ایندکس‌ها کوچک‌تر.
2. کلید صعودی (AUTO_INCREMENT) یعنی درج همیشه در انتهای درخت؛ سریع و بدون شکافتن صفحه.
3. کلید تصادفی (UUID نسخه‌ی ۴) یعنی درج در وسط درخت؛ page split، تکه‌تکه شدن و افت شدید سرعت درج در جدول‌های بزرگ.

AUTO_INCREMENT

CREATE TABLE order_items (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id  BIGINT UNSIGNED NOT NULL,
  carpet_id INT UNSIGNED    NOT NULL,
  qty       SMALLINT UNSIGNED NOT NULL
);

SHOW TABLE STATUS LIKE 'order_items'\G         -- مقدار Auto_increment بعدی
ALTER TABLE order_items AUTO_INCREMENT = 100000; -- شروع از عدد دلخواه

شماره‌ها شکاف دارند و این طبیعی است: تراکنش ROLLBACK‌شده، INSERT IGNORE یا upsert، شماره را مصرف می‌کنند و پس نمی‌دهند. هرگز از id برای «شماره‌ی فاکتور پشت‌سرهم» استفاده نکنید؛ شماره‌ی فاکتور رسمی را با جدول شمارنده و قفل (فصل ششم) بسازید.

UUID: وقتی لازم است، درستش را بسازید

UUID وقتی مفید است که شناسه باید قبل از درج در کلاینت یا چند سرور مستقل ساخته شود، یا نباید قابل حدس باشد (در URL). راه درست در MySQL:

CREATE TABLE devices (
  id    BINARY(16) PRIMARY KEY,           -- 16 بایت، نه CHAR(36) با 144 بایت در utf8mb4
  name  VARCHAR(80) NOT NULL
);

-- UUID() در MySQL نسخه‌ی ۱ (مبتنی بر زمان) است؛ آرگومان 1 بخش زمان را جلو می‌آورد تا صعودی شود
INSERT INTO devices VALUES (UUID_TO_BIN(UUID(), 1), 'دستگاه بافندگی ۳');

SELECT BIN_TO_UUID(id, 1) AS id, name FROM devices;

اگر UUID در برنامه ساخته می‌شود، نسخه‌ی ۷ (UUIDv7) را انتخاب کنید که ذاتاً بر اساس زمان مرتب است و مشکل درج تصادفی را ندارد. الگوی رایج دیگر: کلید اصلی داخلی BIGINT AUTO_INCREMENT و یک ستون UNIQUE جدا برای شناسه‌ی عمومی.

کلید طبیعی یا جانشین؟

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

• تا قبل از 8.0، شمارنده‌ی AUTO_INCREMENT پس از ری‌استارت به max(id)+1 برمی‌گشت و idهای حذف‌شده‌ی آخر دوباره استفاده می‌شدند؛ از 8.0 مقدار آن ماندگار است.
• جدول InnoDB بدون کلید اصلی و بدون UNIQUE NOT NULL، یک کلید پنهان ۶بایتی می‌گیرد که بین همه‌ی این جدول‌ها یک شمارنده‌ی سراسری مشترک دارد؛ هم کند است و هم Replication ردیفی را بسیار کند می‌کند.
• متغیر sql_require_primary_key = ON ساخت جدول بدون کلید اصلی را ممنوع می‌کند؛ بسیاری از سرویس‌های دیتابیس ابری آن را روشن دارند و مایگریشن‌های قدیمی را می‌شکنند.
• از 8.0.30 با sql_generate_invisible_primary_key = ON، MySQL برای جدول بی‌کلید خودش یک ستون نامرئی my_row_id می‌سازد.
• INT UNSIGNED حدود ۴٫۳ میلیارد مقدار دارد؛ برای جدول لاگ و رویداد از اول BIGINT بگذارید، چون تبدیل بعدی روی جدول میلیاردی یعنی ساعت‌ها بازسازی.