SQLite — dữ liệu, JOIN, chỉ mục

SQLite: dữ liệu thật, trong một file

Người ship dành nhiều thời gian hiểu dữ liệu, không kém gì code.

Trong file .db thực sự là gì

File không phải byte ngẫu nhiên — nó được tổ chức để nhanh.

Bảng, dòng, cột — và schema

Từ vựng giống bảng tính, nhưng có cấu trúc — định nghĩa một lần, dùng ở khắp nơi.

Ví dụ xuyên suốt: một cửa hàng nhỏ

-- shop.db — the whole database is this one file
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,  -- unique per user
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER,            -- -> users.id  (foreign key)
    total   REAL,
    status  TEXT
);

Chúng ta dùng cửa hàng này (users + orders + line_items) ở gần như mọi ví dụ bên dưới. Chú ý đường liên kết đã xuất hiện: orders.user_id trỏ tới users.id.

Năm động từ — CREATE + INSERT

CREATE TABLE users (id INTEGER PRIMARY KEY,
                    name TEXT, email TEXT);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ana', 'ana@ex.com'),
  (2, 'Bo',  'bo@ex.com'),
  (3, 'Cyd', 'cyd@ex.com'),
  (4, 'Dee', 'dee@ex.com');

Tạo ra → thêm vào. INSERT thêm dòng vào bảng — mỗi dòng là một bản ghi hoàn chỉnh.

Năm động từ — SELECT, UPDATE, DELETE

SELECT name, email FROM users
WHERE id = 1;

UPDATE users SET name = 'Anna'
WHERE id = 1;

DELETE FROM users WHERE id = 4;

Đọc ra → sửa → xóa. Vòng lặp CREATE → INSERT → SELECT → UPDATE → DELETE là 95% SQL hằng ngày của ứng dụng.

Chọn kiểu cho mỗi cột

SQLite khá thoáng ('type affinity' — tương thích kiểu): không than vãn nếu bạn lưu kiểu lạ. Nhưng hãy chọn kiểu hợp lý: so sánh, sắp xếp và chỉ mục sẽ hoạt động như mong đợi.

Quy tắc mà CSDL tự thực thi

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,     -- auto id
    name  TEXT NOT NULL,           -- may not be empty
    email TEXT NOT NULL UNIQUE,    -- one email, ever
    age   INTEGER DEFAULT 30,      -- default when omitted
    vip   INTEGER NOT NULL DEFAULT 0
           CHECK (vip IN (0, 1))  -- only 0 or 1
);

Ràng buộc là hàng rào an toàn: chính CSDL từ chối dòng sai, nên ứng dụng không thể làm hỏng dữ liệu. Một lúc nữa bạn sẽ thấy các lần từ chối.

Thêm dòng, đọc lại

Chính năm dòng sẽ dùng trong các ví dụ tiếp theo.

Đọc dữ liệu: ba dạng SELECT

-- every row, every column
SELECT * FROM users;
-- only the columns you need
SELECT name, email FROM users;
-- cap the number of rows
SELECT name FROM users LIMIT 2;

SELECT là động lực chính — phần lớn traffic của app là đọc. * tiện khi dùng CLI; trong code app, hãy liệt kê cột cần.

Lọc dữ liệu: các toán tử

WHERE là nơi diễn ra việc lọc — và (Phần 4) nơi chỉ mục khiến nó nhanh.

WHERE trên dữ liệu shop

-- 4 users: Ana, Bo, Cyd, Dee
-- 4 orders: 10 (u1, 120.00, paid)  11 (u1, 45.00, shipped)
--           12 (u2, 80.00, paid)   13 (u4, 9.50, pending)

SELECT name FROM users
WHERE name = 'Bo';

SELECT * FROM orders
WHERE total > 50;

Đọc mỗi truy vấn như một câu: 'cho tôi những dòng thỏa điều kiện'. Kết quả chỉ là các dòng khớp.

LIKE: mẫu có ký tự đặc biệt

Mỗi dòng tô sáng = một sản phẩm khớp mẫu đó (trên cả 5 sản phẩm: keyboard, mouse, usb-c hub, monitor, adapter). % = chuỗi bất kỳ (kể cả rỗng) · _ = đúng một ký tự — cách làm truy vấn 'ô tìm kiếm'.

Kết hợp điều kiện: AND, OR, IN

SELECT * FROM orders
WHERE status = 'paid' AND total > 100;

SELECT name FROM users
WHERE name = 'Bo' OR name = 'Dee';

SELECT * FROM orders
WHERE status IN ('paid','shipped');

AND = chặt hơn (ít kết quả hơn), OR = rộng hơn. IN chỉ là cách viết gọn của chuỗi = OR = OR ...

Khóa chính, khóa ngoại, UNIQUE

Khóa là thứ khiến các dòng đáng tin cậy và các bảng liên kết được.

Xem CSDL nói 'không'

Mọi thao tác ghi sai bên dưới đều bị SQLite từ chối — ứng dụng không bao giờ thấy dòng đó.

ORDER BY và LIMIT

-- biggest first
SELECT * FROM orders
ORDER BY total DESC;

-- first two, alphabetically
SELECT name FROM users
ORDER BY name LIMIT 2;

-- result: Ana, Bo

ORDER BY sắp xếp (mặc định ASC tăng dần, DESC giảm dần); LIMIT giới hạn số dòng. Kết hợp thành 'top 10', '20 mới nhất', phân trang.

Hàm tổng hợp: COUNT, SUM — và GROUP BY

-- how many orders total?
SELECT COUNT(*) FROM orders;   -- 4

-- per user: how many orders, how much spent?
SELECT user_id, COUNT(*) AS n, SUM(total) AS spent
FROM orders
GROUP BY user_id;

GROUP BY gom các dòng thành một dòng cho mỗi nhóm, rồi hàm tổng hợp tóm tắt từng nhóm. Cyd (id 3) không có đơn nên không có nhóm.

Code Flask/Python của bạn chỉ là lớp bọc truy vấn

import sqlite3

conn = sqlite3.connect("shop.db")
row  = conn.execute("SELECT name FROM users WHERE id = ?", (1,))
print(row.fetchone())      # ('Ana',)

sql  = "SELECT name, total FROM orders WHERE user_id =?"
rows = conn.execute(sql, (1,)).fetchall()
print(rows)                # [('Ana', 120.0), ('Ana', 45.0)]

connect → execute(truy vấn, giá trị) → fetch. SQL bên trong chính là những gì bạn đã đọc — ứng dụng chỉ là người đưa thư.

Cửa sổ hằng ngày vào dữ liệu

Một chương trình terminal nhỏ: mở file, xem, truy vấn, thoát.

Một bảng to vs. các bảng liên quan

Cách bạn chia dữ liệu là quyết định thiết kế quan trọng nhất — kiến trúc, chứ không phải cú pháp.

Chỉ hai hình dạng: 1:nhiều và nhiều:nhiều

Gần như mọi mối quan hệ bạn mô hình hóa đều thuộc hai dạng này.

Cửa hàng của bạn, nhìn tổng quan

Mỗi đơn thuộc về một người dùng; mỗi đơn có nhiều mục; mỗi mục thuộc về một đơn. Đó là toàn bộ câu chuyện của JOIN.

Đọc như bản đồ: các cột FK là sợi dây buộc các thẻ với nhau — JOIN là cách lấy dữ liệu xuyên qua những sợi dây đó.

Nhiều:nhiều — bảng trung gian

Một sách có nhiều tác giả, một tác giả viết nhiều sách. Bảng book_authors chứa đúng hai khóa ngoại, và biến 'nhiều:nhiều' thành hai quan hệ 1:nhiều.

Mỗi dòng trong book_authors nói: 'tác giả này đồng tác giả sách này'. Không có cột 'list tác giả' nào ở trong books.

Cách thiết kế mô hình từ đầu

Làm điều này trước khi viết SQL — đây là phần việc của kiến trúc sư.

1. 1

   Tìm các danh từ

   'Người dùng đặt đơn chứa các mục' → users, orders, line_items. Danh từ thành bảng.

2. 2

   Một bảng cho mỗi danh từ

   Mỗi bảng = một thứ; các cột = thuộc tính của thứ đó (bạn cần biết gì về nó?).

3. 3

   Liên kết chúng

   1:nhiều → FK ở phía 'nhiều'. Nhiều:nhiều → thêm bảng trung gian với hai FK.

4. 4

   Chọn các khóa

   PK cho mọi bảng; FK cho cột liên kết; UNIQUE cho giá trị không được lặp (email).

5. 5

   Viết ra

   Viết CREATE TABLE kèm sơ đồ. Sơ đồ là thứ bạn cho người khác (và AI) xem để thống nhất mô hình.

JOIN thực sự làm gì

Một cơ chế, ba hệ quả.

INNER JOIN, từng bước

SELECT u.name, o.total, o.status
FROM   users u
JOIN   orders o ON o.user_id = u.id
ORDER BY o.id;

Đọc là: 'mỗi đơn hàng, tìm người dùng và đặt tên cạnh total'. Cyd biến mất vì INNER JOIN chỉ giữ các dòng khớp.

INNER vs LEFT JOIN — thấy sự khác nhau

SELECT u.name, o.total
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id;

Dòng Cyd vẫn hiện với NULL — 'người dùng này không có đơn'. RIGHT và FULL JOIN là hai hướng còn lại; mặc định (INNER) = chỉ dòng khớp.

Nối tiếp ba bảng

SELECT o.id AS order_id, li.product, li.qty
FROM   orders o
JOIN   line_items li ON li.order_id = o.id
ORDER BY o.id, li.id;

Mỗi JOIN thêm cột của bảng kế tiếp, liên kết bằng FK của nó. Mẫu 'bảng → con → cháu' này là hình dạng của hầu hết các báo cáo.

JOIN + WHERE: 'Anna mua gì?'

SELECT li.product, li.qty
FROM   users u
JOIN   orders o    ON o.user_id   = u.id
JOIN   line_items li ON li.order_id = o.id
WHERE  u.email = 'ana@ex.com';

Thứ tự tư duy: JOIN trước (ghép bảng), sau đó WHERE (thu hẹp). Dạng 'tìm X thuộc về Y' này là truy vấn phổ biến nhất của mọi ứng dụng.

Bẫy thiếu ON: tích Descartes

-- Forgot the ON clause:
SELECT u.name, o.total
FROM   users u, orders o;

Không có khóa để khớp, CSDL ghép mọi dòng với mọi dòng: 4 users × 4 orders = 16 dòng. Nếu kết quả nhiều dòng hơn bình thường — chắc chắn bạn đang thiếu ON.

JOIN nhanh nhờ điều gì

Cơ chế bên trong rất đơn giản — và có thể tối ưu bằng chỉ mục.

Chỉ mục: như trang mục lục của cuốn sách

Tạo bằng một dòng: CREATE INDEX idx_orders_user ON orders(user_id);

Quét vs chỉ mục: 100.000 đơn hàng

-- find all orders of user 1 (100,000 rows total)

-- no index → full scan:
--   read row 1... user_id = 7   ✗
--   read row 2... user_id = 3   ✗
--   ... 100,000 comparisons

-- with index → sorted lookup:
--   index says: user_id 1 → rows 10, 11
--   jump straight to those 2 rows
-- ~100,000 steps  →  ~17 steps

Chỉ mục là danh sách đã sắp xếp, nên SQLite tìm nhị phân (~log2 của 100.000 ≈ 17 bước nhảy) thay vì so sánh từng dòng.

EXPLAIN QUERY PLAN: xem chiến lược thay đổi

SQLite sẽ nói thẳng với bạn: nó quét hay dùng chỉ mục.

Index các cột 'nóng'

-- index columns that appear in WHERE, ORDER BY, and ON:
CREATE INDEX idx_orders_user   ON orders(user_id);
CREATE INDEX idx_users_email   ON users(email);
CREATE INDEX idx_orders_status ON orders(status);

-- don't index:
--  * a column you never query (pure overhead)
--  * every column (each one taxes every write)

Cột FK xứng đáng có chỉ mục dù bạn chỉ dùng chúng trong JOIN — đó là quy tắc Phần 3 trong thực tế.

Chỉ mục tốn gì

Quy tắc: index cái bạn đọc nhiều. Đừng index tất cả — chỉ mục là sự đánh đổi tốc độ đọc/tốc độ ghi.

Một phiên thật trên shop.db

Đếm dòng, hỏi một câu xuyên bảng, xem schema — tất cả từ CLI.

Truy vấn tham số hóa: ? là người bạn của bạn

email = request.args.get('email')          # user input
sql  = "SELECT * FROM users WHERE email = ?"
rows = conn.execute(sql, (email,)).fetchall()

# ? is a placeholder — SQLite receives the query
# AND the value separately. The value is always
# treated as data, never as SQL.

Chỉ một thói quen — không bao giờ dán input của user vào chuỗi SQL — đã ngăn cả một lớp lỗi bảo mật.

SQL injection: lỗi kinh điển

Injection là một trong những lỗi bảo mật phổ biến nhất trong thực tế. Cách dùng ? ở trên là cách sửa chuẩn.

Khi nào SQLite đủ — và khi nào không

Nó là mặc định cho tới khi bạn gặp một bức tường cụ thể.

Bốn tội lỗi kinh điển của mô hình dữ liệu

Bạn sẽ gặp cả bốn thứ này trong các dự án thật.

Vòng lặp shipper với AI trong đội

Bạn sở hữu mô hình; AI viết truy vấn; CLI là công cụ kiểm chứng của bạn.

1. 1

   Mô hình hóa

   Vẽ bảng và mối liên kết (1:nhiều / m:n) trước khi có SQL. Cho người thật xem để kiểm tra.

2. 2

   Soạn SQL

   Mô tả mô hình + câu hỏi cho AI; nó viết SELECT/JOIN. Đọc lại nó so với sơ đồ của bạn.

3. 3

   Kiểm chứng trong CLI

   Mở .db bằng sqlite3, chạy, nhìn các dòng thật. Sai số dòng? Truy vấn hoặc mô hình đang lệch.

4. 4

   Tăng tốc

   Chạy EXPLAIN QUERY PLAN; thấy SCAN ở chỗ cần nhanh, thêm index cho cột nóng.

Từ shop nhỏ tới một site e-commerce thật

users + orders là phần bài tập. Một cửa hàng thật phải xoay xở được với tiền bạc, vận chuyển và thời gian.

Toàn cảnh — commerce cốt lõi

Mỗi bảng = một thứ: người, đơn, mục đơn, sản phẩm, danh mục, địa chỉ, tiền. FK là sợi dây nối; mọi báo cáo chỉ là JOIN xuyên qua chúng.

Vượt ra ngoài bước thanh toán

Giỏ là 'đang nghĩ sẽ mua', tách khỏi 'đã mua'; shipments tách khỏi vòng đời đơn; reviews là mối quan hệ product↔user có dữ liệu đi kèm; coupons phục vụ kế toán khuyến mãi.

Vì sao mỗi bảng cốt lõi tồn tại

Đọc cột thứ ba như một bài kiểm tra: nếu bảng nào không giải thích được vì sao nó tồn tại, hãy xóa — dữ liệu đó có lẽ thuộc về nơi khác.

Vì sao mỗi bảng bổ sung tồn tại

Cùng bài kiểm tra như trên: mỗi bảng xứng đáng tồn tại vì giữ những dữ kiện mà không bảng nào khác nên giữ.

Ba mẫu bạn sẽ dùng lại — và những gì tiếp theo

Nhận ra các hình dạng này trong mọi schema, dù của bạn hay của người khác.

Hai báo cáo trên schema e-commerce

SELECT p.name, SUM(oi.qty * oi.unit_price) AS revenue
FROM   order_items oi
JOIN   products p ON p.id = oi.product_id
JOIN   orders o   ON o.id = oi.order_id
WHERE  o.status IN ('paid','shipped')
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 5;

SELECT o.id, o.total, o.status
FROM   orders o
JOIN   users u ON u.id = o.user_id
WHERE  u.email = 'ana@ex.com'
ORDER BY o.id DESC;

Đúng các kỹ thuật đã học: JOIN chuỗi nhiều bảng, WHERE lọc, GROUP BY tổng hợp, ORDER BY + LIMIT top-N — giờ chỉ khác ở quy mô của mô hình.