\u4ea7\u54c1DBA\u5728\u505a\u65e5\u5e38SQL REVIEW\u65f6\uff0c\u9047\u5230\u4e00\u4e2a\u95ee\u9898\uff1a\u4e00\u4e2a\u5305\u542b\u5b50\u67e5\u8be2\u7684query\uff0c\u4f7f\u7528order by(order by query)\u7adf\u7136\u6bd4\u4e0d\u4f7f\u7528order by(normal query)\u6267\u884c\u65f6\u95f4\u66f4\u77ed\uff0c\u800c\u4e14\u662f\u5728order by\u65e0\u6cd5\u4f7f\u7528index\u6392\u5e8f\u7684\u60c5\u51b5\u4e0b\uff08\u4f7f\u7528filesort\uff09\uff0c\u8fd9\u4e2a\u95ee\u9898\u4e0d\u597d\u89e3\u91ca\uff0c\u53ea\u80fd\u501f\u52a9\u4e00\u4e9b\u5de5\u5177\u4ee5\u53ca\u8ddf\u8e2a\u4ee3\u7801\u5bfb\u627e\u539f\u56e0<\/p>\n
\u73b0\u8c61\u5728Percona 5.5.18\u548cOracle MySQL 5.1.48\u4e0a\u90fd\u5b58\u5728\uff0c\u672c\u6587\u6570\u636e\u548c\u5206\u6790\u57fa\u4e8ePercona 5.5.18
\n<\/p>\n
\r\nmysql> show create table ugcbutterfly\\G\r\n*************************** 1. row ***************************\r\n Table: ugcbutterfly\r\nCreate Table: CREATE TABLE `ugcbutterfly` (\r\n `id` bigint(20) NOT NULL AUTO_INCREMENT ,\r\n `status` tinyint(4) NOT NULL,\r\n `gmt_create` datetime NOT NULL,\r\n `gmt_modified` datetime NOT NULL,\r\n `memo` varchar(512) DEFAULT NULL,\r\n `userid` bigint(20) NOT NULL COMMENT,\r\n `u_contact1` varchar(512) DEFAULT NULL,\r\n `u_contact2` varchar(512) DEFAULT NULL,\r\n `b_name` varchar(512) DEFAULT NULL,\r\n `b_desc` varchar(1024) DEFAULT NULL,\r\n `b_type` tinyint(4) NOT NULL COMMENT,\r\n `b_total` bigint(20) NOT NULL COMMENT,\r\n `b_quantity` bigint(20) NOT NULL COMMENT,\r\n `startime` datetime NOT NULL COMMENT,\r\n `endtime` datetime NOT NULL COMMENT,\r\n `b_url1` varchar(1024) DEFAULT NULL,\r\n `b_url2` varchar(1024) DEFAULT NULL,\r\n `b_url3` varchar(1024) DEFAULT NULL,\r\n `b_url4` varchar(1024) DEFAULT NULL,\r\n `b_longitude` double(10,6) NOT NULL,\r\n `b_latitude` double(10,6) NOT NULL,\r\n `b_radius` bigint(20) NOT NULL,\r\n `b_max_longitude` double(10,6) NOT NULL,\r\n `b_max_latitude` double(10,6) NOT NULL,\r\n `b_min_longitude` double(10,6) NOT NULL,\r\n `b_min_latitude` double(10,6) NOT NULL,\r\n `user_name` varchar(512) DEFAULT NULL,\r\n `device_id` varchar(60) DEFAULT NULL,\r\n `store_name` varchar(512) DEFAULT NULL,\r\n `store_addr` varchar(1024) DEFAULT NULL,\r\n PRIMARY KEY (`id`),\r\n KEY `idx_wmc_cm_ugcbutterfly` (`userid`),\r\n KEY `idx_starttime_endtime_quantity` (`startime`,`endtime`,`b_quantity`),\r\n KEY `idx_start_end_tude` (`startime`,`endtime`,`b_min_longitude`),\r\n KEY `idx_6cols` (`startime`,`endtime`,`b_max_longitude`,`b_min_longitude`,`b_max_latitude`,`b_min_latitude`)\r\n) ENGINE=InnoDB\r\n1 row in set (0.00 sec)\r\n<\/pre>\n\u8868catchlog\u7ed3\u6784<\/h3>\n\r\nmysql> show create table catchlog\\G\r\n*************************** 1. row ***************************\r\n Table: catchlog\r\nCreate Table: CREATE TABLE `catchlog` (\r\n `id` bigint(20) NOT NULL AUTO_INCREMENT,\r\n `status` tinyint(4) NOT NULL,\r\n `gmt_create` datetime NOT NULL ,\r\n `gmt_modified` datetime NOT NULL,\r\n `userid` bigint(20) NOT NULL COMMENT,\r\n `butterfly_id` bigint(20) NOT NULL,\r\n `activity_id` bigint(20) NOT NULL,\r\n `memo` varchar(512) DEFAULT NULL,\r\n `username` varchar(128) NOT NULL,\r\n `distance` int(11) DEFAULT NULL,\r\n `type` tinyint(4) NOT NULL,\r\n PRIMARY KEY (`id`),\r\n KEY `idx_cm_catchlog_userid` (`userid`,`butterfly_id`,`activity_id`),\r\n KEY `idx_bid_status_gmt_modified` (`butterfly_id`,`status`,`gmt_modified`)\r\n) ENGINE=InnoDB\r\n1 row in set (0.00 sec)\r\n<\/pre>\n\u8868\u6570\u636e\u89c4\u6a21<\/h3>\n\r\nmysql> select count(*) from catchlog;\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n| 1930879 |\r\n+----------+\r\n1 row in set (11.14 sec)\r\n\r\nmysql> select count(*) from ugcbutterfly;\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n| 120952 |\r\n+----------+\r\n1 row in set (0.02 sec)\r\n<\/pre>\nquery\u4e0d\u5e26order by\u6267\u884c\u8ba1\u5212\uff1a<\/h3>\n\r\nmysql> explain select id,status,gmt_create,gmt_modified,memo, \r\n userid,u_contact1,u_contact2,b_name,b_desc, \r\n b_type,b_total,b_quantity,startime,endtime,\r\n b_url1,b_url2,b_url3,b_url4,b_longitude, \r\n b_latitude,b_radius,b_max_longitude,b_max_latitude,b_min_longitude,\r\n b_min_latitude, store_name, store_addr, u_contact1 as store_phone,\r\n ROUND(6378.138*2*ASIN(SQRT(POW(SIN((22.299439*PI()\/180-b_latitude*PI()\/180)\/2),2)+COS(22.299439*PI()\/180)*COS(b_latitude*PI()\/180)*POW(SIN((114.173881*PI()\/180-b_longitude*PI()\/180)\/2),2)))*1000) AS juli \r\n from ugcbutterfly b \r\n where b.status = 0 and b_quantity > 0\r\n and (b.startime < now() and b.endtime > now()) \r\n and b.b_type = 7 \r\n and b.b_max_longitude >= 120.118867 and b.b_min_longitude <= 120.118867\r\n and b.b_max_latitude >= 30.277737 and b.b_min_latitude <= 30.277737\r\n and b.b_radius > 0\r\n and b.id not in (select butterfly_id from catchlog where userid = 279104765);\r\n*************************** 1. row ***************************\r\n id: 1\r\n select_type: PRIMARY\r\n table: b\r\n type: ALL\r\npossible_keys: idx_starttime_endtime_quantity,idx_start_end_tude,idx_6cols\r\n key: NULL\r\n key_len: NULL\r\n ref: NULL\r\n rows: 127059\r\n Extra: Using where\r\n*************************** 2. row ***************************\r\n id: 2\r\n select_type: DEPENDENT SUBQUERY\r\n table: catchlog\r\n type: ref\r\npossible_keys: idx_cm_catchlog_userid,idx_bid_status_gmt_modified\r\n key: idx_cm_catchlog_userid\r\n key_len: 16\r\n ref: const,func\r\n rows: 1\r\n Extra: Using index\r\n2 rows in set (0.00 sec)\r\n<\/pre>\n\u67e5\u8be2\u7ed3\u679c\u53ca\u6267\u884c\u65f6\u95f4\uff1a
\n4 rows in set (0.34 sec)<\/p>\n
query\u5e26order by\u6267\u884c\u8ba1\u5212\uff0c\u53ca\u6267\u884c\u65f6\u95f4<\/h3>\n\r\n\r\nmysql> explain select id,status,gmt_create,gmt_modified,memo, \r\n userid,u_contact1,u_contact2,b_name,b_desc, \r\n b_type,b_total,b_quantity,startime,endtime,\r\n b_url1,b_url2,b_url3,b_url4,b_longitude, \r\n b_latitude,b_radius,b_max_longitude,b_max_latitude,b_min_longitude,\r\n b_min_latitude, store_name, store_addr, u_contact1 as store_phone,\r\n ROUND(6378.138*2*ASIN(SQRT(POW(SIN((22.299439*PI()\/180-b_latitude*PI()\/180)\/2),2)+COS(22.299439*PI()\/180)*COS(b_latitude*PI()\/180)*POW(SIN((114.173881*PI()\/180-b_longitude*PI()\/180)\/2),2)))*1000) AS juli \r\n from ugcbutterfly b \r\n where b.status = 0 and b_quantity > 0\r\n and (b.startime < now() and b.endtime > now()) \r\n and b.b_type = 7 \r\n and b.b_max_longitude >= 120.118867 and b.b_min_longitude <= 120.118867\r\n and b.b_max_latitude >= 30.277737 and b.b_min_latitude <= 30.277737\r\n and b.b_radius > 0\r\n and b.id not in (select butterfly_id from catchlog where userid = 279104765) order by juli;\r\n*************************** 1. row ***************************\r\n id: 1\r\n select_type: PRIMARY\r\n table: b\r\n type: ALL\r\npossible_keys: idx_starttime_endtime_quantity,idx_start_end_tude,idx_6cols\r\n key: NULL\r\n key_len: NULL\r\n ref: NULL\r\n rows: 127059\r\n Extra: Using where; Using filesort\r\n*************************** 2. row ***************************\r\n id: 2\r\n select_type: DEPENDENT SUBQUERY\r\n table: catchlog\r\n type: ref\r\npossible_keys: idx_cm_catchlog_userid,idx_bid_status_gmt_modified\r\n key: idx_cm_catchlog_userid\r\n key_len: 16\r\n ref: const,func\r\n rows: 1\r\n Extra: Using index\r\n2 rows in set (0.00 sec)\r\n<\/pre>\n\u67e5\u8be2\u7ed3\u679c\u53ca\u6267\u884c\u65f6\u95f4\uff1a
\n4 rows in set (0.13 sec)<\/p>\n
profile<\/h3>\n\r\nmysql> show profile for query 1; \r\n+----------------------+----------+\r\n| Status | Duration |\r\n+----------------------+----------+\r\n| starting | 0.000168 |\r\n| checking permissions | 0.000003 |\r\n| checking permissions | 0.000005 |\r\n| Opening tables | 0.000028 |\r\n| System lock | 0.000011 |\r\n| init | 0.000081 |\r\n| optimizing | 0.000020 |\r\n| statistics | 0.000137 |\r\n| preparing | 0.000021 |\r\n| executing | 0.000002 |\r\n| Sending data | 0.335989 |\r\n| optimizing | 0.000022 |\r\n| statistics | 0.000075 |\r\n| preparing | 0.000023 |\r\n| executing | 0.000002 |\r\n| Sending data | 0.001634 |\r\n| executing | 0.000002 |\r\n| Sending data | 0.000073 |\r\n| executing | 0.000002 |\r\n| Sending data | 0.000163 |\r\n| executing | 0.000002 |\r\n| Sending data | 0.000567 |\r\n| end | 0.000006 |\r\n| query end | 0.000004 |\r\n| closing tables | 0.000017 |\r\n| freeing items | 0.000044 |\r\n| logging slow query | 0.000002 |\r\n| logging slow query | 0.000157 |\r\n| cleaning up | 0.000004 |\r\n+----------------------+----------+\r\n29 rows in set (0.00 sec)\r\n\r\nmysql> show profile for query 2;\r\n+----------------------+----------+\r\n| Status | Duration |\r\n+----------------------+----------+\r\n| starting | 0.000158 |\r\n| checking permissions | 0.000003 |\r\n| checking permissions | 0.000004 |\r\n| Opening tables | 0.000023 |\r\n| System lock | 0.000010 |\r\n| init | 0.000076 |\r\n| optimizing | 0.000014 |\r\n| statistics | 0.000113 |\r\n| preparing | 0.000022 |\r\n| executing | 0.000002 |\r\n| Sorting result | 0.128376 |\r\n| optimizing | 0.000036 |\r\n| statistics | 0.000082 |\r\n| preparing | 0.000023 |\r\n| executing | 0.000002 |\r\n| Sending data | 0.000632 |\r\n| executing | 0.000001 |\r\n| Sending data | 0.000032 |\r\n| executing | 0.000002 |\r\n| Sending data | 0.000066 |\r\n| executing | 0.000002 |\r\n| Sending data | 0.000269 |\r\n| Sending data | 0.000162 |\r\n| end | 0.000006 |\r\n| query end | 0.000004 |\r\n| closing tables | 0.000012 |\r\n| freeing items | 0.000046 |\r\n| logging slow query | 0.000002 |\r\n| logging slow query | 0.000149 |\r\n| cleaning up | 0.000009 |\r\n+----------------------+----------+\r\n30 rows in set (0.00 sec)\r\n<\/pre>\n\u95ee\u9898\u5206\u6790<\/h3>\n
\u9996\u5148\u603b\u7ed3\u4e00\u4e0b\u95ee\u9898\uff1a
\n1\u3001\u76f8\u5bf9\u4e8enormal query\uff0c\u901a\u8fc7explain\u53ef\u4ee5\u53d1\u73b0order by query\u6267\u884c\u8ba1\u5212\u591a\u4e86\u4e00\u4e2aUsing filesort\uff0c\u8fd9\u4e2a\u8bf4\u660eorder by\u5b57\u6bb5`juli`\u65e0\u6cd5\u5229\u7528\u7d22\u5f15\u8fdb\u884c\u6392\u5e8f
\n2\u3001\u67e5\u8be2\u547d\u4e2d\u7684\u7ed3\u679c\u96c6\u8f83\u5c0f\uff084 rows\uff09\uff0c\u5373order by\u4ee3\u4ef7\u8f83\u5c0f
\n3\u3001\u5728\u6267\u884c\u65f6\u95f4\u4e0a\uff0corder by query\u65f6\u95f4\u77ed\u4e8enormal query\uff0c\u901a\u8fc7profile\u6765\u770b\uff0c\u5dee\u522b\u4e3b\u8981\u5728(Sorting result: 0.128376s) vs. (Sending data\uff1a0.335989s)\u4e0a\uff0c\u800c\u4e14\u53ef\u4ee5\u53d1\u73b0\uff0corder by query\u7684profile\u7ed3\u679c\u591a\u4e86\u4e00\u884c\uff1aSorting result<\/p>\n
systemtap<\/h3>\n
\u901a\u8fc7\u795e\u5668systemtap\u8ffd\u8e2a\u91cd\u8981\u51fd\u6570\u8c03\u7528\u6b21\u6570\uff1a(\u811a\u672c\u5f88\u7b80\u5355\uff0c\u5199\u51e0\u4e2aprobe\u5c31OK)<\/p>\n
\r\nnormal query\r\n# of evaluate_join_record: 120957, sub_select: 10, do_select: 5, JOIN::optimize 2, JOIN::exec 5\r\n\r\norder by query\r\n# of evaluate_join_record: 9, sub_select: 10, do_select: 5, JOIN::optimize 2, JOIN::exec 5\r\n<\/pre>\n\u53d1\u73b0evaluate_join_record\u51fd\u6570\u5728normal query\u4e2d\u8c03\u7528\u7684\u6b21\u6570\u57fa\u672c\u7b49\u4e8e\u8868ugcbutterfly\u7684\u884c\u6570+\u7ed3\u679c\u96c6\u884c\u6570\uff0c\u901a\u8fc7\u4ee3\u7801\u5206\u6790\u8fd9\u4e2a\u51fd\u6570\u7684\u529f\u80fd\uff1a
\n1\u3001\u8fd9\u4e2a\u51fd\u6570\u88absub_select\u8c03\u7528\uff0c\u4f46\u662f\u5b83\u5728\u81ea\u67e5\u6216\u8005\u591a\u8868join\u65f6\u4e5f\u4f1a\u8c03\u7528sub_select\uff0cnormal query\u6267\u884c\u8fc7\u7a0b\u4e2d\u7684\u4e00\u4e2a\u51fd\u6570\u5806\u6808<\/p>\n
\r\n#0 evaluate_join_record (join=0x7fa1cc078d90, join_tab=0x7fa1cc07b770, error=-1) at sql\/sql_select.cc:11805\r\n#1 0x00000000005a70b6 in sub_select (join=0x7fa1cc078d90, join_tab=0x7fa1cc07b770, end_of_records=false) at sql\/sql_select.cc:11745\r\n#2 0x00000000005abf8d in do_select (join=0x7fa1cc078d90, fields=0x7fa1cc064318, table=0x0, procedure=) at sql\/sql_select.cc:11535\r\n#3 0x00000000005c208e in JOIN::exec (this=0x7fa1cc078d90) at sql\/sql_select.cc:2391\r\n#4 0x00000000006fbef9 in subselect_single_select_engine::exec (this=0x7fa1cc073c00) at sql\/item_subselect.cc:2001\r\n#5 0x00000000006fad1d in Item_subselect::exec (this=0x7fa1cc073b10) at sql\/item_subselect.cc:277\r\n#6 0x00000000006fb3c0 in Item_in_subselect::val_bool (this=0x7fa1cc073b10) at sql\/item_subselect.cc:878\r\n#7 0x00000000006b1322 in Item_in_optimizer::val_int (this=0x7fa1cc074388) at sql\/item_cmpfunc.cc:1928\r\n#8 0x000000000069722c in Item::val_bool (this=0x7fa1cc074388) at sql\/item.cc:200\r\n#9 0x00000000006b074c in Item_func_not::val_int (this=0x7fa1cc073c40) at sql\/item_cmpfunc.cc:289\r\n#10 0x000000000069722c in Item::val_bool (this=0x7fa1cc073c40) at sql\/item.cc:200\r\n#11 0x00000000006b24fa in Item_cond_and::val_int (this=0x7fa1cc07a8f0) at sql\/item_cmpfunc.cc:4586\r\n#12 0x00000000005a224c in evaluate_join_record (join=0x7fa1cc074dc0, join_tab=0x7fa1cc078990, error=) at sql\/sql_select.cc:11818\r\n#13 0x00000000005a70b6 in sub_select (join=0x7fa1cc074dc0, join_tab=0x7fa1cc078990, end_of_records=) at sql\/sql_select.cc:11770\r\n#14 0x00000000005abf8d in do_select (join=0x7fa1cc074dc0, fields=0x6450f50, table=0x0, procedure=) at sql\/sql_select.cc:11535\r\n#15 0x00000000005c208e in JOIN::exec (this=0x7fa1cc074dc0) at sql\/sql_select.cc:2391\r\n#16 0x00000000005bdc69 in mysql_select (thd=0x644eb90, rref_pointer_array=, \r\n tables=, wild_num=0, fields=, conds=, og_num=0, \r\n order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fa1cc073d70, unit=0x6450828, select_lex=0x6450e48) \r\n at sql\/sql_select.cc:2600\r\n<\/pre>\n\u901a\u8fc7\u4ee3\u7801\u53ef\u4ee5\u53d1\u73b0\uff1amysql\u7684\u591a\u8868join\u662f\u901a\u8fc7\u6700\u5916\u5c42\u7684do_select\u8fdb\u53bb\uff0c\u8c03\u7528sub_select\u5b8c\u6210nested-loop join\uff0c\u800csub_select\u662f\u4e00\u4e2a\u9012\u5f52\u51fd\u6570\uff0c\u6839\u636e\u7d22\u5f15\u6216\u8005\u5168\u8868\u626b\u63cf\u5faa\u73af\u8bfb\u53d6\u8bb0\u5f55\uff0c\u7136\u540e\u8c03\u7528evaluate_join_record\uff0cevaluate_join_record\u6839\u636ewhere\u6761\u4ef6\u8fc7\u6ee4\uff0c\u5bf9\u4e8e\u6ee1\u8db3\u8981\u6c42\u7684\u8bb0\u5f55\u9012\u5f52\u8c03\u7528sub_select\u8fdb\u5165\u4e0b\u4e00\u5c42nested-loop join\uff0cevaluate_join_record\u90e8\u5206\u4ee3\u7801\u5206\u6790\uff1a<\/p>\n
\r\n ...\r\n if (found)\r\n {\r\n enum enum_nested_loop_state rc;\r\n \/* A match from join_tab is found for the current partial join. *\/\r\n rc= (*join_tab->next_select)(join, join_tab+1, 0);\r\n join->thd->warning_info->inc_current_row_for_warning();\r\n if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)\r\n return rc;\r\n ...\r\n }\r\n<\/pre>\n\u5176\u4e2djoin_tab->next_select\u662f\u4e00\u4e2a\u51fd\u6570\u6307\u9488\uff0c\u5bf9\u4e8ejoin_tables\u4e2d\u7684\u6700\u540e\u4e00\u4e2ajoin_tab\uff0cnext_select\u5728\u6700\u5916\u5c42\u7684do_select\u4e2d\u88ab\u8d4b\u503c\u4e3aend_send(\u53d1\u9001\u7ed3\u679c\u5230\u5ba2\u6237\u7aef)\uff0c\u5bf9\u4e8e\u5176\u5b83join_tab\uff0cjoin_tab->next_select\u5728make_join_readinfo(\u8be5\u51fd\u6570\u88abJOIN::optimize\u8c03\u7528)\u4e2d\u8d4b\u521d\u503csub_select.<\/p>\n
\u67e5\u770bexplain\u7ed3\u679c\uff0cugcbutterfly\u8868\u91c7\u7528\u5168\u8868\u626b\u63cf\u65b9\u5f0f\u8bfb\u53d6\u8bb0\u5f55\uff0c\u56e0\u6b64evaluate_join_record\u7684\u6267\u884c\u6b21\u6570\u5e94\u8be5\u7b49\u4e8e\uff1augcbutterfly + (\u6ee1\u8db3ugcbutterfly\u8868\u4e0a\u51fasubquery\u5916where\u6761\u4ef6\u7684\u8bb0\u5f55\u884c\u6570)\uff0c\u8fd9\u4e2a\u6570\u5b57\u4e0e120957\u57fa\u672c\u543b\u5408<\/p>\n
\u90a3\u4e3a\u4ec0\u4e48order by query\u6267\u884cevaluate_join_record\u5374\u4e0d\u662f120957? \u67e5\u770b\u6267\u884c\u5806\u6808\uff1a<\/p>\n
\r\n#0 evaluate_join_record (join=0x7fa1cc078d90, join_tab=0x7fa1cc07ba08, error=-1) at sql\/sql_select.cc:11805\r\n#1 0x00000000005a70b6 in sub_select (join=0x7fa1cc078d90, join_tab=0x7fa1cc07ba08, end_of_records=)\r\n at sql\/sql_select.cc:11770\r\n#2 0x00000000005abf8d in do_select (join=0x7fa1cc078d90, fields=0x7fa1cc064318, table=0x0, procedure=)\r\n at sql\/sql_select.cc:11535\r\n#3 0x00000000005c208e in JOIN::exec (this=0x7fa1cc078d90) at sql\/sql_select.cc:2391\r\n#4 0x00000000006fbef9 in subselect_single_select_engine::exec (this=0x7fa1cc073c00) at sql\/item_subselect.cc:2001\r\n#5 0x00000000006fad1d in Item_subselect::exec (this=0x7fa1cc073b10) at sql\/item_subselect.cc:277\r\n#6 0x00000000006fb3c0 in Item_in_subselect::val_bool (this=0x7fa1cc073b10) at sql\/item_subselect.cc:878\r\n#7 0x00000000006b1322 in Item_in_optimizer::val_int (this=0x7fa1cc0744b0) at sql\/item_cmpfunc.cc:1928\r\n#8 0x000000000069722c in Item::val_bool (this=0x7fa1cc0744b0) at sql\/item.cc:200\r\n#9 0x00000000006b074c in Item_func_not::val_int (this=0x7fa1cc073c40) at sql\/item_cmpfunc.cc:289\r\n#10 0x000000000069722c in Item::val_bool (this=0x7fa1cc073c40) at sql\/item.cc:200\r\n#11 0x00000000006b24fa in Item_cond_and::val_int (this=0x7fa1cc07ab48) at sql\/item_cmpfunc.cc:4586\r\n#12 0x00000000006895a9 in skip_record (thd=, table=, sortorder=0x7fa1d9b6fe30, \r\n s_length=3422616976, select=0x7fa1cc07acd8, max_rows=140333119110416, sort_positions=false, \r\n examined_rows=0x7fa1d9b700c0) at sql\/opt_range.h:888\r\n#13 find_all_keys (thd=, table=, sortorder=0x7fa1d9b6fe30, s_length=3422616976, \r\n select=0x7fa1cc07acd8, max_rows=140333119110416, sort_positions=false, examined_rows=0x7fa1d9b700c0) at sql\/filesort.cc:628\r\n#14 filesort (thd=, table=, sortorder=0x7fa1d9b6fe30, s_length=3422616976, \r\n select=0x7fa1cc07acd8, max_rows=140333119110416, sort_positions=false, examined_rows=0x7fa1d9b700c0) at sql\/filesort.cc:249\r\n#15 0x00000000005b756e in create_sort_index (thd=0x644eb90, join=0x7fa1cc074dc0, order=, \r\n filesort_limit=18446744073709551615, select_limit=, is_order_by=) at sql\/sql_select.cc:14178\r\n#16 0x00000000005c1faf in JOIN::exec (this=0x7fa1cc074dc0) at sql\/sql_select.cc:2355\r\n#17 0x00000000005bdc69 in mysql_select (thd=0x644eb90, rref_pointer_array=, \r\n tables=, wild_num=0, fields=, conds=, og_num=1, \r\n order=0x7fa1cc073dd8, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fa1cc073e98, \r\n unit=0x6450828, select_lex=0x6450e48) at sql\/sql_select.cc:2600\r\n<\/pre>\n\u901a\u8fc7\u4e0a\u9762\u4e24\u4e2a\u5806\u6808\u8f93\u51fa\uff0c\u8fdb\u4e00\u6b65\u5206\u6790\u4ee3\u7801\uff0c\u53ef\u4ee5\u5f97\u5230\u8fd9\u6837\u7684\u7ed3\u8bba\uff1a
\n1\u3001filesort\u901a\u8fc7find_all_keys–>skip_record–>Item_cond_and::val_int\u8c03\u7528\u5b50\u67e5\u8be2\u7b5b\u9009\u6570\u636e
\n2\u3001normal query\u4e2d\uff0c\u901a\u8fc7evaluate_join_record–>test(select_cond->val_int())–>Item_cond_and::val_int\u8c03\u7528\u5b50\u67e5\u8be2\u7b5b\u9009\u6570\u636e<\/p>\n
\u53ef\u4ee5\u770b\u51fafilesort\u51fd\u6570\u5176\u5b9e\u5df2\u7ecf\u5b8c\u6210\u4e86\u67e5\u8be2\u548c\u6392\u5e8f\uff0c\u4f46\u662f\u5728profile\u4e2d\u4e3a\u4ec0\u4e48order by query\u591a\u4e86\u4e00\u884c? \u67e5\u770bJOIN::exec\u4ee3\u7801\uff1a<\/p>\n
\r\n ...\r\n if (curr_join->group_list || curr_join->order)\r\n {\r\n DBUG_PRINT(\"info\",(\"Sorting for send_result_set_metadata\"));\r\n thd_proc_info(thd, \"Sorting result\");\r\n ...\r\n if (create_sort_index(thd, curr_join,\r\n\t\t\t curr_join->group_list ? \r\n\t\t\t curr_join->group_list : curr_join->order,\r\n\t\t\t curr_join->select_limit,\r\n\t\t\t (select_options & OPTION_FOUND_ROWS ?\r\n\t\t\t HA_POS_ERROR : unit->select_limit_cnt),\r\n curr_join->group_list ? TRUE : FALSE))\r\n ...\r\n }\r\n ...\r\n thd_proc_info(thd, \"Sending data\");\r\n DBUG_PRINT(\"info\", (\"%s\", thd->proc_info));\r\n result->send_result_set_metadata((procedure ? curr_join->procedure_fields_list :\r\n *curr_fields_list),\r\n Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF);\r\n error= do_select(curr_join, curr_fields_list, NULL, procedure);\r\n ...\r\n<\/pre>\n\u53ea\u8981\u662forder by query\uff0c\u90fd\u4f1a\u6bd4normal query\u591a\u4e00\u53e5\uff1athd_proc_info(thd, “Sorting result”)\uff0c\u8fd9\u4e2a\u4f1a\u53cd\u6620\u5728profile\u4e2d\uff0ccreate_sort_index\u628ado_select\u8981\u505a\u7684\u4e8b\u60c5\u90fd\u505a\u7684\u5dee\u4e0d\u591a\u4e86\uff08\u8fd8\u6ca1\u628a\u6570\u636e\u53d1\u9001\u5230\u5ba2\u6237\u7aef\uff09\uff0c\u68c0\u7d22\u51fa\u7ed3\u679c\u5e76\u5df2\u7ecf\u5b8c\u6210\u6392\u5e8f\uff0c\u90a3\u4e48do_select\u4e0d\u662f\u505a\u91cd\u590d\u5de5\u4f5c\u4e86? \u5e26\u7740\u7591\u95ee\u8ffd\u8e2a\u4ee3\u7801\uff0c\u53d1\u73b0\uff0c\u5728create_sort_index\u6267\u884c\u5b8cfilesort\u4e4b\u540e\uff0cselect_cond\u88ab\u7f6e\u4e3a\u7a7a\u4e86\uff0ccreate_sort_index\u4e2d\u90e8\u5206\u4ee3\u7801\uff1a<\/p>\n
\r\n ...\r\n if (select)\r\n {\r\n ...\r\n select->cleanup();\t\t\t\t\/\/ filesort did select\r\n tab->select= 0;\r\n table->quick_keys.clear_all(); \/\/ as far as we cleanup select->quick\r\n table->sort.io_cache= tablesort_result_cache;\r\n }\r\n ...\r\n<\/pre>\n\u7531\u4e8eselect_cond\u4e3aNULL, order by query\u5728filesort\u4e4b\u540e\uff0cdo_select–>sub_select->evaluate_join_record\u65f6\uff0cevaluate_join_record\u5e76\u4e0d\u4f1a\u6267\u884ctest(select_cond->val_int())\uff0c\u5b50\u67e5\u8be2\u4e0d\u4f1a\u88ab\u6267\u884c\uff0c\u4ece\u800c\u76f4\u63a5\u8c03\u7528end_send\u53d1\u9001\u6570\u636e\u5230\u5ba2\u6237\u7aef<\/p>\n
\u800c\u4e14\uff0cfilesort\u4e4b\u540e\uff0cdo_select\u8c03\u7528sub_select\u8bfb\u53d6\u6570\u636e\u65f6\u4f7f\u7528\u7684\u662frr_from_pointers\uff0c\u8fd9\u4e2a\u4e0enormal query\u4e2d\u4f7f\u7528index\/scan\u4e0d\u540c\uff08catchlog: join_read_always_key–>ha_index_init\uff0cugcbutterfly: join_init_read_record–>init_read_record\uff09<\/p>\n
\u56e0\u6b64\u95ee\u9898\u5c31\u53d8\u5f97\u7b80\u5355\u4e86\uff1aorder by query\u7684\u4e24\u4e2a\u9636\u6bb5\u4e2d\uff0cfilesort\u628a\u6570\u636e\u68c0\u7d22\u51fa\u6765\u5e76\u6392\u597d\u5e8f\uff0cdo_select\u8d1f\u8d23\u5c06\u6570\u636e\u53d1\u9001\u5230\u5ba2\u6237\u7aef\uff1bnormal query\u53ea\u6709do_select\u9636\u6bb5\uff0c\u5b83\u5faa\u73af\u4ece\u5916\u5c42\u8868\uff08ugcbutterfly\uff0c\u5168\u8868\u626b\u63cf\uff09\u8bfb\u51fa\u8bb0\u5f55\uff0c\u6ee1\u8db3\u6761\u4ef6\u7684\u884c\u4e0e\u5185\u5c42\u8868\uff08catchlog\uff0c\u4f7f\u7528\u7d22\u5f15\uff09join\uff0c\u53d1\u9001\u6570\u636e\u5230\u5ba2\u6237\u7aef<\/p>\n
\u4ece\u672c\u8d28\u4e0a\u8bb2\uff0c\u8fd9\u4e2a\u95ee\u9898\u5e94\u8be5\u662f\u7531order by\u5f15\u8d77\u7684\uff0c\u4e0esubquery\u5e94\u8be5\u65e0\u5173\uff0c\u5c06query\u4e2d\u7684subquery\u53bb\u6389\u540e\u6d4b\u8bd5\u4e00\u4e0b\uff1a(query 3\u548c4\u662f\u5728query1\u548c2\u7684\u57fa\u7840\u4e0a\u53bb\u6389\u5b50\u67e5\u8be2\uff0c\u67e5\u8be2\u7ed3\u679c\u96c6\u4e0d\u53d8\uff0c\u8fd8\u662f4\u6761\u8bb0\u5f55)<\/p>\n
\r\nmysql> show profile for query 3; \r\n+----------------------+----------+\r\n| Status | Duration |\r\n+----------------------+----------+\r\n| starting | 0.000133 |\r\n| checking permissions | 0.000006 |\r\n| Opening tables | 0.000022 |\r\n| System lock | 0.000010 |\r\n| init | 0.000069 |\r\n| optimizing | 0.000018 |\r\n| statistics | 0.000139 |\r\n| preparing | 0.000021 |\r\n| executing | 0.000002 |\r\n| Sending data | 0.339549 |\r\n| end | 0.000009 |\r\n| query end | 0.000005 |\r\n| closing tables | 0.000010 |\r\n| freeing items | 0.000043 |\r\n| logging slow query | 0.000002 |\r\n| logging slow query | 0.000123 |\r\n| cleaning up | 0.000004 |\r\n+----------------------+----------+\r\n17 rows in set (0.00 sec)\r\n\r\nmysql> show profile for query 4;\r\n+----------------------+----------+\r\n| Status | Duration |\r\n+----------------------+----------+\r\n| starting | 0.000144 |\r\n| checking permissions | 0.000007 |\r\n| Opening tables | 0.000021 |\r\n| System lock | 0.000010 |\r\n| init | 0.000069 |\r\n| optimizing | 0.000018 |\r\n| statistics | 0.000132 |\r\n| preparing | 0.000027 |\r\n| executing | 0.000002 |\r\n| Sorting result | 0.129252 |\r\n| Sending data | 0.000190 |\r\n| end | 0.000006 |\r\n| query end | 0.000005 |\r\n| closing tables | 0.000010 |\r\n| freeing items | 0.000039 |\r\n| logging slow query | 0.000002 |\r\n| logging slow query | 0.000140 |\r\n| cleaning up | 0.000010 |\r\n+----------------------+----------+\r\n18 rows in set (0.00 sec)\r\n<\/pre>\n\u731c\u6d4b\u6210\u7acb\uff0c\u4e0b\u4e00\u6b65\u5c06\u627e\u51fa\u4e3a\u4ec0\u4e48\u91c7\u7528filesort\u4f1a\u7701\u5374\u90a3\u4e48\u591a\u65f6\u95f4\uff0c\u7ee7\u7eed\u4f7f\u7528\u795e\u5668systemtap\u68c0\u6d4b\u4e0b\u8ff0\u51fd\u6570\u8c03\u7528\u6b21\u6570\u548c\u603b\u5171\u6267\u884c\u65f6\u95f4\uff1a<\/p>\n
\r\nnormal query\uff1a\r\nJOIN::exec:1(1586518us), evaluate_join_record:120953(1058604us), sub_select:2(1586451us)\r\ndo_select:1(1586488us), Item_cond_and::val_int:120952(450491us), create_sort_index:0(0us)\r\n\r\norder by query\uff1a\r\nJOIN::exec:1(766256us), evaluate_join_record:5(84us), sub_select:2(193us)\r\ndo_select:1(217us), Item_cond_and::val_int:120952(449748us), create_sort_index:1(765997us)\r\n<\/pre>\n\u6ce8\uff1a\u7531\u4e8esystemtap\u6ce8\u5165\u4e86\u4e00\u4e9b\u94a9\u5b50\uff0c\u5bfc\u81f4query\u6267\u884c\u65f6\u95f4\u5927\u5927\u5ef6\u957f\uff0c\u4f46\u662f\u4e0d\u5f71\u54cd\u7ed3\u679c\u5224\u65ad<\/p>\n
\u53ef\u4ee5\u53d1\u73b0\uff1a
\n1\u3001normal query\u4e3b\u8981\u65f6\u95f4\u5728JOIN::exec–>do_select–>sub_select<\/strong>\uff0corder by query\u4e3b\u8981\u65f6\u95f4\u5728JOIN::exec–>create_sort_index<\/strong>
\n2\u3001normal query\u4e2d\u7684sub_select\u8c03\u7528evaluate_join_record\u8fbe120953\u6b21\uff0c\u5360\u7528\u4e86\u4e3b\u8981\u65f6\u95f4
\n3\u3001normal query\u4e0eorder by query\u5728\u8fc7\u6ee4where\u6761\u4ef6\u4e0a\u5360\u7528\u7684\u65f6\u95f4\u57fa\u672c\u76f8\u540c\uff08Item_cond_and::val_int\uff09\uff0cnormal query\u4e2d\u7684sub_select\u9664\u4e86evaluate_join_record\u5360\u7528\u4e86\u7edd\u5927\u90e8\u5206\u8c03\u7528\u65f6\u95f4\uff0c\u8fd8\u6709\u90e8\u5206\u8f83\u5927\u8c03\u7528\u5f00\u9500\uff081586451us-1586451us\uff0c\u901a\u8fc7\u4ee3\u7801\u5206\u6790\u5e94\u8be5\u4e3a\u5168\u8868\u626b\u63cf\u4ee3\u4ef7\uff09<\/p>\n","protected":false},"excerpt":{"rendered":"