— \u5206\u533a;
\n1.\u5206\u533a\u5c31\u662f\u5c06\u6570\u636e\u5e93\u6216\u5176\u6784\u6210\u5143\u7d20\u5212\u5206\u4e3a\u4e0d\u540c\u7684\u72ec\u7acb\u90e8\u5206;
\n 1.\u9884\u5148\u7ec4\u7ec7\u8868\u5b58\u50a8\u7684\u65b9\u6cd5;
\n 2.\u5f53\u5927\u578b\u8868\u5360\u7528\u5927\u91cf\u53ef\u7528\u78c1\u76d8\u7a7a\u95f4\u5e76\u4e14\u9700\u8981\u5176\u4ed6\u7a7a\u95f4\u6765\u5b58\u50a8\u8868\u6570\u636e\u65f6,\u53ef\u4ee5\u4f7f\u7528\u7269\u7406\u5206\u533a;
\n 3.\u8868\u6587\u4ef6\u53ef\u4ee5\u653e\u5728\u591a\u4e2a\u4f4d\u7f6e,\u800c\u4e0d\u662f\u5c06\u6570\u636e\u96c6\u4e2d\u4e8e\u4e00\u4e2a\u8fc7\u8f7d\u7684\u78c1\u76d8;
\n 4.\u6b64\u6280\u672f\u4e0e\u5b58\u50a8\u5f15\u64ce\u6709\u5173;
\n2.MySQL\u652f\u6301\u6c34\u5e73\u5206\u533a;
\n 1.\u5c06\u7279\u5b9a\u8868\u884c\u5206\u914d\u4e3a\u884c\u7684\u5b50\u96c6;
\n 2.\u6709\u6c34\u5e73\u5206\u533a\u548c\u5782\u76f4\u5206\u533a,\u672c\u8bfe\u7740\u91cd\u8bb2\u8ff0\u79f0\u4e3a\u201c\u6c34\u5e73\u5206\u533a\u201d\u7684MySQL\u5206\u533a\u6280\u672f; <\/p>\n
— \u5206\u533a\u7c7b\u6bd4;
\n1.\u201c\u642c\u5bb6\u201d\u7684\u7c7b\u6bd4:
\n 1.\u5047\u5b9a\u60a8\u6b63\u642c\u51fa\u5bb6\u5916,\u60a8\u53ef\u4ee5\u5c06\u5ba2\u5385\u4e2d\u7684\u6240\u6709\u7269\u54c1\u90fd\u653e\u5230\u4e00\u4e2a\u5927\u76d2\u5b50\u4e2d;
\n 2.\u4f46\u662f,\u8fd9\u4e0d\u662f\u5f88\u5b9e\u9645;\u60a8\u5c06\u5f88\u96be\u627e\u5230\u5355\u4e2a\u7269\u54c1,\u5e76\u4e14\u8fd9\u5c06\u662f\u4e00\u4e2a\u975e\u5e38\u5927\u5e76\u4e14\u975e\u5e38\u91cd\u7684\u76d2\u5b50;
\n 3.\u6700\u597d\u4f7f\u7528\u591a\u4e2a\u76d2\u5b50\u6765\u5b58\u653e\u60a8\u7684\u6240\u6709\u7269\u54c1,\u5e76\u4e14\u6839\u636e\u7c7b\u578b\u5bf9\u8fd9\u4e9b\u76d2\u5b50\u5206\u7c7b;
\n2.\u201cHome\u201d\u6570\u636e\u5e93\u793a\u4f8b:
\n 1.\u53ef\u4ee5\u5c06\u6807\u7b7e\u4e3a\u201c\u4e66\u7c4d\u201d\u7684\u76d2\u5b50\u7528\u4e8e\u6240\u6709\u4e66\u7c4d;
\n 2.\u6807\u7b7e\u4e3a\u201cCD\u201d\u7684\u76d2\u5b50\u7528\u4e8eCD,\u6807\u7b7e\u4e3a\u201c\u5f71\u7247\u201d\u7684\u76d2\u5b50\u7528\u4e8e\u5f71\u7247\u7b49\u7b49;
\n 3.\u60a8\u73b0\u5728\u5df2\u5c06\u60a8\u7684\u7269\u54c1\u201c\u5206\u533a\u201d\u5230\u7279\u5b9a\u76d2\u5b50\u4e2d;<\/p>\n
— \u7279\u5b9a\u4e8eMySQL\u7684\u5206\u533a;
\n1.\u5206\u533a\u7684\u5206\u5e03\u662f\u8de8\u7269\u7406\u5b58\u50a8\u8fdb\u884c\u7684:
\n 1.\u6839\u636e\u7528\u6237\u5728\u9700\u8981\u65f6\u8bbe\u7f6e\u7684\u6307\u5b9a\u89c4\u5219:MySQL\u5206\u533a\u5141\u8bb8\u4f60\u8bbe\u7f6e\u7684\u89c4\u5219\u5c06\u5404\u4e2a\u8868\u7684\u90e8\u5206\u5206\u5e03\u5728\u6574\u4e2a\u7269\u7406\u5b58\u50a8\u4e2d;
\n 2.\u6bcf\u4e2a\u5206\u533a\u5b58\u50a8\u4e3a\u5355\u72ec\u7684\u5355\u5143,\u975e\u5e38\u50cf\u4e00\u4e2a\u8868;
\n2.\u6570\u636e\u7684\u5212\u5206:
\n 1.\u6839\u636e\u5206\u533a\u529f\u80fd\u5c06\u6570\u636e\u5212\u5206\u4e3a\u5b50\u96c6:\u6570\u636e\u7684\u5212\u5206\u901a\u8fc7\u5206\u533a\u529f\u80fd\u6765\u5b8c\u6210,\u8fd9\u5728MySQL\u4e2d\u53ef\u4ee5\u662f\u9488\u5bf9\u4e00\u7ec4\u8303\u56f4\u6216\u503c\u5217\u8868,\u5185\u90e8\u6563\u5217\u51fd\u6570\u6216\u7ebf\u6027\u6563\u5217\u51fd\u6570\u7684\u7b80\u5355\u5339\u914d;
\n 2.\u5206\u533a\u7c7b\u578b\u548c\u8868\u8fbe\u5f0f\u662f\u8868\u5b9a\u4e49\u7684\u4e00\u90e8\u5206;\u4f7f\u7528RANGE\/LIST COLUMNS\u65f6,\u8868\u8fbe\u5f0f\u53ef\u4ee5\u662fINT\/DATE\/DATETIME\/CHAR\/VARCHAR\/BINARY\/VARBINARY\u7b49\u6570\u636e\u7c7b\u578b;
\n 3.\u8868\u8fbe\u5f0f\u53ef\u4ee5\u662f\u6574\u6570\u6216\u8fd4\u56de\u6574\u6570\u503c\u7684\u51fd\u6570,\u8868\u8fbe\u5f0f\u5fc5\u987b\u8fd4\u56deNULL\u6216\u6574\u6570\u503c;
\n 4.\u6b64\u503c\u6839\u636e\u5b9a\u4e49\u786e\u5b9a\u5c06\u6bcf\u6761\u8bb0\u5f55\u5b58\u50a8\u5728\u54ea\u4e2a\u5206\u533a\u4e2d;<\/p>\n
— \u5206\u533a\u7c7b\u578b;
\nMySQL\u652f\u6301\u591a\u79cd\u7c7b\u578b\u7684\u5206\u533a:
\n1.RANGE:\u6839\u636e\u5c5e\u4e8e\u6307\u5b9a\u8303\u56f4\u7684\u5217\u503c\u5c06\u884c\u5206\u914d\u5230\u5206\u533a;\u8303\u56f4\u5e94\u8be5\u8fde\u7eed\u4f46\u4e0d\u91cd\u53e0,\u4f7f\u7528VALUES LESS THAN\u8fd0\u7b97\u7b26\u8fdb\u884c\u5b9a\u4e49;
\n2.LIST:\u6839\u636e\u4e0e\u79bb\u6563\u503c\u96c6\u4e4b\u4e00\u5339\u914d\u7684\u5217\u5c06\u884c\u5206\u914d\u5230\u5206\u533a;\u50cf\u5728\u7531RANGE\u8fdb\u884c\u5206\u533a\u4e00\u6837,\u5fc5\u987b\u663e\u5f0f\u5b9a\u4e49\u6bcf\u4e2a\u5206\u533a;
\n3.HASH:\u57fa\u4e8e\u7531\u7528\u6237\u5b9a\u4e49\u7684\u8868\u8fbe\u5f0f\u8fd4\u56de\u7684\u503c\u800c\u9009\u62e9\u7684\u5206\u533a,\u5bf9\u8981\u63d2\u5165\u8868\u4e2d\u7684\u884c\u7684\u5217\u503c\u8fdb\u884c\u64cd\u4f5c;
\n4.KEY:\u4e0eHASH\u7c7b\u4f3c,\u4e0d\u540c\u4e4b\u5904\u5728\u4e8e\u4ec5\u63d0\u4f9b\u8981\u8bc4\u4f30\u7684\u4e00\u4e2a\u6216\u591a\u4e2a\u5217\u5e76\u4e14MySQL\u670d\u52a1\u5668\u63d0\u4f9b\u6563\u5217\u51fd\u6570;\u5b83\u9002\u7528\u4e8e\u6240\u6709\u5141\u8bb8\u7684\u5217\u7c7b\u578b;
\n5.COLUMNS:RANGE\u548cLIST\u5206\u533a\u4e0a\u7684\u53d8\u4f53;COLUMNS\u5206\u533a\u5141\u8bb8\u5728\u5206\u533a\u952e\u4e2d\u4f7f\u7528\u4e00\u4e2a\u6216\u591a\u4e2a\u5217;
\n 1.RANGE COLUMNS\u548cLIST COLUMNS\u5206\u533a\u652f\u6301\u4f7f\u7528\u975e\u6574\u6570\u5217(\u4ee5\u53ca\u524d\u9762\u5217\u51fa\u7684\u5176\u4ed6\u6570\u636e\u7c7b\u578b)\u6765\u5b9a\u4e49\u503c\u8303\u56f4\u6216\u5217\u8868\u6210\u5458;
\n6.LINEAR:MySQL\u8fd8\u652f\u6301\u7ebf\u6027\u6563\u5217,\u5176\u4e0d\u540c\u4e8e\u5e38\u89c4\u6563\u5217,\u7ebf\u6027\u6563\u5217\u4f7f\u7528\u7ebf\u60272\u7684\u5e42\u7b97\u6cd5,\u800c\u5e38\u89c4\u6563\u5217\u4f7f\u7528\u6563\u5217\u51fd\u6570\u503c\u7684\u6a21;<\/p>\n
— \u5206\u533a\u652f\u6301;
\n1.\u786e\u5b9a\u670d\u52a1\u5668\u5206\u533a\u652f\u6301\u72b6\u6001:SHOW PLUGINS\\G
\n 1.\u5982\u679cMySQL\u4e8c\u8fdb\u5236\u6587\u4ef6\u6784\u5efa\u6709\u5206\u533a\u652f\u6301,\u60a8\u4e0d\u9700\u8981\u6267\u884c\u4efb\u4f55\u8fdb\u4e00\u6b65\u64cd\u4f5c\u6765\u542f\u7528\u8be5\u4e8c\u8fdb\u5236\u6587\u4ef6(\u4f8b\u5982,my.cnf\u6587\u4ef6\u4e2d\u4e0d\u9700\u8981\u4efb\u4f55\u7279\u6b8a\u6761\u76ee);
\n 2.\u5982\u679c\u60a8\u6ca1\u6709\u770b\u5230partition\u63d2\u4ef6\u4ee5\u53caStatus\u503cACTIVE\u5217\u5728SHOW PLUGINS\u7684\u8f93\u51fa\u4e2d,\u5219\u60a8\u7684MySQL\u7248\u672c\u4e0d\u652f\u6301\u5206\u533a;
\n2.\u7981\u7528\u5206\u533a\u652f\u6301:shell> mysqld –skip-partition
\n 1.partition\u63d2\u4ef6\u73b0\u5728\u5177\u6709\u503cDISABLED;<\/p>\n
— \u4f7f\u7528\u5206\u533a\u6539\u5584\u6027\u80fd;
\n1.\u7279\u5b9a\u4e8e\u5927\u578b\u8868\u7684\u5730\u5740\u95ee\u9898:
\n 1.\u5c06\u6570\u636e\u5206\u4e3a\u8f83\u5c0f\u7684\u7ec4\u4ee5\u4f7f\u67e5\u8be2\u66f4\u6709\u6548:\u5c06\u6570\u636e\u5206\u5272\u4e3a\u903b\u8f91\u5206\u7ec4\u7684\u8868\u4e2d\u65f6,\u67e5\u8be2\u641c\u7d22\u8f83\u5c11\u8bb0\u5f55,\u8fd9\u6837\u53ef\u4ee5\u5927\u5927\u63d0\u9ad8\u901f\u5ea6;
\n 2.\u9075\u5b88\u6587\u4ef6\u7cfb\u7edf\u7684\u6587\u4ef6\u5927\u5c0f\u9650\u5236;
\n 3.\u52a0\u5feb\u7ef4\u62a4\u548c\u5220\u9664\u884c\u8fd0\u884c\u65f6\u901f\u5ea6:\u4f8b\u5982\u7ef4\u62a4\u64cd\u4f5c\u8fd0\u884c\u65f6\u901f\u5ea6\u7f13\u6162(\u4f8b\u5982,OPTIMIZE\u548cREPAIR)\u548c\u5220\u9664\u4e0d\u9700\u8981\u7684\u884c\u65f6\u8fd0\u884c\u65f6\u901f\u5ea6\u7f13\u6162;
\n2.\u4f7f\u7528\u8f83\u5c0f\u7684\u7d22\u5f15\u6765\u5141\u8bb8\u201c\u6700\u70ed\u201d\u5206\u533a\u5177\u6709\u5185\u5b58\u4e2d\u7684\u6574\u4e2a\u7d22\u5f15;
\n3.\u5220\u9664\u5206\u533a\u6765\u66f4\u65b9\u4fbf\u5730\u5220\u9664\u6570\u636e;
\n4.\u6839\u636e\u65e5\u671f\u548c\u65f6\u95f4\u90e8\u7f72\u8868:\u4f8b\u5982,\u60a8\u53ef\u80fd\u5e0c\u671b\u6309\u5e74,\u5b63\u5ea6\u6216\u6708\u90e8\u7f72\u5e97\u94fa\u8ba2\u5355\u8868;<\/p>\n
— \u4f7f\u7528\u5206\u533a\u6539\u5584\u6027\u80fd:\u5220\u6539(\u5206\u533a\u6392\u9664);
\n1.\u5206\u533a\u6392\u9664\u4ee5\u5c06\u67e5\u8be2\u8303\u56f4\u9650\u5236\u4e3a\u786e\u5207\u8303\u56f4:
\n 1.MySQL\u4f18\u5316\u5668\u53ef\u4ee5\u4e3a\u67e5\u8be2\u8fc7\u6ee4\u4e0d\u9700\u8981\u7684\u5206\u533a,\u5b83\u4ec5\u641c\u7d22\u7b26\u5408\u67e5\u8be2\u6807\u51c6\u7684\u5206\u533a;
\n 2.\u4f7f\u7528WHERE\u5b50\u53e5\u8fdb\u884c\u6574\u8868\u626b\u63cf\u53ef\u4ee5\u4f5c\u4e3a\u975e\u5339\u914d\u5220\u6539\u6389\u5206\u533a;
\n 3.\u793a\u4f8b\u6240\u793a,\u5047\u5b9a\u60a8\u6709\u4e00\u4e2a\u4e66\u5e97\u5e76\u4e14\u60a8\u5177\u6709\u4e00\u4e2a\u6570\u636e\u5e93,\u5176\u4e2d\u5305\u542b\u4e66\u5e97\u7684\u6574\u4e2a\u4e66\u7c4d\u5e93\u5b58;\u6b64\u6570\u636e\u5e93\u5177\u6709\u9488\u5bf9\u6bcf\u79cd\u4e66\u7c4d\u7c7b\u522b\u7684\u8868,\u4e00\u4e2a\u5ba2\u6237\u6765\u5230\u4e66\u5e97,\u8981\u6c42\u63d0\u4f9b1990\u5e74\u548c2007\u5e74\u4e4b\u95f4\u51fa\u7248\u7684\u533b\u5b66\u7c7b\u522b\u7684\u6240\u6709\u4e66\u7c4d\u5217\u8868;\u56e0\u4e3a\u60a8\u4f7f\u7528\u4e86\u5206\u533a\u5e76\u4e14\u6309\u5e74\u4efd\u5bf9\u533b\u5b66\u8868\u8fdb\u884c\u4e86\u5206\u533a,\u6240\u4ee5\u60a8\u53ef\u4ee5\u8fd0\u884c\u4ec5\u5305\u62ec\u8fd9\u4e9b\u5e74\u4efd\u7684\u67e5\u8be2;\u4f18\u5316\u5668\u201c\u5220\u6539\u201d\u8be5\u641c\u7d22\u6765\u4ec5\u626b\u63cf1990\u20132007\u5206\u533a,\u8282\u7701\u4e86\u5927\u91cf\u65f6\u95f4\u548c\u5de5\u4f5c;
\n SELECT title FROM book
\n WHERE category = ‘medicine\u2018
\n AND published > 1990 AND published < 2007;\n2.\u6267\u884c\u663e\u5f0f\u5206\u533a\u9009\u62e9;\n 1.\u4f8b\u5982,\u5982\u679c\u60a8\u77e5\u9053p0\u5305\u62ec2007\u5e74\u4ee5\u524d\u51fa\u7248\u7684\u6240\u6709\u4e66\u7c4d,\u53ef\u4ee5\u663e\u5f0f\u6307\u51fa\u8be5\u5206\u533a,\u4ece\u800c\u5141\u8bb8\u4f18\u5316\u5668\u5ffd\u7565\u5176\u4ed6\u5206\u533a;\u53ef\u4ee5\u901a\u8fc7\u63d0\u4f9b\u5217\u8868\u6765\u9009\u62e9\u591a\u4e2a\u5206\u533a\u548c\u5b50\u5206\u533a,\u4f8b\u5982PARTITION(p0, p2sp1);\n SELECT title FROM book PARTITION(p0) WHERE category = 'medicine\u2018\n AND published > 1990;
\n3.\u8fd8\u5c06\u663e\u5f0f\u5206\u533a\u9009\u62e9\u7528\u4e8eINSERT\/REPLACE\/UPDATE\/DELETE\/LOAD,\u5373\u4f7f\u60a8\u5728\u5206\u533a\u4e2d\u6dfb\u52a0\u6216\u66f4\u6539\u6570\u636e,\u4e5f\u5fc5\u987b\u786e\u4fdd\u65b0\u6570\u636e\u6ee1\u8db3\u8be5\u5206\u533a\u7684\u5206\u533a\u6807\u51c6;<\/p>\n
— \u5206\u533a\u7684\u6027\u80fd\u6311\u6218;
\n1.\u6253\u5f00\u5206\u533a\u8868\u65f6\u5fc5\u987b\u6253\u5f00\u6240\u6709\u5206\u533a:\u8fd9\u662f\u4e00\u4e2a\u5c0f\u95ee\u9898,\u56e0\u4e3a\u6253\u5f00\u7684\u8868\u7f13\u5b58\u5728\u8868\u9ad8\u901f\u7f13\u5b58\u4e2d;
\n2.\u5220\u6539\u5177\u6709\u7279\u5b9a\u9650\u5236:
\n 1.\u5220\u6539\u5728\u7d22\u5f15\u5217\u4e2d\u6548\u7387\u8f83\u4f4e,\u56e0\u4e3a\u5b83\u6bcf\u4e2a\u5206\u533a\u4ec5\u907f\u514d\u4e00\u6b21\u7d22\u5f15\u67e5\u627e;
\n 2.\u5220\u6539\u5bf9\u4e8e\u975e\u7d22\u5f15\u5217\u7684\u4f5c\u7528\u8981\u5927\u5f97\u591a:
\n 1.\u5176WHERE\u5b50\u53e5\u4e2d\u5305\u542b\u975e\u7d22\u5f15\u5217\u7684\u67e5\u8be2\u53ef\u80fd\u9700\u8981\u8fdb\u884c\u5b8c\u6574\u8868\u626b\u63cf;
\n 2.\u5206\u533a\u626b\u63cf\u82b1\u8d39\u7684\u65f6\u95f4\u662f\u8868\u626b\u63cf\u82b1\u8d39\u65f6\u95f4\u7684\u4e00\u5c0f\u90e8\u5206;<\/p>\n
— \u57fa\u672c\u5206\u533a\u8bed\u6cd5; — RANGE\u5206\u533a; — LIST\u5206\u533a; — HASH\u5206\u533a; — KEY\u5206\u533a; — \u5b50\u5206\u533a; — \u83b7\u53d6\u5206\u533a\u4fe1\u606f; — \u83b7\u53d6\u5206\u533a\u4fe1\u606f:SHOW CREATE TABLE; — \u83b7\u53d6\u5206\u533a\u4fe1\u606f:SHOW TABLE STATUS; — \u83b7\u53d6\u5206\u533a\u4fe1\u606f:INFORMATION_SCHEMA; — \u83b7\u53d6\u5206\u533a\u4fe1\u606f:EXPLAIN PARTITIONS; — \u66f4\u6539\u5206\u533a; — \u91cd\u65b0\u5b9a\u4e49\u5206\u533a\u7c7b\u578b; — \u4ea4\u6362\u5206\u533a; — \u5220\u9664\u5206\u533a; — \u4f7f\u7528DROP PARTITION\u7684\u95ee\u9898; — \u5220\u9664\u5206\u533a; — \u66f4\u6539\u5206\u533a\u5bf9\u6027\u80fd\u7684\u5f71\u54cd; — \u6d4b\u9a8c; — \u5206\u533a:\u5b58\u50a8\u5f15\u64ce\u529f\u80fd; — \u5206\u533a\u4e0e\u9501\u5b9a; — \u5206\u533a\u9650\u5236; — \u5206\u533a\u8868\u8fbe\u5f0f\u9650\u5236; — \u8bfe\u540e\u7ec3\u4e60; — \u5206\u533a; 1.\u5206\u533a\u5c31\u662f\u5c06\u6570\u636e\u5e93\u6216\u5176\u6784\u6210\u5143\u7d20\u5212\u5206\u4e3a\u4e0d\u540c\u7684\u72ec\u7acb\u90e8\u5206; 1.\u9884\u5148\u7ec4\u7ec7\u8868\u5b58\u50a8\u7684\u65b9\u6cd5; 2 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[27,31],"class_list":["post-1306","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql","tag-mysql_ocp"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1306","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1306"}],"version-history":[{"count":2,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1306\/revisions"}],"predecessor-version":[{"id":1308,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1306\/revisions\/1308"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1306"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1306"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1306"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n1.\u901a\u8fc7\u5728CREATE TABLE\u8bed\u53e5\u4e2d\u4f7f\u7528PARTITION BY\u5b50\u53e5\u6765\u521b\u5efa\u5206\u533a\u8868;PARTITION BY\u8ddf\u5728CREATE TABLE\u7684\u6240\u6709\u90e8\u5206\u540e\u9762:
\n CREATE TABLE
\n PARTITION BY
\n2.\u5206\u533a\u7c7b\u578b\u540e\u9762\u662f\u62ec\u53f7\u62ec\u8d77\u6765\u7684\u5206\u533a\u8868\u8fbe\u5f0f;\u6839\u636e\u4f7f\u7528\u7684\u5206\u533a\u7c7b\u578b\u4ee5\u53ca\u60a8\u7684\u8981\u6c42,\u8be5\u8868\u8fbe\u5f0f\u53ef\u4ee5\u662f\u8868\u5217\u7684\u540d\u79f0,\u6d89\u53ca\u4e00\u4e2a\u6216\u591a\u4e2a\u5217\u7684\u7b80\u5355\u6570\u5b66\u8868\u8fbe\u5f0f\u6216\u8005\u5217\u540d\u79f0\u7684\u9017\u53f7\u5206\u9694\u5217\u8868;
\n3.PARTITION BY\u7c7b\u578b\u5305\u62ec\u5728\u5176\u76f8\u5e94\u8bed\u6cd5\u4e2d:
\n \u2022 PARTITION BY RANGE …
\n \u2022 PARTITION BY RANGE COLUMNS …
\n \u2022 PARTITION BY LIST …
\n \u2022 PARTITION BY LIST COLUMNS …
\n \u2022 PARTITION BY HASH …
\n \u2022 PARTITION BY LINEAR HASH …
\n \u2022 PARTITION BY KEY …
\n \u2022 PARTITION BY LINEAR KEY …<\/p>\n
\n1.\u4f7f\u7528PARTITION BY RANGE\u5bf9\u5305\u542b\u4e00\u7ec4\u6307\u5b9a\u8303\u56f4\u5185\u7684\u6307\u5b9a\u503c\u7684\u884c\u8fdb\u884c\u5206\u533a;
\n2.\u4f8b\u5982,\u5047\u5b9a\u60a8\u6709\u4e00\u4e2a\u8868\u6765\u5b58\u50a8\u4e0e\u5176\u8ba2\u5355\u76f8\u5173\u7684\u6570\u636e,\u5e76\u4e14\u60a8\u5e0c\u671b\u6307\u5b9aid\u53f7\u5c0f\u4e8e10,000\u7684\u8ba2\u5355\u5b58\u50a8\u5728\u4e00\u4e2a\u5206\u533a\u4e2d,id\u53f7\u4ecb\u4e8e10,000\u548c19,999\u4e4b\u95f4\u7684\u8ba2\u5355\u5b58\u50a8\u5728\u53e6\u4e00\u4e2a\u5206\u533a\u4e2d,id\u53f7\u4ecb\u4e8e20,000\u548c29,999\u4e4b\u95f4\u7684\u8ba2\u5355\u5b58\u50a8\u5728\u53e6\u4e00\u4e2a\u5206\u533a\u4e2d\u7b49\u7b49;\u6362\u53e5\u8bdd\u8bf4,\u60a8\u57fa\u4e8e\u8fde\u7eed\u503c\u8303\u56f4\u6765\u5bf9\u8868\u8fdb\u884c\u5206\u533a;RANGE\u5206\u533a\u975e\u5e38\u9002\u5408\u4e8e\u6b64\u7c7b\u5206\u533a\u6a21\u5f0f;
\nCREATE TABLE orders_range
\n(id INT AUTO_INCREMENT PRIMARY KEY,
\n customer_surname VARCHAR(30),
\n store_id INT,
\n salesperson_id INT,
\n order_date DATE,
\n note VARCHAR(500)
\n) ENGINE = InnoDB
\nPARTITION BY RANGE(id)
\n(
\n PARTITION p0 VALUES LESS THAN(10000),
\n PARTITION p1 VALUES LESS THAN(20000),
\n PARTITION p2 VALUES LESS THAN(30000),
\n PARTITION p3 VALUES LESS THAN(40000),
\n PARTITION p4 VALUES LESS THAN(50000)
\n);<\/p>\n
\n1.\u4f7f\u7528PARITION BY LIST\u5bf9\u5305\u542b\u6307\u5b9a\u503c\u7684\u884c\u8fdb\u884c\u5206\u533a;
\n2.\u4e3a\u6bcf\u4e2a\u5206\u533a\u6307\u5b9a\u503c\u5217\u8868,\u4ece\u800c\u5305\u542b\u5339\u914d\u5217\u503c\u7684\u884c\u5c06\u5b58\u50a8\u5728\u76f8\u5e94\u5206\u533a\u4e2d;\u4f8b\u5982,\u5047\u8bbe\u60a8\u7684\u6bcf\u4e2a\u8ba2\u5355\u90fd\u653e\u5728\u7ec4\u7ec7\u5230\u4e94\u4e2a\u5730\u533a\u4e2d\u7684\u591a\u4e2a\u4e0d\u540c\u5e97\u94fa\u4e2d\u7684\u4e00\u4e2a\u5e97\u94fa,\u5e76\u4e14\u60a8\u5e0c\u671b\u5e97\u94fa\u8ba2\u5355\u653e\u5728\u76f8\u540c\u5206\u533a\u4e2d\u7684\u6bcf\u4e2a\u5730\u533a\u4e2d;\u4f7f\u7528\u8fd9\u79cd\u7c7b\u578b\u7684\u5206\u533a,\u60a8\u53ef\u4ee5\u4efb\u610f\u5206\u9694\u5e97\u94fa;\u5047\u5b9a\u5728\u8fd9\u4e94\u4e2a\u5730\u533a\u4e2d\u670919\u4e2a\u5e97\u94fa,\u53ef\u4ee5\u521b\u5efaorders_list\u8868,\u5176\u6309\u5982\u4e0b\u6240\u793a\u5c06\u6765\u81ea\u76f8\u540c\u5730\u533a\u7684\u5e97\u94fa\u7684\u8ba2\u5355\u5206\u914d\u5230\u76f8\u540c\u5206\u533a;
\nCREATE TABLE orders_list
\n(id INT AUTO_INCREMENT,
\n customer_surname VARCHAR(30),
\n store_id INT,
\n salesperson_id INT,
\n order_date DATE,
\n note VARCHAR(500),
\n INDEX idx (id)
\n) ENGINE = InnoDB
\nPARTITION BY LIST(store_id)
\n(
\n PARTITION p0 VALUES IN (1, 3, 4, 17),
\n PARTITION p1 VALUES IN (2, 12, 14),
\n PARTITION p2 VALUES IN (6, 8, 20),
\n PARTITION p3 VALUES IN (5, 7, 9, 11, 16),
\n PARTITION p4 VALUES IN (10, 13, 15, 18)
\n);<\/p>\n
\n1.PARTITION BY HASH\u786e\u4fdd\u5728\u5206\u533a\u4e2d\u5e73\u5747\u5206\u5e03\u6570\u636e;
\n2.\u4e0eRANGE\u6216LIST\u4e0d\u540c,HASH\u4e0d\u9700\u8981\u5355\u72ec\u7684\u5206\u533a\u5b9a\u4e49;
\n3.\u8be5\u5206\u5e03\u57fa\u4e8e\u521b\u5efa\u8868\u65f6\u63d0\u4f9b\u7684\u8868\u8fbe\u5f0f,\u4f7f\u7528\u5173\u952e\u5b57PARTITIONS,\u540e\u8ddf\u6240\u9700\u7684(\u6574\u6570)\u5206\u533a\u6570\u91cf;
\nCREATE TABLE orders_hash
\n(id INT AUTO_INCREMENT PRIMARY KEY,
\n customer_surname VARCHAR(30),
\n store_id INT,
\n salesperson_id INT,
\n order_date DATE,
\n note VARCHAR(500)
\n) ENGINE = InnoDB
\nPARTITION BY HASH(id) PARTITIONS 4;<\/p>\n
\n1.PARTITION BY KEY\u7c7b\u4f3c\u4e8eHASH,\u53ea\u662fMySQL\u670d\u52a1\u5668\u4f7f\u7528\u5176\u81ea\u5df1\u7684\u6563\u5217\u8868\u8fbe\u5f0f;
\n2.\u4e0d\u8981\u6c42\u5206\u533a\u8868\u8fbe\u5f0f\u8fd4\u56de\u6574\u6570\u6216NULL;
\n3.[LINEAR] KEY\u540e\u9762\u7684\u8868\u8fbe\u5f0f\u4ec5\u5305\u542b\u4e00\u7ec4\u96f6\u6216\u66f4\u591a\u5217\u540d\u79f0,\u591a\u4e2a\u540d\u79f0\u7531\u9017\u53f7\u5206\u9694;\u5e7b\u706f\u7247\u4e2d\u7684\u793a\u4f8b\u521b\u5efaorders_key\u8868,\u5176\u6309\u5df2\u653e\u7f6e\u8ba2\u5355\u7684\u65e5\u671f\u8fdb\u884c\u5206\u533a;
\nCREATE TABLE orders_key
\n(id INT AUTO_INCREMENT PRIMARY KEY,
\n customer_surname VARCHAR(30),
\n store_id INT,
\n salesperson_id INT,
\n order_date DATE,
\n note VARCHAR(500)
\n) ENGINE = InnoDB
\nPARTITION BY KEY(order_date) PARTITIONS 4;<\/p>\n
\n1.RANGE\u548cLIST\u5206\u533a\u8868\u53ef\u4ee5\u8fdb\u884c\u5b50\u5206\u533a:\u5b50\u5206\u533a(\u4e5f\u79f0\u4e3a\u590d\u5408\u5206\u533a)\u662f\u5bf9\u5206\u533a\u8868\u4e2d\u7684\u6bcf\u4e2a\u5206\u533a\u8fdb\u884c\u8fdb\u4e00\u6b65\u5212\u5206;
\n2.\u5b50\u5206\u533a\u53ef\u4ee5\u4f7f\u7528HASH,LINEAR HASH,KEY\u6216LINEAR KEY\u5206\u533a;
\n3.\u5728\u6b64\u201c\u5e97\u94fa\u8ba2\u5355\u201d\u6570\u636e\u5e93\u793a\u4f8b\u4e2d,\u6839\u636eorders_range\u8868,\u53ef\u4ee5\u8fdb\u4e00\u6b65\u5212\u5206\u8868,\u8fd9\u6837\u8868\u53ef\u4ee5\u5c06\u6bcf\u4e2a\u5206\u533a\u62c6\u5206\u4e3a\u4e24\u4e2a\u5b50\u5206\u533a,\u4e00\u51715*2=10\u4e2a\u5b50\u5206\u533a;
\nCREATE TABLE orders_range_hash
\n(id INT AUTO_INCREMENT,
\n customer_surname VARCHAR(30),
\n store_id INT,
\n salesperson_id INT,
\n order_date DATE,
\n note VARCHAR(500),
\n PRIMARY KEY(id, store_id)
\n) ENGINE = InnoDB
\nPARTITION BY RANGE(id)
\nSUBPARTITION BY HASH(store_id) SUBPARTITIONS 2
\n(
\n PARTITION p0 VALUES LESS THAN(10000),
\n PARTITION p1 VALUES LESS THAN(20000),
\n PARTITION p2 VALUES LESS THAN(30000),
\n PARTITION p3 VALUES LESS THAN(40000),
\n PARTITION p4 VALUES LESS THAN(50000)
\n);
\nTIPS:ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function<\/p>\n
\nMySQL\u63d0\u4f9b\u4e86\u591a\u79cd\u65b9\u6cd5\u6765\u786e\u5b9a\u8868\u7684\u5206\u533a\u72b6\u6001:
\n\u2022 SHOW CREATE TABLE:\u67e5\u770b\u7528\u4e8e\u521b\u5efa\u5206\u533a\u8868\u7684\u5206\u533a\u5b50\u53e5;
\n\u2022 SHOW TABLE STATUS:\u786e\u5b9a\u8868\u662f\u5426\u8fdb\u884c\u4e86\u5206\u533a;
\n\u2022 INFORMATION_SCHEMA.PARTITIONS:\u67e5\u8be2INFORMATION_SCHEMA.PARTITIONS\u8868;
\n\u2022 EXPLAIN PARTITIONS SELECT:\u663e\u793a\u7ed9\u5b9aSELECT\u8bed\u53e5\u4f7f\u7528\u7684\u5206\u533a;<\/p>\n
\nSHOW CREATE TABLE orders_hash\\G<\/p>\n
\nSHOW TABLE STATUS LIKE ‘orders_hash’\\G
\n1.Create_options\u5217\u5305\u542b\u5b57\u7b26\u4e32partitioned;
\n2.Engine\u5217\u5305\u542b\u8868\u7684\u6240\u6709\u5206\u533a\u4f7f\u7528\u7684\u5b58\u50a8\u5f15\u64ce\u7684\u540d\u79f0;\u4f46\u662f\u5982\u679c\u6307\u5b9a\u4e0d\u540c\u7684\u5f15\u64ce\u4f1a\u62a5\u9519;
\nERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL;<\/p>\n
\n1.\u53ef\u4ee5\u5728INFORMATION_SCHEMA.PARTITIONS\u8868\u4e2d\u67e5\u8be2\u5206\u533a\u8be6\u7ec6\u4fe1\u606f;
\n2.\u83b7\u53d6\u6240\u6709\u6570\u636e\u5e93\u8868\u53ca\u5176\u5206\u533a\u7684\u5217\u8868:
\nSELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
\nFROM INFORMATION_SCHEMA.PARTITIONS
\nWHERE TABLE_NAME=’orders_list’ AND TABLE_SCHEMA=’orders’;
\n+—————-+——————+———————-+———————–+
\n| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
\n+—————-+——————+———————-+———————–+
\n| p0 | LIST | store_id | 1,3,4,17 |
\n| p1 | LIST | store_id | 2,12,14 |
\n| p2 | LIST | store_id | 6,8,20 |
\n| p3 | LIST | store_id | 5,7,9,11,16 |
\n| p4 | LIST | store_id | 10,13,15,18 |
\n+—————-+——————+———————-+———————–+
\n5 rows in set (0.00 sec)<\/p>\n
\n1.\u663e\u793aMySQL\u5904\u7406\u5206\u533a\u7684\u65b9\u5f0f;
\n2.\u5224\u65ad\u67e5\u8be2\u5c06\u8bbf\u95ee\u54ea\u4e9b\u5206\u533a\u4ee5\u53ca\u4f7f\u7528\u8fd9\u4e9b\u5206\u533a\u7684\u65b9\u5f0f;
\n3.\u53ea\u80fd\u7528\u5728\u5305\u542b\u884c\u6570\u636e\u7684\u8868\u4e2d:EXPLAIN PARTITIONS SELECT * FROM orders_range\\G<\/p>\n
\n1.\u6570\u636e\u5e93\u901a\u5e38\u8981\u6c42\u5bf9\u73b0\u6709\u5206\u533a\u8bbe\u7f6e\u8fdb\u884c\u52a8\u6001\u66f4\u6539;
\n2.MySQL\u5141\u8bb8\u591a\u79cd\u7c7b\u578b\u7684\u5206\u533a\u66f4\u6539:
\n \u2013 \u6dfb\u52a0;
\n \u2013 \u5220\u9664;
\n \u2013 \u5408\u5e76;
\n \u2013 \u62c6\u5206;
\n \u2013 \u91cd\u7ec4;
\n \u2013 \u91cd\u65b0\u5b9a\u4e49;
\n3.\u4f7f\u7528ALTER TABLE\u8bed\u53e5\u7684\u7b80\u5355\u6269\u5c55\u8fdb\u884c\u8fd9\u4e9b\u66f4\u6539;<\/p>\n
\n1.\u5c06\u975e\u5206\u533a\u8868\u66f4\u6539\u4e3a\u5206\u533a\u8868;
\n2.\u5b8c\u5168\u91cd\u65b0\u5b9a\u4e49\u73b0\u6709\u5206\u533a,\u5305\u62ec\u7c7b\u578b;
\n3.\u5c06ALTER TABLE\u4e0ePARTITION BY\u914d\u5408\u4f7f\u7528,\u5c06\u5206\u533a\u7c7b\u578b\u4eceRANGE\u66f4\u6539\u4e3aHASH:
\n 1.\u67e5\u770b\u5206\u533a\u7c7b\u578b:
\n SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
\n FROM INFORMATION_SCHEMA.PARTITIONS
\n WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
\n 2.\u5728\u7ebf\u91cd\u5b9a\u4e49:
\n ALTER TABLE orders_range PARTITION BY HASH(id) PARTITIONS 4;
\n 3.\u518d\u6b21\u67e5\u770b;
\n SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
\n FROM INFORMATION_SCHEMA.PARTITIONS
\n WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;<\/p>\n
\n1.\u5c06\u8868\u5206\u533a\u6216\u5b50\u5206\u533a\u4e0e\u8868\u4ea4\u6362:
\n 1.\u5c06\u5206\u533a\u6216\u5b50\u5206\u533a\u4e2d\u7684\u6240\u6709\u73b0\u6709\u884c\u79fb\u81f3\u975e\u5206\u533a\u8868;
\n 2.\u5c06\u975e\u5206\u533a\u8868\u4e2d\u7684\u6240\u6709\u73b0\u6709\u884c\u79fb\u81f3\u8868\u5206\u533a\u6216\u5b50\u5206\u533a;
\n 3.\u4ea4\u6362\u5206\u533a\u4e0d\u4f1a\u5728\u5206\u533a\u8868\u6216\u4ea4\u6362\u8868\u4e0a\u8c03\u7528\u89e6\u53d1\u5668;
\n 4.\u5c06\u91cd\u7f6e\u4ea4\u6362\u8868\u4e2d\u7684\u6240\u6709AUTO_INCREMENT\u5217;
\n2.\u8981\u4ea4\u6362\u7684\u8868\u4e0d\u80fd\u8fdb\u884c\u5206\u533a:
\n 1.\u5b83\u5fc5\u987b\u4e0e\u5206\u533a\u8868\u5177\u6709\u76f8\u540c\u8868\u7ed3\u6784:
\n 2.\u4ea4\u6362\u4e4b\u524d,\u975e\u5206\u533a\u8868\u4e2d\u7684\u884c\u5fc5\u987b\u4f4d\u4e8e\u4e3a\u5206\u533a\u6216\u5b50\u5206\u533a\u5b9a\u4e49\u7684\u8303\u56f4\u5185;
\n\u4f8b\u5b50:
\n1.\u628a\u8868\u4fee\u6539\u56de\u8303\u56f4\u5206\u533a;
\nALTER TABLE orders_range
\nPARTITION BY RANGE(id)
\n(
\n PARTITION p0 VALUES LESS THAN(10000),
\n PARTITION p1 VALUES LESS THAN(20000),
\n PARTITION p2 VALUES LESS THAN(30000),
\n PARTITION p3 VALUES LESS THAN(40000),
\n PARTITION p4 VALUES LESS THAN(50000)
\n);
\n2.\u67e5\u770b\u8868\u4fe1\u606f:
\nSELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
\nFROM INFORMATION_SCHEMA.PARTITIONS
\nWHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
\n3.\u521b\u5efa\u6d4b\u8bd5\u8868\u5e76\u63d2\u5165\u6570\u636e;
\nCREATE TABLE orders_ex
\n(id INT AUTO_INCREMENT PRIMARY KEY,
\n customer_surname VARCHAR(30),
\n store_id INT,
\n salesperson_id INT,
\n order_date DATE,
\n note VARCHAR(500)
\n) ENGINE = InnoDB;
\nINSERT INTO orders_ex VALUES(1, ‘test’, 1, 1, sysdate(), ”);
\nINSERT INTO orders_ex VALUES(10001, ‘test’, 1, 1, sysdate(), ”);
\nCOMMIT;
\nINSERT INTO orders_range VALUES(1, ‘xxx’, 1, 1, sysdate(), ”);
\nINSERT INTO orders_range VALUES(10001, ‘xxx’, 1, 1, sysdate(), ”);
\nCOMMIT;
\n4.\u5206\u533a\u4ea4\u6362;
\nmysql> ALTER TABLE orders_range EXCHANGE PARTITION p0 WITH TABLE orders_ex;
\nERROR 1737 (HY000): Found a row that does not match the partition
\n5.\u4fee\u6b63\u6570\u636e;
\nmysql> UPDATE orders_ex SET id = 9999 WHERE id = 10001;
\nmysql> COMMIT;
\nmysql> ALTER TABLE orders_range EXCHANGE PARTITION p0 WITH TABLE orders_ex;
\n6.\u67e5\u770b\u7ed3\u679c;
\nmysql> SELECT * FROM orders_ex;
\nmysql> SELECT * FROM orders_range PARTITION(p0);<\/p>\n
\n1.\u53ef\u4ee5\u5220\u9664\u4e00\u4e2a\u6216\u591a\u4e2a\u5206\u533a;
\n 1.\u5220\u9664\u5206\u533a\u4e0e\u5220\u9664\u884c\u4e0d\u4e00\u6837;\u867d\u7136\u5220\u9664\u5206\u533a\u786e\u5b9e\u53ef\u4ee5\u5220\u9664\u5206\u533a\u5185\u7684\u6240\u6709\u6570\u636e,\u4f46\u662f\u4f7f\u7528DROP,PARTITION\u7684\u4e3b\u8981\u76ee\u7684\u662f\u5220\u9664\u5206\u533a\u6307\u5b9a\u81ea\u8eab,\u4ee5\u53ca\u4e0d\u518d\u5141\u8bb8\u5c06\u6570\u636e\u4f20\u9012\u5230\u8be5\u5206\u533a;
\n 2.\u53ef\u4ee5\u4f7f\u7528ALTER TABLE … TRUNCATE PARTITION\u8bed\u53e5\u5220\u9664\u5206\u533a\u8868\u7684\u4e00\u4e2a\u6216\u591a\u4e2a\u5206\u533a\u4e2d\u7684\u6240\u6709\u884c(\u5b50\u53e5\u4e2d\u6307\u5b9a\u7684\u5206\u533a\u4e0d\u5fc5\u662f\u8fde\u7eed\u7684);
\n2.RANGE\u6216LIST\u8868\u5141\u8bb8\u6b64\u64cd\u4f5c;
\n3.\u5c06ALTER TABLE\u4e0eDROP PARTITION\u914d\u5408\u4f7f\u7528:
\nmysql> ALTER TABLE orders_range TRUNCATE PARTITION p0, p1;
\nmysql> SELECT * FROM orders_range;
\nmysql> ALTER TABLE orders_range DROP PARTITION p0;
\nmysql> SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
\n FROM INFORMATION_SCHEMA.PARTITIONS
\n WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;<\/p>\n
\n1.\u9700\u8981DROP\u7279\u6743\u624d\u80fd\u4f7f\u7528DROP PARTITION;
\n2.\u5220\u9664\u5206\u533a\u4f1a\u5220\u9664\u8be5\u5206\u533a\u4e2d\u7684\u6240\u6709\u6570\u636e;
\n3.DROP PARTITION\u4e0d\u8fd4\u56de\u6240\u5220\u9664\u7684\u884c\u6570;
\n4.DROP PARTITION\u66f4\u6539\u8868\u5b9a\u4e49;
\n5.\u53ef\u4ee5\u6309\u4efb\u4f55\u987a\u5e8f\u5220\u9664\u5206\u533a;
\n6.\u5c06\u6839\u636e\u65b0\u5206\u533a\u5904\u7406\u63d2\u5165\u5230\u6240\u5220\u9664\u5206\u533a\u4e2d\u7684\u884c;
\n 1.\u5220\u9664LIST\u5206\u533a\u4f1a\u7981\u6b62\u5df2\u7ecf\u5339\u914d\u8be5\u5206\u533a\u7684INSERT\u548cUPDATE\u64cd\u4f5c;
\n 2.\u5220\u9664RANGE\u5206\u533a\u5c06\u5220\u9664\u8be5\u5206\u533a\u53ca\u5176\u6570\u636e,\u6765\u81eaINSERT\u548cUPDATE\u64cd\u4f5c\u7684\u65b0\u6570\u636e\u5c06\u5b58\u50a8\u5728\u8303\u56f4\u4e2d\u7684\u4e0b\u4e00\u4e2a\u66f4\u5927\u7684\u5206\u533a,\u5982\u679c\u6ca1\u6709\u8303\u56f4\u5927\u4e8e\u5df2\u5220\u9664\u5206\u533a\u7684\u8303\u56f4\u7684\u5206\u533a,\u65e0\u6cd5\u5bf9\u5df2\u5220\u9664\u5206\u533a\u7684\u8303\u56f4\u4e2d\u7684\u6570\u636e\u6267\u884cINSERT\u548cUPDATE\u64cd\u4f5c;
\n7.\u53ef\u4ee5\u7528\u5355\u4e2a\u8bed\u53e5\u5220\u9664\u591a\u4e2a\u5206\u533a:ALTER TABLE orders_range DROP PARTITION p1, p3;<\/p>\n
\n1.\u8981\u5220\u9664\u8868\u4e2d\u7684\u6240\u6709\u5206\u533a\u4ece\u800c\u5c06\u5176\u6062\u590d\u4e3a\u975e\u5206\u533a\u8868,\u8bf7\u5c06ALTER TABLE\u4e0eREMOVE PARTITIONING\u914d\u5408\u4f7f\u7528;
\n 1.\u6b64\u64cd\u4f5c\u4e0d\u4f1a\u5220\u9664\u4efb\u4f55\u8868\u6570\u636e;
\n2.REMOVE PARTITIONING\u6267\u884c\u5b8c\u6574\u8868\u590d\u5236,\u4e0e\u666e\u901aALTER TABLE ALGORITHM=COPY\u4e00\u6837;
\n\u4f8b\u5b50:
\n1.\u4f7forders_range\u8868\u6062\u590d\u5230\u975e\u5206\u533a\u72b6\u6001:ALTER TABLE orders_range REMOVE PARTITIONING;
\n2.\u67e5\u770borders_range\u8868\u4fe1\u606f:
\nSHOW TABLE STATUS LIKE ‘orders_range’;<\/p>\n
\n1.\u6839\u636e\u5206\u533a\u7684\u6570\u91cf,\u4e0e\u521b\u5efa\u975e\u5206\u533a\u8868\u76f8\u6bd4,\u521b\u5efa\u5206\u533a\u8868\u7684\u901f\u5ea6\u7a0d\u5fae\u6162\u4e00\u4e9b;
\n2.\u5206\u533a\u64cd\u4f5c\u5904\u7406\u901f\u5ea6\u6bd4\u8f83:
\n 1.\u5bf9\u4e8e\u5927\u578b\u4e8b\u52a1\u8868,DROP PARTITION\u6bd4DELETE\u5feb\u5f97\u591a;
\n 2.ADD PARTITION\u5728RANGE\u548cLIST\u8868\u4e0a\u76f8\u5f53\u5feb;
\n 3.\u5bf9\u4e8e\u5728KEY\u6216HASH\u8868\u4e0a\u8fd0\u884c\u7684ADD PARTITION,\u901f\u5ea6\u53d6\u51b3\u4e8e\u5df2\u7ecf\u5b58\u50a8\u7684\u884c\u6570;
\n 1.\u5982\u679c\u5b58\u5728\u66f4\u591a\u6570\u636e,\u9700\u8981\u82b1\u8d39\u66f4\u957f\u65f6\u95f4\u6765\u6dfb\u52a0\u65b0\u5206\u533a;
\n 2.\u5728KEY\/HASH\u4e0a\u8fd0\u884c\u7684ADD PARTITION\u5c06\u6240\u6709\u884c\u91cd\u65b0\u5206\u5e03\u5230\u65b0\u6570\u91cf\u7684\u5206\u533a,\u6709\u6548\u5730\u6267\u884c\u5b8c\u6574\u8868\u590d\u5236;
\n 4.\u5bf9\u8d85\u5927\u578b\u8868\u8fd0\u884cCOALESCE PARTITION,REORGANIZE PARTITION\u548cPARTITION BY\u65f6,\u5b83\u4eec\u7684\u6267\u884c\u901f\u5ea6\u53ef\u80fd\u4f1a\u5f88\u6162;
\n3.\u5728\u6b64\u7c7b\u64cd\u4f5c\u8fc7\u7a0b\u4e2d,\u786c\u4ef6I\/O\u7684\u5f00\u9500\u6bd4\u5206\u533a\u5f15\u64ce\u7684\u5f00\u9500\u9ad8\u5f97\u591a;<\/p>\n
\nd<\/p>\n
\n1.\u5206\u533a\u5b58\u50a8\u5728\u4e0eInnoDB\u8868\u76f8\u540c\u4f4d\u7f6e\u4e2d\u7684\u6587\u4ef6\u4e2d:
\n 1.\u53ef\u4ee5\u63d0\u4f9bDATA DIRECTORY\u9009\u9879\u6765\u91cd\u5b9a\u4f4d\u5206\u533a;
\n 2.\u6bcf\u4e2aPARTITION\u5b50\u53e5\u53ef\u4ee5\u5305\u62ec\u4e00\u4e2a[STORAGE] ENGINE\u9009\u9879,\u8be5\u9009\u9879\u6ca1\u6709\u4f5c\u7528,\u6bcf\u4e2a \u5206\u533a\u4f7f\u7528\u7684\u5b58\u50a8\u5f15\u64ce\u4e0e\u8868\u4f5c\u4e3a\u4e00\u4e2a\u6574\u4f53\u800c\u4f7f\u7528\u7684\u5b58\u50a8\u5f15\u64ce\u76f8\u540c;
\n2.\u6bcf\u4e2a\u5206\u533a\u5728\u4ee5\u4e0b\u6570\u636e\u76ee\u5f55\u4e2d\u5177\u6709\u5176\u81ea\u5df1\u7684\u6587\u4ef6:
\n 1.\u5982\u679c\u7981\u7528innodb_file_per_table,\u5206\u533a\u5c06\u5b58\u50a8\u5728\u5171\u4eab\u8868\u7a7a\u95f4\u4e2d;
\n3.\u6240\u6709\u6570\u636e\u548c\u7d22\u5f15\u90fd\u5c06\u8fdb\u884c\u5206\u533a:
\n 1.\u4e0d\u80fd\u4ec5\u5bf9\u6570\u636e\u6216\u4ec5\u5bf9\u7d22\u5f15\u8fdb\u884c\u5206\u533a;
\n4.\u5206\u533a\u53ef\u7528\u4e8e\u5176\u4ed6\u5b58\u50a8\u5f15\u64ce:
\n 1.\u4e0d\u53ef\u7528\u4e8eMERGE,FEDERATED,CSV;
\n 2.\u662f\u5728\u5b58\u50a8\u5f15\u64ce\u7ea7\u522b\u5b9e\u73b0\u7684;
\neg:
\nCREATE TABLE entries
\n(id INT,
\n entered DATE
\n)
\nPARTITION BY RANGE(YEAR(entered))
\n(
\n PARTITION p0 VALUES LESS THAN (2000) DATA DIRECTORY = ‘\/data\/p0’,
\n PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY = ‘\/data\/p1’
\n);<\/p>\n
\n1.MySQL\u8868\u5c06\u5728\u6267\u884c\u670d\u52a1\u5668\u4ee3\u7801\u7684\u8fc7\u7a0b\u4e2d\u9501\u5b9a;
\n 1.\u9501\u5b9a\u64cd\u4f5c\u5728\u6267\u884c\u6bcf\u4e2a\u8bed\u53e5\u8fc7\u7a0b\u4e2d\u7531\u8868\u5b58\u50a8\u5f15\u64ce\u6765\u5904\u7406;
\n 2.\u6bcf\u4e2a\u5b58\u50a8\u5f15\u64ce\u5904\u7406\u9501\u7684\u65b9\u5f0f\u5747\u4e0d\u76f8\u540c;
\n2.\u6bcf\u4e2a\u5206\u533a\u6709\u4e00\u4e2a\u5b58\u50a8\u5f15\u64ce\u5b9e\u4f8b;
\n 1.\u5c06\u8981\u6c42\u9501\u5b9a\u6bcf\u4e2a\u975e\u5220\u6539\u5206\u533a\/\u5b58\u50a8\u5f15\u64ce\u5b9e\u4f8b;
\n3.\u9501\u5b9a\u5206\u533a\u8868\u65f6,\u5c06\u9501\u5b9a\u975e\u5220\u6539\u5206\u533a;
\n 1.\u4ec5\u5728\u6267\u884c\u8bed\u53e5\u8fc7\u7a0b\u4e2d\u4fdd\u6301\u8be5\u9501\u5b9a;
\n4.ALTER…PARTITION\u8bed\u53e5\u901a\u5e38\u5728\u8868\u4e0a\u4f7f\u7528\u5199\u5165\u9501;
\n 1.\u4ece\u6b64\u7c7b\u8868\u8fdb\u884c\u7684\u8bfb\u53d6\u76f8\u5bf9\u6765\u8bf4\u4e0d\u53d7\u5f71\u54cd,\u5f85\u5b9aINSERT\u548cUPDATE\u64cd\u4f5c\u5728\u5206\u533a\u4e00\u5b8c\u6210\u540e\u5c31\u4f1a\u6267\u884c;\u4f18\u5148\u7ea7\u7684\u95ee\u9898;
\n 2.\u6267\u884cALTER TABLE t
\n1.\u5e38\u89c4:
\n \u2013 \u6bcf\u4e2a\u8868\u7684\u6700\u5927\u5206\u533a\u6570\u4e3a8192;
\n \u2013 \u4e0d\u652f\u6301\u7a7a\u95f4\u7c7b\u578b;
\n \u2013 \u4e0d\u80fd\u5bf9\u4e34\u65f6\u8868\u8fdb\u884c\u5206\u533a;
\n \u2013 \u4e0d\u80fd\u5bf9\u65e5\u5fd7\u8868\u8fdb\u884c\u5206\u533a;
\n2.\u5916\u952e\u548c\u7d22\u5f15:
\n \u2013 \u4e0d\u652f\u6301\u5916\u952e;
\n \u2013 \u4e0d\u652f\u6301FULLTEXT\u7d22\u5f15;
\n \u2013 \u65e0\u5168\u5c40\u7d22\u5f15:\u6bcf\u4e2a\u5206\u533a\u90fd\u6709\u5404\u81ea\u7684\u7d22\u5f15;
\n3.\u4ec5\u53ef\u80fd\u5728\u4ee5\u4e0b\u60c5\u51b5\u4e0b\u8fdb\u884c\u5b50\u5206\u533a:
\n \u2013 \u901a\u8fc7RANGE\u548cLIST\u8fdb\u884c\u5206\u533a\u65f6;
\n \u2013 \u901a\u8fc7LINEAR HASH\u6216LINEAR KEY\u8fdb\u884c\u65f6;<\/p>\n
\n1.\u7528\u4e8eRANGE\/LIST\/HASH\u5206\u533a\u7684\u8868\u8fbe\u5f0f\u7684\u7ed3\u679c\u5fc5\u987b\u4e3a\u6574\u6570:
\n 1.RANGE COLUMNS\u548cLIST COLUMNS\u5206\u533a\u5141\u8bb8\u66f4\u5927\u8303\u56f4\u7684\u6570\u636e\u7c7b\u578b;
\n 2.RANGE COLUMNS\u548cLIST COLUMNS\u5206\u533a\u7c7b\u578b\u4e0d\u9700\u8981\u6574\u6570\u5217\u6216\u8868\u8fbe\u5f0f;
\n 3.RANGE COLUMNS\u548cLIST COLUMNS\u5206\u533a\u53ef\u4ee5\u5728\u5206\u533a\u952e\u4e2d\u4f7f\u7528\u591a\u4e2a\u5217;
\n 4.\u8be5\u5217\u53ef\u4ee5\u662f\u4efb\u4f55\u6574\u6570\u7c7b\u578b\/DATE\/DATETIME\/CHAR\/VARCHAR\/BINARY\/VARBINARY;
\n2.\u4e0d\u80fd\u5728\u5206\u533a\u8868\u8fbe\u5f0f\u4e2d\u4f7f\u7528TEXT\u6216BLOB;
\n3.\u4e0d\u5141\u8bb8\u4f7f\u7528UDF,\u5b58\u50a8\u51fd\u6570,\u53d8\u91cf,\u67d0\u4e9b\u8fd0\u7b97\u7b26\u548c\u67d0\u4e9b\u7f6e\u51fd\u6570:
\n 1.\u8fd0\u7b97\u7b26:|,&,^,< <,>>,~;
\n4.\u4e0d\u5e94\u5728\u521b\u5efa\u8868\u540e\u66f4\u6539SQL\u6a21\u5f0f;
\n5.\u5206\u533a\u8868\u8fbe\u5f0f\u4e2d\u4e0d\u652f\u6301\u5b50\u67e5\u8be2;
\n6.\u5206\u533a\u8868\u8fbe\u5f0f\u4e2d\u4f7f\u7528\u7684\u6240\u6709\u5217\u90fd\u5fc5\u987b\u662f\u8868\u7684\u6240\u6709\u552f\u4e00\u7d22\u5f15\u7684\u4e00\u90e8\u5206:
\n 1.\u56e0\u4e3a\u7d22\u5f15\u5fc5\u987b\u4e0e\u6570\u636e\u4e00\u8d77\u8fdb\u884c\u5206\u533a;
\n 2.\u8981\u4fdd\u6301\u201c\u552f\u4e00\u6027\u201d,\u5177\u6709\u76f8\u540c\u552f\u4e00\u952e\u7684\u4e24\u884c\u5fc5\u987b\u8f6c\u81f3\u76f8\u540c\u5206\u533a;\u5426\u5219,\u5b83\u4eec\u5c06\u8fdd\u53cd\u552f\u4e00\u952e\u9650\u5236;<\/p>\n
\n<\/cmd><\/partition_name><\/table_name><\/partition_expression><\/type><\/engine_name><\/table_column_options><\/table_name>\n","protected":false},"excerpt":{"rendered":"