????3))ORDER BY?????а?????SCALAR SUBQUERY???????SCALAR SUBQUERY??б???????
???????磺
????explain format=json select (select rand() from t1 limit 1) as a from t1 order by a;
????| {
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "1.20"
????}??
????"ordering_operation": {
????"using_temporary_table": true??
????"using_filesort": true??
????...
????4) ???????????ORDER BY?????GROUP BY???????????????????в??????
???????: ?????5.7???????????sql_mode???????only_full_group_by????????????
?????????????м????????????INDEX?????GROUP BY???
???????磺
????set sql_mode='';
????create index idx1 on t1(b);
????explain format=json select t1.a from t1 group by t1.b order by 1;
????| {
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "1.40"
????}??
????"ordering_operation": {
????"using_temporary_table": true??
????"using_filesort": true??
????"grouping_operation": {
????"using_filesort": false??
????...
????drop index idx1 on t1;
??????????????GROUP BY????????????????????漸????????????????????????м???????????м????????GROUP BY??
????1?????????????BNL??Batched Nestloop??/BKA(Batched Key Access)??
???????磺
????explain format=json select t2.a from t1?? t1 as t2 group by t1.a;
????| {
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "8.20"
????}??
????"grouping_operation": {
????"using_temporary_table": true??
????"using_filesort": true??
????"cost_info": {
????"sort_cost": "4.00"
????...
????2) ???GROUP BY???в???????м???е???????????
???????磺
????explain format=json select t2.a from t1?? t1 as t2 group by t2.a;
????| {
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "8.20"
????}??
????"grouping_operation": {
????"using_temporary_table": true??
????"using_filesort": true??
????"nested_loop": [
????...
????3) ???GROUP BY???????????ORDER BY????????в????
???????磺
????set sql_mode='';
????explain format=json select t1.a from t1 group by t1.b order by t1.a;
????| {
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "1.40"
????}??
????"ordering_operation": {
????"using_filesort": true??
????"grouping_operation": {
????"using_temporary_table": true??
????"using_filesort": false??
????...
????4) ???GROUP BY????ROLLUP??????????????????
???????磺
????explain format=json select sum(t1.a) from t1 left join t1 as t2 on true group by t1.a with rollup;
????| {
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "7.20"
????}??
????"grouping_operation": {
????"using_temporary_table": true??
????"using_filesort": true??
????"cost_info": {
????"sort_cost": "4.00"
????}??
????...
????5) ???GROUP BY???????????????SCALAR SUBQUERY????????б???????
???????磺
????explain format=json select (select avg(a) from t1) as a from t1 group by a;
????| {
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "3.40"
????}??
????"grouping_operation": {
????"using_temporary_table": true??
????"using_filesort": true??
????"cost_info": {
????"sort_cost": "2.00"
????}??
????...
????IN????????semi-join???????
????1) ???semi-join??з???Materialization
???????磺
set optimizer_switch='firstmatch=off??duplicateweedout=off';
explain format=json select * from t1 where a in (select b from t1);
| {
"query_block": {
"select_id": 1??
"cost_info": {
"query_cost": "5.60"
}??
"nested_loop": [
{
"rows_examined_per_scan": 1??
"materialized_from_subquery": {
"using_temporary_table": true??
"query_block": {
"table": {
"table_name": "t1"??
"access_type": "ALL"??
...
????2) ???semi-join??з???Duplicate Weedout
???????磺
????set optimizer_switch='firstmatch=off';
????explain format=json select * from t1 where a in (select b from t1);
????| {
????"query_block": {
????"select_id": 1??
????"cost_info": {
????"query_cost": "4.80"
????}??
????"duplicates_removal": {
????"using_temporary_table": true??
????"nested_loop": [
????{
????...
????????????????UNION??MySQL?????????????????UNION?????????????
???????磺
????explain format=json select * from t1 union select * from t1;
????| {
????"query_block": {
????"union_result": {
????"using_temporary_table": true??
????"table_name": ""??
????...
??????????????????????
????????Explain??????????????????????????????status??
???????磺
????update t1?? t1 as t2 set t1.a=3;
????show status like 'CREATE%';
???????????????а??????o???????????????????á?
????1) count(distinct *)
???????磺
????explain format=json select count(distinct a) from t1;
????2) group_concat
???????磺
????explain format=json select group_concat(b) from t1;
????1) count(distinct *)
???????磺
????explain format=json select count(distinct a) from t1;
????2) group_concat
???????磺
????explain format=json select group_concat(b) from t1;
??????????????г???10???????MySQL??????????????????м??????棬?????????????????????????????????洢??????????????????????????????????????????????????????????????????????????????