LIKE query optimization method and device based on dynamic index and related equipment

Abstract

The invention discloses an LIKE query optimization method and device based on dynamic indexing.The method comprises the steps that a target LIKE predicate in a connection condition is recognized, the left operand of the target LIKE predicate is an index column of a first data table, and the right operand of the target LIKE predicate comprises an expression from a second data table; according to the target LIKE predicate, an optimized index scanning plan corresponding to the first data table is generated, and the optimized index scanning plan is used for generating an index scanning key according to the value of the second data table expression during execution; determining an actual matching mode corresponding to the target LIKE predicate based on the value of the second data table expression; according to the actual matching mode, generating an index scanning key for accessing the first data table index; and performing index scanning on the first data table based on the index scanning key to obtain data meeting the connection condition. The index scanning key is dynamically generated according to the variable value, so that efficient index range scanning is carried out, the I / O overhead is reduced, and the execution performance of LIKE query is improved.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, apparatus and related equipment for optimizing LIKE queries based on dynamic indexes. Background Technology

[0002] In modern relational database systems, query performance heavily relies on the query optimizer's intelligent selection of execution plans. A typical query processing flow includes core stages such as syntax parsing, semantic analysis, query rewriting, optimizer plan generation, and executor execution plan. One of the core tasks of the optimizer is to match appropriate data access paths to query conditions, primarily choosing between efficient but conditional index scans and inefficient but general full table scans. When query conditions use basic comparison operators that support index lookups, the optimizer can directly convert them into one or more precise or range index scan keys, thereby guiding the storage engine to quickly locate the target data page and significantly reducing I / O overhead.

[0003] For non-basic operators like LIKE used for fuzzy matching, existing systems achieve limited index optimization through predicate rewriting techniques. Specifically, when the right-hand side of LIKE is a constant string (e.g., col LIKE 'abc%'), the optimizer can logically rewrite it at compile time as a lexicographically ordered combination of range comparison conditions (e.g., col >= 'abc' AND col < 'abd'). The rewritten conditions can be directly used to generate the index scan key, enabling queries to perform efficient range scans using the index on the col column, rather than a full table traversal.

[0004] However, the above optimization mechanism has a key limitation: it heavily relies on the determinism of the pattern string at query compile time. When the right side of the LIKE predicate is an expression containing variables from other tables (e.g., in the join query SELECT * FROM T1 JOIN T2 ON T2.name LIKE T1.prefix + '%', where T1.prefix is ​​a variable), the optimizer cannot predict the value of T1.prefix at runtime during the execution plan generation phase. Because it cannot know the actual pattern string formed at runtime (especially the position of the wildcard '%'), the optimizer cannot safely and deterministically rewrite the predicate from LIKE to range comparison conditions. Therefore, the system is forced to resort to a full table scan or full index scan for internal tables, and after retrieving all rows, filter each row sequentially using the original LIKE expression in the join condition. This "scan first, filter later" approach generates a large amount of unnecessary I / O and CPU overhead when processing large-scale tables or frequently executing such join queries, becoming a significant bottleneck restricting overall system performance. Summary of the Invention

[0005] To address the aforementioned issues, this invention provides a method, apparatus, and related equipment for optimizing LIKE queries based on dynamic indexes. By dynamically generating index scan keys based on variable values ​​during the execution phase, the original full table / full index scan, which could not be optimized due to variables in the right-hand expression of LIKE, is transformed into an efficient index range scan. This significantly reduces I / O overhead and greatly improves the execution performance of such fuzzy matching join queries.

[0006] In a first aspect, embodiments of the present invention provide a method for optimizing LIKE queries based on dynamic indexes. The method includes: identifying a target LIKE predicate in a join condition, wherein the left operand of the target LIKE predicate is an index column of a first data table, and the right operand contains an expression from a second data table; generating an optimized index scan plan corresponding to the first data table based on the target LIKE predicate, wherein the optimized index scan plan generates an index scan key based on the value of the expression in the second data table during execution; determining the actual matching pattern corresponding to the target LIKE predicate based on the value of the expression in the second data table; generating an index scan key for accessing the index of the first data table based on the actual matching pattern; and performing an index scan on the first data table based on the index scan key to obtain data that meets the join condition.

[0007] In one possible embodiment, identifying the target LIKE predicate in the join condition includes: If the left operand of the LIKE predicate is a B+ tree index column of the first data table, and the right operand of the LIKE predicate is an expression containing variables in the second data table, then the LIKE predicate is determined to be the target LIKE predicate.

[0008] In one possible embodiment, generating an index scan key for accessing the first data table index based on the actual matching pattern includes: Extract the values ​​of the target LIKE predicate expression and rewrite the target LIKE predicate; Based on the position of the wildcard in the target LIKE predicate, an index scan key is generated to access the index of the first data table.

[0009] In one possible embodiment, an index scan key for accessing the first data table index is generated based on the position of the wildcard in the target LIKE predicate, including: When the wildcard is at the end of the string, that is, the actual matching pattern is in the form of "prefix %", the range scan condition is constructed as col >= "prefix" AND col < "next lexicographical string"; Based on the range scan conditions, an index scan key is generated to access the index of the first data table.

[0010] In one possible embodiment, an index scan key for accessing the first data table index is generated based on the position of the wildcard in the target LIKE predicate, including: When the wildcard is in the middle of the string, that is, the actual matching pattern is in the form of "prefix % suffix", the range scanning condition is constructed as col >= "prefix" AND col < "next lexicographical string"; Based on the range scan conditions, generate an index scan key for accessing the index of the first data table; An index scan is performed on the first data table based on the index scan key to obtain data that meets the join conditions, including: Use the actual matching pattern as the index filtering condition, and perform an index scan based on the index scan key; The scanned index entries are checked for matching based on the index filtering conditions.

[0011] In one possible embodiment, an index scan key for accessing the first data table index is generated based on the position of the wildcard in the target LIKE predicate, including: When no wildcard is included, i.e. the actual matching pattern is in the form of "string", the range scan condition is constructed as col="string"; Based on the range scan conditions, an index scan key is generated to access the index of the first data table.

[0012] In one possible embodiment, an index scan key for accessing the first data table index is generated based on the position of the wildcard in the target LIKE predicate, including: When the wildcard is at the beginning of the string, that is, the actual matching pattern is in the form of "% suffix", the actual matching pattern is used as the index filtering condition, and the index of the first data table is scanned. An index scan is performed on the first data table based on the index scan key to obtain data that meets the join conditions, including: The scanned index entries are checked for matching based on the index filtering conditions.

[0013] Secondly, embodiments of the present invention provide a LIKE query optimization device based on dynamic indexes. This device includes: an identification module, a first generation module, a determination module, a second generation module, and an index scanning module. The identification module is used to identify the target LIKE predicate in the join condition, wherein the left operand of the target LIKE predicate is an index column of a first data table, and the right operand contains an expression from a second data table. The first generation module is used to generate an optimized index scan plan corresponding to the first data table based on the target LIKE predicate. The optimized index scan plan generates the index scan key based on the value of the expression in the second data table during execution. The determination module is used to determine the actual matching pattern corresponding to the target LIKE predicate based on the value of the expression in the second data table; The second generation module is used to generate an index scan key for accessing the index of the first data table based on the actual matching pattern. The index scan module is used to perform an index scan on the first data table based on the index scan key in order to obtain data that meets the join conditions.

[0014] Thirdly, embodiments of the present invention provide a computer storage medium storing multiple instructions adapted for loading by a processor and executing the steps of the above-described method.

[0015] Fourthly, embodiments of the present invention provide an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being adapted to be loaded by the processor and to execute the steps of the method described above.

[0016] The beneficial effects of the technical solutions provided by some embodiments of the present invention include at least the following: dynamically generating index scan keys based on variable values, transforming full table or full index scans that were originally unoptimizable due to variables in the LIKE right-hand expression into efficient index range scans, thus eliminating the dependence of traditional database optimizers on compile-time determinism. This solution directly reduces unnecessary I / O operations and CPU filtering overhead, lowers query response time and resource consumption, and improves the performance of join queries involving LIKE variable expressions (especially large-scale data table joins) by orders of magnitude. Simultaneously, without changing the SQL semantics and user interface, it seamlessly enhances existing query optimization capabilities, improving the efficiency and scalability of the database system when handling real-time, dynamic fuzzy matching queries, effectively solving the long-standing performance bottleneck problem in such scenarios. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0018] Figure 1 An exemplary system architecture diagram of a LIKE query optimization system based on dynamic indexes provided in this embodiment of the invention; Figure 2 A flowchart illustrating a LIKE query optimization method based on dynamic indexes provided in an embodiment of the present invention; Figure 3 A logical flowchart of the query optimization stage provided in an embodiment of the present invention; Figure 4 A structural block diagram of a LIKE query optimization device based on dynamic index provided in an embodiment of the present invention; Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation

[0019] To make the features and advantages of the present invention more apparent and understandable, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0020] In the following description, when referring to the accompanying drawings, the same numbers in different drawings denote the same or similar elements unless otherwise indicated. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present invention. Rather, they are merely examples of apparatuses and methods consistent with some aspects of the invention as detailed in the appended claims.

[0021] In the description of this invention, it should be understood that the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance. Those skilled in the art can understand the specific meaning of these terms in this invention based on the specific circumstances. Furthermore, in the description of this invention, unless otherwise stated, "multiple" refers to two or more. "And / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. The character " / " generally indicates that the preceding and following related objects have an "or" relationship.

[0022] As mentioned earlier, for operators like LIKE used for fuzzy string matching, traditional optimizers employ static predicate rewriting strategies to leverage indexes. For example, when encountering a condition like `col LIKE 'abc%'` (where the right-hand side is a constant string), the optimizer can rewrite it during query compilation as an equivalent lexicographical range comparison condition: `col>= 'abc' AND col<'abd'`. The rewritten condition can be directly used to generate the range scan key, allowing the query to utilize the index on the `col` column for efficient range scanning, rather than row-by-row matching. However, this optimization mechanism has a fundamental limitation: its effectiveness depends entirely on the determinism of the LIKE right-hand side pattern at compile time. In practical applications, especially in scenarios involving multi-table joins, the right-hand side of LIKE often contains variable expressions that include fields from other tables (e.g., `T2.text LIKE T1.pattern +'%'`). Since the value of `T1.pattern` is unknown at query compilation time, the optimizer cannot determine whether the variable value contains wildcards or their specific positions, thus making it unsafe to rewrite it as a deterministic range condition. In this situation, the system has to abandon index optimization and instead perform a full table scan or full index scan on table T2, filtering row by row using the original LIKE condition after retrieving all rows. This process not only results in a large amount of unnecessary disk I / O, but also quickly becomes a bottleneck for system performance when handling large-scale data or high-concurrency queries.

[0023] In view of this, the present invention provides a method, apparatus, and related equipment for optimizing LIKE queries based on dynamic indexes. The aim is to delay the critical step of predicate rewriting from compile time to execution time, enabling the system to construct a usable index scanning key in real time after obtaining the actual values ​​of variables. This achieves index acceleration for LIKE variable expression queries while maintaining the correctness of query semantics, effectively overcoming the shortcomings of existing static optimization frameworks.

[0024] Please see Figure 1 , Figure 1 This is an exemplary system architecture diagram of a LIKE query optimization method based on dynamic indexes provided in an embodiment of the present invention.

[0025] like Figure 1 As shown, the system architecture may include a terminal 101, a network 102, and a server 103. The network 102 serves as the medium for providing a communication link between the terminal 101 and the server 103. The network 102 may include various types of wired or wireless communication links, such as wired communication links including fiber optic cables, twisted-pair cables, or coaxial cables, and wireless communication links including Bluetooth communication links, Wireless-Fidelity (Wi-Fi) communication links, or microwave communication links, etc.

[0026] Terminal 101 can interact with server 103 via network 102 to receive messages from or send messages to server 103. Alternatively, terminal 101 can interact with server 103 via network 102 to receive messages or data sent to server 103 by other users. Terminal 101 can be hardware or software. When terminal 101 is hardware, it can be various electronic devices, including but not limited to smartwatches, smartphones, tablets, laptops, and desktop computers. When terminal 101 is software, it can be installed in the aforementioned electronic devices and can be implemented as multiple software programs or software modules (e.g., to provide distributed services) or as a single software program or software module; no specific limitation is made here.

[0027] In this embodiment of the invention, terminal 101 can identify the target LIKE predicate in the connection condition, wherein the left operand of the target LIKE predicate is the index column of the first data table, and the right operand contains an expression from the second data table; based on the target LIKE predicate, an optimized index scan plan corresponding to the first data table is generated, wherein the optimized index scan plan generates an index scan key based on the value of the expression in the second data table during execution; based on the value of the expression in the second data table, the actual matching pattern corresponding to the target LIKE predicate is determined; based on the actual matching pattern, an index scan key for accessing the index of the first data table is generated; and an index scan is performed on the first data table based on the index scan key to obtain data that meets the connection condition.

[0028] Server 103 can be a business server providing various services. It should be noted that server 103 can be either hardware or software. When server 103 is hardware, it can be implemented as a distributed server cluster consisting of multiple servers, or as a single server. When server 103 is software, it can be implemented as multiple software programs or software modules (e.g., used to provide distributed services), or as a single software program or software module; no specific limitations are made here.

[0029] Alternatively, the system architecture may not include server 103. In other words, server 103 may be an optional device in the embodiments of this specification. That is, the method provided in the embodiments of this specification can be applied to a system structure that only includes terminal 101. The embodiments of this invention do not limit this.

[0030] It should be understood that Figure 1 The number of terminals, networks, and servers shown is only illustrative; the number can be any number of terminals, networks, and servers depending on the implementation requirements.

[0031] Please see Figure 2 , Figure 2This is a flowchart illustrating a dynamic index-based LIKE query optimization method provided in an embodiment of the present invention. The execution entity of this embodiment can be an electronic device executing the dynamic index-based LIKE query optimization, a processor within the electronic device executing the dynamic index-based LIKE query optimization method, or a dynamic index-based LIKE query optimization service within the electronic device executing the dynamic index-based LIKE query optimization method. For ease of description, the following uses a processor within an electronic device as an example to describe the specific execution process of the dynamic index-based LIKE query optimization method.

[0032] like Figure 2 As shown, LIKE query optimization methods based on dynamic indexes can include at least the following: S201. Identify the target LIKE predicate in the join condition, wherein the left operand of the target LIKE predicate is the index column of the first data table, and the right operand contains an expression from the second data table.

[0033] Specifically, during the query optimization phase, the join conditions are traversed and parsed to identify specific LIKE predicates that meet the dynamic optimization criteria. More specifically, to accurately filter query patterns that cannot be effectively processed under the traditional static optimization framework but have the potential for performance improvement through dynamic index key generation, the optimizer first locates all LIKE predicates in the join conditions and performs a series of structured judgments for each such predicate: the primary judgment is to confirm whether the left operand of the LIKE operator references a column in the first data table to be joined (i.e., the inner table), and that column must have an ordered index, especially when it is the prefix column of the index definition; subsequently, the system performs in-depth syntactic analysis on the right operand to determine whether it is an expression containing a reference from a column in the second data table (i.e., the outer table) or composed of that column, such as "outer table column + '%'". This judgment is key to distinguishing between static constant patterns and dynamic variable patterns. This identification process ensures that a LIKE predicate is only marked as a target LIKE predicate when it simultaneously satisfies the two core conditions: "the left operand is an index column of an internal table" and "the right operand is an expression containing external variables".

[0034] In one possible implementation, if the left operand of the LIKE predicate is a B+ tree index column of the first data table, and the right operand of the LIKE predicate is an expression containing variables in the second data table, then the LIKE predicate is determined to be the target LIKE predicate. Please refer to [link to relevant documentation]. Figure 3 , Figure 3 A logical flowchart of the query optimization stage provided in an embodiment of the present invention, such as Figure 3As shown, specifically, the process first checks if the left side of the LIKE predicate is a column in the table and the right side is an expression. That is, the left operand is a B+ tree index column of the first data table, while the right operand is an expression containing variables in the second data table. Then, it checks whether the column to be matched belongs to the first data table (which is the inner table in the join), and further confirms whether the column has a B+ tree index. This is because the lexicographical arrangement of the B+ tree index is fundamental to generating the subsequent range scan key; without such an index, dynamic optimization is impossible. Next, the right operand of the LIKE predicate is analyzed, and its expression tree is parsed to explore whether there are direct references to columns of the second data table (i.e., the outer table) or calculated variables derived from the outer table columns. This step is crucial because it identifies the parts of the predicate that are uncertain at compile time; it is the existence of these variables that makes traditional static rewrite optimization ineffective. The system will officially designate a LIKE predicate as a "target LIKE predicate" only if both of the above conditions are met simultaneously—that is, the left operand is a B+ tree index column of the inner table, and the right operand expression contains a variable from the outer table.

[0035] S202. Based on the target LIKE predicate, generate an optimized index scan plan corresponding to the first data table. The optimized index scan plan generates the index scan key based on the value of the expression in the second data table during execution.

[0036] For details, please continue reading Figure 3 After identifying the target LIKE predicate, the query optimizer generates a novel, runtime-optimized index scan plan for the first data table (i.e., the internal table) involved in the predicate. This is done to build an access framework that can adaptively adjust in subsequent execution phases, addressing the problem of not being able to predetermine the precise scan key because the variable values ​​in the right operand expression are unknown at compile time. Specifically, the optimizer injects necessary metadata and dynamic generation logic into the plan. It records the structure of the target LIKE predicate itself, the syntax tree of the right operand expression, and the binding relationship between the expression and the columns of the second data table (i.e., the external table). It instructs the executor that whenever a row of data is retrieved from the external table, it must first calculate the specific value of the expression in the current row context. Subsequently, the plan guides the executor to dynamically derive the corresponding index scan key based on this real-time calculated value (e.g., by extracting string prefixes and generating range conditions based on lexicographical order). Therefore, the generated optimized index scan plan is essentially an instruction template with a delayed binding mechanism. It does not fix the specific value of the scan key, but encapsulates the rules and timing for generating the scan key, thereby enabling efficient and customized range scans of the index access of the first data table based on the real-time parameters provided by each row of the table.

[0037] S203. Determine the actual matching pattern corresponding to the target LIKE predicate based on the value taken from the second data table expression.

[0038] Specifically, when the query executor processes a join operation and starts fetching data row by row from the second data table (i.e., the outer table), the system dynamically determines the actual matching pattern corresponding to the target LIKE predicate for each row of the outer table data. Specifically, first, extract the specific data of the relevant columns in the current outer table row and substitute it into the pre-recorded expression structure for calculation, so as to parse an expression that is abstract and variable-dependent during compilation into a specific and definite string result at runtime. This string result corresponds to the "actual matching pattern", which fully reflects the specific rules for matching required in the context of the current row, and clearly includes the wildcard '%' and its specific position in the string.

[0039] S204. Generate an index scan key for accessing the index of the first data table according to the actual matching pattern.

[0040] Specifically, based on the calculated actual matching pattern, the system will dynamically generate a scan key for efficiently accessing the index of the first data table. The core logic of this generation process is to parse the actual matching pattern and convert it into one or more conditions that can directly drive the underlying index structure for efficient lookup. Specifically, first, extract the value of the target LIKE predicate expression and rewrite the target LIKE predicate. For example, extract the prefix string prefix before the wildcard '%' in the actual value of the expression on the right side of the LIKE predicate, and rewrite the LIKE predicate as col>= prefix and col<prefix+1, where prefix+1 represents the next string after prefix in lexicographical order. Analyze the structure of this pattern string, and based on the position of the wildcard in the target LIKE predicate, generate an index scan key for accessing the index of the first data table. By applying predefined conversion rules according to the position of the wildcard '%' in different cases, the original LIKE pattern used for fuzzy matching is logically equivalently transformed into an exact lookup condition or range lookup condition that can be used by the index. This condition is the final index scan key, which enables the index access path that cannot be determined during compilation due to unknown variables to be precisely constructed at runtime based on real-time data, thereby converting the access to the first data table from a full table or full index scan to a range index scan, achieving an improvement in query performance.

[0041] Furthermore, in one possible embodiment, when the wildcard is at the end of the string, i.e. the actual matching pattern is in the form of "prefix %", the range scan condition is constructed as col >= "prefix" AND col < "next lexicographical string". For example, if the expression in the target LIKE predicate evaluates to "abc%", it means that the wildcard is at the end of the string, so the range scan condition is constructed as col >= 'abc' and col < 'abd', and then an index scan key is generated based on this range scan condition. In another scenario, when the wildcard is in the middle of the string (i.e., the actual matching pattern is "prefix % suffix"), the range scan condition is constructed as `col >= "prefix" AND col < "next lexicographical string"`. For example, if the wildcard is in the middle of the string, such as when the expression in the target LIKE predicate evaluates to "abc%123", the range scan condition is constructed as `col >= 'abc' and col < 'abd'`. Furthermore, since this condition alone cannot guarantee that the suffix contains "123", the original predicate `col LIKE "abc%123"` needs to be appended as the index filter condition. In another scenario, when the string does not contain a wildcard (i.e., the actual matching pattern is "string"), the range scan condition is constructed as `col = "string"`. For example, if the expression in the target LIKE predicate evaluates to "abc", the range scan condition is constructed as `col = "abc"`, and then the index scan key is generated based on this range scan condition. In another case, when the wildcard is at the beginning of the string, i.e. the actual matching pattern is in the form of "% suffix", the actual matching pattern is used as the index filtering condition, and the index of the first data table is scanned. For example, when the expression in the target LIKE predicate evaluates to "%abc", the string without a prefix cannot be rewritten, so the original predicate col LIKE '%abc' is directly used as the index filtering condition.

[0042] S205. Perform an index scan on the first data table based on the index scan key to obtain data that meets the join conditions.

[0043] Specifically, using the dynamically generated index scan key from the previous step, a precise index scan operation is initiated on the determined index in the first data table (internal table). This scan key directly operates on the index structure (e.g., a B+ tree), enabling the storage engine to quickly locate one or more consecutive entries (i.e., a "range") in the index that satisfy the key value condition, and sequentially retrieve the data row positions (such as row IDs or clustered index keys) pointed to by these index entries. Subsequently, the system accesses the actual data pages based on this location information and retrieves the complete row data. These retrieved data rows, having undergone preliminary filtering at the index layer using the index scan key, constitute a candidate result set that meets the current join condition (i.e., the matching pattern generated based on the current rows of the second data table). This result set is immediately provided to the upper-level join operator for combination with the rows of the current second data table, ultimately forming a partial query result. In this way, each access to the first data table is transformed from an inefficient full traversal into an efficient, targeted index lookup.

[0044] This invention provides a dynamic index-based LIKE query optimization method. It dynamically generates index scan keys based on variable values, transforming full table or full index scans, which were previously unoptimizable due to variables in the right-hand side of the LIKE expression, into efficient index range scans. This eliminates the reliance of traditional database optimizers on compile-time determinism. This solution directly reduces unnecessary I / O operations and CPU filtering overhead, lowering query response time and resource consumption, resulting in an order-of-magnitude performance improvement for join queries involving LIKE variable expressions (especially large-scale table joins). Simultaneously, without altering SQL semantics or the user interface, it seamlessly enhances existing query optimization capabilities, improving the efficiency and scalability of database systems when handling real-time, dynamic fuzzy matching queries, effectively solving long-standing performance bottlenecks in such scenarios.

[0045] Please see Figure 4 , Figure 4 This is a structural block diagram of a LIKE query optimization device based on dynamic indexing, provided as an embodiment of the present invention. Figure 4 As shown: The LIKE query optimization device 400 based on dynamic indexing includes: an identification module 410, a first generation module 420, a determination module 430, a second generation module 440, and an index scanning module 450. Among them, The identification module 410 is used to identify the target LIKE predicate in the join condition, wherein the left operand of the target LIKE predicate is the index column of the first data table, and the right operand contains an expression from the second data table. The first generation module 420 is used to generate an optimized index scan plan corresponding to the first data table based on the target LIKE predicate. The optimized index scan plan generates the index scan key based on the value of the expression in the second data table during execution. The determination module 430 is used to determine the actual matching pattern corresponding to the target LIKE predicate based on the value of the expression in the second data table; The second generation module 440 is used to generate an index scan key for accessing the index of the first data table based on the actual matching pattern. The index scan module 450 is used to perform an index scan on the first data table based on the index scan key to obtain data that meets the join conditions.

[0046] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0047] Please see Figure 5 , Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Figure 5 As shown, the electronic device 500 may include: at least one processor 501, at least one network interface 504, user interface 503, memory 505, and at least one communication bus 502.

[0048] The communication bus 502 is used to enable communication between these components.

[0049] The user interface 503 may include a display screen and a camera.

[0050] The network interface 504 may optionally include a standard wired interface or a wireless interface (such as a Wi-Fi interface).

[0051] The processor 501 may include one or more processing cores. The processor 501 connects to various parts within the electronic device 500 using various interfaces and lines, and performs various functions and processes data by running or executing instructions, programs, code sets, or instruction sets stored in the memory 505, and by calling data stored in the memory 505. Optionally, the processor 501 may be implemented using at least one hardware form of Digital Signal Processing (DSP), Field-Programmable Gate Array (FPGA), or Programmable Logic Array (PLA). The processor 501 may integrate one or a combination of several of the following: Central Processing Unit (CPU), Graphics Processing Unit (GPU), and modem. The CPU primarily handles the operating system, user interface, and applications; the GPU is responsible for rendering and drawing the content required for display; and the modem handles wireless communication. It is understood that the modem may also not be integrated into the processor 501 and may be implemented as a separate chip.

[0052] The memory 505 may include random access memory (RAM) or read-only memory. Optionally, the memory 505 may include a non-transitory computer-readable storage medium. The memory 505 can be used to store instructions, programs, code, code sets, or instruction sets. The memory 505 may include a program storage area and a data storage area, wherein the program storage area may store instructions for implementing an operating system, instructions for at least one function (such as touch function, sound playback function, image playback function, etc.), instructions for implementing the above-described method embodiments, etc.; the data storage area may store data involved in the above-described method embodiments, etc. Optionally, the memory 505 may also be at least one storage device located remotely from the aforementioned processor 501. Figure 5 As shown, the memory 505, which serves as a computer storage medium, may include an operating system, a network communication module, a user interface module, and a LIKE query optimization application based on dynamic indexes.

[0053] exist Figure 5In the illustrated electronic device 500, the user interface 503 is mainly used to provide an input interface for the user and obtain user input data; while the processor 501 can be used to call the dynamic index-based LIKE query optimization application stored in the memory 505, and specifically perform the following operations: identify the target LIKE predicate in the join condition, wherein the left operand of the target LIKE predicate is the index column of the first data table, and the right operand contains an expression from the second data table; generate an optimized index scan plan corresponding to the first data table based on the target LIKE predicate, wherein the optimized index scan plan generates an index scan key based on the value of the expression in the second data table during execution; determine the actual matching pattern corresponding to the target LIKE predicate based on the value of the expression in the second data table; generate an index scan key for accessing the index of the first data table based on the actual matching pattern; and perform an index scan on the first data table based on the index scan key to obtain data that meets the join condition.

[0054] In some possible embodiments, processor 501 executes the target LIKE predicate in the identification join condition, specifically for performing: If the left operand of the LIKE predicate is a B+ tree index column of the first data table, and the right operand of the LIKE predicate is an expression containing variables in the second data table, then the LIKE predicate is determined to be the target LIKE predicate.

[0055] In some possible embodiments, processor 501 performs an index scan key to access the first data table index based on the actual matching pattern, specifically for performing: Extract the values ​​of the target LIKE predicate expression and rewrite the target LIKE predicate; based on the position of the wildcard in the target LIKE predicate, generate an index scan key for accessing the index of the first data table.

[0056] In some possible embodiments, processor 501 performs an index scan key based on the position of the wildcard in the target LIKE predicate to access the first data table index, specifically for performing: When the wildcard is at the end of the string, that is, the actual matching pattern is in the form of "prefix %", the range scan condition is constructed as col >= "prefix" AND col < "next lexicographical string"; Based on the range scan conditions, an index scan key is generated to access the index of the first data table.

[0057] In some possible embodiments, processor 501 performs an index scan key based on the position of the wildcard in the target LIKE predicate to access the first data table index, specifically for performing: When the wildcard is in the middle of the string, that is, the actual matching pattern is in the form of "prefix % suffix", the range scanning condition is constructed as col >= "prefix" AND col < "next lexicographical string"; Based on the range scan conditions, generate an index scan key for accessing the index of the first data table; Processor 501 performs an index scan on the first data table based on the index scan key to obtain data that meets the join conditions, specifically for the following: Use the actual matching pattern as the index filtering condition, and perform an index scan based on the index scan key; The scanned index entries are checked for matching based on the index filtering conditions.

[0058] In some possible embodiments, processor 501 performs an index scan key based on the position of the wildcard in the target LIKE predicate to access the first data table index, specifically for performing: When no wildcard is included, i.e. the actual matching pattern is in the form of "string", the range scan condition is constructed as col="string"; Based on the range scan conditions, an index scan key is generated to access the index of the first data table.

[0059] In some possible embodiments, processor 501 performs an index scan key based on the position of the wildcard in the target LIKE predicate to access the first data table index, specifically for performing: When the wildcard is at the beginning of the string, that is, the actual matching pattern is in the form of "% suffix", the actual matching pattern is used as the index filtering condition, and the index of the first data table is scanned. Processor 501 performs an index scan on the first data table based on the index scan key to obtain data that meets the join conditions, specifically for the following: The scanned index entries are checked for matching based on the index filtering conditions.

[0060] This invention also provides a computer-readable storage medium storing instructions that, when executed on a computer or processor, cause the computer or processor to perform the above-described instructions. Figure 2 One or more steps in the illustrated embodiment. If the constituent modules of the above-described dynamic index-based LIKE query optimization device are implemented as software functional units and sold or used as independent products, they can be stored in the computer-readable storage medium.

[0061] In the above embodiments, implementation can be achieved entirely or partially through software, hardware, firmware, or any combination thereof. When implemented using software, it can be implemented entirely or partially as a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions described in the embodiments of the present invention are generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted through the computer-readable storage medium. The computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, Digital Subscriber Line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium can be any available medium accessible to a computer or a data storage device such as a server or data center that integrates one or more available media. The available media may be magnetic media (e.g., floppy disks, hard disks, magnetic tapes), optical media (e.g., digital versatile discs (DVDs)), or semiconductor media (e.g., solid state disks (SSDs)).

[0062] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. This program can be stored in a computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. The aforementioned storage medium includes various media capable of storing program code, such as read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks. Unless otherwise specified, the technical features of this embodiment and its implementation schemes can be combined arbitrarily.

[0063] The above description of the disclosed embodiments enables those skilled in the art to make or use the invention. It will be apparent to those skilled in the art that various modifications can be made to these embodiments, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A LIKE query optimization method based on dynamic indexes, characterized in that, The method includes: Identify the target LIKE predicate in the join condition, wherein the left operand of the target LIKE predicate is the index column of the first data table, and the right operand contains an expression from the second data table; Based on the target LIKE predicate, an optimized index scan plan corresponding to the first data table is generated. The optimized index scan plan generates the index scan key based on the value of the expression in the second data table during execution. Based on the value of the expression in the second data table, the actual matching pattern corresponding to the target LIKE predicate is determined; Based on the actual matching pattern, an index scan key is generated for accessing the index of the first data table; An index scan is performed on the first data table based on the index scan key to obtain data that meets the join conditions.

2. The method as described in claim 1, characterized in that, The target LIKE predicate in the identification connection condition includes: If the left operand of the LIKE predicate is a B+ tree index column of the first data table, and the right operand of the LIKE predicate is an expression containing variables in the second data table, then the LIKE predicate is determined to be the target LIKE predicate.

3. The method as described in claim 1 or 2, characterized in that, The step of generating an index scan key for accessing the first data table index based on the actual matching pattern includes: Extract the values ​​of the target LIKE predicate expression and rewrite the target LIKE predicate; Based on the position of the wildcard in the target LIKE predicate, an index scan key is generated for accessing the index of the first data table.

4. The method as described in claim 3, characterized in that, The step of generating an index scan key for accessing the first data table index based on the position of the wildcard in the target LIKE predicate includes: When the wildcard is at the end of the string, that is, when the actual matching pattern is in the form of "prefix %", the range scanning condition is constructed as col >="prefix" AND col < "next lexicographical string"; Based on the range scan conditions, an index scan key is generated for accessing the index of the first data table.

5. The method as described in claim 3, characterized in that, The step of generating an index scan key for accessing the first data table index based on the position of the wildcard in the target LIKE predicate includes: When the wildcard is in the middle of the string, that is, when the actual matching pattern is in the form of "prefix % suffix", the range scanning condition is constructed as col >="prefix" AND col < "next lexicographical string"; Based on the range scan conditions, an index scan key for accessing the index of the first data table is generated; The step of performing an index scan on the first data table based on the index scan key to obtain data that meets the join conditions includes: The actual matching pattern is used as the index filtering condition, and an index scan is performed based on the index scan key. The scanned index entries are checked for matching based on the index filtering conditions.

6. The method as described in claim 3, characterized in that, The step of generating an index scan key for accessing the first data table index based on the position of the wildcard in the target LIKE predicate includes: When the wildcard is not included, i.e. the actual matching pattern is in the form of "string", the range scanning condition is constructed as col="string"; Based on the range scan conditions, an index scan key is generated for accessing the index of the first data table.

7. The method as described in claim 3, characterized in that, The step of generating an index scan key for accessing the first data table index based on the position of the wildcard in the target LIKE predicate includes: When the wildcard is at the beginning of the string, that is, when the actual matching pattern is in the form of "% suffix", the actual matching pattern is used as the index filtering condition to perform a scan on the index of the first data table. The step of performing an index scan on the first data table based on the index scan key to obtain data that meets the join conditions includes: The scanned index entries are checked for matching based on the index filtering conditions.

8. A LIKE query optimization device based on dynamic indexes, characterized in that, The device includes: The identification module is used to identify the target LIKE predicate in the join condition, wherein the left operand of the target LIKE predicate is the index column of the first data table, and the right operand contains an expression from the second data table; The first generation module is used to generate an optimized index scan plan corresponding to the first data table based on the target LIKE predicate. The optimized index scan plan generates an index scan key based on the value of the expression in the second data table during execution. The determination module is used to determine the actual matching pattern corresponding to the target LIKE predicate based on the value of the expression in the second data table; The second generation module is used to generate an index scan key for accessing the index of the first data table based on the actual matching pattern. The index scanning module is used to perform an index scan on the first data table based on the index scanning key to obtain data that meets the join conditions.

9. A computer storage medium, characterized in that, The computer storage medium stores a plurality of instructions adapted for loading by a processor and executing the steps of the method as described in any one of claims 1 to 7.

10. An electronic device, characterized in that, It includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the method as described in any one of claims 1 to 7.

Images