I wrote about this yesterday in the previous chapter: 10 Databases you Should Consider Next time as an Architect

(article links: https://juejin.cn/post/6846687602020450318)

A friend of mine asked me a question, and today I will explain the answer to this question, just as popularizing new knowledge

Question:



QLite is a C language library that implements a small, fast, self-contained, highly reliable, fully functional SQL database engine. SQLite is the most commonly used database engine in the world. SQLite is built into all phones and most computers, and bundled into countless other applications that people use every day.

The SQLite file format is stable, cross-platform and backward compatible, and the developers promise to keep it that way until at least 2050. SQLite database files are typically used as containers for transferring rich content between systems and as a long-term archiving format for data.

The characteristics of

Complete ACID.

Full-featured SQL implementations with advanced features such as partial indexes, expression indexes, JSON, common table expressions, and window functions. (Omitted function)

The complete database is stored in a single cross-platform disk file. Ideal for use as an application file format.

Support for gigabyte size databases as well as gigabyte size strings and bloBs. (See limits. HTML.)

Low code footprint: less than 600KiB of fully configured memory, and even less when optional features are omitted.

Easy to use API.

Fast: SQLite is faster than direct file system I/O in some cases

Written in ANSI-C. TCL bindings are included. Dozens of bindings in other languages are available separately.

Cross-platform: out of the box support Android, * BSD, iOS, Linux, Mac, Solaris, VxWorks and Windows (Win32, WinCE, WinRT). Easy portability to other systems.

Comes with a separate command-line interface (CLI) client that can be used to manage SQLite databases.

Reading and writing tests

SQLite reads and writes small chunks (such as thumbnails) 35% faster than reading or writing the same BLOb from a single file on disk using fread () or fwrite (). In addition, a single SQLite database containing 10 KB bloBs has about 20% less disk space than storing bloBs in a single file.

SQLite officially ran 49 test cases with various BLOB sizes and SQLite page sizes on Linux workstations (Ubuntu circa 2011, using the Ext4 file system on fast SATA disks). For each test case, a database containing 100MB BLOB content is created.

Blobs range in size from 10KB to 1MB. The number of bloBs varies to keep the total content of the BLOB at about 100MB. (So there are 100 bloBs for 1MB, 10,000 bloBs for 10K, and so on.) Use SQLite version 3.7.8 (2011-09-19). New measurements for SQLite version 3.19.0 (2017-05-22) show that SQLite is 35% faster than direct disk I/O for reading and writing 10KB BLOBs.

The matrix below shows the time required to read a BLOB stored in a separate file divided by the time required to read a BLOB stored entirely in the database. Therefore, for numbers greater than 1.0, it is faster to store the BLOB directly in the database. For numbers less than 1.0, it is faster to store the BLOB in a separate file.

In each case, the page tuner cache size is adjusted to keep the amount of cache memory around 2MB. For example, a 2000 page cache is used for 1024 byte pages, while a 31 page cache is used for 65536 byte pages. BLOB values are read in random order.

The following rule of thumb is derived from the above matrix:

Database page sizes of 8192 or 16384 provide optimal performance for large BLOB I/O.

Blobs smaller than 100KB are faster to read when stored directly in a database file. For bloBs larger than 100KB, it is faster to read from a separate file.

— — — — — — — —

Conclusion:

SQLite performs well because bai does not have many of the features necessary for database du, such as full transactionality, large data sets (incidentally, we tested a billion in the previous review, some of which are faster than SQLite in this table, I don’t know how this review works), and no cross-process concurrency.

So to put it bluntly, the performance advantage comes entirely from the lack of functionality. Of course, it also has a wide range of applications

Thanks to those who support xiaobian small partners, you can enter a new xiaobian Java learning exchange group: 925895158

To discuss some curious problems together, we can also xuanwo013 to communicate and learn and make progress together.

There is a need to share some good information, like to a small attention bar, thank you!!