???????????
???????CREATE TEMPORARY TABLE ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
????????????
??????????????????????????????????????????????????????????????MySQL??????????????洢?Щ???????м???????Щ??????????????????λ?????н?Ρ?????????????????????????????????EXPLAIN????SHOW STATUS?????MYSQL??????????????????????????????????????????????SQL????????????а???????????????? MySQL?е??????????????????????????????????????????????????????????????м??????????????????????дSQL?????????t???????????????????
????????????????????????????HEAP??????????????????????????????????У???????????????????IO?????????????OnDisk????????????壬??????????????洢????????OnDisk??????????????м????????????????HEAP?????洢?????????MAX_HEAP_TABLE_SIZE????????ο?MySQL??????????????????HEAP????????????????OnDisk??????OnDisk???????5.7?п??????INTERNAL_TMP_DISK_STORAGE_ENGINE????????????MyISAM???????InnoDB???檔
????????????????????Щ?????????????????????????????????дSQL??????????????????????????????в???????????Ч?????????е?Ч???
????????????????????t1??
????CREATE TABLE t1( a int?? b int); INSERT INTO t1 VALUES(1??2)??(3??4);
???????????е????????????t1???о??????
??????SQL????????SQL_BUFFER_RESULT hint
????SQL_BUFFER_RESULT?????????MySQL???????????????????????????????????????????佫?????????????????????????嵽??????п?????Ч??????????????????
???????磺
mysql> explain format=json select SQL_BUFFER_RESULT * from t1;
EXPLAIN
{
"query_block": {
"select_id": 1??
"cost_info": {
"query_cost": "2.00"
}??
"buffer_result": {
"using_temporary_table": true??
"table": {
"table_name": "t1"??
"access_type": "ALL"??
...
???????SQL????а?????DERIVED_TABLE??
??????5.7?У???????????μ??????????????????? set optimizer_switch=’derived_merge=off’?????derived table?????????Query?С?
???????磺
mysql> explain format=json select * from (select * from t1) as tt;
EXPLAIN
{
"query_block": {
"select_id": 1??
"cost_info": {
"query_cost": "2.40"
}??
"table": {
"table_name": "tt"??
"access_type": "ALL"??
...
"materialized_from_subquery": {
"using_temporary_table": true??
...
??????????????????????????????????洢???????????С?
?????????????????EXPLAIN????????????????????????????????????????????SHOW STATUS???????????????????????
???????磺
????mysql> select * from information_schema.character_sets;
????mysql> show status like 'CREATE%';
???????DISTINCT?????б?????????DISTINCT??????????GROUP BY????????????UNIQUE INDEX????DISTINCT?? ?????????????á?
????mysql> explain format=json select distinct a from t1;
????EXPLAIN
????{
????{
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "1.60"
????}??
????"duplicates_removal": {
????"using_temporary_table": true??
????...
??????????????ORDER BY????????????????????漸????????????????????????м???????????м????????????
????1?????????????BNL??Batched Nestloop??/BKA(Batched Key Access)
???????磺
????1))BNL????????
????mysql> explain format=json select * from t1?? t1 as t2 order by t1.a;
????EXPLAIN
????{
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "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);
????| {
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "1.20"
????}??
????"ordering_operation": {
????"using_temporary_table": true??
????"using_filesort": true??
????"grouping_operation": {
????"using_filesort": false??
????...
????drop index idx1 on t1;