Database Shard Processing via Logical Condition Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In MySQL composite index scenarios, sharded data processing leads to low performance due to the MySQL executor's inability to identify complex logical expressions, resulting in full-table scanning.
Innovation Solution
A database-based data shard processing method that determines first and second type logical conditions based on composite index boundaries, combines these conditions to form candidate combined conditions, and constructs executable Structured Query Language (SQL) statements to improve processing performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If complex logical expressions are used to define shard boundaries in composite index scenarios, then data processing can be more precise, but the MySQL executor cannot identify these expressions resulting in full-table scanning and low performance
Solution Approach 1:
The patent segments the complex logical expression into multiple simple conditions. Each boundary is represented as a set of simple conditions (e.g., `col1 = value1 AND col2 = value2`) rather than a single complex expression. This segmentation allows the MySQL executor to process each condition independently using index scanning, avoiding full-table scanning while maintaining precise shard boundary definition.
Solution Approach 2:
The patent changes the representation of boundary conditions from complex logical expressions to simplified parameter-based conditions. By transforming the logical structure into a set of simple equality and range conditions that can be directly mapped to index parameters, the system enables the MySQL executor to efficiently process queries without degrading to full-table scanning.
2Reliability
If the MySQL executor processes complex logical expressions for sharded data, then comprehensive data filtering can be achieved, but processing performance degrades due to inability to identify the expressions
Solution Approach 1:
The complex logical expression is segmented into multiple simple conditions that can be independently processed. Each condition corresponds to a simple comparison or equality check that the MySQL executor can handle efficiently using the composite index, maintaining accurate data filtering while improving processing speed through index-based scanning instead of full-table scans.
Solution Approach 2:
The patent introduces an intermediary transformation layer that converts complex logical expressions into a format suitable for MySQL executor processing. This intermediary representation uses simple conditions that can be directly translated into SQL queries, ensuring both accurate data filtering and efficient execution without requiring the executor to parse complex expressions.
3Device complexity
If sharded data is processed using composite index boundaries, then data can be divided into manageable chunks, but the MySQL executor fails to identify the boundary conditions leading to full-table scanning
Solution Approach 1:
The data is segmented into shards defined by simple boundary conditions rather than complex expressions. Each shard boundary is represented as a set of simple conditions (e.g., `col1 >= value1 AND col2 < value2`) that can be efficiently processed by the MySQL executor using the composite index, enabling effective data division while maintaining high processing efficiency through index-based queries.
Solution Approach 2:
The boundary conditions are transformed from complex logical expressions to simplified parameter-based conditions. By representing boundaries as simple equality and range conditions that directly map to index parameters, the system enables efficient data shard management while avoiding full-table scanning, thus improving data processing efficiency.
Data Source
AI summary
A database-based data shard processing method and apparatus, a medium, and an electronic device are provided. The method includes: obtaining a processing request for a shard to be processed that corresponds to a first boundary and a second boundary; determining a first type of logical condition, which includes first sub-conditions for defining data greater than or equal to the first boundary, based on the first boundary, and determining a second type of logical condition, which includes second sub-conditions for defining data less than the second boundary, based on the second boundary; combining the first sub-conditions with the second sub-conditions separately to obtain candidate combined conditions; determining executable structured query languages based on an execution statement corresponding to the processing request and the candidate combined conditions; and executing the structured query languages to obtain a processing result corresponding to the processing request.