Rapid data writing and reading system and method based on sqlite database fragmentation and medium

Abstract

The invention discloses a data fast writing and reading system and method based on sqlite database fragments.The system comprises a fragmenter, the fragmenter comprises a fragmentation database, an analyzer, a manager and an optimizer, a plurality of database fragments capable of being horizontally expanded are stored in the fragmentation database, and IDs are arranged in data tables of the database fragments. The analyzer is used for analyzing an SQL statement text and correspondingly sending an SQL statement to the manager or the optimizer based on the type of the SQL statement; the manager is used for keeping the table structure or database configuration of each sqlite database fragment consistent; and the optimizer is used for executing write-in, deletion or query operation on the sqlite database fragments. According to the method, database file fragments are introduced, so that the problems of data writing and reading under high concurrency and large data volume are solved; according to the method, writing and query of the data are agented through the fragmenter, so that sqlite addition, deletion, modification and query are effectively improved, and meanwhile, deadlocks generated during concurrent access of the database can be remarkably reduced.

Description

Technical Field

[0001] This invention belongs to the technical field of SQLite databases, specifically relating to a system, method, and medium for fast data writing and reading based on SQLite database sharding. Background Technology

[0002] SQLite is a lightweight database, easy to use and migrate, and widely used in many lightweight, low-concurrency, and small-data-volume scenarios. It is mostly suitable for resource-constrained environments, such as embedded systems, mobile devices, and small applications. SQLite does not require a separate server process or operating system service to run; it embeds the entire database engine within the application, making it easy to manage and distribute, and irreplaceable in many scenarios. However, SQLite also has several limitations, particularly in handling high concurrency, complex queries, and large data volumes. ① Its concurrency performance is limited. Write operations are exclusive, meaning that other operations will be blocked during a write operation. SQLite uses a database-level locking mechanism, which means that the entire database is locked during a write operation, preventing other operations from proceeding. This leads to contention between write operations, affecting write performance and concurrency capabilities. Only one write operation can be performed at a time; multiple write operations need to queue and wait, failing to take advantage of modern multi-core processors and resulting in decreased write performance.

[0003] ② It also cannot utilize the multi-core capabilities of modern CPUs. Although theoretically SQLite database file sizes can reach 140 TB, in practical applications, the increase in individual file size leads to performance degradation. The larger the file, the greater the overhead of I / O operations, affecting read and write performance. SQLite is practically unsuitable for high-concurrency write and high-concurrency read scenarios.

[0004] Currently, existing technologies employ multiple SQLite databases, primarily aimed at reducing deadlocks during write operations, but not addressing read and write operations under high concurrency and large volumes. This approach splits the client-side SQLite database from one into two or more during the software design phase, with each SQLite database corresponding to a specific type of write operation requirement within the software system. All data tables in the software system are categorized according to different types of write operations. However, this method, requiring the business system to handle data in a categorized manner, is neither flexible nor scalable. It necessitates logical distinctions between reads and writes, and differentiating where reads and writes should occur for different table types, thus interfering with business logic. Furthermore, if a table is a hot table, data will be written to it frequently, still residing in a single SQLite database file, while the other SQLite databases remain idle. Since different types of tables in the database generally follow the Pareto principle (80 / 20 rule), most data reads and writes only affect a small number of tables. Therefore, existing solutions have limited effectiveness in reducing deadlocks. Summary of the Invention

[0005] The purpose of this invention is to provide a system, method, and medium for fast data writing and reading based on SQLite database sharding, in order to solve the above-mentioned problems.

[0006] This invention is mainly achieved through the following technical solutions: A fast data writing and reading system based on SQLite database sharding includes a sharder, which is used to manage, write and read multiple horizontally scalable SQLite database shards and provides an SQLite database access interface to the outside world. The sharder includes a shard database, a parser, a manager, and an optimizer. The shard database stores multiple horizontally scalable SQLite database shards, and the data tables of the SQLite database shards are set with IDs. The parser is used to analyze the SQL statement text and send the SQL statement to the manager or optimizer according to the type of the SQL statement; the manager is used to keep the table structure or database configuration of each SQLite database shard consistent based on the SQL statement; the optimizer is used to perform write, delete or query operations on the SQLite database shard according to the SQL statement. The optimizer incorporates a key-based hash sharding function to perform a hash modulo operation on the unique key to obtain the ID of the SQLite database shard corresponding to the unique key. The optimizer includes an index and key buffer module, which is used to read and store the unique key of each data table in all SQLite database shards and the SQLite database shard to which each unique key belongs, as well as the indexes, composite unique indexes, and content fields in each data table.

[0007] To better implement the present invention, the optimizer further includes a writer, and the parser is connected to the manager and the writer respectively; the writer is used to read the stored unique index based on the index and key buffer module to determine whether there is a uniqueness conflict in the data to be written. If there is a conflict, an error message is returned; otherwise, the largest unique key is found, the unique key is incremented, the hash of the incremented unique key is used to calculate the target SQLite database shard, and the write operation is performed.

[0008] To better implement the present invention, the optimizer further includes a deleter, and the parser is connected to the manager and the deleter respectively; the deleter is used to match the deletion conditions based on the index and key buffer module according to the cache of unique keys and indexes. If the target SQLite database shard is found, the deletion operation is performed; otherwise, the deletion operation is performed in parallel on all SQLite database shards.

[0009] To better implement the present invention, the optimizer further includes a queryer, and the parser is connected to the manager and the queryer respectively. The queryer is based on the index and key buffer module, and matches the query conditions according to the cache of the unique key and the index. If the target SQLite database shard is found, the target SQLite database shard is located and the query operation is executed. Otherwise, the query operation is executed in parallel on all SQLite database shards, and finally the query result is returned to the client.

[0010] This invention is mainly achieved through the following technical solutions: A method for fast data writing and reading based on SQLite database sharding includes the following steps: Step S1: Use a parser to analyze the SQL statement text. If the SQL statement is a write, delete, or query operation, proceed to step S2. If the SQL statement is a management operation, proceed to step S4. Step S2: The optimizer reads the unique keys of each table in all SQLite database shards and records the SQLite database shard to which each unique key belongs; the optimizer reads the indexes and composite unique indexes in each table and caches the fields of the content together; Step S3: Then, based on the writer, deleter, or queryer inside the optimizer, perform data writing, data deletion, or data query, and return the operation results to the client to end the process; Step S4: The manager analyzes the SQL statement. If it is a database table creation, modification, or deletion operation, the table operation is executed in each SQLite database shard to ensure that the data table structure of all SQLite database shards is consistent. If it is a database setting operation, the SQL statement setting operation is executed in each SQLite database shard to ensure that the configuration of all SQLite database shards is consistent. Finally, the operation result is returned to the client, and the process ends.

[0011] To better implement the present invention, further, in step S3, the data writing includes the following steps: Step A1: Receive the SQL statement and obtain the content to be written; Step A2: Analyze the uniqueness constraint of the written content. If a uniqueness conflict exists, return an error message; otherwise, proceed to step A3. Step A3: Find the current maximum unique key, the auto-incrementing maximum unique key, and perform hash modulo calculation to obtain the target SQLite database shard; Step A4: Perform a write operation to the target SQLite database shard, writing the data to the shard database in parallel; finally, return the write result to the client.

[0012] To better implement the present invention, further, in step S3, the data deletion includes the following steps: Step B1: Receive the SQL statement and obtain the deletion conditions; Step B2: Analyze the deletion conditions and match them with the cache of unique keys and indexes. If a match is found, the target SQLite database shard is found and proceed to step B3; otherwise, proceed to step B4. Step B3: Locate the target SQLite database shard, perform the deletion operation, and proceed to step B5; Step B4: Perform the delete operation in parallel across all SQLite database shards; Step B5: Return the deletion results to the client.

[0013] To better implement the present invention, further, in step S3, the data query includes the following steps: Step C1: Receive the SQL statement and obtain the query conditions; Step C2: Analyze the query conditions and match them against the cache of unique keys and indexes. If a match is found, the target SQLite database shard is located and the process proceeds to step C3; otherwise, the process proceeds to step C4. Step C3: Locate the target SQLite database shard, execute the query operation, and proceed to step C5; Step C4: Execute the query operation in parallel across all SQLite database shards; Step C5: Summarize, sort, and truncate the query results based on the SQL statement, and return them to the client.

[0014] A computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the above-described method for fast data writing and reading based on SQLite database sharding.

[0015] The beneficial effects of this invention are as follows: (1) This invention solves the problem of writing and reading data under high concurrency and large data volume by introducing SQLite database file sharding; secondly, this invention introduces an intermediate layer - sharding, which proxies the writing and querying of data through sharding. The components provided by sharding are responsible for sharding to multiple SQLite database files for writing, querying and deleting. At this time, using the database is the same as when it is on a single SQLite database, which has good practicality. (2) This invention improves SQLite CRUD operations while significantly reducing deadlocks caused by concurrent access to the SQLite database. In this invention, the same data table is distributed across multiple sharded databases, thus distributing reads and writes of frequently accessed data tables across multiple sharded databases, resulting in excellent database locking reduction. Furthermore, this invention eliminates the need for business logic to adapt to the database and avoids requiring different types of read and write operations on SQLite tables to be placed in different databases. By setting up a sharder, this invention ensures that the underlying sharded database is indistinguishable from a monolithic database, without affecting business functionality design. Attached Figure Description

[0016] Figure 1 This is a schematic diagram of the slicer structure; Figure 2 A flowchart for the manager; Figure 3 A flowchart for writing data; Figure 4 Flowchart for data deletion; Figure 5 This is a flowchart for data querying. Detailed Implementation

[0017] Example 1: A system for fast data writing and reading based on SQLite database sharding is disclosed. This system introduces a sharder to manage the SQLite database and handle all CRUD operations. Behind the sharder are multiple horizontally scalable SQLite database shard files. These shard files are uniformly managed by the sharder. Since the SQLite database is a single file, it can be easily managed on a file system, which can be a local file system or a remote file system mounted locally, allowing for flexible data arrangement. The sharder comes with its own SQLite driver, which can be a binary program that can be implemented as a service and integrated with various programming languages. It provides a complete SQLite database access interface, allowing applications to access the sharder directly, just like accessing a SQLite database directly. Behind the unified interface provided by the sharder is a unified processing mechanism for the management, writing, and reading of the database shard files.

[0018] Preferably, such as Figure 1 As shown, the sharder includes a parser, a manager, and an optimizer. The optimizer includes a writer, a deleter, and a queryer. Multiple horizontally scalable SQLite database shard files are stored in the sharded database. After the parser identifies the management statement, the manager writes the management operation to the SQLite database shard based on the management statement. This ensures that, apart from the different data stored in each shard, the table structure and configuration are consistent.

[0019] The parser is an essential component in the sharding process. The sharding needs to parse the SQL using the parser before determining the next step. The parser analyzes the SQL statement text to determine which component the SQL should be sent to—the manager, writer, deleter, or queryer.

[0020] Specifically, SQL statements have generally accepted standards, and the parsing methods are very mature. The parser here only needs to parse out the type of the SQL statement. For example, statements starting with "create" need to be sent to the manager, and statements starting with "insert" need to be sent to the writer. The parsing rules are very simple, so they will not be listed here.

[0021] The optimizer incorporates a key-based hash sharding function. The principle is to hash the unique key and perform a modulo operation to obtain the shard ID to which the unique key should be stored. For example, if there are 8 database shards and a unique key 10 exists, its MD5 hash is calculated, and the modulo operation (MD5 hash % 8) yields a number between 1 and 8. This number is the shard database ID to which this data belongs. This requires each data table structure to have an ID.

[0022] Preferably, since the data resides across multiple sharded databases, the database itself cannot check for unique key conflicts; this capability is handled by the optimizer. At startup, the optimizer reads the unique keys of each table in all shards (by default, the ID field of each table), collects all unique keys from all shards, and records the shard to which each unique key belongs. This data is cached in memory or on disk. Additionally, it reads the indexes and composite unique indexes from each table, caching the content fields together to optimize queries and handle field uniqueness conflicts during writes.

[0023] Specifically, when performing a write operation, the optimizer first reads the unique index stored in the optimizer itself to determine whether there is a uniqueness conflict in the data to be written. If there is a conflict, it immediately returns an error message. Otherwise, the optimizer finds the largest unique key and increments it. It then uses the hash of the incremented unique key to calculate the shard position to be placed and returns the optimized write operation information to the client.

[0024] The writer, based on the optimizer's information, locates the specified shard and writes the data in parallel to the sharded database. Before writing, the optimizer assigns a primary key ID to the data being written. This requires a primary key ID when creating the table structure; in relational databases, best practices dictate that each record has a primary key ID.

[0025] Because SQL writes are scheduled by the sharder, database locking is rare. In the rare cases where database locking occurs, the writer will wait and retry. The writer writes to multiple database shards in parallel, fully utilizing the performance of multi-core CPUs. In this case, the overall database throughput increases linearly with the number of shards.

[0026] Specifically, when performing a deletion operation, the optimizer attempts to determine the location of the shard to be deleted based on the cached unique key and index, and then passes the optimized deletion operation information to the deleter to execute the deletion operation. If the optimizer cannot find the location of the shard to be deleted, it directly passes the information to the deleter, performs the deletion in parallel on all shards, and finally returns the operation result to the client.

[0027] When deleting data, queries are often involved. This is essentially a process of querying first and then deleting. Therefore, the optimizer may not be able to identify the specific shard location during the query, and in the worst case, the delete statement needs to be executed on all shards. However, this fully utilizes the capabilities of multiple CPU cores, and since the data volume on each shard is relatively small after being distributed, the deletion efficiency remains extremely high, and the deletion efficiency increases with the number of sharded databases.

[0028] Specifically, when the optimizer performs a query operation, it attempts to determine whether the query field hits the index cache field based on the SQL statement. If it does, it obtains the shard to which the data belongs and the unique key, and passes this information to the query engine. If it does not hit, it directly passes the information to the query engine, executes the query in parallel on all shards, and finally returns the operation result to the client.

[0029] During data querying, the optimizer may fail to match the shard to which the queried data belongs. In the worst-case scenario, the SQL statement will be executed on all shards. During the data collection phase, the query engine supports summarizing, sorting, and truncating the query results based on the SQL statement. For example, the SQL statement might request that the data be returned in descending order of a certain field, specifically rows 10 through 20.

[0030] This invention manages multiple SQLite database shards through a sharder, improving the efficiency of database operations (CRUD operations) and reducing database locking. The invention employs a unified management approach for SQLite database shards based on a manager; high-concurrency write and delete operations based on writers and deleters; and efficient query operations based on a queryer.

[0031] Example 2: A method for fast data writing and reading based on SQLite database sharding includes the following steps: Step S1: Use a parser to analyze the SQL statement text. If the SQL statement is a write, delete, or query operation, proceed to step S2. If the SQL statement is a management operation, proceed to step S4. Step S2: The optimizer reads the unique keys of each table in all SQLite database shards and records the SQLite database shard to which each unique key belongs; the optimizer also reads the indexes and composite unique indexes in each table and caches the fields of the content together to optimize queries and handle field uniqueness conflicts during writes; Step S3: Then, based on the writer, deleter, and queryer inside the optimizer, data is written, deleted, or queried, and the operation results are returned to the client to end the process; Step S4: The manager analyzes the SQL statement. If it is a database table creation, modification, or deletion operation, the table operation is executed in each shard to ensure that the data table structure of all shards is consistent. If it is a database setting operation, the SQL statement setting operation is executed in each shard to ensure that the configuration of all shards is consistent. Finally, the operation result is returned to the client, and the process ends.

[0032] Preferably, such as Figure 2As shown, during manager runtime: First, it receives management SQL statements from the parser, analyzes the SQL type, and if it's a table creation / modification / deletion statement, it executes the table operation in each shard to ensure consistent table structure across all shards. If it's a database setting statement, it executes configuration operations such as PRAGMA in each shard to ensure consistent configuration across all shards. Finally, it returns the operation results to the client. The sharder takes over these management operations on SQLite, and after wrapping, it appears to the business layer as if using a separate SQLite database. Preferably, such as Figure 3 As shown, the data writing process steps are as follows: 1. If the parser determines that the SQL statement is a write statement, it passes the SQL statement and the parsed information to the optimizer; 2. The optimizer analyzes uniqueness constraints. If it finds a uniqueness conflict in the content to be written, it returns with an error and proceeds to step 4. If there is no conflict, it finds the largest unique key in the table to be written, increments it, uses the hash of the incremented unique key to calculate the shard position to place it in, and then passes the optimized write operation information to the writer. 3. The writer finds the shards to be written to based on the optimizer's optimization information and performs the data writing operation; 4. Return the write result to the client.

[0033] From the business layer's perspective, these write operations are the same as when using a separate SQLite database.

[0034] Preferably, such as Figure 4 As shown, the data deletion process is as follows: 1. If the parser determines that the SQL statement is a delete statement, it passes the SQL statement and the parsed information to the optimizer; 2. The optimizer analyzes the deletion conditions, attempts to match the deletion conditions on the cache of unique keys and indexes, and passes the matching results to the deleter; 3. Based on the matching results, if no data shard to be deleted is matched, the deleter will execute the SQL statement on all shards; if a data shard to be deleted is matched, the deleter will locate the shard and perform the data deletion operation. 4. Return the deletion results to the client.

[0035] From the business layer's perspective, these deletion operations are consistent with using a separate SQLite database.

[0036] Preferably, such as Figure 5 As shown, the data query process is as follows: 1. If the parser determines that the SQL statement is a query statement, it passes the SQL statement and the parsed information to the optimizer; 2. The optimizer analyzes the query conditions, attempts to match the query conditions on the cache of unique keys and indexes, and hands the matching results to the deleter; 3. Based on the optimizer's results, if no specific data shard is matched, the query engine will execute the SQL query statement on all shards; if a shard of the queried data is matched, the query engine will locate the shard and execute the data query operation. 4. Collect the results from each slice, and summarize, sort, and truncate the results; 5. Return the queried data to the client.

[0037] Because each shard database contains less data after sharding, and the sharders perform parallel queries, they can make full use of multi-core CPUs, resulting in extremely high query efficiency.

[0038] This invention improves SQLite CRUD operations while significantly reducing deadlocks caused by concurrent access to SQLite databases. In this invention, the same data table is distributed across multiple sharded databases, thus distributing reads and writes to frequently accessed tables across multiple shards, resulting in excellent database locking reduction. Furthermore, this invention eliminates the need for business logic to adapt to the database, and it eliminates the requirement that different types of SQLite database read and write operations must reside in different databases. After the business layer of this invention uses the sharder, it is unaware of any difference between the underlying sharded database and the monolithic database, and it does not affect the design of business functions.

[0039] The above description is merely a preferred embodiment of the present invention and is not intended to limit the present invention in any way. Any simple modifications or equivalent changes made to the above embodiments based on the technical essence of the present invention shall fall within the protection scope of the present invention.

Claims

1. A system for fast writing and reading of data based on sqlite database sharding, characterized in that, It includes a sharder, which manages, writes, and reads multiple horizontally scalable SQLite database shards and provides an SQLite database access interface to the outside world; The sharder includes a shard database, a parser, a manager, and an optimizer. The shard database stores multiple horizontally scalable SQLite database shards, and the data tables of the SQLite database shards are set with IDs. The parser is used to analyze the SQL statement text and send the corresponding SQL statement to the manager or optimizer based on the type of the SQL statement; the manager is used to keep the table structure or database configuration of each SQLite database shard consistent based on the SQL statement. The optimizer is used to perform write, delete, or query operations on the sqlite database shards according to SQL statements; The optimizer incorporates a key-based hash sharding function to perform a hash modulo operation on the unique key to obtain the ID of the SQLite database shard corresponding to the unique key. The optimizer includes an index and key buffer module, which is used to read and store the unique key of each data table in all SQLite database shards and the SQLite database shard to which each unique key belongs, as well as the indexes, composite unique indexes, and content fields in each data table.

2. The system for fast write and read of data based on sqlite database sharding as claimed in claim 1, wherein, The optimizer includes a writer, and the parser is connected to the manager and the writer respectively. The writer is used to read the stored unique index based on the index and key buffer module to determine whether there is a uniqueness conflict in the data to be written. If there is a conflict, an error message is returned. Otherwise, the largest unique key is found and the unique key is incremented. The hash of the incremented unique key is used to calculate the target SQLite database shard and the write operation is performed.

3. The system for fast write and read of data based on sqlite database sharding as claimed in claim 1, wherein, The optimizer includes a deleter, and the parser is connected to the manager and the deleter respectively. The deleter is used to match deletion conditions based on the index and key buffer module, according to the cache of unique keys and indexes. If the target SQLite database shard is found, the deletion operation is performed; otherwise, the deletion operation is performed in parallel on all SQLite database shards.

4. The system for fast write and read of data based on sqlite database sharding as claimed in claim 1, wherein, The optimizer includes a queryer. The parser is connected to the manager and the queryer respectively. The queryer is based on the index and key buffer module. It matches the query conditions according to the cache of the unique key and the index. If the target SQLite database shard is found, the target SQLite database shard is located and the query operation is executed. Otherwise, the query operation is executed in parallel on all SQLite database shards. Finally, the query result is returned to the client.

5. A method for fast writing and reading of data based on sqlite database sharding, characterized in that, Includes the following steps: Step S1: Use a parser to analyze the SQL statement text. If the SQL statement is a write, delete, or query operation, proceed to step S2. If the SQL statement is a management operation, proceed to step S4. Step S2: The optimizer reads the unique keys of each table in all SQLite database shards and records the SQLite database shard to which each unique key belongs; the optimizer reads the indexes and composite unique indexes in each table and caches the fields of the content together; Step S3: Then, based on the writer, deleter, or queryer inside the optimizer, perform data writing, data deletion, or data query, and return the operation results to the client to end the process; Step S4: The manager analyzes the SQL statement. If it is a database table creation, modification, or deletion operation, the table operation is executed in each SQLite database shard to ensure that the data table structure of all SQLite database shards is consistent. If it is a database setting operation, the SQL statement setting operation is executed in each SQLite database shard to ensure that the configuration of all SQLite database shards is consistent. Finally, the operation result is returned to the client, and the process ends.

6. The method for fast write and read of data based on sqlite database sharding as claimed in claim 5 wherein, In step S3, the data writing includes the following steps: Step A1: Receive the SQL statement and obtain the content to be written; Step A2: Analyze the uniqueness constraint of the written content. If a uniqueness conflict exists, return an error message; otherwise, proceed to step A3. Step A3: Find the current maximum unique key, the auto-incrementing maximum unique key, and perform hash modulo calculation to obtain the target SQLite database shard; Step A4: Perform a write operation to the target SQLite database shard, writing the data to the shard database in parallel; finally, return the write result to the client.

7. The method for fast data writing and reading based on SQLite database sharding according to claim 5, characterized in that, In step S3, the data deletion includes the following steps: Step B1: Receive the SQL statement and obtain the deletion conditions; Step B2: Analyze the deletion conditions and match them with the cache of unique keys and indexes. If a match is found, the target SQLite database shard is found and proceed to step B3; otherwise, proceed to step B4. Step B3: Locate the target SQLite database shard, perform the deletion operation, and proceed to step B5; Step B4: Perform the delete operation in parallel across all SQLite database shards; Step B5: Return the deletion results to the client.

8. The method for fast data writing and reading based on SQLite database sharding according to claim 5, characterized in that, In step S3, the data query includes the following steps: Step C1: Receive the SQL statement and obtain the query conditions; Step C2: Analyze the query conditions and match them against the cache of unique keys and indexes. If a match is found, the target SQLite database shard is located and the process proceeds to step C3; otherwise, the process proceeds to step C4. Step C3: Locate the target SQLite database shard, execute the query operation, and proceed to step C5; Step C4: Execute the query operation in parallel across all SQLite database shards; Step C5: Summarize, sort, and truncate the query results based on the SQL statement, and return them to the client.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When executed by the processor, the program implements the fast data writing and reading method based on SQLite database sharding as described in any one of claims 5-8.

Images