即优化器利用自身的优化器来对我们写的SQL进行优化,然后再将其放入InnoDB引擎中执行。
select * from x where ((a = 5));
上面的括号很没必要,优化器就会直接去掉。
select * from x where a = 5;
select * from x where b = a and a = 5;
同样的,虽然是两列比较,但是a的值只有一个,所以可以优化
select * from x where b = 5 and a = 5;
select * from x where a = 5 and b > a;
可以优化为
select * from x where a = 5 and b > 5;
select * from x where a 10 and b > a;
当前两个条件发生时,最后一个条件必然发生,所以可以优化
select * from x where a 10;
select * from x where -a > -5;
优化器不会对其进行优化,而且这个坏处很多就是不能使用索引了,所以我们尽量让列单独出现,而不是在表达式计算中。
当表中只有一两条数据,或则使用主键或唯一列的索引等值查询的话就会被MySQL优化器视为常量表,直接将SQL语句优化成常量。
select * from table1 join table2 on table1.col1 = table2.col2 where table1 = 'a';
select table1的列都作为常量,table2.* from table2 where table1的常量col1 = table2.col2;
外连接呢,首先连接的顺序是固定的,故驱动表和被驱动表是固定不变的。所以是不能像内连接一样交换驱动表的。
但是呢,有一种情况
select * from table1 left join table2 on table1.col1 = table2.col2 where table2.col2 is not null;
我们设定了table2的列是非空的,这意味着什么,当table1匹配不到时设置table2列为null,但是却不满足搜索条件被过滤掉,所以左连接匹配失败null相当于是失效的。这个语句和内连接是没有区别的,直接将其优化为内连接即可。
所以当在外连接出现时,但是被驱动表拒绝空值时,此时外连接和内连接是可以互相转换的,而内连接可以通过交换驱动表来优化SQL查询成本。
子查询分类
再分
不相关标量子查询
select * from x where key1 = (select y.key1 from y where y.primarykey = 1);
对于不相关的标量子查询来说,就是先执行子查询,然后在对外部查询进行查询。
相关子查询
select * from s1 where key1 = (select common_field from s2 where s1.key3 = s2.key3 limit 1);
对于相关的标量子查询
其实和连接的流程差不多。
优化器对于标量的子查询并不需要什么优化,因为对于标量的子查询来说,数据量还算很小的了。
select * from x where key1 in (select key3 from y);
对于上述不相关的IN查询来说,如果IN子查询的参数少的话,还可以试着加载到内存,然后让外层查询对很多的条件进行比较。
但是如果子查询数据量一旦大了起来,内存无法全部加载完,或导致外层查询需要比较的参数太多,外层记录需要对于过多条件进行比较,导致索引无法使用,因为每一次都要使用索引,每次都要比较,还不如直接全表扫描。最后导致性能很低。
MySQL对这种in参数过多时,不会将子查询在作为外部的参数,而是直接创建一个临时表来存储子查询的结果。
子查询转物化表materialized_table后,我们还能将物化表和外层查询转换为连接的方式。
select x.* from x inner join materialized_table m on key1 = m.key3;
然后我们就可以用之前计算成本的知识来计算那个作为驱动表更合适了。
只有不相关子查询才能转换为物化表
像上述结果一样,我们将查询结果转换为物化表,然后我们在把物化表转换为连接的方式。
我们为什么不能直接将子查询转换为连接的方式呢?这就是semi-join优化。
我们可以试试将其转换为如下语句
select x.* from x join y on key1 = key3;
三种情况
能满足的条件就是y表的key3是主键或唯一列,不然就会出现多条的情况,这条语句就不等于原语句了。
但是此时semi join半连接概念的出现,在半连接的情况下,对于驱动表x来说,我们只关心被驱动表y是否有记录能够满足连接条件的,而不关心被驱动表y有几条能匹配,最后结果集只保存驱动表x的记录。
实现半连接semi join的方法。PS:semi join半连接只是一个概念。
semi join使用条件:
如果不能使用semi join和物化表,我们还可以将in的语句改造成EXISTS语句。
将上述改造为如下语句。
select * from x where exists (select 1 from y where key3 = x.key1)
如果被驱动表key3有索引,就可以使用索引了啊 o( ̄▽ ̄)d。
这个算是下下策了。
参与评论
手机查看
返回顶部