A Preliminary Study on partitioned tables

I wrote a blog post last week about partition tables: http://www.bkjia.com/database/201202/119377.html.

However, I found that I did not write much. When your query conditions are not closely related to partition columns, partition tables will not help you improve your efficiency.

 

 

Figure 1

Figure 2

I partition by area_id. The execution plan shown in Figure 1 is as follows:

Figure 2 execution plan:

Create a table. The data in this table is the same as that in test, but there is no partition. Execute the statement in 1 to view its execution plan:

 

Obviously, the execution plan of the PARTITION table has a partition list all, which significantly increases the CPU usage. Let's look at the execution plan of the SQL statement in test111 in Figure 2:

 

Obviously, the partition list single is missing, but the CPU usage has not changed. Of course, my table is very small. If the data volume exceeds 10 million, then we can see the benefits.

From the comparison above, we can clearly see that the use of partition tables depends on the actual application requirements. If the stored procedure always queries data based on a certain condition, as shown in figure 2, area_id is always required for each query, when creating a table, you can consider partitioning by area_id. However, if you do not have any regular queries, you may be able to partition them.

For this blog post, the younger brother suddenly went out and kept inserting the table. Now he has created a 3145728 test table and a test111 table. The two tables share the same data. test has partitions and test111 does not. Let's look at the execution plan. The first is SQL:

SELECT * FROM TEST a WHERE a.item_id = 1AND a.area_id = 290;

Copy code

 

SELECT * FROM TEST111 a WHERE a.item_id = 1AND a.area_id = 290;

Copy code

Then the execution plan is:

1

2

After using a partition table, although the COST of the CPU is increased, both ROWS and BYTES are greatly reduced. Double the number of tables. The ROWS ratio of partitioned and non-Partitioned Tables reaches 2159 K: 10 M, and the BYTES ratio also reaches 121 M: 594 M, and the cpu cost ratio is 14487: 8847. God, partition tables are excellent in reducing the read volume, but they are terrible in increasing the cpu cost.

I have read a book from guobiao before, which says optimizing SQL mainly reduces its physical reading. But I think if we can reduce the ROWS and BYTES here, for a database processor in a small machine environment, a higher CPU COST is understandable.

Please leave a message to correct the problem.