正文
:
"22.00"
},
"ordering_operation"
:
{
"using_temporary_table"
:
true
,
...
2))关掉BNL后，ORDER BY将直接使用filesort。
mysql
>
set
optimizer_switch
=
'block_nested_loop=off'
;
Query
OK
,
0
rows affected
(
0.00
sec
)
mysql
>
explain
format
=
json select *
from
t1
,
t1
as
t2 order by
t1
.
a
;
EXPLAIN
{
"query_block"
:
{
"select_id"
:
1
,
"cost_info"
:
{
"query_cost"
:
"25.00"
},
"ordering_operation"
:
{
"using_filesort"
:
true
,
...
2）ORDER BY的列不属于执行计划中第一个连接表的列。
例如：
mysql
>
explain
format
=
json select *
from
t
as
t1
,
t
as
t2 order by
t2
.
a
;
EXPLAIN
{
"query_block"
:
{
"select_id"
:
1
,
"cost_info"
:
{
"query_cost"
:
"25.00"
},
"ordering_operation"
:
{
"using_temporary_table"
:
true
,
...
3）如果ORDER BY的表达式是个复杂表达式。
那么什么样的ORDER BY表达式，MySQL认为是复杂表达式呢？
1))如果排序表达式是SP或者UDF。
例如：
drop
function
if
exists
func1
;
delimiter
|
create
function
func1
(
x
int
)
returns
int
deterministic
begin
declare
z1
,
z2
int
;
set
z1
=
x
;
set
z2
=
z1
+
2
;
return
z2
;
end
|
delimiter
;
explain
format
=
json select *
from t1 order by func1
(
a
);
{
"query_block"
:
{
"select_id"
:
1
,
"cost_info"
:
{
"query_cost"
:
"2.20"
},
"ordering_operation"
:
{
"using_temporary_table"
:
true
,
...
2))ORDER BY的列包含聚集函数
为了简化执行计划，我们利用INDEX来优化GROUP BY语句。
例如：
create index idx1 on t1
(
a
);
explain
format
=
json
SELECt
a
FROM t1 group
by
a
order by sum
(
a
);
|
