Sổ tay Backend

Giao diện

3. Schema trong MySQL ​

Kiến thức thiết kế chung (chuẩn hoá, quan hệ, EAV, versioning, soft delete, sổ cái, multi-tenant) nằm ở phần Database. Chương này chỉ gồm những gì riêng của MySQL/InnoDB: chọn khoá và ràng buộc, cột JSON, một schema mẫu hoàn chỉnh, và cách đổi schema khi hệ thống đang chạy.

Khoá và ràng buộc trong MySQL ​

  • Khoá chính: dùng khoá thay thế BIGINT tự tăng (hoặc ID có thứ tự như UUIDv7), không dùng khoá tự nhiên như email hay mã số thuế. Trong InnoDB, khoá chính là clustered index và bị chép vào mọi secondary index (chương 4); khoá tự nhiên có thể đổi và thường dài. Khoá tự nhiên để ở cột riêng có UNIQUE.
  • NOT NULL và DEFAULT theo nghiệp vụ (chương 2).
  • UNIQUE cho mọi thứ không được trùng, kể cả khoá idempotency. MySQL không có partial index, nên UNIQUE “chỉ trên dòng chưa xoá” phải dùng cột sinh: active_email AS (IF(deleted_at IS NULL, email, NULL)) + UNIQUE (Soft delete).
  • CHECK chỉ được thực thi từ 8.0.16. Phiên bản cũ hơn chấp nhận cú pháp nhưng âm thầm bỏ qua.
  • FOREIGN KEY trong InnoDB:
    • Khai báo bằng FOREIGN KEY (…) REFERENCES … ở mức bảng. REFERENCES viết ngay sau định nghĩa cột bị MySQL 8.x bỏ qua mà không báo lỗi.
    • InnoDB tự tạo index trên cột khoá ngoại nếu chưa có.
    • Mỗi lệnh ghi vào bảng con phải kiểm tra bảng cha và đặt shared lock trên dòng cha: ghi nhiều vào bảng con có thể chờ lock vì bảng cha.
    • Với MySQL 8.0/8.4, thay đổi do ON DELETE CASCADE được InnoDB tự thực hiện bên trong và không xuất hiện thành sự kiện riêng trong binlog, nên công cụ CDC như Debezium không thấy các dòng con bị xoá theo. Kiểm tra lại nếu dùng bản mới hơn.
    • gh-ost không hỗ trợ bảng có khoá ngoại.
    • Hệ thống nhỏ và vừa nên giữ FK; hệ thống rất lớn thường bỏ FK, kiểm tra ở tầng app và chạy job đối soát.

Cột JSON trong MySQL ​

Kiểu JSON lưu dạng nhị phân, đọc một trường không phải parse cả chuỗi. Lọc theo trường trong JSON phải qua cột sinh có index hoặc multi-valued index (8.0.17) cho mảng; kiểm tra cấu trúc bằng CHECK (JSON_SCHEMA_VALID(…, attrs)). Ví dụ đầy đủ và khi nào nên dùng JSON thay cho EAV: EAV, JSON & thuộc tính động.

Ví dụ: đơn hàng thương mại điện tử ​

customersid PKemail UQfull_namephonecreated_atordersid PKcustomer_id FKstatustotal_amountcreated_atorder_itemsorder_id PK FKproduct_id PK FKunit_pricequantityproductsid PKsku UQnamepricestock1:n1:n1:nPK: khoá chính · FK: khoá ngoại · UQ: unique · 1:n: một dòng bên này ứng với nhiều dòng bên kia
Bốn bảng, ba quan hệ 1:n. order_items.unit_price là snapshot giá lúc đặt, không phải bản sao của products.price.
sql
CREATE TABLE customers (
  id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email      VARCHAR(255) NOT NULL,
  full_name  VARCHAR(100) NOT NULL,
  phone      VARCHAR(20),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_email (email)
);

CREATE TABLE products (
  id    BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  sku   VARCHAR(40)  NOT NULL,
  name  VARCHAR(200) NOT NULL,
  price BIGINT UNSIGNED NOT NULL,                 -- VNĐ
  stock INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uq_sku (sku)
);

CREATE TABLE orders (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id  BIGINT UNSIGNED NOT NULL,
  status       TINYINT UNSIGNED NOT NULL DEFAULT 0,  -- 0 chờ, 1 đã trả, 2 đang giao, 3 xong, 9 huỷ
  total_amount BIGINT UNSIGNED NOT NULL,
  created_at   DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at   DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_customer (customer_id, created_at),      -- “đơn của tôi”, mới nhất trước
  KEY idx_status (status, created_at),             -- hàng đợi xử lý theo trạng thái
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);

CREATE TABLE order_items (
  order_id   BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  unit_price BIGINT UNSIGNED NOT NULL,            -- snapshot giá lúc đặt
  quantity   INT UNSIGNED NOT NULL,
  PRIMARY KEY (order_id, product_id),
  KEY idx_product (product_id),
  CHECK (quantity > 0),
  CONSTRAINT fk_items_order   FOREIGN KEY (order_id)   REFERENCES orders (id),
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products (id)
);
  • order_items dùng khoá chính ghép (order_id, product_id): các dòng của một đơn nằm liền nhau trong clustered index, đọc một đơn chỉ cần một vài page, và mỗi sản phẩm chỉ xuất hiện một lần trong một đơn.
  • unit_price là snapshot, không phải bản sao của products.price (Chuẩn hoá & phá chuẩn).
  • Hai index trên orders đặt cột so sánh bằng trước, cột sort sau, theo quy tắc ESR (chương 4).

Thay đổi schema khi hệ thống đang chạy ​

Thuật toán DDLVí dụẢnh hưởng
INSTANTThêm hoặc xoá cột (8.0.29+), đổi DEFAULT, thêm giá trị ENUM vào cuối, đổi tên cột hoặc indexChỉ sửa metadata, xong ngay
INPLACEThêm index, tăng độ dài VARCHAR trong cùng mức tiền tố độ dàiKhông khoá ghi nhưng có thể rebuild, tốn I/O, gây lag replica
COPYĐổi kiểu cột (INT → BIGINT), đổi charsetDựng lại cả bảng và chặn ghi suốt thời gian đó
  • Ghi rõ ALGORITHM=INSTANT hoặc ALGORITHM=INPLACE, LOCK=NONE trong câu lệnh: nếu MySQL không làm được theo cách đó thì báo lỗi ngay, thay vì âm thầm chuyển sang COPY.
  • Bảng lớn cần COPY: dùng gh-ost hoặc pt-online-schema-change (tạo bảng bóng, chép dần, rồi đổi tên).
  • Migration phải chạy được với cả code đang chạy lẫn code sắp deploy, vì trong lúc deploy hai phiên bản cùng tồn tại.

Bẫy metadata lock

Mọi DDL, kể cả INSTANT, cần metadata lock độc quyền trong chốc lát. Nếu có một transaction đang mở lâu trên bảng đó (một câu SELECT chưa commit cũng tính), DDL phải chờ. Trong lúc DDL chờ, mọi query mới trên bảng đó xếp hàng sau nó, và hệ thống đứng hình. Trước khi chạy DDL, đặt SET SESSION lock_wait_timeout = 5 để DDL tự bỏ cuộc rồi thử lại, và kiểm tra transaction dài trong information_schema.INNODB_TRX.

Expand – contract: đổi cột không cần downtime ​

Đổi tên hoặc đổi định dạng một cột (ví dụ chuẩn hoá phone sang dạng quốc tế ở cột phone_e164) không làm trong một bước. Chia thành các bước, mỗi bước đều có thể quay lại bước trước:

#BướcCột trong bảngAppĐọcGhi
0Ban đầuphonev1phonephone
1ADD COLUMN phone_e164, ALGORITHM=INSTANTphone, phone_e164 (rỗng)v1phonephone
2Deploy app v2: ghi cả hai cộtphone, phone_e164v2phonephone + phone_e164
3Backfill dữ liệu cũ theo batchphone, phone_e164 (đầy đủ)v2phonephone + phone_e164
4Deploy app v3: đọc cột mớiphone, phone_e164v3phone_e164phone + phone_e164
5Deploy app v4: chỉ dùng cột mớiphone, phone_e164v4phone_e164phone_e164
6DROP COLUMN phone, ALGORITHM=INSTANTphone_e164v4phone_e164phone_e164

App v1 đọc và ghi cột phone. Mục tiêu: chuyển sang cột phone_e164 (dạng +84…) mà không dừng hệ thống.

Ghi nhớ

  1. Khoá chính BIGINT tăng dần; khoá tự nhiên để UNIQUE; UNIQUE trên dòng chưa xoá dùng cột sinh.
  2. CHECK chỉ có hiệu lực từ 8.0.16; FK phải khai báo ở mức bảng, có chi phí lock và không đi qua CDC khi cascade.
  3. DDL: ghi rõ ALGORITHM, đặt lock_wait_timeout ngắn, đổi cột theo expand–contract.