{"id":2917,"date":"2017-03-01T15:33:47","date_gmt":"2017-03-01T07:33:47","guid":{"rendered":"https:\/\/yanjingang.com\/blog\/?p=2917"},"modified":"2019-08-01T15:47:32","modified_gmt":"2019-08-01T07:47:32","slug":"innodb%e7%9a%84b%e6%a0%91%e7%b4%a2%e5%bc%95","status":"publish","type":"post","link":"https:\/\/yanjingang.com\/blog\/?p=2917","title":{"rendered":"MySQL InnoDB\u7684B+\u6811\u7d22\u5f15"},"content":{"rendered":"<p class=\" \">B+\u6811\u7d22\u5f15\u5176\u672c\u8d28\u5c31\u662fB+\u6811\u5728\u6570\u636e\u5e93\u4e2d\u7684\u5b9e\u73b0\uff0c\u4f46\u662fB+\u7d22\u5f15\u5728\u6570\u636e\u5e93\u4e2d\u6709\u4e00\u4e2a\u7279\u70b9\u5c31\u662f\u5176<span style=\"color: #ff0000;\">\u9ad8\u6247\u51fa\u6027<\/span>\uff0c\u56e0\u6b64\u5728\u6570\u636e\u5e93\u4e2d\uff0c<span style=\"color: #ff0000;\">B+\u6811\u7684\u9ad8\u5ea6\u4e00\u822c\u90fd\u57282\uff5e3\u5c42<\/span>\uff0c\u4e5f\u5c31\u662f\u5bf9\u4e8e\u67e5\u627e\u67d0\u4e00\u952e\u503c\u7684\u884c\u8bb0\u5f55\uff0c\u6700\u591a\u53ea\u9700\u89812\u52303\u6b21IO\uff0c\u8fd9\u5012\u4e0d\u9519\u3002\u56e0\u4e3a\u6211\u4eec\u77e5\u9053\u73b0\u5728\u4e00\u822c\u7684\u78c1\u76d8\u6bcf\u79d2\u81f3\u5c11\u53ef\u4ee5\u505a100\u6b21IO\uff0c2\uff5e3\u6b21\u7684IO\u610f\u5473\u7740\u67e5\u8be2\u65f6\u95f4\u53ea\u97000.02\uff5e0.03\u79d2\u3002<\/p>\n<p class=\" \"><span class=\" \">\u6570\u636e\u5e93\u4e2d\u7684B+\u6811\u7d22\u5f15\u53ef\u4ee5\u5206\u4e3a<span style=\"color: #ff0000;\">\u805a\u96c6\u7d22\u5f15<\/span>\uff08clustered index\uff09\u548c\u8f85\u52a9\u805a\u96c6\u7d22\u5f15\uff08secondary index\uff09<\/span>\u8f85\u52a9\u805a\u96c6\u7d22\u5f15\u6709\u65f6\u4e5f\u79f0<span style=\"color: #ff0000;\">\u975e\u805a\u96c6\u7d22\u5f15<\/span>\uff08non-clustered index\uff09\u3002<\/p>\n<p><span class=\" \">\u4f46\u662f\u4e0d\u7ba1\u662f\u805a\u96c6\u8fd8\u662f\u975e\u805a\u96c6\u7684\u7d22\u5f15\uff0c\u5176\u5185\u90e8\u90fd\u662fB+\u6811\u7684\uff0c\u5373\u9ad8\u5ea6\u5e73\u8861\u7684\uff0c<span style=\"color: #ff0000;\"><span style=\"color: #333333;\">\u53f6\u8282\u70b9\u5b58\u653e\u7740\u6240\u6709\u7684\u6570\u636e\u3002<\/span>\u805a\u96c6\u7d22\u5f15\u4e0e\u975e\u805a\u96c6\u7d22\u5f15\u4e0d\u540c\u7684\u662f\uff0c\u53f6\u8282\u70b9\u5b58\u653e\u7684\u662f\u5426\u662f\u4e00\u6574\u884c\u7684\u4fe1\u606f<\/span>\u3002<\/span><\/p>\n<h2 id=\"blogTitle0\" class=\" \">\u805a\u96c6\u7d22\u5f15<\/h2>\n<p class=\" \">InnoDB\u5b58\u50a8\u5f15\u64ce\u8868\u662f\u7d22\u5f15\u7ec4\u7ec7\u8868\uff0c\u5373\u8868\u4e2d\u6570\u636e<span style=\"color: #ff0000;\">\u6309\u7167\u4e3b\u952e\u987a\u5e8f\u5b58\u653e<\/span>\u3002\u800c\u805a\u96c6\u7d22\u5f15\u5c31\u662f\u6309\u7167\u6bcf\u5f20\u8868\u7684\u4e3b\u952e\u6784\u9020\u4e00\u9897B+\u6811\uff0c\u5e76\u4e14\u53f6\u8282\u70b9\u4e2d\u5b58\u653e\u7740\u6574\u5f20\u8868\u7684\u884c\u8bb0\u5f55\u6570\u636e\uff0c\u56e0\u6b64\u4e5f\u8ba9\u805a\u96c6\u7d22\u5f15\u7684<span style=\"color: #ff0000;\">\u53f6\u8282\u70b9\u6210\u4e3a\u6570\u636e\u9875<\/span>\u3002\u805a\u96c6\u7d22\u5f15\u7684\u8fd9\u4e2a\u7279\u6027\u51b3\u5b9a\u4e86\u7d22\u5f15\u7ec4\u7ec7\u8868\u4e2d\u6570\u636e\u4e5f\u662f\u7d22\u5f15\u7684\u4e00\u90e8\u5206\u3002\u540cB+\u6811\u6570\u636e\u7ed3\u6784\u4e00\u6837\uff0c\u6bcf\u4e2a\u6570\u636e\u9875\u90fd\u901a\u8fc7\u4e00\u4e2a<span style=\"color: #ff0000;\">\u53cc\u5411\u94fe\u8868<\/span>\u6765\u8fdb\u884c\u94fe\u63a5\u3002<\/p>\n<p class=\" \"><span style=\"color: #ff0000;\">\u7531\u4e8e\u5b9e\u9645\u7684\u6570\u636e\u9875\u53ea\u80fd\u6309\u7167\u4e00\u9897B+\u6811\u8fdb\u884c\u6392\u5e8f\uff0c\u56e0\u6b64\u6bcf\u5f20\u8868\u53ea\u80fd\u62e5\u6709\u4e00\u4e2a\u805a\u96c6\u7d22\u5f15\u3002<\/span>\u5728\u8bb8\u591a\u60c5\u51b5\u4e0b\uff0c\u67e5\u8be2\u4f18\u5316\u5668\u975e\u5e38\u503e\u5411\u4e8e\u91c7\u7528\u805a\u96c6\u7d22\u5f15\uff0c\u56e0\u4e3a\u805a\u96c6\u7d22\u5f15\u80fd\u591f\u8ba9\u6211\u4eec\u5728\u7d22\u5f15\u7684\u53f6\u8282\u70b9\u4e0a\u76f4\u63a5\u627e\u5230\u6570\u636e\u3002\u6b64\u5916\uff0c\u7531\u4e8e\u5b9a\u4e49\u4e86\u6570\u636e\u7684\u903b\u8f91\u987a\u5e8f\uff0c\u805a\u96c6\u7d22\u5f15\u80fd\u591f\u7279\u522b\u5feb\u5730\u8bbf\u95ee\u9488\u5bf9\u8303\u56f4\u503c\u7684\u67e5\u8be2\u3002\u67e5\u8be2\u4f18\u5316\u5668\u80fd\u591f\u5feb\u901f\u53d1\u73b0\u67d0\u4e00\u6bb5\u8303\u56f4\u7684\u6570\u636e\u9875\u9700\u8981\u626b\u63cf\u3002<\/p>\n<p class=\" \">\u73b0\u5728\u6211\u4eec\u6765\u770b\u4e00\u5f20\u8868\uff0c\u6211\u4eec\u4ee5\u4eba\u4e3a\u7684\u65b9\u5f0f\u8ba9\u5176\u6bcf\u4e2a\u9875\u53ea\u80fd\u5b58\u653e\u4e24\u4e2a\u884c\u8bb0\u5f55\uff0c\u5982\uff1a<\/p>\n<p class=\" \">create table t(a int not null primary key,b varchar(8000));<\/p>\n<p class=\" \">insert into t select 1,repeat(&#8216;a&#8217;,7000);<\/p>\n<p class=\" \">insert into t select 2,repeat(&#8216;a&#8217;,7000);<\/p>\n<p class=\" \">insert into t select 3,repeat(a&#8217;,7000);<\/p>\n<p class=\" \">insert into t select 4,repeat(&#8216;a&#8217;,7000);<\/p>\n<p class=\" \">\u53ef\u4ee5\u770b\u5230\uff0c\u6211\u4eec\u8868\u7684\u5b9a\u4e49\u548c\u63d2\u5165\u65b9\u5f0f\u4f7f\u5f97\u76ee\u524d\u6bcf\u4e2a\u9875\u53ea\u80fd\u5b58\u653e\u4e24\u4e2a\u884c\u8bb0\u5f55\uff0c\u6211\u4eec\u7528py_innodb_page_info\u5de5\u5177\u6765\u5206\u6790\u8868\u7a7a\u95f4\uff0c\u53ef\u5f97\uff1apy_innodb_page_info.py-v mytest\/t.ibd<\/p>\n<p>page level\u4e3a0000\u7684\u5373\u662f\u6570\u636e\u9875\u3002\u6211\u4eec\u8981\u5206\u6790\u7684\u662fpage level\u4e3a0001\u7684\u9875\uff0c<span style=\"color: #ff0000;\">\u8be5\u9875\u662fB+\u6811\u7684\u6839<\/span>\uff0c\u6211\u4eec\u6765\u770b\u770b\u7d22\u5f15\u7684\u6839\u9875\u4e2d\u5b58\u653e\u7684\u6570\u636e\u3002<\/p>\n<p class=\" \">\u6211\u4eec\u76f4\u63a5\u901a\u8fc7\u9875\u5c3e\u7684Page Directory\u6765\u5206\u6790\uff0c\u4ece00 63\u53ef\u4ee5\u77e5\u9053\u8be5\u9875\u4e2d\u884c\u5f00\u59cb\u7684\u4f4d\u7f6e\u3002\u63a5\u7740\u901a\u8fc7Recorder Header\u6765\u5206\u6790\uff0c0xc063\u5f00\u59cb\u7684\u503c\u4e3a69 6e 66 69 6d 75 6d 00\uff0c\u5c31\u4ee3\u8868infimum\u4f2a\u884c\u8bb0\u5f55\u3002\u4e4b\u524d\u76845\u4e2a\u5b57\u828201 00 02 00 1b\u5c31\u662fRecorder Header\uff0c\u5206\u6790\u7b2c4\u4f4d\u5230\u7b2c8\u4f4d\u7684\u503c1\u4ee3\u8868\u8be5\u884c\u8bb0\u5f55\u4e2d\u53ea\u6709\u4e00\u4e2a\u8bb0\u5f55\uff08\u9700\u8981\u8bb0\u4f4f\u7684\u662f\uff0cInnoDB\u7684Page Directory\u662f\u7a00\u758f\u7684\uff09\uff0c\u5373infimum\u8bb0\u5f55\u672c\u8eab\u3002\u6211\u4eec\u901a\u8fc7Recorder Header\u4e2d\u6700\u540e\u7684\u4e24\u4e2a\u5b57\u828200 1b\u6765\u5224\u65ad\u4e0b\u4e00\u6761\u8bb0\u5f55\u7684\u4f4d\u7f6e\uff0c\u5373c063+1b=c073\uff0c\u8bfb\u53d6\u952e\u503c\u53ef\u5f9780 01\uff0c\u5c31\u662f\u4e3b\u952e\u4e3a1\u7684\u952e\u503c\uff08\u6211\u4eec\u5236\u5b9a\u7684INT\u662f\u65e0\u7b26\u53f7\u7684\uff0c\u56e0\u6b64\u4e8c\u8fdb\u5236\u662f0x8001\uff0c\u800c\u4e0d\u662f0x0001\uff09\uff0c80 01\u540e\u503c00 00 00 04\u4ee3\u8868\u6307\u5411\u6570\u636e\u9875\u7684\u9875\u53f7\u3002\u4ee5\u540c\u6837\u7684\u65b9\u5f0f\uff0c\u53ef\u4ee5\u627e\u523080 02\u548c80 04\u8fd9\u4e24\u4e2a\u952e\u503c\u4ee5\u53ca\u5b83\u4eec\u6307\u5411\u7684\u6570\u636e\u9875\u3002<\/p>\n<p class=\" \"><span style=\"color: #ff0000;\"><span style=\"color: #333333;\">\u901a\u8fc7\u4ee5\u4e0a\u5bf9\u4e8e\u975e\u6570\u636e\u9875\u8282\u70b9\u7684\u5206\u6790\uff0c\u6211\u4eec\u53d1\u73b0<\/span>\u6570\u636e\u9875\u4e0a\u5b58\u653e\u7684\u662f\u5b8c\u6574\u7684\u884c\u8bb0\u5f55<span style=\"color: #333333;\">\uff0c<\/span>\u800c\u5728\u975e\u6570\u636e\u9875\u7684\u7d22\u5f15\u9875\u4e2d\uff0c\u5b58\u653e\u7684\u4ec5\u4ec5\u662f\u952e\u503c\u4ee5\u53ca\u6307\u5411\u6570\u636e\u9875\u7684\u504f\u79fb\u91cf<span style=\"color: #333333;\">\uff0c\u800c\u4e0d\u662f\u4e00\u4e2a\u5b8c\u6574\u7684\u884c\u8bb0\u5f55<\/span><\/span><span style=\"color: #333333; font-size: 1rem;\">\u3002<\/span><span style=\"font-size: 1rem;\">\u56e0\u6b64\u6211\u4eec\u6784\u9020\u7684\u8fd9\u9897\u4e8c\u53c9\u6811\u5927\u81f4\u5982\u56fe\u6240\u793a\uff1a<\/span><\/p>\n<p><img loading=\"lazy\" src=\"https:\/\/images2015.cnblogs.com\/blog\/990532\/201701\/990532-20170117141020817-571812725.png\" alt=\"\" width=\"506\" height=\"259\" \/><a href=\"https:\/\/yanjingang.com\/blog\/?attachment_id=2920\" rel=\"attachment wp-att-2920\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-2920\" src=\"https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/1.png\" alt=\"\" width=\"717\" height=\"367\" srcset=\"https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/1.png 717w, https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/1-300x154.png 300w, https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/1-624x319.png 624w\" sizes=\"(max-width: 717px) 100vw, 717px\" \/><\/a><\/p>\n<p class=\" \">\u8bb8\u591a\u6570\u636e\u5e93\u7684\u6587\u6863\u4f1a\u8fd9\u6837\u544a\u8bc9\u8bfb\u8005\uff1a\u805a\u96c6\u7d22\u5f15\u6309\u7167\u987a\u5e8f\u7269\u7406\u5730\u5b58\u50a8\u6570\u636e\u3002\u4f46\u662f\u8bd5\u60f3\uff0c\u5982\u679c\u805a\u96c6\u7d22\u5f15\u5fc5\u987b\u6309\u7167\u7279\u5b9a\u987a\u5e8f\u5b58\u653e\u7269\u7406\u8bb0\u5f55\u7684\u8bdd\uff0c\u5219\u7ef4\u62a4\u6210\u672c\u5373\u663e\u5f97\u975e\u5e38\u4e4b\u9ad8\u4e86\u3002\u6240\u4ee5\uff0c<span style=\"color: #ff0000;\">\u805a\u96c6\u7d22\u5f15\u7684\u5b58\u50a8\u5e76\u4e0d\u662f\u7269\u7406\u4e0a\u7684\u8fde\u7eed\uff0c\u76f8\u53cd\u662f\u903b\u8f91\u4e0a\u8fde\u7eed\u7684\u3002<\/span>\u8fd9\u5176\u4e2d\u6709\u4e24\u70b9\uff1a<span style=\"color: #ff0000;\">\u4e00\u662f\u6211\u4eec\u524d\u9762\u8bf4\u8fc7\u7684\u9875\u901a\u8fc7\u53cc\u5411\u94fe\u8868\u94fe\u63a5\uff0c\u9875\u6309\u7167\u4e3b\u952e\u7684\u987a\u5e8f\u6392\u5217\u3002\u53e6\u4e00\u70b9\u662f\u6bcf\u4e2a\u9875\u4e2d\u7684\u8bb0\u5f55\u4e5f\u662f\u901a\u8fc7\u53cc\u5411\u94fe\u8868\u8fdb\u884c\u7ef4\u62a4\uff0c\u7269\u7406\u5b58\u50a8\u4e0a\u53ef\u4ee5\u540c\u6837\u4e0d\u6309\u7167\u4e3b\u952e\u5b58\u50a8\u3002<\/span><\/p>\n<p class=\" \">\u805a\u96c6\u7d22\u5f15\u7684\u53e6\u4e00\u4e2a\u597d\u5904\u662f\uff0c\u5b83\u5bf9\u4e8e\u4e3b\u952e\u7684\u6392\u5e8f\u67e5\u627e\u548c\u8303\u56f4\u67e5\u627e\u901f\u5ea6\u975e\u5e38\u5feb\u3002\u53f6\u8282\u70b9\u7684\u6570\u636e\u5c31\u662f\u6211\u4eec\u8981\u67e5\u8be2\u7684\u6570\u636e\uff0c\u5982\u6211\u4eec\u8981\u67e5\u8be2\u4e00\u5f20\u6ce8\u518c\u7528\u6237\u7684\u8868\uff0c\u67e5\u8be2\u6700\u540e\u6ce8\u518c\u768410\u4f4d\u7528\u6237\uff0c\u7531\u4e8eB+\u6811\u7d22\u5f15\u662f\u53cc\u5411\u94fe\u8868\u7684\uff0c\u6211\u4eec\u53ef\u4ee5\u5feb\u901f\u627e\u5230\u6700\u540e\u4e00\u4e2a\u6570\u636e\u9875\uff0c\u5e76\u53d6\u51fa10\u6761\u8bb0\u5f55\uff0c\u6211\u4eec\u7528Explain\u8fdb\u884c\u5206\u6790\uff0c\u53ef\u5f97\uff1a<\/p>\n<p>explain select * from Profile order by id limit 10;<\/p>\n<p>\u53e6\u4e00\u4e2a\u662f\u8303\u56f4\u67e5\u8be2\uff08range query\uff09\uff0c\u5982\u679c\u8981\u67e5\u627e\u4e3b\u952e\u67d0\u4e00\u8303\u56f4\u5185\u7684\u6570\u636e\uff0c\u901a\u8fc7\u53f6\u8282\u70b9\u7684\u4e0a\u5c42\u4e2d\u95f4\u8282\u70b9\u5c31\u53ef\u4ee5\u5f97\u5230\u9875\u7684\u8303\u56f4\uff0c\u4e4b\u540e\u76f4\u63a5\u8bfb\u53d6\u6570\u636e\u9875\u5373\u53ef\uff0c\u53c8\u5982\uff1a<\/p>\n<p>explain select * from Profile where id\uff1e10 and id\uff1c10000;<\/p>\n<p>Explain\u5f97\u5230\u4e86MySQL\u7684\u6267\u884c\u8ba1\u5212\uff08execute plan\uff09\uff0c\u5e76\u4e14rows\u5217\u7ed9\u51fa\u4e86\u4e00\u4e2a\u67e5\u8be2\u7ed3\u679c\u7684\u9884\u4f30\u8fd4\u56de\u884c\u6570\u3002\u8981\u6ce8\u610f\u7684\u662f\uff0crows\u4ee3\u8868\u7684\u662f\u4e00\u4e2a\u9884\u4f30\u503c\uff0c\u4e0d\u662f\u786e\u5207\u7684\u503c\uff0c\u5982\u679c\u6211\u4eec\u5b9e\u9645\u8fdb\u884c\u8fd9\u53e5SQL\u7684\u67e5\u8be2\uff0c\u53ef\u4ee5\u770b\u5230\u5b9e\u9645\u4e0a\u53ea\u67099 946\u884c\u8bb0\u5f55\uff1a<\/p>\n<p>select count(*) from Profile where id\uff1e10 and id\uff1c10000;<\/p>\n<h2 id=\"blogTitle1\" class=\" \">\u975e\u805a\u96c6\u7d22\u5f15<\/h2>\n<p>\u5bf9\u4e8e\u8f85\u52a9\u7d22\u5f15\uff08\u4e5f\u79f0\u975e\u805a\u96c6\u7d22\u5f15\uff09\uff0c<span style=\"color: #ff0000;\">\u53f6\u7ea7\u522b\u4e0d\u5305\u542b\u884c\u7684\u5168\u90e8\u6570\u636e<\/span>\u3002<span style=\"color: #ff0000;\">\u53f6\u8282\u70b9\u9664\u4e86\u5305\u542b\u952e\u503c\u4ee5\u5916\uff0c\u6bcf\u4e2a\u53f6\u7ea7\u522b\u4e2d\u7684\u7d22\u5f15\u884c\u4e2d\u8fd8\u5305\u542b\u4e86\u4e00\u4e2a\u4e66\u7b7e\uff08bookmark\uff09\uff0c\u8be5\u4e66\u7b7e\u7528\u6765\u544a\u8bc9InnoDB\u5b58\u50a8\u5f15\u64ce\uff0c\u54ea\u91cc\u53ef\u4ee5\u627e\u5230\u4e0e\u7d22\u5f15\u76f8\u5bf9\u5e94\u7684\u884c\u6570\u636e\u3002<\/span>\u56e0\u4e3aInnoDB\u5b58\u50a8\u5f15\u64ce\u8868\u662f\u7d22\u5f15\u7ec4\u7ec7\u8868\uff0c\u56e0\u6b64InnoDB\u5b58\u50a8\u5f15\u64ce\u7684\u8f85\u52a9\u7d22\u5f15\u7684\u4e66\u7b7e\u5c31\u662f\u76f8\u5e94\u884c\u6570\u636e\u7684\u805a\u96c6\u7d22\u5f15\u952e\u3002\u663e\u793a\u4e86InnoDB\u5b58\u50a8\u5f15\u64ce\u4e2d\u8f85\u52a9\u7d22\u5f15\u4e0e\u805a\u96c6\u7d22\u5f15\u7684\u5173\u7cfb\u3002<\/p>\n<p><a href=\"https:\/\/yanjingang.com\/blog\/?attachment_id=2919\" rel=\"attachment wp-att-2919\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-2919\" src=\"https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/2.png\" alt=\"\" width=\"553\" height=\"578\" srcset=\"https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/2.png 553w, https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/2-287x300.png 287w\" sizes=\"(max-width: 553px) 100vw, 553px\" \/><\/a><\/p>\n<div class=\"r_c\">\n<p class=\" \">\u8f85\u52a9\u7d22\u5f15\u7684\u5b58\u5728\u5e76\u4e0d\u5f71\u54cd\u6570\u636e\u5728\u805a\u96c6\u7d22\u5f15\u4e2d\u7684\u7ec4\u7ec7\uff0c\u56e0\u6b64\u6bcf\u5f20\u8868\u4e0a\u53ef\u4ee5\u6709\u591a\u4e2a\u8f85\u52a9\u7d22\u5f15\u3002\u5f53\u901a\u8fc7\u8f85\u52a9\u7d22\u5f15\u6765\u5bfb\u627e\u6570\u636e\u65f6\uff0cInnoDB\u5b58\u50a8\u5f15\u64ce\u4f1a\u904d\u5386\u8f85\u52a9\u7d22\u5f15\u5e76\u901a\u8fc7\u53f6\u7ea7\u522b\u7684\u6307\u9488\u83b7\u5f97\u6307\u5411\u4e3b\u952e\u7d22\u5f15\u7684\u4e3b\u952e\uff0c\u7136\u540e\u518d\u901a\u8fc7\u4e3b\u952e\u7d22\u5f15\u6765\u627e\u5230\u4e00\u4e2a\u5b8c\u6574\u7684\u884c\u8bb0\u5f55\u3002\u4e3e\u4f8b\u6765\u8bf4\uff0c\u5982\u679c\u5728\u4e00\u9897\u9ad8\u5ea6\u4e3a3\u7684\u8f85\u52a9\u7d22\u5f15\u6811\u4e2d\u67e5\u627e\u6570\u636e\uff0c\u90a3\u4e48\u9700\u8981\u5bf9\u8fd9\u9897\u8f85\u52a9\u7d22\u5f15\u904d\u53863\u6b21\u627e\u5230\u6307\u5b9a\u4e3b\u952e\uff1b\u5982\u679c\u805a\u96c6\u7d22\u5f15\u6811\u7684\u9ad8\u5ea6\u540c\u6837\u4e3a3\uff0c\u90a3\u4e48\u8fd8\u9700\u8981\u5bf9\u805a\u96c6\u7d22\u5f15\u8fdb\u884c3\u6b21\u67e5\u627e\uff0c\u624d\u80fd\u6700\u7ec8\u627e\u5230\u4e00\u4e2a\u5b8c\u6574\u7684\u884c\u6570\u636e\u6240\u5728\u7684\u9875\uff0c\u56e0\u6b64\u4e00\u5171\u9700\u89816\u6b21\u903b\u8f91IO\u6765\u8bbf\u95ee\u6700\u7ec8\u7684\u4e00\u4e2a\u6570\u636e\u9875\u3002<\/p>\n<p class=\" \"><span style=\"color: #ff0000;\">\u5bf9\u4e8e\u5176\u4ed6\u7684\u4e00\u4e9b\u6570\u636e\u5e93\uff0c\u5982Microsoft SQL Server\u6570\u636e\u5e93\uff0c\u5176\u8868\u7c7b\u578b\u6709\u4e00\u79cd\u4e0d\u662f\u7d22\u5f15\u7ec4\u7ec7\u8868\uff0c\u79f0\u4e3a\u5806\u8868\u3002\u5728\u6570\u636e\u7684\u5b58\u653e\u6309\u63d2\u5165\u987a\u5e8f\u65b9\u9762\uff0c\u4e0eMySQL\u7684MyISAM\u5b58\u50a8\u5f15\u64ce\u6709\u4e9b\u7c7b\u4f3c\u3002<\/span>\u5806\u8868\u7684\u7279\u6027\u51b3\u5b9a\u4e86\u5806\u8868\u4e0a\u7684\u7d22\u5f15\u90fd\u662f\u975e\u805a\u96c6\u7684\uff0c\u4f46\u662f\u5806\u8868\u6ca1\u6709\u4e3b\u952e\u3002\u56e0\u6b64\u8fd9\u65f6\u4e66\u7b7e\u662f\u4e00\u4e2a\u884c\u6807\u8bc6\u7b26\uff08row identifier\uff0cRID\uff09\uff0c\u53ef\u4ee5\u7528\u5982\u201c\u6587\u4ef6\u53f7\uff1a\u9875\u53f7\uff1a\u69fd\u53f7\u201d\u7684\u683c\u5f0f\u6765\u5b9a\u4f4d\u5b9e\u9645\u7684\u884c\u3002<\/p>\n<p class=\" \">\u5806\u8868\u7684\u975e\u805a\u96c6\u7d22\u5f15\u65e2\u7136\u4e0d\u9700\u8981\u518d\u901a\u8fc7\u4e3b\u952e\u5bf9\u805a\u96c6\u7d22\u5f15\u8fdb\u884c\u67e5\u627e\uff0c\u90a3\u4e0d\u662f\u901f\u5ea6\u4f1a\u66f4\u5feb\u5417\uff1f\u7b54\u6848\u662f\u4e5f\u8bb8\uff0c\u5728\u67d0\u4e9b\u53ea\u8bfb\u7684\u60c5\u51b5\u4e0b\uff0c\u4e66\u7b7e\u4e3a\u884c\u6807\u8bc6\u7b26\u65b9\u5f0f\u7684\u975e\u805a\u96c6\u7d22\u5f15\u53ef\u80fd\u4f1a\u6bd4\u4e66\u7b7e\u4e3a\u4e3b\u952e\u65b9\u5f0f\u7684\u975e\u805a\u96c6\u7d22\u5f15\u5feb\u3002\u4f46\u662f\u8003\u8651\u5728OLTP\uff08OnLine Transaction Processing\uff0c\u5728\u7ebf\u4e8b\u52a1\u5904\u7406\uff09\u5e94\u7528\u7684\u60c5\u51b5\u4e0b\uff0c\u8868\u53ef\u80fd\u8fd8\u9700\u8981\u53d1\u751f\u63d2\u5165\u3001\u66f4\u65b0\u3001\u5220\u9664\u7b49DML\u64cd\u4f5c\u3002\u5f53\u8fdb\u884c\u8fd9\u7c7b\u64cd\u4f5c\u65f6\uff0c\u4e66\u7b7e\u4e3a\u884c\u6807\u8bc6\u7b26\u65b9\u5f0f\u7684\u975e\u805a\u96c6\u7d22\u5f15\u53ef\u80fd\u9700\u8981\u4e0d\u65ad\u66f4\u65b0\u884c\u6807\u8bc6\u7b26\u6240\u6307\u5411\u6570\u636e\u9875\u7684\u4f4d\u7f6e\uff0c\u8fd9\u65f6\u7684\u5f00\u9500\u53ef\u80fd\u5c31\u4f1a\u5927\u4e8e\u4e66\u7b7e\u4e3a\u4e3b\u952e\u65b9\u5f0f\u7684\u975e\u805a\u96c6\u7d22\u5f15\u4e86\u3002<\/p>\n<p class=\" \">Microsoft SQL Server\u6570\u636e\u5e93DBA\u95ee\u8fc7\u8fd9\u6837\u7684\u95ee\u9898\uff0c\u4e3a\u4ec0\u4e48\u5728SQL Server\u4e0a\u8fd8\u8981\u4f7f\u7528\u7d22\u5f15\u7ec4\u7ec7\u8868\uff1f\u5806\u8868\u7684\u4e66\u7b7e\u6027\u4f7f\u5f97\u975e\u805a\u96c6\u67e5\u627e\u53ef\u4ee5\u6bd4\u4e3b\u952e\u4e66\u7b7e\u65b9\u5f0f\u66f4\u5feb\uff0c\u5e76\u4e14\u975e\u805a\u96c6\u53ef\u80fd\u5728\u4e00\u5f20\u8868\u4e2d\u5b58\u5728\u591a\u4e2a\uff0c\u6211\u4eec\u9700\u8981\u5bf9\u591a\u4e2a\u975e\u805a\u96c6\u7d22\u5f15\u7684\u67e5\u627e\u3002\u800c\u4e14\u5bf9\u4e8e\u975e\u805a\u96c6\u7d22\u5f15\u7684\u79bb\u6563\u8bfb\u53d6\uff0c\u7d22\u5f15\u7ec4\u7ec7\u8868\u4e0a\u7684\u975e\u805a\u96c6\u7d22\u5f15\u4f1a\u6bd4\u5806\u8868\u4e0a\u7684\u805a\u96c6\u7d22\u5f15\u6162\u4e00\u4e9b\u3002\u5f53\u7136\uff0c\u5728\u4e00\u4e9b\u60c5\u51b5\u4e0b\uff0c\u4f7f\u7528\u5806\u8868\u7684\u786e\u4f1a\u6bd4\u7d22\u5f15\u7ec4\u7ec7\u8868\u66f4\u5feb\uff0c\u4f46\u662f\u6211\u89c9\u5f97\u5927\u90e8\u5206\u662f\u7531\u4e8e\u5b58\u5728\u4e8eOLAP\uff08On-Line Analytical Processing\uff0c\u5728\u7ebf\u5206\u6790\u5904\u7406\uff09\u7684\u5e94\u7528\u3002\u5176\u6b21\u5c31\u662f\u524d\u9762\u63d0\u5230\u7684\uff0c\u8868\u4e2d\u6570\u636e\u662f\u5426\u9700\u8981\u66f4\u65b0\uff0c\u5e76\u4e14\u66f4\u65b0\u4f1a\u5426\u5f71\u54cd\u5230\u7269\u7406\u5730\u5740\u7684\u53d8\u66f4\u3002<span style=\"color: #ff0000;\">\u6b64\u5916\u53e6\u4e00\u4e2a\u4e0d\u80fd\u5ffd\u89c6\u7684\u662f\u5bf9\u4e8e\u6392\u5e8f\u548c\u8303\u56f4\u67e5\u627e\uff0c\u7d22\u5f15\u7ec4\u7ec7\u8868\u53ef\u4ee5\u901a\u8fc7B+\u6811\u7684\u4e2d\u95f4\u8282\u70b9\u5c31\u627e\u5230\u8981\u67e5\u627e\u7684\u6240\u6709\u9875\uff0c\u7136\u540e\u8fdb\u884c\u8bfb\u53d6\uff0c\u800c\u5806\u8868\u7684\u7279\u6027\u51b3\u5b9a\u4e86\u8fd9\u5bf9\u5176\u662f\u4e0d\u80fd\u5b9e\u73b0\u7684\u3002<\/span>\u6700\u540e\uff0c\u975e\u805a\u96c6\u7d22\u5f15\u7684\u79bb\u6563\u8bfb\uff0c\u7684\u786e\u662f\u5b58\u5728\u4e0a\u8ff0\u60c5\u51b5\uff0c\u4f46\u662f\u4e00\u822c\u7684\u6570\u636e\u5e93\u90fd\u901a\u8fc7\u5b9e\u73b0\u9884\u8bfb\uff08read ahead\uff09\u6280\u672f\u6765\u907f\u514d\u591a\u6b21\u7684\u79bb\u6563\u8bfb\u64cd\u4f5c\u3002\u56e0\u6b64\uff0c\u5177\u4f53\u662f\u5efa\u5806\u8868\u8fd8\u662f\u7d22\u5f15\u7ec4\u7ec7\u8868\uff0c\u8fd9\u53d6\u51b3\u4e8e\u4f60\u7684\u5e94\u7528\uff0c\u4e0d\u5b58\u5728\u54ea\u4e2a\u66f4\u4f18\u7684\u60c5\u51b5\u3002\u8fd9\u548cInnoDB\u5b58\u50a8\u5f15\u64ce\u597d\u8fd8\u662fMyISAM\u5b58\u50a8\u5f15\u64ce\u597d\u7684\u95ee\u9898\u662f\u4e00\u6837\u7684\uff0c\u5177\u4f53\u60c5\u51b5\u5177\u4f53\u5206\u6790\u3002<\/p>\n<\/div>\n<p>\u63a5\u4e0b\u6765\uff0c\u6211\u4eec\u901a\u8fc7\u9605\u8bfb\u8868\u7a7a\u95f4\u6587\u4ef6\u6765\u5206\u6790InnoDB\u5b58\u50a8\u5f15\u64ce\u7684\u975e\u805a\u96c6\u7d22\u5f15\uff0c\u6211\u4eec\u8fd8\u662f\u5206\u6790\u4e0a\u4e00\u5c0f\u8282\u6240\u7528\u7684\u8868t\u3002<\/p>\n<p>\u4e0d\u540c\u7684\u662f\uff0c\u5728\u8868t\u4e0a\u518d\u5efa\u7acb\u4e00\u4e2a\u5217c\uff0c\u5e76\u5bf9\u5217c\u521b\u5efa\u975e\u805a\u96c6\u7d22\u5f15\uff1a<\/p>\n<p>alter table t add c int not null;<\/p>\n<p>update t set c=0-a;<\/p>\n<p>alter table t add key idx_c(c);<\/p>\n<p>show index from t;<\/p>\n<p>select a,c from t;<\/p>\n<p class=\" \">\u7136\u540e\u7528py_innodb_page_info\u5de5\u5177\u6765\u5206\u6790\u8868\u7a7a\u95f4\uff0c\u53ef\u5f97\uff1apy_innodb_page_info.py-v t.ibd<\/p>\n<p>\u5bf9\u6bd4\u524d\u4e00\u6b21\u6211\u4eec\u7684\u5206\u6790\uff0c\u53ef\u4ee5\u770b\u5230\u8fd9\u6b21\u591a\u4e86\u4e00\u4e2a\u9875\u3002\u5206\u6790page offset\u4e3a4\u7684\u9875\uff0c\u8be5\u9875\u4e3a\u975e\u805a\u96c6\u7d22\u5f15\u6240\u5728\u9875\uff0c\u901a\u8fc7\u5de5\u5177hexdump\u5206\u6790\u53ef\u5f97\uff1a<\/p>\n<p>\u56e0\u4e3a\u53ea\u67094\u884c\u6570\u636e\uff0c\u5e76\u4e14\u5217c\u53ea\u67094\u4e2a\u5b57\u8282\uff0c\u56e0\u6b64\u5728\u4e00\u4e2a\u975e\u805a\u96c6\u7d22\u5f15\u9875\u4e2d\u5373\u53ef\u5b8c\u6210\uff0c\u6574\u7406\u5206\u6790\u53ef\u5f97\u4e0b\u56fe\u6240\u793a\u7684\u5173\u7cfb\uff1a<\/p>\n<p><a href=\"https:\/\/yanjingang.com\/blog\/?attachment_id=2918\" rel=\"attachment wp-att-2918\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-2918\" src=\"https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/3.png\" alt=\"\" width=\"692\" height=\"529\" srcset=\"https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/3.png 692w, https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/3-300x229.png 300w, https:\/\/yanjingang.com\/blog\/wp-content\/uploads\/2019\/08\/3-624x477.png 624w\" sizes=\"(max-width: 692px) 100vw, 692px\" \/><\/a><\/p>\n<p>\u663e\u793a\u4e86\u8868t\u4e2d\u8f85\u52a9\u7d22\u5f15idx_c\u548c\u805a\u96c6\u7d22\u5f15\u7684\u5173\u7cfb\u3002\u53ef\u4ee5\u770b\u5230\u8f85\u52a9\u7d22\u5f15\u7684\u53f6\u8282\u70b9\u4e2d\u5305\u542b\u4e86\u5217c\u7684\u503c\u548c\u4e3b\u952e\u7684\u503c\u3002\u8fd9\u91cc\u952e\u503c\u56e0\u4e3a\u6211\u7279\u610f\u8bbe\u4e3a\u8d1f\u503c\uff0c\u4f60\u4f1a\u53d1\u73b0-1\u4ee57f ff ff ff\u7684\u65b9\u5f0f\u8fdb\u884c\u5185\u90e8\u5b58\u50a8\u30027\uff080111\uff09\u6700\u9ad8\u4f4d\u4e3a0\uff0c\u4ee3\u8868\u8d1f\u503c\uff0c\u5b9e\u9645\u7684\u503c\u5e94\u8be5\u53d6\u53cd\u540e\uff0c\u52a01\uff0c\u5373\u5f97-1\u3002<\/p>\n<h2 id=\"blogTitle2\" class=\" \">B+\u6811\u7d22\u5f15\u7684\u7ba1\u7406<\/h2>\n<p class=\" \">\u7d22\u5f15\u7684\u521b\u5efa\u548c\u5220\u9664\u53ef\u4ee5\u901a\u8fc7\u4e24\u79cd\u65b9\u6cd5\uff0c\u4e00\u79cd\u662fALTER TABLE\uff0c\u53e6\u4e00\u79cd\u662fCREATE\/DROP INDEX\u3002ALTER TABLE\u521b\u5efa\u7d22\u5f15\u7684\u8bed\u6cd5\u4e3a\uff1a<\/p>\n<p class=\" \">ALTER TABLE tbl_name<\/p>\n<p class=\" \">|ADD{INDEX|KEY}[index_name]<\/p>\n<p class=\" \">[index_type] (index_col_name\uff0c\u2026\u2026) [index_option]\u2026\u2026<\/p>\n<p class=\" \">ALTER TABLE tbl_name<\/p>\n<p class=\" \">DROP PRIMARY KEY<\/p>\n<p class=\" \">|DROP {INDEX|KEY} index_name<\/p>\n<p class=\" \">CREATE\/DROP INDEX\u7684\u8bed\u6cd5\u540c\u6837\u5f88\u7b80\u5355\uff1a<\/p>\n<p class=\" \">CREATE [UNIQUE] INDEX index_name<\/p>\n<p class=\" \">[index_type]<\/p>\n<p class=\" \">ON tbl_name\uff08index_col_name\uff0c\u2026\u2026\uff09<\/p>\n<p class=\" \">DROP INDEX index_name ON tbl_name<\/p>\n<p class=\" \">\u7d22\u5f15\u53ef\u4ee5\u7d22\u5f15\u6574\u4e2a\u5217\u7684\u6570\u636e\uff0c\u4e5f\u53ef\u4ee5\u53ea\u7d22\u5f15\u4e00\u4e2a\u5217\u7684\u5f00\u5934\u90e8\u5206\u6570\u636e\uff0c\u5982\u524d\u9762\u6211\u4eec\u521b\u5efa\u7684\u8868t\uff0cb\u5217\u4e3avarchar(8000)\uff0c\u4f46\u662f\u6211\u4eec\u53ef\u4ee5\u53ea\u7d22\u5f15\u524d100\u4e2a\u5b57\u6bb5\uff0c\u5982\uff1a<\/p>\n<p class=\" \">alter table t add key idx_b (b(100));<\/p>\n<p class=\" \">\u76ee\u524dMySQL\u6570\u636e\u5e93\u5b58\u5728\u7684\u4e00\u4e2a\u666e\u904d\u95ee\u9898\u662f\uff0c<span style=\"color: #ff0000;\">\u6240\u6709\u5bf9\u4e8e\u7d22\u5f15\u7684\u6dfb\u52a0\u6216\u8005\u5220\u9664\u64cd\u4f5c\uff0cMySQL\u6570\u636e\u5e93\u662f\u5148\u521b\u5efa\u4e00\u5f20\u65b0\u7684\u4e34\u65f6\u8868\uff0c\u7136\u540e\u628a\u6570\u636e\u5bfc\u5165\u4e34\u65f6\u8868\uff0c\u5220\u9664\u539f\u8868\uff0c\u518d\u628a\u4e34\u65f6\u8868\u91cd\u540d\u4e3a\u539f\u6765\u7684\u8868\u540d\u3002<\/span>\u56e0\u6b64\u5bf9\u4e8e\u4e00\u5f20\u5927\u8868\uff0c\u6dfb\u52a0\u548c\u5220\u9664\u7d22\u5f15\u9700\u8981\u5f88\u957f\u7684\u65f6\u95f4\u3002\u5bf9\u4e8e\u4eceMicrosoft SQL Server\u6216Oracle\u6570\u636e\u5e93\u7684DBA\u6765\u8bf4\uff0cMySQL\u6570\u636e\u5e93\u7684\u7d22\u5f15\u7ef4\u62a4\u59cb\u7ec8\u8ba9\u4ed6\u4eec\u975e\u5e38\u82e6\u607c\u3002<\/p>\n<p class=\" \">InnoDB\u5b58\u50a8\u5f15\u64ce\u4ece\u7248\u672cInnoDB Plugin\u5f00\u59cb\uff0c\u652f\u6301\u4e00\u79cd\u79f0\u4e3a\u5feb\u901f\u7d22\u5f15\u521b\u5efa\u65b9\u6cd5\u3002\u5f53\u7136\u8fd9\u79cd\u65b9\u6cd5\u53ea\u9650\u5b9a\u4e8e\u8f85\u52a9\u7d22\u5f15\uff0c\u5bf9\u4e8e\u4e3b\u952e\u7684\u521b\u5efa\u548c\u5220\u9664\u8fd8\u662f\u9700\u8981\u91cd\u5efa\u4e00\u5f20\u8868\u3002<span style=\"color: #ff0000;\">\u5bf9\u4e8e\u8f85\u52a9\u7d22\u5f15\u7684\u521b\u5efa\uff0cInnoDB\u5b58\u50a8\u5f15\u64ce\u4f1a\u5bf9\u8868\u52a0\u4e0a\u4e00\u4e2aS\u9501\u3002\u5728\u521b\u5efa\u7684\u8fc7\u7a0b\u4e2d\uff0c\u4e0d\u9700\u8981\u91cd\u5efa\u8868\uff0c\u56e0\u6b64\u901f\u5ea6\u6781\u5feb\u3002\u4f46\u662f\u5728\u521b\u5efa\u7684\u8fc7\u7a0b\u4e2d\uff0c\u7531\u4e8e\u4e0a\u4e86S\u9501\uff0c\u56e0\u6b64\u521b\u5efa\u7684\u8fc7\u7a0b\u4e2d\u8be5\u8868\u53ea\u80fd\u8fdb\u884c\u8bfb\u64cd\u4f5c\u3002\u5220\u9664\u8f85\u52a9\u7d22\u5f15\u64cd\u4f5c\u5c31\u66f4\u7b80\u5355\u4e86\uff0c\u53ea\u9700\u5728InnoDB\u5b58\u50a8\u5f15\u64ce\u7684\u5185\u90e8\u89c6\u56fe\u66f4\u65b0\u4e0b\uff0c\u5c06\u8f85\u52a9\u7d22\u5f15\u7684\u7a7a\u95f4\u6807\u8bb0\u4e3a\u53ef\u7528\uff0c\u5e76\u5220\u9664MySQL\u5185\u90e8\u89c6\u56fe\u4e0a\u5bf9\u4e8e\u8be5\u8868\u7684\u7d22\u5f15\u5b9a\u4e49\u5373\u53ef\u3002<\/span><\/p>\n<p class=\" \">\u67e5\u770b\u8868\u4e2d\u7d22\u5f15\u7684\u4fe1\u606f\u53ef\u4ee5\u4f7f\u7528SHOW INDEX\u8bed\u53e5\u3002\u5982\u6211\u4eec\u6765\u5206\u6790\u8868t\uff0c\u4e4b\u524d\u5148\u52a0\u4e00\u4e2a\u8054\u5408\u7d22\u5f15\uff0c\u53ef\u5f97\uff1a<\/p>\n<p class=\" \">alter table t add key idx_a_b(a,c);<\/p>\n<p class=\" \">show index from t;<\/p>\n<p class=\" \">\u56e0\u4e3a\u5728\u8868t\u4e0a\u67093\u4e2a\u7d22\u5f15\uff1a\u4e00\u4e2a\u4e3b\u952e\u7d22\u5f15\uff0cc\u5217\u4e0a\u7684\u7d22\u5f15\uff0c\u548cb\u5217\u524d100\u4e2a\u5b57\u8282\u6784\u6210\u7684\u7d22\u5f15\u3002<\/p>\n<p class=\" \">\u63a5\u7740\u6211\u4eec\u6765\u5177\u4f53\u8bb2\u89e3\u6bcf\u4e2a\u5217\u7684\u542b\u4e49\uff1a<\/p>\n<ol>\n<li class=\" \">Table\uff1a\u7d22\u5f15\u6240\u5728\u7684\u8868\u540d\u3002<\/li>\n<li class=\" \">Non_unique\uff1a\u975e\u552f\u4e00\u7684\u7d22\u5f15\uff0c\u53ef\u4ee5\u770b\u5230primary key\u662f0\uff0c\u56e0\u4e3a\u5fc5\u987b\u662f\u552f\u4e00\u7684\u3002<\/li>\n<li class=\" \">Key_name\uff1a\u7d22\u5f15\u7684\u540d\u79f0\uff0c\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7\u8fd9\u4e2a\u540d\u79f0\u6765DROP INDEX\u3002<\/li>\n<li class=\" \">Seq_in_index\uff1a\u7d22\u5f15\u4e2d\u8be5\u5217\u7684\u4f4d\u7f6e\uff0c\u5982\u679c\u770b\u8054\u5408\u7d22\u5f15idx_a_b\u5c31\u6bd4\u8f83\u76f4\u89c2\u4e86\u3002<\/li>\n<li class=\" \">Column_name\uff1a\u7d22\u5f15\u7684\u5217<\/li>\n<li class=\" \">Collation\uff1a\u5217\u4ee5\u4ec0\u4e48\u65b9\u5f0f\u5b58\u50a8\u5728\u7d22\u5f15\u4e2d\u3002\u53ef\u4ee5\u662f&#8217;A&#8217;\u6216\u8005NULL\u3002B+\u6811\u7d22\u5f15\u603b\u662fA\uff0c\u5373\u6392\u5e8f\u7684\u3002\u5982\u679c\u4f7f\u7528\u4e86Heap\u5b58\u50a8\u5f15\u64ce\uff0c\u5e76\u4e14\u5efa\u7acb\u4e86Hash\u7d22\u5f15\uff0c\u8fd9\u91cc\u5c31\u4f1a\u663e\u793aNULL\u4e86\u3002\u56e0\u4e3aHash\u6839\u636eHash\u6876\u6765\u5b58\u653e\u7d22\u5f15\u6570\u636e\uff0c\u800c\u4e0d\u662f\u5bf9\u6570\u636e\u8fdb\u884c\u6392\u5e8f\u3002<\/li>\n<li class=\" \">Cardinality\uff1a\u975e\u5e38\u5173\u952e\u7684\u503c\uff0c\u8868\u793a\u7d22\u5f15\u4e2d\u552f\u4e00\u503c\u7684\u6570\u76ee\u7684\u4f30\u8ba1\u503c\u3002Cardinality\/\u8868\u7684\u884c\u6570\u5e94\u5c3d\u53ef\u80fd\u63a5\u8fd11\uff0c\u5982\u679c\u975e\u5e38\u5c0f\uff0c\u90a3\u4e48\u9700\u8981\u8003\u8651\u662f\u5426\u8fd8\u9700\u8981\u5efa\u8fd9\u4e2a\u7d22\u5f15\u3002<\/li>\n<li class=\" \">Sub_part\uff1a\u662f\u5426\u662f\u5217\u7684\u90e8\u5206\u88ab\u7d22\u5f15\u3002\u5982\u679c\u770bidx_b\u8fd9\u4e2a\u7d22\u5f15\uff0c\u8fd9\u91cc\u663e\u793a100\uff0c\u8868\u793a\u6211\u4eec\u53ea\u7d22\u5f15b\u5217\u7684\u524d100\u4e2a\u5b57\u7b26\u3002\u5982\u679c\u7d22\u5f15\u6574\u4e2a\u5217\uff0c\u5219\u8be5\u5b57\u6bb5\u4e3aNULL\u3002<\/li>\n<li class=\" \">Packed\uff1a\u5173\u952e\u5b57\u5982\u4f55\u88ab\u538b\u7f29\u3002\u5982\u679c\u6ca1\u6709\u88ab\u538b\u7f29\uff0c\u5219\u4e3aNULL\u3002<\/li>\n<li class=\" \">Null\uff1a\u662f\u5426\u7d22\u5f15\u7684\u5217\u542b\u6709NULL\u503c\u3002\u53ef\u4ee5\u770b\u5230idx_b\u8fd9\u91cc\u4e3aYes\u3002\u56e0\u4e3a\u6211\u4eec\u5b9a\u4e49\u7684b\u5217\u5141\u8bb8NULL\u503c\u3002<\/li>\n<li class=\" \">Index_type\uff1a\u7d22\u5f15\u7684\u7c7b\u578b\u3002InnoDB\u5b58\u50a8\u5f15\u64ce\u53ea\u652f\u6301B+\u6811\u7d22\u5f15\uff0c\u6240\u4ee5\u8fd9\u91cc\u663e\u793a\u7684\u90fd\u662fBTREE\u3002<\/li>\n<li class=\" \">Comment\uff1a\u6ce8\u91ca\u3002<\/li>\n<\/ol>\n<p class=\" \"><span style=\"color: #ff0000;\">Cardinality\u503c\u975e\u5e38\u5173\u952e\uff0c\u4f18\u5316\u5668\u4f1a\u6839\u636e\u8fd9\u4e2a\u503c\u6765\u5224\u65ad\u662f\u5426\u4f7f\u7528\u8fd9\u4e2a\u7d22\u5f15<\/span>\u3002\u4f46\u662f\u8fd9\u4e2a\u503c\u5e76\u4e0d\u662f\u5b9e\u65f6\u66f4\u65b0\u7684\uff0c\u5e76\u975e\u6bcf\u6b21\u7d22\u5f15\u7684\u66f4\u65b0\u90fd\u4f1a\u66f4\u65b0\u8be5\u503c\uff0c\u56e0\u4e3a\u8fd9\u6837\u4ee3\u4ef7\u592a\u5927\u4e86\u3002\u56e0\u6b64\u8fd9\u4e2a\u503c\u662f\u4e0d\u592a\u51c6\u786e\u7684\uff0c\u53ea\u662f\u4e00\u4e2a\u5927\u6982\u7684\u503c\u3002\u4e0a\u9762\u663e\u793a\u7684\u7ed3\u679c\u4e3b\u952e\u7684Cardinality\u4e3a2\uff0c\u4f46\u662f\u5f88\u663e\u7136\u6211\u4eec\u8868\u4e2d\u67094\u6761\u8bb0\u5f55\uff0c\u8fd9\u4e2a\u503c\u5e94\u8be5\u662f4\u3002\u5982\u679c\u9700\u8981\u66f4\u65b0\u7d22\u5f15Cardinality\u7684\u4fe1\u606f\uff0c\u53ef\u4ee5\u4f7f\u7528ANALYZE TABLE\u547d\u4ee4\u3002\u5982\uff1a<\/p>\n<p class=\" \"><span style=\"color: #ff0000;\">analyze table t;<\/span><\/p>\n<p class=\" \">show index from t;<\/p>\n<p class=\" \">\u8fd9\u65f6\u7684Cardinality\u7684\u503c\u5c31\u5bf9\u4e86\u3002\u4e0d\u8fc7\uff0c\u5728\u6bcf\u4e2a\u7cfb\u7edf\u4e0a\u53ef\u80fd\u5f97\u5230\u7684\u7ed3\u679c\u4e0d\u4e00\u6837\uff0c\u56e0\u4e3aANALYZE TABLE\u73b0\u5728\u8fd8\u5b58\u5728\u4e00\u4e9b\u95ee\u9898\uff0c\u53ef\u80fd\u4f1a\u5f71\u54cd\u5f97\u5230\u6700\u540e\u7684\u7ed3\u679c\u3002<\/p>\n<p class=\" \">\u53e6\u4e00\u4e2a\u95ee\u9898\u662fMySQL\u6570\u636e\u5e93\u5bf9\u4e8eCardinality\u8ba1\u6570\u7684\u95ee\u9898\uff0c\u5728\u8fd0\u884c\u4e00\u6bb5\u65f6\u95f4\u540e\uff0c\u53ef\u80fd\u4f1a\u770b\u5230\u4e0b\u9762\u7684\u7ed3\u679c\uff1a<\/p>\n<p class=\" \">show index from Profile;<\/p>\n<p class=\" \"><span style=\"color: #ff0000;\">Cardinality\u4e3aNULL\uff0c\u5728\u67d0\u4e9b\u60c5\u51b5\u4e0b\u53ef\u80fd\u4f1a\u53d1\u751f\u7d22\u5f15\u5efa\u7acb\u4e86\u3001\u4f46\u662f\u6ca1\u6709\u7528\u5230\uff0c\u6216\u8005explain\u4e24\u6761\u57fa\u672c\u4e00\u6837\u7684\u8bed\u53e5\uff0c\u4f46\u662f\u6700\u7ec8\u51fa\u6765\u7684\u7ed3\u679c\u4e0d\u4e00\u6837\u3002<\/span>\u4e00\u4e2a\u4f7f\u7528\u7d22\u5f15\uff0c\u53e6\u5916\u4e00\u4e2a\u4f7f\u7528\u5168\u8868\u626b\u63cf\uff0c<span style=\"color: #ff0000;\">\u8fd9\u65f6\u6700\u597d\u7684\u89e3\u51b3\u529e\u6cd5\u5c31\u662f\u505a\u4e00\u6b21ANALYZE TABLE\u7684\u64cd\u4f5c\u3002<\/span>\u56e0\u6b64\u6211\u5efa\u8bae\u5728\u4e00\u4e2a\u975e\u9ad8\u5cf0\u65f6\u95f4\uff0c\u5bf9\u5e94\u7528\u7a0b\u5e8f\u4e0b\u7684\u51e0\u5f20\u6838\u5fc3\u8868\u505aANALYZE TABLE\u64cd\u4f5c\uff0c\u8fd9\u80fd\u4f7f\u4f18\u5316\u5668\u548c\u7d22\u5f15\u66f4\u597d\u5730\u4e3a\u4f60\u5de5\u4f5c\u3002<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #999999;\">\u6458\u81ea\uff1ahttps:\/\/www.cnblogs.com\/wade-luffy\/p\/6292991.html<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>B+\u6811\u7d22\u5f15\u5176\u672c\u8d28\u5c31\u662fB+\u6811\u5728\u6570\u636e\u5e93\u4e2d\u7684\u5b9e\u73b0\uff0c\u4f46\u662fB+\u7d22\u5f15\u5728\u6570\u636e\u5e93\u4e2d\u6709\u4e00\u4e2a\u7279\u70b9\u5c31\u662f\u5176\u9ad8\u6247\u51fa\u6027\uff0c\u56e0\u6b64\u5728\u6570\u636e\u5e93\u4e2d\uff0cB [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[850,849,8,851,852],"_links":{"self":[{"href":"https:\/\/yanjingang.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2917"}],"collection":[{"href":"https:\/\/yanjingang.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yanjingang.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yanjingang.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yanjingang.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2917"}],"version-history":[{"count":0,"href":"https:\/\/yanjingang.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2917\/revisions"}],"wp:attachment":[{"href":"https:\/\/yanjingang.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2917"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yanjingang.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2917"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yanjingang.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2917"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}