Optimization example based on MySQL horizontal Partition

We know that MySQL 5.1 supports horizontal partitioning. Let's try to optimize the query on a partitioned table.
In general, the optimization principle is the same as the optimization for individual tables, ensuring that the number of scans on the disk table is reduced.

The table structure is as follows:

We have inserted 2 million rows of data for testing.

Look at this query.

Select * from T1 where system_type in (1, 2)
Union all
Select * from T1 where system_type = 3;

This statement filters out the system_type field twice and then performs Union all. But I don't know. In fact, we performed a total of three full table scans for the two partitions.

We changed it to this:

Select * from T1 where system_type in (1, 3)
Union all
Select * from T1 where system_type = 2;

As a simple change, we reduced the number of scans for two shards from three to two. But in this case, the overhead is huge. Can we remove Union all? Of course.

Select * from T1 where system_type> 0 and system_type <4;

Union all is removed, but the problem is that the partition scan is changed to a range query, and the upper and lower limits are not fixed. There is still room for optimization.

Let's change the filtering condition for the system_type column to the following:

Select * from T1 where system_type in (1, 2, 3 );

Id select_type
Table partitions
Type possible_keys
Key key_len
Ref rows
Extra
1 simple
T1 r0, r1
All \ n
\ N
17719 using where

Currently, it is still a range scan, but the upper and lower limits are clear. In this way, the upper and lower limits can be quickly found for scanning partitions, which is faster than before, and the overhead is smaller.
However, it seems that it can be optimized. Although the upper and lower limits of the filter conditions are obvious, the scans within the region are still full partitions (equivalent to the full table of the entire table .).
OK. Now add an index to this column.

Alter table T1 analyze partition r0, R1;

Select * from T1 where system_type in (1, 2, 3 );

Id select_type
Table partitions
Type possible_keys
Key key_len
Ref rows
Extra
1 simple
T1 r0, r1
Range newindex1
Newindex1 1
\ N 6462
Using where

Of course, our example is very simple. Here we just want to demonstrate how to optimize SQL in a horizontal partition.

Http://blog.csdn.net/yueliangdao0608/article/details/7779108