How to use the table storage to improve performance recommendation _ database other

First, Dong describes what he thinks is optimization: first, the quickest way to do anything is to do nothing.

▲ Alipay Senior Database architect Dong

Second, do not access unnecessary data: the use of B*tree/hash and other methods to locate the necessary data. Store data separately by using column store or table. Use the Bloom filter algorithm to exclude null-value queries.

Third, reasonable use of hardware to enhance access efficiency: Use caching to eliminate duplication of data access. Use bulk processing to reduce disk seek operations. Use batch processing to reduce round trips to the network. Use SSD to improve disk access efficiency.

　　The relationship between response time and throughput

1, performance. Measure the speed or efficiency of accomplishing a particular task.

2, Response time. Measure how long the system is interacting with the user to be able to send a response.

3, throughput. Measure the amount of work a system can accomplish in a unit of time.

　　

▲ Reaction time

　　

▲ Access characteristics of traditional disks

B*tree Optimization Data Access Introduction

　　

▲b*tree Optimizing data Access

B*tree optimized data access simulation scenario

　　

▲b*tree optimized data access simulation scenario

Dong through Alibaba's real application scenario, describes how to use the table storage to improve performance.

First, the scene introduction:

1, table verybigtable contains 30 columns

2, the record number of the table is 50,000,000

3, the average per user is about 300

4, of which 2 columns belong to the detailed Description field, the average length is 2k

5, the total length of other columns on average 250 bytes

6, the query on this table has two modes

7, list the main information in the table (20 per time, not including details, 90% of the query)

8, view the record details (10% of the query)

9, Save with Oracle database, default block_size (8k)

Second, the request:

1, to optimize the business

2, analyze the data to persuade the development department to implement this optimization

Third, performance analysis

1. Number of records per block

8192 * 0.80 (1)/250 = 25.5 (Main table)

8192 * 0.80/2000 = 3.27 (Details table)

8192 * 0.80/(2000 + 250) = 2.91

2, access to the logical IO (memory block access)

Query price for list

After the improvement = (300/25.5) * y + 4 + x = 4 + x + 11.8y = 4 (2) + 7 (3) + 11.8 * 1.5 (4) = 28.7

Before the improvement = (300/2.91) * y + 4 + x = 4 + x + 103.y = 4 + 7 + 103 * 1.5 = 165.5

3, access to the physical read (disk block access)

Query cost for list (logical IO * (in-rate hit))

After the improvement = 28.7 * (1–0.85 (5)) = 4.305

Improved before =165.5 * (1–0.85) = 24.825

4, Access time (MS)

Pre-improvement = Logical IO time + physical IO time = 28.7 * 0.01 (6) + 4.305 * 7 (7) = 30.422ms
After improvement = Logical IO time + physical IO time = 165.5 * 0.01 + 24.825 * 7 = 175.43ms