摘要:

mysql-explain-tree-解读-以TPCH-Q2为例

explain format=tree说明

1. mysql8版本对explain增加的参数
2. 相对应的还有format=json
3. format=tree格式看起来相比json更为精简,也能表现出执行的层次
4. mysql的执行过程难以理解的是表之间的join关系, 毕竟对于子查询也会优化成join的一种特殊形式
5. TPCH-Q2涉及的表的数量很多, 可以表现出表之间的处理关系

TPCH-Q2-SQL:

	explain format=tree
	select
		s_acctbal,
		s_name,
		n_name,
		p_partkey,
		p_mfgr,
		s_address,
		s_phone,
		s_comment
	from
		part,
		supplier,
		partsupp,
		nation,
		region
	where
		p_partkey = ps_partkey
		and s_suppkey = ps_suppkey
		and p_size = 15
		and p_type like '%BRASS'
		and s_nationkey = n_nationkey
		and n_regionkey = r_regionkey
		and r_name = 'EUROPE'
		and ps_supplycost = (
			select
				min(ps_supplycost)
			from
				partsupp,
				supplier,
				nation,
				region
			where
				p_partkey = ps_partkey
				and s_suppkey