Method for quickly indexing database

Abstract

Along with the expansion of the database scale and the enhancement of functions, quick indexing of multi-dimensional data is more concerned, and improvement of the database performance is particularly important. The invention provides an innovative rapid indexing method, and aims to efficiently retrieve data in a database under specific query conditions. By deeply analyzing multi-dimensional data in a data table, a division and mapping function of a data grid structure is designed, the multi-dimensional data is effectively mapped to a one-dimensional space, and a piecewise linear model is constructed for one-dimensional indexing processing. The data retrieval efficiency is remarkably improved through the series of technical innovations. Through verification on an experimental platform, the superior performance of the index structure method is displayed, and the problem that the traditional index construction and maintenance cost is high is solved. The method has a wide application prospect, and plays an important role in the rapid indexing process of the database.

Description

Technical Field

[0001] The present invention belongs to the application of database indexing, and mainly can quickly retrieve and return specific data when executing a query statement, that is, it can perform one-dimensional mapping on multidimensional data according to the query conditions, use machine learning to replace the traditional index structure, and improve the database retrieval efficiency. Background Art

[0002] With the continuous development of big data, the field of database indexing has also received much attention. Fast indexing technology for complex data can not only provide decision support for various fields, but also ensure data security and accuracy. Traditional one-dimensional indexing has certain advantages under certain query conditions. However, it is not feasible to build different index structures for each query requirement. In addition, with the increase in data volume, traditional indexes grow exponentially in terms of storage consumption and structural maintenance costs. However, with the increase in data volume and data dimension, ordinary index structures gradually cannot meet the indexing needs of the real world. Machine learning has an intelligent and efficient data processing method. Introducing machine learning into the field of database indexing can not only improve database security performance, but also improve database indexing efficiency. However, most of the existing learning indexing technologies process one-dimensional data, and very few scholars apply machine learning to the indexing process of multidimensional data. Moreover, the existing multidimensional learning indexes have extremely complex structures, resulting in no significant improvement in indexing efficiency compared to traditional index structures. Summary of the invention

[0003] In view of the shortcomings of the prior art, the present invention aims to provide a learning indexing method that can build a simple indexing structure and efficiently execute the indexing process when facing the multidimensional data indexing requirements, thereby greatly improving the database indexing efficiency. To solve the above technical problems, the present invention adopts the following technical solutions: A method for fast indexing of a database comprises the following steps: (1) Loading data from the database and storing it; The data in the database is diverse, often with more than two columns of values. When the indexing process needs to be performed, the data is loaded into the memory and stored. (2) The distribution of each dimension of the learning data; When data has multiple dimensions, that is, the data table in the database contains multiple columns, in order to make the constructed index structure more effective, it is necessary to first learn the distribution of data in each dimension. This process traverses each dimension of the data and uses a curve to fit the data key value and its actual position in the database. This curve represents the data distribution. (3) Divide the data grid structure; After traversing all the data and learning its distribution in each dimension, it is necessary to divide the data. Dividing the grid according to the data distribution is to take into account the distribution of the data so that the index structure can evenly distribute database resources. The unique identification id value of each grid is: Among them, d is the data dimension, is the offset of the grid in dimension i, is the offset of dimension i itself. (4) Design a mapping function to map multidimensional data to one dimension; Considering the complexity of multidimensional data, this method designs a mapping function to map multidimensional data to one-dimensional space. This allows data that are adjacent in multidimensional space to maintain an adjacent relationship in one-dimensional space, which helps to achieve excellent performance when executing the query process. The mapping function designed in this step is: M(x)=id+f Among them, id is the unique identifier of each grid, which ensures the uniqueness of the data mapping values ​​within different grids. u i is the measure of the data in the i-th dimension within the grid. The mapping function designed in this step is partially monotonic, that is, the mapping function value increases with the increase of the values ​​of each dimension, which ensures that the data mapping value in the upper right corner of the query range is always greater than the data mapping value in the lower left corner. This feature ensures the query efficiency and the integrity of the query results. (5) Design a piecewise linear model to perform one-dimensional indexing; After mapping all data to one dimension, in order to execute database query statements, fast indexing is required in one-dimensional space. This method constructs a piecewise linear model as an alternative to traditional one-dimensional indexing. The motivation for using a linear model is its fast indexing process and less storage space consumption. Each segment in a linear model only stores the slope and intercept parameters. It is difficult to accurately predict the true position of a data point using only a linear model. To solve this problem, this method selects a piecewise linear model consisting of several segments, which can ensure that the true position of the data can be quickly retrieved within the error range. First, this method uses a linear fitting function To fit the relationship between the mapping value and the actual position, where a is the slope of the linear function and b is the intercept of the linear function. is the actual position of the mapped value estimated on the linear fitting function, and x is the data key value. The purpose of this step is to find the optimal a and b values ​​to better fit the relationship between the mapped value and the actual position. Secondly, by traversing all the mapped values, the mapped value x iand the actual position y i As input, the optimal a and b values ​​are output through the least squares method. The formula of the fitting loss function is: Substitution get: When the loss function L value is the minimum, the a and b values ​​are the optimal solutions. Using the least squares method to derive the a and b values, we get: The linear fitting function It can fit the relationship between the mapped value and the actual position well. Since it is difficult to fit the relationship between all mapping values ​​and actual positions with one line segment, this step uses multiple line segments for fitting. First, a fitting error ε is set. If the deviation between the actual position of the mapping value and the predicted value is greater than ε, a new line segment is formed until all mapping values ​​are fitted. After traversing all mapping values, a certain number of linear line segments will be generated, which are connected end to end to form a piecewise linear model. Since this piecewise linear model only stores the slope, intercept, and starting mapping value of each line segment, it can save a lot of storage space in the actual construction process. (6) Perform range queries and K nearest neighbor queries. There are two most common cases of database query: range query is to search for multidimensional data within a certain range and return all data within this range; K nearest neighbor query is to query the nearest k data around a data point in a given data space. This method executes the above two query processes according to the designed index structure. The results show that compared with the traditional index structure, this index structure shows extremely high query performance. The advantages and positive effects of the present invention are: (1) The present invention highlights the advantages of fast execution of multidimensional data query statements in a database, does not rely on complete prior knowledge, and makes full use of language writing and running effects for analysis. (2) The method proposed in the present invention can solve the performance problem of low efficiency of multidimensional data indexing in a variable database, and can provide database operators with fast, accurate and efficient retrieval results, thereby improving the accuracy, efficiency and security of decision-making. (3) The implementation of this method can be realized by programming in the basic operating system without introducing additional equipment. (4) This method has been verified through a large number of experiments, which effectively improves the reliability of the method. BRIEF DESCRIPTION OF THE DRAWINGS

[0004] Figure 1It is a flow chart of a database rapid indexing method in a specific implementation mode of the present invention; Figure 2 It is a distribution diagram of each dimension of data in a specific implementation mode of the present invention; Figure 3 is a grid structure diagram in a specific implementation manner of the present invention; Figure 4 is a mapping function diagram in a specific implementation manner of the present invention; Figure 5 is a piecewise linear model diagram in a specific implementation manner of the present invention; Figure 6 It is a diagram showing the effect of executing a range query on the index structure in a specific implementation mode of the present invention; Figure 7 This is a diagram showing the effect of executing K nearest neighbor query on the index structure in a specific implementation manner of the present invention. DETAILED DESCRIPTION

[0005] This example takes the database multidimensional data indexing process as the research object and describes the implementation of the present invention in detail. In view of the multidimensional characteristics of the data in the database, the rapid indexing requirements of the analysis structure are analyzed through language writing, so that the database can execute the indexing process when facing multidimensional data and quickly return the search results, thereby improving the operability, readability and efficiency of the database. In order to make the purpose and technical solution of the present invention clearer, the specific implementation steps of the present invention are described in detail below with reference to the accompanying drawings. Figure 1 , the implementation steps are: (1) Load the data in the database and store it; open the data table in the database, click Load Data, obtain the data information in the current data table, including data dimensions, primary key columns, and data of all other columns, and store it in the collection Data of the index data class. (2) Learn the distribution of each dimension of the data; traverse each dimension of the data and construct Figure 2 The data distribution diagram of each dimension data is as follows: traverse all the data on this dimension, plot the data key value and its position in the data table on the coordinate axis, and fit these points with a curve. (3) Divide the data grid structure; considering that the data in the data table cannot be completely evenly distributed, this method takes data distribution into consideration when dividing the grid, in order to ensure balanced distribution of database resources. Small areas are divided where data is densely distributed, and large areas are divided where data is sparsely distributed. In this way, the amount of data in each grid is evenly distributed, which is more conducive to the execution of a fast indexing process. Grid division results reference Figure 3 , the specific steps are: Determine the number of data partitions on each dimension based on the database cost model. When the number of partitions is too small, each cell in the grid structure will contain a lot of data, which will have a very bad impact on index performance when executing the query process. This is when performing an exact search (such as a binary search or an exponential search), the final predicted search interval will be very large and take a long time. When the number of partitions is too large, a lot of time and memory will be wasted in the process of building the grid structure, and unnecessary time will be spent on the process before executing the exact search during the query process. This method is based on the database cost model: Cost=Page+w*Rows Where Page represents the number of data pages accessed, w represents the weight of each row of data, which can be set according to the importance of the data or the query frequency, and Rows represents the number of data rows in each partition. Set the initial number of partitions P for each dimension 0 , by preliminarily analyzing the dimensional distribution of the data, set a reasonable initial value. For each partitioning strategy, the amount of data is N, then the number of rows contained in each partition can be calculated: The number of pages and total cost are calculated based on the number of partitions, where the number of data rows contained in each page is Const: Page=Const*P 0 Compare the cost of calculating different numbers of partitions P, record the cost under each strategy, and select the number of partitions with the lowest cost from the calculation results as the number of partitions P finally selected for the grid structure. Traverse each dimension and divide each dimension of the data. After determining the number of partitions for each dimension, divide the data in each dimension. First, obtain the size of all data Data; then calculate the amount of data in each partition size = Data.size() / P; then, traverse the data of each dimension, and divide the area when traversing to a point that is a multiple of size. Iterate and divide each dimension. After the traversal is completed, the grid structure is obtained. (4) Design a mapping function to map multidimensional data to one dimension. The mapping function must ensure partial monotonicity and spatial proximity, so as to reduce unnecessary search time during query execution. For the specific characteristics of some data after being mapped by the mapping function, refer to Figure 4 . Encode each cell in the grid structure, the encoding value is unique, and the unique ID value of each grid is obtained. Encode the data falling inside the cell, and the encoding value is the measurement value u of the data point relative to the cell in each dimension i product. The mapping function is to add the cell code value to the data point code value: (5) Design a piecewise linear model to perform one-dimensional indexing; since it is impossible for a linear model to completely fit all data points and their positions, this section sets the maximum allowable error for fitting to ε. Sort the mapping values. Traverse all mapping values ​​and their positions. If the error between the straight line fitted by all current mapping values ​​and the position of the next mapping value does not exceed ε, then add the next mapping value to the current straight line and continue to traverse the next mapping value; if the error exceeds ε, then the next mapping point is the starting point of a new straight line, and continue to traverse the next mapping value. After traversing all mapping values, we get the following: Figure 5 The piecewise linear model consists of several line segments shown. (6) Perform range query and K nearest neighbor query. In order to verify the data index structure proposed by this method, range query and K nearest neighbor query experiments were performed. The specific process and results are as follows: Figure 6 The effect of this structure on range query is shown in the figure. For a range query, the mapping value range of the query box is first determined by the mapping function, and the positions of the minimum and maximum mapping values ​​in the one-dimensional space are determined by the piecewise linear model. An exact search is performed within this position interval to return all data within the query range. Figure 7 The effect of this structure executing K-nearest neighbor query is shown in the figure. For a K-nearest neighbor query, the mapping value of the query point and its position in the grid structure are first determined by the mapping function, the search range is initialized, and then the position of the minimum and maximum mapping values ​​in this range in one-dimensional space is determined by the piecewise linear model, and an accurate search is performed within this position interval until k data points are searched and the retrieval results are returned. According to the experimental results, the data index structure proposed by this method can improve the retrieval efficiency exponentially compared with the traditional index, and can show extremely high query efficiency when facing multidimensional data, which helps to improve database performance. The above-mentioned embodiments are only preferred specific implementation methods. This article uses the description of individual implementations to help understand the method and core ideas of the present invention. From the implementation and actual effect of this example, it can be seen that the invention realizes the rapid retrieval of multidimensional data. By introducing machine learning, the effectiveness of the index structure is improved, which helps to improve the performance of the database and greatly solves the problem of inefficient retrieval of complex data. Modifications to the technical solutions recorded in the above-mentioned embodiments or equivalent replacement of some indicators should be included in the protection scope of the present invention.

Claims

1. A method for rapid database indexing, characterized in that: The data indexing method comprises: (1) Obtain all data in the database, including data dimensions, primary key columns, and other data columns, and store them; (2) Learn the distribution of each dimension of the data and use curves to fit the data key values ​​and their position distribution; (3) Divide the data grid structure. Divide the grid according to the data distribution to take into account the distribution of the data so that the index structure can evenly distribute database resources; (4) Design a mapping function to map multidimensional data to one-dimensional space. This ensures that data that are adjacent in the multidimensional space also maintain an adjacent relationship in the one-dimensional space. At the same time, the mapping function value increases as the value of each dimension increases, ensuring query efficiency and the integrity of the query results. (5) Design a piecewise linear model to perform one-dimensional indexing to ensure that the true location of the data can be quickly retrieved within the error range; (6) Perform range query and K-nearest neighbor query. Range query is to search for multidimensional data within a certain range; K-nearest neighbor query is to query the nearest k data around a data point. The query results show that compared with the traditional index structure, this index structure has a very high query performance.

2. A method for rapid database indexing as claimed in claim 1, characterized in that: The method for dividing the data grid structure in step (3) is as follows: Considering that the data in the data table cannot be completely evenly distributed, this method takes data distribution into consideration when dividing the grids to ensure balanced distribution of database resources. Smaller areas are divided where data is densely distributed, and larger areas are divided where data is sparsely distributed. This way, the amount of data in each grid is evenly distributed, which is more conducive to the fast indexing process. The specific steps are: ① Determine the number of data partitions in each dimension based on the database cost model. When the number of partitions is too small, each cell in the grid structure will contain a lot of data, which will have a very bad impact on index performance when executing the query process. This is when performing an exact search (such as a binary search or an exponential search), the final predicted search interval will be very large and take a long time. When the number of partitions is too large, a lot of time and memory will be wasted in the process of building the grid structure, and unnecessary time will be spent on the process before executing the exact search during the query process. This method determines the number of data partitions for each dimension based on the database cost model. For each partitioning strategy, the database optimizer will evaluate the space and time consumption of the indexing process under this strategy, and select a partitioning strategy with the lowest consumption as the number of partitions P finally selected by the grid structure. ② Traverse each dimension and divide each dimension of the data. After determining the number of partitions for each dimension, you need to partition the data for each dimension. First, get the size of all the data. Then, calculate the amount of data in each partition. Then, traverse the data of each dimension, and when the traversal reaches a point that is a multiple of the amount of data in the area, divide the area. Iterate the division of each dimension. ③After the traversal is completed, the grid structure is obtained.

3. A method for rapid database indexing as claimed in claim 1, characterized in that: In step (5), a piecewise linear model is designed to perform a one-dimensional indexing method: Since it is impossible for a linear model to completely fit all data points and their locations, this section sets the maximum allowable error for fitting to ε. ① Sort the mapping values. ② Traverse all mapping values ​​and their positions. If the error between the straight line fitted by all current mapping values ​​and the position of the next mapping value does not exceed the set maximum error value, add the next mapping value to the current straight line and continue to traverse the next mapping value; if the error exceeds the maximum error value, the next mapping point is the starting point of a new straight line and continue to traverse the next mapping value. ③After traversing all mapping values, a piecewise linear model consisting of several line segments is obtained.

4. A method for rapid database indexing as claimed in claim 1, characterized in that: In step (6), range query and K nearest neighbor query method are performed: In order to verify the data index structure proposed by this method, range query and K nearest neighbor query experiments were carried out. The specific process and results are as follows: ① For a range query, first determine the mapping value range of the query box through the mapping function, and then determine the positions of the minimum mapping value and the maximum mapping value in the one-dimensional space through the piecewise linear model. Perform an exact search within this position interval and return all data within the query range. ② For a K-nearest neighbor query, first determine the mapping value of the query point and its position in the grid structure through the mapping function, initialize the search range, and then use the piecewise linear model to determine the positions of the minimum and maximum mapping values ​​in this range in one-dimensional space, perform an exact search within this position interval, until k data points are found, and return the retrieval results. According to the experimental results, the data index structure proposed by this method can improve the retrieval efficiency exponentially compared with the traditional index, can show extremely high query efficiency when facing multidimensional data, and helps to improve database performance.

Images