{"id":634,"date":"2011-03-18T22:33:35","date_gmt":"2011-03-18T14:33:35","guid":{"rendered":"http:\/\/www.strongd.net\/?p=634"},"modified":"2011-03-18T22:33:35","modified_gmt":"2011-03-18T14:33:35","slug":"mysql%e6%95%b0%e6%8d%ae%e5%8e%8b%e7%bc%a9%e6%80%a7%e8%83%bd%e5%af%b9%e6%af%94%ef%bc%88%e4%ba%8c%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.strongd.net\/?p=634","title":{"rendered":"mysql\u6570\u636e\u538b\u7f29\u6027\u80fd\u5bf9\u6bd4\uff08\u4e8c\uff09"},"content":{"rendered":"

\u5728\u4e0a\u4e00\u7bc7\u6587\u7ae0\u4e2d\uff0c\u6211\u4eec\u7528\u751f\u4ea7\u73af\u5883\u7684\u771f\u5b9e\u6570\u636e\u4e0e\u771f\u5b9eSQL\u6d4b\u8bd5\u4e86archive\u548cmyisampack\u4e24\u79cd\u65b9\u5f0f\u4e0b\u7684\u6027\u80fd\u5bf9\u6bd4\u60c5\u51b5\u3002\u6211\u4eec\u5f97\u5230\u4e00\u4e2a\u5bf9\u8fd9\u4e2a\u6d4b\u8bd5case\u6709\u6548\u7684\u7ed3\u8bba\uff0c\u90a3\u5c31\u662f\u5728240\u4e07\u6570\u636e\u91cf\u7684\u60c5\u51b5\u4e0b\uff0c\u91c7\u7528archive\u5f15\u64ce\u5c06\u4f7f\u5f97\u67d0\u4e9b\u67e5\u8be2\u6162\u5f97\u65e0\u6cd5\u5fcd\u53d7!<\/p>\n

\u90a3\u4e48\uff0c\u539f\u56e0\u662f\u4ec0\u4e48\u5462?<\/p>\n

\u6211\u4eec\u77e5\u9053\uff0cmysql\u63d0\u4f9barchive\u8fd9\u79cd\u5b58\u50a8\u5f15\u64ce\u662f\u4e3a\u4e86\u964d\u4f4e\u78c1\u76d8\u5f00\u9500\uff0c\u4f46\u8fd8\u6709\u4e00\u4e2a\u524d\u63d0\uff0c\u90a3\u5c31\u662f\u88ab\u5f52\u6863\u7684\u6570\u636e\u4e0d\u9700\u8981\u6216\u8005\u5f88\u5c11\u88ab\u5728\u7ebf\u67e5\u8be2\uff0c\u5076\u5c14\u7684\u67e5\u8be2\u6162\u4e00\u4e9b\u4e5f\u662f\u6ca1\u5173\u7cfb\u7684\u3002\u9274\u4e8e\u4e0a\u8ff0\u539f\u56e0\uff0carchive\u8868\u662f\u4e0d\u5141\u8bb8\u5efa\u7acb\u81ea\u589e\u5217\u4e4b\u5916\u7684\u7d22\u5f15\u7684\u3002<\/p>\n

\u6709\u4e86\u8fd9\u4e2a\u5171\u8bc6\uff0c\u6211\u4eec\u62ff\u4e00\u6761\u6d4b\u8bd5SQL\u6765\u5206\u6790\u4e00\u4e0b\u4e0d\u7528\u7d22\u5f15\u524d\u540e\u7684\u67e5\u8be2\u6027\u80fd\u5dee\u522b\u4e3a\u4ec0\u4e48\u8fd9\u4e48\u5927\u3002\u5728\u6211\u4eec\u7684\u6d4b\u8bd5SQL\u4e2d\u6709\u8fd9\u4e48\u4e00\u6761\uff1a<\/p>\n

\n
\n
SELECT c1,c2,...,cn FROM  mysqlslap.rpt_topranks_v3\r\nWHERE ... AND partition_by1 = '50008090'\r\nORDER BY added_quantity3 DESC\r\nLIMIT 500<\/pre>\n<\/div>\n<\/div>\n
\u6211\u4eec\u524d\u8fb9\u8bf4\u8fc7\uff0c\u6d4b\u8bd5\u7684\u8fd9\u4e2a\u8868\u5728partition_by1\u8fd9\u4e2a\u5b57\u6bb5\u4e0a\u5efa\u7acb\u4e86\u7d22\u5f15\uff0c\u90a3\u4e48\uff0c\u6211\u4eec\u521d\u6b65\u5224\u65ad\u5728\u57fa\u51c6\u8868\u548cmyisampack\u8868\u4e0a\uff0c\u8fd9\u4e2a\u67e5\u8be2\u5e94\u8be5\u7528\u5230\u4e86partition_by1\u7684\u7d22\u5f15\uff1bEXPLAIN\u4e00\u4e0b\uff1a<\/p>\n
\n
\n
mysql&gt; EXPLAIN\r\n    -&gt; SELECT ... FROM  mysqlslap.rpt_topranks_v3\r\n    -&gt; WHERE ... AND partition_by1 = '50008090'\r\n    -&gt; ORDER BY added_quantity3 DESC\r\n    -&gt; LIMIT 500\\G\r\n*************************** 1. ROW ***************************\r\n           id: 1\r\n  select_type: SIMPLE\r\n        TABLE: rpt_topranks_v3\r\n         TYPE: REF\r\npossible_keys: idx_toprank_pid,idx_toprank_chg\r\n          KEY: idx_toprank_pid\r\n      key_len: 99\r\n          REF: const\r\n         ROWS: 2477\r\n        Extra: USING WHERE; USING filesort\r\n1 ROW IN SET (0.00 sec)<\/pre>\n<\/div>\n<\/div>\n
\u6b63\u5982\u6211\u4eec\u6240\u6599\uff0c\u8fd9\u4e2a\u67e5\u8be2\u7528\u5230\u4e86\u5efa\u7acb\u5728partition_by1\u8fd9\u4e2a\u5b57\u6bb5\u4e0a\u7684\u7d22\u5f15\uff0c\u5339\u914d\u7684\u76ee\u6807\u884c\u6570\u4e3a2477\uff0c\u7136\u540e\u8fd8\u6709\u4e00\u4e2a\u5728added_quantity3\u5b57\u6bb5\u4e0a\u7684\u6392\u5e8f\u3002\u7531\u4e8eadded_quantity3\u6ca1\u6709\u7d22\u5f15\uff0c\u6240\u4ee5\u7528\u5230\u4e86filesort\u3002<\/p>\n
\u6211\u4eec\u518d\u770b\u4e00\u4e0b\u8fd9\u6761SQL\u5728\u5f52\u6863\u8868\u4e0a\u7684EXPLAIN\u7ed3\u679c\uff1a<\/p>\n
\n
\n
mysql&gt; EXPLAIN\r\n    -&gt; SELECT ... FROM  mysqlslap.rpt_topranks_v3_<strong>archive<\/strong>\r\n    -&gt; WHERE ... AND partition_by1 = '50008090'\r\n    -&gt; ORDER BY added_quantity3 DESC\r\n    -&gt; LIMIT 500\\G\r\n*************************** 1. ROW ***************************\r\n           id: 1\r\n  select_type: SIMPLE\r\n        TABLE: rpt_topranks_v3_archive\r\n         TYPE: ALL\r\npossible_keys: NULL\r\n          KEY: NULL\r\n      key_len: NULL\r\n          REF: NULL\r\n         ROWS: 2424753\r\n        Extra: USING WHERE; USING filesort\r\n1 ROW IN SET (0.00 sec)<\/pre>\n<\/div>\n<\/div>\n
EXPLAIN\u8bf4\uff1a\u201c\u6211\u6ca1\u6709\u7d22\u5f15\u53ef\u7528\uff0c\u6240\u4ee5\u53ea\u80fd\u5168\u8868\u626b\u63cf2424753\u884c\u8bb0\u5f55\uff0c\u7136\u540e\u518d\u6765\u4e2afilesort\u3002\u201d\u4f60\u8981\u8ffd\u6c42\u6027\u80fd\uff0c\u90a3\u663e\u7136\u662f\u59d4\u5c48MySQL\u4e86\u3002<\/p>\n
\u6269\u5c55\u9605\u8bfb\uff1a<\/strong><\/p>\n
\u5173\u4e8eMYSQL\u7684ORDER BY\u5b9e\u73b0\u539f\u7406\uff0c\u8bf7\u53c2\u8003\u00a0http:\/\/isky000.com\/database\/mysql_order_by_implement<\/a><\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
\u5728\u4e0a\u4e00\u7bc7\u6587\u7ae0\u4e2d\uff0c\u6211\u4eec\u7528\u751f\u4ea7\u73af\u5883\u7684\u771f\u5b9e\u6570\u636e\u4e0e\u771f\u5b9eSQL\u6d4b\u8bd5\u4e86archive\u548cmyisampack\u4e24\u79cd\u65b9\u5f0f\u4e0b\u7684\u6027\u80fd\u5bf9\u6bd4\u60c5\u51b5\u3002\u6211\u4eec\u5f97\u5230\u4e00\u4e2a\u5bf9\u8fd9\u4e2a\u6d4b\u8bd5case\u6709\u6548\u7684\u7ed3\u8bba\uff0c\u90a3\u5c31\u662f\u5728240\u4e07\u6570\u636e\u91cf\u7684\u60c5\u51b5\u4e0b\uff0c\u91c7\u7528archive\u5f15\u64ce\u5c06\u4f7f\u5f97\u67d0\u4e9b\u67e5\u8be2\u6162\u5f97\u65e0\u6cd5\u5fcd\u53d7! \u90a3\u4e48\uff0c\u539f\u56e0\u662f\u4ec0\u4e48\u5462? \u6211\u4eec\u77e5\u9053\uff0cmysql\u63d0\u4f9barchive\u8fd9\u79cd\u5b58\u50a8\u5f15\u64ce\u662f\u4e3a\u4e86\u964d\u4f4e\u78c1\u76d8\u5f00\u9500\uff0c\u4f46\u8fd8\u6709\u4e00\u4e2a\u524d\u63d0\uff0c\u90a3\u5c31\u662f\u88ab\u5f52\u6863\u7684\u6570\u636e\u4e0d\u9700\u8981\u6216\u8005\u5f88\u5c11\u88ab\u5728\u7ebf\u67e5\u8be2\uff0c\u5076\u5c14\u7684\u67e5\u8be2\u6162\u4e00\u4e9b\u4e5f\u662f\u6ca1\u5173\u7cfb\u7684\u3002\u9274\u4e8e\u4e0a\u8ff0\u539f\u56e0\uff0carchive\u8868\u662f\u4e0d\u5141\u8bb8\u5efa\u7acb\u81ea\u589e\u5217\u4e4b\u5916\u7684\u7d22\u5f15\u7684\u3002 \u6709\u4e86\u8fd9\u4e2a\u5171\u8bc6\uff0c\u6211\u4eec\u62ff\u4e00\u6761\u6d4b\u8bd5SQL\u6765\u5206\u6790\u4e00\u4e0b\u4e0d\u7528\u7d22\u5f15\u524d\u540e\u7684\u67e5\u8be2\u6027\u80fd\u5dee\u522b\u4e3a\u4ec0\u4e48\u8fd9\u4e48\u5927\u3002\u5728\u6211\u4eec\u7684\u6d4b\u8bd5SQL\u4e2d\u6709\u8fd9\u4e48\u4e00\u6761\uff1a SELECT c1,c2,…,cn FROM mysqlslap.rpt_topranks_v3 WHERE … AND partition_by1 = ‘50008090’ ORDER BY added_quantity3 DESC LIMIT 500 \u6211\u4eec\u524d\u8fb9\u8bf4\u8fc7\uff0c\u6d4b\u8bd5\u7684\u8fd9\u4e2a\u8868\u5728partition_by1\u8fd9\u4e2a\u5b57\u6bb5\u4e0a\u5efa\u7acb\u4e86\u7d22\u5f15\uff0c\u90a3\u4e48\uff0c\u6211\u4eec\u521d\u6b65\u5224\u65ad\u5728\u57fa\u51c6\u8868\u548cmyisampack\u8868\u4e0a\uff0c\u8fd9\u4e2a\u67e5\u8be2\u5e94\u8be5\u7528\u5230\u4e86partition_by1\u7684\u7d22\u5f15\uff1bEXPLAIN\u4e00\u4e0b\uff1a mysql&gt; EXPLAIN -&gt; SELECT … FROM mysqlslap.rpt_topranks_v3 -&gt; WHERE … AND partition_by1 = ‘50008090’ -&gt; ORDER BY added_quantity3 DESC -&gt; LIMIT 500\\G *************************** 1. ROW *************************** id: 1 select_type: SIMPLE TABLE: rpt_topranks_v3 TYPE: REF … Continue reading mysql\u6570\u636e\u538b\u7f29\u6027\u80fd\u5bf9\u6bd4\uff08\u4e8c\uff09<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[203],"class_list":["post-634","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql"],"_links":{"self":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/634","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=634"}],"version-history":[{"count":1,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/634\/revisions"}],"predecessor-version":[{"id":635,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/634\/revisions\/635"}],"wp:attachment":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=634"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=634"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=634"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}