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
- 不相关子查询:
子查询可以独立运行,不依赖于外层
- 标量子查询:返回单一值的子查询
- 对于
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
。也就是说对于不相干标量子查询,可以视作两个查询 。
- 对于
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中所有记录
- 对于
- 行子查询:返回一行记录的子查询(包含多个列)
同标量子查询
- 物化表
对于不相关子查询select * from s1 where key1 in (select f from s2 where key3='a')
如果子查询中记录条数很少,那么当作两个查询即可,但是如果子查询记录非常多,导致内存放不下 。mysql可以将in子句的查询结果放入临时表,并对记录进行去重(根据查询结果列建立主键,唯一索引,或者联合唯一索引),in子句的结果去重并不影响执行结果,如果子查询结果集并不是很大那么会使用memory赢球,并且建立hash索引(in子句相当于判断列是否在临时表中,这时候hash索引可以起到很好的作用),如果很大那么会使用基于磁盘的存储引擎来保存结果集中的记录 , 并使用B+树 。
这种将子查询结果集中的记录保存到临时表的过程称作物化表
- 物化表转连接
假如上面物化表名称为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
转换为内连接后,查询优化器 , 可以评估使用不同连接顺序的成本,并选择成本最小的查询方式
- 子查询优化为半连接
- 转换为物化表,再执行查询具备建立临时表的成本 。
select * from s1 where key1 in (select f from s2 where key3='a')
可以被优化为
select s1.* from s1 inner join s2 on s1.key1 = s2.f where s2.key3 = 'a'
但是这存在s2中满足s2.key3 = 'a'
存在多个重复值f,导致s1中一条记录连接相同f值多条记录的情况,失去了推荐阅读
- 手机怎样连接我的电脑(手机怎么访问自己电脑)
- MYSQL-->InnoDB引擎底层原理
- 浅谈MySQL、Hadoop、BigTable、Clickhouse数据读写机制
- Docker | Compose创建mysql容器
- 一文读懂 MySQL 索引
- 如何优雅的备份MySQL数据?看这篇文章就够了
- 【博学谷学习记录】超强总结,用心分享|MySql连接查询超详细总结
- day08-MySQL事务
- MySQL 窗口函数
- 线上服务宕机,码农试用期被毕业,原因竟是给MySQL加个字段
- 转换为物化表,再执行查询具备建立临时表的成本 。