March 6, 2014

MySQL 5.6的优化器改进

本文整理了下MySQL5.6在优化器部分的相关知识点,不涉及任何代码内容,主要搜集了网上的一些相关资料(这是重点 :)) 子查询优化 首先要提的当然是臭名昭著的MySQL子查询问题,在MySQL5.5及之前的版本,所有有经验的MySQL DBA都会告诉你:绝不能在SQL的WHERE子句中使用子查询,因为那将可能产生灾难性的后果,因为很有可能每扫描一条数据,Where子查询都会被重新执行一遍,workaround的办法就是把WHERE里的子查询提升到FROM中,做成join操作; semi join的定义点wiki, MySQL需要满足如下条件,才会转换成Semi-join #子查询是IN或者=ANY的,不可以是NOT IN #子查询只能包含一个Query block,不可以有UNION等操作 #子查询不能包含GROUP BY或者HAVING #子查询不能包含聚合函数 #子查询谓语不可以是外接查询条件或者否定查询条件 #不可以包含STRAIGHT_JOIN限定词 #Semi-join只能用于SELECT或者INSERT,不可用于UPDATE和DELETE 偷个懒,上述总结翻译自这篇博文,官方文档上也有说明 和普通join查询不同的是,在semi join中,inner table 的结果集没有重复数据,当两表关联时,例如t1 semi join t2, 当t2存在匹配的记录时,返回t1的记录(t2的记录不会加入操作的结果集中),并且t1的记录最多只返回一次;不像inner join,每一个匹配的记录都会返回,对于semi join,在乎的只是是否匹配子查询而已。 当满足转换成semi join的条件时,就会将子查询进行转换,并选择如下策略之一去对记录进行去重: #TABLE PULLOUT, 直接使用TABLE PULLOUT,将子查询的表和outer表进行inner join;子查询上产生的记录本身需要具有唯一性,例如是primary key或者Unique key #Duplicate Weedout, 先和子查询做简单的inner join操作,并使用临时表(建有primary key)来消除重复记录; 在explain的extra字段会看到Start temporary和End temporary标识 下面几个图都偷的mariadb的博客上的,嘿嘿         mariadb的博客:duplicate  weedout #FirstMatch,当扫描inner table来组合数据时,并且有多个符合条件的数据时,只选择第一条满足条件的记录 […]