15.查询优化和子查询半查询(gold_axe)

一些重写规则

移除没用的括号
常量传递(确定值换成对应常数)
移除明显没用的条件
表达式计算
Having移到where
常量表

唯一索引的等值查询,被任务查询的时间少到可忽略,

这里table1就是常量表, 假设 table1.primary_key = 1 这行是 1,a语句就会被优化为SELECT * FROM 1,a INNER JOIN table2 ON a= table2.column2

外连接和内连接效果一样转内连接

子查询

类型按结果分:

标量子查询 : 子查询子返回一个值
行子查询:
列子查询:
表子查询

按与外层关系分:

子查询结果集的布尔表达式

IN
ANY/SOME

等价于
ALL (同意可换成极值)
EXISTS

如果子查询结果集有记录就是 ture

IN子查询优化

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

物化表

SELECT * FROM s1 WHERE key1
SELECT common_field FROM s2 WHERE key3 = 'a'
这2个查的是 s1 s2 2张表, 不相关的查询
内层查询的结果集太多, 可能内存发不下,而且 in(这里面太多) 效率太低,
所以, 内层查询的结果会被放到临时表里面, 这个过程会去重(瘦身), 这个过程叫物化

一般是基于内存的 Memory表, hash索引
太大了(超过系统变量设置的大小) 只能用基于磁盘的存储引擎, 索引的B+

semi-join 半连接

SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.key1 = s2.common_field WHERE key3 = 'a';
这个语句不能执行只做示意
半连接:只关心在s2表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配

执行半连接如何消除重复值:

Table pullout （子查询中的表上拉）:
比如
SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');因为key2在表2里面是唯一的, 可以变成
SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2WHERE s2.key3 = 'a';
DuplicateWeedout execution strategy （重复值消除）
会建立一个临时表, 临时表只有一个列 id, 是主键, 把s1查询出来的结果的id插入这个临时表, 如果插成功的, 就是不重复的, 记入结果集
LooseScan execution strategy （松散索引扫描）
SELECT * FROM s1
WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');
可以转换成
SELECT * FROM s1 INNER JOIN s2 ON s1.key3 = s2.key1 WHERE s2.key1 > 'a' AND s2.key1 < 'b'
以s2为驱动表, 先走s2的 key1上的索引找到 s2.key1 > 'a' AND s2.key1 < 'b', 然后相同的key1 只取第一个比如

in 子查询转成 EXISTS

SELECT
    *
FROM
    s1
WHERE
    key1 IN (
        SELECT
            key3
        FROM
            s2
        WHERE
            s1.common_field = s2.common_field
    )
OR key2 > 1000;

SELECT
    *
FROM
    s1
WHERE
    EXISTS (
        SELECT
            1
        FROM
            s2
        WHERE
            s1.common_field = s2.common_field
        AND s2.key3 = s1.key1// 可以用到s2.key3上的索引了
    )
OR key2 > 1000;

如果IN子查询不满足转换为semi-join的条件，又不能转换为物化表或者转换为物化表的成本太大，那么它就会被转换为EXISTS查询。

派生表消除

SELECT
    *
FROM
    (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1// 派生表
INNER JOIN s2 ON derived_s1.key1 = s2.key1
WHERE
    s2.key2 = 1;

SELECT
    *
FROM
    s1
INNER JOIN s2 ON s1.key1 = s2.key1
WHERE
    s1.key1 = 'a'
AND s2.key2 = 1;

15.查询优化和子查询 半查询(gold_axe)