Đến nội dung chính
notes.vinhnq.com

Tìm kiếm

Database & ORMSenior25 phút đọc

Database: index, EXPLAIN, N+1, phân trang và soft delete

Ôn 18 thẻ
Trong bài này

Cụm bài Database: 1. Bảng và SQL cơ bản · 2. ERD, JOIN, GROUP BY · 3. Chuẩn hoá · 4. Bài tập thư viện · 5. Index và hiệu năng truy vấn · 6. Transaction và locking

Bài này trả lời câu hỏi vì sao một câu SQL đúng vẫn chậm, và sửa thế nào. Đọc xong, bạn thiết kế được index, đọc được EXPLAIN, sửa N+1, phân trang keyset và biết cái giá của soft delete. Nên đọc trước JOIN và GROUP BY; phần đúng khi có nhiều request đồng thời nằm ở Transaction, isolation level và locking.

Dialect: MySQL 8 với InnoDB. Các output EXPLAIN dưới đây là kết quả thật mình chạy trên MySQL 8.4 với bảng orders 50.000 dòng. Chỗ nào PostgreSQL khác đáng kể đều có ghi chú.

CREATE TABLE orders (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT        NOT NULL,
  status      VARCHAR(16)   NOT NULL,   -- NEW / PAID / SHIPPED / CANCELLED
  total       DECIMAL(12,2) NOT NULL,
  created_at  DATETIME      NOT NULL,
  KEY idx_orders_created (created_at, id),
  KEY idx_orders_cust_status_created (customer_id, status, created_at)
);

1. Index

Ý chính: index là một cấu trúc sắp xếp sẵn giúp tìm dòng trong O(log n) thay vì đọc cả bảng. Nó chỉ giúp khi thứ tự cột khớp với truy vấn và điều kiện không bọc cột trong hàm; đổi lại, ghi chậm hơn.

Đi sâu hơn: Index bên trong (B-tree so với B+tree, clustered so với heap, chọn khoá chính, bloat, rebuild) và Các loại index ngoài B-tree (GIN, GiST, bitmap, LSM-tree).

1.1. Không có index thì sao?

Không có index, tìm đơn của khách 42 phải đọc mọi dòng (full table scan): O(n). Index là cấu trúc dữ liệu phụ, sắp xếp sẵn theo một hoặc nhiều cột, tìm trong O(log n).

1.2. B+tree: cấu trúc bên dưới

Hầu hết index trong MySQL (InnoDB) và PostgreSQL là B+tree:

flowchart TB
  R["Root: [ 100 | 200 ]"] --> I1["Internal: [ 30 | 60 ]"]
  R --> I2["Internal: [ 130 | 160 ]"]
  R --> I3["Internal: [ 230 | 260 ]"]
  I1 --> L1["Leaf: 1..29"]
  I1 --> L2["Leaf: 30..59"]
  I1 --> L3["Leaf: 60..99"]
  L1 -. "next" .-> L2
  L2 -. "next" .-> L3
  • Mỗi node là một page (InnoDB mặc định 16 KB) chứa hàng trăm khoá. Nhờ fan-out (số nhánh con mỗi node) lớn, cây chỉ cao 3–4 tầng dù có hàng trăm triệu dòng; các tầng trên thường nằm sẵn trong buffer pool (vùng RAM làm cache của DB).
  • Chỉ lá chứa dữ liệu (hoặc con trỏ), và các lá nối thành danh sách liên kết. Range scan (BETWEEN, >, ORDER BY) chỉ cần tìm điểm đầu rồi đi tuần tự theo lá.
  • Hash tra bằng nhanh hơn nhưng không làm được range hay sort. Cây nhị phân quá cao, mỗi tầng một lần đọc đĩa.

1.3. Clustered index và secondary index trong InnoDB

Clustered index (PK) Secondary index
Lá chứa gì Toàn bộ dòng Giá trị cột index + giá trị PK
Số lượng Đúng 1 mỗi bảng Nhiều
Hệ quả Dữ liệu sắp xếp vật lý theo PK Tìm được rồi phải tra tiếp clustered index bằng PK để lấy cột còn lại

Bước tra tiếp bằng PK gọi là lookup về bảng (bookmark lookup). Tìm 10.000 dòng qua secondary index là 10.000 lần nhảy ngẫu nhiên, nên optimizer đôi khi bỏ index và quét cả bảng.

1.4. Composite index và quy tắc leftmost prefix

Index (customer_id, status, created_at) sắp như danh bạ: theo customer_id, rồi status, rồi created_at.

Truy vấn Dùng được index?
WHERE customer_id = ? Có (prefix 1 cột)
WHERE customer_id = ? AND status = ? Có (prefix 2 cột)
WHERE customer_id = ? AND status = ? ORDER BY created_at Có, không cần sort
WHERE customer_id = ? AND created_at > ? Chỉ phần customer_id (bỏ qua status thì đứt chuỗi)
WHERE status = ? Không (thiếu cột đầu)

Thứ tự cột khi thiết kế composite index:

  1. Cột so sánh bằng (=, IN) đặt trước.
  2. Cột sắp xếp hoặc range (>, BETWEEN) đặt sau cùng. Sau một cột range, các cột phía sau không còn dùng để tìm được.
  3. Trong nhóm so sánh bằng, cột selectivity cao thường đặt trước, nhưng quan trọng hơn là index phục vụ được nhiều truy vấn nhất nhờ leftmost prefix.
Kiểm chứng bằng EXPLAIN: key_len cho biết dùng mấy cột
EXPLAIN SELECT id, status, created_at FROM orders
WHERE customer_id = 42 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 10;
type: ref   key: idx_orders_cust_status_created   key_len: 74   ref: const,const
rows: 1     Extra: Backward index scan; Using index
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND created_at > '2026-01-10';
type: ref   key: idx_orders_cust_status_created   key_len: 8   ref: const
rows: 25    filtered: 50.00   Extra: Using index condition
  • key_len: 8 = chỉ 8 byte của customer_id (BIGINT) dùng để tìm; created_at chỉ được lọc sau.
  • key_len: 74 = 8 (BIGINT) + 66 (VARCHAR(16) utf8mb4: 16×4 + 2 byte độ dài), tức dùng cả hai cột.
  • Từ MySQL 8.0.13 có index skip scan: đôi khi dùng được index khi thiếu cột đầu, nếu cột đầu có rất ít giá trị khác nhau. Đừng thiết kế dựa vào nó.

1.5. Covering index

Nếu mọi cột câu truy vấn cần đều nằm trong index, DB không cần quay về bảng; EXPLAIN hiện Using index. Ví dụ SELECT id, status, created_at ở trên là covering, vì id là PK, có sẵn trong mọi secondary index của InnoDB.

PostgreSQL gọi là index-only scan, có INCLUDE để thêm cột không dùng để tìm: CREATE INDEX ... ON orders (customer_id) INCLUDE (total). Nó còn phụ thuộc visibility map (bản đồ page nào chỉ chứa dòng ai cũng thấy), nên bảng ít được VACUUM vẫn phải đọc heap.

1.6. Selectivity: khi index vô dụng

Selectivity (độ chọn lọc) = số giá trị khác nhau / tổng số dòng. email gần bằng 1, status 4 giá trị trên 50.000 dòng thì gần 0.

EXPLAIN SELECT * FROM orders WHERE status = 'PAID';
type: ALL   key: NULL   rows: 50170   filtered: 10.00   Extra: Using where

Không index nào bắt đầu bằng status: quét cả bảng.

Optimizer ước lượng dựa trên statistics (thống kê phân bố dữ liệu); thống kê cũ (sau khi nạp dữ liệu lớn) làm plan sai. Cập nhật bằng ANALYZE TABLE (MySQL) hoặc ANALYZE (Postgres); MySQL 8 còn có histogram: ANALYZE TABLE orders UPDATE HISTOGRAM ON status.

1.7. Sargable: đừng bọc cột trong hàm

-- KHÔNG dùng được index trên created_at
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2026-01-10';
-- type: ALL, rows: 50170

-- Dùng được index: so cột "trơn" với khoảng nửa mở
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2026-01-10' AND created_at < '2026-01-11';
-- type: range, key: idx_orders_created, rows: 1440
PostgreSQL: lọc theo ngày còn dính múi giờ của session

WHERE created_at::date = '2024-06-10' vừa không sargable vừa tính “ngày 10/06” theo TimeZone của session. App (UTC) và máy dev (giờ Việt Nam) sẽ ra hai kết quả khác nhau. Cách viết khoảng nửa mở có offset tường minh ở bài Lưu ngày giờ trong PostgreSQL, MySQL và JSON.

1.8. Cái giá của index

  • Ghi chậm hơn: mỗi INSERT cập nhật mọi index; mỗi UPDATE cột có index sửa index đó.
  • Tốn bộ nhớ: tranh buffer pool với dữ liệu.
  • Index thừa: (a) là thừa nếu đã có (a, b).

Luyện chọn index trên schema thật: Thiết kế database đặt vé máy bay có bảng index cho từng truy vấn chính (tìm chuyến theo tuyến và ngày, sơ đồ ghế, đếm ghế trống bằng partial index) và bài tập.

2. Đọc EXPLAIN

Ý chính: nhìn type (cách truy cập), key/key_len (index nào, mấy cột) và Extra (có sort thêm, có bảng tạm không). EXPLAIN chỉ ước lượng; muốn biết thật thì EXPLAIN ANALYZE.

Cột Ý nghĩa
type Cách truy cập, tốt đến tệ: const (tra PK/unique bằng hằng) → eq_ref (join theo PK/unique) → ref (tra index không unique) → range → index (quét toàn bộ index) → ALL (quét toàn bảng)
key Index thực sự được dùng (possible_keys chỉ là ứng viên)
key_len Số byte index được dùng, cho biết dùng mấy cột của composite index
rows Ước lượng số dòng phải đọc
filtered % dòng ước lượng còn lại sau điều kiện
Extra Using index (covering), Using where, Using index condition (ICP, lọc ngay trên index), Using filesort (phải sort), Using temporary (bảng tạm, thường do GROUP BY/DISTINCT)

EXPLAIN ANALYZE (MySQL 8.0.18+) chạy thật và in thời gian, số dòng thực tế từng bước; PostgreSQL dùng EXPLAIN (ANALYZE, BUFFERS). Cẩn thận: với UPDATE/DELETE là chạy thật, nên bọc trong transaction rồi rollback.

Quy trình tối ưu một query chậm:

  1. Lấy query và tham số thật từ slow query log hoặc APM (đừng đoán).
  2. EXPLAIN ANALYZE: bước nào tốn nhất, rows ước lượng lệch xa thực tế không.
  3. Lệch xa thì cập nhật statistics trước khi thêm index.
  4. Kiểm tra sargability, thứ tự cột index, khả năng covering.
  5. Đo lại trên dữ liệu cỡ production; “nhanh” trên 1.000 dòng chẳng chứng minh gì.
Đọc plan của PostgreSQL: hai bài nên đọc tiếp

3. Bài toán N+1

Ý chính: một query lấy N bản ghi, rồi lazy loading bắn thêm một query cho mỗi bản ghi: 1 + N query. Sửa bằng cách lấy dữ liệu liên quan có chủ đích trong một hoặc vài query.

// Lấy 100 đơn hàng
List<Order> orders = orderRepository.findTop100ByStatus(Status.PAID);   // 1 query
for (Order o : orders) {
    // customer là LAZY -> mỗi vòng lặp bắn thêm 1 query
    System.out.println(o.getCustomer().getName());                    // +100 query
}

Mỗi query 1 ms, cộng round-trip thì 101 query thành 100+ ms, connection bị giữ lâu hơn. ORM che giấu nên dễ lọt lên production.

Phát hiện: log SQL ở dev (spring.jpa.show-sql, tốt hơn là datasource-proxy/p6spy để thấy tham số), hibernate.generate_statistics, hoặc test đếm số query cho endpoint quan trọng.

Sửa:

// 1. JOIN FETCH: một query duy nhất
@Query("select o from Order o join fetch o.customer where o.status = :status")
List<Order> findPaidWithCustomer(@Param("status") Status status);

// 2. Entity graph: khai báo thay vì viết JPQL
@EntityGraph(attributePaths = "customer")
List<Order> findTop100ByStatus(Status status);

// 3. DTO projection: chỉ lấy cột cần, không tạo entity (Java record)
public record OrderView(Long id, BigDecimal total, String customerName) {}

@Query("""
       select new com.example.OrderView(o.id, o.total, c.name)
       from Order o join o.customer c
       where o.status = :status
       """)
List<OrderView> findViews(@Param("status") Status status);
# 4. Batch fetching: gom N query lazy thành ceil(N/50) query dạng WHERE id IN (...)
spring.jpa.properties.hibernate.default_batch_fetch_size=50

Đừng dùng FetchType.EAGER để “sửa” N+1: để mọi association LAZY và fetch có chủ đích theo use case.

Bẫy khi sửa N+1: phân trang collection, MultipleBagFetchException, EAGER
  • JOIN FETCH một collection kèm phân trang: Hibernate không LIMIT được ở SQL (join nhân dòng), nên kéo hết về rồi phân trang trong bộ nhớ, kèm cảnh báo HHH90003004 (Hibernate 6). Cách đúng: phân trang id trước, rồi fetch theo danh sách id; hoặc dùng batch fetching.
  • Fetch cùng lúc hai collection kiểu List (bag) gây MultipleBagFetchException. Đổi sang Set để né thì kết quả vẫn là tích Descartes. Tách thành nhiều query.
  • FetchType.EAGER sai hướng vì N+1 vẫn xảy ra với query JPQL, chỉ là lúc nào cũng xảy ra.

4. Phân trang: OFFSET và keyset

Ý chính: OFFSET đọc rồi vứt N dòng nên trang càng sâu càng chậm. Keyset lấy các dòng sau dòng cuối trang trước theo khoá có index, nên trang nào cũng nhanh như nhau.

4.1. OFFSET chậm dần theo số trang

SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 40000;
-> Limit/Offset: 20/40000 row(s)
    -> Index scan on orders using PRIMARY  (rows=40020)

Để trả 20 dòng, DB đọc và bỏ đi 40.000 dòng: chi phí tuyến tính theo offset. Có dòng được insert/xoá giữa hai lần gọi thì người dùng còn thấy trùng hoặc sót dòng.

4.2. Keyset (cursor) pagination

-- Trang đầu
SELECT id, created_at, total FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Trang sau: truyền (created_at, id) của dòng cuối trang trước
SELECT id, created_at, total FROM orders
WHERE created_at < :last_created_at
   OR (created_at = :last_created_at AND id < :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Với index (created_at, id), DB nhảy thẳng tới vị trí con trỏ và đọc đúng 20 dòng: trang 1 hay trang 10.000 đều nhanh như nhau. Giống kẹp đánh dấu sách: mở ngay chỗ đang đọc, không phải đếm lại từ trang đầu.

Con trỏ thường mã hoá (base64 của created_at|id) thành chuỗi next_cursor để client không phụ thuộc cấu trúc bên trong.

Trong Spring Data JPA, OFFSET là Pageable + Page (kèm một câu COUNT(*)), còn Slice lấy size + 1 dòng để bỏ câu đếm: xem bài phân trang và lọc động.

Nếu bắt buộc dùng OFFSET: deferred join, và cái giá của COUNT(*)

Mẹo deferred join: phân trang trên covering index chỉ lấy id, rồi join lại lấy cột đầy đủ cho 20 dòng.

SELECT o.*
FROM orders o
JOIN (SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 40000) page
  ON page.id = o.id
ORDER BY o.created_at DESC, o.id DESC;

DB vẫn phải bỏ qua 40.000 entry, chỉ là bỏ qua trên index gọn thay vì dòng đầy đủ. Bài REST API nâng cao đo mẹo này trên PostgreSQL: nó dựa vào index-only scan, nên chỉ nhanh khi visibility map đã được VACUUM (ngay sau khi nạp dữ liệu, deferred join còn chậm hơn OFFSET thường). Bài đó cũng có cursor pagination ở mức API: cursor không bị client sửa và whitelist field sort.

SELECT COUNT(*) để hiện “tổng số trang” cũng phải quét toàn bộ dữ liệu khớp, vì InnoDB không lưu sẵn số dòng do MVCC (mỗi transaction có thể thấy số dòng khác nhau). Với bảng lớn, cân nhắc bỏ tổng số trang hoặc hiển thị số ước lượng.

5. Soft delete

Ý chính: đánh dấu deleted_at = NOW() thay vì DELETE, rồi lọc WHERE deleted_at IS NULL ở mọi nơi. Khôi phục được, nhưng giá phải trả thường bị đánh giá thấp.

Lợi ích: khôi phục khi xoá nhầm; giữ lịch sử, audit; không vỡ FK của dữ liệu lịch sử (đơn cũ vẫn trỏ tới sản phẩm đã ngừng bán).

Cái giá:

  1. Mọi truy vấn phải nhớ lọc. Quên một chỗ là dữ liệu đã xoá hiện lại. Hibernate có @SQLRestriction("deleted_at is null") (6.3+, thay @Where đã deprecated), @SQLDelete biến delete thành update, và @SoftDelete từ 6.4. Native query, báo cáo, tool BI vẫn phải tự lọc.
  2. Unique bị vỡ khi đăng ký lại cùng email (khối bên dưới).
  3. Bảng và index phình với dữ liệu chết. Index nên có deleted_at hoặc là partial index.
  4. Pháp lý: GDPR hay Luật Bảo vệ dữ liệu cá nhân 2025 của Việt Nam (trước đó là Nghị định 13/2023) có thể yêu cầu xoá thật dữ liệu cá nhân.
  5. Cascade: xoá mềm khách thì đơn hàng, địa chỉ ra sao? FK ON DELETE không còn giúp, phải tự viết logic.

Bài tập

Bài mình tự đặt (khoá học không có bài tập phần này).

Bài 1: Thiết kế index cho màn hình “đơn hàng của tôi”

Đề: API lấy 10 đơn mới nhất của một khách theo trạng thái: WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 10. Thiết kế index để không phải sort và không phải quay về bảng nếu chỉ cần id, status, created_at.

Lời giải bài 1

(customer_id, status, created_at): hai cột so sánh bằng trước, cột sắp xếp sau cùng. EXPLAIN cho type: ref, Extra: Backward index scan; Using index:

  • không có Using filesort: index đã sắp sẵn, chỉ việc đọc ngược;
  • có Using index: covering, vì id là PK nằm sẵn trong secondary index.

Nếu cần thêm total: hoặc chấp nhận lookup 10 dòng (rẻ), hoặc thêm total vào cuối index (đánh đổi kích thước index lấy covering).

Bài 2: Sửa query không sargable

Đề: báo cáo chạy WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 3 và luôn full scan. Viết lại.

Lời giải bài 2
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'

Khoảng nửa mở [đầu tháng, đầu tháng sau) đúng với mọi độ chính xác của thời gian (kể cả giây lẻ), khác với BETWEEN '2026-03-01' AND '2026-03-31 23:59:59' dễ sót dòng ở giây cuối.

Bài 3: Chuyển API phân trang sang keyset

Đề: GET /orders?page=2000&size=20 chậm. Đổi sang cursor.

Lời giải bài 3
  • API mới: GET /orders?size=20&cursor=<opaque>.
  • Server giải mã cursor thành (created_at, id), chạy câu keyset ở mục 4.2, trả items và next_cursor (mã hoá từ dòng cuối), hoặc null khi hết.
  • Cần index (created_at, id).
  • Giữ API cũ một thời gian cho client cũ (deprecate dần), vì keyset không hỗ trợ “nhảy tới trang 2000”.

Bài 4: Email unique với soft delete

Đề: bảng user có soft delete, người dùng xoá tài khoản rồi đăng ký lại bằng cùng email thì bị lỗi trùng. Sửa mà vẫn đảm bảo không có hai tài khoản đang hoạt động cùng email.

Lời giải bài 4

Generated column active_email + unique (mục 5). Mình đã chạy thử: xoá mềm a@x.com rồi insert lại a@x.com thành công, bảng có hai dòng, chỉ một dòng active. Trên Postgres dùng partial unique index.

Bài 5: Đọc một EXPLAIN có Using filesort

Đề: màn hình admin chạy SELECT id, total, created_at FROM orders WHERE status = 'PAID' ORDER BY created_at DESC LIMIT 50 và EXPLAIN cho type: ALL, Extra: Using where; Using filesort. Giải thích từng phần và đề xuất sửa.

Lời giải bài 5
  • type: ALL: quét cả bảng vì không có index nào bắt đầu bằng status.
  • Using filesort: sau khi lọc, DB phải tự sắp xếp mọi dòng PAID theo created_at rồi mới lấy 50 dòng đầu, nên LIMIT không giúp gì cho phần quét.
  • Sửa: index (status, created_at), cột so sánh bằng trước, cột sắp xếp sau. DB đi thẳng tới đoạn status = 'PAID', đọc ngược theo created_at và dừng sau 50 dòng (Backward index scan, hết filesort).
  • Mỗi dòng vẫn lookup về bảng để lấy total; muốn bỏ bước này thì dùng (status, created_at, total).
  • Về selectivity: status ít giá trị, nhưng index vẫn đáng giá vì nó phục vụ sắp xếp + LIMIT, không phải để lọc.
  • Cuối cùng, đo lại bằng EXPLAIN ANALYZE trên dữ liệu cỡ production.

Ghi nhớ

  • Giải thích được vì sao PK của InnoDB nên ngắn và tăng dần.
  • Cho một câu WHERE ... ORDER BY, xếp được thứ tự cột composite index và kiểm chứng bằng key_len, Extra.
  • Nhận ra điều kiện không sargable và viết lại thành khoảng nửa mở.
  • Biết bốn cách sửa N+1 và bẫy khi JOIN FETCH collection kèm phân trang.
  • Biết khi nào dùng OFFSET, khi nào keyset, và những gì soft delete bắt bạn phải xử lý.

Câu hỏi phỏng vấn

Junior

Index là gì, vì sao làm truy vấn nhanh hơn?

Là cấu trúc dữ liệu phụ (thường là B+tree) sắp xếp sẵn theo cột, tìm trong O(log n) thay vì quét cả bảng O(n).

Đổi lại, ghi chậm hơn và tốn bộ nhớ.

Bài toán N+1 là gì?

Một query lấy N bản ghi cha, rồi mỗi bản ghi kích hoạt thêm một query lấy dữ liệu liên quan: tổng 1 + N query.

Thường do lazy loading của ORM. Sửa bằng JOIN FETCH, entity graph, batch fetching hoặc DTO projection.

Khi nào không nên thêm index?

Khi index không giúp đọc đáng kể mà vẫn làm chậm mọi lần ghi.

  • Bảng rất nhỏ (quét cả bảng vẫn rẻ).
  • Cột selectivity thấp và không phục vụ sắp xếp.
  • Bảng ghi rất nhiều mà truy vấn đọc theo cột đó hiếm.
  • Index mới là tiền tố của index đã có ((a) thừa khi đã có (a, b)).

Mỗi index làm chậm INSERT/UPDATE và chiếm buffer pool.

Mid

Composite index (a, b, c) phục vụ được những truy vấn nào?

Những truy vấn dùng prefix bên trái: a, a,b, a,b,c.

Cùng với sắp xếp theo cột kế tiếp khi các cột trước so sánh bằng. Không phục vụ b hay c đứng một mình. Sau một điều kiện range trên b, cột c không còn dùng để tìm kiếm.

Covering index là gì? Nhận biết trong EXPLAIN thế nào?

Index chứa mọi cột mà query cần, nên DB không phải quay về bảng.

MySQL hiện Using index trong Extra, Postgres hiện Index Only Scan. Trong InnoDB, PK luôn có sẵn trong secondary index nên cũng được tính là “covered”.

Vì sao OFFSET lớn chậm và thay thế bằng gì?

DB phải đọc rồi bỏ đi offset dòng trước khi trả kết quả, nên chi phí tuyến tính theo độ sâu trang. Thay bằng keyset pagination.

Keyset: WHERE (sort_key, id) < (last_sort_key, last_id) ORDER BY sort_key DESC, id DESC LIMIT n với index phù hợp. Trên MySQL nên khai triển thành dạng OR để có range scan.

Một API đột nhiên chậm, bạn điều tra câu SQL thế nào?

Lấy câu SQL và tham số thật, chạy EXPLAIN ANALYZE, sửa theo bằng chứng rồi đo lại trên dữ liệu cỡ production.

  • Lấy query thật từ slow query log hoặc APM.
  • EXPLAIN ANALYZE: bước tốn nhất, so rows ước lượng với thực tế; lệch xa thì cập nhật statistics trước.
  • Kiểm tra type (có ALL không), key/key_len (dùng mấy cột index), Extra (Using filesort, Using temporary), sargability và thứ tự cột composite index.

Senior

Vì sao UUID v4 làm khoá chính có hại trong InnoDB, và nên dùng gì?

InnoDB sắp dữ liệu vật lý theo PK; UUID v4 ngẫu nhiên làm insert rơi khắp cây và bị chép vào mọi secondary index. Dùng PK tăng dần.

  • Insert ngẫu nhiên gây page split, page chỉ đầy một phần, buffer pool phải giữ nhiều page “nóng” hơn.
  • UUID 16 byte (36 byte nếu lưu chuỗi) bị chép vào mọi secondary index.
  • Lựa chọn: BIGINT AUTO_INCREMENT làm PK nội bộ và một cột UUID unique để lộ ra ngoài; hoặc ID tăng theo thời gian như UUIDv7/ULID lưu BINARY(16).
  • MySQL có UUID_TO_BIN(uuid, 1) để đảo các phần thời gian của UUID v1 cho tăng dần.
Bạn có ủng hộ soft delete không?

Tuỳ dữ liệu: ủng hộ cho dữ liệu cần khôi phục hoặc giữ tham chiếu lịch sử, với điều kiện xử lý đủ cái giá của nó.

  • Lọc tự động (@SQLRestriction/@SoftDelete).
  • Unique trên dòng active (partial index hoặc generated column).
  • Index có tính đến deleted_at.
  • Job archive/xoá thật sau N ngày, tuân thủ yêu cầu xoá dữ liệu cá nhân.

Nhiều trường hợp trạng thái nghiệp vụ rõ nghĩa (CANCELLED) hoặc bảng archive kèm audit log tốt hơn một cờ deleted.

Từ vựng tiếng Anh

Term Phát âm Nghĩa Ví dụ câu tiếng Anh dùng trong phỏng vấn
clustered index /ˈklʌstəd ˈɪndeks/ index chứa dữ liệu dòng “In InnoDB the primary key is the clustered index, so it should be short and monotonic.”
covering index /ˈkʌvərɪŋ ˈɪndeks/ index chứa đủ cột cần “I added the column to make it a covering index and avoid lookups.”
leftmost prefix /ˈleftməʊst ˈpriːfɪks/ tiền tố bên trái “The composite index only helps queries that use its leftmost prefix.”
selectivity /səˌlekˈtɪvəti/ độ chọn lọc “A status column has low selectivity, so an index on it alone rarely helps.”
sargable /ˈsɑːɡəbl/ điều kiện dùng được index “Wrapping the column in DATE() makes the predicate non-sargable.”
filesort /ˈfaɪlsɔːt/ bước sắp xếp thêm ngoài index “Using filesort tells me the index doesn’t match the ORDER BY.”
lazy loading /ˈleɪzi ˈləʊdɪŋ/ chỉ tải dữ liệu liên quan khi được truy cập “Lazy loading inside a loop is the usual cause of N+1 queries.”
keyset pagination /ˈkiːset ˌpædʒɪˈneɪʃn/ phân trang theo con trỏ “Keyset pagination keeps deep pages as fast as the first one.”
soft delete /sɒft dɪˈliːt/ xoá mềm bằng cờ/thời điểm xoá “Soft delete needs a partial unique index, otherwise re-registration fails.”
page split /peɪdʒ splɪt/ tách một page index đầy thành hai “Random UUID keys cause page splits all over the clustered index.”

Ghi chép dựa trên khoá Fundamentals of Backend (Ronin Engineer), viết lại theo cách hiểu của mình.