一年的时间过得真快,去年这个时候,哥还在上海看mysql 5.6的feature, 今年就在北京看mysql 5.7的feature了。
mysql的union语句一直是被人广为诟病的,因为它不分青红皂白,总是会创建temporary table, 然后把全部数据写入此临时表,再从中读取数据返回给用户。
其实有些情况下,比如UNION ALL且没有最外层排序条件( top level ORDER BY)的时候,完全可以直接从第一张表里面读取数据返回给用户,再从第二张表里读取数据返回给用户…….没有必要使用临时表。
如果能够做到这一点,可以省去创建,写入,读取临时表的过程,可以节省内存或磁盘空间,同时第一张表的数据可以立刻返回给用户。
不过呢,临时表也有一个隐含的优点:由于多张表的字段type可能不完全相同,当数据写入临时表的时候,会自动做type casting,写入完毕再读取的时候,得到的column type就完全一致了。
下面就粗略地讲一讲mysql 5.7.3 是如何优化UNION ALL的,我对源码也不了解的说。
1) 什么样的UNION语句可以不写入临时表
sql/sql_lex.cc
/**
Decide if a temporary table is needed for the UNION.
@retval true A temporary table is needed.
@retval false A temporary table is not needed.
*/
bool st_select_lex_unit::union_needs_tmp_table()
{
return union_distinct != NULL ||
global_parameters()->order_list.elements != 0 ||
thd->lex->sql_command == SQLCOM_INSERT_SELECT ||
thd->lex->sql_command == SQLCOM_REPLACE_SELECT;
}
可以看到,如果满足以下全部条件,那么就不必写入临时表:
1. UNION ALL,非UNION,非UNION DISTINCT。
2. 没有global ORDER BY语句。
3. UNION不是INSERT … SELECT语句的一部分。
4. UNION不是REPLACE…SELECT语句的一部分。
2) 创建临时表
其实 ,即使做了优化,也还是存在临时表的创建过程,
因为临时表可以做type casting。
正常情况下,临时表的创建create_tmp_table()是分为二个过程的:
1. 创建表结构 data structure ,细节看不懂。
2. 如果skip_create_table为false,调用instantiate_tmp_table(),把临时表创建出来,细节看不懂。
mysql5.7.3 优化之后,跳过了创建临时表的第2部分:
if (is_union() && !union_needs_tmp_table())
{
fake_select_lex= NULL;
instantiate_tmp_table= false;
}
else
{
instantiate_tmp_table= true;
}
3) select_union与select_union_direct
为了处理不写入临时表的情况, mysql创建了一个新的 class: class select_union_direct :public select_union
比较一下这二个class send_data的不同点:
bool select_union::send_data(List &values)
{
int error= 0;
if (unit->offset_limit_cnt)
{ // using limit offset,count
unit->offset_limit_cnt--;
return 0;
}
fill_record(thd, table->field, values, 1, NULL, NULL);
if (thd->is_error())
return 1;
if ((error= table->file->ha_write_row(table->record[0])))
{
/* create_myisam_from_heap will generate error if needed */
if (!table->file->is_ignorable_error(error) &&
create_myisam_from_heap(thd, table, tmp_table_param.start_recinfo,
&tmp_table_param.recinfo, error, TRUE, NULL))
return 1;
}
return 0;
}
bool select_union_direct::send_data(List &items)
{
if (!limit)
return false;
limit--;
if (offset)
{
offset--;
return false;
}
fill_record(thd, table->field, items, true, NULL, NULL);
if (thd->is_error())
return true; /* purecov: inspected */
return result->send_data(unit->item_list);
}
可以看到, select_union_direct 只是做了fill_record(),并没有ha_write_row()写入真实的行数据。
所以,临时表的创建,只是为了fill_record()做type casting.
与此同时,explain select union 的输出也做了一定的改变。