{"id":284,"date":"2024-06-20T18:21:56","date_gmt":"2024-06-20T10:21:56","guid":{"rendered":"http:\/\/zhaoyanqi.cn\/?p=284"},"modified":"2024-06-20T18:21:57","modified_gmt":"2024-06-20T10:21:57","slug":"innodb%e5%ad%98%e5%82%a8%e5%bc%95%e6%93%8e%e8%a1%a8%e7%a9%ba%e9%97%b4","status":"publish","type":"post","link":"https:\/\/zhaoyanqi.cn\/?p=284","title":{"rendered":"InnoDB\u5b58\u50a8\u5f15\u64ce\u8868\u7a7a\u95f4"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"jksRF\">1. \u884c\uff0c\u9875\uff0c\u533a\uff0c\u6bb5<\/h2>\n\n\n\n<p id=\"ue2f606c1\">InnoDB\u5b58\u50a8\u5f15\u64ce\u91c7\u7528\u5206\u5c42\u7684\u5b58\u50a8\u7ed3\u6784\uff0c\u5305\u62ec\u6bb5\uff08Segment\uff09\u3001\u533a\uff08Extent\uff09\u3001\u9875\uff08Page\uff09\u548c\u884c\uff08Row\uff09\u3002\u6bcf\u4e00\u5c42\u90fd\u6709\u7279\u5b9a\u7684\u529f\u80fd\u548c\u4f5c\u7528\uff0c\u5171\u540c\u4fdd\u8bc1\u4e86\u6570\u636e\u7684\u9ad8\u6548\u5b58\u50a8\u548c\u68c0\u7d22\u3002\u4e0b\u9762\u6211\u4eec\u8be6\u7ec6\u4ecb\u7ecd\u6bcf\u4e00\u4e2a\u5c42\u6b21\u7684\u5177\u4f53\u5185\u5bb9\u3002<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-47-1024x688.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"688\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-47-1024x688.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-285\"  sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"IQLBt\"><strong>1.1 \u884c (Row)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5b9a\u4e49<\/strong>\uff1a\u884c\u662f\u6570\u636e\u5e93\u8868\u4e2d\u6700\u57fa\u672c\u7684\u5b58\u50a8\u5355\u4f4d\uff0c\u8868\u793a\u8868\u4e2d\u7684\u4e00\u6761\u8bb0\u5f55\u3002<\/li>\n\n\n\n<li><strong>\u7ed3\u6784<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u4e8b\u52a1ID\uff08Trx id\uff09<\/strong>\uff1a\u6807\u8bc6\u6700\u8fd1\u4fee\u6539\u8be5\u884c\u7684\u4e8b\u52a1ID\uff0c\u7528\u4e8e\u591a\u7248\u672c\u5e76\u53d1\u63a7\u5236\uff08MVCC\uff09\u3002<\/li>\n\n\n\n<li><strong>\u56de\u6eda\u6307\u9488\uff08Roll Pointer\uff09<\/strong>\uff1a\u6307\u5411\u65e7\u7248\u672c\u6570\u636e\uff0c\u7528\u4e8e\u652f\u6301\u4e8b\u52a1\u56de\u6eda\u548cMVCC\u3002<\/li>\n\n\n\n<li><strong>\u5217\u6570\u636e\uff08Columns\uff09<\/strong>\uff1a\u5b58\u50a8\u5b9e\u9645\u7684\u6570\u636e\u503c\uff0c\u6bcf\u5217\u5bf9\u5e94\u8868\u4e2d\u7684\u4e00\u4e2a\u5b57\u6bb5\u3002<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5b58\u50a8\u4f4d\u7f6e<\/strong>\uff1a\u884c\u6570\u636e\u5b58\u50a8\u5728\u9875\u4e2d\uff0c\u6bcf\u4e00\u9875\u53ef\u4ee5\u5b58\u50a8\u591a\u884c\u6570\u636e\u3002<\/li>\n\n\n\n<li><strong>\u793a\u4f8b<\/strong>\uff1a\u5728\u56fe\u4e2d\uff0c\u884c\u663e\u793a\u4e3a\u4e00\u4e2a\u5305\u542b\u591a\u4e2a\u5b57\u6bb5\u7684\u7ed3\u6784\uff0c\u5176\u4e2d\u5305\u62ec<code>Trx id<\/code>\u548c<code>Roll Pointer<\/code>\uff0c\u8fd9\u4e9b\u5b57\u6bb5\u7528\u4e8e\u4e8b\u52a1\u5904\u7406\u548c\u591a\u7248\u672c\u5e76\u53d1\u63a7\u5236\uff08MVCC\uff09\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"r74Fo\"><strong>1.2 \u9875 (Page)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5b9a\u4e49<\/strong>\uff1a\u9875\u662fInnoDB\u5b58\u50a8\u5f15\u64ce\u4e2d\u6700\u5c0f\u7684\u5b58\u50a8\u5355\u5143\uff0c\u6bcf\u4e2a\u9875\u7684\u5927\u5c0f\u4e3a16KB\u3002\u6bcf\u9875\u6700\u591a\u5b58\u653e7992\u884c\u6570\u636e\u3002<\/li>\n\n\n\n<li><strong>\u7c7b\u578b<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u6570\u636e\u9875\uff08Data Page\uff09<\/strong>\uff1a\u5b58\u50a8\u8868\u7684\u6570\u636e\u884c\u3002<\/li>\n\n\n\n<li><strong>\u7d22\u5f15\u9875\uff08Index Page\uff09<\/strong>\uff1a\u5b58\u50a8B+\u6811\u7d22\u5f15\u7684\u8282\u70b9\u3002<\/li>\n\n\n\n<li><strong>Undo\u65e5\u5fd7\u9875\uff08Undo Log Page\uff09<\/strong>\uff1a\u5b58\u50a8Undo\u65e5\u5fd7\uff0c\u7528\u4e8e\u4e8b\u52a1\u56de\u6eda\u548cMVCC\u3002<\/li>\n\n\n\n<li><strong>\u7ed3\u6784<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u9875\u5934\uff08Page Header\uff09<\/strong>\uff1a\u5305\u542b\u9875\u7c7b\u578b\u3001\u9875ID\u7b49\u5143\u6570\u636e\u3002<\/li>\n\n\n\n<li><strong>\u9875\u6570\u636e\uff08Page Data\uff09<\/strong>\uff1a\u5b9e\u9645\u5b58\u50a8\u7684\u6570\u636e\u884c\u6216\u7d22\u5f15\u9879\u3002<\/li>\n\n\n\n<li><strong>\u9875\u5c3e\uff08Page Trailer\uff09<\/strong>\uff1a\u5305\u542b\u6821\u9a8c\u4fe1\u606f\u7b49\u3002<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u4f5c\u7528<\/strong>\uff1a\u9875\u7528\u4e8e\u5b58\u50a8\u5b9e\u9645\u7684\u6570\u636e\u6216\u7d22\u5f15\u9879\uff0c\u6bcf\u4e2a\u9875\u53ef\u4ee5\u5305\u542b\u591a\u4e2a\u884c\u3002<\/li>\n\n\n\n<li><strong>\u793a\u4f8b<\/strong>\uff1a\u5728\u56fe\u4e2d\uff0c\u9875\u663e\u793a\u4e3a\u4e00\u4e2a\u5305\u542b\u591a\u4e2a\u884c\u7684\u77e9\u9635\u7ed3\u6784\uff0c\u6bcf\u4e2a\u5c0f\u65b9\u683c\u4ee3\u8868\u4e00\u884c\u6570\u636e\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"TfrDU\"><strong>1.3 \u533a (Extent)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5b9a\u4e49<\/strong>\uff1a\u533a\u662fInnoDB\u8868\u7a7a\u95f4\u4e2d\u7684\u5206\u914d\u5355\u5143\uff0c\u6bcf\u4e2a\u533a\u901a\u5e38\u5305\u542b64\u4e2a\u8fde\u7eed\u7684\u9875\u3002<\/li>\n\n\n\n<li><strong>\u5927\u5c0f<\/strong>\uff1a\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u6bcf\u4e2a\u533a\u7684\u5927\u5c0f\u4e3a1MB\u3002<\/li>\n\n\n\n<li><strong>\u7c7b\u578b<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u6bb5\u533a\uff08Segment Extent\uff09<\/strong>\uff1a\u5206\u914d\u7ed9\u7279\u5b9a\u6bb5\u4f7f\u7528\u7684\u533a\u3002<\/li>\n\n\n\n<li><strong>\u81ea\u7531\u533a\uff08Free Extent\uff09<\/strong>\uff1a\u5c1a\u672a\u5206\u914d\u7ed9\u4efb\u4f55\u6bb5\u7684\u7a7a\u95f2\u533a\u3002<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u4f5c\u7528<\/strong>\uff1a\u533a\u7528\u4e8e\u51cf\u5c11\u5b58\u50a8\u7ba1\u7406\u7684\u788e\u7247\u5316\uff0c\u5e76\u63d0\u9ad8I\/O\u6027\u80fd\u3002<\/li>\n\n\n\n<li><strong>\u7ba1\u7406<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u7a7a\u95f4\u5206\u914d<\/strong>\uff1a\u901a\u8fc7\u6bb5\u7ba1\u7406\u5668\u8fdb\u884c\u533a\u7684\u5206\u914d\u548c\u91ca\u653e\u3002<\/li>\n\n\n\n<li><strong>\u5408\u5e76\u548c\u5206\u5272<\/strong>\uff1a\u6839\u636e\u4f7f\u7528\u60c5\u51b5\u8fdb\u884c\u533a\u7684\u5408\u5e76\u548c\u5206\u5272\uff0c\u4f18\u5316\u7a7a\u95f4\u5229\u7528\u7387\u3002<\/li>\n\n\n\n<li><strong>\u793a\u4f8b<\/strong>\uff1a\u5728\u56fe\u4e2d\uff0c\u533a\u663e\u793a\u4e3a\u4e00\u4e2a\u5305\u542b\u591a\u4e2a\u9875\u7684\u77e9\u9635\u7ed3\u6784\uff0c\u591a\u4e2a\u533a\u7ec4\u6210\u4e86\u4e00\u4e2a\u6bb5\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Af77f\"><strong>1.4 \u6bb5 (Segment)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5b9a\u4e49<\/strong>\uff1a\u6bb5\u662fInnoDB\u7528\u4e8e\u5b58\u50a8\u6570\u636e\u6216\u7d22\u5f15\u7684\u903b\u8f91\u7ed3\u6784\u3002\u6bcf\u4e2a\u8868\u7684\u6570\u636e\u548c\u7d22\u5f15\u5206\u522b\u5b58\u50a8\u5728\u4e0d\u540c\u7684\u6bb5\u4e2d\u3002<\/li>\n\n\n\n<li><strong>\u7c7b\u578b<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u6570\u636e\u6bb5\uff08Data Segment\uff09<\/strong>\uff1a\u5b58\u50a8\u8868\u7684\u6570\u636e\u884c\u3002<\/li>\n\n\n\n<li><strong>\u7d22\u5f15\u6bb5\uff08Index Segment\uff09<\/strong>\uff1a\u5b58\u50a8B+\u6811\u7d22\u5f15\u7684\u8282\u70b9\u3002<\/li>\n\n\n\n<li><strong>\u56de\u6eda\u6bb5\uff08Rollback Segment\uff09<\/strong>\uff1a\u5b58\u50a8Undo\u65e5\u5fd7\uff0c\u7528\u4e8e\u4e8b\u52a1\u56de\u6eda\u548cMVCC\u3002<\/li>\n\n\n\n<li><strong>\u7ba1\u7406<\/strong>\uff1a<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u6bb5\u5934\uff08Segment Header\uff09<\/strong>\uff1a\u5305\u542b\u6bb5\u7c7b\u578b\u3001\u6bb5ID\u7b49\u5143\u6570\u636e\u3002<\/li>\n\n\n\n<li><strong>\u6bb5\u533a\u5217\u8868\uff08Extent List\uff09<\/strong>\uff1a\u8bb0\u5f55\u5c5e\u4e8e\u8be5\u6bb5\u7684\u6240\u6709\u533a\u3002<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u4f5c\u7528<\/strong>\uff1a\u6bb5\u7ba1\u7406\u591a\u4e2a\u533a\uff0c\u6bcf\u4e2a\u6bb5\u53ef\u4ee5\u5305\u542b\u4e00\u4e2a\u6216\u591a\u4e2a\u533a\u3002<\/li>\n\n\n\n<li><strong>\u793a\u4f8b<\/strong>\uff1a\u5728\u56fe\u4e2d\uff0c\u6bb5\u663e\u793a\u4e3a\u4e00\u4e2a\u5305\u542b\u591a\u4e2a\u533a\u7684\u7ed3\u6784\uff0c\u5305\u62ec\u53f6\u8282\u70b9\u6bb5\uff08Leaf node segment\uff09\u3001\u975e\u53f6\u8282\u70b9\u6bb5\uff08Non-Leaf node segment\uff09\u548c\u56de\u6eda\u6bb5\uff08Rollback segment\uff09\u3002<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Tcj1c\">2. \u5171\u4eab\u8868\u7a7a\u95f4\u548c\u72ec\u7acb\u8868\u7a7a\u95f4<\/h2>\n\n\n\n<p id=\"uf1bbad1b\">InnoDB\u5b58\u50a8\u5f15\u64ce\u652f\u6301\u4e24\u79cd\u8868\u7a7a\u95f4\u6a21\u5f0f\uff1a\u5171\u4eab\u8868\u7a7a\u95f4\uff08System Tablespace\uff09\u548c\u72ec\u7acb\u8868\u7a7a\u95f4\uff08File-Per-Table Tablespace\uff09\u3002\u8fd9\u4e24\u79cd\u6a21\u5f0f\u5728\u6570\u636e\u5b58\u50a8\u548c\u7ba1\u7406\u4e0a\u6709\u4e0d\u540c\u7684\u7279\u70b9\u548c\u9002\u7528\u573a\u666f\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"y1XsE\">2.1. \u5171\u4eab\u8868\u7a7a\u95f4 (System Tablespace)<\/h3>\n\n\n\n<p id=\"u12153658\"><strong>\u5b9a\u4e49<\/strong>\uff1a\u5171\u4eab\u8868\u7a7a\u95f4\u662fInnoDB\u7684\u9ed8\u8ba4\u8868\u7a7a\u95f4\u6a21\u5f0f\uff0c\u6240\u6709\u8868\u7684\u6570\u636e\u548c\u7d22\u5f15\u5b58\u50a8\u5728\u4e00\u4e2a\u6216\u591a\u4e2a\u5171\u4eab\u8868\u7a7a\u95f4\u6587\u4ef6\u4e2d\uff08\u4f8b\u5982<code>ibdata1<\/code>\uff09\u3002<\/p>\n\n\n\n<p id=\"u19600b17\"><strong>\u7ed3\u6784<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5355\u4e2a\u6587\u4ef6<\/strong>\uff1a\u6240\u6709\u8868\u7684\u6570\u636e\u548c\u7d22\u5f15\u90fd\u5b58\u50a8\u5728\u540c\u4e00\u4e2a\u6216\u591a\u4e2a\u6587\u4ef6\u4e2d\u3002<\/li>\n\n\n\n<li><strong>\u5143\u6570\u636e\u5b58\u50a8<\/strong>\uff1a\u8868\u7a7a\u95f4\u4e2d\u8fd8\u5305\u542b\u5143\u6570\u636e\u3001\u6570\u636e\u5b57\u5178\u3001\u53cc\u5199\u7f13\u51b2\u533a\uff08Doublewrite Buffer\uff09\u3001\u4ee5\u53caUndo\u65e5\u5fd7\u3002<\/li>\n<\/ul>\n\n\n\n<p id=\"ub9817164\"><strong>\u4f18\u70b9<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u7ba1\u7406\u65b9\u4fbf<\/strong>\uff1a\u96c6\u4e2d\u7ba1\u7406\u6240\u6709\u8868\u7684\u6570\u636e\u548c\u7d22\u5f15\u6587\u4ef6\u3002<\/li>\n\n\n\n<li><strong>\u7b80\u5316\u5907\u4efd<\/strong>\uff1a\u53ef\u4ee5\u901a\u8fc7\u5907\u4efd\u5c11\u91cf\u6587\u4ef6\u6765\u5907\u4efd\u6574\u4e2a\u6570\u636e\u5e93\u3002<\/li>\n<\/ul>\n\n\n\n<p id=\"u4b08de07\"><strong>\u7f3a\u70b9<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u6587\u4ef6\u81a8\u80c0<\/strong>\uff1a\u5f53\u8868\u7684\u6570\u636e\u91cf\u589e\u52a0\u65f6\uff0c\u5171\u4eab\u8868\u7a7a\u95f4\u6587\u4ef6\u4f1a\u4e0d\u65ad\u589e\u5927\uff0c\u5bb9\u6613\u5bfc\u81f4\u78c1\u76d8\u7a7a\u95f4\u7ba1\u7406\u590d\u6742\u3002<\/li>\n\n\n\n<li><strong>\u788e\u7247\u95ee\u9898<\/strong>\uff1a\u5220\u9664\u6570\u636e\u540e\uff0c\u6587\u4ef6\u7a7a\u95f4\u4e0d\u4f1a\u81ea\u52a8\u91ca\u653e\uff0c\u5bb9\u6613\u4ea7\u751f\u788e\u7247\u3002<\/li>\n\n\n\n<li><strong>\u6062\u590d\u56f0\u96be<\/strong>\uff1a\u5982\u679c\u4e00\u4e2a\u5171\u4eab\u8868\u7a7a\u95f4\u6587\u4ef6\u635f\u574f\uff0c\u53ef\u80fd\u4f1a\u5f71\u54cd\u5230\u591a\u4e2a\u8868\u7684\u6570\u636e\u3002<\/li>\n<\/ul>\n\n\n\n<p id=\"u2752a781\"><strong>\u9002\u7528\u573a\u666f<\/strong>\uff1a\u9002\u7528\u4e8e\u5c0f\u578b\u6570\u636e\u5e93\u6216\u6570\u636e\u91cf\u8f83\u5c11\u7684\u5e94\u7528\u573a\u666f\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"bWmjw\">2.2. \u72ec\u7acb\u8868\u7a7a\u95f4 (File-Per-Table Tablespace)<\/h3>\n\n\n\n<p id=\"u3b3c5202\"><strong>\u5b9a\u4e49<\/strong>\uff1a\u72ec\u7acb\u8868\u7a7a\u95f4\u6a21\u5f0f\u4e0b\uff0c\u6bcf\u4e2a\u8868\u7684\u6570\u636e\u548c\u7d22\u5f15\u5b58\u50a8\u5728\u72ec\u7acb\u7684\u8868\u7a7a\u95f4\u6587\u4ef6\u4e2d\uff08\u4f8b\u5982<code>*.ibd<\/code>\u6587\u4ef6\uff09\u3002<\/p>\n\n\n\n<p id=\"u69a4fc75\"><strong>\u7ed3\u6784<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5355\u72ec\u6587\u4ef6<\/strong>\uff1a\u6bcf\u4e2a\u8868\u6709\u72ec\u7acb\u7684\u8868\u7a7a\u95f4\u6587\u4ef6\uff0c\u901a\u5e38\u4ee5<code>*.ibd<\/code>\u4e3a\u6269\u5c55\u540d\u3002<\/li>\n\n\n\n<li><strong>\u6570\u636e\u548c\u7d22\u5f15<\/strong>\uff1a\u8868\u7684\u6570\u636e\u548c\u7d22\u5f15\u90fd\u5b58\u50a8\u5728\u8fd9\u4e2a\u72ec\u7acb\u6587\u4ef6\u4e2d\u3002<\/li>\n<\/ul>\n\n\n\n<p id=\"u7614bd51\"><strong>\u4f18\u70b9<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u7a7a\u95f4\u7ba1\u7406<\/strong>\uff1a\u5220\u9664\u8868\u65f6\u53ef\u4ee5\u76f4\u63a5\u91ca\u653e\u5bf9\u5e94\u7684\u8868\u7a7a\u95f4\u6587\u4ef6\uff0c\u51cf\u5c11\u788e\u7247\u3002<\/li>\n\n\n\n<li><strong>\u5907\u4efd\u548c\u6062\u590d<\/strong>\uff1a\u53ef\u4ee5\u5355\u72ec\u5907\u4efd\u548c\u6062\u590d\u67d0\u4e2a\u8868\u7684\u8868\u7a7a\u95f4\u6587\u4ef6\uff0c\u63d0\u9ad8\u7075\u6d3b\u6027\u3002<\/li>\n\n\n\n<li><strong>\u4f18\u5316\u6027\u80fd<\/strong>\uff1a\u5728\u9ad8\u5e76\u53d1\u5199\u5165\u573a\u666f\u4e0b\uff0c\u53ef\u4ee5\u51cf\u5c0f\u9501\u4e89\u7528\uff0c\u63d0\u9ad8\u6027\u80fd\u3002<\/li>\n<\/ul>\n\n\n\n<p id=\"u5b61e449\"><strong>\u7f3a\u70b9<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u6587\u4ef6\u6570\u91cf\u589e\u52a0<\/strong>\uff1a\u5bf9\u4e8e\u5927\u91cf\u8868\u7684\u6570\u636e\u5e93\uff0c\u6587\u4ef6\u7cfb\u7edf\u7ba1\u7406\u7684\u6587\u4ef6\u6570\u91cf\u4f1a\u663e\u8457\u589e\u52a0\u3002<\/li>\n\n\n\n<li><strong>\u590d\u6742\u7ba1\u7406<\/strong>\uff1a\u9700\u8981\u7ba1\u7406\u66f4\u591a\u7684\u6587\u4ef6\uff0c\u53ef\u80fd\u589e\u52a0\u8fd0\u7ef4\u590d\u6742\u5ea6\u3002<\/li>\n<\/ul>\n\n\n\n<p id=\"u0ac29c76\"><strong>\u9002\u7528\u573a\u666f<\/strong>\uff1a\u9002\u7528\u4e8e\u5927\u4e2d\u578b\u6570\u636e\u5e93\u6216\u9700\u8981\u9891\u7e41\u589e\u5220\u8868\u7684\u5e94\u7528\u573a\u666f\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"EgDcj\">2.3. \u914d\u7f6e\u548c\u542f\u7528\u72ec\u7acb\u8868\u7a7a\u95f4<\/h3>\n\n\n\n<p id=\"u62fe0a07\"><strong>\u542f\u7528\u72ec\u7acb\u8868\u7a7a\u95f4\u6a21\u5f0f<\/strong>\uff1a<br>\u5728MySQL\u914d\u7f6e\u6587\u4ef6<code>my.cnf<\/code>\u6216<code>my.ini<\/code>\u4e2d\u6dfb\u52a0\u4ee5\u4e0b\u914d\u7f6e\uff1a<\/p>\n\n\n\n<pre id=\"lW5Mz\" class=\"wp-block-code\"><code>&#91;mysqld]\ninnodb_file_per_table=1<\/code><\/pre>\n\n\n\n<p id=\"u23d0444d\"><strong>\u521b\u5efa\u8868<\/strong>\uff1a<br>\u5f53\u542f\u7528\u72ec\u7acb\u8868\u7a7a\u95f4\u6a21\u5f0f\u540e\uff0c\u521b\u5efa\u65b0\u8868\u65f6\u4f1a\u81ea\u52a8\u5728\u5355\u72ec\u7684\u6587\u4ef6\u4e2d\u5b58\u50a8\u6570\u636e\u548c\u7d22\u5f15\uff1a<\/p>\n\n\n\n<pre id=\"Ktiud\" class=\"wp-block-code\"><code>CREATE TABLE test_table (\n    id INT PRIMARY KEY,\n    name VARCHAR(50)\n) ENGINE=InnoDB;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"rYJ3l\">\u603b\u7ed3<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5171\u4eab\u8868\u7a7a\u95f4<\/strong>\uff1a\u9002\u7528\u4e8e\u5c0f\u578b\u6570\u636e\u5e93\uff0c\u7ba1\u7406\u65b9\u4fbf\uff0c\u4f46\u6587\u4ef6\u81a8\u80c0\u548c\u788e\u7247\u95ee\u9898\u53ef\u80fd\u8f83\u4e25\u91cd\u3002<\/li>\n\n\n\n<li><strong>\u72ec\u7acb\u8868\u7a7a\u95f4<\/strong>\uff1a\u9002\u7528\u4e8e\u5927\u4e2d\u578b\u6570\u636e\u5e93\uff0c\u7075\u6d3b\u6027\u9ad8\uff0c\u7a7a\u95f4\u7ba1\u7406\u66f4\u6709\u6548\uff0c\u4f46\u6587\u4ef6\u6570\u91cf\u589e\u591a\uff0c\u9700\u8981\u66f4\u590d\u6742\u7684\u7ba1\u7406\u3002<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Sd7Xt\">3. \u5b9e\u9a8c\uff1a\u72ec\u7acb\u8868\u7a7a\u95f4\u8fc1\u79fb\u548c\u7528mysqldump\u8fc1\u79fb\u6570\u636e\u6bd4\u8f83<\/h2>\n\n\n\n<p id=\"u84f000bd\">\u76f4\u63a5\u5bfc\u51fa\u8868\u7ed3\u6784\u548c\u6570\u636e\u4e0e\u62f7\u8d1d<code>.ibd<\/code>\u6587\u4ef6\u6709\u4e00\u4e9b\u5173\u952e\u533a\u522b\u3002\u4e0b\u9762\u662f\u4e24\u79cd\u65b9\u6cd5\u7684\u8be6\u7ec6\u6bd4\u8f83\uff1a<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"wDuHX\">3.1. \u5bfc\u51fa\u8868\u7ed3\u6784\u548c\u6570\u636e<\/h3>\n\n\n\n<p id=\"u544616cf\"><strong>\u6b65\u9aa4<\/strong>\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u4f7f\u7528<code>mysqldump<\/code>\u5bfc\u51fa\u8868\u7ed3\u6784\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"kyqsG\" class=\"wp-block-code\"><code>mysqldump -u root -p --no-data database_name test_table &gt; test_table_structure.sql<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li>\u4f7f\u7528<code>mysqldump<\/code>\u5bfc\u51fa\u8868\u6570\u636e\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"S4dPP\" class=\"wp-block-code\"><code>mysqldump -u root -p --no-create-info database_name test_table &gt; test_table_data.sql<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li>\u5728\u76ee\u6807\u6570\u636e\u5e93\u4e2d\u5bfc\u5165\u8868\u7ed3\u6784\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"aObbm\" class=\"wp-block-code\"><code>mysql -u root -p database_name &lt; test_table_structure.sql<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li>\u5728\u76ee\u6807\u6570\u636e\u5e93\u4e2d\u5bfc\u5165\u8868\u6570\u636e\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"XEkWE\" class=\"wp-block-code\"><code>mysql -u root -p database_name &lt; test_table_data.sql<\/code><\/pre>\n\n\n\n<p id=\"u3999a335\"><strong>\u4f18\u70b9<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u4e00\u81f4\u6027\u4fdd\u969c<\/strong>\uff1a<code>mysqldump<\/code>\u5de5\u5177\u4f1a\u9501\u8868\uff08\u6839\u636e\u914d\u7f6e\uff0c\u53ef\u4ee5\u662f\u8bfb\u9501\u6216\u5199\u9501\uff09\uff0c\u786e\u4fdd\u5bfc\u51fa\u8fc7\u7a0b\u4e2d\u6570\u636e\u7684\u4e00\u81f4\u6027\u3002<\/li>\n\n\n\n<li><strong>\u7075\u6d3b\u6027\u9ad8<\/strong>\uff1a\u53ef\u4ee5\u9009\u62e9\u53ea\u5bfc\u51fa\u7ed3\u6784\u6216\u53ea\u5bfc\u51fa\u6570\u636e\uff0c\u4e5f\u53ef\u4ee5\u5bf9\u6570\u636e\u8fdb\u884c\u7b5b\u9009\u3002<\/li>\n\n\n\n<li><strong>\u6613\u7528\u6027<\/strong>\uff1a\u5bfc\u51fa\u7684\u6587\u4ef6\u662f\u6807\u51c6\u7684SQL\u811a\u672c\uff0c\u6613\u4e8e\u9605\u8bfb\u548c\u7f16\u8f91\u3002<\/li>\n\n\n\n<li><strong>\u8de8\u5e73\u53f0\u652f\u6301<\/strong>\uff1a\u53ef\u4ee5\u5728\u4e0d\u540c\u7684MySQL\u7248\u672c\u6216\u4e0d\u540c\u7684\u5e73\u53f0\u4e4b\u95f4\u8fc1\u79fb\u6570\u636e\u3002<\/li>\n<\/ul>\n\n\n\n<p id=\"u5b7e18df\"><strong>\u7f3a\u70b9<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5bfc\u51fa\u548c\u5bfc\u5165\u65f6\u95f4\u957f<\/strong>\uff1a\u5bf9\u4e8e\u5927\u8868\uff0c\u5bfc\u51fa\u548c\u5bfc\u5165\u8fc7\u7a0b\u53ef\u80fd\u975e\u5e38\u8017\u65f6\u3002<\/li>\n\n\n\n<li><strong>\u6027\u80fd\u5f00\u9500<\/strong>\uff1a\u5728\u5bfc\u51fa\u8fc7\u7a0b\u4e2d\uff0c\u5c24\u5176\u662f\u5bf9\u5927\u8868\u8fdb\u884c\u5168\u8868\u626b\u63cf\uff0c\u4f1a\u4ea7\u751f\u8f83\u9ad8\u7684I\/O\u548cCPU\u5f00\u9500\u3002<\/li>\n\n\n\n<li><strong>\u6587\u4ef6\u5927\u5c0f\u5927<\/strong>\uff1a\u5bfc\u51fa\u7684SQL\u6587\u4ef6\u53ef\u80fd\u975e\u5e38\u5927\uff0c\u5360\u7528\u5927\u91cf\u78c1\u76d8\u7a7a\u95f4\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"pvkLZ\">3.2. \u590d\u5236.ibd\u6587\u4ef6<\/h3>\n\n\n\n<p id=\"u5cd2be19\"><strong>\u6b65\u9aa4<\/strong>\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u786e\u4fdd\u6e90\u8868\u5df2\u4f7f\u7528\u72ec\u7acb\u8868\u7a7a\u95f4\uff08<code>innodb_file_per_table=1<\/code>\uff09\u3002<\/li>\n\n\n\n<li>\u5c06\u8868\u7ed3\u6784\u5bfc\u51fa\u5230\u76ee\u6807\u6570\u636e\u5e93\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"lEPAF\" class=\"wp-block-code\"><code>mysqldump -u root -p --no-data database_name test_table &gt; test_table_structure.sql\nmysql -u root -p database_name &lt; test_table_structure.sql<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li>\u5728\u6e90\u6570\u636e\u5e93\u4e2d\u9501\u5b9a\u8868\uff0c\u5e76\u4e14\u5c06\u8868\u7684<code>.ibd<\/code>\u6587\u4ef6\u8131\u673a\uff08flush + table discard\uff09\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"JH9rU\" class=\"wp-block-code\"><code>FLUSH TABLES test_table WITH READ LOCK;\nALTER TABLE test_table DISCARD TABLESPACE;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li>\u590d\u5236\u6e90\u6570\u636e\u5e93\u7684\u6570\u636e\u76ee\u5f55\u4e2d\u7684<code>test_table.ibd<\/code>\u6587\u4ef6\u5230\u76ee\u6807\u6570\u636e\u5e93\u7684\u6570\u636e\u76ee\u5f55\u4e2d\u3002<\/li>\n\n\n\n<li>\u5728\u76ee\u6807\u6570\u636e\u5e93\u4e2d\u63a5\u6536<code>.ibd<\/code>\u6587\u4ef6\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"LRl2D\" class=\"wp-block-code\"><code>ALTER TABLE test_table IMPORT TABLESPACE;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"6\">\n<li>\u89e3\u9501\u8868\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"W7H9O\" class=\"wp-block-code\"><code>UNLOCK TABLES;<\/code><\/pre>\n\n\n\n<p id=\"uc4b1a609\"><strong>\u4f18\u70b9<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u901f\u5ea6\u5feb<\/strong>\uff1a\u76f4\u63a5\u590d\u5236\u6587\u4ef6\uff0c\u901f\u5ea6\u901a\u5e38\u6bd4\u901a\u8fc7<code>mysqldump<\/code>\u5bfc\u51fa\u548c\u5bfc\u5165\u8981\u5feb\u5f97\u591a\u3002<\/li>\n\n\n\n<li><strong>\u4f4e\u5f00\u9500<\/strong>\uff1a\u907f\u514d\u4e86\u5168\u8868\u626b\u63cf\u548cSQL\u89e3\u6790\uff0c\u51cf\u5c11\u4e86CPU\u548cI\/O\u5f00\u9500\u3002<\/li>\n\n\n\n<li><strong>\u7a7a\u95f4\u8282\u7701<\/strong>\uff1a\u76f4\u63a5\u590d\u5236\u6587\u4ef6\uff0c\u907f\u514d\u4e86\u4e2d\u95f4\u7684\u4e34\u65f6\u6587\u4ef6\u5360\u7528\u7a7a\u95f4\u3002<\/li>\n<\/ul>\n\n\n\n<p id=\"ub6c1f952\"><strong>\u7f3a\u70b9<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u4e00\u81f4\u6027\u98ce\u9669<\/strong>\uff1a\u5982\u679c\u5728\u590d\u5236\u8fc7\u7a0b\u4e2d\u672a\u80fd\u6b63\u786e\u9501\u5b9a\u8868\u6216\u672a\u80fd\u786e\u4fdd\u6570\u636e\u7684\u4e00\u81f4\u6027\uff0c\u53ef\u80fd\u4f1a\u5bfc\u81f4\u6570\u636e\u635f\u574f\u3002<\/li>\n\n\n\n<li><strong>\u590d\u6742\u6027\u9ad8<\/strong>\uff1a\u9700\u8981\u5bf9\u8868\u8fdb\u884c\u9501\u5b9a\u548c\u8131\u673a\u64cd\u4f5c\uff0c\u5e76\u4e14\u5728\u64cd\u4f5c\u4e0d\u5f53\u65f6\u53ef\u80fd\u4f1a\u5f71\u54cd\u6570\u636e\u5e93\u7684\u6b63\u5e38\u8fd0\u884c\u3002<\/li>\n\n\n\n<li><strong>\u5e73\u53f0\u9650\u5236<\/strong>\uff1a\u901a\u5e38\u53ea\u5728\u76f8\u540c\u7684MySQL\u7248\u672c\u548c\u76f8\u540c\u7684\u5e73\u53f0\u4e4b\u95f4\u590d\u5236\u6587\u4ef6\u624d\u53ef\u9760\u3002<\/li>\n\n\n\n<li><strong>\u6587\u4ef6\u7cfb\u7edf\u4f9d\u8d56<\/strong>\uff1a\u9700\u8981\u786e\u4fdd\u76ee\u6807\u6587\u4ef6\u7cfb\u7edf\u548c\u6e90\u6587\u4ef6\u7cfb\u7edf\u517c\u5bb9\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"s6u41\">3.3. \u4e24\u79cd\u8fc1\u79fb\u65b9\u6cd5\u5bf9\u6bd4<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5bfc\u51fa\u8868\u7ed3\u6784\u548c\u6570\u636e<\/strong>\u9002\u7528\u4e8e\u9700\u8981\u8de8\u5e73\u53f0\u6216\u8de8MySQL\u7248\u672c\u8fc1\u79fb\u7684\u573a\u666f\uff0c\u6570\u636e\u4e00\u81f4\u6027\u6709\u4fdd\u969c\uff0c\u4f46\u53ef\u80fd\u4f1a\u8017\u65f6\u8f83\u957f\u3002<\/li>\n\n\n\n<li><strong>\u590d\u5236.ibd\u6587\u4ef6<\/strong>\u9002\u7528\u4e8e\u76f8\u540cMySQL\u7248\u672c\u548c\u5e73\u53f0\u4e4b\u95f4\u7684\u6570\u636e\u8fc1\u79fb\uff0c\u901f\u5ea6\u5feb\u3001\u5f00\u9500\u4f4e\uff0c\u4f46\u64cd\u4f5c\u590d\u6742\u4e14\u5b58\u5728\u4e00\u81f4\u6027\u98ce\u9669\u3002<\/li>\n<\/ul>\n\n\n\n<p id=\"u94cf65a4\">\u5728\u9009\u62e9\u65b9\u6cd5\u65f6\uff0c\u9700\u8981\u6839\u636e\u5177\u4f53\u7684\u4f7f\u7528\u573a\u666f\u3001\u6570\u636e\u91cf\u548c\u5bf9\u6570\u636e\u4e00\u81f4\u6027\u7684\u8981\u6c42\u6765\u51b3\u5b9a\u3002<\/p>\n\n\n\n<p id=\"u8986241d\">\u4e3a\u4e86\u5728\u72ec\u7acb\u8868\u7a7a\u95f4\u4e2d\u5206\u79bbUndo\u65e5\u5fd7\uff0c\u9700\u8981\u5bf9MySQL\u914d\u7f6e\u6587\u4ef6\u8fdb\u884c\u76f8\u5173\u8bbe\u7f6e\uff0c\u5e76\u786e\u4fdd\u64cd\u4f5c\u6b65\u9aa4\u6b63\u786e\u3002\u4ee5\u4e0b\u662f\u5177\u4f53\u7684\u8bbe\u7f6e\u6b65\u9aa4\u548c\u914d\u7f6e\u8bf4\u660e\uff1a<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"JdUNm\">4. \u5206\u79bbUndo\u65e5\u5fd7\u5230\u72ec\u7acb\u8868\u7a7a\u95f4<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"dS6HL\">4.1. \u4fee\u6539MySQL\u914d\u7f6e\u6587\u4ef6<\/h3>\n\n\n\n<p id=\"u5d244a69\">\u5728MySQL\u7684\u914d\u7f6e\u6587\u4ef6<code>my.cnf<\/code>\u6216<code>my.ini<\/code>\u4e2d\u6dfb\u52a0\u6216\u4fee\u6539\u4ee5\u4e0b\u914d\u7f6e\u9879\uff1a<\/p>\n\n\n\n<pre id=\"uXrmB\" class=\"wp-block-code\"><code>&#91;mysqld]\n# \u542f\u7528\u72ec\u7acb\u8868\u7a7a\u95f4\ninnodb_file_per_table=1\n\n# \u8bbe\u7f6eUndo\u8868\u7a7a\u95f4\u6570\u91cf\ninnodb_undo_tablespaces=4\n\n# \u6307\u5b9aUndo\u8868\u7a7a\u95f4\u76ee\u5f55\ninnodb_undo_directory=\/var\/lib\/mysql\/undo\n\n# \u542f\u7528\u81ea\u52a8\u6536\u7f29Undo\u65e5\u5fd7\ninnodb_undo_log_truncate=1\n\n# \u8bbe\u7f6e\u5355\u4e2aUndo\u65e5\u5fd7\u6587\u4ef6\u7684\u6700\u5927\u5927\u5c0f\ninnodb_max_undo_log_size=1G\n\n# \u8bbe\u7f6eUndo\u65e5\u5fd7\u6536\u7f29\u9891\u7387\ninnodb_purge_rseg_truncate_frequency=128<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>innodb_file_per_table=1<\/code>\uff1a\u542f\u7528\u72ec\u7acb\u8868\u7a7a\u95f4\u6a21\u5f0f\u3002<\/li>\n\n\n\n<li><code>innodb_undo_tablespaces=4<\/code>\uff1a\u6307\u5b9aUndo\u8868\u7a7a\u95f4\u7684\u6570\u91cf\u3002<\/li>\n\n\n\n<li><code>innodb_undo_directory<\/code>\uff1a\u6307\u5b9aUndo\u8868\u7a7a\u95f4\u6587\u4ef6\u5b58\u50a8\u7684\u76ee\u5f55\u3002<\/li>\n\n\n\n<li><code>innodb_undo_log_truncate=1<\/code>\uff1a\u542f\u7528\u81ea\u52a8\u6536\u7f29Undo\u65e5\u5fd7\u3002<\/li>\n\n\n\n<li><code>innodb_max_undo_log_size=1G<\/code>\uff1a\u8bbe\u7f6e\u5355\u4e2aUndo\u65e5\u5fd7\u6587\u4ef6\u7684\u6700\u5927\u5927\u5c0f\u3002<\/li>\n\n\n\n<li><code>innodb_purge_rseg_truncate_frequency=128<\/code>\uff1a\u8bbe\u7f6eUndo\u65e5\u5fd7\u6536\u7f29\u7684\u9891\u7387\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"aYxJ0\">4.2. \u521b\u5efaUndo\u8868\u7a7a\u95f4\u76ee\u5f55<\/h3>\n\n\n\n<p id=\"u1748483b\">\u786e\u4fdd\u914d\u7f6e\u6587\u4ef6\u4e2d\u6307\u5b9a\u7684Undo\u8868\u7a7a\u95f4\u76ee\u5f55\u5b58\u5728\uff0c\u5e76\u5177\u6709\u9002\u5f53\u7684\u6743\u9650\u3002<\/p>\n\n\n\n<pre id=\"iQ72A\" class=\"wp-block-code\"><code>mkdir -p \/var\/lib\/mysql\/undo\nchown mysql:mysql \/var\/lib\/mysql\/undo<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"I45UV\">4.3. \u91cd\u542fMySQL\u670d\u52a1<\/h3>\n\n\n\n<p id=\"u4860f34c\">\u4f7f\u914d\u7f6e\u751f\u6548\u5e76\u521b\u5efaUndo\u8868\u7a7a\u95f4\u6587\u4ef6\u3002<\/p>\n\n\n\n<pre id=\"XsUTW\" class=\"wp-block-code\"><code>systemctl restart mysql<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"I2LnO\">4.4. \u67e5\u770bUndo\u8868\u7a7a\u95f4\u72b6\u6001<\/h3>\n\n\n\n<p id=\"u1a8a03d0\">\u901a\u8fc7\u4ee5\u4e0bSQL\u547d\u4ee4\u67e5\u770bUndo\u8868\u7a7a\u95f4\u7684\u72b6\u6001\uff0c\u4ee5\u786e\u4fdd\u914d\u7f6e\u6b63\u786e\u3002<\/p>\n\n\n\n<pre id=\"cewbA\" class=\"wp-block-code\"><code>SHOW VARIABLES LIKE 'innodb_undo_tablespaces';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"kduz5\">4.5. \u76d1\u63a7Undo\u65e5\u5fd7\u6536\u7f29<\/h3>\n\n\n\n<p id=\"u7a478cbd\">\u901a\u8fc7\u4ee5\u4e0b\u547d\u4ee4\u76d1\u63a7Undo\u65e5\u5fd7\u7684\u6536\u7f29\u60c5\u51b5\uff0c\u786e\u4fddUndo\u65e5\u5fd7\u7684\u5206\u79bb\u548c\u81ea\u52a8\u6536\u7f29\u529f\u80fd\u6b63\u5e38\u5de5\u4f5c\u3002<\/p>\n\n\n\n<pre id=\"QmMtc\" class=\"wp-block-code\"><code>SELECT * FROM information_schema.innodb_tablespaces WHERE SPACE_TYPE='Undo';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"DS4TJ\">5. \u5b9e\u9a8c\uff1a\u5728\u7ebf\u81ea\u52a8\u6536\u7f29Undo\u65e5\u5fd7<\/h2>\n\n\n\n<p id=\"uf0dbb4c5\">\u4e3a\u4e86\u6d4b\u8bd5\u5728\u7ebf\u81ea\u52a8\u6536\u7f29Undo\u65e5\u5fd7\u7684\u529f\u80fd\uff0c\u53ef\u4ee5\u901a\u8fc7\u4ee5\u4e0b\u6b65\u9aa4\u8fdb\u884c\u5b9e\u9a8c\u3002\u8be5\u5b9e\u9a8c\u5c06\u6f14\u793a\u5982\u4f55\u542f\u7528\u81ea\u52a8\u6536\u7f29Undo\u65e5\u5fd7\u529f\u80fd\uff0c\u5e76\u9a8c\u8bc1\u5176\u6548\u679c\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"aD3z6\">5.1. <strong>\u6b65\u9aa41\uff1a\u542f\u7528\u81ea\u52a8\u6536\u7f29\u529f\u80fd<\/strong><\/h3>\n\n\n\n<p id=\"u04c0f1d9\">\u5728MySQL\u914d\u7f6e\u6587\u4ef6<code>my.cnf<\/code>\u6216<code>my.ini<\/code>\u4e2d\u542f\u7528\u81ea\u52a8\u6536\u7f29\u529f\u80fd\uff0c\u5e76\u8bbe\u7f6e\u76f8\u5173\u53c2\u6570\uff1a<\/p>\n\n\n\n<pre id=\"SbFwG\" class=\"wp-block-code\"><code>&#91;mysqld]\n# \u542f\u7528\u72ec\u7acb\u8868\u7a7a\u95f4\ninnodb_file_per_table=1\n\n# \u8bbe\u7f6eUndo\u8868\u7a7a\u95f4\u6570\u91cf\ninnodb_undo_tablespaces=4\n\n# \u6307\u5b9aUndo\u8868\u7a7a\u95f4\u76ee\u5f55\ninnodb_undo_directory=\/var\/lib\/mysql\/undo\n\n# \u542f\u7528\u81ea\u52a8\u6536\u7f29Undo\u65e5\u5fd7\ninnodb_undo_log_truncate=1\n\n# \u8bbe\u7f6e\u5355\u4e2aUndo\u65e5\u5fd7\u6587\u4ef6\u7684\u6700\u5927\u5927\u5c0f\ninnodb_max_undo_log_size=1G\n\n# \u8bbe\u7f6eUndo\u65e5\u5fd7\u6536\u7f29\u9891\u7387\ninnodb_purge_rseg_truncate_frequency=128<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"O2B0w\">5.2. <strong>\u6b65\u9aa42\uff1a\u91cd\u542fMySQL\u670d\u52a1<\/strong><\/h3>\n\n\n\n<p id=\"ue76407be\">\u91cd\u542fMySQL\u670d\u52a1\u4ee5\u4f7f\u914d\u7f6e\u751f\u6548\uff0c\u5e76\u521b\u5efaUndo\u8868\u7a7a\u95f4\u6587\u4ef6\uff1a<\/p>\n\n\n\n<pre id=\"P4kzx\" class=\"wp-block-code\"><code>systemctl restart mysql<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"rZ8BJ\">5.3. <strong>\u6b65\u9aa43\uff1a\u521b\u5efa\u6d4b\u8bd5\u8868\u5e76\u63d2\u5165\u6570\u636e<\/strong><\/h3>\n\n\n\n<p id=\"u91d4ad55\">\u521b\u5efa\u4e00\u4e2a\u6d4b\u8bd5\u8868\uff0c\u5e76\u63d2\u5165\u5927\u91cf\u6570\u636e\u4ee5\u751f\u6210Undo\u65e5\u5fd7\uff1a<\/p>\n\n\n\n<pre id=\"DNTGZ\" class=\"wp-block-code\"><code>CREATE TABLE test_table (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    data VARCHAR(255)\n) ENGINE=InnoDB;\n\n-- \u63d2\u5165\u5927\u91cf\u6570\u636e\nINSERT INTO test_table (data) VALUES (REPEAT('A', 255));\nINSERT INTO test_table (data) SELECT REPEAT('A', 255) FROM test_table;\n-- \u91cd\u590d\u63d2\u5165\uff0c\u76f4\u5230\u751f\u6210\u5927\u91cfUndo\u65e5\u5fd7<\/code><\/pre>\n\n\n\n<p id=\"uea9a7b88\">\u4f7f\u7528\u5faa\u73af\u63d2\u5165\u6570\u636e\uff0c\u786e\u4fdd\u751f\u6210\u5927\u91cf\u7684Undo\u65e5\u5fd7\uff1a<\/p>\n\n\n\n<pre id=\"Nmutp\" class=\"wp-block-code\"><code>for i in {1..10}; do\n    mysql -u root -p -e \"INSERT INTO test_db.test_table (data) SELECT REPEAT('A', 255) FROM test_db.test_table;\"\ndone<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"OUAvU\">5.4. <strong>\u6b65\u9aa44\uff1a\u76d1\u63a7Undo\u65e5\u5fd7\u6587\u4ef6\u5927\u5c0f<\/strong><\/h3>\n\n\n\n<p id=\"u179f532e\">\u4f7f\u7528\u7cfb\u7edf\u547d\u4ee4\u67e5\u770bUndo\u65e5\u5fd7\u6587\u4ef6\u7684\u5927\u5c0f\u53d8\u5316\uff1a<\/p>\n\n\n\n<pre id=\"l1BdJ\" class=\"wp-block-code\"><code>ls -lh \/var\/lib\/mysql\/undo\/<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"X66ni\">5.5. <strong>\u6b65\u9aa45\uff1a\u6267\u884c\u4e8b\u52a1\u5e76\u76d1\u63a7\u6536\u7f29\u8fc7\u7a0b<\/strong><\/h3>\n\n\n\n<p id=\"u079644fb\">\u5728MySQL\u4e2d\u6267\u884c\u4e8b\u52a1\u5e76\u5b9a\u671f\u67e5\u8be2Undo\u65e5\u5fd7\u7684\u72b6\u6001\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u6267\u884c\u591a\u4e2a\u4e8b\u52a1\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"Akfd3\" class=\"wp-block-code\"><code>START TRANSACTION;\nDELETE FROM test_table WHERE id BETWEEN 1 AND 1000;\nCOMMIT;\n\nSTART TRANSACTION;\nUPDATE test_table SET data = REPEAT('B', 255) WHERE id BETWEEN 1001 AND 2000;\nCOMMIT;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li>\u67e5\u8be2Undo\u65e5\u5fd7\u7684\u72b6\u6001\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"RVywu\" class=\"wp-block-code\"><code>SELECT * FROM information_schema.innodb_tablespaces WHERE SPACE_TYPE='Undo';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"YP2kk\">5.6. <strong>\u6b65\u9aa46\uff1a\u9a8c\u8bc1\u81ea\u52a8\u6536\u7f29\u6548\u679c<\/strong><\/h3>\n\n\n\n<p id=\"ub2d07925\">\u5b9a\u671f\u68c0\u67e5Undo\u65e5\u5fd7\u6587\u4ef6\u7684\u5927\u5c0f\u53d8\u5316\uff0c\u786e\u4fdd\u81ea\u52a8\u6536\u7f29\u529f\u80fd\u6b63\u5e38\u5de5\u4f5c\uff1a<\/p>\n\n\n\n<pre id=\"oI0Rm\" class=\"wp-block-code\"><code>ls -lh \/var\/lib\/mysql\/undo\/<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"U7SDI\">5.7. <strong>\u6b65\u9aa47\uff1a\u6e05\u7406\u5b9e\u9a8c\u6570\u636e<\/strong><\/h3>\n\n\n\n<p id=\"u557562b3\">\u5b9e\u9a8c\u5b8c\u6210\u540e\uff0c\u53ef\u4ee5\u5220\u9664\u6d4b\u8bd5\u8868\u5e76\u6e05\u7406\u5b9e\u9a8c\u6570\u636e\uff1a<\/p>\n\n\n\n<pre id=\"GBLEx\" class=\"wp-block-code\"><code>DROP TABLE test_table;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>1. \u884c\uff0c\u9875\uff0c\u533a\uff0c\u6bb5 InnoDB\u5b58\u50a8\u5f15\u64ce\u91c7\u7528\u5206\u5c42\u7684\u5b58\u50a8\u7ed3\u6784\uff0c\u5305\u62ec\u6bb5\uff08Segment\uff09\u3001\u533a\uff08Extent\uff09\u3001\u9875 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,8],"tags":[],"class_list":["post-284","post","type-post","status-publish","format-standard","hentry","category-mysql_storage_engine","category-mysql"],"_links":{"self":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/284","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=284"}],"version-history":[{"count":1,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/284\/revisions"}],"predecessor-version":[{"id":286,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/284\/revisions\/286"}],"wp:attachment":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}