SQLite và MySQL khác nhau thế nào? Nên chọn cái nào

Hai hình dạng cơ sở dữ liệu khác nhau

SQLite và MySQL đều dùng SQL và đều lưu hàng trong bảng, nhưng cách chúng nằm trong một hệ thống thì hoàn toàn khác. SQLite là một thư viện: ứng dụng liên kết với nó và đọc từ một tệp trên đĩa. MySQL là một máy chủ: một tiến trình riêng mà bạn kết nối tới qua socket hoặc qua mạng.

Chỉ một khác biệt đó định hình mọi thứ còn lại: cách cài đặt, số tiến trình ghi có thể làm việc cùng lúc, cách sao lưu, cách triển khai. Phần lớn câu hỏi mọi người đặt ra về SQLite và MySQL thực chất là câu hỏi về mô hình nhúng so với mô hình máy khách/máy chủ.

-- SQLite: open a file, you have a database.
sqlite3 app.db

-- MySQL: connect to a running server.
mysql -h localhost -u root -p

Lệnh SQLite mở (hoặc tạo) một tệp. Lệnh MySQL mở kết nối tới một tiến trình phải đang chạy sẵn, đã được cấu hình và đang chấp nhận đăng nhập.

Kiến trúc: nhúng và máy khách/máy chủ

Trong một ứng dụng SQLite, bộ máy cơ sở dữ liệu chạy bên trong chương trình của bạn. Không cổng, không daemon, không systemctl start. Một lời gọi thư viện sqlite3 đọc và ghi các trang dữ liệu trực tiếp từ một tệp trên đĩa.

MySQL thì ngược lại. Máy chủ mysqld giữ dữ liệu, quản lý kết nối, thực thi phân quyền, chạy bộ lập kế hoạch truy vấn và xử lý khóa. Ứng dụng của bạn là một máy khách gửi các chuỗi SQL qua mạng và nhận lại các hàng kết quả.

Hệ quả thực tế:

• Triển khai. SQLite đi kèm ứng dụng: một tệp thực thi, một tệp dữ liệu. MySQL cần một máy chủ riêng để cài đặt, bảo mật, giám sát và sao lưu.
• Truy cập qua mạng. MySQL mở một cổng, nên nhiều máy chủ ứng dụng có thể kết nối tới cùng một cơ sở dữ liệu. SQLite giả định chỉ có một tiến trình (hoặc vài tiến trình phối hợp với nhau) trên cùng một máy.
• Phân quyền. MySQL có người dùng, vai trò và câu lệnh GRANT. Hệ thống phân quyền duy nhất của SQLite là quyền truy cập tệp của hệ điều hành trên tệp cơ sở dữ liệu.

Không hình dạng nào "tốt hơn". Chúng giải quyết những bài toán khác nhau.

Xử lý đồng thời và ghi dữ liệu

Đây là chỗ hai hệ thực sự tách đôi. Engine InnoDB của MySQL khóa ở cấp hàng: nhiều kết nối có thể ghi vào các hàng khác nhau cùng lúc mà không chặn nhau.

SQLite tuần tự hóa việc ghi ở cấp cơ sở dữ liệu. Chỉ một tiến trình ghi tại một thời điểm, không ngoại lệ. Các tiến trình đọc có thể chạy song song với tiến trình ghi (nhất là ở chế độ WAL), nhưng tiến trình ghi thứ hai phải chờ đến lượt.

-- SQLite: this works fine for many readers, one writer at a time.
PRAGMA journal_mode = WAL;

-- MySQL: many writers, fine-grained locking.
-- (No special setup - InnoDB does it by default.)

Với một ứng dụng có một hoặc hai tiến trình ghi ở mức vừa phải, như công cụ desktop, ứng dụng di động hay CMS nhỏ, việc ghi tuần tự của SQLite thường đủ nhanh để bạn không bao giờ nhận ra. Với một dịch vụ web bận rộn có hàng trăm kết nối cùng thêm đơn hàng hoặc cập nhật phiên, khóa cấp hàng của MySQL là khác biệt giữa "ổn" và "mọi thứ xếp hàng sau một ổ khóa".

Kiểu dữ liệu

MySQL có một danh sách kiểu dài và nghiêm ngặt: TINYINT, INT, BIGINT, VARCHAR(n), DATETIME, DECIMAL(p,s), BLOB, JSON và nhiều kiểu khác. Khai báo một cột là INT thì MySQL sẽ từ chối một chuỗi.

SQLite thì dùng type affinity (ái lực kiểu). Kiểu của cột chỉ là gợi ý, không phải quy tắc bắt buộc. Bạn có thể đặt một chuỗi vào cột INTEGER và SQLite vẫn lưu nó (trừ khi bạn chọn dùng bảng STRICT, có từ phiên bản 3.37).

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
);

INSERT INTO products (name, price) VALUES ('Coffee', 3.50);
INSERT INTO products (name, price) VALUES ('Tea', '2.75');  -- string in REAL column

SELECT id, name, price, typeof(price) FROM products;

Cả hai hàng đều được thêm thành công. Sự linh hoạt này tiện khi làm prototype, nhưng gây bất ngờ khi bạn trông đợi cơ sở dữ liệu bảo đảm an toàn kiểu. Hãy dùng bảng STRICT khi bạn muốn SQLite kiểm tra kiểu chặt chẽ như MySQL.

Những khác biệt cú pháp bạn sẽ thực sự gặp

Phần lớn SQL cơ bản như SELECT, JOIN, WHERE, GROUP BY là giống hệt nhau. Các khác biệt tập trung ở vài chỗ:

• Khóa chính tự tăng. SQLite dùng INTEGER PRIMARY KEY (mặc định tự tăng). MySQL dùng INT AUTO_INCREMENT PRIMARY KEY.
• Đặt tên trong dấu nháy. MySQL cho phép dùng dấu backtick cho định danh (`table`). SQLite dùng dấu nháy kép ("table") theo chuẩn SQL.
• Hàm ngày tháng. MySQL có NOW(), CURDATE(), DATE_ADD(). SQLite có datetime('now'), date('now'), datetime('now', '+1 day').
• Cú pháp LIMIT. Cả hai đều hỗ trợ LIMIT n OFFSET m, nên điểm này tương thích.
• Boolean. MySQL có BOOLEAN (bí danh của TINYINT(1)). SQLite lưu boolean dưới dạng 0 và 1 trong cột INTEGER.

-- MySQL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME DEFAULT NOW()
);

-- SQLite
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    created_at TEXT DEFAULT (datetime('now'))
);

Cùng một ý định, khác từ khóa. Cách tư duy vẫn giữ nguyên; cú pháp chỉ cần chỉnh một chút.

Hiệu năng: tùy vào câu hỏi

"SQLite có nhanh hơn MySQL không?" không có một câu trả lời duy nhất.

Với một tiến trình đọc và ghi cục bộ, SQLite thường nhanh hơn: không có bước nhảy qua mạng, không giao tiếp giữa các tiến trình, không có bộ phân tích truy vấn chạy trong một không gian địa chỉ riêng. Một câu SELECT trong SQLite về cơ bản chỉ là một lời gọi hàm.

Với nhiều kết nối đồng thời cùng ghi vào một cơ sở dữ liệu, MySQL vượt lên nhờ khóa cấp hàng. Mô hình một tiến trình ghi của SQLite khiến tranh chấp xuất hiện rất nhanh với khối lượng công việc đó.

Với khối lượng công việc chủ yếu là đọc và đã bật chế độ WAL, SQLite mở rộng tốt đến bất ngờ: các tiến trình đọc không chặn nhau, cũng không chặn tiến trình ghi duy nhất. Rất nhiều trang web production phục vụ lưu lượng thật bằng SQLite.

Đừng chọn dựa trên các benchmark đọc được trên mạng. Hãy chọn dựa trên cách truy cập dữ liệu thực tế của bạn.

Khi nào chọn hệ nào

Chọn SQLite khi:

• Cơ sở dữ liệu nằm cạnh một ứng dụng duy nhất (ứng dụng di động, công cụ desktop, CLI, trang web nhỏ).
• Bạn muốn triển khai không cần cấu hình: chỉ cần gửi kèm tệp.
• Lượng đọc vượt xa lượng ghi, hoặc việc ghi diễn ra không thường xuyên.
• Bạn cần một cơ sở dữ liệu kiểm thử nhúng phản ánh đúng SQL trên production.
• Bạn đang làm prototype và chưa muốn nghĩ đến máy chủ.

Chọn MySQL khi:

• Nhiều máy chủ ứng dụng cần dùng chung một cơ sở dữ liệu.
• Bạn có nhiều tiến trình ghi đồng thời.
• Bạn cần phân quyền người dùng chi tiết và quản lý vai trò.
• Bạn xây dựng trên một stack (LAMP, các dịch vụ cloud được quản lý phổ biến) vốn mặc định dùng MySQL.
• Công cụ vận hành như replication, khôi phục về một thời điểm, giám sát là yêu cầu bắt buộc.

Một quy tắc kinh nghiệm: nếu bạn mô tả nhu cầu lưu trữ là "một ứng dụng, một ổ đĩa", SQLite có lẽ là đủ. Nếu bạn nói "một dịch vụ, có người vận hành", hãy chọn MySQL (hoặc PostgreSQL).

Chuyển đổi giữa hai hệ

Bắt đầu với SQLite rồi chuyển sang MySQL sau là con đường quen thuộc, và là một kế hoạch tốt. Schema chuyển sang được với vài chỉnh sửa nhỏ, còn dữ liệu xuất ra gọn gàng bằng .dump từ SQLite CLI. Phần lớn việc bạn cần làm là sửa cú pháp tự tăng, các hàm ngày tháng và mọi tính năng riêng của SQLite (partial index có hình dạng lạ, WITHOUT ROWID, bảng STRICT) không có tương đương trực tiếp trong MySQL.

Chiều ngược lại, từ MySQL sang SQLite, ít gặp hơn nhưng cũng làm được, thường để phân tích offline, tạo bản sao nhúng của một phần dữ liệu hoặc làm dữ liệu kiểm thử.

Điều quan trọng: chọn SQLite hôm nay không trói buộc bạn. SQL bạn viết vẫn dùng được, và hiểu biết của bạn cũng vậy.

Tiếp theo: SQLite và PostgreSQL

MySQL là phép so sánh phổ biến nhất, nhưng PostgreSQL là cơ sở dữ liệu còn lại mà bạn sẽ thấy được đặt cạnh SQLite, và các khác biệt ở đó lại khác nữa. Đó là nội dung của trang tiếp theo.