mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `k` (`k`,`d`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=gbk MAX_ROWS=1000000
1 row in set (0.00 sec)
一条简单的SQL:
mysql> explain select * from sbtest1 where k >= 160000 and k < 320000 order by pad limit 3;
+----+-------------+---------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | sbtest1 | range | k | k | 4 | NULL | 3660 | Using index condition; Using filesort |
+----+-------------+---------+-------+---------------+------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)
Initialization and linking JOIN structure to st_select_lex.
fix_fields() for all items (after fix_fields(), we know everything about item).
Moving HAVING to WHERE if possible.
Initialization procedure if there is one.
在该阶段的详细流程比较复杂,暂时不展开描述。
准备阶段optimizer_trace的输出为:
“join_preparation”: {
“select#”: 1,
“steps”: [
{
“expanded_query”: “/* select#1 */ select `sbtest1`.`id` AS `id`,`sbtest1`.`k` AS `k`,`sbtest1`.`c` AS `c`,`sbtest1`.`pad` AS `pad`,`sbtest1`.`d` AS `d` from `sbtest1` where ((`sbtest1`.`k` >= 160000) and (`sbtest1`.`k` < 320000)) order by `sbtest1`.`pad` limit 3″
我们知道在5.6里已经对类似select … where id in (select…)这样的子查询做了优化,将其转化成了semi-join操作,这一步发生在函数flatten_subqueries()中;这里实际上是一个递归调用函数flatten_subqueries的步骤,因为子查询中也可能存在子查询;
select * from t1 left join t2 on t1.a = t2.a where t2.b < 1000;
那么在optimizer_trace中会打印:
{
"transformations_to_nested_joins": {
"transformations": [
"outer_join_to_inner_join",
"JOIN_condition_to_WHERE",
"parenthesis_removal"
],
"expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t2`.`a` AS `a`,`t2`.`b` AS `b`,`t2`.`c` AS `c` from `t1` join `t2` where ((`t2`.`b` < 1000) and (`t1`.`a` = `t2`.`a`))"
}
},
类似的转换:
SELECT * from t1 LEFT JOIN (t2, t3) ON t2.a=t1.a t3.b=t1.b WHERE t2.c < 5
转换为SELECT * FROM t1, t2, t3 WHERE t2.c < 5 AND t2.a=t1.a t3.b=t1.b
3767 if (thd->lex->is_single_level_stmt())
3768 thd->status_var.last_query_cost= join->best_read;
3769
3770 /* Generate an execution plan from the found optimal join order. */
3771 if (join->get_best_combination())
3772 DBUG_RETURN(true);
设置线程session遍历last_query_cost,并依据之前确定的Join顺序生成执行计划
c.我们回到函数JOIN::optimize继续
{
“attaching_conditions_to_tables“: {
“original_condition”: “((`sbtest1`.`k` >= 160000) and (`sbtest1`.`k` < 320000))”,
“attached_conditions_computation“: [
{
“table”: “`sbtest1`”,
“rechecking_index_usage”: {
“recheck_reason”: “low_limit”,
“limit”: 3,
“row_estimate”: 3660
}
}
],
“attached_conditions_summary“: [
{
“table”: “`sbtest1`”,
“attached”: “((`sbtest1`.`k` >= 160000) and (`sbtest1`.`k` < 320000))”
}
]
}
},
###########################################
调用函数:
505 if (make_join_select(this, conds))
根据注释,make_join_select主要做几件事儿:
Step #1: Extract constant condition
– Extract and check the constant part of the WHERE
– Extract constant parts of ON expressions from outer
joins and attach them appropriately.
trace输出关键字:condition_on_constant_tables
Step #2: Extract WHERE/ON parts
Heuristic: Switch from ‘ref’ to ‘range’ access if ‘range’
access can utilize more keyparts than ‘ref’ access. Conditions
MySQL Join
MySQL本身没有常规意义上的执行计划,一般情况就是通过JOIN和QEP_TAB这两个结构组成。QEP_TAB 的全称是Query Execution Plan Table,这个“Table“可以是物理表、内存表、常量表、子查询的结果表等等。作为整个单独JOIN执行计划载体之前还承担着整个执行路径的调用和流转,但是从8.0.20后,全面的生成了独立的