High-performance experience in Large-Scale Systems-Data Query and paging

This article discusses a feasible and efficient solution for searching and paging large data tables (with more than 10 million records.

First, an appropriate index is required.

Without the support of indexes, it is incredible to query large data tables. The key point is how to create an index?

1. Create a correct clustered index ). Because the leaf node of the clustered index is the record itself, it is critical to select which index is the clustered index. Scanning records through clustered indexes is faster.

2. summarize common single query conditions or comprehensive query conditions based on your system requirements. Create a single column index for common single query conditions and create common comprehensive query conditions.Joint Index.

3. Pay attention to the following points about how the database query engine uses indexes:

(1) for a single column index, as long as the index column appears in the condition column, the index query can be used no matter where it is located.

(2) The Union index can be used if the first or all column of the Union index appears in the query condition.

(3) as long as the conditions in the condition column are connected together, the Union index is used no matter before or after the condition column.

(4) The first column of the Union index does not appear in the query condition, and the second or third column of the Union index does not use the Union Index query.

Next, let's see how to paging.

1. Use an index (or a joint index) to into a temporary table (only one column corresponds to the primary key of the target table) for the record that meets the condition ).

2. Count (*) temporary table to obtain the total number of records meeting the conditions.

3. Obtain the primary key value set on page N from the temporary table.

4. Retrieve the corresponding records from the target table based on the set of primary key values to form the desired page.

5. Release the temporary table.

This method is used for paging query. If the number of records meeting the condition is less than tens of thousands, paging query can return within 1 second.

Last, we would like to remind you not to execute the select count (*) from Table statement without conditions on the big data table easily. This operation takes a long time, in addition, the S lock will be applied to the target table during the scan. During this period, the insert, update, and delete operations on the target table will be blocked, which may lead to timeout for the insert, update, and delete operations.

 

Note: This article is based on my SQL Server experience and may provide a better solution in Oracle :)