{"id":116238,"date":"2026-10-21T00:40:42","date_gmt":"2026-10-20T16:40:42","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/116238.html"},"modified":"2026-10-21T00:40:42","modified_gmt":"2026-10-20T16:40:42","slug":"%e6%88%91%e5%8f%aa%e5%a4%9a%e6%9f%a5%e4%ba%86%e4%b8%80%e4%b8%aa%e5%ad%97%e6%ae%b5%ef%bc%8c%e4%b8%ba%e4%bb%80%e4%b9%88%e6%85%a2%e4%ba%86-10-%e5%80%8d%ef%bc%9f-mysql-%e5%a4%a7%e5%ad%97","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/116238.html","title":{"rendered":"\u6211\u53ea\u591a\u67e5\u4e86\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e3a\u4ec0\u4e48\u6162\u4e86 10 \u500d\uff1f\u2014\u2014MySQL \u5927\u5b57\u6bb5\u4e0e\u6ea2\u51fa\u9875\u5165\u95e8"},"content":{"rendered":"<\/p>\n<h4>\u76ee\u5f55<\/h4>\n<ul>\n<li>\n<ul>\n<li>\u4e00\u3001\u4e00\u4e2a\u5e38\u89c1\u7684\u56f0\u60d1<\/li>\n<li>\u4e8c\u3001\u5148\u5206\u6e05\u4e24\u4e2a\u6982\u5ff5&#xff1a;\u56de\u8868 \u2260 \u6ea2\u51fa\u9875<\/li>\n<li>\u4e09\u3001MySQL \u7684\u6570\u636e\u662f\u600e\u4e48\u6446\u5728\u78c1\u76d8\u4e0a\u7684<\/li>\n<li>\u56db\u3001\u4e00\u4e2a\u9875 16KB&#xff0c;\u4e00\u7bc7\u6587\u7ae0 3 \u4e07\u5b57&#xff0c;\u600e\u4e48\u529e<\/li>\n<li>\n<ul>\n<li>4.1 \u4e00\u6761\u786c\u6027\u89c4\u5b9a<\/li>\n<li>4.2 \u95ee\u9898\u6765\u4e86<\/li>\n<li>4.3 InnoDB \u7684\u89e3\u6cd5&#xff1a;\u642c\u8d70&#xff0c;\u53ea\u7559\u4e2a\u5730\u5740<\/li>\n<li>4.4 20 \u5b57\u8282\u6307\u9488\u91cc\u88c5\u4e86\u4ec0\u4e48<\/li>\n<li>4.5 \u4e00\u4e2a\u5927\u5b57\u6bb5\u53ef\u80fd\u4e0d\u6b62\u4e00\u4e2a\u6ea2\u51fa\u9875<\/li>\n<li>4.6 \u8fd9\u4e2a\u673a\u5236\u6709\u4e2a\u597d\u7684\u526f\u4f5c\u7528<\/li>\n<\/ul>\n<\/li>\n<li>\u4e94\u3001\u884c\u683c\u5f0f&#xff1a;\u51b3\u5b9a\u300c\u632a\u591a\u5c11\u300d\u7684\u9690\u85cf\u5f00\u5173<\/li>\n<li>\n<ul>\n<li>5.1 \u56db\u79cd\u884c\u683c\u5f0f<\/li>\n<li>5.2 &#096;COMPACT&#096; \u7684 768 \u5b57\u8282\u524d\u7f00\u5751<\/li>\n<li>5.3 \u68c0\u67e5\u5e76\u4fee\u590d<\/li>\n<li>5.4 \u4e00\u4e2a\u5e38\u89c1\u8bef\u89e3&#xff1a;\u300cTEXT \u4e00\u5b9a\u884c\u5916&#xff0c;VARCHAR \u4e00\u5b9a\u884c\u5185\u300d<\/li>\n<\/ul>\n<\/li>\n<li>\u516d\u3001\u4eb2\u624b\u505a\u4e2a\u5b9e\u9a8c&#xff0c;\u770b\u89c1\u8fd9\u4ef6\u4e8b<\/li>\n<li>\n<ul>\n<li>6.1 \u51c6\u5907\u4e24\u5f20\u8868<\/li>\n<li>6.2 \u704c 5 \u4e07\u884c\u6570\u636e&#xff0c;\u6bcf\u884c\u6b63\u6587\u7ea6 9.4KB<\/li>\n<li>6.3 \u770b\u770b\u4e24\u5f20\u8868\u5360\u4e86\u591a\u5c11\u7a7a\u95f4<\/li>\n<li>6.4 \u5b9e\u9a8c\u4e00&#xff1a;\u53ea\u67e5\u5c0f\u5b57\u6bb5&#xff0c;\u5927\u5b57\u6bb5\u5b58\u4e0d\u5b58\u5728\u6709\u5f71\u54cd\u5417<\/li>\n<li>6.5 \u5b9e\u9a8c\u4e8c&#xff1a;\u8fd9\u624d\u662f\u91cd\u70b9\u2014\u2014\u591a\u53d6\u4e00\u4e2a\u5927\u5b57\u6bb5<\/li>\n<li>6.6 \u5b9e\u9a8c\u4e09&#xff1a;\u6700\u9690\u853d\u7684\u5751\u2014\u2014\u53bb\u91cd\/\u5206\u7ec4\u65f6\u5e26\u4e0a\u5927\u5b57\u6bb5<\/li>\n<\/ul>\n<\/li>\n<li>\u4e03\u3001\u516b\u79cd\u4f1a\u88ab\u5927\u5b57\u6bb5\u62d6\u6162\u7684\u64cd\u4f5c<\/li>\n<li>\n<ul>\n<li>\u7b2c 7 \u6761\u503c\u5f97\u5355\u72ec\u8bf4&#xff1a;\u4e3a\u4ec0\u4e48\u6a21\u7cca\u641c\u7d22\u8981\u4e0a\u641c\u7d22\u5f15\u64ce<\/li>\n<li>\u987a\u4fbf\u8bf4\u4e2a\u51b7\u77e5\u8bc6&#xff1a;&#096;COUNT(*)&#096; \u548c &#096;SUM(LENGTH(content))&#096; \u7684\u5dee\u522b<\/li>\n<\/ul>\n<\/li>\n<li>\u516b\u3001\u516d\u4e2a\u4f18\u5316\u624b\u6cd5&#xff08;\u5e26\u4ee3\u7801&#xff09;<\/li>\n<li>\n<ul>\n<li>8.1 \u6c38\u8fdc\u663e\u5f0f\u5217\u5b57\u6bb5&#xff0c;\u522b\u7528 &#096;SELECT *&#096;<\/li>\n<li>8.2 \u6df1\u5206\u9875&#xff1a;\u7528\u5ef6\u8fdf\u5173\u8054<\/li>\n<li>8.3 \u6279\u91cf\u626b\u63cf&#xff1a;\u7528\u4e3b\u952e\u6e38\u6807\u4ee3\u66ff OFFSET<\/li>\n<li>8.4 \u6392\u5e8f\/\u53bb\u91cd\u9636\u6bb5\u4e0d\u8981\u5e26\u5927\u5b57\u6bb5&#xff08;\u4e24\u6bb5\u5f0f\u67e5\u8be2&#xff09;<\/li>\n<li>\n<ul>\n<li>\u573a\u666f\u4e00&#xff1a;\u6392\u5e8f\u53d6 Top N<\/li>\n<li>\u573a\u666f\u4e8c&#xff1a;\u53bb\u91cd<\/li>\n<li>\u573a\u666f\u4e09&#xff1a;\u6309\u5185\u5bb9\u5927\u5c0f\u5207\u6279\u6b21<\/li>\n<\/ul>\n<\/li>\n<li>8.5 \u5782\u76f4\u62c6\u8868&#xff1a;\u51b7\u70ed\u5206\u79bb<\/li>\n<li>8.6 \u5176\u4ed6<\/li>\n<\/ul>\n<\/li>\n<li>\u4e5d\u3001\u600e\u4e48\u8bca\u65ad&#xff1a;&#096;EXPLAIN&#096; \u7684\u76f2\u533a<\/li>\n<li>\n<ul>\n<li>9.1 \u5148\u627f\u8ba4 &#096;EXPLAIN&#096; \u5728\u8fd9\u4ef6\u4e8b\u4e0a\u65e0\u80fd<\/li>\n<li>9.2 \u65b9\u6cd5\u4e00&#xff1a;\u5bf9\u6bd4 Buffer Pool \u8bfb\u8bf7\u6c42\u6570&#xff08;\u6700\u76f4\u63a5&#xff09;<\/li>\n<li>9.3 \u65b9\u6cd5\u4e8c&#xff1a;\u770b\u4e34\u65f6\u8868\u548c\u6392\u5e8f\u6709\u6ca1\u6709\u843d\u78c1\u76d8<\/li>\n<li>9.4 \u65b9\u6cd5\u4e09&#xff1a;\u4ece Performance Schema \u627e\u6162\u8bed\u53e5<\/li>\n<li>9.5 \u65b9\u6cd5\u56db&#xff1a;\u4f30\u7b97\u5927\u5b57\u6bb5\u5230\u5e95\u5360\u4e86\u591a\u5c11<\/li>\n<li>9.6 \u4e00\u4e2a\u503c\u5f97\u77e5\u9053\u7684\u539f\u7406&#xff1a;\u4f18\u5316\u5668\u4e5f\u770b\u4e0d\u89c1\u6ea2\u51fa\u9875<\/li>\n<\/ul>\n<\/li>\n<li>\u5341\u3001\u603b\u7ed3&#xff1a;\u4e94\u53e5\u8bdd\u8bb0\u4f4f<\/li>\n<li>\n<ul>\n<li>\u4e00\u5f20\u5224\u65ad\u6d41\u7a0b\u56fe<\/li>\n<li>\u6700\u540e\u4e00\u6761\u53cd\u76f4\u89c9\u7684\u5bf9\u7167<\/li>\n<\/ul>\n<\/li>\n<li>\u9644&#xff1a;\u5ef6\u4f38\u9605\u8bfb<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/10\/20261020164040-6ad799888eab8.png\" alt=\"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\" \/><\/p>\n<p>\u672c\u6587\u662f\u5c0f\u9a6c\u771f\u5b9e\u9047\u5230\u7684\u573a\u666f\u5206\u4eab\u3002 \u9762\u5411\u4eba\u7fa4&#xff1a;\u4f1a\u5199 SQL\u3001\u77e5\u9053\u300c\u7d22\u5f15\u80fd\u8ba9\u67e5\u8be2\u53d8\u5feb\u300d&#xff0c;\u4f46\u6ca1\u6df1\u5165\u7814\u7a76\u8fc7 MySQL \u5b58\u50a8\u7ed3\u6784\u7684\u540c\u5b66\u3002<\/p>\n<p>\u8bfb\u5b8c\u4f60\u4f1a\u77e5\u9053&#xff1a;TEXT \u5b57\u6bb5\u5230\u5e95\u5b58\u5728\u54ea\u3001\u4e3a\u4ec0\u4e48 SELECT * \u8fd9\u4e48\u5371\u9669\u3001\u4e3a\u4ec0\u4e48 EXPLAIN \u6709\u65f6\u5019\u9a97\u4eba\u3001\u4ee5\u53ca 6 \u4e2a\u7acb\u523b\u80fd\u7528\u7684\u4f18\u5316\u624b\u6cd5\u3002<\/p>\n<p>\u73af\u5883&#xff1a;MySQL 5.7 \/ 8.0&#xff0c;InnoDB \u5f15\u64ce\u3002\u7b2c\u516d\u8282\u7684\u5b9e\u9a8c\u53ef\u4ee5\u76f4\u63a5\u590d\u5236\u6267\u884c&#xff0c;\u6587\u4e2d\u7ed9\u51fa\u7684\u8017\u65f6\u548c\u7a7a\u95f4\u6570\u5b57\u5747\u4e3a\u793a\u610f\u91cf\u7ea7&#xff0c;\u8bf7\u4ee5\u4f60\u81ea\u5df1\u673a\u5668\u4e0a\u8dd1\u51fa\u6765\u7684\u7ed3\u679c\u4e3a\u51c6\u3002<\/p>\n<h3>\u4e00\u3001\u4e00\u4e2a\u5e38\u89c1\u7684\u56f0\u60d1<\/h3>\n<p>\u5047\u8bbe\u4f60\u6709\u4e00\u5f20\u6587\u7ae0\u8868&#xff0c;\u51e0\u5343\u4e07\u884c\u3002\u4f60\u5199\u4e86\u4e24\u4e2a\u67e5\u8be2&#xff1a;<\/p>\n<p><span class=\"token comment\">&#8212; \u67e5\u8be2 A<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> user_id<span class=\"token punctuation\">,<\/span> like_count <span class=\"token keyword\">FROM<\/span> t_article <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span> <span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">1000<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u67e5\u8be2 B<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> user_id<span class=\"token punctuation\">,<\/span> like_count<span class=\"token punctuation\">,<\/span> content <span class=\"token keyword\">FROM<\/span> t_article <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span> <span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">1000<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u53ea\u591a\u4e86\u4e00\u4e2a content \u5b57\u6bb5\u3002\u4f60\u7528 EXPLAIN \u770b&#xff0c;\u4e24\u6761\u7684\u8f93\u51fa\u4e00\u6a21\u4e00\u6837&#xff1a;<\/p>\n<p>&#043;&#8212;-&#043;&#8212;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#043;<br \/>\n| id | table       | type | key           | rows        | Extra|<br \/>\n&#043;&#8212;-&#043;&#8212;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#043;<br \/>\n|  1 | t_article   | ref  | idx_cate      | 1024        |      |<br \/>\n&#043;&#8212;-&#043;&#8212;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#043;<\/p>\n<p>\u540c\u6837\u7684\u6267\u884c\u8ba1\u5212\u3001\u540c\u6837\u7684\u7d22\u5f15\u3001\u540c\u6837\u7684\u626b\u63cf\u884c\u6570\u3002\u4f46\u5b9e\u9645\u8dd1\u8d77\u6765&#xff1a;<\/p>\n<p>\u67e5\u8be2 A&#xff1a;  8 ms<br \/>\n\u67e5\u8be2 B&#xff1a; 95 ms      \u2190 \u6162\u4e86 10 \u500d\u4ee5\u4e0a<\/p>\n<p>EXPLAIN \u6ca1\u6709\u4efb\u4f55\u4e00\u5217\u80fd\u89e3\u91ca\u8fd9\u4e2a\u5dee\u8ddd\u3002<\/p>\n<p>\u7b54\u6848\u85cf\u5728\u4e00\u4e2a\u5f88\u591a\u4eba\u6ca1\u542c\u8fc7\u7684\u8bcd\u91cc&#xff1a;\u884c\u5916\u5b58\u50a8&#xff08;off-page storage&#xff09;\/ \u6ea2\u51fa\u9875&#xff08;overflow page&#xff09;\u3002<\/p>\n<hr \/>\n<h3>\u4e8c\u3001\u5148\u5206\u6e05\u4e24\u4e2a\u6982\u5ff5&#xff1a;\u56de\u8868 \u2260 \u6ea2\u51fa\u9875<\/h3>\n<p>\u5f88\u591a\u4eba\u95ee\u300c\u5b57\u6bb5\u592a\u5927\u4f1a\u4e0d\u4f1a\u56de\u8868\u300d&#xff0c;\u8fd9\u5176\u5b9e\u628a\u4e24\u4ef6\u4e0d\u76f8\u5173\u7684\u4e8b\u6df7\u5728\u4e00\u8d77\u4e86\u3002\u5b83\u4eec\u662f\u5b8c\u5168\u72ec\u7acb\u7684\u4e24\u4e2a\u673a\u5236&#xff1a;<\/p>\n<table>\n<tr>\u56de\u8868\u884c\u5916\u5b58\u50a8 \/ \u6ea2\u51fa\u9875<\/tr>\n<tbody>\n<tr>\n<td>\u82f1\u6587<\/td>\n<td>index lookup \/ bookmark lookup<\/td>\n<td>off-page storage \/ overflow page<\/td>\n<\/tr>\n<tr>\n<td>\u4ec0\u4e48\u65f6\u5019\u53d1\u751f<\/td>\n<td>\u8981\u67e5\u7684\u5217\u4e0d\u5168\u5728\u7528\u5230\u7684\u7d22\u5f15\u91cc<\/td>\n<td>\u5355\u884c\u653e\u4e0d\u8fdb\u4e00\u4e2a\u6570\u636e\u9875<\/td>\n<\/tr>\n<tr>\n<td>\u7531\u4ec0\u4e48\u51b3\u5b9a<\/td>\n<td>\u53d6\u7684\u5b57\u6bb5\u662f\u54ea\u4e9b<\/td>\n<td>\u5b57\u6bb5\u6709\u591a\u5927<\/td>\n<\/tr>\n<tr>\n<td>\u4e00\u6b21\u7684\u4ee3\u4ef7<\/td>\n<td>\u4e00\u6b21 B&#043; \u6811\u67e5\u627e&#xff08;\u7ea6 3-4 \u6b21\u9875\u8bbf\u95ee&#xff09;<\/td>\n<td>\u4e00\u6b21&#xff08;\u6216\u591a\u6b21&#xff09;\u989d\u5916\u968f\u673a I\/O<\/td>\n<\/tr>\n<tr>\n<td>\u600e\u4e48\u6d88\u9664<\/td>\n<td>\u5efa\u8986\u76d6\u7d22\u5f15<\/td>\n<td>\u522b\u53d6\u90a3\u4e2a\u5b57\u6bb5 \/ \u62c6\u8868 \/ \u538b\u7f29<\/td>\n<\/tr>\n<tr>\n<td>EXPLAIN \u770b\u5f97\u5230\u5417<\/td>\n<td>\u2705 \u770b\u5f97\u5230&#xff08;Using index &#061; \u6ca1\u56de\u8868&#xff09;<\/td>\n<td>\u274c \u5b8c\u5168\u770b\u4e0d\u5230<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u5212\u91cd\u70b9&#xff1a;\u56de\u8868\u8ddf\u5b57\u6bb5\u591a\u5927\u6ca1\u6709\u5173\u7cfb&#xff1b;\u6ea2\u51fa\u9875\u8ddf\u4f60\u8d70\u54ea\u4e2a\u7d22\u5f15\u6ca1\u6709\u5173\u7cfb\u3002 \u5b83\u4eec\u53ef\u4ee5\u4efb\u610f\u7ec4\u5408&#xff1a;<\/p>\n<p>\u8d70\u4e3b\u952e  &#043; \u53d6 content     \u2192 \u4e0d\u56de\u8868&#xff0c;\u4f46\u8981\u8bfb\u6ea2\u51fa\u9875<br \/>\n\u8d70\u4e8c\u7ea7\u7d22\u5f15 &#043; \u53ea\u53d6\u5c0f\u5b57\u6bb5   \u2192 \u56de\u8868\u4e86&#xff0c;\u4f46\u4e0d\u8bfb\u6ea2\u51fa\u9875<br \/>\n\u8d70\u4e8c\u7ea7\u7d22\u5f15 &#043; \u53d6 content   \u2192 \u65e2\u56de\u8868\u53c8\u8bfb\u6ea2\u51fa\u9875     \u2190 \u6700\u8d35<br \/>\n\u8986\u76d6\u7d22\u5f15                 \u2192 \u4e24\u4e2a\u90fd\u6ca1\u6709           \u2190 \u6700\u4fbf\u5b9c<\/p>\n<p>\u6709\u4e2a\u53cd\u76f4\u89c9\u7684\u7ed3\u8bba\u5148\u653e\u5728\u8fd9\u513f&#xff0c;\u540e\u9762\u4f1a\u8bc1\u660e&#xff1a;<\/p>\n<p>\u300c\u4e0d\u56de\u8868\u300d\u4e0d\u7b49\u4e8e\u300c\u5feb\u300d\u3002 \u5f53\u5b57\u6bb5\u5f88\u5927\u65f6&#xff0c;\u8bfb\u6ea2\u51fa\u9875\u7684\u4ee3\u4ef7\u53ef\u80fd\u9ad8\u4e8e\u4e00\u6b21\u56de\u8868\u2014\u2014\u56e0\u4e3a\u56de\u8868\u8981\u8d70\u7684\u975e\u53f6\u5b50\u8282\u70b9\u901a\u5e38\u5df2\u7ecf\u7f13\u5b58\u5728\u5185\u5b58\u91cc&#xff0c;\u800c\u6ea2\u51fa\u9875\u5f80\u5f80\u662f\u4e00\u6b21\u5b9e\u6253\u5b9e\u7684\u968f\u673a I\/O&#xff0c;\u4e14\u5b57\u6bb5\u8d8a\u5927\u8981\u8bfb\u7684\u9875\u8d8a\u591a\u3002<\/p>\n<p>\u672c\u6587\u53ea\u8bb2\u53f3\u8fb9\u90a3\u4e00\u5217\u3002<\/p>\n<hr \/>\n<h3>\u4e09\u3001MySQL \u7684\u6570\u636e\u662f\u600e\u4e48\u6446\u5728\u78c1\u76d8\u4e0a\u7684<\/h3>\n<p>\u8981\u7406\u89e3\u6ea2\u51fa\u9875&#xff0c;\u5f97\u5148\u77e5\u9053 InnoDB \u5b58\u6570\u636e\u7684\u57fa\u672c\u5355\u4f4d\u4e0d\u662f\u300c\u884c\u300d&#xff0c;\u800c\u662f\u9875&#xff08;page&#xff09;&#xff0c;\u9ed8\u8ba4 16KB\u3002<\/p>\n<p><span class=\"token keyword\">SHOW<\/span> VARIABLES <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;innodb_page_size&#039;<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token comment\">&#8212; &#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;-&#043;<\/span><br \/>\n<span class=\"token comment\">&#8212; | innodb_page_size | 16384 |<\/span><br \/>\n<span class=\"token comment\">&#8212; &#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;-&#043;<\/span><\/p>\n<p>\u4f60\u7684\u8868\u6570\u636e\u957f\u8fd9\u6837&#xff08;\u8fd9\u53eb\u805a\u7c07\u7d22\u5f15&#xff0c;\u53f6\u5b50\u8282\u70b9\u5c31\u662f\u771f\u5b9e\u6570\u636e&#xff09;&#xff1a;<\/p>\n<p>                    \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510<br \/>\n                    \u2502  \u975e\u53f6\u5b50\u8282\u70b9   \u2502   \u2190 \u53ea\u5b58\u300c\u4e3b\u952e \u2192 \u9875\u53f7\u300d\u7684\u8def\u7531\u4fe1\u606f<br \/>\n                    \u2502  id: 1,500   \u2502<br \/>\n                    \u2514\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2518<br \/>\n                        \u2502      \u2502<br \/>\n          \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518      \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510<br \/>\n          \u25bc                                  \u25bc<br \/>\n \u250c\u2500\u2500\u2500 \u53f6\u5b50\u9875&#xff08;16KB&#xff09;\u2500\u2500\u2500\u2510           \u250c\u2500\u2500\u2500 \u53f6\u5b50\u9875&#xff08;16KB&#xff09;\u2500\u2500\u2500\u2510<br \/>\n \u2502 id&#061;1  | \u5b8c\u6574\u4e00\u884c\u6570\u636e \u2502 \u2500\u2500next\u2500\u2500\u25b6 \u2502 id&#061;500| \u5b8c\u6574\u4e00\u884c\u6570\u636e \u2502<br \/>\n \u2502 id&#061;2  | \u5b8c\u6574\u4e00\u884c\u6570\u636e \u2502           \u2502 id&#061;501| \u5b8c\u6574\u4e00\u884c\u6570\u636e \u2502<br \/>\n \u2502 &#8230;                \u2502           \u2502 &#8230;                \u2502<br \/>\n \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518           \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/p>\n<p>\u5173\u952e\u8ba4\u77e5&#xff1a;\u8bfb\u6570\u636e\u7684\u6700\u5c0f\u4ee3\u4ef7\u5355\u4f4d\u662f\u300c\u8bfb\u4e00\u4e2a 16KB \u7684\u9875\u300d&#xff0c;\u4e0d\u662f\u300c\u8bfb\u4e00\u884c\u300d\u3002<\/p>\n<p>\u6240\u4ee5\u6027\u80fd\u7684\u672c\u8d28\u95ee\u9898\u53d8\u6210\u4e86&#xff1a;\u4e00\u4e2a\u9875\u91cc\u80fd\u585e\u4e0b\u591a\u5c11\u884c&#xff1f; \u585e\u5f97\u8d8a\u591a&#xff0c;\u626b\u63cf 1000 \u884c\u9700\u8981\u8bfb\u7684\u9875\u5c31\u8d8a\u5c11&#xff0c;\u5c31\u8d8a\u5feb\u3002<\/p>\n<p>\u800c\u300c\u4e00\u884c\u6709\u591a\u80d6\u300d\u76f4\u63a5\u51b3\u5b9a\u4e86\u8fd9\u4e2a\u6570\u5b57\u3002\u8bb0\u4f4f\u8fd9\u6761\u7ebf\u7d22&#xff0c;\u540e\u9762\u5168\u9760\u5b83\u3002<\/p>\n<hr \/>\n<h3>\u56db\u3001\u4e00\u4e2a\u9875 16KB&#xff0c;\u4e00\u7bc7\u6587\u7ae0 3 \u4e07\u5b57&#xff0c;\u600e\u4e48\u529e<\/h3>\n<h4>4.1 \u4e00\u6761\u786c\u6027\u89c4\u5b9a<\/h4>\n<p>InnoDB \u8981\u6c42&#xff1a;\u6bcf\u4e2a\u6570\u636e\u9875\u81f3\u5c11\u8981\u80fd\u653e\u4e0b 2 \u884c\u3002<\/p>\n<p>\u4e3a\u4ec0\u4e48&#xff1f;\u56e0\u4e3a\u5982\u679c\u4e00\u4e2a\u9875\u53ea\u80fd\u653e 1 \u884c&#xff0c;B&#043; \u6811\u5c31\u9000\u5316\u6210\u4e86\u94fe\u8868&#xff0c;\u7d22\u5f15\u5c42\u7ea7\u4f1a\u7206\u70b8\u5f0f\u589e\u957f&#xff0c;\u5f7b\u5e95\u5931\u53bb\u610f\u4e49\u3002<\/p>\n<p>\u4e8e\u662f\u5355\u884c\u6709\u4e86\u4e0a\u9650&#xff1a;<\/p>\n<p>16384 \u5b57\u8282 \u00f7 2 \u2248 8192 \u5b57\u8282<br \/>\n\u518d\u6263\u6389\u9875\u5934\u3001\u9875\u5c3e\u3001\u884c\u6307\u9488\u69fd\u7b49\u7ba1\u7406\u5f00\u9500<br \/>\n\u2192 \u5355\u884c\u5b9e\u9645\u4e0a\u9650 \u2248 8126 \u5b57\u8282<\/p>\n<p>\u4f60\u5927\u6982\u89c1\u8fc7\u8fd9\u4e2a\u62a5\u9519&#xff1a;<\/p>\n<p>ERROR 1118 (42000): Row size too large (&gt; 8126).<br \/>\nChanging some columns to TEXT or BLOB may help.<\/p>\n<p>\u73b0\u5728\u4f60\u77e5\u9053 8126 \u8fd9\u4e2a\u6570\u5b57\u662f\u600e\u4e48\u6765\u7684\u4e86\u3002<\/p>\n<h4>4.2 \u95ee\u9898\u6765\u4e86<\/h4>\n<p>\u4e00\u7bc7 3000 \u5b57\u7684\u6587\u7ae0&#xff1a;<\/p>\n<p>3000 \u4e2a\u6c49\u5b57 \u00d7 utf8mb4 \u6700\u591a 4 \u5b57\u8282\/\u5b57 &#061; 12000 \u5b57\u8282<br \/>\n\u2192 \u5df2\u7ecf\u8d85\u8fc7 8126 \u7684\u5355\u884c\u4e0a\u9650<\/p>\n<p>\u5982\u679c InnoDB \u6b7b\u5b88\u300c\u6574\u884c\u5fc5\u987b\u653e\u8fdb\u4e00\u4e2a\u9875\u300d&#xff0c;\u8fd9\u6761\u6570\u636e\u6839\u672c\u5b58\u4e0d\u4e0b\u3002<\/p>\n<h4>4.3 InnoDB \u7684\u89e3\u6cd5&#xff1a;\u642c\u8d70&#xff0c;\u53ea\u7559\u4e2a\u5730\u5740<\/h4>\n<p>\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500 \u805a\u7c07\u7d22\u5f15\u53f6\u5b50\u9875&#xff08;16KB&#xff09;\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510<br \/>\n\u2502  \u884c1: id&#061;1001 | user_id&#061;55 | cate_id&#061;3      \u2502<br \/>\n\u2502        like_count&#061;120 | created_at&#061;&#8230;      \u2502<br \/>\n\u2502        content \u2192 &#xff3b;20 \u5b57\u8282\u6307\u9488&#xff3d;\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510  \u2502<br \/>\n\u2502  \u884c2: id&#061;1002 | &#8230;                      \u2502  \u2502<br \/>\n\u2502  \u884c3: id&#061;1003 | &#8230;                      \u2502  \u2502<br \/>\n\u2502  &#8230;                                     \u2502  \u2502<br \/>\n\u2502  \u2191 \u56e0\u4e3a\u5927\u5b57\u6bb5\u642c\u8d70\u4e86&#xff0c;\u8fd9\u4e2a\u9875\u80fd\u585e\u4e0b\u5f88\u591a\u884c     \u2502  \u2502<br \/>\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2518<br \/>\n                                           \u2502<br \/>\n                                           \u25bc<br \/>\n                \u250c\u2500\u2500\u2500\u2500 \u6ea2\u51fa\u9875 \/ BLOB Page&#xff08;16KB&#xff09;\u2500\u2500\u2500\u2500\u2510<br \/>\n                \u2502  \u300c\u8fd9\u662f\u6587\u7ae0\u7684\u6b63\u6587\u5185\u5bb9&#xff0c;\u53ef\u80fd\u6709\u51e0\u5343   \u2502<br \/>\n                \u2502   \u5b57\u751a\u81f3\u51e0\u4e07\u5b57\u2026\u2026\u300d                \u2502<br \/>\n                \u2502  next_page \u2192 NULL \u6216\u4e0b\u4e00\u4e2a\u6ea2\u51fa\u9875   \u2502<br \/>\n                \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/p>\n<p>\u8fd9\u5c31\u662f\u300c\u884c\u5916\u5b58\u50a8\u300d\u3002 \u5927\u5b57\u6bb5\u88ab\u642c\u5230\u72ec\u7acb\u7684 BLOB \u9875&#xff0c;\u884c\u5185\u53ea\u7559\u4e00\u4e2a 20 \u5b57\u8282\u7684\u300c\u5730\u5740\u300d\u3002<\/p>\n<h4>4.4 20 \u5b57\u8282\u6307\u9488\u91cc\u88c5\u4e86\u4ec0\u4e48<\/h4>\n<p>\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510<br \/>\n\u2502 space_id \u2502 page_no  \u2502  offset  \u2502   \u5b57\u6bb5\u603b\u957f\u5ea6     \u2502<br \/>\n\u2502  4 \u5b57\u8282   \u2502  4 \u5b57\u8282   \u2502  4 \u5b57\u8282   \u2502     8 \u5b57\u8282      \u2502<br \/>\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<br \/>\n  \u8868\u7a7a\u95f4ID   \u9996\u4e2a\u6ea2\u51fa\u9875  \u9875\u5185\u504f\u79fb    \u7528\u4e8e\u9884\u5206\u914d\u7f13\u51b2\u533a<\/p>\n<h4>4.5 \u4e00\u4e2a\u5927\u5b57\u6bb5\u53ef\u80fd\u4e0d\u6b62\u4e00\u4e2a\u6ea2\u51fa\u9875<\/h4>\n<p>\u6ea2\u51fa\u9875\u4e5f\u662f 16KB&#xff0c;\u88c5\u4e0d\u4e0b\u5c31\u94fe\u5f0f\u4e32\u8d77\u6765&#xff1a;<\/p>\n<p>\u884c\u5185 20 \u5b57\u8282\u6307\u9488<br \/>\n  \u2514\u2500\u25b6 BLOB \u9875 #1 \u2500\u2500next\u2500\u2500\u25b6 BLOB \u9875 #2 \u2500\u2500next\u2500\u2500\u25b6 BLOB \u9875 #3 \u2500\u2500\u25b6 NULL<\/p>\n<p>\u6240\u4ee5\u5f00\u9500\u4e0d\u662f\u56fa\u5b9a\u4e00\u6b21&#xff0c;\u800c\u662f\u548c\u5b57\u6bb5\u957f\u5ea6\u6210\u6b63\u6bd4&#xff1a;<\/p>\n<p>content &#061; 5 \u5343\u5b57&#xff08;\u7ea6 15KB&#xff09;  \u2192 1 \u4e2a\u6ea2\u51fa\u9875   \u2192 1 \u6b21\u989d\u5916 I\/O<br \/>\ncontent &#061; 5 \u4e07\u5b57&#xff08;\u7ea6 150KB&#xff09; \u2192 \u7ea6 10 \u4e2a\u6ea2\u51fa\u9875 \u2192 \u7ea6 10 \u6b21\u989d\u5916 I\/O&#xff08;\u800c\u4e14\u662f\u4e32\u884c\u8df3\u8f6c&#xff09;<\/p>\n<h4>4.6 \u8fd9\u4e2a\u673a\u5236\u6709\u4e2a\u597d\u7684\u526f\u4f5c\u7528<\/h4>\n<p>\u56e0\u4e3a\u5927\u5b57\u6bb5\u88ab\u642c\u8d70\u4e86&#xff0c;\u884c\u53d8\u7626\u4e86&#xff0c;\u4e00\u4e2a\u53f6\u5b50\u9875\u80fd\u88c5\u7684\u884c\u6570\u5927\u5e45\u589e\u52a0\u3002<\/p>\n<p>\u5047\u8bbe\u4e00\u884c\u7684\u5c0f\u5b57\u6bb5\u52a0\u8d77\u6765 100 \u5b57\u8282&#xff1a;<\/p>\n<p>\u5927\u5b57\u6bb5\u642c\u8d70\u540e&#xff1a;\u4e00\u4e2a\u9875 \u2248 16384 \/ 120 \u2248 136 \u884c<br \/>\n\u5982\u679c\u4e0d\u642c\u8d70&#xff1a;  \u4e00\u4e2a\u9875 \u2248 2 \u884c&#xff08;\u88ab\u5355\u884c\u4e0a\u9650\u9650\u5236&#xff09;<\/p>\n<p>\u8fd9\u89e3\u91ca\u4e86\u4e3a\u4ec0\u4e48\u672c\u6587\u5f00\u5934\u7684\u67e5\u8be2 A \u90a3\u4e48\u5feb\u2014\u2014\u5b83\u53ea\u53d6\u5c0f\u5b57\u6bb5&#xff0c;content \u7684\u6ea2\u51fa\u9875\u538b\u6839\u6ca1\u88ab\u78b0\u8fc7&#xff0c;\u800c\u4e14\u6bcf\u8bfb\u4e00\u4e2a\u9875\u5c31\u80fd\u62ff\u5230 100 \u591a\u884c\u3002<\/p>\n<hr \/>\n<h3>\u4e94\u3001\u884c\u683c\u5f0f&#xff1a;\u51b3\u5b9a\u300c\u632a\u591a\u5c11\u300d\u7684\u9690\u85cf\u5f00\u5173<\/h3>\n<p>\u8fd9\u4e00\u8282\u662f\u5b9e\u6218\u4e2d\u6700\u5bb9\u6613\u8e29\u5751\u3001\u4e5f\u6700\u5bb9\u6613\u88ab\u5ffd\u7565\u7684\u5730\u65b9\u3002<\/p>\n<h4>5.1 \u56db\u79cd\u884c\u683c\u5f0f<\/h4>\n<p>InnoDB \u6709\u56db\u79cd\u884c\u683c\u5f0f&#xff0c;\u5bf9\u5927\u5b57\u6bb5\u7684\u5904\u7406\u65b9\u5f0f\u5dee\u522b\u5de8\u5927&#xff1a;<\/p>\n<table>\n<tr>\u884c\u683c\u5f0f\u884c\u5185\u4fdd\u7559\u4ec0\u4e48\u9ed8\u8ba4\u4e8e\u8bc4\u4ef7<\/tr>\n<tbody>\n<tr>\n<td>REDUNDANT<\/td>\n<td>\u524d 768 \u5b57\u8282 &#043; 20 \u5b57\u8282\u6307\u9488<\/td>\n<td>\u5f88\u8001\u7684\u7248\u672c<\/td>\n<td>\u5df2\u6dd8\u6c70<\/td>\n<\/tr>\n<tr>\n<td>COMPACT<\/td>\n<td>\u524d 768 \u5b57\u8282 &#043; 20 \u5b57\u8282\u6307\u9488<\/td>\n<td>MySQL 5.0 &#8211; 5.6<\/td>\n<td>\u26a0\ufe0f \u8001\u8868\u91cc\u5927\u91cf\u5b58\u5728<\/td>\n<\/tr>\n<tr>\n<td>DYNAMIC<\/td>\n<td>\u53ea\u6709 20 \u5b57\u8282\u6307\u9488&#xff08;\u6574\u5217\u642c\u8d70&#xff09;<\/td>\n<td>MySQL 5.7&#043;<\/td>\n<td>\u2705 \u63a8\u8350<\/td>\n<\/tr>\n<tr>\n<td>COMPRESSED<\/td>\n<td>20 \u5b57\u8282\u6307\u9488 &#043; \u9875\u538b\u7f29<\/td>\n<td>\u2014<\/td>\n<td>\u7701\u7a7a\u95f4&#xff0c;\u8d39 CPU<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>5.2 COMPACT \u7684 768 \u5b57\u8282\u524d\u7f00\u5751<\/h4>\n<p>\u8fd9\u662f\u6700\u9634\u9669\u7684\u4e00\u4e2a\u70b9\u3002\u5728 COMPACT \u683c\u5f0f\u4e0b&#xff0c;\u6bcf\u4e2a\u5927\u5b57\u6bb5\u90fd\u8981\u5728\u884c\u5185\u7559 768 \u5b57\u8282\u7684\u524d\u7f00&#xff1a;<\/p>\n<p>\u250c\u2500\u2500\u2500\u2500 \u805a\u7c07\u7d22\u5f15\u53f6\u5b50\u9875&#xff08;16KB&#xff09;\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510<br \/>\n\u2502 \u884c1: id | user_id | &#8230; | content \u524d 768B  \u2502  \u2190 \u767d\u767d\u5403\u6389\u884c\u5185\u7a7a\u95f4&#xff01;<br \/>\n\u2502                          &#043; 20B \u6307\u9488        \u2502<br \/>\n\u2502 \u884c2: &#8230;                                   \u2502<br \/>\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/p>\n<p>\u540e\u679c\u975e\u5e38\u4e25\u91cd\u3002\u5047\u8bbe\u4f60\u7684\u8868\u6709 3 \u4e2a TEXT \u5b57\u6bb5&#xff08;\u6bd4\u5982 content\u3001content_html\u3001summary&#xff09;&#xff1a;<\/p>\n<p>\u5149\u524d\u7f00\u5c31\u5360\u7528 768 \u00d7 3 &#061; 2304 \u5b57\u8282&#xff0c;\u7ea6\u7b49\u4e8e\u5355\u884c\u4e0a\u9650\u7684 28%<\/p>\n<p>\u884c\u53d8\u5f97\u6781\u80d6 \u2192 \u4e00\u4e2a\u9875\u80fd\u88c5\u7684\u884c\u6570\u66b4\u8dcc \u2192 \u54ea\u6015\u4f60\u53ea\u67e5 SELECT id&#xff0c;\u4e5f\u4f1a\u53d8\u6162&#xff08;\u56e0\u4e3a\u626b\u63cf\u540c\u6837\u884c\u6570\u8981\u8bfb\u66f4\u591a\u9875&#xff09;\u3002<\/p>\n<p>\u7c97\u7b97\u4e00\u4e0b&#xff08;\u5047\u8bbe\u5c0f\u5b57\u6bb5\u5408\u8ba1\u7ea6 100 \u5b57\u8282&#xff0c;\u9875\u5185\u53ef\u7528\u7ea6 16200 \u5b57\u8282&#xff09;&#xff1a;<\/p>\n<p>COMPACT  &#043; 3 \u4e2a TEXT&#xff1a;\u884c\u5bbd \u2248 100 &#043; 3\u00d7(768&#043;20) &#061; 2464 \u5b57\u8282 \u2192 \u4e00\u9875\u7ea6 6 \u884c<br \/>\nDYNAMIC  &#043; 3 \u4e2a TEXT&#xff1a;\u884c\u5bbd \u2248 100 &#043; 3\u00d720      &#061;  160 \u5b57\u8282 \u2192 \u4e00\u9875\u7ea6 100 \u884c<\/p>\n<p>\u540c\u4e00\u5f20\u8868\u3001\u540c\u4e00\u6761\u67e5\u8be2&#xff0c;\u53ea\u56e0\u4e3a\u884c\u683c\u5f0f\u4e0d\u540c&#xff0c;\u8981\u8bfb\u7684\u9875\u6570\u53ef\u80fd\u5dee\u5341\u51e0\u500d\u3002 \u800c\u8fd9\u4ef6\u4e8b EXPLAIN \u5b8c\u5168\u4e0d\u4f1a\u63d0\u9192\u4f60\u3002<\/p>\n<h4>5.3 \u68c0\u67e5\u5e76\u4fee\u590d<\/h4>\n<p>\u5148\u67e5\u6e05\u695a\u4f60\u7684\u8868\u662f\u4ec0\u4e48\u683c\u5f0f&#xff1a;<\/p>\n<p><span class=\"token keyword\">SELECT<\/span> TABLE_NAME<span class=\"token punctuation\">,<\/span><br \/>\n       ROW_FORMAT<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token keyword\">ENGINE<\/span><span class=\"token punctuation\">,<\/span><br \/>\n       TABLE_ROWS<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">ROUND<\/span><span class=\"token punctuation\">(<\/span>DATA_LENGTH  <span class=\"token operator\">\/<\/span> <span class=\"token number\">1024<\/span> <span class=\"token operator\">\/<\/span> <span class=\"token number\">1024<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> data_mb<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">ROUND<\/span><span class=\"token punctuation\">(<\/span>INDEX_LENGTH <span class=\"token operator\">\/<\/span> <span class=\"token number\">1024<\/span> <span class=\"token operator\">\/<\/span> <span class=\"token number\">1024<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> index_mb<br \/>\n<span class=\"token keyword\">FROM<\/span> information_schema<span class=\"token punctuation\">.<\/span><span class=\"token keyword\">TABLES<\/span><br \/>\n<span class=\"token keyword\">WHERE<\/span> TABLE_SCHEMA <span class=\"token operator\">&#061;<\/span> <span class=\"token keyword\">DATABASE<\/span><span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">ORDER<\/span> <span class=\"token keyword\">BY<\/span> DATA_LENGTH <span class=\"token keyword\">DESC<\/span><br \/>\n<span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">20<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u5982\u679c\u770b\u5230 COMPACT&#xff0c;\u800c\u8fd9\u5f20\u8868\u6709\u591a\u4e2a\u5927\u5b57\u6bb5&#xff0c;\u8003\u8651\u6539\u6389&#xff1a;<\/p>\n<p><span class=\"token comment\">&#8212; \u6ce8\u610f&#xff1a;\u8fd9\u662f\u91cd\u5efa\u8868\u64cd\u4f5c&#xff0c;\u5927\u8868\u52a1\u5fc5\u5728\u4f4e\u5cf0\u671f\u6267\u884c\u5e76\u8bc4\u4f30\u8017\u65f6<\/span><br \/>\n<span class=\"token comment\">&#8212; MySQL 5.6&#043; \u652f\u6301 ONLINE DDL&#xff0c;\u4f46\u4ecd\u4f1a\u4ea7\u751f\u5927\u91cf I\/O \u548c redo<\/span><br \/>\n<span class=\"token keyword\">ALTER<\/span> <span class=\"token keyword\">TABLE<\/span> t_article ROW_FORMAT<span class=\"token operator\">&#061;<\/span>DYNAMIC<span class=\"token punctuation\">,<\/span> <span class=\"token keyword\">ALGORITHM<\/span><span class=\"token operator\">&#061;<\/span>INPLACE<span class=\"token punctuation\">,<\/span> <span class=\"token keyword\">LOCK<\/span><span class=\"token operator\">&#061;<\/span>NONE<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u5efa\u65b0\u8868\u65f6\u76f4\u63a5\u6307\u5b9a\u6e05\u695a&#xff0c;\u522b\u4f9d\u8d56\u9ed8\u8ba4\u503c&#xff1a;<\/p>\n<p><span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> t_article <span class=\"token punctuation\">(<\/span><br \/>\n  id          <span class=\"token keyword\">BIGINT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">AUTO_INCREMENT<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  user_id     <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  cate_id     <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  title       <span class=\"token keyword\">VARCHAR<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">255<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token string\">&#039;&#039;<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  content     <span class=\"token keyword\">LONGTEXT<\/span><span class=\"token punctuation\">,<\/span>                          <span class=\"token comment\">&#8212; \u5927\u5b57\u6bb5<\/span><br \/>\n  like_count  <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  created_at  <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  <span class=\"token keyword\">PRIMARY<\/span> <span class=\"token keyword\">KEY<\/span> <span class=\"token punctuation\">(<\/span>id<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  <span class=\"token keyword\">KEY<\/span> idx_cate_time <span class=\"token punctuation\">(<\/span>cate_id<span class=\"token punctuation\">,<\/span> created_at<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">ENGINE<\/span><span class=\"token operator\">&#061;<\/span><span class=\"token keyword\">InnoDB<\/span><br \/>\n  <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token keyword\">CHARSET<\/span><span class=\"token operator\">&#061;<\/span>utf8mb4<br \/>\n  ROW_FORMAT<span class=\"token operator\">&#061;<\/span>DYNAMIC<span class=\"token punctuation\">;<\/span>                            <span class=\"token comment\">&#8212; \u2190 \u663e\u5f0f\u5199\u51fa\u6765<\/span><\/p>\n<h4>5.4 \u4e00\u4e2a\u5e38\u89c1\u8bef\u89e3&#xff1a;\u300cTEXT \u4e00\u5b9a\u884c\u5916&#xff0c;VARCHAR \u4e00\u5b9a\u884c\u5185\u300d<\/h4>\n<p>\u90fd\u4e0d\u5bf9\u3002<\/p>\n<p>DYNAMIC \u4e4b\u6240\u4ee5\u53eb dynamic&#xff0c;\u662f\u56e0\u4e3a\u5b83\u6309\u6574\u884c\u662f\u5426\u8d85\u9650\u52a8\u6001\u51b3\u5b9a&#xff1a;<\/p>\n<p>\u6574\u884c\u80fd\u653e\u8fdb 16KB \u9875\u5417&#xff1f;<br \/>\n \u251c\u2500 \u80fd \u2192 \u6240\u6709\u5b57\u6bb5\u90fd\u7559\u5728\u884c\u5185&#xff08;\u54ea\u6015\u5b83\u662f TEXT \u7c7b\u578b&#xff09;<br \/>\n \u2514\u2500 \u4e0d\u80fd \u2192 \u6311\u5f53\u524d\u6700\u957f\u7684\u90a3\u4e2a\u53d8\u957f\u5217&#xff0c;\u6574\u5217\u642c\u5230\u6ea2\u51fa\u9875&#xff0c;\u884c\u5185\u7559 20 \u5b57\u8282\u6307\u9488<br \/>\n            \u2192 \u642c\u5b8c\u8fd8\u653e\u4e0d\u4e0b&#xff1f;\u7ee7\u7eed\u6311\u4e0b\u4e00\u4e2a\u6700\u957f\u7684\u642c<br \/>\n            \u2192 \u76f4\u5230\u6574\u884c\u80fd\u653e\u8fdb\u9875\u5185\u4e3a\u6b62<\/p>\n<p>\u4e24\u4e2a\u76f4\u63a5\u63a8\u8bba&#xff1a;<\/p>\n<table>\n<tr>\u60c5\u51b5\u5b9e\u9645\u7ed3\u679c<\/tr>\n<tbody>\n<tr>\n<td>content LONGTEXT \u91cc\u53ea\u5b58\u4e86 3 \u4e2a\u5b57<\/td>\n<td>\u7559\u5728\u884c\u5185&#xff0c;\u53d6\u5b83\u4e0d\u4ea7\u751f\u4efb\u4f55\u989d\u5916 I\/O<\/td>\n<\/tr>\n<tr>\n<td>VARCHAR(2000) \u5b58\u6ee1\u4e86\u4e2d\u6587<\/td>\n<td>\u53ef\u80fd\u88ab\u642c\u5230\u884c\u5916&#xff08;2000 \u00d7 4 &#061; 8000 \u5b57\u8282&#xff09;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u6240\u4ee5\u300c\u53d6\u4e00\u884c\u7684 content \u8981\u4e0d\u8981\u591a\u8bfb\u4e00\u6b21\u9875\u300d&#xff0c;\u53d6\u51b3\u4e8e\u8fd9\u4e00\u884c\u5177\u4f53\u6709\u591a\u957f\u3002<\/p>\n<p>\u8fd9\u5e26\u6765\u4e00\u4e2a\u5f88\u70e6\u4eba\u7684\u73b0\u8c61&#xff1a;<\/p>\n<p>\u540c\u4e00\u5f20\u8868\u3001\u540c\u4e00\u6761 SQL\u3001\u540c\u6837\u53d6 1000 \u884c&#xff0c;\u4eca\u5929\u8dd1 10ms&#xff0c;\u660e\u5929\u8dd1 300ms\u3002<\/p>\n<p>\u56e0\u4e3a\u4eca\u5929\u635e\u5230\u7684\u662f\u4e00\u6279\u77ed\u8bc4\u8bba&#xff08;\u90fd\u5728\u884c\u5185&#xff09;&#xff0c;\u660e\u5929\u635e\u5230\u7684\u662f\u4e00\u6279\u957f\u6587\u7ae0&#xff08;\u5168\u5728\u884c\u5916&#xff09;\u3002<\/p>\n<p>\u5982\u679c\u4f60\u7684\u8868\u91cc\u65e2\u6709\u300c3 \u4e2a\u5b57\u7684\u77ed\u8bc4\u8bba\u300d\u53c8\u6709\u300c3 \u4e07\u5b57\u7684\u957f\u6587\u300d&#xff0c;\u90a3\u6279\u5904\u7406\u4efb\u52a1\u7684\u8017\u65f6\u6ce2\u52a8\u4f1a\u975e\u5e38\u5927\u3002\u8fd9\u65f6\u5019\u6309\u300c\u884c\u6570\u300d\u5207\u6279\u6b21\u662f\u9519\u7684&#xff0c;\u5e94\u8be5\u6309\u300c\u5185\u5bb9\u603b\u5b57\u8282\u6570\u300d\u5207\u3002 \u540e\u9762\u7b2c\u516b\u8282\u6709\u4ee3\u7801\u3002<\/p>\n<hr \/>\n<h3>\u516d\u3001\u4eb2\u624b\u505a\u4e2a\u5b9e\u9a8c&#xff0c;\u770b\u89c1\u8fd9\u4ef6\u4e8b<\/h3>\n<p>\u5149\u8bf4\u4e0d\u7ec3\u6ca1\u611f\u89c9\u3002\u4e0b\u9762\u8fd9\u6bb5\u53ef\u4ee5\u76f4\u63a5\u8dd1&#xff08;MySQL 8.0&#xff0c;5.7 \u8bf7\u628a\u9012\u5f52 CTE \u6362\u6210\u5faa\u73af\u5b58\u50a8\u8fc7\u7a0b&#xff09;\u3002<\/p>\n<h4>6.1 \u51c6\u5907\u4e24\u5f20\u8868<\/h4>\n<p>\u540c\u6837\u7684\u5c0f\u5b57\u6bb5&#xff0c;\u533a\u522b\u53ea\u5728\u4e8e\u6709\u6ca1\u6709\u5927\u5b57\u6bb5&#xff1a;<\/p>\n<p><span class=\"token keyword\">DROP<\/span> <span class=\"token keyword\">TABLE<\/span> <span class=\"token keyword\">IF<\/span> <span class=\"token keyword\">EXISTS<\/span> t_with_text<span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">DROP<\/span> <span class=\"token keyword\">TABLE<\/span> <span class=\"token keyword\">IF<\/span> <span class=\"token keyword\">EXISTS<\/span> t_without_text<span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u8868 1&#xff1a;\u5e26\u4e00\u4e2a\u5927\u5b57\u6bb5<\/span><br \/>\n<span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> t_with_text <span class=\"token punctuation\">(<\/span><br \/>\n  id         <span class=\"token keyword\">BIGINT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">AUTO_INCREMENT<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  cate_id    <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  like_count <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  content    <span class=\"token keyword\">LONGTEXT<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  <span class=\"token keyword\">PRIMARY<\/span> <span class=\"token keyword\">KEY<\/span> <span class=\"token punctuation\">(<\/span>id<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  <span class=\"token keyword\">KEY<\/span> idx_cate <span class=\"token punctuation\">(<\/span>cate_id<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">ENGINE<\/span><span class=\"token operator\">&#061;<\/span><span class=\"token keyword\">InnoDB<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token keyword\">CHARSET<\/span><span class=\"token operator\">&#061;<\/span>utf8mb4 ROW_FORMAT<span class=\"token operator\">&#061;<\/span>DYNAMIC<span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u8868 2&#xff1a;\u4e0d\u5e26\u5927\u5b57\u6bb5&#xff08;\u7ed3\u6784\u5176\u4f59\u5b8c\u5168\u4e00\u81f4&#xff09;<\/span><br \/>\n<span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> t_without_text <span class=\"token punctuation\">(<\/span><br \/>\n  id         <span class=\"token keyword\">BIGINT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">AUTO_INCREMENT<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  cate_id    <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  like_count <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  <span class=\"token keyword\">PRIMARY<\/span> <span class=\"token keyword\">KEY<\/span> <span class=\"token punctuation\">(<\/span>id<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  <span class=\"token keyword\">KEY<\/span> idx_cate <span class=\"token punctuation\">(<\/span>cate_id<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">ENGINE<\/span><span class=\"token operator\">&#061;<\/span><span class=\"token keyword\">InnoDB<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token keyword\">CHARSET<\/span><span class=\"token operator\">&#061;<\/span>utf8mb4 ROW_FORMAT<span class=\"token operator\">&#061;<\/span>DYNAMIC<span class=\"token punctuation\">;<\/span><\/p>\n<h4>6.2 \u704c 5 \u4e07\u884c\u6570\u636e&#xff0c;\u6bcf\u884c\u6b63\u6587\u7ea6 9.4KB<\/h4>\n<p><span class=\"token comment\">&#8212; \u7528\u9012\u5f52 CTE \u9020\u6570\u636e&#xff08;MySQL 8.0&#xff09;<\/span><br \/>\n<span class=\"token keyword\">SET<\/span> <span class=\"token keyword\">SESSION<\/span> cte_max_recursion_depth <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">100000<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token keyword\">INSERT<\/span> <span class=\"token keyword\">INTO<\/span> t_with_text <span class=\"token punctuation\">(<\/span>cate_id<span class=\"token punctuation\">,<\/span> like_count<span class=\"token punctuation\">,<\/span> content<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">WITH<\/span> RECURSIVE seq<span class=\"token punctuation\">(<\/span>n<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> <span class=\"token punctuation\">(<\/span><br \/>\n  <span class=\"token keyword\">SELECT<\/span> <span class=\"token number\">1<\/span><br \/>\n  <span class=\"token keyword\">UNION<\/span> <span class=\"token keyword\">ALL<\/span><br \/>\n  <span class=\"token keyword\">SELECT<\/span> n <span class=\"token operator\">&#043;<\/span> <span class=\"token number\">1<\/span> <span class=\"token keyword\">FROM<\/span> seq <span class=\"token keyword\">WHERE<\/span> n <span class=\"token operator\">&lt;<\/span> <span class=\"token number\">50000<\/span><br \/>\n<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> n <span class=\"token operator\">%<\/span> <span class=\"token number\">10<\/span><span class=\"token punctuation\">,<\/span><br \/>\n       n <span class=\"token operator\">%<\/span> <span class=\"token number\">1000<\/span><span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token comment\">&#8212; 16 \u4e2a\u6c49\u5b57 \u00d7 3 \u5b57\u8282 \u00d7 200 &#061; 9600 \u5b57\u8282&#xff0c;\u5df2\u8d85\u8fc7 8126 \u4e0a\u9650&#xff0c;\u5fc5\u7136\u884c\u5916<\/span><br \/>\n       <span class=\"token keyword\">REPEAT<\/span><span class=\"token punctuation\">(<\/span><span class=\"token string\">&#039;\u8fd9\u662f\u4e00\u6bb5\u7528\u6765\u6491\u5927\u6b63\u6587\u7684\u6d4b\u8bd5\u6587\u672c\u3002&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">200<\/span><span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">FROM<\/span> seq<span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token keyword\">INSERT<\/span> <span class=\"token keyword\">INTO<\/span> t_without_text <span class=\"token punctuation\">(<\/span>cate_id<span class=\"token punctuation\">,<\/span> like_count<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> cate_id<span class=\"token punctuation\">,<\/span> like_count <span class=\"token keyword\">FROM<\/span> t_with_text<span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token keyword\">ANALYZE<\/span> <span class=\"token keyword\">TABLE<\/span> t_with_text<span class=\"token punctuation\">,<\/span> t_without_text<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u9a8c\u7b97\u4e00\u4e0b&#xff1a;SELECT LENGTH(content) FROM t_with_text LIMIT 1; \u5e94\u8be5\u8fd4\u56de 9600\u3002 LENGTH() \u7b97\u7684\u662f\u5b57\u8282&#xff0c;CHAR_LENGTH() \u7b97\u5b57\u7b26&#xff0c;\u522b\u641e\u6df7\u3002<\/p>\n<h4>6.3 \u770b\u770b\u4e24\u5f20\u8868\u5360\u4e86\u591a\u5c11\u7a7a\u95f4<\/h4>\n<p><span class=\"token keyword\">SELECT<\/span> TABLE_NAME<span class=\"token punctuation\">,<\/span><br \/>\n       TABLE_ROWS<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">ROUND<\/span><span class=\"token punctuation\">(<\/span>DATA_LENGTH <span class=\"token operator\">\/<\/span> <span class=\"token number\">1024<\/span> <span class=\"token operator\">\/<\/span> <span class=\"token number\">1024<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">1<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> data_mb<br \/>\n<span class=\"token keyword\">FROM<\/span> information_schema<span class=\"token punctuation\">.<\/span><span class=\"token keyword\">TABLES<\/span><br \/>\n<span class=\"token keyword\">WHERE<\/span> TABLE_SCHEMA <span class=\"token operator\">&#061;<\/span> <span class=\"token keyword\">DATABASE<\/span><span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><br \/>\n  <span class=\"token operator\">AND<\/span> TABLE_NAME <span class=\"token operator\">IN<\/span> <span class=\"token punctuation\">(<\/span><span class=\"token string\">&#039;t_with_text&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#039;t_without_text&#039;<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u7ed3\u679c\u5927\u81f4\u662f\u8fd9\u6837&#xff08;\u6570\u503c\u56e0\u7248\u672c\u548c\u73af\u5883\u800c\u5f02&#xff0c;\u91cf\u7ea7\u624d\u662f\u91cd\u70b9&#xff09;&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;<br \/>\n| TABLE_NAME     | TABLE_ROWS | data_mb |<br \/>\n&#043;&#8212;&#8212;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;<br \/>\n| t_with_text    |      50000 |   780&#043;  |   \u2190 \u7edd\u5927\u90e8\u5206\u662f\u6ea2\u51fa\u9875<br \/>\n| t_without_text |      50000 |     1.7 |<br \/>\n&#043;&#8212;&#8212;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;<\/p>\n<p>\u8fd9\u91cc\u6709\u4e2a\u5f88\u591a\u4eba\u4e0d\u77e5\u9053\u7684\u7ec6\u8282&#xff0c;\u4e5f\u662f\u4e0a\u9762\u6570\u5b57\u7684\u6765\u6e90&#xff1a;<\/p>\n<p>\u6ea2\u51fa\u9875\u4e0d\u4f1a\u88ab\u591a\u884c\u5171\u7528\u3002 \u6bcf\u4e2a\u884c\u5916\u5b57\u6bb5\u90fd\u6709\u81ea\u5df1\u72ec\u7acb\u7684 BLOB \u9875\u94fe\u3002<\/p>\n<p>\u6240\u4ee5\u4e00\u4e2a 9600 \u5b57\u8282\u7684 content&#xff0c;\u7167\u6837\u8981\u72ec\u5360\u4e00\u4e2a\u5b8c\u6574\u7684 16KB \u9875&#xff0c;\u5269\u4e0b\u7684 6.7KB \u7eaf\u5c5e\u6d6a\u8d39\u3002<\/p>\n<p>50000 \u884c \u00d7 16KB \u2248 780MB\u2014\u2014\u8fd9\u5c31\u662f\u4e3a\u4ec0\u4e48\u5b9e\u9645\u5360\u7528\u6bd4\u300c5 \u4e07 \u00d7 9.4KB \u2248 470MB\u300d\u7684\u7406\u8bba\u503c\u5927\u4e86\u8fd1\u4e00\u500d\u3002<\/p>\n<p>\u987a\u5e26\u4e00\u4e2a\u5b9e\u8df5\u7ed3\u8bba&#xff1a;\u8ba9\u5927\u5b57\u6bb5\u7684\u957f\u5ea6\u521a\u597d\u7565\u5fae\u8d85\u8fc7\u884c\u5185\u4e0a\u9650&#xff0c;\u662f\u6700\u6d6a\u8d39\u7a7a\u95f4\u7684\u60c5\u51b5\u3002<\/p>\n<h4>6.4 \u5b9e\u9a8c\u4e00&#xff1a;\u53ea\u67e5\u5c0f\u5b57\u6bb5&#xff0c;\u5927\u5b57\u6bb5\u5b58\u4e0d\u5b58\u5728\u6709\u5f71\u54cd\u5417<\/h4>\n<p><span class=\"token comment\">&#8212; FLUSH STATUS \u7684\u4f5c\u7528\u662f\u628a\u672c\u4f1a\u8bdd\u7684\u72b6\u6001\u8ba1\u6570\u5668\u6e05\u96f6&#xff0c;\u548c\u67e5\u8be2\u7f13\u5b58\u65e0\u5173<\/span><br \/>\nFLUSH <span class=\"token keyword\">STATUS<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">SUM<\/span><span class=\"token punctuation\">(<\/span>like_count<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">FROM<\/span> t_with_text<span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> <span class=\"token keyword\">SESSION<\/span> <span class=\"token keyword\">STATUS<\/span> <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;Innodb_buffer_pool_read_requests&#039;<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>FLUSH <span class=\"token keyword\">STATUS<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">SUM<\/span><span class=\"token punctuation\">(<\/span>like_count<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">FROM<\/span> t_without_text<span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> <span class=\"token keyword\">SESSION<\/span> <span class=\"token keyword\">STATUS<\/span> <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;Innodb_buffer_pool_read_requests&#039;<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u4f60\u4f1a\u53d1\u73b0\u4e24\u8005\u5dee\u522b\u4e0d\u5927\u3002\u8fd9\u6b63\u662f\u884c\u5916\u5b58\u50a8\u7684\u597d\u5904&#xff1a;\u5927\u5b57\u6bb5\u642c\u8d70\u4e86&#xff0c;\u626b\u5c0f\u5b57\u6bb5\u51e0\u4e4e\u4e0d\u53d7\u5f71\u54cd\u3002<\/p>\n<p>\u4e3a\u4ec0\u4e48\u9009 Innodb_buffer_pool_read_requests \u8fd9\u4e2a\u6307\u6807&#xff1f;<\/p>\n<p>\u5b83\u7edf\u8ba1\u7684\u662f\u903b\u8f91\u9875\u8bbf\u95ee\u6b21\u6570&#xff08;\u547d\u4e2d\u5185\u5b58\u7684 &#043; \u9700\u8981\u4ece\u78c1\u76d8\u8bfb\u7684&#xff0c;\u90fd\u7b97&#xff09;\u3002<\/p>\n<p>\u6240\u4ee5\u5b83\u4e0d\u53d7\u7f13\u5b58\u662f\u5426\u9884\u70ed\u7684\u5f71\u54cd&#xff0c;\u6bd4\u76f4\u63a5\u770b\u8017\u65f6\u7a33\u5b9a\u5f97\u591a\u2014\u2014\u540e\u8005\u7b2c\u4e00\u6b21\u8dd1\u548c\u7b2c\u4e8c\u6b21\u8dd1\u53ef\u80fd\u5dee\u51e0\u5341\u500d\u3002<\/p>\n<p>\u5982\u679c\u4f60\u60f3\u770b\u771f\u6b63\u843d\u5230\u78c1\u76d8\u7684\u90e8\u5206&#xff0c;\u770b Innodb_buffer_pool_reads&#xff08;\u6ca1\u6709 _requests&#xff09;\u3002<\/p>\n<h4>6.5 \u5b9e\u9a8c\u4e8c&#xff1a;\u8fd9\u624d\u662f\u91cd\u70b9\u2014\u2014\u591a\u53d6\u4e00\u4e2a\u5927\u5b57\u6bb5<\/h4>\n<p>FLUSH <span class=\"token keyword\">STATUS<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> like_count <span class=\"token keyword\">FROM<\/span> t_with_text <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span> <span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">1000<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> <span class=\"token keyword\">SESSION<\/span> <span class=\"token keyword\">STATUS<\/span> <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;Innodb_buffer_pool_read_requests&#039;<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token comment\">&#8212; \u5047\u8bbe\u5f97\u5230 A &#061; 1200<\/span><\/p>\n<p>FLUSH <span class=\"token keyword\">STATUS<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> like_count<span class=\"token punctuation\">,<\/span> content <span class=\"token keyword\">FROM<\/span> t_with_text <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span> <span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">1000<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> <span class=\"token keyword\">SESSION<\/span> <span class=\"token keyword\">STATUS<\/span> <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;Innodb_buffer_pool_read_requests&#039;<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token comment\">&#8212; \u5047\u8bbe\u5f97\u5230 B &#061; 2400<\/span><\/p>\n<p>B &#8211; A \u5c31\u662f\u8bfb\u6ea2\u51fa\u9875\u5e26\u6765\u7684\u989d\u5916\u9875\u8bbf\u95ee\u6b21\u6570\u30021000 \u884c\u3001\u6bcf\u884c 9600 \u5b57\u8282&#xff0c;\u5dee\u503c\u5927\u81f4\u5c31\u662f 1000 \u4e0a\u4e0b&#xff08;\u6bcf\u884c\u6b63\u597d\u4e00\u4e2a\u6ea2\u51fa\u9875&#xff09;\u3002<\/p>\n<p>\u8fd9\u4e2a\u5dee\u503c&#xff0c;EXPLAIN \u4e00\u4e2a\u5b57\u90fd\u4e0d\u4f1a\u544a\u8bc9\u4f60\u3002<\/p>\n<h4>6.6 \u5b9e\u9a8c\u4e09&#xff1a;\u6700\u9690\u853d\u7684\u5751\u2014\u2014\u53bb\u91cd\/\u5206\u7ec4\u65f6\u5e26\u4e0a\u5927\u5b57\u6bb5<\/h4>\n<p>\u5148\u8bf4\u6e05\u4e00\u4e2a\u5f88\u591a\u6587\u7ae0\u8bb2\u9519\u7684\u524d\u63d0&#xff1a;<\/p>\n<p>\u5149\u6709 ORDER BY \u4e0d\u4f1a\u521b\u5efa\u4e34\u65f6\u8868&#xff0c;\u5b83\u8d70\u7684\u662f filesort&#xff08;\u6392\u5e8f\u7f13\u51b2\u533a&#xff0c;\u5fc5\u8981\u65f6\u6ea2\u51fa\u5230\u78c1\u76d8\u4e34\u65f6\u6587\u4ef6&#xff09;\u3002<\/p>\n<p>\u771f\u6b63\u9700\u8981\u4e34\u65f6\u8868\u7684\u662f DISTINCT\u3001GROUP BY\u3001UNION\u3001\u6d3e\u751f\u8868\u7269\u5316\u8fd9\u7c7b\u64cd\u4f5c\u3002<\/p>\n<p>\u6240\u4ee5\u5b9e\u9a8c\u8981\u7528 DISTINCT&#xff1a;<\/p>\n<p>FLUSH <span class=\"token keyword\">STATUS<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token keyword\">DISTINCT<\/span> cate_id<span class=\"token punctuation\">,<\/span> like_count <span class=\"token keyword\">FROM<\/span> t_with_text<span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> <span class=\"token keyword\">SESSION<\/span> <span class=\"token keyword\">STATUS<\/span> <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;Created_tmp%&#039;<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token comment\">&#8212; \u7ed3\u679c\u96c6\u4e0d\u542b\u5927\u5b57\u6bb5<\/span><\/p>\n<p>FLUSH <span class=\"token keyword\">STATUS<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token keyword\">DISTINCT<\/span> cate_id<span class=\"token punctuation\">,<\/span> like_count<span class=\"token punctuation\">,<\/span> content <span class=\"token keyword\">FROM<\/span> t_with_text<span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> <span class=\"token keyword\">SESSION<\/span> <span class=\"token keyword\">STATUS<\/span> <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;Created_tmp%&#039;<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token comment\">&#8212; \u7ed3\u679c\u96c6\u542b LONGTEXT<\/span><\/p>\n<p><span class=\"token comment\">&#8212; Created_tmp_tables        \u521b\u5efa\u7684\u4e34\u65f6\u8868\u603b\u6570<\/span><br \/>\n<span class=\"token comment\">&#8212; Created_tmp_disk_tables   \u5176\u4e2d\u843d\u5230\u78c1\u76d8\u7684\u6570\u91cf<\/span><\/p>\n<p>\u4f46\u662f&#xff01;\u8fd9\u91cc\u7684\u7ed3\u8bba\u53d6\u51b3\u4e8e\u4f60\u7684 MySQL \u7248\u672c&#xff0c;\u8fd9\u662f\u672c\u8282\u6700\u9700\u8981\u6ce8\u610f\u7684\u5730\u65b9&#xff1a;<\/p>\n<table>\n<tr>\u7248\u672c\u5185\u5b58\u4e34\u65f6\u8868\u5f15\u64ce\u7ed3\u679c\u96c6\u542b TEXT\/BLOB \u65f6<\/tr>\n<tbody>\n<tr>\n<td>5.7<\/td>\n<td>MEMORY<\/td>\n<td>&#x1f534; \u5fc5\u7136\u843d\u78c1\u76d8&#xff08;MEMORY \u5f15\u64ce\u6839\u672c\u4e0d\u652f\u6301 BLOB\/TEXT \u5217&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>8.0.13&#043;<\/td>\n<td>TempTable&#xff08;\u9ed8\u8ba4&#xff09;<\/td>\n<td>&#x1f7e1; \u53ef\u4ee5\u5148\u653e\u5185\u5b58&#xff0c;\u4f46\u53d7 temptable_max_ram \u9650\u5236&#xff0c;\u8d85\u4e86\u5c31\u843d\u76d8<\/td>\n<\/tr>\n<tr>\n<td>8.0 \u4e14\u624b\u52a8\u8bbe\u4e3a MEMORY<\/td>\n<td>MEMORY<\/td>\n<td>&#x1f534; \u540c 5.7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u67e5\u4e00\u4e0b\u81ea\u5df1\u7684\u914d\u7f6e&#xff1a;<\/p>\n<p><span class=\"token keyword\">SHOW<\/span> VARIABLES <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;internal_tmp_mem_storage_engine&#039;<\/span><span class=\"token punctuation\">;<\/span>   <span class=\"token comment\">&#8212; 8.0 \u9ed8\u8ba4 TempTable<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> VARIABLES <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;temptable_max_ram&#039;<\/span><span class=\"token punctuation\">;<\/span>                 <span class=\"token comment\">&#8212; \u9ed8\u8ba4 1073741824&#xff08;1GB&#xff09;<\/span><\/p>\n<p>\u6240\u4ee5\u51c6\u786e\u7684\u8bf4\u6cd5\u662f&#xff1a;<\/p>\n<p>\u5728 MySQL 5.7 \u4e0a&#xff0c;\u7ed3\u679c\u96c6\u91cc\u53ea\u8981\u5e26 TEXT\/BLOB&#xff0c;\u5185\u90e8\u4e34\u65f6\u8868\u4e00\u5b9a\u5efa\u5728\u78c1\u76d8\u4e0a\u3002<\/p>\n<p>\u5728 MySQL 8.0.13&#043; \u4e0a&#xff0c;TempTable \u5f15\u64ce\u80fd\u628a\u5927\u5b57\u6bb5\u653e\u5728\u5185\u5b58\u91cc&#xff0c;\u4f46 1GB \u7684 temptable_max_ram \u5f88\u5bb9\u6613\u88ab\u51e0\u4e07\u6761\u6b63\u6587\u6491\u7206&#xff0c;\u4e00\u65e6\u8d85\u9650\u540c\u6837\u843d\u76d8\u3002<\/p>\n<p>\u60f3\u5728 8.0 \u4e0a\u590d\u73b0 5.7 \u7684\u884c\u4e3a&#xff0c;\u53ef\u4ee5\u4e34\u65f6\u6539\u4e00\u4e0b\u518d\u8dd1\u4e0a\u9762\u7684\u5b9e\u9a8c&#xff1a;<\/p>\n<p><span class=\"token keyword\">SET<\/span> <span class=\"token keyword\">SESSION<\/span> internal_tmp_mem_storage_engine <span class=\"token operator\">&#061;<\/span> MEMORY<span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token comment\">&#8212; \u8dd1\u5b8c\u8bb0\u5f97\u6539\u56de TempTable<\/span><\/p>\n<p>\u65e0\u8bba\u54ea\u4e2a\u7248\u672c&#xff0c;EXPLAIN \u4e24\u6b21\u90fd\u53ea\u4f1a\u663e\u793a Using temporary&#xff0c;\u7edd\u4e0d\u4f1a\u544a\u8bc9\u4f60\u5b83\u5728\u5185\u5b58\u8fd8\u662f\u78c1\u76d8\u4e0a\u3002\u8fd9\u5c31\u662f\u5fc5\u987b\u770b\u72b6\u6001\u53d8\u91cf\u7684\u539f\u56e0\u3002<\/p>\n<p>\u8bb0\u5f97\u6e05\u7406\u5b9e\u9a8c\u8868&#xff1a;<\/p>\n<p><span class=\"token keyword\">DROP<\/span> <span class=\"token keyword\">TABLE<\/span> t_with_text<span class=\"token punctuation\">,<\/span> t_without_text<span class=\"token punctuation\">;<\/span><\/p>\n<hr \/>\n<h3>\u4e03\u3001\u516b\u79cd\u4f1a\u88ab\u5927\u5b57\u6bb5\u62d6\u6162\u7684\u64cd\u4f5c<\/h3>\n<table>\n<tr>#\u64cd\u4f5c\u4e3a\u4ec0\u4e48\u8d35\u4e25\u91cd\u5ea6<\/tr>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>SELECT content<\/td>\n<td>\u6bcf\u884c\u591a\u4e00\u6b21&#xff08;\u6216\u591a\u6b21&#xff09;\u968f\u673a I\/O<\/td>\n<td>&#x1f534; \u9ad8<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>SELECT *<\/td>\n<td>\u628a\u6240\u6709\u5927\u5b57\u6bb5\u7684\u6ea2\u51fa\u9875\u5168\u8bfb\u4e00\u904d<\/td>\n<td>&#x1f534; \u9ad8<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>DISTINCT\/GROUP BY\/UNION \u65f6\u7ed3\u679c\u96c6\u5e26\u5927\u5b57\u6bb5<\/td>\n<td>\u4e34\u65f6\u8868\u88c5\u7740\u5927\u5b57\u6bb5&#xff0c;5.7 \u4e0a\u5fc5\u7136\u843d\u78c1\u76d8&#xff1b;8.0 \u4e0a\u6491\u7206 temptable_max_ram \u540e\u540c\u6837\u843d\u76d8<\/td>\n<td>&#x1f534; \u9ad8<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>ORDER BY \u5e26\u5927\u5b57\u6bb5<\/td>\n<td>filesort \u7684\u6392\u5e8f\u7f13\u51b2\u533a\u88ab\u6b63\u6587\u5360\u6ee1&#xff0c;\u6392\u5e8f\u8f6e\u6b21\u66b4\u589e\u3001\u6ea2\u51fa\u5230\u78c1\u76d8\u4e34\u65f6\u6587\u4ef6<\/td>\n<td>&#x1f534; \u9ad8<\/td>\n<\/tr>\n<tr>\n<td>5<\/td>\n<td>\u6df1\u5206\u9875 LIMIT 100000, 10<\/td>\n<td>\u56de\u8868 100010 \u6b21 &#043; \u8bfb 100010 \u6b21\u6ea2\u51fa\u9875&#xff0c;\u6700\u540e\u6254\u6389 10 \u4e07\u884c<\/td>\n<td>&#x1f534; \u9ad8<\/td>\n<\/tr>\n<tr>\n<td>6<\/td>\n<td>UPDATE \u5927\u5b57\u6bb5<\/td>\n<td>\u65e7\u6ea2\u51fa\u9875\u4f5c\u5e9f &#043; \u5206\u914d\u65b0\u9875&#xff0c;\u4ea7\u751f\u788e\u7247&#xff0c;undo\/redo \u91cf\u5de8\u5927<\/td>\n<td>&#x1f7e1; \u4e2d<\/td>\n<\/tr>\n<tr>\n<td>7<\/td>\n<td>LIKE &#039;%\u5173\u952e\u8bcd%&#039;<\/td>\n<td>\u5168\u8868\u626b &#043; \u6bcf\u884c\u8bfb\u6ea2\u51fa\u9875&#xff0c;\u53cc\u91cd\u5f00\u9500\u53e0\u52a0<\/td>\n<td>&#x1f534; \u9ad8<\/td>\n<\/tr>\n<tr>\n<td>8<\/td>\n<td>\u7ed9\u5927\u5b57\u6bb5\u5efa\u666e\u901a\u7d22\u5f15<\/td>\n<td>TEXT \u4e0d\u80fd\u6574\u5217\u5efa B&#043; \u6811\u7d22\u5f15&#xff0c;\u53ea\u80fd\u524d\u7f00\u7d22\u5f15 content(100)&#xff0c;\u4e14\u524d\u7f00\u7d22\u5f15\u65e0\u6cd5\u8986\u76d6\u67e5\u8be2&#xff08;\u5168\u6587\u68c0\u7d22\u8981\u7528 FULLTEXT&#xff0c;\u90a3\u662f\u53e6\u4e00\u5957\u7ed3\u6784&#xff09;<\/td>\n<td>&#x1f7e1; \u4e2d<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u7b2c 3\u30014 \u6761\u7ecf\u5e38\u88ab\u6df7\u4e3a\u4e00\u8c08&#xff0c;\u4f46\u673a\u5236\u4e0d\u540c&#xff1a;DISTINCT\/GROUP BY \u7528\u4e34\u65f6\u8868&#xff0c;ORDER BY \u7528 filesort\u3002 \u4e24\u8005\u90fd\u4f1a\u88ab\u5927\u5b57\u6bb5\u62d6\u7d2f&#xff0c;\u4f46\u8bca\u65ad\u6307\u6807\u4e0d\u4e00\u6837\u2014\u2014\u524d\u8005\u770b Created_tmp_disk_tables&#xff0c;\u540e\u8005\u770b Sort_merge_passes\u3002<\/p>\n<h4>\u7b2c 7 \u6761\u503c\u5f97\u5355\u72ec\u8bf4&#xff1a;\u4e3a\u4ec0\u4e48\u6a21\u7cca\u641c\u7d22\u8981\u4e0a\u641c\u7d22\u5f15\u64ce<\/h4>\n<p>\u5f88\u591a\u4eba\u77e5\u9053 LIKE &#039;%xxx%&#039; \u7528\u4e0d\u4e0a\u7d22\u5f15&#xff0c;\u4f46\u90a3\u53ea\u662f\u7b2c\u4e00\u5c42\u4ee3\u4ef7&#xff1a;<\/p>\n<p>LIKE &#039;%\u5173\u952e\u8bcd%&#039; \u5728 5000 \u4e07\u884c\u7684\u6587\u7ae0\u8868\u4e0a&#xff1a;<br \/>\n  \u2460 \u65e0\u6cd5\u4f7f\u7528\u7d22\u5f15 \u2192 \u5168\u8868\u626b\u63cf<br \/>\n  \u2461 \u6bcf\u4e00\u884c\u8fd8\u8981\u8bfb content \u7684\u6ea2\u51fa\u9875   \u2190 \u8fd9\u4e00\u5c42\u51e0\u4e4e\u603b\u662f\u88ab\u5ffd\u7565<br \/>\n  &#061; \u51e0\u5343\u4e07\u6b21\u968f\u673a I\/O<\/p>\n<p>\u4e24\u5c42\u53e0\u52a0&#xff0c;\u624d\u662f\u300c\u4e3a\u4ec0\u4e48\u6570\u636e\u91cf\u4e00\u4e0a\u6765\u5c31\u5fc5\u987b\u5f15\u5165 Elasticsearch\u300d\u7684\u5b8c\u6574\u7b54\u6848\u3002\u4e0d\u662f\u53ea\u56e0\u4e3a\u300c\u7d22\u5f15\u7528\u4e0d\u4e0a\u300d&#xff0c;\u66f4\u56e0\u4e3a\u300c\u6bcf\u884c\u90fd\u8981\u989d\u5916\u8df3\u4e00\u6b21\u6ea2\u51fa\u9875\u300d\u3002<\/p>\n<h4>\u987a\u4fbf\u8bf4\u4e2a\u51b7\u77e5\u8bc6&#xff1a;COUNT(*) \u548c SUM(LENGTH(content)) \u7684\u5dee\u522b<\/h4>\n<p><span class=\"token comment\">&#8212; \u5b8c\u5168\u4e0d\u8bfb\u6ea2\u51fa\u9875&#xff1a;COUNT(*) \u4e0d\u8bbf\u95ee\u4efb\u4f55\u5217\u503c&#xff0c;\u4f18\u5316\u5668\u4f1a\u9009\u6700\u5c0f\u7684\u7d22\u5f15\u53bb\u626b<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">COUNT<\/span><span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">FROM<\/span> t_article <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u8981\u8bfb\u6ea2\u51fa\u9875&#xff1a;LENGTH \u867d\u7136\u53ea\u8981\u957f\u5ea6&#xff0c;\u4f46 MySQL \u4ecd\u9700\u628a\u503c\u53d6\u51fa\u6765<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">SUM<\/span><span class=\"token punctuation\">(<\/span>LENGTH<span class=\"token punctuation\">(<\/span>content<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">FROM<\/span> t_article <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u4e0d\u8fc7\u7b2c\u4e8c\u6761\u4ecd\u7136\u6bd4\u300c\u628a\u6b63\u6587\u5168\u67e5\u56de\u5e94\u7528\u5c42\u518d\u7b97\u957f\u5ea6\u300d\u5212\u7b97\u5f97\u591a\u2014\u2014\u56e0\u4e3a\u5185\u5bb9\u4e0d\u7ecf\u8fc7\u7f51\u7edc\u4f20\u8f93\u3001\u4e0d\u5360\u5e94\u7528\u5185\u5b58\u3002\u8fd9\u4e2a\u601d\u8def\u5728\u7b2c\u516b\u8282\u4f1a\u53d8\u6210\u4e00\u4e2a\u5177\u4f53\u624b\u6cd5\u3002<\/p>\n<hr \/>\n<h3>\u516b\u3001\u516d\u4e2a\u4f18\u5316\u624b\u6cd5&#xff08;\u5e26\u4ee3\u7801&#xff09;<\/h3>\n<h4>8.1 \u6c38\u8fdc\u663e\u5f0f\u5217\u5b57\u6bb5&#xff0c;\u522b\u7528 SELECT *<\/h4>\n<p>\u8fd9\u662f\u6536\u76ca\u6700\u9ad8\u3001\u6210\u672c\u6700\u4f4e\u7684\u4e00\u6761\u3002<\/p>\n<p><span class=\"token comment\"># \u274c \u574f&#xff1a;\u8868\u7ed3\u6784\u4e00\u6539\u5c31\u6084\u6084\u53d8\u6162<\/span><br \/>\ncursor<span class=\"token punctuation\">.<\/span>execute<span class=\"token punctuation\">(<\/span><span class=\"token string\">&#034;SELECT * FROM t_article WHERE id &#061; %s&#034;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token punctuation\">(<\/span>aid<span class=\"token punctuation\">,<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span><\/p>\n<p><span class=\"token comment\"># \u2705 \u597d&#xff1a;\u53ea\u53d6\u771f\u6b63\u7528\u5230\u7684<\/span><br \/>\ncursor<span class=\"token punctuation\">.<\/span>execute<span class=\"token punctuation\">(<\/span><br \/>\n    <span class=\"token string\">&#034;SELECT id, user_id, title, like_count FROM t_article WHERE id &#061; %s&#034;<\/span><span class=\"token punctuation\">,<\/span><br \/>\n    <span class=\"token punctuation\">(<\/span>aid<span class=\"token punctuation\">,<\/span><span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token punctuation\">)<\/span><\/p>\n<p>ORM \u5c24\u5176\u5bb9\u6613\u8e29&#xff0c;\u56e0\u4e3a\u9ed8\u8ba4\u884c\u4e3a\u5c31\u662f SELECT *&#xff1a;<\/p>\n<p><span class=\"token comment\"># \u274c Django&#xff1a;\u9ed8\u8ba4\u67e5\u6240\u6709\u5b57\u6bb5&#xff0c;\u5305\u62ec\u90a3\u4e2a 10KB \u7684 content<\/span><br \/>\narticles <span class=\"token operator\">&#061;<\/span> Article<span class=\"token punctuation\">.<\/span>objects<span class=\"token punctuation\">.<\/span><span class=\"token builtin\">filter<\/span><span class=\"token punctuation\">(<\/span>cate_id<span class=\"token operator\">&#061;<\/span><span class=\"token number\">3<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">[<\/span><span class=\"token punctuation\">:<\/span><span class=\"token number\">50<\/span><span class=\"token punctuation\">]<\/span><\/p>\n<p><span class=\"token comment\"># \u2705 \u53ea\u8981\u5217\u8868\u9875\u7528\u7684\u5b57\u6bb5<\/span><br \/>\narticles <span class=\"token operator\">&#061;<\/span> Article<span class=\"token punctuation\">.<\/span>objects<span class=\"token punctuation\">.<\/span><span class=\"token builtin\">filter<\/span><span class=\"token punctuation\">(<\/span>cate_id<span class=\"token operator\">&#061;<\/span><span class=\"token number\">3<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">.<\/span>only<span class=\"token punctuation\">(<\/span><span class=\"token string\">&#039;id&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#039;title&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#039;like_count&#039;<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">[<\/span><span class=\"token punctuation\">:<\/span><span class=\"token number\">50<\/span><span class=\"token punctuation\">]<\/span><\/p>\n<p><span class=\"token comment\"># \u2705 \u6216\u8005\u53cd\u5411\u6392\u9664\u5927\u5b57\u6bb5<\/span><br \/>\narticles <span class=\"token operator\">&#061;<\/span> Article<span class=\"token punctuation\">.<\/span>objects<span class=\"token punctuation\">.<\/span><span class=\"token builtin\">filter<\/span><span class=\"token punctuation\">(<\/span>cate_id<span class=\"token operator\">&#061;<\/span><span class=\"token number\">3<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">.<\/span>defer<span class=\"token punctuation\">(<\/span><span class=\"token string\">&#039;content&#039;<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">[<\/span><span class=\"token punctuation\">:<\/span><span class=\"token number\">50<\/span><span class=\"token punctuation\">]<\/span><\/p>\n<p><span class=\"token comment\">\/\/ \u274c Laravel Eloquent&#xff1a;\u540c\u6837\u9ed8\u8ba4 select *<\/span><br \/>\n<span class=\"token variable\">$list<\/span> <span class=\"token operator\">&#061;<\/span> <span class=\"token class-name static-context\">Article<\/span><span class=\"token operator\">::<\/span><span class=\"token function\">where<\/span><span class=\"token punctuation\">(<\/span><span class=\"token string single-quoted-string\">&#039;cate_id&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">3<\/span><span class=\"token punctuation\">)<\/span><span class=\"token operator\">-&gt;<\/span><span class=\"token function\">limit<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">50<\/span><span class=\"token punctuation\">)<\/span><span class=\"token operator\">-&gt;<\/span><span class=\"token function\">get<\/span><span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">\/\/ \u2705<\/span><br \/>\n<span class=\"token variable\">$list<\/span> <span class=\"token operator\">&#061;<\/span> <span class=\"token class-name static-context\">Article<\/span><span class=\"token operator\">::<\/span><span class=\"token function\">where<\/span><span class=\"token punctuation\">(<\/span><span class=\"token string single-quoted-string\">&#039;cate_id&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">3<\/span><span class=\"token punctuation\">)<\/span><span class=\"token operator\">-&gt;<\/span><span class=\"token function\">select<\/span><span class=\"token punctuation\">(<\/span><span class=\"token string single-quoted-string\">&#039;id&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string single-quoted-string\">&#039;title&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string single-quoted-string\">&#039;like_count&#039;<\/span><span class=\"token punctuation\">)<\/span><span class=\"token operator\">-&gt;<\/span><span class=\"token function\">limit<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">50<\/span><span class=\"token punctuation\">)<\/span><span class=\"token operator\">-&gt;<\/span><span class=\"token function\">get<\/span><span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">\/\/ \u274c MyBatis-Plus<\/span><br \/>\n<span class=\"token class-name\">List<\/span><span class=\"token generics\"><span class=\"token punctuation\">&lt;<\/span><span class=\"token class-name\">Article<\/span><span class=\"token punctuation\">&gt;<\/span><\/span> list <span class=\"token operator\">&#061;<\/span> articleMapper<span class=\"token punctuation\">.<\/span><span class=\"token function\">selectList<\/span><span class=\"token punctuation\">(<\/span><span class=\"token keyword\">new<\/span> <span class=\"token class-name\">QueryWrapper<\/span><span class=\"token generics\"><span class=\"token punctuation\">&lt;<\/span><span class=\"token class-name\">Article<\/span><span class=\"token punctuation\">&gt;<\/span><\/span><span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">.<\/span><span class=\"token function\">eq<\/span><span class=\"token punctuation\">(<\/span><span class=\"token string\">&#034;cate_id&#034;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">3<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">\/\/ \u2705<\/span><br \/>\n<span class=\"token class-name\">List<\/span><span class=\"token generics\"><span class=\"token punctuation\">&lt;<\/span><span class=\"token class-name\">Article<\/span><span class=\"token punctuation\">&gt;<\/span><\/span> list <span class=\"token operator\">&#061;<\/span> articleMapper<span class=\"token punctuation\">.<\/span><span class=\"token function\">selectList<\/span><span class=\"token punctuation\">(<\/span><br \/>\n    <span class=\"token keyword\">new<\/span> <span class=\"token class-name\">QueryWrapper<\/span><span class=\"token generics\"><span class=\"token punctuation\">&lt;<\/span><span class=\"token class-name\">Article<\/span><span class=\"token punctuation\">&gt;<\/span><\/span><span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">.<\/span><span class=\"token function\">eq<\/span><span class=\"token punctuation\">(<\/span><span class=\"token string\">&#034;cate_id&#034;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">3<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">.<\/span><span class=\"token function\">select<\/span><span class=\"token punctuation\">(<\/span><span class=\"token string\">&#034;id&#034;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#034;title&#034;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#034;like_count&#034;<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u4e00\u4e2a\u5bb9\u6613\u5ffd\u7565\u7684\u957f\u671f\u98ce\u9669&#xff1a;\u4eca\u5929 SELECT * \u4e0d\u6162&#xff0c;\u662f\u56e0\u4e3a\u8868\u91cc\u6070\u597d\u6ca1\u6709\u5927\u5b57\u6bb5\u3002\u7b49\u534a\u5e74\u540e\u6709\u4eba\u52a0\u4e86\u4e00\u5217 content_html LONGTEXT&#xff0c;\u4f60\u6240\u6709\u7684 SELECT * \u4f1a\u5728\u540c\u4e00\u5929\u4e00\u8d77\u53d8\u6162&#xff0c;\u800c\u4e14\u6ca1\u6709\u4efb\u4f55\u4ee3\u7801\u6539\u52a8\u80fd\u8ba9\u4f60\u5b9a\u4f4d\u5230\u539f\u56e0\u3002<\/p>\n<h4>8.2 \u6df1\u5206\u9875&#xff1a;\u7528\u5ef6\u8fdf\u5173\u8054<\/h4>\n<p><span class=\"token comment\">&#8212; \u274c \u574f&#xff1a;\u626b\u63cf\u5e76\u56de\u8868 100010 \u884c\u3001\u8bfb 100010 \u6b21\u6ea2\u51fa\u9875&#xff0c;\u6700\u540e\u53ea\u8981 10 \u884c<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> title<span class=\"token punctuation\">,<\/span> content<br \/>\n<span class=\"token keyword\">FROM<\/span> t_article<br \/>\n<span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span><br \/>\n<span class=\"token keyword\">ORDER<\/span> <span class=\"token keyword\">BY<\/span> created_at <span class=\"token keyword\">DESC<\/span><br \/>\n<span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">100000<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">10<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u2705 \u597d&#xff1a;\u5148\u53ea\u7528\u7d22\u5f15\u5b9a\u4f4d\u51fa 10 \u4e2a\u4e3b\u952e&#xff08;\u4e0d\u78b0\u4efb\u4f55\u5927\u5b57\u6bb5&#xff09;&#xff0c;\u518d\u53bb\u53d6\u5185\u5bb9<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> a<span class=\"token punctuation\">.<\/span>id<span class=\"token punctuation\">,<\/span> a<span class=\"token punctuation\">.<\/span>title<span class=\"token punctuation\">,<\/span> a<span class=\"token punctuation\">.<\/span>content<br \/>\n<span class=\"token keyword\">FROM<\/span> t_article a<br \/>\n<span class=\"token keyword\">INNER<\/span> <span class=\"token keyword\">JOIN<\/span> <span class=\"token punctuation\">(<\/span><br \/>\n    <span class=\"token keyword\">SELECT<\/span> id<br \/>\n    <span class=\"token keyword\">FROM<\/span> t_article<br \/>\n    <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span><br \/>\n    <span class=\"token keyword\">ORDER<\/span> <span class=\"token keyword\">BY<\/span> created_at <span class=\"token keyword\">DESC<\/span><br \/>\n    <span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">100000<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">10<\/span><br \/>\n<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> t <span class=\"token keyword\">ON<\/span> a<span class=\"token punctuation\">.<\/span>id <span class=\"token operator\">&#061;<\/span> t<span class=\"token punctuation\">.<\/span>id<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u5b50\u67e5\u8be2\u90a3\u4e00\u6b65\u5982\u679c\u7528\u4e0a\u4e86 (cate_id, created_at) \u8fd9\u6837\u7684\u7d22\u5f15&#xff0c;\u5c31\u662f\u8986\u76d6\u7d22\u5f15&#xff0c;\u4e00\u6b21\u6ea2\u51fa\u9875\u90fd\u4e0d\u8bfb\u3002\u53ea\u6709\u6700\u540e\u90a3 10 \u884c\u624d\u771f\u6b63\u53bb\u8bfb\u6b63\u6587\u3002<\/p>\n<p>\u8fd9\u4e2a\u6280\u5de7\u53eb\u300c\u5ef6\u8fdf\u5173\u8054\u300d&#xff08;deferred join&#xff09;&#xff0c;\u662f\u6df1\u5206\u9875\u4f18\u5316\u7684\u6807\u51c6\u7b54\u6848\u3002<\/p>\n<h4>8.3 \u6279\u91cf\u626b\u63cf&#xff1a;\u7528\u4e3b\u952e\u6e38\u6807\u4ee3\u66ff OFFSET<\/h4>\n<p><span class=\"token comment\"># \u274c \u574f&#xff1a;offset \u8d8a\u5927\u8d8a\u6162&#xff0c;\u800c\u4e14\u524d\u9762\u6240\u6709\u884c\u7684\u6ea2\u51fa\u9875\u90fd\u88ab\u8bfb\u4e86\u53c8\u6254\u6389<\/span><br \/>\noffset <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">0<\/span><br \/>\n<span class=\"token keyword\">while<\/span> <span class=\"token boolean\">True<\/span><span class=\"token punctuation\">:<\/span><br \/>\n    rows <span class=\"token operator\">&#061;<\/span> db<span class=\"token punctuation\">.<\/span>query<span class=\"token punctuation\">(<\/span><br \/>\n        <span class=\"token string\">&#034;SELECT id, content FROM t_article ORDER BY id LIMIT %s, %s&#034;<\/span><span class=\"token punctuation\">,<\/span><br \/>\n        <span class=\"token punctuation\">(<\/span>offset<span class=\"token punctuation\">,<\/span> <span class=\"token number\">1000<\/span><span class=\"token punctuation\">)<\/span><br \/>\n    <span class=\"token punctuation\">)<\/span><br \/>\n    <span class=\"token keyword\">if<\/span> <span class=\"token keyword\">not<\/span> rows<span class=\"token punctuation\">:<\/span><br \/>\n        <span class=\"token keyword\">break<\/span><br \/>\n    process<span class=\"token punctuation\">(<\/span>rows<span class=\"token punctuation\">)<\/span><br \/>\n    offset <span class=\"token operator\">&#043;&#061;<\/span> <span class=\"token number\">1000<\/span><\/p>\n<p><span class=\"token comment\"># \u2705 \u597d&#xff1a;\u4e3b\u952e\u6e38\u6807&#xff0c;\u6bcf\u6279\u4ee3\u4ef7\u6052\u5b9a<\/span><br \/>\nlast_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">0<\/span><br \/>\n<span class=\"token keyword\">while<\/span> <span class=\"token boolean\">True<\/span><span class=\"token punctuation\">:<\/span><br \/>\n    rows <span class=\"token operator\">&#061;<\/span> db<span class=\"token punctuation\">.<\/span>query<span class=\"token punctuation\">(<\/span><br \/>\n        <span class=\"token string\">&#034;SELECT id, content FROM t_article &#034;<\/span><br \/>\n        <span class=\"token string\">&#034;WHERE id &gt; %s ORDER BY id ASC LIMIT 1000&#034;<\/span><span class=\"token punctuation\">,<\/span><br \/>\n        <span class=\"token punctuation\">(<\/span>last_id<span class=\"token punctuation\">,<\/span><span class=\"token punctuation\">)<\/span><br \/>\n    <span class=\"token punctuation\">)<\/span><br \/>\n    <span class=\"token keyword\">if<\/span> <span class=\"token keyword\">not<\/span> rows<span class=\"token punctuation\">:<\/span><br \/>\n        <span class=\"token keyword\">break<\/span><br \/>\n    process<span class=\"token punctuation\">(<\/span>rows<span class=\"token punctuation\">)<\/span><br \/>\n    last_id <span class=\"token operator\">&#061;<\/span> rows<span class=\"token punctuation\">[<\/span><span class=\"token operator\">&#8211;<\/span><span class=\"token number\">1<\/span><span class=\"token punctuation\">]<\/span><span class=\"token punctuation\">[<\/span><span class=\"token string\">&#039;id&#039;<\/span><span class=\"token punctuation\">]<\/span><\/p>\n<h4>8.4 \u6392\u5e8f\/\u53bb\u91cd\u9636\u6bb5\u4e0d\u8981\u5e26\u5927\u5b57\u6bb5&#xff08;\u4e24\u6bb5\u5f0f\u67e5\u8be2&#xff09;<\/h4>\n<p>\u8fd9\u662f\u6700\u503c\u5f97\u638c\u63e1\u7684\u4e00\u4e2a\u6a21\u5f0f\u3002\u6838\u5fc3\u601d\u60f3&#xff1a;\u628a\u300c\u51b3\u5b9a\u8981\u54ea\u4e9b\u884c\u300d\u548c\u300c\u53d6\u51fa\u8fd9\u4e9b\u884c\u7684\u5185\u5bb9\u300d\u62c6\u6210\u4e24\u6b65\u3002<\/p>\n<h5>\u573a\u666f\u4e00&#xff1a;\u6392\u5e8f\u53d6 Top N<\/h5>\n<p><span class=\"token comment\">&#8212; \u274c \u574f&#xff1a;filesort \u7684\u6392\u5e8f\u7f13\u51b2\u533a\u91cc\u585e\u6ee1\u4e86\u6b63\u6587<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> title<span class=\"token punctuation\">,<\/span> content<br \/>\n<span class=\"token keyword\">FROM<\/span> t_article<br \/>\n<span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span><br \/>\n<span class=\"token keyword\">ORDER<\/span> <span class=\"token keyword\">BY<\/span> like_count <span class=\"token keyword\">DESC<\/span><br \/>\n<span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">50<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u2705 \u7b2c\u4e00\u6b65&#xff1a;\u53ea\u53d6\u4e3b\u952e\u3002\u914d\u5408 (cate_id, like_count) \u7d22\u5f15\u5373\u4e3a\u8986\u76d6\u7d22\u5f15&#xff0c;\u4e00\u6b21\u6ea2\u51fa\u9875\u90fd\u4e0d\u8bfb<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<br \/>\n<span class=\"token keyword\">FROM<\/span> t_article<br \/>\n<span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span><br \/>\n<span class=\"token keyword\">ORDER<\/span> <span class=\"token keyword\">BY<\/span> like_count <span class=\"token keyword\">DESC<\/span><br \/>\n<span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">50<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u2705 \u7b2c\u4e8c\u6b65&#xff1a;\u62ff\u7740\u8fd9 50 \u4e2a\u4e3b\u952e\u7cbe\u51c6\u53d6\u6b63\u6587&#xff08;50 \u6b21\u4e3b\u952e\u70b9\u67e5&#xff09;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> title<span class=\"token punctuation\">,<\/span> content<br \/>\n<span class=\"token keyword\">FROM<\/span> t_article<br \/>\n<span class=\"token keyword\">WHERE<\/span> id <span class=\"token operator\">IN<\/span> <span class=\"token punctuation\">(<\/span><span class=\"token comment\">\/* \u4e0a\u4e00\u6b65\u8fd4\u56de\u7684 50 \u4e2a id *\/<\/span><span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">ORDER<\/span> <span class=\"token keyword\">BY<\/span> FIELD<span class=\"token punctuation\">(<\/span>id<span class=\"token punctuation\">,<\/span> <span class=\"token comment\">\/* \u540c\u6837\u7684 id \u987a\u5e8f *\/<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span>   <span class=\"token comment\">&#8212; \u4fdd\u6301\u7b2c\u4e00\u6b65\u7684\u6392\u5e8f<\/span><\/p>\n<p>\u8fd9\u4e24\u6b65\u548c 8.2 \u7684\u5ef6\u8fdf\u5173\u8054\u662f\u540c\u4e00\u4e2a\u601d\u8def&#xff0c;\u533a\u522b\u53ea\u662f&#xff1a;\u5ef6\u8fdf\u5173\u8054\u4ea4\u7ed9 MySQL \u4e00\u6761 SQL \u641e\u5b9a&#xff0c;\u4e24\u6bb5\u5f0f\u628a\u4e2d\u95f4\u7ed3\u679c\u62ff\u56de\u5e94\u7528\u5c42\u3002<\/p>\n<p>\u80fd\u7528\u5ef6\u8fdf\u5173\u8054\u5c31\u7528\u5ef6\u8fdf\u5173\u8054&#xff08;\u5c11\u4e00\u6b21\u7f51\u7edc\u5f80\u8fd4&#xff09;&#xff1b;\u4f46\u5982\u679c\u4e3b\u952e\u5217\u8868\u8fd8\u8981\u88ab\u522b\u7684\u903b\u8f91\u590d\u7528&#xff0c;\u4e24\u6bb5\u5f0f\u66f4\u7075\u6d3b\u3002<\/p>\n<h5>\u573a\u666f\u4e8c&#xff1a;\u53bb\u91cd<\/h5>\n<p><span class=\"token comment\">&#8212; \u274c \u574f&#xff1a;\u4e34\u65f6\u8868\u91cc\u585e\u6ee1\u4e86\u6b63\u6587<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token keyword\">DISTINCT<\/span> user_id<span class=\"token punctuation\">,<\/span> content<br \/>\n<span class=\"token keyword\">FROM<\/span> t_article<br \/>\n<span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u2705 \u7b2c\u4e00\u6b65&#xff1a;\u53ea\u5728\u5c0f\u5b57\u6bb5\u4e0a\u53bb\u91cd&#xff0c;\u987a\u4fbf\u62ff\u5230\u6bcf\u7ec4\u7684\u4ee3\u8868\u4e3b\u952e<\/span><br \/>\n<span class=\"token comment\">&#8212;    \u8bed\u4e49&#xff1a;\u6bcf\u4e2a\u4f5c\u8005\u53d6 id \u6700\u5c0f\u7684\u90a3\u7bc7<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">MIN<\/span><span class=\"token punctuation\">(<\/span>id<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> id<br \/>\n<span class=\"token keyword\">FROM<\/span> t_article<br \/>\n<span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span><br \/>\n<span class=\"token keyword\">GROUP<\/span> <span class=\"token keyword\">BY<\/span> user_id<span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u2705 \u7b2c\u4e8c\u6b65&#xff1a;\u6309\u4e3b\u952e\u53d6\u6b63\u6587<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> user_id<span class=\"token punctuation\">,<\/span> content<br \/>\n<span class=\"token keyword\">FROM<\/span> t_article<br \/>\n<span class=\"token keyword\">WHERE<\/span> id <span class=\"token operator\">IN<\/span> <span class=\"token punctuation\">(<\/span><span class=\"token comment\">\/* \u4e0a\u4e00\u6b65\u7684\u4e3b\u952e *\/<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u6ce8\u610f\u8fd9\u4e24\u6b65\u4e0d\u662f\u5b57\u9762\u7b49\u4ef7\u7684&#xff1a;\u539f SQL \u7684\u53bb\u91cd\u952e\u5305\u542b content&#xff0c;\u6539\u5199\u540e\u53ea\u6309 user_id \u5206\u7ec4\u3002\u6539\u5199\u524d\u52a1\u5fc5\u786e\u8ba4\u4e1a\u52a1\u771f\u6b63\u8981\u7684\u662f\u54ea\u79cd\u8bed\u4e49\u2014\u2014\u8fd9\u662f\u4e24\u6bb5\u5f0f\u6700\u5bb9\u6613\u51fa bug \u7684\u5730\u65b9\u3002<\/p>\n<h5>\u573a\u666f\u4e09&#xff1a;\u6309\u5185\u5bb9\u5927\u5c0f\u5207\u6279\u6b21<\/h5>\n<p>\u8fd8\u8bb0\u5f97\u7b2c 5.4 \u8282\u90a3\u4e2a\u300c\u540c\u4e00\u6761 SQL \u8017\u65f6\u5ffd\u5feb\u5ffd\u6162\u300d\u7684\u95ee\u9898\u5417&#xff1f;\u6839\u56e0\u662f\u6309\u884c\u6570\u5207\u6279\u6b21&#xff0c;\u800c\u884c\u7684\u80d6\u7626\u5dee\u5f02\u5de8\u5927\u3002\u6b63\u786e\u505a\u6cd5\u662f\u6309\u5b57\u8282\u6570\u5207&#xff1a;<\/p>\n<p>MAX_BYTES_PER_BATCH <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">2<\/span> <span class=\"token operator\">*<\/span> <span class=\"token number\">1024<\/span> <span class=\"token operator\">*<\/span> <span class=\"token number\">1024<\/span>      <span class=\"token comment\"># \u6bcf\u6279\u6700\u591a 2MB \u6b63\u6587<\/span><\/p>\n<p><span class=\"token keyword\">def<\/span> <span class=\"token function\">plan_batches<\/span><span class=\"token punctuation\">(<\/span>db<span class=\"token punctuation\">,<\/span> since<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">:<\/span><br \/>\n    <span class=\"token triple-quoted-string string\">&#034;&#034;&#034;\u5148\u7528\u4e00\u6b21\u5ec9\u4ef7\u7684\u805a\u5408\u67e5\u8be2\u7b97\u51fa\u5206\u6279\u65b9\u6848&#xff0c;\u518d\u6309\u65b9\u6848\u53bb\u53d6\u6b63\u6587\u3002&#034;&#034;&#034;<\/span><br \/>\n    <span class=\"token comment\"># \u7b2c\u4e00\u6b65&#xff1a;\u53ea\u67e5\u300c\u5c3a\u5bf8\u300d&#xff0c;\u4e0d\u67e5\u5185\u5bb9<\/span><br \/>\n    <span class=\"token comment\"># SUM(LENGTH()) \u867d\u7136\u4ecd\u8981\u8bfb\u6ea2\u51fa\u9875&#xff0c;\u4f46\u6b63\u6587\u4e0d\u7ecf\u8fc7\u7f51\u7edc\u3001\u4e0d\u5360\u5e94\u7528\u5185\u5b58<\/span><br \/>\n    size_rows <span class=\"token operator\">&#061;<\/span> db<span class=\"token punctuation\">.<\/span>query<span class=\"token punctuation\">(<\/span><br \/>\n        <span class=\"token string\">&#034;SELECT cate_id, COUNT(*) AS row_cnt, SUM(LENGTH(content)) AS byte_cnt &#034;<\/span><br \/>\n        <span class=\"token string\">&#034;FROM t_article WHERE created_at &gt;&#061; %s GROUP BY cate_id&#034;<\/span><span class=\"token punctuation\">,<\/span><br \/>\n        <span class=\"token punctuation\">(<\/span>since<span class=\"token punctuation\">,<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><br \/>\n    <span class=\"token punctuation\">)<\/span><\/p>\n<p>    <span class=\"token comment\"># \u7b2c\u4e8c\u6b65&#xff1a;\u6839\u636e\u771f\u5b9e\u5b57\u8282\u6570\u89c4\u5212\u6279\u6b21&#xff0c;\u800c\u4e0d\u662f\u62cd\u8111\u888b\u6309\u884c\u6570\u5207<\/span><br \/>\n    batch<span class=\"token punctuation\">,<\/span> cur_bytes <span class=\"token operator\">&#061;<\/span> <span class=\"token punctuation\">[<\/span><span class=\"token punctuation\">]<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">0<\/span><br \/>\n    <span class=\"token keyword\">for<\/span> r <span class=\"token keyword\">in<\/span> size_rows<span class=\"token punctuation\">:<\/span><br \/>\n        <span class=\"token keyword\">if<\/span> batch <span class=\"token keyword\">and<\/span> cur_bytes <span class=\"token operator\">&#043;<\/span> r<span class=\"token punctuation\">[<\/span><span class=\"token string\">&#034;byte_cnt&#034;<\/span><span class=\"token punctuation\">]<\/span> <span class=\"token operator\">&gt;<\/span> MAX_BYTES_PER_BATCH<span class=\"token punctuation\">:<\/span><br \/>\n            <span class=\"token keyword\">yield<\/span> batch<br \/>\n            batch<span class=\"token punctuation\">,<\/span> cur_bytes <span class=\"token operator\">&#061;<\/span> <span class=\"token punctuation\">[<\/span><span class=\"token punctuation\">]<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">0<\/span><br \/>\n        batch<span class=\"token punctuation\">.<\/span>append<span class=\"token punctuation\">(<\/span>r<span class=\"token punctuation\">[<\/span><span class=\"token string\">&#034;cate_id&#034;<\/span><span class=\"token punctuation\">]<\/span><span class=\"token punctuation\">)<\/span><br \/>\n        cur_bytes <span class=\"token operator\">&#043;&#061;<\/span> r<span class=\"token punctuation\">[<\/span><span class=\"token string\">&#034;byte_cnt&#034;<\/span><span class=\"token punctuation\">]<\/span><br \/>\n    <span class=\"token keyword\">if<\/span> batch<span class=\"token punctuation\">:<\/span><br \/>\n        <span class=\"token keyword\">yield<\/span> batch<\/p>\n<p><span class=\"token comment\"># \u7b2c\u4e09\u6b65&#xff1a;\u6309\u89c4\u5212\u597d\u7684\u5206\u7ec4&#xff0c;\u7cbe\u51c6\u53d6\u6b63\u6587<\/span><br \/>\n<span class=\"token keyword\">for<\/span> cate_ids <span class=\"token keyword\">in<\/span> plan_batches<span class=\"token punctuation\">(<\/span>db<span class=\"token punctuation\">,<\/span> since<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">:<\/span><br \/>\n    placeholders <span class=\"token operator\">&#061;<\/span> <span class=\"token string\">&#034;,&#034;<\/span><span class=\"token punctuation\">.<\/span>join<span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">[<\/span><span class=\"token string\">&#034;%s&#034;<\/span><span class=\"token punctuation\">]<\/span> <span class=\"token operator\">*<\/span> <span class=\"token builtin\">len<\/span><span class=\"token punctuation\">(<\/span>cate_ids<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span><br \/>\n    rows <span class=\"token operator\">&#061;<\/span> db<span class=\"token punctuation\">.<\/span>query<span class=\"token punctuation\">(<\/span><br \/>\n        <span class=\"token string-interpolation\"><span class=\"token string\">f&#034;SELECT id, content FROM t_article &#034;<\/span><\/span><br \/>\n        <span class=\"token string-interpolation\"><span class=\"token string\">f&#034;WHERE cate_id IN (<\/span><span class=\"token interpolation\"><span class=\"token punctuation\">{<\/span>placeholders<span class=\"token punctuation\">}<\/span><\/span><span class=\"token string\">) AND created_at &gt;&#061; %s&#034;<\/span><\/span><span class=\"token punctuation\">,<\/span><br \/>\n        <span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span>cate_ids<span class=\"token punctuation\">,<\/span> since<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><br \/>\n    <span class=\"token punctuation\">)<\/span><br \/>\n    process<span class=\"token punctuation\">(<\/span>rows<span class=\"token punctuation\">)<\/span><\/p>\n<p>\u5bf9\u6bd4\u4e00\u4e0b&#xff1a;<\/p>\n<table>\n<tr>\u505a\u6cd5\u6ea2\u51fa\u9875\u8bfb\u53d6\u91cf\u5e94\u7528\u5185\u5b58\u5355\u6279\u8017\u65f6<\/tr>\n<tbody>\n<tr>\n<td>\u4e00\u6b21\u5168\u91cf SELECT content \u518d\u5728\u5185\u5b58\u91cc\u5206\u7ec4<\/td>\n<td>\u5168\u90e8<\/td>\n<td>\u53ef\u80fd OOM<\/td>\n<td>\u2014<\/td>\n<\/tr>\n<tr>\n<td>\u6309\u884c\u6570\u5207\u6279<\/td>\n<td>\u6309\u9700<\/td>\n<td>\u4e0d\u53ef\u63a7&#xff08;\u53d6\u51b3\u4e8e\u884c\u7684\u80d6\u7626&#xff09;<\/td>\n<td>\u6ce2\u52a8\u5267\u70c8<\/td>\n<\/tr>\n<tr>\n<td>\u6309\u5b57\u8282\u6570\u5207\u6279&#xff08;\u4e0a\u9762\u7684\u505a\u6cd5&#xff09;<\/td>\n<td>\u6309\u9700<\/td>\n<td>\u53ef\u63a7<\/td>\n<td>\u5e73\u7a33<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>8.5 \u5782\u76f4\u62c6\u8868&#xff1a;\u51b7\u70ed\u5206\u79bb<\/h4>\n<p>\u5982\u679c\u4e00\u5f20\u8868\u7684\u5927\u5b57\u6bb5\u53ea\u5728\u5c11\u6570\u573a\u666f\u7528\u5230&#xff08;\u6bd4\u5982\u8be6\u60c5\u9875&#xff09;&#xff0c;\u800c\u5217\u8868\u3001\u7edf\u8ba1\u3001\u641c\u7d22\u7b49\u9ad8\u9891\u573a\u666f\u90fd\u4e0d\u7528&#xff0c;\u90a3\u5c31\u628a\u5b83\u62c6\u51fa\u53bb&#xff1a;<\/p>\n<p><span class=\"token comment\">&#8212; \u70ed\u8868&#xff1a;\u7a84\u3001\u5c0f\u3001\u626b\u5f97\u98de\u5feb&#xff0c;\u9ad8\u9891\u67e5\u8be2\u90fd\u53ea\u78b0\u5b83<\/span><br \/>\n<span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> t_article <span class=\"token punctuation\">(<\/span><br \/>\n  id         <span class=\"token keyword\">BIGINT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">AUTO_INCREMENT<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  user_id    <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  cate_id    <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  title      <span class=\"token keyword\">VARCHAR<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">255<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token string\">&#039;&#039;<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  like_count <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  created_at <span class=\"token keyword\">INT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">0<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  <span class=\"token keyword\">PRIMARY<\/span> <span class=\"token keyword\">KEY<\/span> <span class=\"token punctuation\">(<\/span>id<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  <span class=\"token keyword\">KEY<\/span> idx_cate_time <span class=\"token punctuation\">(<\/span>cate_id<span class=\"token punctuation\">,<\/span> created_at<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">ENGINE<\/span><span class=\"token operator\">&#061;<\/span><span class=\"token keyword\">InnoDB<\/span> ROW_FORMAT<span class=\"token operator\">&#061;<\/span>DYNAMIC<span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u51b7\u8868&#xff1a;\u53ea\u5b58\u5927\u5b57\u6bb5&#xff0c;\u53ea\u5728\u8be6\u60c5\u9875\u6309\u4e3b\u952e\u70b9\u67e5<\/span><br \/>\n<span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> t_article_content <span class=\"token punctuation\">(<\/span><br \/>\n  article_id <span class=\"token keyword\">BIGINT<\/span> <span class=\"token keyword\">UNSIGNED<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  content    <span class=\"token keyword\">LONGTEXT<\/span><span class=\"token punctuation\">,<\/span><br \/>\n  <span class=\"token keyword\">PRIMARY<\/span> <span class=\"token keyword\">KEY<\/span> <span class=\"token punctuation\">(<\/span>article_id<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">ENGINE<\/span><span class=\"token operator\">&#061;<\/span><span class=\"token keyword\">InnoDB<\/span> ROW_FORMAT<span class=\"token operator\">&#061;<\/span>DYNAMIC<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u6548\u679c&#xff1a;<\/p>\n<p>\u5217\u8868\u9875 \/ \u7edf\u8ba1 \/ \u6392\u884c\u699c  \u2192 \u53ea\u67e5 t_article&#xff0c;\u4e00\u4e2a\u9875\u80fd\u88c5 100&#043; \u884c<br \/>\n\u8be6\u60c5\u9875                \u2192 \u4e3b\u952e\u70b9\u67e5 t_article_content&#xff0c;\u4e00\u6b21\u5c31\u547d\u4e2d<\/p>\n<p>\u5f88\u591a\u9879\u76ee\u7684\u8868\u7ed3\u6784\u91cc\u5176\u5b9e\u5df2\u7ecf\u9690\u542b\u4e86\u8fd9\u4e2a\u601d\u8def\u2014\u2014\u4e00\u5f20\u4e3b\u8868\u914d\u4e00\u5f20 xxx_detail \/ xxx_content \u7684\u4ece\u8868\u3002\u73b0\u5728\u4f60\u77e5\u9053\u5b83\u4e0d\u53ea\u662f\u300c\u5b57\u6bb5\u592a\u591a\u653e\u4e0d\u4e0b\u300d&#xff0c;\u66f4\u662f\u6027\u80fd\u4e0a\u7684\u4e3b\u52a8\u8bbe\u8ba1\u3002<\/p>\n<p>\u62c6\u8868\u7684\u4ee3\u4ef7\u4e5f\u8981\u8bf4\u6e05\u695a&#xff1a;<\/p>\n<ul>\n<li>\u8be6\u60c5\u9875\u591a\u4e00\u6b21\u67e5\u8be2&#xff08;\u4f46\u662f\u4e3b\u952e\u70b9\u67e5&#xff0c;\u5f88\u4fbf\u5b9c&#xff09;<\/li>\n<li>\u5199\u5165\u8981\u4fdd\u8bc1\u4e24\u5f20\u8868\u4e00\u81f4&#xff08;\u540c\u4e00\u4e8b\u52a1\u5185\u5199&#xff0c;\u6216\u5bb9\u5fcd\u6700\u7ec8\u4e00\u81f4&#xff09;<\/li>\n<li>\u4e0d\u80fd\u518d\u7528\u4e00\u6761 SQL \u628a\u5217\u8868\u548c\u6b63\u6587\u4e00\u8d77\u67e5\u51fa\u6765<\/li>\n<\/ul>\n<p>\u5224\u65ad\u6807\u51c6\u5f88\u7b80\u5355&#xff1a;\u5982\u679c\u5927\u5b57\u6bb5\u7684\u8bbf\u95ee\u9891\u7387\u8fdc\u4f4e\u4e8e\u5c0f\u5b57\u6bb5&#xff0c;\u5c31\u8be5\u62c6\u3002<\/p>\n<h4>8.6 \u5176\u4ed6<\/h4>\n<p><span class=\"token comment\">&#8212; 1) \u5927\u5b57\u6bb5\u7528 TEXT\/LONGTEXT&#xff0c;\u800c\u4e0d\u662f\u8d85\u957f VARCHAR<\/span><br \/>\n<span class=\"token comment\">&#8212;    \u6ce8\u610f\u539f\u56e0\u4e0d\u662f\u300cVARCHAR \u4e00\u5b9a\u884c\u5185\u300d&#xff08;\u7b2c 5.4 \u8282\u5df2\u6f84\u6e05&#xff0c;DYNAMIC \u4e0b\u4e24\u8005\u884c\u4e3a\u4e00\u81f4&#xff09;&#xff0c;<\/span><br \/>\n<span class=\"token comment\">&#8212;    \u800c\u662f VARCHAR \u7684\u300c\u58f0\u660e\u957f\u5ea6\u300d\u4f1a\u7b97\u8fdb 65535 \u5b57\u8282\u7684\u884c\u5b9a\u4e49\u4e0a\u9650&#xff0c;\u66f4\u5bb9\u6613\u649e\u5899<\/span><\/p>\n<p><span class=\"token comment\">&#8212; 2) \u8981\u5728\u5927\u5b57\u6bb5\u4e0a\u505a\u524d\u7f00\u5339\u914d&#xff0c;\u7528\u524d\u7f00\u7d22\u5f15&#xff08;\u6ce8\u610f&#xff1a;\u5b83\u65e0\u6cd5\u8986\u76d6\u67e5\u8be2&#xff09;<\/span><br \/>\n<span class=\"token keyword\">ALTER<\/span> <span class=\"token keyword\">TABLE<\/span> t_article <span class=\"token keyword\">ADD<\/span> <span class=\"token keyword\">KEY<\/span> idx_title_prefix <span class=\"token punctuation\">(<\/span>title<span class=\"token punctuation\">(<\/span><span class=\"token number\">32<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; 3) \u5168\u6587\u68c0\u7d22\u4ea4\u7ed9 Elasticsearch&#xff0c;\u6216\u7528 MySQL \u81ea\u5e26\u7684\u5168\u6587\u7d22\u5f15<\/span><br \/>\n<span class=\"token comment\">&#8212;    \u4e2d\u6587\u5fc5\u987b\u914d ngram \u5206\u8bcd\u5668&#xff0c;\u4e14 ngram_token_size \u8981\u5728\u5efa\u7d22\u5f15\u524d\u8bbe\u597d&#xff08;\u9ed8\u8ba4 2&#xff09;<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> VARIABLES <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;ngram_token_size&#039;<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">ALTER<\/span> <span class=\"token keyword\">TABLE<\/span> t_article <span class=\"token keyword\">ADD<\/span> FULLTEXT <span class=\"token keyword\">KEY<\/span> ft_content <span class=\"token punctuation\">(<\/span>content<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">WITH<\/span> PARSER ngram<span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; 4) \u4e34\u65f6\u8868\u7528\u5b8c\u5373\u5220\u3002\u6ce8\u610f DELETE \u53ea\u6807\u8bb0\u7a7a\u95f4\u53ef\u590d\u7528&#xff0c;\u4e0d\u8fd8\u7ed9\u64cd\u4f5c\u7cfb\u7edf<\/span><br \/>\n<span class=\"token keyword\">DELETE<\/span> <span class=\"token keyword\">FROM<\/span> t_import_tmp <span class=\"token keyword\">WHERE<\/span> task_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">123<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token comment\">&#8212; \u957f\u671f\u79ef\u7d2f\u788e\u7247\u540e\u9700\u8981\u91cd\u5efa\u8868\u624d\u80fd\u771f\u6b63\u56de\u6536\u7a7a\u95f4<\/span><br \/>\n<span class=\"token keyword\">OPTIMIZE<\/span> <span class=\"token keyword\">TABLE<\/span> t_import_tmp<span class=\"token punctuation\">;<\/span>         <span class=\"token comment\">&#8212; \u5bf9 InnoDB \u7b49\u4ef7\u4e8e ALTER TABLE &#8230; ENGINE&#061;InnoDB<\/span><\/p>\n<p><span class=\"token comment\">&#8212; 5) \u4e00\u6b21\u6027\u6e05\u7a7a\u6574\u5f20\u4e34\u65f6\u8868&#xff0c;\u7528 TRUNCATE \u800c\u4e0d\u662f DELETE<\/span><br \/>\n<span class=\"token comment\">&#8212;    TRUNCATE \u76f4\u63a5\u4e22\u5f03\u5e76\u91cd\u5efa\u8868\u7a7a\u95f4\u6587\u4ef6&#xff0c;\u6ea2\u51fa\u9875\u5360\u7684\u7a7a\u95f4\u7acb\u523b\u5f52\u8fd8<\/span><br \/>\n<span class=\"token keyword\">TRUNCATE<\/span> <span class=\"token keyword\">TABLE<\/span> t_import_tmp<span class=\"token punctuation\">;<\/span><\/p>\n<hr \/>\n<h3>\u4e5d\u3001\u600e\u4e48\u8bca\u65ad&#xff1a;EXPLAIN \u7684\u76f2\u533a<\/h3>\n<h4>9.1 \u5148\u627f\u8ba4 EXPLAIN \u5728\u8fd9\u4ef6\u4e8b\u4e0a\u65e0\u80fd<\/h4>\n<p><span class=\"token keyword\">EXPLAIN<\/span> <span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> content <span class=\"token keyword\">FROM<\/span> t_article <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span> <span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">100<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u8f93\u51fa\u91cc\u6709 type\u3001key\u3001rows\u3001filtered\u3001Extra&#xff0c;\u6ca1\u6709\u4efb\u4f55\u4e00\u5217\u53cd\u6620\u6ea2\u51fa\u9875\u3002\u4e24\u4e2a\u53ea\u5dee\u4e00\u4e2a\u5b57\u6bb5\u7684\u67e5\u8be2&#xff0c;EXPLAIN \u8f93\u51fa\u53ef\u4ee5\u5b8c\u5168\u4e00\u81f4&#xff0c;\u800c\u5b9e\u9645\u8017\u65f6\u5dee 10 \u500d\u3002<\/p>\n<p>EXPLAIN FORMAT&#061;JSON \u91cc\u7684 data_read_per_join \u7a0d\u6709\u53c2\u8003\u4ef7\u503c&#xff0c;\u4f46\u5b83\u7b97\u7684\u4e5f\u662f\u884c\u5185\u90e8\u5206\u3002<\/p>\n<h4>9.2 \u65b9\u6cd5\u4e00&#xff1a;\u5bf9\u6bd4 Buffer Pool \u8bfb\u8bf7\u6c42\u6570&#xff08;\u6700\u76f4\u63a5&#xff09;<\/h4>\n<p>FLUSH <span class=\"token keyword\">STATUS<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id <span class=\"token keyword\">FROM<\/span> t_article <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span> <span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">1000<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> <span class=\"token keyword\">SESSION<\/span> <span class=\"token keyword\">STATUS<\/span> <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;Innodb_buffer_pool_read_requests&#039;<\/span><span class=\"token punctuation\">;<\/span>   <span class=\"token comment\">&#8212; \u8bb0\u4e0b A<\/span><\/p>\n<p>FLUSH <span class=\"token keyword\">STATUS<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> id<span class=\"token punctuation\">,<\/span> content <span class=\"token keyword\">FROM<\/span> t_article <span class=\"token keyword\">WHERE<\/span> cate_id <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">3<\/span> <span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">1000<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> <span class=\"token keyword\">SESSION<\/span> <span class=\"token keyword\">STATUS<\/span> <span class=\"token operator\">LIKE<\/span> <span class=\"token string\">&#039;Innodb_buffer_pool_read_requests&#039;<\/span><span class=\"token punctuation\">;<\/span>   <span class=\"token comment\">&#8212; \u8bb0\u4e0b B<\/span><\/p>\n<p><span class=\"token comment\">&#8212; B &#8211; A \u2248 \u8bfb\u6ea2\u51fa\u9875\u5e26\u6765\u7684\u989d\u5916\u9875\u8bbf\u95ee\u6b21\u6570<\/span><\/p>\n<h4>9.3 \u65b9\u6cd5\u4e8c&#xff1a;\u770b\u4e34\u65f6\u8868\u548c\u6392\u5e8f\u6709\u6ca1\u6709\u843d\u78c1\u76d8<\/h4>\n<p>\u8fd9\u4e24\u4e2a\u662f\u4e0d\u540c\u7684\u673a\u5236&#xff0c;\u8981\u5206\u522b\u770b&#xff1a;<\/p>\n<p>FLUSH <span class=\"token keyword\">STATUS<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token comment\">&#8212; \u8dd1\u4f60\u6000\u7591\u7684\u90a3\u6761 SQL<\/span><br \/>\n<span class=\"token keyword\">SHOW<\/span> <span class=\"token keyword\">SESSION<\/span> <span class=\"token keyword\">STATUS<\/span> <span class=\"token keyword\">WHERE<\/span> Variable_name <span class=\"token operator\">IN<\/span> <span class=\"token punctuation\">(<\/span><br \/>\n  <span class=\"token string\">&#039;Created_tmp_tables&#039;<\/span><span class=\"token punctuation\">,<\/span>       <span class=\"token comment\">&#8212; \u521b\u5efa\u7684\u5185\u90e8\u4e34\u65f6\u8868\u603b\u6570<\/span><br \/>\n  <span class=\"token string\">&#039;Created_tmp_disk_tables&#039;<\/span><span class=\"token punctuation\">,<\/span>  <span class=\"token comment\">&#8212; \u5176\u4e2d\u843d\u5230\u78c1\u76d8\u7684\u6570\u91cf  \u2190 DISTINCT\/GROUP BY \u770b\u8fd9\u4e2a<\/span><br \/>\n  <span class=\"token string\">&#039;Sort_merge_passes&#039;<\/span><span class=\"token punctuation\">,<\/span>        <span class=\"token comment\">&#8212; filesort \u7684\u5f52\u5e76\u8f6e\u6b21 \u2190 ORDER BY \u770b\u8fd9\u4e2a&#xff0c;&gt; 0 \u8bf4\u660e\u6392\u5e8f\u7f13\u51b2\u4e0d\u591f<\/span><br \/>\n  <span class=\"token string\">&#039;Sort_rows&#039;<\/span><br \/>\n<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<table>\n<tr>\u6307\u6807\u5bf9\u5e94\u4ec0\u4e48\u64cd\u4f5c\u600e\u4e48\u7f13\u89e3<\/tr>\n<tbody>\n<tr>\n<td>Created_tmp_disk_tables &gt; 0<\/td>\n<td>DISTINCT\/GROUP BY\/UNION \u7684\u7ed3\u679c\u96c6\u91cc\u5e26\u4e86\u5927\u5b57\u6bb5<\/td>\n<td>\u6539\u4e24\u6bb5\u5f0f&#xff08;8.4&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>Sort_merge_passes &gt; 0<\/td>\n<td>ORDER BY \u7684\u7ed3\u679c\u96c6\u91cc\u5e26\u4e86\u5927\u5b57\u6bb5&#xff0c;\u6392\u5e8f\u7f13\u51b2\u533a\u88c5\u4e0d\u4e0b<\/td>\n<td>\u6539\u4e24\u6bb5\u5f0f&#xff0c;\u6216\u8c03 sort_buffer_size<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>MySQL 8.0 \u4e0a Created_tmp_disk_tables \u7684\u7edf\u8ba1\u53e3\u5f84\u968f\u5c0f\u7248\u672c\u6709\u8c03\u6574&#xff08;TempTable \u5f15\u64ce\u7684 mmap \u6587\u4ef6\u662f\u5426\u8ba1\u5165\u66fe\u53d8\u52a8\u8fc7&#xff09;\u3002\u5982\u679c\u6570\u5b57\u770b\u8d77\u6765\u53ef\u7591&#xff0c;\u7528 9.2 \u7684\u9875\u8bbf\u95ee\u6b21\u6570\u6cd5\u4ea4\u53c9\u9a8c\u8bc1\u3002<\/p>\n<h4>9.4 \u65b9\u6cd5\u4e09&#xff1a;\u4ece Performance Schema \u627e\u6162\u8bed\u53e5<\/h4>\n<p><span class=\"token keyword\">SELECT<\/span> DIGEST_TEXT<span class=\"token punctuation\">,<\/span><br \/>\n       COUNT_STAR<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">ROUND<\/span><span class=\"token punctuation\">(<\/span>AVG_TIMER_WAIT <span class=\"token operator\">\/<\/span> <span class=\"token number\">1000000000<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">2<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> avg_ms<span class=\"token punctuation\">,<\/span><br \/>\n       SUM_ROWS_EXAMINED<span class=\"token punctuation\">,<\/span><br \/>\n       SUM_CREATED_TMP_DISK_TABLES          <span class=\"token comment\">&#8212; \u2190 \u91cd\u70b9\u770b\u8fd9\u4e00\u5217<\/span><br \/>\n<span class=\"token keyword\">FROM<\/span> performance_schema<span class=\"token punctuation\">.<\/span>events_statements_summary_by_digest<br \/>\n<span class=\"token keyword\">WHERE<\/span> SCHEMA_NAME <span class=\"token operator\">&#061;<\/span> <span class=\"token keyword\">DATABASE<\/span><span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">ORDER<\/span> <span class=\"token keyword\">BY<\/span> SUM_TIMER_WAIT <span class=\"token keyword\">DESC<\/span><br \/>\n<span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">20<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>SUM_CREATED_TMP_DISK_TABLES \u4e0d\u4e3a 0 \u7684\u8bed\u53e5&#xff0c;\u5927\u6982\u7387\u5c31\u662f\u300c\u6392\u5e8f\/\u53bb\u91cd\u65f6\u5e26\u4e86\u5927\u5b57\u6bb5\u300d\u3002<\/p>\n<h4>9.5 \u65b9\u6cd5\u56db&#xff1a;\u4f30\u7b97\u5927\u5b57\u6bb5\u5230\u5e95\u5360\u4e86\u591a\u5c11<\/h4>\n<p><span class=\"token comment\">&#8212; \u26a0\ufe0f \u5927\u8868\u4e0a\u52a1\u5fc5\u52a0\u65f6\u95f4\/\u4e3b\u952e\u8303\u56f4\u9650\u5236&#xff0c;\u5426\u5219\u8fd9\u6761\u67e5\u8be2\u672c\u8eab\u5c31\u4f1a\u628a\u6ea2\u51fa\u9875\u8bfb\u7206<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">COUNT<\/span><span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span><span class=\"token punctuation\">)<\/span>                                        <span class=\"token keyword\">AS<\/span> rows_scanned<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">ROUND<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">AVG<\/span><span class=\"token punctuation\">(<\/span>LENGTH<span class=\"token punctuation\">(<\/span>content<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span>                     <span class=\"token keyword\">AS<\/span> avg_bytes<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">MAX<\/span><span class=\"token punctuation\">(<\/span>LENGTH<span class=\"token punctuation\">(<\/span>content<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">)<\/span>                            <span class=\"token keyword\">AS<\/span> max_bytes<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">SUM<\/span><span class=\"token punctuation\">(<\/span>LENGTH<span class=\"token punctuation\">(<\/span>content<span class=\"token punctuation\">)<\/span> <span class=\"token operator\">&gt;<\/span> <span class=\"token number\">8000<\/span><span class=\"token punctuation\">)<\/span>                     <span class=\"token keyword\">AS<\/span> maybe_overflow<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">ROUND<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">100<\/span> <span class=\"token operator\">*<\/span> <span class=\"token function\">SUM<\/span><span class=\"token punctuation\">(<\/span>LENGTH<span class=\"token punctuation\">(<\/span>content<span class=\"token punctuation\">)<\/span> <span class=\"token operator\">&gt;<\/span> <span class=\"token number\">8000<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token operator\">\/<\/span> <span class=\"token function\">COUNT<\/span><span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token number\">1<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> overflow_pct<br \/>\n<span class=\"token keyword\">FROM<\/span> t_article<br \/>\n<span class=\"token keyword\">WHERE<\/span> id <span class=\"token operator\">BETWEEN<\/span> <span class=\"token number\">1000000<\/span> <span class=\"token operator\">AND<\/span> <span class=\"token number\">1010000<\/span><span class=\"token punctuation\">;<\/span>      <span class=\"token comment\">&#8212; \u91c7\u6837<\/span><\/p>\n<p>overflow_pct \u662f\u300c\u6709\u591a\u5c11\u6bd4\u4f8b\u7684\u884c\u4f1a\u8bfb\u6ea2\u51fa\u9875\u300d\u7684\u7c97\u7565\u4f30\u8ba1\u3002<\/p>\n<p>\u4e3a\u4ec0\u4e48\u9608\u503c\u53d6 8000 \u800c\u4e0d\u662f 8126&#xff1f;\u56e0\u4e3a 8126 \u662f\u6574\u884c\u7684\u4e0a\u9650&#xff0c;content \u4e4b\u5916\u7684\u5b57\u6bb5\u4e5f\u8981\u5360\u4f4d\u7f6e\u3002\u5b9e\u9645\u89e6\u53d1\u884c\u5916\u5b58\u50a8\u7684\u95e8\u69db\u6bd4 8126 \u66f4\u4f4e&#xff0c;\u53d6 8000 \u662f\u4e2a\u504f\u4fdd\u5b88\u7684\u8fd1\u4f3c\u3002<\/p>\n<p>\u60f3\u7cbe\u786e\u5224\u65ad&#xff0c;\u5f97\u770b information_schema.INNODB_BUFFER_PAGE \u91cc PAGE_TYPE&#061;&#039;BLOB&#039; \u7684\u9875\u3002<\/p>\n<h4>9.6 \u4e00\u4e2a\u503c\u5f97\u77e5\u9053\u7684\u539f\u7406&#xff1a;\u4f18\u5316\u5668\u4e5f\u770b\u4e0d\u89c1\u6ea2\u51fa\u9875<\/h4>\n<p>MySQL \u4f18\u5316\u5668\u7684\u4ee3\u4ef7\u6a21\u578b&#xff0c;\u57fa\u672c\u4e0d\u8ba1\u7b97\u6ea2\u51fa\u9875\u6210\u672c\u3002\u5b83\u6309\u300c\u884c\u6570 \u00d7 \u884c\u5bbd\u300d\u4f30\u7b97 I\/O&#xff0c;\u800c\u884c\u5bbd\u7b97\u7684\u662f\u884c\u5185\u90e8\u5206&#xff08;\u4e5f\u5c31\u662f\u90a3 20 \u5b57\u8282\u6307\u9488&#xff09;&#xff0c;\u4e0d\u7b97\u6ea2\u51fa\u9875\u7684\u771f\u5b9e\u4f53\u79ef\u3002<\/p>\n<p>\u7ed3\u679c\u5c31\u662f&#xff1a;\u5728\u5927\u5b57\u6bb5\u8868\u4e0a&#xff0c;\u4f18\u5316\u5668\u4f1a\u7cfb\u7edf\u6027\u5730\u4f4e\u4f30\u6210\u672c&#xff0c;\u56e0\u6b64\u66f4\u5bb9\u6613\u9009\u9519\u6267\u884c\u8ba1\u5212\u3002<\/p>\n<p>\u5982\u679c\u4f60\u5728\u67d0\u4e2a\u9879\u76ee\u91cc\u770b\u5230\u5927\u91cf FORCE INDEX \/ USE INDEX \u7684\u63d0\u793a&#xff0c;\u800c\u8fd9\u4e9b\u8868\u6070\u597d\u90fd\u5e26\u7740\u5927\u6587\u672c\u5b57\u6bb5\u2014\u2014\u8fd9\u5f88\u53ef\u80fd\u4e0d\u662f\u5199\u4ee3\u7801\u7684\u4eba\u4e71\u52a0&#xff0c;\u800c\u662f\u4f18\u5316\u5668\u5728\u8fd9\u7c7b\u8868\u4e0a\u786e\u5b9e\u4e0d\u592a\u53ef\u9760\u3002<\/p>\n<hr \/>\n<h3>\u5341\u3001\u603b\u7ed3&#xff1a;\u4e94\u53e5\u8bdd\u8bb0\u4f4f<\/h3>\n<p>\u2460 \u4e00\u884c\u653e\u4e0d\u8fdb 16KB \u9875 \u2192 \u5927\u5b57\u6bb5\u88ab\u632a\u5230\u6ea2\u51fa\u9875&#xff0c;\u884c\u5185\u53ea\u7559 20 \u5b57\u8282\u6307\u9488<br \/>\n\u2461 \u53d6\u90a3\u4e2a\u5b57\u6bb5 \u2192 \u591a\u4e00\u6b21&#xff08;\u6216\u591a\u6b21&#xff0c;\u4e0e\u957f\u5ea6\u6210\u6b63\u6bd4&#xff09;\u968f\u673a I\/O<br \/>\n\u2462 \u4e0d\u53d6\u90a3\u4e2a\u5b57\u6bb5 \u2192 \u96f6\u6210\u672c&#xff0c;\u800c\u4e14\u56e0\u4e3a\u884c\u53d8\u7626&#xff0c;\u626b\u63cf\u53cd\u800c\u66f4\u5feb<br \/>\n\u2463 EXPLAIN \u5b8c\u5168\u770b\u4e0d\u89c1\u8fd9\u4ef6\u4e8b&#xff0c;\u8981\u9760 Buffer Pool \u9875\u8bbf\u95ee\u6570 \/ Created_tmp_disk_tables \/ Sort_merge_passes<br \/>\n\u2464 \u6392\u5e8f\u3001\u53bb\u91cd\u3001\u5206\u9875\u7684\u300c\u51b3\u7b56\u9636\u6bb5\u300d\u7edd\u4e0d\u8981\u5e26\u5927\u5b57\u6bb5&#xff0c;\u62c6\u6210\u4e24\u6bb5\u5f0f<\/p>\n<h4>\u4e00\u5f20\u5224\u65ad\u6d41\u7a0b\u56fe<\/h4>\n<p>\u6211\u7684\u67e5\u8be2\u4f1a\u4e0d\u4f1a\u8bfb\u6ea2\u51fa\u9875&#xff1f;<br \/>\n\u2502<br \/>\n\u251c\u2500 SELECT \u5217\u8868\u91cc\u6709 TEXT \/ BLOB \/ \u957f VARCHAR \/ JSON \u5417&#xff1f;<br \/>\n\u2502   \u251c\u2500 \u6ca1\u6709 \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u25b6 \u4e0d\u4f1a&#xff0c;\u653e\u5fc3<br \/>\n\u2502   \u2514\u2500 \u6709<br \/>\n\u2502       \u2514\u2500 \u8fd9\u4e9b\u5b57\u6bb5\u7684\u5b9e\u9645\u5185\u5bb9\u8d85\u8fc7\u884c\u5185\u7a7a\u95f4\u4e86\u5417&#xff1f;<br \/>\n\u2502           \u251c\u2500 \u5927\u90e8\u5206\u884c\u90fd\u5f88\u77ed \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u25b6 \u591a\u6570\u7559\u5728\u884c\u5185&#xff0c;\u5f71\u54cd\u5c0f<br \/>\n\u2502           \u2514\u2500 \u5927\u90e8\u5206\u884c\u5f88\u957f \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u25b6 \u4f1a&#xff0c;\u4e14\u4ee3\u4ef7\u4e0e\u957f\u5ea6\u6210\u6b63\u6bd4<br \/>\n\u2502               \u2502<br \/>\n\u2502               \u251c\u2500 \u8fd9\u4e2a\u5b57\u6bb5\u662f\u4e1a\u52a1\u5fc5\u9700\u7684\u5417&#xff1f;<br \/>\n\u2502               \u2502   \u251c\u2500 \u662f \u2500\u25b6 \u65e0\u6cd5\u6d88\u9664&#xff0c;\u6539\u4e3a\u63a7\u5236\u300c\u6bcf\u6279\u5b57\u8282\u6570\u300d\u800c\u975e\u884c\u6570<br \/>\n\u2502               \u2502   \u2514\u2500 \u5426 \u2500\u25b6 \u4ece SELECT \u5217\u8868\u91cc\u5220\u6389\u5b83<br \/>\n\u2502               \u2502<br \/>\n\u2502               \u2514\u2500 \u540c\u65f6\u8fd8\u6709 ORDER BY \/ GROUP BY \/ DISTINCT \u5417&#xff1f;<br \/>\n\u2502                   \u251c\u2500 DISTINCT \/ GROUP BY \u2500\u25b6 &#x1f534; \u4e34\u65f6\u8868\u5e26\u5927\u5b57\u6bb5&#xff0c;\u6539\u4e24\u6bb5\u5f0f<br \/>\n\u2502                   \u2514\u2500 ORDER BY \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u25b6 &#x1f534; \u6392\u5e8f\u7f13\u51b2\u88ab\u6b63\u6587\u5360\u6ee1&#xff0c;\u6539\u4e24\u6bb5\u5f0f<\/p>\n<h4>\u6700\u540e\u4e00\u6761\u53cd\u76f4\u89c9\u7684\u5bf9\u7167<\/h4>\n<table>\n<tr>\u67e5\u8be2\u56de\u8868\u8bfb\u6ea2\u51fa\u9875\u5355\u884c\u5b9e\u9645\u6210\u672c<\/tr>\n<tbody>\n<tr>\n<td>\u8d70\u4e3b\u952e &#043; SELECT *&#xff08;\u542b\u5927\u5b57\u6bb5&#xff09;<\/td>\n<td>\u274c \u6ca1\u6709<\/td>\n<td>\u2705 \u6709<\/td>\n<td>\u4e2d\u7b49<\/td>\n<\/tr>\n<tr>\n<td>\u8d70\u4e8c\u7ea7\u7d22\u5f15 &#043; \u53ea\u53d6\u5c0f\u5b57\u6bb5<\/td>\n<td>\u2705 \u6709<\/td>\n<td>\u274c \u6ca1\u6709<\/td>\n<td>\u66f4\u4f4e<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u6240\u4ee5\u522b\u518d\u628a\u300c\u6709\u6ca1\u6709\u56de\u8868\u300d\u5f53\u6210\u552f\u4e00\u7684\u4f18\u5316\u76ee\u6807\u4e86\u3002 \u771f\u6b63\u8be5\u95ee\u7684\u662f&#xff1a;<\/p>\n<p>\u8fd9\u4e2a\u67e5\u8be2&#xff0c;\u4e00\u5171\u8981\u8bbf\u95ee\u591a\u5c11\u4e2a 16KB \u7684\u9875&#xff1f;<\/p>\n<hr \/>\n<h3>\u9644&#xff1a;\u5ef6\u4f38\u9605\u8bfb<\/h3>\n<p>\u5b98\u65b9\u6587\u6863&#xff08;\u5f3a\u70c8\u5efa\u8bae\u5bf9\u7167\u7740\u770b&#xff0c;\u672c\u6587\u7684\u7ed3\u8bba\u90fd\u80fd\u5728\u8fd9\u91cc\u627e\u5230\u4f9d\u636e&#xff09;&#xff1a;<\/p>\n<ul>\n<li>InnoDB Row Formats \u2014\u2014 \u56db\u79cd\u884c\u683c\u5f0f\u4e0e 768 \u5b57\u8282\u524d\u7f00<\/li>\n<li>The Physical Structure of an InnoDB Index<\/li>\n<li>Internal Temporary Table Use in MySQL \u2014\u2014 \u4ec0\u4e48\u60c5\u51b5\u4e0b\u4f1a\u7528\u4e34\u65f6\u8868\u3001\u4ec0\u4e48\u60c5\u51b5\u4e0b\u843d\u78c1\u76d8<\/li>\n<li>Limits on Table Column Count and Row Size \u2014\u2014 8126 \u548c 65535 \u4e24\u4e2a\u4e0a\u9650\u7684\u6765\u5386<\/li>\n<li>Optimizing ORDER BY \u2014\u2014 filesort \u4e0e\u5927\u5b57\u6bb5\u7684\u5173\u7cfb<\/li>\n<\/ul>\n<p>\u52a8\u624b\u67e5\u8bc1\u7528&#xff1a;<\/p>\n<p><span class=\"token comment\">&#8212; \u76f4\u63a5\u770b Buffer Pool \u91cc\u6709\u591a\u5c11 BLOB&#xff08;\u6ea2\u51fa&#xff09;\u9875&#xff0c;\u4ee5\u53ca\u5c5e\u4e8e\u54ea\u5f20\u8868<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> TABLE_NAME<span class=\"token punctuation\">,<\/span> PAGE_TYPE<span class=\"token punctuation\">,<\/span> <span class=\"token function\">COUNT<\/span><span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> pages<br \/>\n<span class=\"token keyword\">FROM<\/span> information_schema<span class=\"token punctuation\">.<\/span>INNODB_BUFFER_PAGE<br \/>\n<span class=\"token keyword\">WHERE<\/span> PAGE_TYPE <span class=\"token operator\">IN<\/span> <span class=\"token punctuation\">(<\/span><span class=\"token string\">&#039;BLOB&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#039;INDEX&#039;<\/span><span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">GROUP<\/span> <span class=\"token keyword\">BY<\/span> TABLE_NAME<span class=\"token punctuation\">,<\/span> PAGE_TYPE<br \/>\n<span class=\"token keyword\">ORDER<\/span> <span class=\"token keyword\">BY<\/span> pages <span class=\"token keyword\">DESC<\/span><br \/>\n<span class=\"token keyword\">LIMIT<\/span> <span class=\"token number\">20<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u26a0\ufe0f \u8fd9\u5f20\u8868\u5728\u5927 Buffer Pool \u4e0a\u67e5\u8be2\u5f88\u91cd&#xff0c;\u751f\u4ea7\u73af\u5883\u614e\u7528\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u76ee\u5f55\u4e00\u3001\u4e00\u4e2a\u5e38\u89c1\u7684\u56f0\u60d1\u4e8c\u3001\u5148\u5206\u6e05\u4e24\u4e2a\u6982\u5ff5&#xff1a;\u56de\u8868 \u2260 \u6ea2\u51fa\u9875\u4e09\u3001MySQL \u7684\u6570\u636e\u662f\u600e\u4e48\u6446\u5728\u78c1\u76d8\u4e0a\u7684\u56db\u3001\u4e00\u4e2a\u9875 16KB&#xff0c;\u4e00\u7bc7\u6587\u7ae0 3 \u4e07\u5b57&#xff0c;\u600e\u4e48\u529e4.1 \u4e00\u6761\u786c\u6027\u89c4\u5b9a4.2 \u95ee\u9898\u6765\u4e864.3 InnoDB \u7684\u89e3\u6cd5&#xff1a;\u642c\u8d70&#xff0c;\u53ea\u7559\u4e2a\u5730\u57404.4 20 \u5b57\u8282\u6307\u9488\u91cc\u88c5\u4e86\u4ec0\u4e484.5 \u4e00\u4e2a\u5927\u5b57\u6bb5\u53ef\u80fd\u4e0d\u6b62\u4e00\u4e2a\u6ea2\u51fa\u98754.6 \u8fd9\u4e2a\u673a\u5236\u6709\u4e2a\u597d\u7684\u526f\u4f5c\u7528\u4e94\u3001\u884c\u683c\u5f0f&#xff1a;\u51b3\u5b9a\u300c\u632a\u591a\u5c11\u300d\u7684\u9690\u85cf\u5f00\u51735.1 \u56db\u79cd\u884c\u683c\u5f0f5.2 COMP<\/p>\n","protected":false},"author":2,"featured_media":116237,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[13178,84,114,768,292,100],"topic":[],"class_list":["post-116238","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-server","tag-1024---","tag-android","tag-mysql","tag-sql","tag-292","tag-100"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>\u6211\u53ea\u591a\u67e5\u4e86\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e3a\u4ec0\u4e48\u6162\u4e86 10 \u500d\uff1f\u2014\u2014MySQL \u5927\u5b57\u6bb5\u4e0e\u6ea2\u51fa\u9875\u5165\u95e8 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.wsisp.com\/helps\/116238.html\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"\u6211\u53ea\u591a\u67e5\u4e86\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e3a\u4ec0\u4e48\u6162\u4e86 10 \u500d\uff1f\u2014\u2014MySQL \u5927\u5b57\u6bb5\u4e0e\u6ea2\u51fa\u9875\u5165\u95e8 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"og:description\" content=\"\u76ee\u5f55\u4e00\u3001\u4e00\u4e2a\u5e38\u89c1\u7684\u56f0\u60d1\u4e8c\u3001\u5148\u5206\u6e05\u4e24\u4e2a\u6982\u5ff5&#xff1a;\u56de\u8868 \u2260 \u6ea2\u51fa\u9875\u4e09\u3001MySQL \u7684\u6570\u636e\u662f\u600e\u4e48\u6446\u5728\u78c1\u76d8\u4e0a\u7684\u56db\u3001\u4e00\u4e2a\u9875 16KB&#xff0c;\u4e00\u7bc7\u6587\u7ae0 3 \u4e07\u5b57&#xff0c;\u600e\u4e48\u529e4.1 \u4e00\u6761\u786c\u6027\u89c4\u5b9a4.2 \u95ee\u9898\u6765\u4e864.3 InnoDB \u7684\u89e3\u6cd5&#xff1a;\u642c\u8d70&#xff0c;\u53ea\u7559\u4e2a\u5730\u57404.4 20 \u5b57\u8282\u6307\u9488\u91cc\u88c5\u4e86\u4ec0\u4e484.5 \u4e00\u4e2a\u5927\u5b57\u6bb5\u53ef\u80fd\u4e0d\u6b62\u4e00\u4e2a\u6ea2\u51fa\u98754.6 \u8fd9\u4e2a\u673a\u5236\u6709\u4e2a\u597d\u7684\u526f\u4f5c\u7528\u4e94\u3001\u884c\u683c\u5f0f&#xff1a;\u51b3\u5b9a\u300c\u632a\u591a\u5c11\u300d\u7684\u9690\u85cf\u5f00\u51735.1 \u56db\u79cd\u884c\u683c\u5f0f5.2 COMP\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.wsisp.com\/helps\/116238.html\" \/>\n<meta property=\"og:site_name\" content=\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"article:published_time\" content=\"2026-10-20T16:40:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/10\/20261020164040-6ad799888eab8.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"14 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/116238.html\",\"url\":\"https:\/\/www.wsisp.com\/helps\/116238.html\",\"name\":\"\u6211\u53ea\u591a\u67e5\u4e86\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e3a\u4ec0\u4e48\u6162\u4e86 10 \u500d\uff1f\u2014\u2014MySQL \u5927\u5b57\u6bb5\u4e0e\u6ea2\u51fa\u9875\u5165\u95e8 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"isPartOf\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\"},\"datePublished\":\"2026-10-20T16:40:42+00:00\",\"dateModified\":\"2026-10-20T16:40:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/116238.html#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.wsisp.com\/helps\/116238.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/116238.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.wsisp.com\/helps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"\u6211\u53ea\u591a\u67e5\u4e86\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e3a\u4ec0\u4e48\u6162\u4e86 10 \u500d\uff1f\u2014\u2014MySQL \u5927\u5b57\u6bb5\u4e0e\u6ea2\u51fa\u9875\u5165\u95e8\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\",\"url\":\"https:\/\/www.wsisp.com\/helps\/\",\"name\":\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"description\":\"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"contentUrl\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"caption\":\"admin\"},\"sameAs\":[\"http:\/\/wp.wsisp.com\"],\"url\":\"https:\/\/www.wsisp.com\/helps\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"\u6211\u53ea\u591a\u67e5\u4e86\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e3a\u4ec0\u4e48\u6162\u4e86 10 \u500d\uff1f\u2014\u2014MySQL \u5927\u5b57\u6bb5\u4e0e\u6ea2\u51fa\u9875\u5165\u95e8 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.wsisp.com\/helps\/116238.html","og_locale":"zh_CN","og_type":"article","og_title":"\u6211\u53ea\u591a\u67e5\u4e86\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e3a\u4ec0\u4e48\u6162\u4e86 10 \u500d\uff1f\u2014\u2014MySQL \u5927\u5b57\u6bb5\u4e0e\u6ea2\u51fa\u9875\u5165\u95e8 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","og_description":"\u76ee\u5f55\u4e00\u3001\u4e00\u4e2a\u5e38\u89c1\u7684\u56f0\u60d1\u4e8c\u3001\u5148\u5206\u6e05\u4e24\u4e2a\u6982\u5ff5&#xff1a;\u56de\u8868 \u2260 \u6ea2\u51fa\u9875\u4e09\u3001MySQL \u7684\u6570\u636e\u662f\u600e\u4e48\u6446\u5728\u78c1\u76d8\u4e0a\u7684\u56db\u3001\u4e00\u4e2a\u9875 16KB&#xff0c;\u4e00\u7bc7\u6587\u7ae0 3 \u4e07\u5b57&#xff0c;\u600e\u4e48\u529e4.1 \u4e00\u6761\u786c\u6027\u89c4\u5b9a4.2 \u95ee\u9898\u6765\u4e864.3 InnoDB \u7684\u89e3\u6cd5&#xff1a;\u642c\u8d70&#xff0c;\u53ea\u7559\u4e2a\u5730\u57404.4 20 \u5b57\u8282\u6307\u9488\u91cc\u88c5\u4e86\u4ec0\u4e484.5 \u4e00\u4e2a\u5927\u5b57\u6bb5\u53ef\u80fd\u4e0d\u6b62\u4e00\u4e2a\u6ea2\u51fa\u98754.6 \u8fd9\u4e2a\u673a\u5236\u6709\u4e2a\u597d\u7684\u526f\u4f5c\u7528\u4e94\u3001\u884c\u683c\u5f0f&#xff1a;\u51b3\u5b9a\u300c\u632a\u591a\u5c11\u300d\u7684\u9690\u85cf\u5f00\u51735.1 \u56db\u79cd\u884c\u683c\u5f0f5.2 COMP","og_url":"https:\/\/www.wsisp.com\/helps\/116238.html","og_site_name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","article_published_time":"2026-10-20T16:40:42+00:00","og_image":[{"url":"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/10\/20261020164040-6ad799888eab8.png"}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"admin","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"14 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.wsisp.com\/helps\/116238.html","url":"https:\/\/www.wsisp.com\/helps\/116238.html","name":"\u6211\u53ea\u591a\u67e5\u4e86\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e3a\u4ec0\u4e48\u6162\u4e86 10 \u500d\uff1f\u2014\u2014MySQL \u5927\u5b57\u6bb5\u4e0e\u6ea2\u51fa\u9875\u5165\u95e8 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","isPartOf":{"@id":"https:\/\/www.wsisp.com\/helps\/#website"},"datePublished":"2026-10-20T16:40:42+00:00","dateModified":"2026-10-20T16:40:42+00:00","author":{"@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41"},"breadcrumb":{"@id":"https:\/\/www.wsisp.com\/helps\/116238.html#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.wsisp.com\/helps\/116238.html"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.wsisp.com\/helps\/116238.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.wsisp.com\/helps"},{"@type":"ListItem","position":2,"name":"\u6211\u53ea\u591a\u67e5\u4e86\u4e00\u4e2a\u5b57\u6bb5\uff0c\u4e3a\u4ec0\u4e48\u6162\u4e86 10 \u500d\uff1f\u2014\u2014MySQL \u5927\u5b57\u6bb5\u4e0e\u6ea2\u51fa\u9875\u5165\u95e8"}]},{"@type":"WebSite","@id":"https:\/\/www.wsisp.com\/helps\/#website","url":"https:\/\/www.wsisp.com\/helps\/","name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","description":"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41","name":"admin","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/","url":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","contentUrl":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","caption":"admin"},"sameAs":["http:\/\/wp.wsisp.com"],"url":"https:\/\/www.wsisp.com\/helps\/author\/admin"}]}},"_links":{"self":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/116238","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/comments?post=116238"}],"version-history":[{"count":0,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/116238\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/media\/116237"}],"wp:attachment":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/media?parent=116238"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/categories?post=116238"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/tags?post=116238"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/topic?post=116238"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}