查询基本概念

查询SQL结构

Select Statement
    - select expr
        - wild star: *
        - const value: number,String
        - common field
        - expression
        - aggregation
        - window function
        - Scalar Operand
    - target table
        - common table
        - derived table
    - where condition
    - order by
    - group by
        - col_name
        - expr
        - position
    - limit offset
    - windown
    - having condition
    - Lock Clause
    - UNION Clause

子查询

• Derived table

  • 子查询出现在Target Table中，比如：select * from (select sum(a) from t1) T;

• Scalar Operand

  • 子查询出现在select expr中，比如：select (select 1) from t1;

• Comparison

  • 子查询出现在where，having条件中，比如：select * from t1 where id IN (select id from t2);

• 关联子查询

  • 子查询中引用了外部查询的字段，比如：select * from t1 where id IN(select id from t2 where t2.a=t1.b);

• 非关联子查询

  • 子查询没有引用外部查询的字段

多表join

• Inner Join

  • 查询所有满足join条件的记录。

• Outer Join

  • 查询所有满足join条件的记录，且输出至少一张表的全部记录，left/right join。

• Semi Join

  • 查询是否有至少一条满足join条件的记录，对IN、=ANY、EXIST子查询的一种优化手段。

• Anti Join

  • 查询是否不存在满足join条件的记录，是对NOT IN、!=ALL、NOT EXIST子查询的一种优化手段。

查询执行策略

表访问方式

• 全表扫描

  • 主键索引上扫描

• 索引扫描

  • 二级索引上扫描

• 索引Range

  • 索引上范围扫描
  • <、>、<=、>=

• 索引ref查询

  • 索引上同一个value查询
  • =

order by

• 索引扫描

  • 索引顺序按order by字段有序，可直接通过扫描索引的到有序结果
  • 条件：order by字段满足索引上最左匹配
    • 如"order by a,b"，索引idx_abc(a,b,c)可以扫描使用

• filesort排序

  • 查询结果通过filesort进行外部排序
  • 不满足索引扫描条件时使用

group by

• 索引扫描

  • 通过loose scan的方式扫描索引去重
  • 条件：order by字段满足索引上最左匹配
    • 如：“order by a,b”，索引idx_abc(a,b,c)可以扫描使用

• 临时表分组

  • 查询结果保存在一张临时表中，并通过group by字段更新aggregation字段。
  • 不满足索引扫描条件时使用

derived table

• Merge

  • 子查询直接合入外部的查询语句，清除derived table
  • 条件：非关联简单子查询
    • 如：select * from (select a,b from t1) A order by A.b; 可直接改成merge：select a,b from t1 order by b;

• 物化

  • 子查询的结果保持在一张临时表中，供外部查询继续使用。
  • 条件：非关联子查询
    • 如：select sum(col_sum) from (select sum(a) as col_sum from t1 group by b) A;

子查询

• 物化

  • 子查询的结果保持在一张临时表中，供外部查询继续使用
  • 条件：非关联子查询
    • 如：select * from t1 where a> (select max(b) from t2);

• Semi Join

  • 子查询改写成semi join执行
  • 条件：关联子查询，且是IN、EXIST、=ANY等查询条件
    • 如select * from t1 where a IN (select b from t2)，会改写成select t1.* from t1 semi join t2 on t1.a=t2.b;

• 嵌套执行

  • 内部子查询嵌套在外部查询执行过程中执行，效率最差
  • 上面两个条件都不满足：
    • 如：select * from t1 where a>(select max(b) from t2 where t2.b=t1.c)

多表join

• Nest Loop Join（NL）

  • 相当于两层for循环执行，外表的每条记录去内部做扫描查询匹配
  • 效率最差

• Block Nest Loop Join（BNL）

  • 外表的记录存放在Join Cache缓存中，内表循环时每条记录去Join Cache中查找匹配记录
  • Join Cache的目的是为了减少内表的遍历次数

• Block Key Access Join（BKA）

  • 外表的记录存放在Join Cache缓存中，然后Join Cache中每条记录通过索引查询内部中匹配的记录。

• Hash Join

  • 外表作为一张build表创建hash map
  • 内部作为probe表在hash map中查找匹配记录

执行计划分析

Explain分析

• 基本用途

  • 用来展示SQL查询语句的执行计划信息，帮助分析和改进查询语句的执行效率

• 使用方式

  • Explain [format=json/tree] query statement

• 输出内容

  • Explain

Explain

• Explain输出内容
  • select type
    • SIMPLE、PRIMARY、UNION、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、DEPENDENT DERIVED
  • Type
    • system、const、er_ref、ref、fulltext、index、range、all
  • possible_keys
    • SQL语句命中的所有索引
  • Key
    • SQL语句使用的索引
  • Rows
    • 预估的输出行数
  • Filtered
    • 过滤条件过滤数据的比例

Explain例子

• 表访问

  • 全表扫描，explain select * from t1;
  • 主键ref，explain select * from t1 where id = 1;
  • 主键range，explain select * from t1 where id > 1;
  • 覆盖索引扫描，explain select a from t1;

• Order by

  • 索引排序，explain select a from t1 order by id;
  • filesort排序，explain select a from t1 order by id;

• Group by

  • 索引排序，explain select sum(a) from t1 group by id;
  • 临时表分组，explain select sum(a) t1 group by b;

• Derived table

  • System table，explain select * from (select sum(a) from t1) A;
  • 物化临时表，explain select * from (select sum(a) from t1 group by b) A;
  • merge，explain select * from (select * from t1) A order by A.a;

• 子查询

  • Subquery，explain select * from t1 where id > (select sum(a) from t1);
  • Dependent subquery，explain select * from t1 where id in (select sum(a) from t1 group by b);
  • Semi join(first match)，explain select * from t1 where id in (select a from t1 as tt where t1.b=tt.b);
  • 物化子查询，explain select * from t1 where id in (select a from t1 as tt);

• 多表Join

  • Nested Loop Join，explain select * from t1 join t2 on t1.id=t2.b;
  • BNL Join，explain select * from t1 join t2 on t1.b=t2.b;
  • BKA Join，explain select * from t1 join t2 on t1.b=t2.a;

Optimize trace分析

• 作用：
  • 用来分析优化器生成执行计划的详细过程，往往用例分析生产错误执行计划的原因，从而给索引优化提供参考建议
  • 打开方式
    • set session optimizer_trace="enabled=on";
    • set session optimizer_trace_max_mem_size=1000000;
    • execute your query statement sql
    • select * from information_Schema.Optimizer_Trace [into outfile "out.txt"];
    • set session optimizer_trace="enabled=off";