摘要:

Record the remaining three slowsSQL的共性,Mainly for semi-join processing

Semi-join instructions:

什么是semi-join？
所谓的semi-join是指semi-join子查询. 当一张表在另一张表找到匹配的记录之后,半连接（semi-jion）返回第一张表中的记录.Contrary to conditional joins,Even if few matching records are found in the right node,左节点 The table will only return one record.另外,The table of the right node will not return a single record.Semi-join is usually usedIN  或 EXISTS 作为连接条件. The subquery has the following structure：

SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

即在where条件的“IN”that subquery in .
The peculiarity of this query is that we only careouter_table中与semi-join相匹配的记录.
换句话说,The final result set is inouter_tables中的,而semi-joinThe effect is just rightouter_tablesto filter the records in .这也是我们进行 semi-join优化的基础,i.e. we only need from semi-joinIt is enough to get the least amount from the outer_tablesIt is sufficient to record the information for filtering.
The so-called minimum amount,Reflected in the optimization strategy is how to de-duplication.

Mysql支持的semi-join策略主要有5个,它们分别为：
1. DuplicateWeedout: Use temporary table pairssemi-joinThe resulting result set is deduplicated.

Duplicate Weedout: Run the semi-join as if it was a join and remove duplicate records using a temporary table.

​

The corresponding matching condition is ：


2. FirstMatch: Only the first part of the internal table is used1records that match the appearance.

FirstMatch: When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.

​

The corresponding matching condition is ：


3. LooseScan: 把inner-tableData is grouped based on the index,Take the first data of each group for matching.

LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.

​

The corresponding matching condition is ：

4. Materializelookup: 将inner-tableDeduplication is solidified into a temporary table,遍历outer-table,Then look for a match on the curing table.

Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned.
the corresponding matching conditions：

​

5. MaterializeScan: 将inner-tableDeduplication is solidified into a temporary table,Traverse the curing table,然后在outer-table上寻找匹配.

对应的条件：

Each of these strategies except Duplicate Weedout can be enabled or disabled using the  optimizer_switch system variable. The  semijoin flag controls whether semi-joins are used. If it is set to  on, the  firstmatch,  loosescan, and  materializationflags enable finer control over the permitted semi-join strategies. These flags are  on by default.

The use of semi-join strategies is indicated in EXPLAIN output as follows:

• Semi-joined tables show up in the outer select. EXPLAIN EXTENDED plus SHOW WARNINGS shows the rewritten query, which displays the semi-join structure. From this you can get an idea about which tables were pulled out of the semi-join. If a subquery was converted to a semi-join, you will see that the subquery predicate is gone and its tables and WHERE clause were merged into the outer query join list and WHERE clause.

• Temporary table use for Duplicate Weedout is indicated by Start temporary and End temporary in the Extra column. Tables that were not pulled out and are in the range of EXPLAIN output rows covered by Start temporary and End temporary will have their rowid in the temporary table.

• FirstMatch(tbl_name) in the Extra column indicates join shortcutting.

• LooseScan(m..n) in the Extra column indicates use of the LooseScan strategy. m and n are key part numbers.

• As of MySQL 5.6.7, temporary table use for materialization is indicated by rows with a select_type value of MATERIALIZED and rows with a table value of <subqueryN>.

  Before MySQL 5.6.7, temporary table use for materialization is indicated in the Extra column by Materialize if a single table is used, or by Start materialize and End materialize if multiple tables are used. If Scan is present, no temporary table index is used for table reads. Otherwise, an index lookup is used.

Legacy semi-join statement:

in类型

Q16

                    select
                        p_brand,
                        p_type,
                        p_size,
                        count(distinct ps_suppkey) as supplier_cnt
                    from
                        partsupp,
                        part
                    where
                        p_partkey = ps_partkey
                        and p_brand <> 'Brand#45'
                        and p_type not like 'MEDIUM POLISHED%'
                        and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
                        and ps_suppkey not in (
                            select
                                s_suppkey
                            from
                                supplier
                            where
                                s_comment like '%Customer%Complaints%'
                        )
                    group by
                        p_brand,
                        p_type,
                        p_size
                    order by
                        supplier_cnt desc,
                        p_brand,
                        p_type,
                        p_size;

Q18

                            select
                                c_name,
                                c_custkey,
                                o_orderkey,
                                o_orderdate,
                                o_totalprice,
                                sum(l_quantity)
                            from
                                customer,
                                orders,
                                lineitem
                            where
                                o_orderkey in (
                                    select
                                        l_orderkey
                                    from
                                        lineitem
                                    group by
                                        l_orderkey having
                                            sum(l_quantity) > 300
                                )
                                and c_custkey = o_custkey
                                and o_orderkey = l_orderkey
                            group by
                                c_name,
                                c_custkey,
                                o_orderkey,
                                o_orderdate,
                                o_totalprice
                            order by
                                o_totalprice desc,
                                o_orderdate
                            limit 100;

范围类型:

Q17

                        select
                            sum(l_extendedprice) / 7.0 as avg_yearly
                        from
                            lineitem,
                            part
                        where
                            p_partkey = l_partkey
                            and p_brand = 'Brand#23'
                            and p_container = 'MED BOX'
                            and l_quantity < (
                                select
                                    0.2 * avg(l_quantity)
                                from
                                    lineitem
                                where
                                    l_partkey = p_partkey
                            );