MySQL?е??????????
???????????? ???????[ 2016/7/6 10:34:49 ] ????????????? MySQL
????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??????????????????м??????棬?????????????????????????????????洢??????????????????????????????????????????????????????????????????????????????
???????????????????????漰???????????????????SPASVOС??(021-61079698-8054)?????????????????????????
??????
???mysql?????????????mysql????? distinct?÷?MySQL?н???????????з??Mysql?????????MySQL Cluster??δ????????????????ν?MySQL??????????300???????MySQL???Uber???????????????????MySql SQL??????????MySQL?????????????????λЧ?????SQL???Java???????:?????MySQL???????linux?°??mysql????MySQL ???????????????????binlog??MySQL????????????Щ??????MySQL???????Mysql???????JDBC?????mysql?????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11????????
?????????App Bug???????????????????????Jmeter?????????QC??????APP????????????????app?????е????????jenkins+testng+ant+webdriver??????????????JMeter????HTTP???????Selenium 2.0 WebDriver ??????