Optimization example based on MySQL horizontal Partition

We know the optimization example based on MySQL horizontal partition that MYSQL5.1 began to support the horizontal partition function. 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. Www.2cto.com our 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 allselect * FROM t1 WHERE system_type = 3; this statement filters out the system_type field twice and then performs union all once. But I don't know. In fact, we performed a total of three full table scans for the two partitions. Let's change it to this: SELECT * FROM t1 WHERE system_type IN (1, 3) union allselect * FROM t1 WHERE system_type = 2; seemingly simple, we reduced the number of scans for the 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 scan of the partition becomes a range query, and the upper and lower limits are not fixed. Relatively speaking, there is also room for optimization. Www.2cto.com let's change the filtering condition for the system_type column to the following: SELECT * FROM t1 WHERE system_type in (, 3 ); id select_type table partitions type possible_keys key key_len ref rows Extra1 SIMPLE t1 r0, r1 ALL \ N 17719 Using where, 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 Extra1 SIMPLE t1 r0, r1 range NewIndex1 NewIndex1 1 \ N 6462 Using where www.2cto.com, of course, our example is very simple. Here we just want to demonstrate how to optimize SQL in a horizontal partition. Author siye