How does MySQL simplify external Union?

How does MySQL simplify external Union?

In many cases, the expression of a table in the FROM clause can be simplified.

During the analysis phase, queries with right outer join operations are converted to equivalent queries that only contain left join operations. In general, the conversion is based on the following principles:

(T1,...) right join (T2,...) on p (T1,..., T2,...) =

(T2,...) left join (T1,...) on p (T1,..., T2 ,...)

All the inner join expressions in the form of T1 inner join T2 on p (T1, T2) are replaced with T1, T2, P (T1, T2 ), join a connection based on the WHERE condition (or nested connection condition, if any.

When the optimizer evaluates the join query scheme for external join operations, it only considers the scheme of accessing the external table before accessing the internal table. The Optimizer option is limited because only such a solution allows us to use the nested loop mechanism to perform queries for out-of-band join operations.

Suppose we have a query in the following form:

SELECT * T1 left join T2 ON P1 (T1, T2)

Where p (T1, T2) and r (T2)

R (T2) greatly reduces the number of matched rows in table T2. If we execute the query in this way, the optimizer will not have any other options. We can only access table T1 before table T2, resulting in a very low execution plan.

Fortunately, if the WHERE condition rejects null, MySQL can convert this type of query to a query without any outer join operation. If the row supplemented by NULL constructed for this operation is evaluated as FALSE or UNKNOWN, this condition is called rejecting null for an outer join operation.

Therefore, for this outer join:

T1 left join T2 ON T1.A = T2.A

Similar to the following condition:

T2. B IS NOT NULL,

T2. B> 3,

T2.C <= T1.C,

T2. B <2 OR T2.C> 1

Similar to the following condition, null is not denied:

T2. B IS NULL,

T1. B <3 OR T2. B IS NOT NULL,

T1. B <3 OR T2. B> 3

It is easy to check whether a condition for an outer join operation rejects null. The condition for rejecting null is:

· The form is a is not null, where a is an attribute of any internal table

· Contains the correlated formula referenced by the internal table. When a parameter is NULL, it is evaluated as UNKNOWN.

· Include the Union of conditions used to reject null for connection

· Reject the logic and

One condition can deny null for one outer join operation in a query, but does not deny null for the other. In the following query:

SELECT * FROM T1 left join T2 ON T2.A = T1.A

Left join T3 ON T3. B = T1. B

WHERE T3.C> 0

The WHERE condition rejects null for 2nd outer join operations, but does not reject null for 1st outer join operations.

If the WHERE condition rejects null for an outer join operation in a query, the outer join operation is replaced by an inner join operation.

For example, the preceding query is replaced by the following query:

SELECT * FROM T1 left join T2 ON T2.A = T1.A

Inner join T3 ON T3. B = T1. B

WHERE T3.C> 0

For the original query, the optimizer will evaluate a solution that is only compatible with one access sequence T1, T2, and T3. The access sequence T3, T1, and T2.

Conversion of an external join operation can trigger another conversion. In this way, query:

SELECT * FROM T1 left join T2 ON T2.A = T1.A

Left join T3 ON T3. B = T2. B

WHERE T3.C> 0

Convert to query first:

SELECT * FROM T1 left join T2 ON T2.A = T1.A

Inner join T3 ON T3. B = T2. B

WHERE T3.C> 0

This query is equivalent to the query:

SELECT * FROM (T1 left join T2 ON T2.A = T1.A), T3

WHERE T3.C> 0 AND T3. B = T2. B

Now the remaining outer join operations can be replaced by an inner join, because the condition T3. B = T2. B rejects null, we can get a query with no outer join at all:

SELECT * FROM (T1 inner join T2 ON T2.A = T1.A), T3

WHERE T3.C> 0 AND T3. B = T2. B

Sometimes we can successfully replace the embedded outer join operation, but cannot convert the embedded outer join. The following query:

SELECT * FROM T1 LEFT JOIN

(T2 left join T3 ON T3. B = T2. B)

ON T2.A = T1.A

WHERE T3.C> 0

Converted:

SELECT * FROM T1 LEFT JOIN

(T2 inner join T3 ON T3. B = T2. B)

ON T2.A = T1.A

WHERE T3.C> 0,

It can only be rewritten as a form that still contains the embedded outer join operation:

SELECT * FROM T1 LEFT JOIN

(T2, T3)

ON (T2.A = T1.A AND T3. B = T2. B)

WHERE T3.C> 0.

To convert an embedded outer join operation in a query, we must consider the join conditions and WHERE conditions of the embedded outer join. In the following query:

SELECT * FROM T1 LEFT JOIN

(T2 left join T3 ON T3. B = T2. B)

ON T2.A = T1.A AND T3.C = T1.C

WHERE T3.D> 0 OR T1.D> 0

The WHERE condition does not deny null for the embedded outer join, but the join condition of the embedded outer join T2.A = T1.A AND T3.C = T1.C is deny null. Therefore, the query can be converted:

SELECT * FROM T1 LEFT JOIN

(T2, T3)

ON T2.A = T1.A AND T3.C = T1.C AND T3. B = T2. B

WHERE T3.D> 0 OR T1.D> 0

7.2.12. How does MySQL optimize order?

In some cases, MySQL can use an index to satisfy the order by clause without additional sorting.

Even if order by does not exactly match the index, the index can be used as long as all unused index parts and all additional order by columns in the WHERE clause are constant. The following query uses indexes to solve the order by section:

SELECT * FROM t1

ORDERKey_part1,Key_part2,...;

   

SELECT * FROM t1

WHEREKey_part1=Constant

ORDERKey_part2;

   

SELECT * FROM t1

ORDERKey_part1DESC,Key_part2DESC;

   

SELECT * FROM t1

WHEREKey_part1= 1

ORDERKey_part1DESC,Key_part2DESC;

In some cases, MySQLNoUse indexes to solve order by, although it still uses indexes to find the rows matching the WHERE clause. These situations include:

· Use order by for different keywords:

· SELECT * FROM t1 ORDERKey1, Key2;

· Use order by for non-continuous elements of keywords:

· SELECT * FROM t1 WHEREKey2=ConstantORDERKey_part2;

· Hybrid ASC and DESC:

· SELECT * FROM t1 ORDERKey_part1DESC,Key_part2ASC;

· The keywords used to query rows are different from those used in order:

· SELECT * FROM t1 WHEREKey2=ConstantORDERKey1;

· You are concatenating many tables, and not all columns in order by come from 1st extraordinary scales used to search rows. (This is the 1st tables in the EXPLAIN output that do not have the const join type ).

· There are different order by and group by expressions.

· The table index type used cannot store rows in sequence. For example, this is true for the HASH index of the HEAP table.

BY using explain select... order by, you can check whether MySQL can use indexes to solve queries. If the Extra column contains Using filesort, the query cannot be solved. See section 7.2.1 "EXPLAIN syntax (obtain information about SELECT )".

File sorting optimization is used not only to record sorting keywords and row locations, but also to record the required columns for query. This avoids reading rows twice. The file sorting algorithm works like this:

1. Read rows match the rows in the WHERE clause, as shown above.

2. For each row, the record forms a series of values of the sorting keyword and row location, and records the columns required for query.

3. Sort tuples by sorting keywords

4. Retrieve rows in the sorted order, but read the required columns directly from the sorted tuples instead of accessing the table again.

This algorithm is greatly improved than Mysql in earlier versions.

To avoid slow speed, this optimization is only used when the total size of the extra column in the sorting tuples does not exceed the system variable value of max_length_for_sort_data. (The indication that this variable is set too high is that hard drive activity is too frequent and CPU activity is low ).

To increase the order by speed, first check whether MySQL can use indexes instead of additional sorting stages. If not, try the following policy:

· Increase the size of the variable sort_buffer_size.

· Increase the size of the variable read_rnd_buffer_size.

· Change tmpdir to a dedicated file system with a large amount of free space. This option accepts several paths that use the round-robin mode. Apply the colon (':') to the path in Unix. Use the semicolon (';') in Windows, NetWare, and OS/2 (';'). You can use this feature to evenly distribute loads to several directories.Note:The path should be differentPhysicalThe Directory of the file system on the hard disk, rather than the different partitions on the same hard disk.

BY default, MySQL sorts all GROUPCol1,Col2,... The query method is like specifying order by in the queryCol1,Col2,.... If the statement explicitly contains an order by clause containing the same columns, MySQL can optimize it without slowing down, even though it still performs sorting. If the query includes group by but you want to avoid consumption of sorting results, you can specify order by null to prohibit sorting. For example:

Insert into foo

SELECT a, COUNT (*) FROM bar group by a order by null;