Shulou(Shulou.com)05/31 Report--

MySQL in how to achieve horizontal partition, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

The table structure is as follows:

Inquiry.

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.

Www.2cto.com

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

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.