{"id":10021006,"date":"2025-09-18T15:33:26","date_gmt":"2025-09-18T07:33:26","guid":{"rendered":"https:\/\/itmax.cn\/?p=10021006"},"modified":"2025-09-18T15:33:26","modified_gmt":"2025-09-18T07:33:26","slug":"%e7%a0%b4%e8%a7%a3gh-ost%e5%8f%98%e6%9b%b4%e5%af%bc%e8%87%b4mysql%e8%a1%a8%e8%86%a8%e8%83%80%e4%b9%8b%e8%b0%9c%ef%bd%9c%e5%be%97%e7%89%a9%e6%8a%80%e6%9c%af","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10021006","title":{"rendered":"\u7834\u89e3gh-ost\u53d8\u66f4\u5bfc\u81f4MySQL\u8868\u81a8\u80c0\u4e4b\u8c1c\uff5c\u5f97\u7269\u6280\u672f"},"content":{"rendered":"<h2>\u4e00\u3001\u95ee\u9898\u80cc\u666f<\/h2>\n<p>\u4e1a\u52a1\u540c\u5b66\u5728 OneDBA \u5e73\u53f0\u8fdb\u884c\u4e00\u6b21\u6b63\u5e38 DDL \u53d8\u66f4\u5b8c\u6210\u540e\uff08\u53d8\u66f4\u5185\u5bb9\u8ddf\u6b64\u6b21\u95ee\u9898\u65e0\u5173\uff09\uff0c\u53d1\u73b0\u4e00\u4e9b SQL \u5f00\u59cb\u51fa\u73b0\u6162\u67e5\uff0c\u540c\u65f6\u53d8\u66f4\u540e\u7684\u8868\u6bd4\u53d8\u66f4\u524d\u7684\u8868\u5b58\u50a8\u7a7a\u95f4\u81a8\u80c0\u4e86\u51e0\u4e4e 100%\u3002\u7ecf\u8fc7\u5206\u6790\u548c\u6d41\u7a0b\u590d\u73b0\u5b8c\u6574\u8fd8\u539f\u4e86\u6574\u4e2a\u4e8b\u4ef6\uff0c\u53d1\u73b0\u4e86 MySQL \u5728\u5e73\u8861 B+tree \u9875\u5206\u88c2\u65b9\u9762\u9047\u5230\u5355\u884c\u8bb0\u5f55\u592a\u5927\u65f6\u7684\u4e00\u4e9b\u7f3a\u9677\uff0c\u6574\u7406\u5206\u4eab\u3002<\/p>\n<p>\u4e3a\u4e86\u80fd\u66f4\u597d\u7684\u8bf4\u660e\u95ee\u9898\u80cc\u540e\u7684\u673a\u5236\uff0c\u4f1a\u8fdb\u884c\u4e00\u4e9b\u5173\u952e\u7684\u201cMySQL\u539f\u7406\u201d\u548c\u201c\u5f53\u524dDDL\u53d8\u66f4\u6d41\u7a0b\u201d\u65b9\u9762\u7684\u77e5\u8bc6\u94fa\u57ab\uff0c\u719f\u6089\u7684\u540c\u5b66\u53ef\u4ee5\u8df3\u8fc7\u3002<\/p>\n<p>\u672c\u6b21 DDL \u53d8\u66f4\u540e\u5e26\u6765\u4e86\u5982\u4e0b\u95ee\u9898\uff1a<\/p>\n<ul>\n<li>\u53d8\u66f4\u540e\uff0c\u8868\u5b58\u50a8\u7a7a\u95f4\u81a8\u80c0\u4e86\u51e0\u4e4e 100%\uff1b<\/li>\n<li>\u53d8\u66f4\u540e\uff0c\u8868\u7edf\u8ba1\u4fe1\u606f\u51fa\u73b0\u4e86\u4e25\u91cd\u504f\u5dee\uff1b<\/li>\n<li>\u53d8\u66f4\u540e\uff0c\u90e8\u5206\u6709\u6392\u5e8f\u7684 SQL \u51fa\u73b0\u4e86\u6162\u67e5\u3002<\/li>\n<\/ul>\n<p>\u73b0\u5728\u6765\u770b\uff0c\u8868\u7a7a\u95f4\u81a8\u80c0\u8ddf\u7edf\u8ba1\u4fe1\u606f\u51fa\u9519\u662f\u540c\u4e00\u4e2a\u95ee\u9898\u5bfc\u81f4\uff0c\u800c\u7edf\u8ba1\u4fe1\u606f\u51fa\u9519\u95f4\u63a5\u5bfc\u81f4\u4e86\u90e8\u5206SQL\u51fa\u73b0\u4e86\u6162\u67e5\uff0c\u4e0b\u9762\u5e26\u7740\u8fd9\u4e9b\u95ee\u9898\u5f00\u59cb\u4e00\u6b65\u6b65\u5206\u6790\u627e\u6839\u56e0\u3002<\/p>\n<h2>\u4e8c\u3001\u7d22\u5f15\u7ed3\u6784<\/h2>\n<p><strong>B+tree<\/strong><\/p>\n<p>InnoDB \u8868\u662f\u7d22\u5f15\u7ec4\u7ec7\u8868\uff0c\u4e5f\u5c31\u662f\u6240\u8c13\u7684\u7d22\u5f15\u5373\u6570\u636e\uff0c\u6570\u636e\u5373\u7d22\u5f15\u3002\u7d22\u5f15\u5206\u4e3a\u805a\u96c6\u7d22\u5f15\u548c\u4e8c\u7ea7\u7d22\u5f15\uff0c\u6240\u6709\u884c\u6570\u636e\u90fd\u5b58\u50a8\u5728\u805a\u96c6\u7d22\u5f15\uff0c\u4e8c\u7ea7\u7d22\u5f15\u5b58\u50a8\u7684\u662f\u5b57\u6bb5\u503c\u548c\u4e3b\u952e\uff0c\u4f46\u4e0d\u7ba1\u54ea\u79cd\u7d22\u5f15\uff0c\u5176\u7ed3\u6784\u90fd\u662f B+tree \u7ed3\u6784\u3002<\/p>\n<p>\u4e00\u68f5 B+tree \u5206\u4e3a\u6839\u9875\u3001\u975e\u53f6\u5b50\u8282\u70b9\u548c\u53f6\u5b50\u8282\u70b9\uff0c\u4e00\u4e2a\u7b80\u5355\u7684\u793a\u610f\u56fe\uff08from Jeremy Cole\uff09\u5982\u4e0b\uff1a<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"723\" height=\"402\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwc1\" alt=\"image.png\" title=\"image.png\"><\/p>\n<p>\u7531\u4e8e InnoDB B+tree \u7ed3\u6784\u9ad8\u6247\u533a\u7279\u6027\uff0c\u6240\u4ee5\u6bcf\u4e2a\u7d22\u5f15\u9ad8\u5ea6\u57fa\u672c\u5728 3-5 \u5c42\u4e4b\u95f4\uff0c\u5c42\u7ea7\uff08Level\uff09\u4ece\u53f6\u5b50\u8282\u70b9\u7684 0 \u5f00\u59cb\u7f16\u53f7\uff0c\u6cbf\u6811\u5411\u4e0a\u9012\u589e\u3002\u6bcf\u5c42\u7684\u9875\u9762\u8282\u70b9\u4e4b\u95f4\u4f7f\u7528\u53cc\u5411\u94fe\u8868\uff0c\u524d\u4e00\u4e2a\u6307\u9488\u548c\u540e\u4e00\u4e2a\u6307\u9488\u6309key\u5347\u5e8f\u6392\u5217\u3002<\/p>\n<p>\u6700\u5c0f\u5b58\u50a8\u5355\u4f4d\u662f\u9875\uff0c\u6bcf\u4e2a\u9875\u6709\u4e00\u4e2a\u7f16\u53f7\uff0c\u9875\u5185\u7684\u8bb0\u5f55\u4f7f\u7528\u5355\u5411\u94fe\u8868\uff0c\u6309 key \u5347\u5e8f\u6392\u5217\u3002\u6bcf\u4e2a\u6570\u636e\u9875\u4e2d\u6709\u4e24\u4e2a\u865a\u62df\u7684\u884c\u8bb0\u5f55\uff0c\u7528\u6765\u9650\u5b9a\u8bb0\u5f55\u7684\u8fb9\u754c\uff1b\u5176\u4e2d\u6700\u5c0f\u503c\uff08Infimum\uff09\u8868\u793a\u5c0f\u4e8e\u9875\u9762\u4e0a\u4efb\u4f55 key \u7684\u503c\uff0c\u5e76\u4e14\u59cb\u7ec8\u662f\u5355\u5411\u94fe\u8868\u8bb0\u5f55\u5217\u8868\u4e2d\u7684\u7b2c\u4e00\u4e2a\u8bb0\u5f55\uff1b\u6700\u5927\u503c\uff08Supremum\uff09\u8868\u793a\u5927\u4e8e\u9875\u9762\u4e0a\u4efb\u4f55 key \u7684\u503c\uff0c\u5e76\u4e14\u59cb\u7ec8\u662f\u5355\u5411\u94fe\u8868\u8bb0\u5f55\u5217\u8868\u4e2d\u7684\u6700\u540e\u4e00\u6761\u8bb0\u5f55\u3002\u8fd9\u4e24\u4e2a\u503c\u5728\u9875\u521b\u5efa\u65f6\u88ab\u5efa\u7acb\uff0c\u5e76\u4e14\u5728\u4efb\u4f55\u60c5\u51b5\u4e0b\u4e0d\u4f1a\u88ab\u5220\u9664\u3002<\/p>\n<p>\u975e\u53f6\u5b50\u8282\u70b9\u9875\u5305\u542b\u5b50\u9875\u7684\u6700\u5c0f key \u548c\u5b50\u9875\u53f7\uff0c\u79f0\u4e3a\u201c\u8282\u70b9\u6307\u9488\u201d\u3002<\/p>\n<p>\u73b0\u5728\u6211\u4eec\u77e5\u9053\u4e86\u6211\u4eec\u63d2\u5165\u7684\u6570\u636e\u6700\u7ec8\u6839\u636e\u4e3b\u952e\u987a\u5e8f\u5b58\u50a8\u5728\u53f6\u5b50\u8282\u70b9\uff08\u9875\uff09\u91cc\u9762\uff0c\u53ef\u4ee5\u6ee1\u8db3\u70b9\u67e5\u548c\u8303\u56f4\u67e5\u8be2\u7684\u9700\u6c42\u3002<\/p>\n<p><strong>\u9875\uff08page\uff09<\/strong><\/p>\n<p>\u9ed8\u8ba4\u4e00\u4e2a\u9875 16K \u5927\u5c0f\uff0c\u4e14 InnoDB \u89c4\u5b9a\u4e00\u4e2a\u9875\u6700\u5c11\u80fd\u591f\u5b58\u50a8\u4e24\u884c\u6570\u636e\uff0c\u8fd9\u91cc\u9700\u8981\u6ce8\u610f\u89c4\u5b9a\u4e00\u4e2a\u9875\u6700\u5c11\u80fd\u591f\u5b58\u50a8\u4e24\u884c\u6570\u636e\u662f\u6307\u5728\u7a7a\u95f4\u5206\u914d\u4e0a\uff0c\u5e76\u4e0d\u662f\u8bf4\u4e00\u4e2a\u9875\u5fc5\u987b\u8981\u5b58\u4e24\u884c\uff0c\u4e5f\u53ef\u4ee5\u5b58\u4e00\u884c\u3002<\/p>\n<p>\u600e\u4e48\u5b9e\u73b0\u4e00\u4e2a\u9875\u5fc5\u987b\u8981\u80fd\u591f\u5b58\u50a8\u4e24\u884c\u8bb0\u5f55\u5462\uff1f \u5f53\u4e00\u6761\u8bb0\u5f55 &lt;8k \u65f6\u4f1a\u5b58\u50a8\u5728\u5f53\u524d\u9875\u5185\uff0c\u53cd\u4e4b &gt;8k \u65f6\u5fc5\u987b\u6ea2\u51fa\u5b58\u50a8\uff0c\u5f53\u524d\u9875\u53ea\u5b58\u50a8\u6ea2\u51fa\u9875\u9762\u7684\u5730\u5740\uff0c\u9700 20 \u4e2a\u5b57\u8282\uff08\u884c\u683c\u5f0f\uff1aDynamic\uff09\uff0c\u8fd9\u6837\u5c31\u80fd\u4fdd\u8bc1\u4e00\u4e2a\u9875\u80af\u5b9a\u80fd\u6700\u5c11\u5b58\u50a8\u7684\u4e0b\u4e24\u6761\u8bb0\u5f55\u3002<\/p>\n<p><strong>\u6ea2\u51fa\u9875<\/strong><\/p>\n<p>\u5f53\u4e00\u4e2a\u8bb0\u5f55 &gt;8k \u65f6\u4f1a\u5faa\u73af\u67e5\u627e\u53ef\u4ee5\u6ea2\u51fa\u5b58\u50a8\u7684\u5b57\u6bb5\uff0ctext\u7c7b\u5b57\u6bb5\u4f1a\u4f18\u5148\u6ea2\u51fa\uff0c\u6ca1\u6709\u5c31\u5f00\u59cb\u6311\u9009 varchar \u7c7b\u5b57\u6bb5\uff0c\u603b\u4e4b\u8fd9\u662f InnoDB \u5185\u90e8\u884c\u4e3a\uff0c\u76ee\u524d\u65e0\u6cd5\u5e72\u9884\u3002<\/p>\n<p>\u5efa\u8868\u65f6\u65e0\u8bba\u662f\u4f7f\u7528 text \u7c7b\u578b\uff0c\u8fd8\u662f varchar \u7c7b\u578b\uff0c\u5f53\u5927\u5c0f &lt;8k \u65f6\u90fd\u662f\u5b58\u50a8\u5728\u5f53\u524d\u9875\uff0c\u4e5f\u5c31\u662f\u5728 B+tree \u7ed3\u6784\u4e2d\uff0c\u53ea\u6709 &gt;8k \u65f6\u624d\u4f1a\u8fdb\u884c\u6ea2\u51fa\u5b58\u50a8\u3002<\/p>\n<p><strong>\u9875\u9762\u5206\u88c2<\/strong><\/p>\n<p>\u968f\u7740\u8868\u6570\u636e\u7684\u53d8\u5316\uff0c\u5bf9\u8bb0\u5f55\u7684\u65b0\u589e\u3001\u66f4\u65b0\u3001\u5220\u9664\uff1b\u90a3\u4e48\u5982\u4f55\u5728 B+tree \u4e2d\u9ad8\u6548\u7ba1\u7406\u52a8\u6001\u6570\u636e\u4e5f\u662f\u4e00\u9879\u6838\u5fc3\u6311\u6218\u3002<\/p>\n<p>MySQL InnoDB \u5f15\u64ce\u901a\u8fc7\u9875\u9762\u5206\u88c2\u548c\u9875\u9762\u5408\u5e76\u4e24\u5927\u5173\u952e\u673a\u5236\u6765\u52a8\u6001\u8c03\u6574\u5b58\u50a8\u7ed3\u6784\uff0c\u4e0d\u4ec5\u80fd\u786e\u4fdd\u6570\u636e\u7684\u903b\u8f91\u5b8c\u6574\u6027\u548c\u903b\u8f91\u987a\u5e8f\u6b63\u786e\uff0c\u8fd8\u80fd\u4fdd\u8bc1\u6570\u636e\u5e93\u7684\u6574\u4f53\u6027\u80fd\u3002\u8fd9\u4e9b\u673a\u5236\u53d1\u751f\u4e8e InnoDB \u7684 B+tree \u7d22\u5f15\u7ed3\u6784\u5185\u90e8\uff0c\u5176\u5177\u4f53\u64cd\u4f5c\u662f\uff1a<\/p>\n<ul>\n<li><strong>\u9875\u9762\u5206\u88c2<\/strong>\uff1a\u5f53\u5df2\u6ee1\u7684\u7d22\u5f15\u9875\u65e0\u6cd5\u5bb9\u7eb3\u65b0\u8bb0\u5f55\u65f6\uff0c\u521b\u5efa\u65b0\u9875\u5e76\u91cd\u65b0\u5206\u914d\u8bb0\u5f55\u3002<\/li>\n<li><strong>\u9875\u9762\u5408\u5e76<\/strong>\uff1a\u5f53\u9875\u5185\u8bb0\u5f55\u56e0\u5220\u9664\/\u66f4\u65b0\u4f4e\u4e8e\u9608\u503c\u65f6\uff0c\u4e0e\u76f8\u90bb\u9875\u5408\u5e76\u4ee5\u4f18\u5316\u7a7a\u95f4\u3002<\/li>\n<\/ul>\n<p>\u6df1\u5165\u7406\u89e3\u4e0a\u8ff0\u673a\u5236\u81f3\u5173\u91cd\u8981\uff0c\u56e0\u4e3a\u9875\u9762\u7684\u5206\u88c2\u4e0e\u5408\u5e76\u5c06\u76f4\u63a5\u5f71\u54cd\u5b58\u50a8\u6548\u7387\u3001I\/O\u6a21\u5f0f\u3001\u52a0\u9501\u884c\u4e3a\u53ca\u6574\u4f53\u6027\u80fd\u3002\u5176\u4e2d\u9875\u9762\u7684\u5206\u88c2\u4e00\u822c\u5206\u4e3a\u4e24\u79cd\uff1a<\/p>\n<ul>\n<li><strong>\u4e2d\u95f4\u70b9\uff08mid point\uff09\u5206\u88c2<\/strong>\uff1a\u5c06\u539f\u59cb\u9875\u9762\u4e2d50%\u6570\u636e\u79fb\u52a8\u5230\u65b0\u7533\u8bf7\u9875\u9762\uff0c\u8fd9\u662f\u6700\u666e\u901a\u7684\u5206\u88c2\u65b9\u6cd5\u3002<\/li>\n<li><strong>\u63d2\u5165\u70b9\uff08insert point\uff09\u5206\u88c2<\/strong>\uff1a\u5224\u65ad\u672c\u6b21\u63d2\u5165\u662f\u5426\u9012\u589e or \u9012\u51cf\uff0c\u5982\u679c\u5224\u5b9a\u4e3a\u987a\u5e8f\u63d2\u5165\uff0c\u5c31\u5728\u5f53\u524d\u63d2\u5165\u70b9\u8fdb\u884c\u5206\u88c2\uff0c\u8fd9\u91cc\u60c5\u51b5\u7ec6\u5206\u8f83\u591a\uff0c\u5927\u90e8\u5206\u60c5\u51b5\u662f\u76f4\u63a5\u63d2\u5165\u5230\u65b0\u7533\u8bf7\u9875\u9762\uff0c\u4e5f\u53ef\u80fd\u4f1a\u6d89\u53ca\u5230\u5df2\u5b58\u5728\u8bb0\u5f55\u79fb\u52a8\u5230\u65b0\u9875\u9762\uff0c\u6709\u6709\u4e9b\u7279\u6b8a\u60c5\u51b5\u4e0b\u8fd8\u4f1a\u76f4\u63a5\u63d2\u5165\u8001\u7684\u9875\u9762\uff08\u8001\u9875\u9762\u7684\u8bb0\u5f55\u88ab\u79fb\u52a8\u5230\u65b0\u9875\u9762\uff09\u3002<\/li>\n<\/ul>\n<p><strong>\u8868\u7a7a\u95f4\u7ba1\u7406<\/strong><\/p>\n<p>InnoDB\u7684B+tree\u662f\u901a\u8fc7\u591a\u5c42\u7ed3\u6784\u6620\u5c04\u5728\u78c1\u76d8\u4e0a\u7684\uff0c\u4ece\u5b83\u7684\u903b\u8f91\u5b58\u50a8\u7ed3\u6784\u6765\u770b\uff0c\u6240\u6709\u6570\u636e\u90fd\u88ab\u6709\u903b\u8f91\u5730\u5b58\u653e\u5728\u4e00\u4e2a\u7a7a\u95f4\u4e2d\uff0c\u8fd9\u4e2a\u7a7a\u95f4\u5c31\u53eb\u505a\u8868\u7a7a\u95f4\uff08tablespace\uff09\u3002\u8868\u7a7a\u95f4\u7531\u6bb5\uff08segment\uff09\u3001\u533a\uff08extent\uff09\u3001\u9875\uff08page\uff09\u7ec4\u6210\uff0c\u641e\u8fd9\u4e48\u591a\u624b\u6bb5\u7684\u552f\u4e00\u76ee\u7684\u5c31\u662f\u4e3a\u4e86\u964d\u4f4eIO\u7684\u968f\u673a\u6027\uff0c\u4fdd\u8bc1\u5b58\u50a8\u7269\u7406\u4e0a\u5c3d\u53ef\u80fd\u662f\u987a\u5e8f\u7684\u3002<\/p>\n<h2>\u4e09\u3001\u5f53\u524dDDL\u53d8\u66f4\u673a\u5236<\/h2>\n<p>\u5728\u6574\u4e2a\u6570\u636e\u5e93\u5e73\u53f0\uff08OneDBA\uff09\u6784\u5efa\u8fc7\u7a0b\u4e2d\uff0cMySQL \u7ed3\u6784\u53d8\u66f4\u6a21\u5757\u662f\u6838\u5fc3\u57fa\u7840\u80fd\u529b\uff0c\u4e5f\u662f\u7814\u53d1\u540c\u5b66\u5728\u65e5\u5e38\u4e1a\u52a1\u8fed\u4ee3\u8fc7\u7a0b\u4e2d\u4f7f\u7528\u9891\u7387\u8f83\u9ad8\u7684\u529f\u80fd\u4e4b\u4e00\u3002<\/p>\n<p>\u4e3b\u8981\u56f4\u7ed5\u5bf9\u8868\u52a0\u5b57\u6bb5\u3001\u52a0\u7d22\u5f15\u3001\u6539\u5c5e\u6027\u7b49\u64cd\u4f5c\uff0c\u4e3a\u4e86\u51cf\u5c11\u8fd9\u4e9b\u64cd\u4f5c\u5bf9\u7ebf\u4e0a\u6570\u636e\u5e93\u6216\u4e1a\u52a1\u7684\u5f71\u54cd\uff0c\u65e9\u671f\u4fbf\u4e3a MySQL \u7ed3\u6784\u53d8\u66f4\u5f00\u53d1\u4e86\u4e00\u5957\u57fa\u4e8e\u5bb9\u5668\u8fd0\u884c\u7684\u65e0\u9501\u53d8\u66f4\u7a0b\u5e8f\uff0c\u6838\u5fc3\u91c7\u7528\u7684\u662f\u5168\u91cf\u6570\u636e\u590d\u5236+\u589e\u91cf binlog \u56de\u653e\u6765\u8fdb\u884c\u53d8\u66f4\uff0c\u4e5f\u662f\u4e1a\u754c\u901a\u7528\u505a\u6cd5\uff08\u5185\u90e8\u4ee3\u53f7\uff1adw-osc\uff0c\u57fa\u4e8e GitHub \u5f00\u6e90\u7684 ghost \u5de5\u5177\u4e8c\u6b21\u5f00\u53d1\uff09\uff0c\u4e3b\u8981\u89e3\u51b3\u7684\u6838\u5fc3\u95ee\u9898\uff1a<\/p>\n<ul>\n<li>\u5b9e\u73b0\u65e0\u9501\u5316\u7684\u7ed3\u6784\u53d8\u66f4\uff0c\u53d8\u66f4\u8fc7\u7a0b\u4e2d\u4e0d\u4f1a\u963b\u6321\u4e1a\u52a1\u5bf9\u8868\u7684\u8bfb\u5199\u64cd\u4f5c\u3002<\/li>\n<li>\u5b9e\u73b0\u53d8\u66f4\u4e0d\u4f1a\u5bfc\u81f4\u8f83\u5927\u4e3b\u4ece\u6570\u636e\u5ef6\u8fdf\uff0c\u907f\u514d\u4e1a\u52a1\u4ece\u5e93\u8bfb\u53d6\u4e0d\u5230\u6570\u636e\u5bfc\u81f4\u4e1a\u52a1\u6545\u969c\u3002<\/li>\n<li>\u5b9e\u73b0\u540c\u65f6\u652f\u6301\u5927\u89c4\u6a21\u4efb\u52a1\u53d8\u66f4\uff0c\u4f7f\u7528\u5bb9\u5668\u5b9e\u73b0\u4f7f\u7528\u5b8c\u5373\u9500\u6bc1\uff0c\u65e0\u53d8\u66f4\u4efb\u52a1\u65f6\u4e0d\u5360\u7528\u8d44\u6e90\u3002<\/li>\n<\/ul>\n<p>\u53d8\u66f4\u5de5\u5177\u5de5\u4f5c\u539f\u7406\u7b80\u5355\u63cf\u8ff0 <strong>\uff08\u91cd\u8981\uff09<\/strong> \uff1a<\/p>\n<p><img decoding=\"async\" width=\"529\" height=\"608\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwdd\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<p><strong>\u91cd\u70b9\uff1a<\/strong><\/p>\n<p>\u7b80\u5355\u7406\u89e3\u5de5\u5177\u8fdb\u884c DDL \u53d8\u66f4\u8fc7\u7a0b\u4e2d\u4e3a\u4e86\u4fdd\u8bc1\u6570\u636e\u4e00\u81f4\u6027\uff0c\u5bf9\u4e8e\u5168\u91cf\u6570\u636e\u7684\u590d\u5236\u4e0e binlog \u56de\u653e\u662f\u5e76\u884c\u4ea4\u53c9\u5904\u7406\uff0c\u8fd9\u79cd\u673a\u5236\u5b83\u6709\u4e00\u4e2a\u7279\u70b9\u5c31\u662f\u3010\u7b2c\u4e09\u6b65\u3011\u4f1a\u5bfc\u81f4\u65b0\u63d2\u5165\u7684\u8bb0\u5f55\u53ef\u80fd\u4f1a\u5148\u5199\u5165\u5230\u8868\u4e2d\uff08\u4e3b\u952e ID \u5927\u7684\u8bb0\u5f55\u5148\u5199\u5165\u5230\u4e86\u8868\uff09\uff0c\u7136\u540e\u3010\u7b2c\u4e8c\u6b65\u3011\u4e2d\u590d\u5236\u6570\u636e\u540e\u5199\u5165\u5230\u8868\u4e2d\uff08\u4e3b\u952e ID \u5c0f\u7684\u8bb0\u5f55\u540e\u5199\u5165\u8868\uff09\u3002<\/p>\n<p>\u8fd9\u91cc\u987a\u4fbf\u8bf4\u4e00\u4e0b\u5f53\u524d\u5f97\u7269\u7ed3\u6784\u53d8\u66f4\u6574\u4f53\u67b6\u6784\uff1a\u7531\u4e8e\u53d8\u66f4\u5de5\u5177\u7684\u5de5\u4f5c\u539f\u7406\u9700\u6d88\u8d39\u5927\u91cf binlog \u65e5\u5fd7\u4fdd\u8bc1\u6570\u636e\u4e00\u81f4\u6027\uff0c\u4f1a\u5bfc\u81f4\u5728\u53d8\u66f4\u8fc7\u7a0b\u4e2d\u4f1a\u6709\u5927\u91cf\u7684\u5e26\u5bbd\u5360\u7528\u95ee\u9898\uff0c\u4e3a\u4e86\u6d88\u9664\u5e26\u5bbd\u5360\u7528\u95ee\u9898\uff0c\u5f00\u53d1\u4e86 Proxy \u4ee3\u7406\u7a0b\u5e8f\uff0c\u5728\u6b64\u57fa\u7840\u4e4b\u4e0a\u652f\u6301\u4e86\u591a\u4e91\u5546\u3001\u591a\u533a\u57df\u672c\u5730\u5316\u53d8\u66f4\u3002<\/p>\n<p>\u76ee\u524d\u6574\u4f53\u67b6\u6784\u56fe\u5982\u4e0b\uff1a<\/p>\n<p><img decoding=\"async\" width=\"723\" height=\"363\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwc6\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<h2>\u56db\u3001\u53d8\u66f4\u540e\uff0c\u8868\u4e3a\u4ec0\u4e48\u81a8\u80c0\uff1f<\/h2>\n<p><strong>\u539f\u56e0\u8bf4\u660e<\/strong><\/p>\n<p>\u4e0a\u9762\u51e0\u4e2a\u5173\u952e\u70b9\u94fa\u57ab\u5b8c\u4e86\uff0c\u56de\u5230\u7b2c\u4e00\u4e2a\u95ee\u9898\uff0c\u8fd9\u91cc\u5148\u76f4\u63a5\u8bf4\u660e\u6839\u672c\u539f\u56e0\uff0c\u540e\u9762\u4f1a\u9610\u8ff0\u4e00\u4e0b\u6392\u67e5\u8fc7\u7a0b\uff08\u6709\u540c\u5b66\u611f\u5174\u8da3\u6240\u4ee5\u5206\u4eab\u4e00\u4e0b\uff0c\u6574\u4e2a\u8fc7\u7a0b\u8fd8\u662f\u8017\u8d39\u4e0d\u5c11\u65f6\u95f4\uff09\u3002<\/p>\n<p>\u5728\u300e\u7ed3\u6784\u53d8\u66f4\u673a\u5236\u300f\u4ecb\u7ecd\u4e2d\uff0c\u6211\u4eec\u53d1\u73b0\u8fd9\u79cd\u53d8\u66f4\u673a\u5236\u5b83\u6709\u4e00\u4e2a\u7279\u70b9\uff0c\u5c31\u662f\u3010\u7b2c\u4e09\u6b65\u3011\u4f1a\u5bfc\u81f4\u65b0\u63d2\u5165\u7684\u8bb0\u5f55\u53ef\u80fd\u4f1a\u5148\u5199\u5165\u5230\u8868\u4e2d\uff08\u4e3b\u952e ID \u5927\u7684\u8bb0\u5f55\u5148\u5199\u5165\u5230\u4e86\u8868\uff09\uff0c\u7136\u540e\u3010\u7b2c\u4e8c\u6b65\u3011\u4e2d\u590d\u5236\u6570\u636e\u540e\u5199\u5165\u5230\u8868\u4e2d\uff08\u4e3b\u952e ID \u5c0f\u7684\u8bb0\u5f55\uff09\u3002\u8fd9\u79cd\u5199\u5165\u7279\u6027\u53e0\u52a0\u5355\u884c\u8bb0\u5f55\u8fc7\u5927\u7684\u65f6\u5019\uff08\u4e1a\u52a1\u8868\u5355\u884c\u8bb0\u5f55\u5927\u5c0f 5k \u5de6\u53f3\uff09\uff0c\u4f1a\u78b0\u5230 MySQL \u9875\u5206\u88c2\u7684\u4e00\u4e2a\u7455\u75b5\uff08\u6682\u4e14\u79f0\u4e4b\u4e3a\u7455\u75b5\uff0c\u6216\u8bb8\u662f\u4e00\u4e2a Bug\uff09\uff0c\u5bfc\u81f4\u4e86\u4e00\u4e2a\u9875\u53ea\u5b58\u50a8\u4e86 1 \u6761\u8bb0\u5f55\uff0816k \u7684\u9875\u53ea\u5b58\u50a8\u4e86 5k\uff0c\u6d6a\u8d39 2\/3 \u7a7a\u95f4\uff09\uff0c\u653e\u5927\u4e86\u5b58\u50a8\u95ee\u9898\u3002<\/p>\n<p><strong>\u6d41\u7a0b\u590d\u73b0<\/strong><\/p>\n<p>\u4e0b\u9762\u76f4\u63a5\u590d\u73b0\u4e00\u4e0b\u8fd9\u79cd\u73b0\u8c61\u4e0b\u5bfc\u81f4\u5f02\u5e38\u9875\u5206\u88c2\u7684\u8fc7\u7a0b\uff1a<\/p>\n<pre><code>CREATE TABLE `sbtest` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `pad` varchar(12000),\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB;\n<\/code><\/pre>\n<p>\u7136\u540e\u63d2\u5165\u4e24\u884c 5k \u5927\u5c0f\u7684\u5927\u4e3b\u952e\u8bb0\u5f55\uff08\u6a21\u62df\u53d8\u66f4\u65f6 binlog \u56de\u653e\u5148\u63d2\u5165\u6570\u636e\uff09\uff1a<\/p>\n<pre><code>insert into sbtest values (10000, repeat('a',5120));\ninsert into sbtest values (10001, repeat('a',5120));\n<\/code><\/pre>\n<p>\u8fd9\u91cc\u5199\u4e86\u4e00\u4e2a\u5c0f\u5de5\u5177\u6253\u5370\u8bb0\u5f55\u5bf9\u5e94\u7684 page \u53f7\u548c heap \u53f7\u3002<\/p>\n<pre><code># .\/peng\n[pk:10000] page: 3 -&gt; heap: 2\n[pk:10001] page: 3 -&gt; heap: 3\n<\/code><\/pre>\n<p>\u53ef\u4ee5\u770b\u5230\u4e24\u6761\u8bb0\u5f55\u90fd\u5b58\u5728 3 \u53f7\u9875\uff0c\u6b64\u65f6\u8868\u53ea\u6709\u8fd9\u4e00\u4e2a\u9875\u3002<\/p>\n<p>\u7ee7\u7eed\u5f00\u59cb\u987a\u5e8f\u63d2\u5165\u6570\u636e\uff08\u6a21\u62df\u53d8\u66f4\u65f6 copy \u5168\u91cf\u6570\u636e\u8fc7\u7a0b\uff09\uff0c\u63d2\u5165 rec-1\uff1a<\/p>\n<pre><code>insert into sbtest values (1, repeat('a',5120));\n<\/code><\/pre>\n<p>&lt;!&#8212;-&gt;<\/p>\n<pre><code># .\/peng\n[pk:1] page: 3 -&gt; heap: 4\n[pk:10000] page: 3 -&gt; heap: 2\n[pk:10001] page: 3 -&gt; heap: 3\n<\/code><\/pre>\n<p>\u63d2\u5165 rec-2\uff1a<\/p>\n<pre><code>insert into sbtest values (2, repeat('a',5120));\n<\/code><\/pre>\n<p>&lt;!&#8212;-&gt;<\/p>\n<pre><code># .\/peng\n[pk:1] page: 4 -&gt; heap: 2\n[pk:2] page: 4 -&gt; heap: 3\n[pk:10000] page: 5 -&gt; heap: 2\n[pk:10001] page: 5 -&gt; heap: 3\n<\/code><\/pre>\n<p>\u53ef\u4ee5\u770b\u5230\u5f00\u59cb\u5206\u88c2\u4e86\uff0cpage 3 \u88ab\u63d0\u5347\u4e3a\u6839\u8282\u70b9\u4e86\uff0c\u540c\u65f6\u5206\u88c2\u51fa\u4e24\u4e2a\u53f6\u5b50\u8282\u70b9\uff0c\u5404\u81ea\u5b58\u4e86\u4e24\u6761\u6570\u636e\u3002\u6b64\u65f6\u5df2\u7ecf\u5f62\u6210\u4e86\u4e00\u68f5 2 \u5c42\u9ad8\u7684\u6811\uff0c\u8fd8\u662f\u7528\u56fe\u8868\u793a\u5427\uff0c\u6bd4\u8f83\u76f4\u89c2\uff0c\u5982\u4e0b\uff1a<\/p>\n<p><img decoding=\"async\" width=\"723\" height=\"346\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwde\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<p>\u63d2\u5165 rec-3\uff1a<\/p>\n<pre><code>insert into sbtest values (3, repeat('a',5120));\n<\/code><\/pre>\n<p>&lt;!&#8212;-&gt;<\/p>\n<pre><code># .\/peng\n[pk:1] page: 4 -&gt; heap: 2\n[pk:2] page: 4 -&gt; heap: 3\n[pk:3] page: 5 -&gt; heap: 4\n[pk:10000] page: 5 -&gt; heap: 2\n[pk:10001] page: 5 -&gt; heap: 3\n<\/code><\/pre>\n<p>\u793a\u610f\u56fe\u5982\u4e0b\uff1a<\/p>\n<p><img decoding=\"async\" width=\"723\" height=\"351\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwdr\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<p>\u63d2\u5165 rec-4\uff1a<\/p>\n<pre><code>insert into sbtest values (4, repeat('a',5120));\n<\/code><\/pre>\n<p>&lt;!&#8212;-&gt;<\/p>\n<pre><code># .\/peng\n[pk:1] page: 4 -&gt; heap: 2\n[pk:2] page: 4 -&gt; heap: 3\n[pk:3] page: 5 -&gt; heap: 4\n[pk:4] page: 5 -&gt; heap: 3\n[pk:10000] page: 5 -&gt; heap: 2\n[pk:10001] page: 6 -&gt; heap: 2\n<\/code><\/pre>\n<p>\u8fd9\u91cc\u5f00\u59cb\u5206\u88c2\u4e00\u4e2a\u65b0\u9875 page 6\uff0c\u5f00\u59cb\u51fa\u73b0\u6bd4\u8f83\u590d\u6742\u7684\u60c5\u51b5\uff0c\u540c\u65f6\u4e5f\u4e3a\u540e\u9762\u5206\u88c2\u5bfc\u81f4\u4e00\u4e2a\u9875\u53ea\u6709 1 \u6761\u6570\u636e\u57cb\u4e0b\u4f0f\u7b14\uff1a<\/p>\n<p><img decoding=\"async\" width=\"723\" height=\"251\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwds\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<p>\u8fd9\u91cc\u53ef\u4ee5\u770b\u5230\u628a 10001 \u8fd9\u6761\u8bb0\u5f55\u4ece page 5 \u4e0a\u9762\u8fc1\u79fb\u5230\u4e86\u65b0\u5efa\u7684 page 6 \u4e0a\u9762\uff08\u8001\u7684 page 5 \u4e2d\u4f1a\u5220\u9664 10001 \u8fd9\u6761\u8bb0\u5f55\uff0c\u5e76\u653e\u5165\u5230\u5220\u9664\u94fe\u8868\u4e2d\uff09\uff0c\u800c\u628a\u5f53\u524d\u63d2\u5165\u7684 rec-4 \u63d2\u5165\u5230\u4e86\u539f\u6765\u7684 page 5 \u4e0a\u9762\uff0c\u8fd9\u4e2a\u5904\u7406\u903b\u8f91\u5728\u4ee3\u7801\u4e2d\u662f\u4e00\u4e2a\u7279\u6b8a\u5904\u7406\uff0c\u5411\u53f3\u5206\u88c2\u65f6\uff0c\u5f53\u63d2\u5165\u70b9\u9875\u9762\u524d\u9762\u6709\u5927\u4e8e\u7b49\u4e8e\u4e24\u6761\u8bb0\u5f55\u65f6\uff0c\u4f1a\u8bbe\u7f6e\u5206\u88c2\u8bb0\u5f55\u4e3a 10001\uff0c\u6240\u4ee5\u628a\u5b83\u8fc1\u79fb\u5230\u4e86 page 6\uff0c\u540c\u65f6\u4f1a\u628a\u5f53\u524d\u63d2\u5165\u8bb0\u5f55\u63d2\u5165\u5230\u539f page 5\u3002\u5177\u4f53\u53ef\u4ee5\u770b btr\/_page\/_get\/_split\/_rec\/_to\/_right \u51fd\u6570\u3002<\/p>\n<pre><code>\/* \u8fd9\u91cc\u8fd4\u56detrue\u8868\u793a\u5c06\u884c\u8bb0\u5f55\u5411\u53f3\u5206\u88c2\uff1a\u5373\u5206\u914d\u7684\u65b0page\u7684hint_page_no\u4e3a\u539fpage+1 *\/\nibool btr_page_get_split_rec_to_right(\n\/*============================*\/\n        btr_cur_t*        cursor,\n        rec_t**           split_rec)\n{\n  page_t*        page;\n  rec_t*        insert_point;\n  \n  \/\/ \u83b7\u53d6\u5f53\u524d\u6e38\u6807\u9875\u548cinsert_point\n  page = btr_cur_get_page(cursor);\n  insert_point = btr_cur_get_rec(cursor);\n  \n  \/* \u4f7f\u7528\u542f\u53d1\u5f0f\u65b9\u6cd5\uff1a\u5982\u679c\u65b0\u7684\u63d2\u5165\u64cd\u4f5c\u7d27\u8ddf\u5728\u540c\u4e00\u9875\u9762\u4e0a\u7684\u524d\u4e00\u4e2a\u63d2\u5165\u64cd\u4f5c\u4e4b\u540e\uff0c\n     \u6211\u4eec\u5047\u8bbe\u8fd9\u91cc\u5b58\u5728\u4e00\u4e2a\u987a\u5e8f\u63d2\u5165\u7684\u6a21\u5f0f\u3002 *\/\n  \n  \/\/ PAGE_LAST_INSERT\u4ee3\u8868\u4e0a\u6b21\u63d2\u5165\u4f4d\u7f6e\uff0cinsert_point\u4ee3\u8868\u5c0f\u4e8e\u7b49\u4e8e\u5f85\u63d2\u5165\u76ee\u6807\u8bb0\u5f55\u7684\u6700\u5927\u8bb0\u5f55\u4f4d\u7f6e\n  \/\/ \u5982\u679cPAGE_LAST_INSERT=insert_point\u610f\u5473\u7740\u672c\u6b21\u5f85\u63d2\u5165\u7684\u8bb0\u5f55\u662f\u7d27\u63a5\u7740\u4e0a\u6b21\u5df2\u63d2\u5165\u7684\u8bb0\u5f55\uff0c\n  \/\/ \u8fd9\u662f\u4e00\u79cd\u987a\u5e8f\u63d2\u5165\u6a21\u5f0f\uff0c\u4e00\u65e6\u5224\u5b9a\u662f\u987a\u5e8f\u63d2\u5165\uff0c\u5fc5\u7136\u53cd\u56detrue\uff0c\u5411\u53f3\u5206\u88c2\n  if (page_header_get_ptr(page, PAGE_LAST_INSERT) == insert_point) {\n    \/\/ 1. \u83b7\u53d6\u5f53\u524dinsert_point\u7684page\u5185\u7684\u4e0b\u4e00\u6761\u8bb0\u5f55\uff0c\u5e76\u5224\u65ad\u662f\u5426\u662fsupremum\u8bb0\u5f55\n    \/\/ 2. \u5982\u679c\u4e0d\u662f\uff0c\u7ee7\u7eed\u5224\u65ad\u5f53\u524dinsert_point\u7684\u4e0b\u4e0b\u6761\u8bb0\u5f55\u662f\u5426\u662fsupremum\u8bb0\u5f55\n    \/\/ \u4e5f\u5c31\u662f\u8bf4\uff0c\u4f1a\u5411\u540e\u770b\u4e24\u6761\u8bb0\u5f55\uff0c\u8fd9\u4e24\u6761\u8bb0\u5f55\u6709\u4e00\u6761\u4e3asupremum\u8bb0\u5f55\uff0c\n    \/\/ split_rec\u90fd\u4f1a\u88ab\u8bbe\u7f6e\u4e3aNULL\uff0c\u5411\u53f3\u5206\u88c2\n    rec_t*        next_rec;\n    next_rec = page_rec_get_next(insert_point);\n    \n    if (page_rec_is_supremum(next_rec)) {\n    split_at_new:\n      \/* split_rec\u4e3aNULL\u8868\u793a\u4ece\u65b0\u63d2\u5165\u7684\u8bb0\u5f55\u5f00\u59cb\u5206\u88c2\uff0c\u63d2\u5165\u5230\u65b0\u9875 *\/\n      *split_rec = nullptr;\n    } else {\n      rec_t* next_next_rec = page_rec_get_next(next_rec);\n      if (page_rec_is_supremum(next_next_rec)) {\n        goto split_at_new;\n      }\n      \n      \/* \u5982\u679c\u4e0d\u662fsupremum\u8bb0\u5f55\uff0c\u5219\u8bbe\u7f6e\u62c6\u5206\u8bb0\u5f55\u4e3a\u4e0b\u4e0b\u6761\u8bb0\u5f55 *\/\n\n\n      \/* \u8fd9\u6837\u505a\u7684\u76ee\u7684\u662f\uff0c\u5982\u679c\u4ece\u63d2\u5165\u70b9\u5f00\u59cb\u5411\u4e0a\u6709 &gt;= 2 \u6761\u7528\u6237\u8bb0\u5f55\uff0c\n         \u6211\u4eec\u5728\u8be5\u9875\u4e0a\u4fdd\u7559 1 \u6761\u8bb0\u5f55\uff0c\u56e0\u4e3a\u8fd9\u6837\u540e\u9762\u7684\u987a\u5e8f\u63d2\u5165\u5c31\u53ef\u4ee5\u4f7f\u7528\n         \u81ea\u9002\u5e94\u54c8\u5e0c\u7d22\u5f15\uff0c\u56e0\u4e3a\u5b83\u4eec\u53ea\u9700\u67e5\u770b\u6b64\u9875\u9762\u4e0a\u7684\u8bb0\u5f55\u5373\u53ef\u5bf9\u6b63\u786e\u7684\n         \u641c\u7d22\u4f4d\u7f6e\u8fdb\u884c\u5fc5\u8981\u7684\u68c0\u67e5 *\/\n      \n      *split_rec = next_next_rec;\n    }\n    \n    return true;\n  }\n  \n  return false;\n}\n<\/code><\/pre>\n<p>\u63d2\u5165 rec-5\uff1a<\/p>\n<pre><code>insert into sbtest values (5, repeat('a',5120));\n<\/code><\/pre>\n<p>&lt;!&#8212;-&gt;<\/p>\n<pre><code># .\/peng\n[pk:1] page: 4 -&gt; heap: 2\n[pk:2] page: 4 -&gt; heap: 3\n[pk:3] page: 5 -&gt; heap: 4\n[pk:4] page: 5 -&gt; heap: 3\n[pk:5] page: 7 -&gt; heap: 3\n[pk:10000] page: 7 -&gt; heap: 2\n[pk:10001] page: 6 -&gt; heap: 2\n<\/code><\/pre>\n<p>\u5f00\u59cb\u5206\u88c2\u4e00\u4e2a\u65b0\u9875 page 7\uff0c\u65b0\u7684\u7ec4\u7ec7\u7ed3\u6784\u65b9\u5f0f\u5982\u4e0b\u56fe\uff1a<\/p>\n<p><img decoding=\"async\" width=\"723\" height=\"232\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwdt\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<p>\u6b64\u65f6\u662f\u4e00\u4e2a\u6b63\u5e38\u7684\u63d2\u5165\u70b9\u53f3\u5206\u88c2\u673a\u5236\uff0c\u628a\u8001\u7684 page 5 \u4e2d\u7684\u8bb0\u5f55 10000 \u90fd\u79fb\u52a8\u5230\u4e86 page 7\uff0c\u5e76\u4e14\u65b0\u63d2\u5165\u7684 rec-5 \u4e5f\u5199\u5165\u5230\u4e86 page 7 \u4e2d\u3002\u5230\u6b64\u65f6\u770b\u4e0a\u53bb\u4e00\u5207\u6b63\u5e38\uff0c\u63a5\u4e0b\u6765\u518d\u63d2\u5165\u8bb0\u5f55\u5728\u5f53\u524d\u8fd9\u79cd\u7ed3\u6784\u4e0b\u5c31\u4f1a\u4ea7\u751f\u5f02\u5e38\u3002<\/p>\n<p>\u63d2\u5165 rec-6\uff1a<\/p>\n<pre><code>insert into sbtest values (5, repeat('a',5120));\n<\/code><\/pre>\n<p>&lt;!&#8212;-&gt;<\/p>\n<pre><code># .\/peng\n[pk:1] page: 4 -&gt; heap: 2\n[pk:2] page: 4 -&gt; heap: 3\n[pk:3] page: 5 -&gt; heap: 4\n[pk:4] page: 5 -&gt; heap: 3\n[pk:5] page: 7 -&gt; heap: 3\n[pk:6] page: 8 -&gt; heap: 3\n[pk:10000] page: 8 -&gt; heap: 2\n[pk:10001] page: 6 -&gt; heap: 2\n<\/code><\/pre>\n<p><img decoding=\"async\" width=\"723\" height=\"213\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwdv\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<p>\u6b64\u65f6\u4e5f\u662f\u4e00\u4e2a\u6b63\u5e38\u7684\u63d2\u5165\u70b9\u53f3\u5206\u88c2\u673a\u5236\uff0c\u628a\u8001\u7684 page 7 \u4e2d\u7684\u8bb0\u5f55 10000 \u90fd\u79fb\u52a8\u5230\u4e86 page 8\uff0c\u5e76\u4e14\u65b0\u63d2\u5165\u7684 rec-6 \u4e5f\u5199\u5165\u5230\u4e86 page 8 \u4e2d\uff0c\u4f46\u662f\u6211\u4eec\u53ef\u4ee5\u53d1\u73b0 page 7 \u4e2d\u53ea\u6709\u4e00\u6761\u5b64\u96f6\u96f6\u7684 rec-5 \u4e86\uff0c\u4e00\u4e2a\u9875\u53ea\u5b58\u50a8\u4e86\u4e00\u6761\u8bb0\u5f55\u3002<\/p>\n<p>\u6309\u7167\u4ee3\u7801\u4e2d\u6b63\u5e38\u7684\u63d2\u5165\u70b9\u53f3\u5206\u88c2\u673a\u5236\uff0c\u7ee7\u7eed\u63d2\u5165 rec-7 \u4f1a\u5bfc\u81f4 rec-6 \u6210\u4e3a\u4e00\u4e2a\u5355\u9875\u3001\u63d2\u5165 rec-8 \u53c8\u4f1a\u5bfc\u81f4 rec-7 \u6210\u4e3a\u4e00\u4e2a\u5355\u9875\uff0c\u4e00\u76f4\u8fd9\u6837\u5faa\u73af\u4e0b\u53bb\u3002<\/p>\n<p>\u76ee\u524d\u6765\u770b\u5c31\u662f\u5728\u63d2\u5165 rec-4\uff0c\u89e6\u53d1\u4e86\u4e00\u4e2a\u5185\u90e8\u4f18\u5316\u7b56\u7565\uff08\u5177\u4f53\u4f18\u5316\u6ca1\u592a\u53bb\u7814\u7a76\uff09\uff0c\u8fdb\u884c\u4e86\u4e00\u4e9b\u7279\u6b8a\u7684\u8bb0\u5f55\u8fc1\u79fb\u548c\u63d2\u5165\u52a8\u4f5c\uff0c\u5f53\u7136\u8ddf\u8bb0\u5f55\u8fc7\u5927\u4e5f\u6709\u5f88\u5927\u5173\u7cfb\u3002<\/p>\n<p><strong>\u6392\u67e5\u8fc7\u7a0b<\/strong><\/p>\n<p>\u6709\u540c\u5b66\u5bf9\u8fd9\u4e2a\u95ee\u9898\u6392\u67e5\u8fc7\u7a0b\u6bd4\u8f83\u611f\u5174\u8da3\uff0c\u6240\u4ee5\u8fd9\u91cc\u4e5f\u6574\u7406\u5206\u4eab\u4e00\u4e0b\uff0c\u7b80\u5316\u4e86\u4e00\u4e9b\u65e0\u7528\u4fe1\u606f\uff0c\u4ec5\u4f9b\u53c2\u8003\u3002<\/p>\n<p>\u8868\u603b\u884c\u6570\u5728 400 \u767e\u4e07\uff0c\u6b63\u5e38\u60c5\u51b5\u4e0b\u7684\u5927\u5c0f\u5728 33G \u5de6\u53f3\uff0c\u53d8\u66f4\u4e4b\u540e\u7684\u5927\u5c0f\u5728 67G \u5de6\u53f3\u3002<\/p>\n<ul>\n<li>\u9996\u5148\u6839\u636e\u5907\u4efd\u6062\u590d\u4e86\u4e00\u4e2a\u6570\u636e\u5e93\u73b0\u573a\u51fa\u6765\u3002<\/li>\n<li>\u7edf\u8ba1\u4e86\u4e1a\u52a1\u8868\u884c\u5927\u5c0f\uff0c\u53d1\u73b0\u884c\u57fa\u672c\u504f\u5927\uff0c\u5728 4-7k \u4e4b\u95f4\uff08\u4e00\u4e2a\u9875\u53ea\u5b58\u4e862\u884c\uff0c\u6d6a\u8d391\/3\u7a7a\u95f4\uff09\u3002<\/li>\n<li>\u5206\u6790\u4e86\u53d8\u66f4\u524d\u540e\u7684\u8868\u6570\u636e\u9875\uff0c\u4ee5\u53ca\u6bcf\u4e2a\u9875\u5b58\u50a8\u591a\u5c11\u884c\u6570\u636e\u3002<\/li>\n<li>\n<ul>\n<li>\u53d1\u73b0\u53d8\u66f4\u4e4b\u524d\u6570\u636e\u9875\u5927\u6982 200 \u767e\u4e07\uff0c\u53d8\u66f4\u4e4b\u540e 400 \u767e\u4e07\uff0c\u89e3\u91ca\u4e86\u5b58\u50a8\u7ffb\u500d\u3002<\/li>\n<li>\u53d1\u73b0\u53d8\u66f4\u4e4b\u524d\u5b58\u50a8 1 \u884c\u7684\u9875\u57fa\u672c\u6ca1\u6709\uff0c\u53d8\u66f4\u4e4b\u540e\u5b58\u50a8 1 \u884c\u7684\u9875\u63a5\u8fd1 400 \u767e\u4e07\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>\u57fa\u4e8e\u73b0\u5728\u8fd9\u4e9b\u4fe1\u606f\u6211\u4eec\u77e5\u9053\u4e86\u5b58\u50a8\u7ffb\u500d\u7684\u6839\u672c\u539f\u56e0\uff0c\u5c31\u662f\u4e4b\u524d\u4e00\u4e2a\u9875\u5b58\u50a8 2 \u6761\u8bb0\u5f55\uff0c\u73b0\u5728\u4e00\u4e2a\u9875\u53ea\u5b58\u50a8\u4e86 1 \u6761\u8bb0\u5f55\uff0c\u65b0\u7684\u95ee\u9898\u6765\u4e86\uff0c\u4e3a\u4ec0\u4e48\u53d8\u66f4\u540e\u4f1a\u5b58\u50a8 1 \u6761\u8bb0\u5f55\uff0c\u7ee7\u7eed\u5bfb\u627e\u7b54\u6848\u3002<\/p>\n<ul>\n<li>\u6211\u4eec\u9996\u5148\u5728\u5907\u4efd\u6062\u590d\u7684\u5b9e\u4f8b\u4e0a\u9762\u8fdb\u884c\u4e86\u4e00\u6b21\u9759\u6001\u53d8\u66f4\uff0c\u5c31\u662f\u53d8\u66f4\u671f\u95f4\u6ca1\u6709\u65b0\u7684 DML \u64cd\u4f5c\uff0c\u6ca1\u6709\u590d\u73b0\u3002\u4f46\u8bf4\u660e\u4e86\u4e00\u4e2a\u95ee\u9898\uff0c\u5f02\u5e38\u8ddf\u589e\u91cf\u6709\u5173\uff0c\u6b64\u65f6\u5927\u6982\u77e5\u9053\u8ddf\u53d8\u66f4\u8fc7\u7a0b\u4e2d\u7684 binlog \u56de\u653e\u7279\u6027\u6709\u5173\u3010\u4e0a\u9762\u8bf4\u7684\u56de\u653e\u4f1a\u5bfc\u81f4\u4e3b\u952e ID \u5927\u7684\u8bb0\u5f55\u5148\u5199\u5165\u8868\u4e2d\u3011\u3002<\/li>\n<li>\u5199\u4e86\u4e2a\u5de5\u5177\u628a 400 \u767e\u4e07\u6570\u636e\u6bcf\u6761\u8bb0\u5f55\u5206\u5e03\u5728\u54ea\u4e2a\u9875\u91cc\u9762\uff0c\u4ee5\u53ca\u9875\u91cc\u9762\u7684\u8bb0\u5f55\u5bf9\u5e94\u7684 heap \u662f\u4ec0\u4e48\u90fd\u8bb0\u5f55\u5230\u6570\u636e\u5e93\u8868\u4e2d\u5206\u6790\uff0c\u6162\u957f\u7b49\u5f85\u8dd1\u6570\u636e\u3002<\/li>\n<\/ul>\n<p><img decoding=\"async\" width=\"723\" height=\"377\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwdx\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<ul>\n<li>\u6570\u636e\u5206\u6790\u5b8c\u540e\u901a\u8fc7\u5206\u6790\u53d1\u73b0\u5b58\u50a8\u4e00\u6761\u6570\u636e\u7684\u9875\u5bf9\u5e94\u7684\u8bb0\u5f55\u7684 heap \u503c\u57fa\u672c\u90fd\u662f 3\uff0c\u6b63\u5e38\u5e94\u8be5\u662f 2\uff0c\u610f\u5473\u7740\u8fd9\u4e9b\u9875\u5e76\u4e0d\u662f\u4e00\u5f00\u59cb\u5c31\u5b58\u4e00\u6761\u6570\u636e\uff0c\u800c\u662f\u4ea7\u751f\u4e86\u9875\u5206\u88c2\u5bfc\u81f4\u7684\u3002<\/li>\n<li>\u5f00\u59cb\u7ee7\u7eed\u518d\u770b\u9875\u5206\u88c2\u76f8\u5173\u7684\u8d44\u6599\u548c\u4ee3\u7801\uff0c\u5217\u51fa\u9875\u5206\u88c2\u7684\u5404\u79cd\u60c5\u51b5\uff0c\u7ed3\u5408\u4e0a\u9762\u7684\u4fe1\u606f\u6784\u5efa\u4e86\u4e00\u4e2a\u590d\u73b0\u73af\u5883\u3002\u63d2\u5165\u6570\u636e\u9875\u5206\u88c2\u6838\u5fc3\u51fd\u6570\u3002<\/li>\n<li>\n<ul>\n<li>btr\/_cur\/_optimistic\/_insert\uff1a\u4e50\u89c2\u63d2\u5165\u6570\u636e\uff0c\u5f53\u524d\u9875\u76f4\u63a5\u5b58\u50a8<\/li>\n<li>btr\/_cur\/_pessimistic\/_insert\uff1a\u60b2\u89c2\u63d2\u5165\u6570\u636e\uff0c\u5f00\u59cb\u5206\u88c2\u9875<\/li>\n<li>btr\/_root\/_raise\/_and\/_insert\uff1a\u5355\u72ec\u5904\u7406\u6839\u8282\u70b9\u7684\u5206\u88c2<\/li>\n<li>btr\/_page\/_split\/_and\/_insert\uff1a\u5206\u88c2\u666e\u901a\u9875\uff0c\u6240\u6709\u6d41\u7a0b\u90fd\u5728\u8fd9\u4e2a\u51fd\u6570<\/li>\n<li>btr\/_page\/_get\/_split\/_rec\/_to\/_right\uff1a\u5224\u65ad\u662f\u5426\u662f\u5411\u53f3\u5206\u88c2<\/li>\n<li>btr\/_page\/_get\/_split\/_rec\/_to\/_left\uff1a\u5224\u65ad\u662f\u5426\u662f\u5411\u5de6\u5206\u88c2<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><strong>heap<\/strong><\/p>\n<p>heap \u662f\u9875\u91cc\u9762\u7684\u4e00\u4e2a\u6982\u5ff5\uff0c\u7528\u6765\u6807\u8bb0\u8bb0\u5f55\u5728\u9875\u91cc\u9762\u7684\u76f8\u5bf9\u4f4d\u7f6e\uff0c\u9875\u91cc\u9762\u7684\u7b2c\u4e00\u6761\u7528\u6237\u8bb0\u5f55\u4e00\u822c\u662f 2\uff0c\u800c 0 \u548c 1 \u9ed8\u8ba4\u5206\u914d\u7ed9\u4e86\u6700\u5927\u6700\u5c0f\u865a\u62df\u8bb0\u5f55\uff0c\u5728\u9875\u9762\u521b\u5efa\u7684\u65f6\u5019\u5c31\u521d\u59cb\u5316\u597d\u4e86\uff0c\u6700\u5927\u6700\u5c0f\u8bb0\u5f55\u4e0a\u9762\u6709\u7b80\u5355\u4ecb\u7ecd\u3002<\/p>\n<p><strong>\u89e3\u6790 ibd \u6587\u4ef6<\/strong><\/p>\n<p>\u66f4\u5feb\u7684\u65b9\u5f0f\u8fd8\u662f\u5e94\u8be5\u5206\u6790\u7269\u7406 ibd \u6587\u4ef6\uff0c\u80fd\u591f\u89e3\u6790\u51fa\u9875\u7684\u5177\u4f53\u6570\u636e\uff0c\u4ee5\u53ca\u88ab\u5206\u88c2\u5220\u9664\u7684\u6570\u636e\uff0c\u5206\u88c2\u5c31\u662f\u628a\u4e00\u4e2a\u9875\u91cc\u9762\u7684\u90e8\u5206\u8bb0\u5f55\u79fb\u52a8\u5230\u65b0\u7684\u9875\uff0c\u7136\u540e\u5220\u9664\u8001\u7684\u8bb0\u5f55\uff0c\u4f46\u4e0d\u4f1a\u771f\u6b63\u5220\u9664\uff0c\u800c\u662f\u79fb\u52a8\u5230\u9875\u91cc\u9762\u7684\u4e00\u4e2a\u5220\u9664\u94fe\u8868\uff0c\u540e\u9762\u53ef\u4ee5\u590d\u7528\u3002<\/p>\n<h2>\u4e94\u3001\u53d8\u66f4\u540e\uff0c\u7edf\u8ba1\u4fe1\u606f\u4e3a\u4ec0\u4e48\u5dee\u5f02\u5de8\u5927\uff1f<\/h2>\n<p>\u8868\u7edf\u8ba1\u4fe1\u606f\u4e3b\u8981\u6d89\u53ca\u7d22\u5f15\u57fa\u6570\u7edf\u8ba1\uff08\u4e5f\u5c31\u662f\u552f\u4e00\u503c\u7684\u6570\u91cf\uff09\uff0c\u4e3b\u952e\u7d22\u5f15\u7684\u57fa\u6570\u7edf\u8ba1\u4e5f\u5c31\u662f\u8868\u884c\u6570\uff0c\u5728\u4f18\u5316\u5668\u8fdb\u884c\u6210\u672c\u4f30\u7b97\u65f6\u6709\u4e9b SQL \u6761\u4ef6\u4f1a\u4f7f\u7528\u7d22\u5f15\u57fa\u6570\u8fdb\u884c\u6289\u62e9\u7d22\u5f15\u9009\u62e9\uff08\u5927\u90e8\u5206\u60c5\u51b5\u662f index dive \u65b9\u5f0f\u4f30\u7b97\u626b\u63cf\u884c\u6570\uff09\u3002<\/p>\n<p>InnoDB \u7edf\u8ba1\u4fe1\u606f\u6536\u96c6\u7b97\u6cd5\u7b80\u5355\u7406\u89e3\u5c31\u662f\u91c7\u6837\u53f6\u5b50\u8282\u70b9 N \u4e2a\u9875\uff08\u9ed8\u8ba4 20 \u4e2a\u9875\uff09\uff0c\u626b\u63cf\u7edf\u8ba1\u6bcf\u4e2a\u9875\u7684\u552f\u4e00\u503c\u6570\u91cf\uff0cN \u4e2a\u9875\u7684\u552f\u4e00\u503c\u6570\u91cf\u7d2f\u52a0\uff0c\u7136\u540e\u9664\u4ee5N\u5f97\u5230\u5355\u4e2a\u9875\u5e73\u5747\u552f\u4e00\u503c\u6570\u91cf\uff0c\u518d\u4e58\u4ee5\u8868\u7684\u603b\u9875\u9762\u6570\u91cf\u5c31\u4f30\u7b97\u51fa\u4e86\u7d22\u5f15\u603b\u7684\u552f\u4e00\u503c\u6570\u91cf\u3002<\/p>\n<p>\u4f46\u662f\u5f53\u4e00\u4e2a\u9875\u53ea\u6709 1 \u6761\u6570\u636e\u7684\u65f6\u5019\u7edf\u8ba1\u4fe1\u606f\u4f1a\u4ea7\u751f\u4e25\u91cd\u504f\u5dee\uff08\u4e0a\u9762\u5df2\u7ecf\u5206\u6790\u51fa\u4e86\u8868\u81a8\u80c0\u7684\u539f\u56e0\u5c31\u662f\u4e00\u4e2a\u9875\u53ea\u5b58\u50a8\u4e86 1 \u6761\u8bb0\u5f55\uff09\uff0c\u4e3b\u8981\u662f\u4ee3\u7801\u91cc\u9762\u6709\u4e2a\u4f18\u5316\u903b\u8f91\uff0c\u5bf9\u5355\u4e2a\u9875\u7684\u552f\u4e00\u503c\u8fdb\u884c\u4e86\u51cf 1 \u64cd\u4f5c\uff0c\u5177\u4f53\u63cf\u8ff0\u5982\u4e0b\u6ce8\u91ca\u3002\u672c\u6765\u4e00\u4e2a\u9875\u9762\u5c31\u53ea\u6709 1 \u6761\u8bb0\u5f55\uff0c\u518d\u8fdb\u884c\u51cf 1 \u64cd\u4f5c\u5c31\u53d8\u6210 0 \u4e86\uff0c\u6839\u636e\u4e0a\u9762\u7684\u516c\u5f0f\u5f97\u5230\u7684\u7d22\u5f15\u603b\u552f\u4e00\u503c\u5c31\u504f\u5dee\u975e\u5e38\u5927\u4e86\u3002<\/p>\n<pre><code>static bool dict_stats_analyze_index_for_n_prefix(\n    ...\n    \/\/ \u8bb0\u5f55\u9875\u552f\u4e00key\u6570\u91cf\n    uint64_t n_diff_on_leaf_page;\n    \n    \/\/ \u5f00\u59cb\u8fdb\u884cdive\uff0c\u83b7\u53d6n_diff_on_leaf_page\u7684\u503c\n    dict_stats_analyze_index_below_cur(pcur.get_btr_cur(), n_prefix,\n                                       &amp;n_diff_on_leaf_page, &amp;n_external_pages);\n    \n    \/* \u4e3a\u4e86\u907f\u514d\u76f8\u90bb\u4e24\u6b21dive\u7edf\u8ba1\u5230\u8fde\u7eed\u7684\u76f8\u540c\u7684\u4e24\u4e2a\u6570\u636e\uff0c\u56e0\u6b64\u51cf1\u8fdb\u884c\u4fee\u6b63\u3002\n    \u4e00\u6b21\u662f\u67d0\u4e2a\u9875\u9762\u7684\u6700\u540e\u4e00\u4e2a\u503c\uff0c\u4e00\u6b21\u662f\u53e6\u4e00\u4e2a\u9875\u9762\u7684\u7b2c\u4e00\u4e2a\u503c\u3002\u8bf7\u8003\u8651\u4ee5\u4e0b\u793a\u4f8b\uff1a\n    Leaf level:\n    page: (2,2,2,2,3,3)\n    ... \u8bb8\u591a\u9875\u9762\u7c7b\u4f3c\u4e8e (3,3,3,3,3,3)...\n    page: (3,3,3,3,5,5)\n    ... \u8bb8\u591a\u9875\u9762\u7c7b\u4f3c\u4e8e (5,5,5,5,5,5)...\n    page: (5,5,5,5,8,8)\n    page: (8,8,8,8,9,9)\n    \u6211\u4eec\u7684\u7b97\u6cd5\u4f1a\uff08\u6b63\u786e\u5730\uff09\u4f30\u8ba1\u5e73\u5747\u6bcf\u9875\u6709 2 \u6761\u4e0d\u540c\u7684\u8bb0\u5f55\u3002\n    \u7531\u4e8e\u6709 4 \u9875 non-boring \u8bb0\u5f55\uff0c\u5b83\u4f1a\uff08\u9519\u8bef\u5730\uff09\u5c06\u4e0d\u540c\u8bb0\u5f55\u7684\u6570\u91cf\u4f30\u8ba1\u4e3a 8 \u6761\n    *\/ \n    if (n_diff_on_leaf_page &gt; 0) {\n      n_diff_on_leaf_page--;\n    }\n    \n    \/\/ \u66f4\u65b0\u6570\u636e\uff0c\u5728\u6240\u6709\u5206\u6790\u7684\u9875\u9762\u4e0a\u53d1\u73b0\u7684\u4e0d\u540c\u952e\u503c\u6570\u91cf\u7684\u7d2f\u8ba1\u603b\u548c\n    n_diff_data-&gt;n_diff_all_analyzed_pages += n_diff_on_leaf_page;\n)\n<\/code><\/pre>\n<p>\u53ef\u4ee5\u770b\u5230PRIMARY\u4e3b\u952e\u5f02\u5e38\u60c5\u51b5\u4e0b\u7edf\u8ba1\u6570\u636e\u53ea\u6709 20 \u4e07\uff0c\u8868\u6709 400 \u767e\u4e07\u6570\u636e\u3002\u6b63\u5e38\u60c5\u51b5\u4e0b\u4e3b\u952e\u7edf\u8ba1\u6570\u636e\u6709 200 \u767e\u4e07\uff0c\u4e5f\u4e0e\u8868\u5b9e\u9645\u884c\u6570\u5dee\u5f02\u8f83\u5927\uff0c\u540c\u6837\u662f\u56e0\u4e3a\u5355\u4e2a\u9875\u9762\u884c\u6570\u592a\u5c11\uff08\u6b63\u5e38\u60c5\u51b5\u5927\u90e8\u5206\u4e5f\u53ea\u67092\u6761\u6570\u636e\uff09\uff0c\u518d\u8fdb\u884c\u51cf1\u64cd\u4f5c\u540e\uff0c\u5bfc\u81f4\u7edf\u8ba1\u4e5f\u4e0d\u51c6\u786e\u3002<\/p>\n<pre><code>MySQL&gt; select table_name,index_name,stat_value,sample_size from mysql.innodb_index_stats where database_name like 'sbtest' and TABLE_NAME like 'table_1' and stat_name='n_diff_pfx01';\n+-------------------+--------------------------------------------+------------+-------------+\n| table_name        | index_name                                 | stat_value | sample_size |\n+-------------------+--------------------------------------------+------------+-------------+\n| table_1           | PRIMARY                                    |     206508 |          20 |\n+-------------------+--------------------------------------------+------------+-------------+\n11 rows in set (0.00 sec)\n<\/code><\/pre>\n<p><strong>\u4f18\u5316<\/strong><\/p>\n<p>\u4e3a\u4e86\u907f\u514d\u76f8\u90bb\u4e24\u6b21dive\u7edf\u8ba1\u5230\u8fde\u7eed\u7684\u76f8\u540c\u7684\u4e24\u4e2a\u6570\u636e\uff0c\u56e0\u6b64\u51cf1\u8fdb\u884c\u4fee\u6b63\u3002<\/p>\n<p>\u8fd9\u91cc\u5e94\u8be5\u662f\u53ef\u4ee5\u4f18\u5316\u7684\uff0c\u5bf9\u4e8e\u4e3b\u952e\u6765\u8bf4\u662f\u4e0d\u662f\u53ef\u4ee5\u5224\u65ad\u53ea\u6709\u4e00\u4e2a\u5b57\u6bb5\u65f6\u4e0d\u9700\u8981\u8fdb\u884c\u51cf1\u64cd\u4f5c\uff0c\u4f1a\u5bfc\u81f4\u8868\u884c\u6570\u7edf\u8ba1\u975e\u5e38\u4e0d\u51c6\u786e\uff0c\u6bd5\u7adf\u76f8\u90bb\u9875\u4e0d\u4f1a\u6570\u636e\u91cd\u53e0\u3002<\/p>\n<p>\u6700\u4f4e\u9650\u5ea6\u4e5f\u9700\u8981\u5224\u65ad\u5355\u4e2a\u9875\u53ea\u6709\u4e00\u6761\u6570\u636e\u65f6\u4e0d\u9700\u8981\u51cf1\u64cd\u4f5c\u3002<\/p>\n<h2>\u516d\u3001\u7edf\u8ba1\u4fe1\u606f\u4e0e\u6162SQL\u4e4b\u95f4\u7684\u5173\u8054\u5173\u7cfb\uff1f<\/h2>\n<p>\u5f53\u524d MySQL \u5bf9\u5927\u90e8\u5206 SQL \u5728\u8bc4\u4f30\u626b\u63cf\u884c\u6570\u65f6\u90fd\u4e0d\u518d\u4f9d\u8d56\u7edf\u8ba1\u4fe1\u606f\u6570\u636e\uff0c\u800c\u662f\u901a\u8fc7\u4e00\u79cd index dive \u91c7\u6837\u7b97\u6cd5\u5b9e\u65f6\u83b7\u53d6\u5927\u6982\u9700\u8981\u626b\u63cf\u7684\u6570\u636e\uff0c\u8fd9\u79cd\u65b9\u5f0f\u7684\u7f3a\u70b9\u5c31\u662f\u6210\u672c\u7565\u9ad8\uff0c\u6240\u4ee5\u4e5f\u63d0\u4f9b\u6709\u53c2\u6570\u6765\u63a7\u5236\u67d0\u4e9b SQL \u662f\u8d70 index dive \u8fd8\u662f\u76f4\u63a5\u4f7f\u7528\u7edf\u8ba1\u6570\u636e\u3002<\/p>\n<p>\u53e6\u5916\u5728SQL\u5e26\u6709 order by field limit \u65f6\u4f1a\u89e6\u53d1MySQL\u5185\u90e8\u7684\u4e00\u4e2a\u5173\u4e8e prefer\/_ordering\/_index \u7684 ORDER BY \u4f18\u5316\uff0c\u5728\u8be5\u4f18\u5316\u4e2d\uff0c\u4f1a\u6bd4\u8f83\u4f7f\u7528\u6709\u5e8f\u7d22\u5f15\u548c\u65e0\u5e8f\u7d22\u5f15\u7684\u4ee3\u4ef7\uff0c\u8c01\u4f4e\u7528\u8c01\u3002<\/p>\n<p>\u5f53\u65f6\u4e1a\u52a1\u6709\u95ee\u9898\u7684\u6162 SQL \u5c31\u662f\u88ab\u8fd9\u4e2a\u4f18\u5316\u5e72\u6270\u4e86\u3002<\/p>\n<pre><code># where\u6761\u4ef6\nuser_id = ? and biz = ? and is_del = ? and status in (?) ORDER BY modify_time limit 5\n\n\n# \u8868\u7d22\u5f15\nidx_modify_time(`modify_time`)\nidx_user_biz_del(`user_id`,`biz`, `is_del`)\n<\/code><\/pre>\n<p>\u6b63\u5e38\u8d70 idx\/_user\/_biz\/_del \u7d22\u5f15\u4e3a\u8fc7\u6ee4\u6027\u6700\u597d\uff0c\u4f46\u9700\u8981\u5bf9 modify\/_time \u5b57\u6bb5\u8fdb\u884c\u6392\u5e8f\u3002<\/p>\n<p>\u8fd9\u4e2a\u4f18\u5316\u673a\u5236\u5c31\u662f\u60f3\u5c1d\u8bd5\u8d70 idx\/_modify\/_time \u7d22\u5f15\uff0c\u8d70\u6709\u5e8f\u7d22\u5f15\u60f3\u907f\u514d\u6392\u5e8f\uff0c\u7136\u540e\u5957\u4e86\u4e00\u4e2a\u516c\u5f0f\u6765\u9884\u4f30\u5982\u679c\u8d70 idx\/_modify\/_time \u6709\u5e8f\u7d22\u5f15\u5927\u6982\u9700\u8981\u626b\u63cf\u591a\u5c11\u884c\uff1f\u516c\u5f0f\u975e\u5e38\u7b80\u5355\u76f4\u63a5\uff1a\u8868\u603b\u884c\u6570 \/ \u6700\u4f18\u7d22\u5f15\u7684\u626b\u63cf\u884c\u6570 * limit\u3002<\/p>\n<ul>\n<li><strong>\u8868\u603b\u884c\u6570<\/strong>\uff1a\u4e5f\u5c31\u662f\u7edf\u8ba1\u4fe1\u606f\u91cc\u9762\u4e3b\u952e\u7684 n\/_rows<\/li>\n<li><strong>\u6700\u4f18\u7d22\u5f15\u7684\u626b\u63cf\u884c\u6570<\/strong>\uff1a\u4e5f\u5c31\u662f\u8d70 idx\/_user\/_biz\/_del \u7d22\u5f15\u9700\u8981\u626b\u63cf\u7684\u884c\u6570<\/li>\n<li><strong>limit<\/strong>\uff1a\u4e5f\u5c31\u662f SQL \u8bed\u53e5\u91cc\u9762\u7684 limit \u503c<\/li>\n<\/ul>\n<p>\u4f7f\u7528\u6709\u5e8f\u7d22\u5f15\u9884\u4f30\u7684\u884c\u6570\u5bf9\u6bd4\u6700\u4f18\u7d22\u5f15\u7684\u626b\u63cf\u884c\u6570\u6765\u51b3\u5b9a\u4f7f\u7528\u8c01\uff0c\u5728\u8fd9\u79cd\u6539\u53d8\u7d22\u5f15\u7684\u7b56\u7565\u4e0b\uff0c\u5982\u679c\u8868\u7684\u603b\u884c\u6570\u4f30\u8ba1\u8f83\u4f4e\uff08\u5c31\u662f\u4e0a\u9762\u4e3b\u952e\u7684\u7edf\u8ba1\u503c\uff09\uff0c\u4f1a\u5bfc\u81f4\u66f4\u503e\u5411\u4e8e\u9009\u62e9\u6709\u5e8f\u7d22\u5f15\u3002<\/p>\n<p>\u4f46\u4e00\u4e2a\u6700\u91cd\u8981\u7684\u56e0\u7d20\u88ab MySQL \u5ffd\u7565\u4e86\uff0c\u5c31\u662f\u5b9e\u9645\u4e1a\u52a1\u6570\u636e\u5206\u5e03\u5e76\u4e0d\u662f\u6309\u5b83\u7ed9\u7684\u8fd9\u79cd\u516c\u5f0f\u6765\uff0c\u5f80\u5f80\u9700\u8981\u626b\u63cf\u5f88\u591a\u6570\u636e\u624d\u80fd\u6ee1\u8db3 limit \u503c\uff0c\u9020\u6210\u6162 SQL\u3002<\/p>\n<h2>\u4e03\u3001\u5982\u4f55\u4e34\u65f6\u89e3\u51b3\u8be5\u95ee\u9898\uff1f<\/h2>\n<p>\u53d1\u73b0\u95ee\u9898\u540e\uff0c\u53ef\u63a7\u7684\u60c5\u51b5\u4e0b\u9009\u62e9\u5728\u4f4e\u5cf0\u671f\u5bf9\u8868\u6267\u884c\u539f\u751f alter table xxx engine=innodb \u8bed\u53e5\uff0c MySQL \u5185\u90e8\u91cd\u65b0\u6574\u7406\u4e86\u8868\u7a7a\u95f4\u6570\u636e\uff0c\u76f8\u5173\u95ee\u9898\u6062\u590d\u6b63\u5e38\u3002\u4f46\u8fd9\u4e2a\u539f\u751f DDL \u8bed\u53e5\uff0c\u867d\u7136\u53d8\u66f4\u4e0d\u4f1a\u4ea7\u751f\u9501\u8868\uff0c\u4f46\u8be5\u8bed\u53e5\u65e0\u6cd5\u9650\u901f\uff0c\u540c\u65f6\u4e5f\u4f1a\u5bfc\u81f4\u4e3b\u4ece\u6570\u636e\u8f83\u5927\u5ef6\u8fdf\u3002<\/p>\n<p>\u4e3a\u4ec0\u4e48\u539f\u751f DDL \u8bed\u53e5\u53ef\u4ee5\u89e3\u51b3\u8be5\u95ee\u9898\uff1f\u770b\u4e24\u8005\u5728\u6d41\u7a0b\u4e0a\u7684\u5bf9\u6bd4\u533a\u522b\u3002<\/p>\n<p><img decoding=\"async\" width=\"723\" height=\"311\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwdF\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<p>\u53ef\u4ee5\u770b\u51fa\u7ed3\u6784\u53d8\u66f4\u552f\u4e00\u4e0d\u540c\u7684\u5c31\u662f\u589e\u91cf DML \u8bed\u53e5\u662f\u7b49\u5168\u91cf\u6570\u636e\u590d\u5236\u5b8c\u6210\u540e\u624d\u5f00\u59cb\u5e94\u7528\uff0c\u6240\u4ee5\u80fd\u4fee\u590d\u8868\u7a7a\u95f4\uff0c\u6ca1\u6709\u5bfc\u81f4\u8868\u81a8\u80c0\u3002<\/p>\n<h2>\u516b\u3001\u5982\u4f55\u957f\u671f\u89e3\u51b3\u8be5\u95ee\u9898\uff1f<\/h2>\n<p>\u5173\u4e8e\u4e1a\u52a1\u4fa7\u7684\u6539\u9020\u8fd9\u91cc\u4e0d\u505a\u8fc7\u591a\u8bf4\u660e\uff0c\u6211\u4eec\u770b\u770b\u4ece\u53d8\u66f4\u6d41\u7a0b\u4e0a\u9762\u662f\u5426\u53ef\u4ee5\u907f\u514d\u8fd9\u4e2a\u95ee\u9898\u3002<\/p>\n<p>\u65e2\u7136\u5728\u53d8\u66f4\u8fc7\u7a0b\u4e2d\u590d\u5236\u5168\u91cf\u6570\u636e\u548c binlog \u589e\u91cf\u6570\u636e\u56de\u653e\u5b58\u5728\u4ea4\u53c9\u5e76\u884c\u6267\u884c\u7684\u53ef\u80fd\uff0c\u90a3\u4e48\u5982\u679c\u6211\u4eec\u5148\u6267\u884c\u5168\u91cf\u6570\u636e\u590d\u5236\uff0c\u7136\u540e\u518d\u8fdb\u884c\u589e\u91cf binlog \u56de\u653e\u662f\u4e0d\u662f\u5c31\u53ef\u4ee5\u7ed5\u8fc7\u8fd9\u4e2a\u9875\u5206\u88c2\u95ee\u9898\uff08\u5c31\u53d8\u6210\u4e86\u8ddf MySQL \u539f\u751f DDL \u4e00\u6837\u7684\u6d41\u7a0b\uff09\u3002<\/p>\n<p>\u53d8\u66f4\u5de5\u5177\u5b9e\u9645\u6539\u52a8\u5982\u4e0b\u56fe\uff1a<\/p>\n<p><img decoding=\"async\" width=\"723\" height=\"442\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdmwdz\" alt=\"image.png\" title=\"image.png\" loading=\"lazy\"><\/p>\n<p>\u8fd9\u6837\u5c31\u4e0d\u5b58\u5728\u6700\u5927\u8bb0\u5f55\u5148\u63d2\u5165\u5230\u8868\u4e2d\u7684\u95ee\u9898\uff0c\u4e22\u5f03\u7684\u8bb0\u5f55\u540e\u7eed\u5168\u91cf\u590d\u5236\u4e5f\u540c\u6837\u4f1a\u628a\u8bb0\u5f55\u590d\u5236\u5230\u4e34\u65f6\u8868\u4e2d\u3002\u5e76\u4e14\u8fd9\u4e2a\u4f18\u5316\u8fd8\u80fd\u89e3\u51b3\u9700\u8981\u5927\u91cf\u56de\u653e binlog \u95ee\u9898\uff0c\u7ec6\u8282\u53ef\u4ee5\u770b\u770b gh-ost \u7684 PR-1378\u3002<\/p>\n<h2>\u4e5d\u3001\u603b\u7ed3<\/h2>\n<p>\u672c\u6587\u5148\u4ecb\u7ecd\u4e86\u4e00\u4e9b\u5173\u4e8e InnoDB \u7d22\u5f15\u673a\u5236\u548c\u9875\u6ea2\u51fa\u3001\u9875\u5206\u88c2\u65b9\u9762\u7684\u77e5\u8bc6\uff1b\u4ecb\u7ecd\u4e86\u4e1a\u754c\u901a\u7528\u7684 DDL \u53d8\u66f4\u5de5\u5177\u6d41\u7a0b\u539f\u7406\u3002<\/p>\n<p>\u968f\u540e\u8be6\u7ec6\u5206\u6790\u4e86\u53d8\u66f4\u540e\u8868\u7a7a\u95f4\u81a8\u80c0\u95ee\u9898\u6839\u56e0\uff0c\u4e3b\u8981\u662f\u5f53\u524d\u53d8\u66f4\u6d41\u7a0b\u673a\u5236\u53e0\u52a0\u5355\u884c\u8bb0\u5f55\u8fc7\u5927\u7684\u65f6\u5019\uff08\u4e1a\u52a1\u8868\u5355\u884c\u8bb0\u5f55\u5927\u5c0f 5k \u5de6\u53f3\uff09\uff0c\u4f1a\u78b0\u5230 MySQL \u9875\u5206\u88c2\u7684\u4e00\u4e2a\u7455\u75b5\uff0c\u5bfc\u81f4\u4e86\u4e00\u4e2a\u9875\u53ea\u5b58\u50a8\u4e86 1 \u6761\u8bb0\u5f55\uff0816k \u7684\u9875\u53ea\u5b58\u50a8\u4e86 5k\uff0c\u6d6a\u8d39 2\/3 \u7a7a\u95f4\uff09\uff0c\u5bfc\u81f4\u5b58\u50a8\u7a7a\u95f4\u81a8\u80c0\u95ee\u9898\u3002<\/p>\n<p>\u6700\u540e\u5206\u6790\u4e86\u7edf\u8ba1\u4fe1\u606f\u51fa\u9519\u7684\u539f\u56e0\u548c\u7edf\u8ba1\u4fe1\u606f\u51fa\u9519\u4e0e\u6162 SQL \u4e4b\u95f4\u7684\u5173\u8054\u5173\u7cfb\uff0c\u4ee5\u53ca\u89e3\u51b3\u65b9\u6848\u3002<\/p>\n<p>\u5168\u6587\u5b8c\uff0c\u611f\u8c22\u9605\u8bfb\u3002<\/p>\n<p><strong>\u5f80\u671f\u56de\u987e<\/strong><\/p>\n<ol>\n<li>MySQL\u5355\u8868\u4e3a\u4f55\u522b\u8d852000\u4e07\u884c\uff1f\u63ed\u79d8B+\u6811\u4e0e16KB\u9875\u7684\u751f\u6b7b\u535a\u5f08\uff5c\u5f97\u7269\u6280\u672f<\/li>\n<li>0\u57fa\u7840\u5e26\u4f60\u7cbe\u901aJava\u5bf9\u8c61\u5e8f\u5217\u5316&#8211;\u4ee5Hessian\u4e3a\u4f8b\uff5c\u5f97\u7269\u6280\u672f<\/li>\n<li>\u524d\u7aef\u65e5\u5fd7\u56de\u635e\u7cfb\u7edf\u7684\u6027\u80fd\u4f18\u5316\u5b9e\u8df5\uff5c\u5f97\u7269\u6280\u672f<\/li>\n<li>\u5f97\u7269\u7075\u7280\u641c\u7d22\u63a8\u8350\u8bcd\u5206\u53d1\u5e73\u53f0\u6f14\u8fdb3.0<\/li>\n<li>R8\u7591\u96be\u6742\u75c7\u5206\u6790\u5b9e\u6218\uff1a\u5916\u8054\u4f18\u5316\u8bbe\u8ba1\u7f3a\u9677\u5f15\u8d77\u7684\u5d29\u6e83\uff5c\u5f97\u7269\u6280\u672f<\/li>\n<\/ol>\n<p>\u6587 \/ \u4e1c\u9752<\/p>\n<p>\u5173\u6ce8\u5f97\u7269\u6280\u672f\uff0c\u6bcf\u5468\u66f4\u65b0\u6280\u672f\u5e72\u8d27<\/p>\n<p>\u8981\u662f\u89c9\u5f97\u6587\u7ae0\u5bf9\u4f60\u6709\u5e2e\u52a9\u7684\u8bdd\uff0c\u6b22\u8fce\u8bc4\u8bba\u8f6c\u53d1\u70b9\u8d5e\uff5e<\/p>\n<p>\u672a\u7ecf\u5f97\u7269\u6280\u672f\u8bb8\u53ef\u4e25\u7981\u8f6c\u8f7d\uff0c\u5426\u5219\u4f9d\u6cd5\u8ffd\u7a76\u6cd5\u5f8b\u8d23\u4efb\u3002<\/p>\n<p><a href=\"https:\/\/segmentfault.com\/a\/1190000047273730\">https:\/\/segmentfault.com\/a\/1190000047273730<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u3001\u95ee\u9898\u80cc\u666f \u4e1a\u52a1\u540c\u5b66\u5728 OneDBA \u5e73\u53f0\u8fdb\u884c\u4e00\u6b21\u6b63\u5e38 DDL \u53d8\u66f4\u5b8c\u6210\u540e\uff08\u53d8\u66f4\u5185\u5bb9\u8ddf\u6b64\u6b21\u95ee\u9898\u65e0\u5173\uff09\uff0c\u53d1\u73b0\u4e00 [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-10021006","post","type-post","status-publish","format-standard","hentry","category-share"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10021006","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10021006"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10021006\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10021006"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10021006"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10021006"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}