Khóa ngoại: chi phí nằm trong trigger, không nằm trong plan

Câu hỏi bài này trả lời: khóa ngoại có làm chậm thao tác ghi không, vì sao xóa một lô hàng cha có thể mất hơn nửa giây dù plan của chính câu lệnh chỉ tra theo khóa chính, và khi nào cột tham chiếu cần index riêng?

Cần biết trước: SQL cơ bản, đọc EXPLAIN ANALYZE và lab database. Bài chạy PostgreSQL 18.6 trên macOS arm64 với dữ liệu giả; MySQL chỉ có một phép kiểm về index tự tạo. Docker/Linux của fixture chưa kiểm.

Khóa ngoại kiểm tra theo hai hướng

Khai báo FOREIGN KEY (order_id) REFERENCES orders(id) làm server tự kiểm hai việc mỗi khi dữ liệu đổi:

Tài liệu PostgreSQL 18 nói rõ cả hai vế. Cột được tham chiếu phải là khóa chính hoặc unique nên bên cha luôn có index để tra. Còn xóa hàng cha hoặc đổi cột được tham chiếu buộc server phải quét bảng con để tìm hàng khớp giá trị cũ, nên thường nên đánh index cột tham chiếu; vì việc đó không phải lúc nào cũng cần và có nhiều cách đánh index, khai báo khóa ngoại không tự tạo nó. Bài này đo hậu quả của vế cuối.

Khóa ngoại được hiện thực bằng trigger hệ thống. Lab dưới đây thêm một khóa ngoại có ON DELETE CASCADE rồi liệt kê các trigger nó sinh ra; mỗi lượt đo đều nằm trong giao dịch và rollback nên dữ liệu không đổi.

Tạo thư mục sạch, chép bốn file của cách A từ bài lab (lab-common.sh, lab-local.sh, seed-pg.sql, seed-mysql.sql) rồi dựng lab:

. ./lab-local.sh
lab_up
lab_seed
lab_whoami

BEGIN;
ALTER TABLE wiki_lab.order_items
  ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id)
  REFERENCES wiki_lab.orders(id) ON DELETE CASCADE;
SELECT tgrelid::regclass AS bang,
       regexp_replace(pg_get_triggerdef(oid), '^CREATE CONSTRAINT TRIGGER "[^"]+" ', '') AS dinh_nghia
FROM pg_trigger
WHERE tgconstraint <> 0
  AND tgrelid IN ('wiki_lab.orders'::regclass, 'wiki_lab.order_items'::regclass)
ORDER BY 1, 2;
ROLLBACK;

. ./lab-local.sh
lab_psql -At -F ' | ' -f triggers.sql

Output thật ở lượt kiểm ngày 2026-10-04:

wiki_lab.orders | AFTER DELETE ON wiki_lab.orders FROM wiki_lab.order_items NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_cascade_del"()
wiki_lab.orders | AFTER UPDATE ON wiki_lab.orders FROM wiki_lab.order_items NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_noaction_upd"()
wiki_lab.order_items | AFTER INSERT ON wiki_lab.order_items FROM wiki_lab.orders NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()
wiki_lab.order_items | AFTER UPDATE ON wiki_lab.order_items FROM wiki_lab.orders NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_upd"()

Bốn trigger AFTER ... FOR EACH ROW, hai ở mỗi bảng, ứng với hai hướng kiểm tra trong bảng ở trên. Chúng chạy sau khi plan của câu lệnh đã xong, và đó là lý do chi phí của chúng khó thấy.

Chi phí không nằm trong plan của câu lệnh

Xóa 100 đơn đầu của fixture. Bản thứ nhất để order_items.order_id không có index, bản thứ hai tạo index ngay trước khi thêm khóa ngoại:

BEGIN;
ALTER TABLE wiki_lab.order_items
  ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id)
  REFERENCES wiki_lab.orders(id) ON DELETE CASCADE;
EXPLAIN (ANALYZE, COSTS OFF) DELETE FROM wiki_lab.orders WHERE id <= 100;
ROLLBACK;

BEGIN;
CREATE INDEX order_items_order_id ON wiki_lab.order_items(order_id);
ALTER TABLE wiki_lab.order_items
  ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id)
  REFERENCES wiki_lab.orders(id) ON DELETE CASCADE;
EXPLAIN (ANALYZE, COSTS OFF) DELETE FROM wiki_lab.orders WHERE id <= 100;
ROLLBACK;

. ./lab-local.sh
echo '== không index ở bảng con'
lab_psql -At -f delete-no-index.sql
echo '== có index ở bảng con'
lab_psql -At -f delete-index.sql

== không index ở bảng con
Delete on orders (actual time=0.034..0.034 rows=0.00 loops=1)
  ->  Index Scan using orders_pkey on orders (actual time=0.004..0.009 rows=100.00 loops=1)
        Index Cond: (id <= 100)
        Index Searches: 1
Trigger for constraint fk_items_order: time=645.067 calls=100
Execution Time: 645.221 ms
== có index ở bảng con
Delete on orders (actual time=0.078..0.078 rows=0.00 loops=1)
  ->  Index Scan using orders_pkey on orders (actual time=0.003..0.008 rows=100.00 loops=1)
        Index Cond: (id <= 100)
        Index Searches: 1
Trigger for constraint fk_items_order: time=0.329 calls=100
Execution Time: 0.461 ms

Hai plan của DELETE giống hệt nhau và đều rất nhanh: node Delete mất vài chục micro giây để tìm 100 hàng theo khóa chính. Toàn bộ chênh lệch nằm ở dòng Trigger for constraint: 645 ms so với 0,3 ms cho đúng 100 lần gọi. Mỗi hàng cha bị xóa gọi một câu DELETE trên order_items để cascade; khi bảng con có 300.000 hàng và không có index, mỗi câu phải quét cả bảng. 645 ms chia cho 100 hàng là khoảng 6,4 ms mỗi hàng cha, gần với thời gian một lần quét tuần tự bảng đó ở mục skip scan bên dưới (7,3 ms).

Tài liệu PostgreSQL giải thích vì sao dòng này tách khỏi node: trigger AFTER chạy sau khi cả plan xong nên thời gian của chúng không nằm trong node Delete; EXPLAIN ANALYZE in tổng thời gian mỗi trigger thành dòng riêng. Cũng theo tài liệu, trigger hoãn (deferred) chỉ chạy lúc cuối giao dịch nên EXPLAIN ANALYZE không đo chúng. Hai hệ quả thực tế: nhìn plan của câu lệnh hoặc node chậm nhất sẽ không thấy nguyên nhân, và khóa ngoại khai báo DEFERRABLE INITIALLY DEFERRED giấu chi phí kiểm ra khỏi EXPLAIN ANALYZE.

Một lượt đo có thể là may rủi, nên lab chạy mỗi bản ba lần và đòi lượt nhanh nhất của bản không index chậm hơn lượt chậm nhất của bản có index ít nhất 20 lần. Ngưỡng này là kiểm tra chống đo hỏng, không phải lời hứa về tốc độ trên máy khác:

. ./lab-local.sh
for run in 1 2 3; do
  lab_psql -At -f delete-no-index.sql | grep '^Trigger for constraint' | sed 's/^/không index: /'
  lab_psql -At -f delete-index.sql | grep '^Trigger for constraint' | sed 's/^/có index: /'
done | tee delete.txt
awk -F'time=' '
  /^không index/ { split($2, a, " "); if (lo == "" || a[1] + 0 < lo + 0) lo = a[1] }
  /^có index/ { split($2, b, " "); if (b[1] + 0 > hi + 0) hi = b[1] }
  END {
    printf "nhanh nhất không index %.3f ms, chậm nhất có index %.3f ms\n", lo, hi
    exit !(lo + 0 > 20 * hi)
  }' delete.txt

không index: Trigger for constraint fk_items_order: time=640.512 calls=100
có index: Trigger for constraint fk_items_order: time=0.379 calls=100
nhanh nhất không index 639.563 ms, chậm nhất có index 0.379 ms

Index ở cột con cũng có giá

Chiều ngược lại: chèn 20.000 dòng vào order_items trong ba cấu hình, không khóa ngoại, có khóa ngoại, có khóa ngoại kèm index cột con. Mỗi dòng mới phải được kiểm là có hàng cha.

\echo == không khóa ngoại
BEGIN;
EXPLAIN (ANALYZE, COSTS OFF)
INSERT INTO wiki_lab.order_items
SELECT g, (g % 100000) + 1, 'sku-x', 1, 100 FROM generate_series(300001, 320000) AS g;
ROLLBACK;
\echo == có khóa ngoại
BEGIN;
ALTER TABLE wiki_lab.order_items
  ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id);
EXPLAIN (ANALYZE, COSTS OFF)
INSERT INTO wiki_lab.order_items
SELECT g, (g % 100000) + 1, 'sku-x', 1, 100 FROM generate_series(300001, 320000) AS g;
ROLLBACK;
\echo == có khóa ngoại và index cột con
BEGIN;
CREATE INDEX order_items_order_id ON wiki_lab.order_items(order_id);
ALTER TABLE wiki_lab.order_items
  ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id);
EXPLAIN (ANALYZE, COSTS OFF)
INSERT INTO wiki_lab.order_items
SELECT g, (g % 100000) + 1, 'sku-x', 1, 100 FROM generate_series(300001, 320000) AS g;
ROLLBACK;

. ./lab-local.sh
lab_psql -At -f insert.sql | grep -E '^==|^Insert on|^Trigger for constraint|^Execution Time'

== không khóa ngoại
Insert on order_items (actual time=17.177..17.177 rows=0.00 loops=1)
Execution Time: 17.195 ms
== có khóa ngoại
Insert on order_items (actual time=18.622..18.622 rows=0.00 loops=1)
Trigger for constraint fk_items_order: time=35.984 calls=20000
Execution Time: 55.062 ms
== có khóa ngoại và index cột con
Insert on order_items (actual time=39.895..39.895 rows=0.00 loops=1)
Trigger for constraint fk_items_order: time=37.356 calls=20000
Execution Time: 77.718 ms

Có hai khoản chi phí ghi, và chúng khác nhau về bản chất. Khóa ngoại thêm 20.000 lần kiểm tra cha, tốn khoảng 36 ms vì mỗi lần là một lần tra theo khóa chính: rẻ, nhưng không phải bằng không (tổng thời gian từ 17 lên 55 ms trong lượt này). Index ở cột con không làm trigger chậm đi mà làm node Insert chậm hơn (từ 18,6 lên 39,9 ms) vì mỗi dòng phải được ghi thêm vào index. Đây là giá thật của index, trả ở mọi lần ghi vào bảng con.

So hai bảng đo: khoản 21 ms thêm cho 20.000 dòng chèn là cái giá để mỗi hàng cha bị xóa từ khoảng 6,4 ms xuống khoảng 3 micro giây (645 ms và 0,33 ms cho 100 hàng ở mục trên). Giá đó đáng trả khi bảng cha có xóa hoặc đổi khóa, hoặc khi cột con xuất hiện trong join và điều kiện lọc; nó không đáng khi bảng cha chỉ thêm, cột con không bao giờ được tra và bảng con ghi rất nhiều.

Cột khóa ngoại nên đứng đâu trong index

Lời khuyên quen thuộc là đặt cột khóa ngoại đầu tiên. Với PostgreSQL 18 điều đó không còn là điều kiện cần, vì skip scan cho phép dùng index nhiều cột khi cột đầu có ít giá trị. Câu hỏi thực tế là kiểm tra của khóa ngoại tra theo order_id = giá trị rẻ đến đâu với từng kiểu index. Lab mô phỏng đúng câu tra đó (SELECT 1 FROM ONLY order_items x WHERE order_id = 12345 FOR KEY SHARE OF x) và đếm số lần tìm (Index Searches):

\echo == index (qty, order_id): cột đầu có 3 giá trị
BEGIN;
CREATE INDEX ix_qty_order ON wiki_lab.order_items(qty, order_id);
ANALYZE wiki_lab.order_items;
EXPLAIN (ANALYZE, COSTS OFF) SELECT 1 FROM ONLY wiki_lab.order_items x WHERE x.order_id = 12345 FOR KEY SHARE OF x;
ROLLBACK;
\echo == index (sku, order_id): cột đầu có 500 giá trị
BEGIN;
CREATE INDEX ix_sku_order ON wiki_lab.order_items(sku, order_id);
ANALYZE wiki_lab.order_items;
EXPLAIN (ANALYZE, COSTS OFF) SELECT 1 FROM ONLY wiki_lab.order_items x WHERE x.order_id = 12345 FOR KEY SHARE OF x;
ROLLBACK;
\echo == index (order_id)
BEGIN;
CREATE INDEX ix_order ON wiki_lab.order_items(order_id);
ANALYZE wiki_lab.order_items;
EXPLAIN (ANALYZE, COSTS OFF) SELECT 1 FROM ONLY wiki_lab.order_items x WHERE x.order_id = 12345 FOR KEY SHARE OF x;
ROLLBACK;
\echo == không index, quét tuần tự không song song như trigger
BEGIN;
SET LOCAL max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, COSTS OFF) SELECT 1 FROM ONLY wiki_lab.order_items x WHERE x.order_id = 12345 FOR KEY SHARE OF x;
ROLLBACK;

. ./lab-local.sh
lab_psql -At -f skip.sql | grep -E '^==|Index Searches'

== index (qty, order_id): cột đầu có 3 giá trị
        Index Searches: 5
== index (sku, order_id): cột đầu có 500 giá trị
        Index Searches: 643
== index (order_id)
        Index Searches: 1
== không index, quét tuần tự không song song như trigger

. ./lab-local.sh
lab_psql -At -f skip.sql | grep -E '^==|Index Scan|Seq Scan|^Execution Time'

== index (qty, order_id): cột đầu có 3 giá trị
  ->  Index Scan using ix_qty_order on order_items x (actual time=0.017..0.031 rows=3.00 loops=1)
Execution Time: 0.046 ms
== index (sku, order_id): cột đầu có 500 giá trị
  ->  Index Scan using ix_sku_order on order_items x (actual time=0.382..1.847 rows=3.00 loops=1)
Execution Time: 1.857 ms
== index (order_id)
  ->  Index Scan using ix_order on order_items x (actual time=0.009..0.011 rows=3.00 loops=1)
Execution Time: 0.017 ms
== không index, quét tuần tự không song song như trigger
  ->  Seq Scan on order_items x (actual time=0.317..7.317 rows=3.00 loops=1)
Execution Time: 7.341 ms

Index chỉ có order_id cần một lần tìm và 0,017 ms. Index (qty, order_id) vẫn dùng được nhờ skip scan: cột đầu chỉ có ba giá trị nên planner lặp phép tìm theo từng giá trị, tổng cộng 5 lần tìm và 0,046 ms. Index (sku, order_id) có 500 giá trị ở cột đầu cần 643 lần tìm, chậm hơn chừng trăm lần index đúng, nhưng trong lượt đo này vẫn nhanh hơn quét tuần tự bảng 300.000 hàng (1,9 so với 7,3 ms). Số lần tìm do executor quyết định, không suy ra được chỉ từ số giá trị khác biệt của cột đầu. Tài liệu PostgreSQL 18 nói skip scan hiệu quả khi cột đầu có ít giá trị, còn khi có nhiều thì thường planner chọn quét tuần tự. Ở lượt đo này planner vẫn chọn index với 500 giá trị đầu, nên đừng coi “nhiều giá trị thì chắc chắn quét bảng” là quy tắc cứng; hãy đo với dữ liệu của bạn.

Quy tắc dùng được: đặt cột khóa ngoại đầu tiên, trừ khi index đó chủ yếu phục vụ truy vấn khác và cột đầu có rất ít giá trị. Quy tắc này không áp dụng nguyên cho MySQL: tài liệu MySQL 26.7 yêu cầu cột khóa ngoại là các cột đầu của index theo cùng thứ tự, và bài này không đo skip scan ở đó.

Khóa ngoại và khóa hàng

Kiểm tra phía con không chỉ đọc hàng cha: nó khóa hàng đó ở mức FOR KEY SHARE. Tài liệu PostgreSQL mô tả mức này yếu nhất trong bốn mức khóa hàng: nó chặn DELETE và mọi UPDATE đổi giá trị khóa, nhưng không chặn UPDATE khác, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE hay SELECT FOR KEY SHARE.

Lab dưới đây dựng hai phiên. Phiên giữ chèn một dòng con rồi ngủ 8 giây trong giao dịch chưa commit; trong lúc đó phiên kiểm xem bảng khóa ở mức bảng, rồi thử ba thao tác trên hàng cha:

. ./lab-local.sh
lab_seed
lab_psql -c 'CREATE INDEX order_items_order_id ON wiki_lab.order_items(order_id)'
lab_psql -c 'ALTER TABLE wiki_lab.order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id)'
( lab_psql -At -c "BEGIN; INSERT INTO wiki_lab.order_items VALUES (900001, 1, 'sku-x', 1, 100); SELECT pg_sleep(8); COMMIT;" > holder.log 2>&1 ) &
holder=$!
i=0
until [ "$(lab_psql -At -c "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'PgSleep'")" = 1 ]; do
  i=$((i + 1))
  [ "$i" -lt 100 ] || { echo 'phiên giữ không tới điểm chờ' >&2; exit 1; }
  sleep 0.1
done
echo '== khóa mức bảng của phiên đang chèn dòng con'
modes=$(lab_psql -At -F ' ' -c "SELECT c.relname, l.mode FROM pg_locks l JOIN pg_stat_activity a USING (pid) JOIN pg_class c ON c.oid = l.relation WHERE a.wait_event = 'PgSleep' AND l.locktype = 'relation' AND c.relname IN ('orders', 'order_items') ORDER BY 1, 2")
echo "$modes"
[ "$(echo "$modes" | grep -cvE ' (RowShareLock|RowExclusiveLock)$')" = 0 ] || { echo 'có khóa mạnh hơn ROW EXCLUSIVE' >&2; exit 1; }
echo '== SELECT FOR UPDATE NOWAIT trên hàng cha'
lab_psql -At -c "SELECT id FROM wiki_lab.orders WHERE id = 1 FOR UPDATE NOWAIT" 2>&1 || true
echo '== UPDATE cột không phải khóa của hàng cha'
lab_psql -At -c "SET lock_timeout = '1s'; UPDATE wiki_lab.orders SET status = 'paid' WHERE id = 1" 2>&1 && echo 'update xong, không phải chờ'
echo '== DELETE hàng cha'
lab_psql -At -c "SET lock_timeout = '500ms'; DELETE FROM wiki_lab.orders WHERE id = 1" 2>&1 || true
wait "$holder"
cat holder.log

bash twosessions.sh

== khóa mức bảng của phiên đang chèn dòng con
order_items RowExclusiveLock
orders RowShareLock
== SELECT FOR UPDATE NOWAIT trên hàng cha
ERROR:  could not obtain lock on row in relation "orders"
== UPDATE cột không phải khóa của hàng cha
update xong, không phải chờ
== DELETE hàng cha
ERROR:  canceling statement due to lock timeout
CONTEXT:  while deleting tuple (735,42) in relation "orders"

Phiên chèn dòng con chỉ giữ RowExclusiveLock trên bảng con và RowShareLock trên bảng cha ở mức bảng. Ở mức hàng, việc kiểm khóa ngoại chặn đúng những gì bảng trên nói: FOR UPDATE bị từ chối ngay, DELETE chờ tới hết lock_timeout rồi bị hủy, còn UPDATE không đổi khóa đi qua không chờ. Vì vậy khóa ngoại không làm mọi thay đổi hàng cha phải xếp hàng, chỉ những thay đổi xóa hoặc đổi khóa.

Hướng xóa hàng cha cũng nên xem khóa mức bảng. Lab dưới đây reset dữ liệu và thêm khóa ngoại không index ngoài giao dịch đo (vì chính lệnh ALTER TABLE ... ADD FOREIGN KEY giữ khóa mạnh hơn và sẽ làm lẫn kết quả), rồi xóa một hàng cha mới (chưa có dòng con) trong giao dịch và liệt kê khóa của chính phiên đó trước khi rollback. Bước kiểm dừng lab nếu thấy bất kỳ mode nào ngoài RowShareLock và RowExclusiveLock:

BEGIN;
INSERT INTO wiki_lab.orders VALUES (900001, 1, 'pending', 100, timestamp '2026-01-01');
DELETE FROM wiki_lab.orders WHERE id = 900001;
SELECT c.relname, l.mode
FROM pg_locks l JOIN pg_class c ON c.oid = l.relation
WHERE l.pid = pg_backend_pid() AND l.locktype = 'relation'
  AND c.relname IN ('orders', 'order_items')
ORDER BY 1, 2;
ROLLBACK;

. ./lab-local.sh
lab_seed
lab_psql -c 'ALTER TABLE wiki_lab.order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id)'
lab_psql -At -F ' ' -f locks-delete.sql | tee locks-delete.txt
if grep -qvE ' (RowShareLock|RowExclusiveLock)$' locks-delete.txt; then
  echo 'có khóa mạnh hơn ROW EXCLUSIVE' >&2
  exit 1
fi
echo 'chỉ có ROW SHARE và ROW EXCLUSIVE'

order_items RowShareLock
orders RowExclusiveLock
orders RowShareLock
chỉ có ROW SHARE và ROW EXCLUSIVE

Ở hướng xóa cha, hai bảng cũng chỉ giữ RowShareLock và RowExclusiveLock; không có mode nào mạnh hơn ROW EXCLUSIVE. Tài liệu PostgreSQL ghi thêm rằng server không nhớ thông tin hàng đã khóa trong bộ nhớ nên không giới hạn số hàng khóa cùng lúc. Các số đo ở cả hai hướng không cho thấy khóa bảng mạnh nào. Bài này không kiểm engine khác, nên không nói gì về việc ở đó khóa ngoại thiếu index có dẫn tới khóa cả bảng hay không; với PostgreSQL, hậu quả đo được của thiếu index con là chi phí quét lặp ở phía cha, không phải khóa bảng.

Tìm khóa ngoại chưa có index ở cột con

Truy vấn dưới đây liệt kê khóa ngoại không có index hợp lệ nào bắt đầu bằng đúng các cột của nó (so sánh theo tập, không theo thứ tự). Nó là truy vấn bảo thủ: index (qty, order_id) ở mục trước vẫn dùng được nhờ skip scan nhưng bị liệt kê, vì cột khóa ngoại không đứng đầu. Dùng kết quả như danh sách để xem xét, không phải bản án.

SELECT c.conrelid::regclass AS bang_con, c.conname
FROM pg_constraint c
WHERE c.contype = 'f'
  AND c.connamespace = 'wiki_lab'::regnamespace
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND i.indisvalid
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] @> c.conkey
      AND c.conkey @> (i.indkey::int2[])[0:cardinality(c.conkey) - 1]
  );

. ./lab-local.sh
lab_seed
lab_psql -c 'ALTER TABLE wiki_lab.order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id)'
echo '== chưa có index'
lab_psql -At -F ' ' -f missing.sql
lab_psql -c 'CREATE INDEX order_items_order_id ON wiki_lab.order_items(order_id)'
echo '== có index (order_id)'
lab_psql -At -F ' ' -f missing.sql
lab_psql -c 'DROP INDEX wiki_lab.order_items_order_id'
lab_psql -c 'CREATE INDEX ix_qty_order ON wiki_lab.order_items(qty, order_id)'
echo '== chỉ có index (qty, order_id)'
lab_psql -At -F ' ' -f missing.sql

== chưa có index
wiki_lab.order_items fk_items_order
== có index (order_id)
== chỉ có index (qty, order_id)
wiki_lab.order_items fk_items_order

MySQL InnoDB: index tự tạo

MySQL giải quyết vấn đề ở trên theo hướng ngược lại. Tài liệu MySQL 26.7 ghi rằng MySQL yêu cầu index trên khóa ngoại và khóa được tham chiếu để kiểm tra khóa ngoại nhanh, không quét bảng; ở bảng tham chiếu phải có index với các cột khóa ngoại đứng đầu theo cùng thứ tự, và index đó được tạo tự động nếu chưa có. Cũng theo tài liệu, index này có thể bị gỡ lặng lẽ về sau nếu bạn tạo index khác dùng được cho ràng buộc.

. ./lab-local.sh
echo '== trước khi thêm khóa ngoại'
lab_mysql -N -e "SELECT index_name, column_name FROM information_schema.statistics WHERE table_schema = 'wiki_lab' AND table_name = 'order_items' ORDER BY 1, seq_in_index"
lab_mysql -e "ALTER TABLE wiki_lab.order_items ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES wiki_lab.orders(id)"
echo '== sau khi thêm khóa ngoại'
lab_mysql -N -e "SELECT index_name, column_name FROM information_schema.statistics WHERE table_schema = 'wiki_lab' AND table_name = 'order_items' ORDER BY 1, seq_in_index"

== trước khi thêm khóa ngoại
PRIMARY id
== sau khi thêm khóa ngoại
fk_items_order order_id
PRIMARY id

Vì lab của bài chạy hai engine từ cùng seed không có khóa ngoại, đây là chỗ hai engine bắt đầu khác nhau ngay khi bạn thêm ràng buộc. Phép kiểm này chỉ xác nhận index được tạo; chi phí xóa cha, cascade và chèn con ở MySQL chưa được đo trong bài, và index tự tạo vẫn trả giá ghi như index tự tạo bằng tay.

Quyết định cho từng khóa ngoại

1. Hỏi ba câu: hàng cha có bị xóa hoặc đổi khóa không (kể cả qua CASCADE)? Cột con có xuất hiện trong join hoặc điều kiện lọc không? Bảng con đủ lớn để một lần quét tốn thời gian đo được không? Nếu một trong ba là có, tạo index bắt đầu bằng cột khóa ngoại.
2. Không thêm index khi bảng cha chỉ thêm và không bao giờ xóa hay đổi khóa, cột con không dùng để tra, và bảng con chịu ghi nặng: index là chi phí ghi thật (21 ms thêm cho 20.000 dòng ở lab).
3. Coi ON DELETE CASCADE là một câu DELETE trên bảng con cho mỗi hàng cha. Xóa hàng loạt hàng cha mà bảng con không có index tốn thời gian tỉ lệ với số hàng cha nhân với kích thước bảng con.
4. Khi DELETE hoặc cascade chậm mà plan nhanh, đọc các dòng Trigger for constraint trong EXPLAIN (ANALYZE) trước khi tìm nguyên nhân ở chỗ khác, và nhớ rằng trigger hoãn không hiện ở đó.
5. Rà định kỳ bằng truy vấn catalog ở trên và xem từng dòng kết quả.

Giới hạn và lỗi thường gặp

• Số đo thuộc PostgreSQL 18.6 và MySQL 26.7 trên một máy macOS arm64, một fixture. Lab đòi tỉ lệ tối thiểu 20 lần, không đòi con số tuyệt đối; hãy tự đo trên dữ liệu và phần cứng của bạn.
• Chưa đo: tác dụng của index cột con lên join và điều kiện lọc (bài chỉ đo chi phí của chính khóa ngoại), khóa ngoại trên bảng phân vùng, DEFERRABLE và ON UPDATE, MATCH FULL, hiệu năng nạp dữ liệu lớn khi khóa ngoại đang bật, hành vi nhiều engine khác, và so sánh với việc kiểm tra tính toàn vẹn ở ứng dụng.
• Truy vấn mô phỏng ở mục skip scan là câu tra tương đương, không phải câu trigger thực sự chạy; số lần tìm của trigger có thể khác nếu server dùng đường đi khác.
• EXPLAIN ANALYZE thực sự chạy câu lệnh. Lab đặt mọi thứ trong BEGIN ... ROLLBACK để không đổi dữ liệu; không làm vậy trên database thật.
• Lỗi thường gặp: tạo index sau khi cascade đã chậm rồi kết luận “khóa ngoại làm chậm” chỉ vì thiếu một index; đo hai lượt với cache khác nhau và so nhau; đặt khóa ngoại và index trong cùng migration mà không kiểm thời gian khóa bảng khi chạy trên dữ liệu thật.

Dọn

. ./lab-local.sh
lab_clean
test ! -e lab.env

Học tiếp và nguồn

• Đọc EXPLAIN ANALYZE: cách đọc node, loops và dòng trigger.
• Composite index: thứ tự cột và skip scan của PostgreSQL 18.
• Lab database dùng chung: dữ liệu, reset và dấu nhận diện của lab.
• PostgreSQL 18, Constraints, phần Foreign Keys: hành vi ON DELETE và việc không tự tạo index cột tham chiếu.
• PostgreSQL 18, Explicit Locking, khóa hàng: bốn mức khóa hàng và bảng xung đột.
• PostgreSQL 18, Using EXPLAIN: thời gian trigger và Index Searches.
• PostgreSQL 18, Multicolumn Indexes: phạm vi scan và skip scan.
• MySQL 26.7, FOREIGN KEY Constraints: yêu cầu index, index tự tạo, bảng phân vùng.

Nguồn chính thức đọc ngày 2026-10-04; số đo trong bài thuộc PostgreSQL 18.6 và MySQL 26.7 trên macOS arm64, không có nghiệm thu Docker/Linux.