\u80cc\u666f\u4ea4\u4ee3<\/strong><\/div>\n
\u7528 tpcc-mysql\u200b \u5de5\u5177\u751f\u6210 50\u4e2a\u4ed3\u5e93 \u7684\u6d4b\u8bd5\u6570\u636e\uff0c\u8868 order_line \u5171\u6709 37970973 \u6761\u8bb0\u5f55\u3002<\/p>\n
\u67d0\u5de5\u5177\u5728\u8fd0\u884c\u8fc7\u7a0b\u4e2d\uff0c\u4f1a\u4ea7\u751f\u4e0b\u9762\u7684SQL\u8fdb\u884c\u67e5\u8be2\uff0cWHERE\u540e\u8ddf\u4e86N\u591a\u4e2a\u6761\u4ef6\uff1a<\/p>\n
mysql> select * from order_line where \r\n (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2221' and ol_number = '5') \r\nor (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2225' and ol_number = '1')\r\nor (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2155' and ol_number = '2')\r\n...<\/pre>\n
\u8fd9\u91cc\u8bf4\u7684N\u591a\u4e2a\uff0c\u662f\u6307\u603b\u5171\u670910000\u4e2aOR\u6761\u4ef6\uff0c\u8fd9\u6761SQL\u7684\u957f\u5ea6\u5927\u6982\u5c06\u8fd1800KB\u3002<\/p>\n
\u8fd9\u6761SQL\u5728\u6211\u7684\u6d4b\u8bd5\u670d\u52a1\u5668\u4e0a\uff0c\u8fd0\u884c\u4e86\u7ea656\u79d2\uff08\u53e6\u4e00\u4e2a\u6027\u80fd\u7565\u5dee\u7684\u673a\u5668\u4e0a\u8dd1\u4e861800\u79d2\u5de6\u53f3\u624d\u5b8c\u6210\uff09\uff0c\u5171\u626b\u63cf75563\u884c\u8bb0\u5f55\uff0c\u8fd4\u56de8192\u884c\u7ed3\u679c\uff1a<\/p>\n
# Query_time: 56.031955 Lock_time: 0.047795 Rows_sent: 8129 Rows_examined: 75563 ... Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 75563 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 ...\r\n...\r\n# InnoDB_pages_distinct: 501\r\n...\r\nselect * from order_line where ...<\/pre>\n
\u76f8\u5f53\u4e8e\u53ea\u505a\u4e861\u6b21\u7d22\u5f15\u8303\u56f4\u67e5\u8be2\uff0c\u4f46\u603b\u5171\u8981\u626b\u63cf7.5\u4e07\u6761\u6570\u636e\u3002<\/p>\n
\u95ee\u9898\u5206\u6790<\/strong><\/div>\n
\u53ea\u9700\u8981\u626b\u63cf 7.5\u4e07\u884c\u8bb0\u5f55\uff0c501\u4e2apage\uff0c\u8fd4\u56de8192\u884c\u7ed3\u679c\uff0c\u6b63\u5e38\u60c5\u51b5\u4e0b\u4e0d\u5e94\u8be5\u9700\u8981\u8fd9\u4e48\u4e45\u624d\u5bf9\uff0c\u80af\u5b9a\u662f\u54ea\u91cc\u6709\u95ee\u9898\u3002<\/p>\n
\u518d\u6b21\u624b\u52a8\u6267\u884c\u8fd9\u6761SQL\uff0c\u53d1\u73b0\u7684\u786e\u662f\u8fd9\u4e48\u6162\uff0c\u5e76\u4e14\u5728\u6700\u540e\u8fd8\u6709\u4e2a warnings \u63d0\u9192\uff0c\u67e5\u770b\u4e0b\u662f\u5565\u5185\u5bb9\uff1a<\/p>\n
mysql> show warnings\\G\r\n...\r\n Level: Warning\r\n Code: 3170\r\nMessage: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.<\/pre>\n
\u7b2c\u4e00\u6b21\u89c1\u5230\u8fd9\u79cd\u544a\u8b66\uff0c\u5148\u68c0\u67e5MySQL\u624b\u518c\uff0c\u770b\u770b range_optimizer_max_mem_size \u8fd9\u4e2a\u9009\u9879\u662f\u5e72\u561b\u7528\u7684\uff1a<\/p>\n
\u6587\u6863\u51fa\u5904\uff1ahttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-system-variables.html#sysvar_range_optimizer_max_mem_size\r\n\r\nThe limit on memory consumption for the range optimizer. A value of 0 means \u201cno limit.\u201d \r\nIf an execution plan considered by the optimizer uses the range access method but \r\nthe optimizer estimates that the amount of memory needed for this method would \r\nexceed the limit, it abandons the plan and considers other plans. For more \r\ninformation, see Limiting Memory Use for Range Optimization.<\/pre>\n
\u8fd9\u4e2a\u9009\u9879\u662f\u4eceMySQL 5.7.9\u5f00\u59cb\u5f15\u5165\u7684\uff0c\u7528\u4e8e\u63a7\u5236\u5f53\u4f18\u5316\u5668\u91c7\u7528\u8303\u56f4\uff08RANGE\uff09\u67e5\u8be2\u4f18\u5316\u65b9\u6848\u65f6\u4f7f\u7528\u7684\u5185\u5b58\u6d88\u8017\u9650\u5236\u3002<\/p>\n
\u5176\u9ed8\u8ba4\u503c\u4e3a8MB\uff085.7.12\u53ca\u4ee5\u4e0a\u7248\u672c\uff09\uff0c\u5f53\u8bbe\u7f6e\u4e3a0\u65f6\uff0c\u8868\u793a\u4e0d\u505a\u4efb\u4f55\u9650\u5236\u3002\u5f53WHERE\u67e5\u8be2\u6761\u4ef6\u91cc\u6709\u5f88\u591aOR\u3001AND\u7ec4\u6210\u65f6\uff0c\u4f18\u5316\u5668\u5224\u65ad\u8d85\u8fc7\u5185\u5b58\u6d88\u8017\u9650\u5236\uff0c\u5219\u4f1a\u8c03\u6574SQL\u6267\u884c\u8ba1\u5212\uff0c\u53d8\u6210\u5176\u4ed6\u6267\u884c\u65b9\u6848\uff0c\u751a\u81f3\u53ef\u80fd\u662f\u5168\u8868\u626b\u63cf\u3002<\/p>\n
\u8fd9\u4e5f\u5c31\u662f\u4e3a\u4ec0\u4e48\u6267\u884c\u4e0a\u9762\u7684\u5927SQL\u540e\uff0cMySQL\u4f1a\u6709\u8fd9\u6837\u7684\u544a\u8b66\u63d0\u793a\u4e86\u3002<\/p>\n
\u7ecf\u8fc7\u51e0\u6b21\u7b80\u5355\u5c1d\u8bd5\uff0c\u628a range_optimizer_max_mem_size \u9009\u9879\u503c\u8c03\u5927\u5230 24MB \u540e\uff0c\u8fd9\u4e2aSQL\u5c31\u53ef\u4ee5\u6b63\u5e38\u6267\u884c\uff0c\u5e76\u4e14\u8fd0\u884c\u901f\u5ea6\u5f88\u5feb\uff1a<\/p>\n
# Query_time: 6.721209 Lock_time: 0.044637 Rows_sent: 8129 Rows_examined: 8129 Read_first: 0 Read_last: 0 Read_key: 10000 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 ...\r\n...\r\n# InnoDB_pages_distinct: 81<\/pre>\n
\u6ce8\u610f\u5230\u51e0\u4e2a\u53d8\u5316\uff1a<\/p>\n
\u8017\u65f6\u4ece56\u79d2\u964d\u52306.7\u79d2\uff1b<\/li>\n
\u626b\u63cf\u884c\u6570\u4ece7.5\u4e07\u884c\u964d\u52308192\u884c\uff08\u8fd4\u56de\u7ed3\u679c\u6570\u4e0d\u53d8\uff09\uff1b<\/li>\n
Read_key\u4ece1\u589e\u52a0\u523010000\uff1b<\/li>\n
Read_next\u4ece75563\u964d\u52300\uff1b<\/li>\n
\u626b\u63cf\u7684page\u6570\u4ece501\u964d\u523081\u3002<\/li>\n
\u76f8\u5f53\u4e8e\u505a\u4e861\u4e07\u6b21\u7d22\u5f15\u5217\u7b49\u503c\u6761\u4ef6\u67e5\u8be2\u3002<\/p>\n
\u67e5\u8be2\u6548\u7387\u63d0\u5347\u975e\u5e38\u663e\u8457\u3002<\/p>\n
\u8fdb\u4e00\u6b65\u4f18\u5316<\/strong><\/div>\n
\u7ebf\u4e0a\u751f\u4ea7\u73af\u5883\u4e2d\uff0c\u5404\u5f0f\u5404\u6837\u7684SQL\u5c42\u51fa\u4e0d\u7a77\uff0c\u8fd9\u6b21\u53ef\u80fd\u662f\u4e00\u4e07\u6761OR\u6761\u4ef6\uff0c\u4e0b\u6b21\u53ef\u80fd\u662f\u5176\u4ed6\u7684\uff0c\u662f\u4e0d\u80fd\u65e0\u9650\u5ea6\u589e\u52a0\u6570\u636e\u5e93\u5185\u5b58\u6d88\u8017\u7684\u3002<\/p>\n
\u9488\u5bf9\u672c\u6848\u4e2d\u7684SQL\uff0c\u66f4\u597d\u7684\u4f18\u5316\u529e\u6cd5\u662f\u627e\u51fa\u8fd9\u4e9bOR\u6761\u4ef6\u7684\u8303\u56f4\u89c4\u5f8b\uff0c\u5e76\u6539\u5199\u6210\u4e00\u6761\u66f4\u7b80\u5355\u7684SQL\uff0c\u7c7b\u4f3c\u4e0b\u9762\u8fd9\u6837\uff1a<\/p>\n
mysql> select * from order_line where\r\nol_w_id = 1 and ol_d_id = 1 and (ol_o_id between 2007 and 2997) \r\nand (ol_number between 1 and 15 );<\/pre>\n
\u65b0\u7684SQL\u6267\u884c\u4ee3\u4ef7\uff1a<\/p>\n
# Query_time: 0.006338 Lock_time: 0.000084 Rows_sent: 9883 Rows_examined: 9883...Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 9883 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0...\r\n...\r\n# InnoDB_pages_distinct: 81<\/pre>\n
\u76f8\u5f53\u4e8e\u53ea\u505a\u4e861\u6b21\u7d22\u5f15\u8303\u56f4\u67e5\u8be2\uff0c\u4e14\u53ea\u9700\u626b\u63cf9883\u6761\u8bb0\u5f55\u3002<\/p>\n
\u76f8\u6bd4\u4e0a\u9762\u8c03\u9ad8\u5185\u5b58\u4e0a\u9650\u7684\u4f18\u5316\u65b9\u6848\uff0c\u672c\u6b21\u7684\u505a\u6cd5\u5219\u66f4\u4e3a\u5f7b\u5e95\uff0c\u8017\u65f6\u4ece6.7\u79d2\u76f4\u63a5\u964d\u4e3a6.3\u6beb\u79d2\uff0c\u63d0\u5347\u4e861000\u500d\uff1b\u626b\u63cf\u884c\u6570\u3001\u6b21\u6570\u548cpage\u6570\u4e5f\u4e0b\u964d\u4e86\u5f88\u591a\u3002<\/p>\n
\u4e0d\u8fc7\u8981\u6ce8\u610f\u7684\u662f\uff0c\u6539\u5199\u540e\u7684SQL\u67e5\u8be2\u7ed3\u679c\u548c\u539f\u6765\u5e76\u4e0d\u662f\u5b8c\u5168\u4e00\u81f4\u7684\uff0c\u5b9e\u9645\u5e94\u7528\u4e2d\uff0c\u53ef\u80fd\u8fd8\u8981\u518d\u505a\u8fdb\u4e00\u6b65\u7b5b\u9009\u6216\u8005\u589e\u52a0 LIMIT N \u6765\u63a7\u5236\u3002<\/p>\n
\u6700\u540e\u518d\u6b21\u63d0\u9192\uff0cWHERE\u6761\u4ef6\u540e\u8ddf\u7740N\u591a\u4e2aOR\/AND\u6761\u4ef6\u7684\u5199\u6cd5\u975e\u5e38\u4e0d\u53ef\u53d6\uff0c\u5c24\u5176\u662f\u5728\u7528\u4e00\u4e9b\u5f00\u53d1\u6846\u67b6\u6784\u9020\u67e5\u8be2SQL\u65f6\uff0c\u5c24\u5176\u8981\u6ce8\u610f\u89c4\u907f\u8fd9\u4e2a\u95ee\u9898\uff0c\u5426\u5219\u53ef\u80fd\u9020\u6210\u4e25\u91cd\u6027\u80fd\u95ee\u9898\u3002<\/p>\n
\u5ef6\u4f38\u9605\u8bfb<\/strong><\/div>\n
sysvars-range_optimizer_max_mem_size,https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-system-variables.html#sysvar_range_optimizer_max_mem_size<\/p>\n
Limiting Memory Use for Range Optimization,https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/range-optimization.html#range-optimization-memory-use<\/p>\n","protected":false},"excerpt":{"rendered":"
\u7528 tpcc-mysql\u200b \u5de5\u5177\u751f\u6210 50\u4e2a\u4ed3\u5e93 \u7684\u6d4b\u8bd5\u6570\u636e\uff0c\u8868 order_line \u5171\u6709 37970973 […]<\/p>\n","protected":false},"author":1920,"featured_media":103595,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[55],"tags":[],"class_list":["post-259799","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-thread"],"acf":[],"_links":{"self":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/259799","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/users\/1920"}],"replies":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/comments?post=259799"}],"version-history":[{"count":1,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/259799\/revisions"}],"predecessor-version":[{"id":259800,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/259799\/revisions\/259800"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media\/103595"}],"wp:attachment":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media?parent=259799"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/categories?post=259799"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/tags?post=259799"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}