1Index là B+tree
Index là cấu trúc sắp sẵn, tìm dòng trong O(log n) thay vì đọc cả bảng. B+tree chỉ cao 3–4 tầng, các lá nối nhau nên range scan rẻ.
| InnoDB | Lá chứa gì |
|---|---|
| Clustered index (PK) | Toàn bộ dòng |
| Secondary index | Cột index + giá trị PK |
Database & ORMSenior29 phút đọc
Cả bài trong một trang: ý chính, sơ đồ, điểm phỏng vấn.
Index là cấu trúc sắp sẵn, tìm dòng trong O(log n) thay vì đọc cả bảng. B+tree chỉ cao 3–4 tầng, các lá nối nhau nên range scan rẻ.
| InnoDB | Lá chứa gì |
|---|---|
| Clustered index (PK) | Toàn bộ dòng |
| Secondary index | Cột index + giá trị PK |
Index (customer_id, status, created_at) sắp như danh bạ, chỉ dùng được theo prefix bên trái. Cột so sánh bằng trước, cột sort hay range sau cùng.
WHERE customer_id = ? AND status = ? ORDER BY created_at: không cần sortWHERE status = ?: không dùng được, thiếu cột đầuUsing indexstatus: optimizer thường bỏ quaBọc cột trong hàm hay ép kiểu ngầm làm index vô dụng. So cột "trơn" với khoảng nửa mở.
-- type: ALL, quét 50.170 dòng
WHERE DATE(created_at) = '2026-01-10'
-- type: range, đọc 1.440 dòng
WHERE created_at >= '2026-01-10'
AND created_at < '2026-01-11'
EXPLAIN chỉ ước lượng; EXPLAIN ANALYZE chạy thật. N+1 là 1 query lấy N dòng rồi lazy loading bắn thêm N query.
flowchart TD
A["Query chậm từ slow log"] --> B["EXPLAIN ANALYZE"]
B --> C{"rows lệch xa thực tế?"}
C -->|có| D["ANALYZE TABLE cập nhật statistics"]
C -->|không| E["Sửa sargable, thứ tự cột, covering"]
E --> F["Đo trên dữ liệu cỡ production"]
OFFSET đọc rồi vứt N dòng nên trang sâu chậm dần; keyset lấy dòng sau con trỏ, trang nào cũng nhanh. Soft delete giữ lịch sử nhưng có giá.
| OFFSET | Keyset | |
|---|---|---|
| Trang sâu | Chậm dần | Ổn định |
| Nhảy trang bất kỳ | Có | Chỉ trước/sau |
| Dữ liệu đổi | Có thể trùng, sót | Ổn định |
💡 Ghi nhớ
PK InnoDB nên ngắn và tăng dần.
PostgreSQL không tự index cột khoá ngoại.
Sửa N+1: JOIN FETCH, entity graph, batch fetching, DTO.
Soft delete: mọi query phải lọc deleted_at IS NULL.
⭐ Điểm phỏng vấn
Tự trả lời trước, rồi bấm từng câu để xem đáp án.
Thứ tự cột composite index?
So sánh bằng trước, sort hoặc range sau cùng.
OFFSET hay keyset?
OFFSET khi cần số trang; keyset cho feed, API.
Phát hiện N+1 thế nào?
Log SQL, Hibernate statistics, test đếm số query.
Xong phần tự kiểm tra, bài này đã được đánh dấu đã học ✓
⚠️ Lỗi thường gặp
UUID v4 làm PK InnoDB: dài, ngẫu nhiên, gây page split.
Dùng FetchType.EAGER để "sửa" N+1.
Soft delete với unique email: đăng ký lại bị trùng.
🛠 Tự tay làm
Tự viết phần lõi, chưa nhờ agent. Tick từng bước khi xong.
Nhiệm vụ: Làm nhanh GET /accounts/{id}/transactions (Phase 1, wallet-service) trên 1 triệu dòng ledger_entry: đo khi chưa có index, thêm composite index, đổi OFFSET sang keyset.
Tự viết: migration index, query keyset trong LedgerEntryRepository, tham số cursor, test đo · Được nhờ agent: script seed, hàm đo median.
(account_id, id DESC) của V1, sắp theo created_at DESC, id DESC; đo trang đầu account 42 và 1001, xem EXPLAIN (ANALYZE, BUFFERS).(account_id, created_at DESC, id DESC) bằng migration, đo lại.before bằng ?cursor=<createdAt>_<id> lọc (created_at, id) < (?, ?); kiểm tra trang ở dòng 90 000 giống OFFSET 90000.seeded ledger_entry=1,000,040 rows in 43 s
page 1, account 42 (900 rows) no index 47.10 ms | (account_id, created_at) 3.11 ms | (created_at, account_id) 2.29 ms
page 1, account 1001 (20 rows) no index 35.78 ms | (account_id, created_at) 3.25 ms | (created_at, account_id) 32.79 ms
account 1, row 90000 OFFSET 91.60 ms | keyset 1.82 ms
no index: Parallel Seq Scan on ledger_entry ... Rows Removed by Filter: 333044
OFFSET: Sort ... (actual time=108.499..121.820 rows=90020 loops=1) Sort Method: external merge Disk: 4120kB
keyset: Index Scan using ledger_entry_account_created ... Buffers: shared hit=5
Đổi thứ tự cột thành (created_at DESC, account_id): account 42 vẫn nhanh, nhưng account 1001 ít dùng lên 32,79 ms, đọc 3835 buffer thay vì 4, vì phải đi gần hết index từ mới về cũ mới gom đủ 20 dòng.
account_id phải đứng trước created_at?created_at, không có id, thì sai ở đâu?Xong bài thực hành. Giờ dán 3 câu hỏi cuối cho agent để nó chất vấn code của bạn.
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)
);
Ý 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).
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).
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
BETWEEN, >, ORDER BY) chỉ cần tìm điểm đầu rồi đi tuần tự theo lá.| 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.
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:
=, IN) đặt trước.>, 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.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.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.
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.
-- 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
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.
INSERT cập nhật mọi index; mỗi UPDATE cột có index sửa index đó.(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.
Ý 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:
EXPLAIN ANALYZE: bước nào tốn nhất, rows ước lượng lệch xa thực tế không.EXPLAIN (ANALYZE, BUFFERS).LIKE và pg_trgm, thứ tự cột composite, INCLUDE) và so plan trước và sau khi thêm index.Ý 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.
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.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.Ý 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.
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.
-- 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.
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.
Ý 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á:
@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.deleted_at hoặc là partial index.ON DELETE không còn giúp, phải tự viết logic.Bài mình tự đặt (khoá học không có bài tập phần này).
Đề: 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.
(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:
Using filesort: index đã sắp sẵn, chỉ việc đọc ngượ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áo cáo chạy WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 3 và luôn full scan. Viết lại.
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.
Đề: GET /orders?page=2000&size=20 chậm. Đổi sang cursor.
GET /orders?size=20&cursor=<opaque>.(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.(created_at, id).Đề: 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.
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.
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.
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.(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).total; muốn bỏ bước này thì dùng (status, created_at, total).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.EXPLAIN ANALYZE trên dữ liệu cỡ production.WHERE ... ORDER BY, xếp được thứ tự cột composite index và kiểm chứng bằng key_len, Extra.JOIN FETCH collection kèm phân trang.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ớ.
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 index không giúp đọc đáng kể mà vẫn làm chậm mọi lần ghi.
(a) thừa khi đã có (a, b)).Mỗi index làm chậm INSERT/UPDATE và chiếm buffer pool.
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.
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”.
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.
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.
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.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.Từ tốt tới xấu: const/system (một dòng theo PK/unique), eq_ref (join theo PK/unique, mỗi dòng trước khớp đúng một dòng), ref (so sánh bằng trên index không unique), range (một khoảng trên index), index (quét toàn bộ index), ALL (quét toàn bảng).
index dễ bị hiểu nhầm là tốt: nó đọc hết cây index, thường do thiếu cột đầu của composite index hoặc do ORDER BY theo index mà không có WHERE lọc được. Nhanh hơn ALL một chút vì index nhỏ hơn bảng, nhưng vẫn tăng tuyến tính theo kích thước. Đọc type cùng rows (ước lượng số dòng phải đọc) và Extra (Using index, Using filesort, Using temporary). Muốn biết thật thì dùng EXPLAIN ANALYZE.
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.
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).UUID_TO_BIN(uuid, 1) để đảo các phần thời gian của UUID v1 cho tăng dần.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ó.
@SQLRestriction/@SoftDelete).deleted_at.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.
| 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.