How to optimize horizontal Partition in MySQL

In this issue, the editor will bring you about how to optimize the horizontal zoning in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

The table structure is as follows:

2W multiple rows of data have been inserted here for testing.

Take a look at this query.

SELECT * FROM T1 WHERE system_type IN (1jue 2)

UNION ALL

SELECT * FROM T1 WHERE system_type = 3

This statement filters the system_type field twice and then UNION ALL it once. But I don't know, in fact, a total of three full table scans were performed on the two partitions.

Let's change it to this:

SELECT * FROM T1 WHERE system_type IN (1pm 3)

UNION ALL

SELECT * FROM T1 WHERE system_type = 2

In a seemingly simple change, we reduced the number of scans of the two partitions from three to two. But in this way, it is also very expensive, can you get rid of the UNION ALL? Yes, of course.

SELECT * FROM T1 WHERE system_type > 0 and system_type < 4

UNION ALL has been removed, but the problem encountered is that the scan of the partition has become a range search, and the upper and lower limits are not fixed, relatively speaking, there is still room for optimization.

Let's change the filter criteria for the system_type column to the following:

SELECT * FROM T1 WHERE system_type in (1meme 2jue 3)

Id select_type table partitions type possible_keys key key_len ref rows Extra

1 SIMPLE T1 r0 ALL R1 ALL\ N\ N\ N 17719 Using where

Now, it's still range scanning, but the upper and lower limits are clear. In this way, for scanning partitions, the upper and lower limits can be found quickly, which is faster and less expensive than before.

But it seems that it can be optimized, although the upper and lower limits of the filter conditions are obvious, but the scan within the area is still fully partitioned (equivalent to the entire table of the entire table. ).

OK, now index this column. ALTER TABLE t1 ANALYZE PARTITION r0,r1

SELECT * FROM T1 WHERE system_type in (1meme 2jue 3)

Id select_type table partitions type possible_keys key key_len ref rows Extra

1 SIMPLE T1 r0 range NewIndex1 NewIndex1 1\ N 6462 Using where

The above is the editor for you to share how to optimize the horizontal partition in MySQL, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.