{"id":879,"date":"2017-08-24T23:24:59","date_gmt":"2017-08-24T15:24:59","guid":{"rendered":"http:\/\/www.5x44.cn\/?p=879"},"modified":"2019-05-14T22:18:53","modified_gmt":"2019-05-14T14:18:53","slug":"mysql%e5%88%86%e5%8c%ba%ef%bc%88partition%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.5x44.cn\/?p=879","title":{"rendered":"mysql\u5206\u533a\u4ecb\u7ecd\uff08partition\uff09"},"content":{"rendered":"<p>\u4e00\u3001\u5206\u533a\u5e76\u4e0d\u662fSQL\u6807\u51c6\u7684\u4e00\u90e8\u5206<\/p>\n<p>\u4e8c\u3001\u901a\u8fc7\u5206\u533a\u53ef\u4ee5\u4f7f\u4e00\u4e2a\u8868\u7684\u4e0d\u540c\u90e8\u5206\u6839\u636e\u6211\u4eec\u8bbe\u7f6e\u7684\u89c4\u5219\u5b58\u50a8\u5230\u4e0d\u540c\u7684\u6587\u4ef6\u7cfb\u7edf\u4e0a\u3002\u5b9e\u9645\u4e0a\u4e00\u4e2a\u8868\u7684\u4e0d\u540c\u90e8\u5206\u505a\u4e3a\u8868\u7684\u5f62\u5f0f\u88ab\u5b58\u50a8\u5230\u4e0d\u540c\u4f4d\u7f6e\u3002<\/p>\n<p>\u4e09\u3001\u7528\u6237\u9009\u62e9\u89c4\u5219\u6765\u5206\u5272\u6570\u636e\uff0c\u5728\u8fd9\u4e9b\u6570\u636e\u770b\u6765\u89c4\u5219\u505a\u4e3a\u4e00\u79cd\u5206\u533a\u51fd\u6570\uff0c\u800c\u5206\u533a\u51fd\u6570\u5728MYSQL\u4e2d\u662f\u4e00\u4e2a\u201c\u7cfb\u6570\u201d\uff0c\u5b83\u7b80\u5355\u7684\u5339\u914d\u4e00\u7ec4\u8303\u56f4\u6216\u503c\u7684\u5217\u8868\u3001\u5185\u90e8\u54c8\u5e0c\u51fd\u6570\u3001\u6216\u4e00\u4e2a\u7ebf\u6027\u54c8\u5e0c\u51fd\u6570\u3002\u88ab\u9009\u62e9\u7684\u51fd\u6570\u6839\u636e\u5206\u533a\u7c7b\u578b\u7531\u7528\u6237\u6307\u5b9a\uff0c\u5b83\u4f7f\u7528\u7684\u53c2\u6570\u503c\u7531\u7528\u6237\u63d0\u4f9b\u4e00\u4e2a\u8868\u8fbe\u5f0f\u3002\u8fd9\u4e2a\u8868\u8fbe\u5f0f\u53ef\u4ee5\u662f\u4e00\u5217\u503c\u3001\u4e00\u4e2a\u4f5c\u7528\u5728\u4e00\u5217\u6216\u591a\u5217\u4e0a\u7684\u51fd\u6570\u3001\u6216\u662f\u4e00\u5217\u6216\u51e0\u5217\u7684\u503c\uff0c\u8fd9\u4e00\u4f9d\u8d56\u4e8e\u4f7f\u7528\u7684\u5206\u533a\u7c7b\u578b\u3002<\/p>\n<p>\u56db\u3001\u5728range\u3001list\u548c[linear] hash\u5206\u533a\u7684\u4f8b\u5b50\u4e2d\uff0c\u5206\u533a\u5217\u7684\u503c\u88ab\u4f20\u5230\u5206\u533a\u51fd\u6570\u4e2d\uff0c\u51fd\u6570\u8fd4\u56de\u4e00\u4e2a\u6574\u6570\u503c\uff0c\u8fd9\u4e2a\u6574\u6570\u503c\u7528\u6765\u63cf\u8ff0\u6570\u636e\u5e94\u8be5\u88ab\u5b58\u5230\u54ea\u4e2a\u7279\u5b9a\u7684\u5206\u533a\u4e2d\u3002\u8fd9\u4e2a\u51fd\u6570\u5fc5\u987b\u662f\u975e\u8fde\u7eed\u7684\u3001\u975e\u968f\u673a\u7684\u3002\u5b83\u4e0d\u53ef\u4ee5\u5305\u51fd\u4efb\u4f55\u67e5\u8be2\uff0c\u4f46\u662f\u53ef\u4ee5\u4f7f\u7528MYSQL\u4e2d\u6709\u6548\u7684SQL\u8868\u8fbe\u5f0f\uff0c\u53ea\u8981\u8868\u8fbe\u5f0f\u8fd4\u56deNULL\u6216\u6574\u6570\u3002\u8fd4\u56de\u7684\u6574\u6570\u5e94\u8be5\u5728-MAXVALUE\u5230MAXVALUE\u4e4b\u95f4\u3002<\/p>\n<p>\u5bf9\u4e8e[linear] key\uff0c range columns\uff0clist columns\u5206\u533a\uff0c\u5206\u533a\u8868\u8fbe\u5f0f\u7531\u4e00\u5217\u6216\u591a\u5217\u7ec4\u6210\u3002\u5bf9\u4e8e[linear] key\u7c7b\u578b\uff0c \u5206\u533a\u51fd\u6570\u662f\u7531MYSQL\u63d0\u4f9b\u3002<\/p>\n<p>\u4e94\u3001\u6c34\u5e73\u5206\u533a\u2014\u2014\u4e00\u4e2a\u8868\u4e2d\u7684\u4e0d\u540c\u884c\u53ef\u4ee5\u88ab\u5b58\u8d2e\u5230\u4e0d\u540c\u7684\u7269\u7406\u5206\u533a\u4e2d\u3002\u5782\u76f4\u5206\u533a\u2014\u2014\u4e00\u4e2a\u8868\u4e2d\u7684\u4e0d\u540c\u5217\u53ef\u4ee5\u88ab\u5b58\u8d2e\u5230\u4e0d\u540c\u7684\u7269\u7406\u5206\u533a\u4e2d\uff0c\u4f46\u5728MYSQL5.7\u4e2d\u5e76\u4e0d\u652f\u6301\u8fd9\u79cd\u5206\u533a\u3002<\/p>\n<p>\u4f7f\u7528<code>show plugins;<\/code>\u547d\u4ee4\u6765\u67e5\u770b\u662f\u5426\u652f\u6301\u5206\u533a\u3002\u5982\u679cpartition\u7684status\u5217\u663e\u793aACTIVE\uff0c\u8868\u793a\u5f53\u524dMYSQL\u7248\u672c\u652f\u6301\u5206\u533a\u3002<\/p>\n<p>\u516d\u3001\u53ef\u4ee5\u4f7f\u7528\u4efb\u4f55MySQL\u652f\u6301\u7684\u5f15\u64ce\u521b\u5efa\u5206\u533a\u8868\uff0cMySQL5.7\u4e2d\u540c\u4e00\u4e2a\u8868\u7684\u4e0d\u540c\u5206\u533a\u8981\u4f7f\u7528\u76f8\u540c\u7684\u5f15\u64ce\u3002\u4f46\u662f\uff0cMYSQL\u5206\u533a\u4e0d\u80fd\u4f7f\u7528MERGE\uff0cCSV\u6216FEDERATED\u5b58\u50a8\u5f15\u64ce\u3002<\/p>\n<p>DNB\u5f15\u64ce\u662f\u53ef\u4ee5\u7531KEY\u6216LINEAR KEY\u5206\u533a\u7684\uff0c\u4f46\u662f\u5176\u5b83\u7528\u6237\u5b9a\u4e49\u7c7b\u578b\u7684\u5206\u533a\u5e76\u4e0d\u652f\u6301\u8fd9\u79cd\u5f15\u64ce\u3002\u53e6\u5916NDB\u8868\u4f7f\u7528\u7528\u6237\u5b9a\u4e49\u5206\u533a\u65f6\u5fc5\u987b\u5305\u542b\u660e\u663e\u7684\u4e3b\u952e\uff0c\u5e76\u4e14\u5176\u5b83\u7d22\u5f15\u5230\u8be5\u8868\u7684\u5217\u5fc5\u987b\u5305\u542b\u4e3b\u952e\u5728\u5185\u3002<\/p>\n<p>\u4e03\u3001\u4f7f\u7528\u7279\u5b9a\u7684\u5b58\u50a8\u5f15\u64ce\u5728\u5206\u533a\u8868\u4e2d\u548c\u975e\u5206\u533a\u8868\u4e00\u6837\uff0c\u4f46\u662f\u8981\u4fdd\u8bc1<strong>ENGINE<\/strong>\u5173\u952e\u5b57\u5728\u5206\u533a\u9009\u9879\u4e4b\u524d\u3002\u4e0b\u9762\u6709\u4e00\u4e2aInnoDB\u5f15\u64ce\u7684\u54c8\u5e0c\u5206\u533a\u8868\uff0c\u5206\u6210\u4e86\u516d\u4e2a\u8868\uff0c\u4f8b\uff1a<\/p>\n<pre>create table ti(id INT, amount DECIMAL(7,2), tr_date DATE)ENGINE=INNODB\npartition by hash(month(tr_date))\npartitions 6;\n<\/pre>\n<p>\u516b\u3001\u6bcf\u4e2a\u5206\u533a\u7684\u6570\u636e\u548c\u7d22\u5f15\u80fd\u88ab\u5b58\u5230\u6307\u5b9a\u7684\u76ee\u5f55\u4e2d\uff0c\u901a\u8fc7<code>data directory<\/code>\u548c<code>index directory<\/code>\u9009\u9879\u6307\u5b9a\uff0c\u8fd9\u4e2a\u9009\u9879\u7528\u4e8e<code>create table<\/code>\u7684partition\u5b50\u53e5\u4e2d\uff0c\u7528\u4e8e\u5efa\u7acb\u5206\u533a\u8868\u3002<\/p>\n<p>\u6ce8\u610f\uff1adata directory\u548cindex directory\u5e76\u4e0d\u652f\u6301\u5355\u72ec\u5206\u533a\u6216windows\u4e0a\u7684MYISAM\u8868\u7684\u5b50\u5206\u533a\u3002\u5b83\u4eec\u53ea\u88ab\u652f\u6301\u5728\u6240\u6709\u5e73\u53f0\u4e0a\u7684InnoDB\u8868\u7684\u72ec\u7acb\u5206\u533a\u548c\u5b50\u5206\u533a\u3002<\/p>\n<p>\u4e5d\u3001\u6240\u6709\u7528\u4e8e\u5206\u533a\u8868\u8fbe\u5f0f\u7684\u5217\uff0c\u5fc5\u987b\u662f\u6bcf\u552f\u4e00\u7ea6\u675f\u548c\u4e3b\u952e\u7684\u4e00\u90e8\u5206\uff0c\u8fd9\u610f\u5473\u7740\u4e0b\u9762\u7684\u8868\u4e0d\u80fd\u88ab\u5206\u533a\uff1a<\/p>\n<pre>create table tnp(\n   id int not null auto_increment,\n   ref bigint not null,\n   name varchar(255),\n   primary key pk (id),\n   unique key uk(name)\n);\n<\/pre>\n<p>\u56e0\u4e3apk\u548cuk\u6ca1\u6709\u5171\u540c\u7684\u5217\uff0c\u6240\u4ee5\u6ca1\u6709\u5217\u53ef\u4ee5\u7528\u4e8e\u5206\u533a\u8868\u8fbe\u5f0f\u3002\u8981\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898\u53ef\u4ee5\u5c06name\u6dfb\u52a0\u5230\u4e3b\u952e\u4e2d\u5e76\u5c06id\u52a0\u5230\u552f\u4e00\u7ea6\u675fuk\u4e2d\u3002\u6216\u662f\u7b80\u5355\u7684\u628a\u552f\u4e00\u7ea6\u675f\u5220\u9664\u3002<\/p>\n<p>\u5341\u3001\u4f7f\u7528\u5206\u533a\u7684\u4e00\u4e9b\u4f18\u70b9\uff1a<\/p>\n<ul>\n<li>\u76f8\u6bd4\u6570\u636e\u5b58\u50a8\u5728\u5355\u72ec\u7684\u78c1\u76d8\u6216\u6587\u4ef6\u7cfb\u7edf\u4e0a\uff0c\u5206\u533a\u53ef\u4ee5\u8ba9\u5355\u4e2a\u8868\u5b58\u50a8\u66f4\u591a\u7684\u6570\u636e\uff08\u5982\uff0c\u67d0\u4e9b\u6587\u4ef6\u7cfb\u7edf\u8981\u6c42\u5355\u4e2a\u6587\u4ef6\u4e0d\u80fd\u8d85\u8fc74GB\uff0c\u4f7f\u7528\u5206\u533a\u5c31\u53ef\u4ee5\u7a81\u7834\u8fd9\u4e2a\u9650\u5236\uff0c\u8ba9\u5355\u4e2a\u8868\u8d85\u8fc74G\uff09\u3002<\/li>\n<li>Data that loses its usefulness can often be easily removed from a partitioned table by dropping the partition (or partitions) containing only that data. Conversely, the process of adding new data can in some cases be greatly facilitated by adding one or more new partitions for storing specifically that data.\u53ef\u4ee5\u5f88\u5feb\u7684\u6dfb\u52a0\u5220\u9664\u6570\u636e\uff1b\u5220\u9664\u6570\u636e\u53ea\u8981\u5220\u9664\u76f8\u5e94\u5206\u533a\u5c31\u53ef\u4ee5\uff0c\u6dfb\u52a0\u53ea\u8981\u6dfb\u52a0\u65b0\u5206\u533a\u3002<\/li>\n<li>\u4e00\u4e9b\u6ee1\u8db3where\u6761\u4ef6\u7684\u67e5\u8be2\u4e5f\u53ef\u4ee5\u5f97\u5230\u4f18\u5316\uff0c\u53ea\u5728\u67d0\u4e00\u5206\u533a\u4e2d\u67e5\u627e\uff0c\u4e0d\u4f1a\u6d89\u53ca\u5176\u5b83\u5206\u533a\u3002\u56e0\u4e3a\u53ef\u4ee5\u628a\u5e38\u7528\u6570\u636e\u653e\u5230\u4e00\u4e2a\u5206\u533a\u4e2d\uff0c\u4e0d\u5e38\u7528\u7684\u6570\u636e\u653e\u5230\u5176\u5b83\u5206\u533a\u4e2d\u3002In addition, MySQL 5.7 supports explicit partition selection for queries. For example, <a class=\"link\" title=\"13.2.9\u00a0SELECT Syntax\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/select.html\"><code class=\"literal\">SELECT * FROM t<br \/>\nPARTITION (p0,p1) WHERE c &lt; 5<\/code><\/a> selects only those rows in partitions <code class=\"literal\">p0<\/code> and <code class=\"literal\">p1<\/code> that match the <code class=\"literal\">WHERE<\/code> condition. In this case, MySQL does not check any other partitions of table <code class=\"literal\">t<\/code>; this can greatly speed up queries when you already know which partition or partitions you wish to examine. Partition selection is also supported for the data modification statements <a class=\"link\" title=\"13.2.2\u00a0DELETE Syntax\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/delete.html\"><code class=\"literal\">DELETE<\/code><\/a>, <a class=\"link\" title=\"13.2.5\u00a0INSERT Syntax\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/insert.html\"><code class=\"literal\">INSERT<\/code><\/a>, <a class=\"link\" title=\"13.2.8\u00a0REPLACE Syntax\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/replace.html\"><code class=\"literal\">REPLACE<\/code><\/a>, <a class=\"link\" title=\"13.2.11\u00a0UPDATE Syntax\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/update.html\"><code class=\"literal\">UPDATE<\/code><\/a>, and <a class=\"link\" title=\"13.2.6\u00a0LOAD DATA INFILE Syntax\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/load-data.html\"><code class=\"literal\">LOAD DATA<\/code><\/a>, <a class=\"link\" title=\"13.2.7\u00a0LOAD XML Syntax\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/load-xml.html\"><code class=\"literal\">LOAD XML<\/code><\/a>. See the descriptions of these statements for more information and examples.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u3001\u5206\u533a\u5e76\u4e0d\u662fSQL\u6807\u51c6\u7684\u4e00\u90e8\u5206 \u4e8c\u3001\u901a\u8fc7\u5206\u533a\u53ef\u4ee5\u4f7f\u4e00\u4e2a\u8868\u7684\u4e0d\u540c\u90e8\u5206\u6839\u636e\u6211\u4eec\u8bbe\u7f6e\u7684\u89c4\u5219\u5b58\u50a8\u5230\u4e0d\u540c\u7684\u6587\u4ef6\u7cfb\u7edf\u4e0a\u3002\u5b9e\u9645\u4e0a\u4e00\u4e2a\u8868\u7684\u4e0d\u540c\u90e8\u5206\u505a\u4e3a\u8868\u7684\u5f62\u5f0f\u88ab\u5b58\u50a8\u5230\u4e0d\u540c\u4f4d\u7f6e&#8230;<\/p>\n<p class=\"read-more\"><a class=\"btn btn-default\" href=\"https:\/\/www.5x44.cn\/?p=879\"> Read More<span class=\"screen-reader-text\">  Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37],"tags":[],"class_list":["post-879","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/posts\/879","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=879"}],"version-history":[{"count":12,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/posts\/879\/revisions"}],"predecessor-version":[{"id":7146,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=\/wp\/v2\/posts\/879\/revisions\/7146"}],"wp:attachment":[{"href":"https:\/\/www.5x44.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=879"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=879"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.5x44.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=879"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}