Mysql单表访问方法,索引合并,多表连接原理,基于规则的优化,子查询优化( 四 )

const访问方法) 。查询优化器在分析一个查询语句的时候,优先使用常量表查询,然后把查询中涉及的条件替换成常数,然后再去分析其余表的查询成本 。如:
select * from table1 inner join table2on table1.column1 = table2.column2 where table1.primarykey = 1
其中我们可以看到table1.primarykey=1使用主键进行等值查询,可以先不分析table2表查询成本 , 先执行select * from table1 where table1.primarykey = 1 然后将语句转换成如下
select table1满足where的记录中各个字段,table2.* from table1 inner join table2 on table1表记录中column1列常量值 = table2.column2
2.外连接消除上面我们说过,对于外连接,即使被驱动表的记录不满足on子句,还是被加入到结果集 , 但是无论什么记录只要不满足那么都无法加入到最终结果集 。如果我们在where子句中指定了被驱动中的列不为空,这时候外连接的驱动表和被驱动表顺序就可以交换了,mysql优化器可以通过评估不同的连接顺序的成本,来选择成本最小的顺序来执行查询
这种指定被驱动表,或者隐含被驱动表列不为空的查询条件称为——空值拒绝
3.子查询优化3.1相关子查询和不相关子查询

  • 相关子查询:子查询的执行依赖于外层查询的值
    如:select * from t1 where t1.m1 in (select t2.m2 where t2 where t2.n2 = t1.n1)
    这里面的 t2.n2 = t1.n1,t2.n2的筛选依赖于外层查询得到的t1.n1
  • 不相关子查询:
    子查询可以独立运行,不依赖于外层
3.2 子查询在mysql中是如何执行的3.2.1 标量子查询 , 行子查询的执行方式
  • 标量子查询:返回单一值的子查询
    1. 对于select * from s1 where key1 =(select f from s2 where key3='a' limit 1)其中select f from s2 where key3='a' limit 1是一个标量子查询 。
      mysql会先执行select f from s2 where key3='a' limit 1得到 f的值为1 然后再替换执行select * from s1 where key1=1 。也就是说对于不相干标量子查询,可以视作两个查询 。
    2. 对于select * from s1 where key1 = (select f from s2 where s1.key3 = s2.key3 limit 1)其中select f from s2 where s1.key3 = s2.key3 limit 1是一个相关标量子查询 。
      mysq会先从外层查询中获取一条记录,从s1表查询一条记录 。然后从这条记录中找到子查询中涉及到的值,即找到key3的值(假设是3) , 然后执行子查询select f from s2 where 3 = s2.key3,假设返回的f=4,再返回到校验外层查询key1 = 4是否成立,继续执行直到遍历完s1中所有记录
  • 行子查询:返回一行记录的子查询(包含多个列)
    同标量子查询
3.2.2 in子查询优化
  1. 物化表
    对于不相关子查询select * from s1 where key1 in (select f from s2 where key3='a')
    如果子查询中记录条数很少,那么当作两个查询即可,但是如果子查询记录非常多,导致内存放不下 。mysql可以将in子句的查询结果放入临时表,并对记录进行去重(根据查询结果列建立主键,唯一索引,或者联合唯一索引),in子句的结果去重并不影响执行结果,如果子查询结果集并不是很大那么会使用memory赢球,并且建立hash索引(in子句相当于判断列是否在临时表中,这时候hash索引可以起到很好的作用),如果很大那么会使用基于磁盘的存储引擎来保存结果集中的记录 , 并使用B+树 。
    这种将子查询结果集中的记录保存到临时表的过程称作物化表
  2. 物化表转连接
    假如上面物化表名称为tmp,并且为查询结果建立了索引pk,select * from s1 where key1 in (select f from s2 where key3='a')查询就是判断s1中key1是否在tmp表的主键中 , 建立了索引那么判断是非常块的 。但是mysql还会继续优化为内连接,如
    select s1.* from s1 inner join tmp on s1.key1 = tmp.pk 转换为内连接后,查询优化器 , 可以评估使用不同连接顺序的成本,并选择成本最小的查询方式
  3. 子查询优化为半连接