Giải thích về tệp SQLite: Cấu trúc, trường hợp sử dụng và các giới hạn quan trọng

Một tệp SQLite là một tệp đĩa đơn duy nhất chứa toàn bộ cơ sở dữ liệu quan hệ — bao gồm các bảng, chỉ mục và lược đồ cùng nhau. Tài liệu hướng dẫn của chính SQLite gọi nó là "tệp cơ sở dữ liệu chính" và lưu ý rằng "trạng thái hoàn chỉnh của một cơ sở dữ liệu SQLite thường được chứa" trong đó. Từ "thường" đóng vai trò thực sự quan trọng trong câu đó.

Nếu bạn từng được giao cho một tệp .db, .sqlite, hoặc .sqlite3 và tự hỏi liệu mình đã nhận được toàn bộ dữ liệu hay chưa, thì đây là định dạng mà bạn cần hiểu rõ.

Tệp SQLite thực chất là gì

SQLite tự mô tả mình là "một thư viện trong tiến trình triển khai một công cụ cơ sở dữ liệu SQL giao dịch, tự chứa, không cần máy chủ, cấu hình bằng không." Trang này cũng tuyên bố rằng SQLite "không có tiến trình máy chủ riêng biệt." Một ứng dụng sẽ liên kết với thư viện và đọc tệp.

Từ đó dẫn đến hai hệ quả. Thứ nhất, cơ sở dữ liệu di chuyển như một thành phần duy nhất, đó là lý do tại sao rất nhiều ứng dụng phân phối dữ liệu của họ theo cách này. Thứ hai, định dạng này phải cực kỳ ổn định, bởi vì những tệp đó có tuổi thọ lâu hơn cả phần mềm đã ghi ra chúng. SQLite liệt kê "định dạng tệp ổn định, bền vững" trong số các tính năng nổi bật của mình. Nó cũng tuyên bố rằng mã nguồn thuộc phạm vi công cộng, "miễn phí sử dụng cho mọi mục đích, thương mại hay cá nhân."

Quy mô của nó rất dễ bị đánh giá thấp. Trang giới thiệu của chính SQLite cho biết đây "là cơ sở dữ liệu được triển khai rộng rãi nhất trên thế giới với số lượng ứng dụng nhiều hơn chúng ta có thể đếm được."

Bên trong tệp có gì

Phần tiêu đề 100-byte

Các byte đầu tiên xác định định dạng. Tại vị trí offset 0, tệp mang một chuỗi tiêu đề 16-byte: SQLite format 3\000. Chữ ký đó là cách các công cụ nhận diện tệp bất kể phần mở rộng của nó là gì.

Trường tiếp theo quan trọng hơn vẻ ngoài của nó. Tại vị trí offset 16 là một số nguyên 2-byte lưu trữ "kích thước trang cơ sở dữ liệu tính bằng byte." Tài liệu hướng dẫn nêu rõ nó "phải là lũy thừa của hai trong khoảng từ 512 đến 32768, hoặc giá trị 1 đại diện cho kích thước trang là 65536." Tất cả các trường nhiều byte trong phần tiêu đề đều được lưu trữ với byte có nghĩa lớn nhất trước.

Hai byte tiếp theo nằm ở vị trí offset 18 và 19: phiên bản ghi và phiên bản đọc của định dạng tệp. Tài liệu lưu ý giá trị này là "1 cho phiên bản cũ; 2 cho WAL."

Trang, không phải hàng

Bên dưới phần tiêu đề, tệp là một ngăn xếp các trang có kích thước cố định. Tài liệu đặc tả nêu rõ: "Tệp cơ sở dữ liệu chính bao gồm một hoặc nhiều trang. Kích thước của một trang là lũy thừa của hai trong khoảng từ 512 đến 65536. Tất cả các trang trong cùng một cơ sở dữ liệu đều có cùng kích thước."

Các trang được đánh số từ 1, và số trang tối đa là 4,294,967,294. Các bảng và chỉ mục nằm bên trong các trang đó dưới dạng cấu trúc B-tree, đó là lý do tại sao một trình soạn thảo văn bản hiển thị rất ít thông tin cho bạn.

Các tệp phụ trợ không ai nhắc đến

Đây là phần thường khiến mọi người bất ngờ. Tài liệu hướng dẫn nói rằng trạng thái hoàn chỉnh "thường" nằm trong một tệp. Sau đó, nó chỉ ra trường hợp ngoại lệ. Trong một giao dịch, SQLite "lưu trữ thông tin bổ sung trong một tệp thứ hai gọi là 'rollback journal'." Ở chế độ WAL, tệp thứ hai đó là một write-ahead log.

Vì vậy, một bản sao được tạo khi ứng dụng đang ghi dở dang có thể bị thiếu dữ liệu đã commit vốn vẫn nằm trong tệp phụ trợ. Nếu một đồng nghiệp gửi cho bạn một tệp .db và không có gì khác, và các con số có vẻ hơi cũ, thì đây là điều đầu tiên cần kiểm tra.

Cách mở tệp SQLite

Có ba cách, và cách phù hợp phụ thuộc vào việc bạn định làm gì tiếp theo.

Đọc bằng trình xem. Các trình xem SQLite trên máy tính và trình duyệt sẽ mở tệp, liệt kê các bảng và cho phép bạn nhấp qua các hàng. Đây là cách nhanh nhất để trả lời câu hỏi "thực sự có gì trong này," và nó thường là đủ cho cái nhìn đầu tiên.

Truy vấn bằng dòng lệnh hoặc thư viện. Shell sqlite3 và các liên kết thư viện tiêu chuẩn trong Python, Node và hầu hết các ngôn ngữ khác đọc định dạng này trực tiếp. Đây là cách tiếp cận khi bạn đã biết lược đồ và muốn tìm một con số cụ thể.

Xuất bảng và phân tích ở nơi khác. Xuất một bảng sang CSV và đưa nó vào bất kỳ công cụ nào mà nhóm của bạn đang sử dụng. Bạn sẽ mất đi các mối quan hệ giữa các bảng, vốn là điều duy nhất mà định dạng này bảo vệ. Hãy xuất kết quả đã liên kết thay vì các bảng thô nếu có thể.

Tại sao một công cụ có thể báo rằng tệp không phải là cơ sở dữ liệu

Tài liệu đặc tả giải thích điều này. Mọi tệp hợp lệ đều bắt đầu bằng một chuỗi tiêu đề 16-byte, SQLite format 3\000. Một trình đọc mở tệp và không tìm thấy chữ ký đó ở vị trí offset 0 nghĩa là nó không được giao cho một cơ sở dữ liệu SQLite.

Ba nguyên nhân thông thường bao gồm hầu hết các trường hợp. Tệp được truyền không hoàn chỉnh, vì vậy phần tiêu đề có ở đó nhưng phần còn lại bị cắt bớt. Tệp bị mã hóa hoặc được bao bọc bởi một ứng dụng, vì vậy các byte đầu tiên là một thứ khác. Hoặc phần mở rộng gây hiểu lầm, và những gì bạn thực sự nhận được là một bản xuất thô được đổi tên bởi một ai đó muốn giúp đỡ.

Tệp SQLite có thể lớn đến mức nào

Lớn hơn những gì câu hỏi thường ngụ ý. Trang giới hạn của SQLite tuyên bố rằng kích thước tối đa của một tệp cơ sở dữ liệu là 4,294,967,294 trang. Với kích thước trang tối đa là 65,536 byte, kích thước cơ sở dữ liệu tối đa sẽ vào khoảng 281 terabytes.

Trang này thành thật một cách mới mẻ về con số đó. Nó lưu ý rằng giới hạn trên "chưa được thử nghiệm vì các nhà phát triển không có quyền truy cập vào phần cứng có khả năng đạt đến giới hạn này."

Số lượng hàng cũng bị giới hạn bởi cùng một bức tường. Giới hạn lý thuyết là 2^64 hàng trong một bảng. Tài liệu chỉ ra rằng giới hạn này "không thể đạt tới vì kích thước cơ sở dữ liệu tối đa 281 terabytes sẽ bị chạm tới trước."

Đối với công việc thực tế, bài học hữu ích rút ra lại ngược lại với một giới hạn. Nếu ai đó đưa cho bạn một tệp .db và cảnh báo rằng nó lớn, định dạng này gần như chắc chắn không phải là thứ cản trở bạn. Kích thước trang được chọn khi tệp được tạo, và việc nó có mang các chỉ mục hay không, sẽ ảnh hưởng đến trải nghiệm nhiều hơn bất kỳ mức trần nào được ghi chép.

Nơi bạn sẽ gặp các tệp SQLite

• Bản xuất của ứng dụng. Các ứng dụng máy tính và di động thường lưu trữ lịch sử, cài đặt và nhật ký tin nhắn trong một tệp SQLite mà bạn có thể sao chép ra ngoài.
• Bàn giao phân tích. Các kỹ sư gửi một bản sao nhanh dưới dạng một tệp thay vì cấp quyền truy cập cơ sở dữ liệu.
• Thiết bị và đo lường từ xa. Các hệ thống nhúng ghi dữ liệu cục bộ vì không có máy chủ để giao tiếp.
• Lưu trữ. Sự ổn định lâu dài của định dạng này khiến nó trở thành lựa chọn phổ biến cho các tập dữ liệu cần phải duy trì khả năng đọc được trong nhiều năm.
• Cơ chế nội bộ của trình duyệt và công cụ. Nhiều công cụ cục bộ lưu giữ trạng thái theo cách này, đó là lý do tại sao phần mở rộng này xuất hiện trong các yêu cầu hỗ trợ.

Các tệp WAL và journal dùng để làm gì

Bạn có thể đã sao chép một tệp .db và tìm thấy một tệp -wal hoặc -journal nằm cạnh nó. Đó là các tệp phụ trợ mà tài liệu đặc tả mô tả, và việc xóa chúng là cách mà mọi người làm mất dữ liệu.

The rollback journal là cơ chế cũ hơn. Trước khi thay đổi một trang, SQLite ghi phiên bản gốc của trang đó vào nhật ký. Nếu quá trình ghi bị gián đoạn, bản gốc có thể được khôi phục lại, đó là điều giúp một giao dịch sống sót qua một sự cố sập nguồn.

The write-ahead log đảo ngược cách sắp xếp đó. Các thay đổi được đưa vào nhật ký trước và tệp chính được cập nhật sau. Phần tiêu đề gắn cờ cho biết cơ sở dữ liệu đang ở chế độ nào. Phiên bản ghi của định dạng tệp tại vị trí offset 18 là "1 cho phiên bản cũ; 2 cho WAL."

Quy tắc thực tế suy ra trực tiếp từ câu nói về trạng thái hoàn chỉnh. Giả sử cơ sở dữ liệu đang ở chế độ WAL và ai đó chỉ đưa cho bạn tệp chính. Những thay đổi đã commit gần đây nhất có thể vẫn đang nằm trong tệp nhật ký mà bạn không nhận được.

Vì vậy, khi bạn được giao một tệp cơ sở dữ liệu, hãy hỏi hai câu hỏi. Ứng dụng có được đóng một cách sạch sẽ khi bản sao được tạo không, và có thứ gì khác đi kèm với nó không? Cả hai câu trả lời thường là có, và lần duy nhất chúng không phải là có chính là lúc các con số âm thầm không khớp với môi trường production.

Tệp SQLite so với CSV so với Parquet

	Tệp SQLite	CSV	Parquet
Hình dạng	Nhiều bảng, một tệp	Một bảng, một tệp	Một bảng, một tệp hoặc thư mục
Kiểu dữ liệu	Được lưu trữ cùng dữ liệu	Được suy luận bởi trình đọc	Được lưu trữ cùng dữ liệu
Mối quan hệ	Được giữ lại, thông qua các khóa và chỉ mục	Bị mất	Bị mất
Con người có thể đọc được	Không	Có	Không
Được viết để truy vấn	Có, bằng SQL	Không	Có, bởi các công cụ phân tích
Lỗi phổ biến	Thiếu tệp phụ trợ journal hoặc WAL	Đoán sai kiểu dữ liệu và dấu phân cách	Hỗ trợ chuỗi công cụ

Nếu bạn làm việc với các định dạng này thường xuyên, các bài viết giải thích của chúng tôi về tệp Parquet và tệp TSV cũng đề cập đến các khía cạnh tương tự cho hai định dạng đó.

Tại sao các nhóm lựa chọn định dạng này

Không cần vận hành gì cả. Bởi vì SQLite "không có tiến trình máy chủ riêng biệt," việc bàn giao chỉ đơn giản là sao chép tệp thay vì một yêu cầu cấp phát tài nguyên.

Kiểu dữ liệu được bảo toàn. Một cột ngày tháng sẽ đến nơi dưới dạng ngày tháng. Bất kỳ ai từng chứng kiến một trình đọc CSV biến một mã định danh thành ký hiệu khoa học đều hiểu giá trị của điều đó.

Các mối quan hệ cũng được bảo toàn. Nhiều bảng liên quan nằm cùng nhau trong một thành phần duy nhất, vì vậy các liên kết giúp dữ liệu có ý nghĩa vẫn khả dụng.

Tính bền vững được thiết kế sẵn. SQLite liệt kê các giao dịch "ngay cả sau khi mất nguồn điện" trong số các tính năng cốt lõi của nó, đó là lý do tại sao rất nhiều phần mềm nhúng dựa vào nó.

Các giới hạn đáng biết

Một tệp, một người ghi tại một thời điểm. Công cụ này được nhúng thay vì được phục vụ qua máy chủ, vì vậy mô hình đồng thời khác với cơ sở dữ liệu client-server. Đó là một lựa chọn thiết kế, không phải là một lỗi, nhưng nó định hình những gì tệp này phù hợp để sử dụng.

Kích thước trang được cố định khi tạo. Mọi trang trong cơ sở dữ liệu đều có cùng kích thước, và kích thước đó được ghi lại trong phần tiêu đề. Bạn chỉ chọn nó một lần duy nhất.

Lại là quy tắc tệp phụ trợ. Bất kỳ quy trình sao chép, sao lưu hoặc tải lên nào chỉ lấy tệp chính đều có thể bỏ lỡ bất kỳ thứ gì nằm trong journal hoặc write-ahead log.

Tính không rõ ràng. Một tệp SQLite không thể đọc lướt qua như cách của một tệp CSV. Việc đọc nó cần có công cụ, đây chính là rào cản làm trì trệ nhiều hoạt động phân tích.

Cách lấy câu trả lời từ một tệp SQLite

Cách truyền thống là cài đặt một ứng dụng khách, mở tệp, tìm hiểu lược đồ và bắt đầu viết SQL. Điều đó vẫn ổn khi bạn đã biết các bảng. Nhưng nó sẽ rất chậm khi bạn vừa được giao tệp vào sáng nay và cuộc họp diễn ra vào chiều nay.

Cách ngắn hơn là đặt câu hỏi trực tiếp. Powerdrill Bloom cho phép bạn làm việc với dữ liệu của mình bằng ngôn ngữ tự nhiên và trả về câu trả lời có đính kèm nguồn của nó. Trang chủ hứa hẹn rằng "mọi con số trả về đều đi kèm với trang, hàng và số liệu đằng sau nó." Từ đó, cùng một không gian làm việc có thể tạo ra các biểu đồ, trang tính hoặc một bản trình bày ngắn.

Hai trang liên quan rất đáng để biết nếu đây là quy trình làm việc thường xuyên của bạn. Chat with Database đề cập đến hướng tiếp cận trò chuyện để đi vào dữ liệu có cấu trúc, và Text to SQL đề cập đến trường hợp bạn muốn có chính câu truy vấn đó. Nếu thay vào đó, bản bàn giao của bạn đến dưới dạng một bản xuất phẳng, trang CSV AI assistant sẽ đề cập đến hướng đi đó.

Thêm một điều nữa mà phần tiêu đề cho bạn biết

Bởi vì kích thước trang nằm ở một vị trí offset cố định, bạn có thể tìm hiểu điều gì đó hữu ích về một tệp trước khi mở nó một cách chính thức. Một cơ sở dữ liệu được tạo với kích thước trang 4,096-byte sẽ hoạt động khác với một cơ sở dữ liệu được tạo với các trang 65,536-byte. Lựa chọn đó đã được đưa ra một lần duy nhất, khi tệp được tạo.

Đó không phải là một con số mà bạn có thể thay đổi một cách ngẫu nhiên sau đó. Nó thuộc cùng một nhóm tư duy với quyết định về lược đồ hơn là một cài đặt thông thường.

Kết luận

Một tệp SQLite là toàn bộ cơ sở dữ liệu trong một thành phần duy nhất. Nó chứa một chữ ký 16-byte, một kích thước trang được ghi lại ở vị trí offset 16, và một ngăn xếp các trang có kích thước cố định mang các bảng và chỉ mục của bạn. Nó di chuyển dễ dàng, giữ nguyên các kiểu dữ liệu và duy trì khả năng đọc được trong nhiều năm.

Hãy nhớ một lưu ý quan trọng mà tài liệu đặc tả đã cẩn thận nhắc nhở. Trạng thái hoàn chỉnh thường nằm trong tệp đó. Trong một giao dịch, một phần của nó nằm trong một rollback journal hoặc một write-ahead log bên cạnh nó. Hãy kiểm tra tệp phụ trợ trước khi bạn tin tưởng vào bản sao.

Khi bạn có tệp và cần câu trả lời thay vì lược đồ, hãy thử Powerdrill Bloom và đặt câu hỏi trực tiếp trên dữ liệu của bạn.

Các câu hỏi thường gặp

Sự khác biệt giữa .db, .sqlite và .sqlite3 là gì?

Không có gì khác biệt về mặt cấu trúc. Cả ba đều là các phần mở rộng thông thường cho cùng một định dạng, và mã định danh thực sự là chuỗi tiêu đề 16-byte SQLite format 3\000 ở đầu tệp.

Làm thế nào để tôi biết tệp SQLite sử dụng kích thước trang nào?

Nó được ghi lại trong phần tiêu đề. Một số nguyên 2-byte tại vị trí offset 16 lưu giữ kích thước trang tính bằng byte. Nó phải là lũy thừa của hai trong khoảng từ 512 đến 32768, hoặc giá trị 1 đại diện cho 65536.

Tệp SQLite có phải là cơ sở dữ liệu hoàn chỉnh không?

Thường là vậy, nhưng không phải luôn luôn. Tài liệu hướng dẫn nêu rõ rằng trong một giao dịch, SQLite giữ thông tin bổ sung trong một rollback journal. Ở chế độ WAL, thông tin đó sẽ được chuyển đến một write-ahead log để thay thế.

Tôi có thể mở tệp SQLite trong Excel không?

Không trực tiếp, bởi vì tệp lưu trữ các trang B-tree thay vì các hàng văn bản. Cách phổ biến là xuất một bảng sang CSV trước, hoặc sử dụng một công cụ đọc định dạng cơ sở dữ liệu và trả về kết quả.

SQLite có được sử dụng miễn phí cho mục đích thương mại không?

Có. SQLite tuyên bố rằng mã nguồn của nó thuộc phạm vi công cộng và "miễn phí sử dụng cho mọi mục đích, thương mại hay cá nhân."