{"id":179,"date":"2022-06-05T17:13:50","date_gmt":"2022-06-05T09:13:50","guid":{"rendered":"https:\/\/cococat.top\/?p=179"},"modified":"2022-06-05T17:13:50","modified_gmt":"2022-06-05T09:13:50","slug":"mysql_innodb_learning_note","status":"publish","type":"post","link":"https:\/\/cococat.top\/index.php\/2022\/06\/05\/mysql_innodb_learning_note\/","title":{"rendered":"\u3010\u7f13\u6162\u65bd\u5de5\u4e2d\u3011\u5173\u7cfb\u578b\u6570\u636e\u5e93\u5b66\u4e60\u6574\u7406\uff08MySQL-InnoDB\uff09"},"content":{"rendered":"<h1>1. \u6570\u636e\u5e93\u7684\u4e09\u8303\u5f0f<\/h1>\n<p>1NF\uff1a\u5217\u5177\u6709\u539f\u5b50\u6027\uff0c\u4e0d\u53ef\u62c6\u5206\uff08\u4e5f\u53ef\u4ee5\u8868\u8ff0\u4e3a\u201c\u8868\u90fd\u662f\u4e8c\u7ef4\u7684\u201d\uff09<br \/>\n2NF\uff1a\u4e00\u4e2a\u8868\u5fc5\u987b\u6709\u4e00\u4e2a\u4e3b\u952e\uff0c\u5176\u4ed6\u5217\u5b8c\u5168\u4f9d\u8d56\u4e8e\u4e3b\u952e\uff08\u9650\u5236\u90e8\u5206\u4f9d\u8d56\uff0c\u5373\u4e0d\u53ef\u53ea\u4f9d\u8d56\u4e8e\u8054\u5408\u4e3b\u952e\u7684\u4e00\u90e8\u5206\uff09<br \/>\n3NF\uff1a\u5176\u4ed6\u5217\u5fc5\u987b\u548c\u4e3b\u952e\u76f4\u63a5\u76f8\u5173\uff0c\u4e0d\u80fd\u95f4\u63a5\u76f8\u5173\uff08\u9650\u5236\u5217\u7684\u5197\u4f59\u6027\uff0c\u4e0d\u53ef\u4f20\u9012\u4f9d\u8d56\uff09<\/p>\n<h1>2. \u4ec0\u4e48\u662f\u4e8b\u52a1\uff1f\u4e8b\u52a1\u7684ACID\u7279\u6027\uff1f\u5982\u4f55\u5b9e\u73b0\uff1f<\/h1>\n<p>\u4e8b\u52a1\u662f\u7528\u6237\u5b9a\u4e49\u7684\u4e00\u7ec4\u6570\u636e\u5e93\u547d\u4ee4\uff0c\u5177\u6709ACID\u7279\u6027\u3002<br \/>\nAtomic \u539f\u5b50\u6027\uff1a\u4e00\u6761\u4e8b\u52a1\u8981\u4e48\u5168\u90e8\u6210\u529f\u6267\u884c\uff0c\u8981\u4e48\u5168\u90e8\u5931\u8d25\u56de\u6eda<br \/>\nConsistency \u4e00\u81f4\u6027\uff1a\u65e0\u8bba\u4e8b\u52a1\u662f\u5426\u6210\u529f\uff0c\u6570\u636e\u5e93\u5b8c\u6574\u6027\u7ea6\u675f\u4e0d\u88ab\u7834\u574f\uff08eg.\u4e3b\u952e\u7ea6\u675f\uff0c\u5916\u952e\u7ea6\u675f\uff0c\u975e\u7a7a\u7ea6\u675f\uff0c\u552f\u4e00\u6027\u7ea6\u675f\uff0ccheck\u7ea6\u675f\uff09<br \/>\nIsolation \u9694\u79bb\u6027\uff1a\u591a\u4e2a\u4e8b\u52a1\u5e76\u53d1\u64cd\u4f5c\u65f6\uff0c\u5404\u4e2a\u4e8b\u52a1\u4e92\u4e0d\u5f71\u54cd<br \/>\nDurability \u6301\u4e45\u6027\uff1a\u4e8b\u52a1\u63d0\u4ea4\u540e\uff0c\u5bf9\u6570\u636e\u5e93\u7684\u6539\u53d8\u662f\u6c38\u4e45\u7684<br \/>\nACD\u662f\u7528\u65e5\u5fd7\u673a\u5236\u5b9e\u73b0\u7684\uff0c\u5982\u679c\u4e8b\u52a1\u53d1\u751f\u9519\u8bef\uff0c\u6839\u636e\u65e5\u5fd7\u56de\u6eda\u3002\u8be6\u7ec6\u533a\u5206\u4e0b\uff0cD\u662f\u7528redo log\u5b9e\u73b0\u7684\uff0cA\u548cC\u662f\u7528undo log\u5b9e\u73b0\u7684\u3002<br \/>\nI\u662f\u7528\u9501\u673a\u5236\u5b9e\u73b0\u7684\uff0c\u53ea\u6709\u6301\u6709\u9501\u7684\u624d\u53ef\u4ee5\u66f4\u65b0\u6570\u636e\u3002<\/p>\n<h1>3. MySQL\u9501\u7684\u7c7b\u578b<\/h1>\n<h2>3.1 \u6309\u8bfb\u5199\u5206\uff1a<\/h2>\n<h3>3.1.1 \u5171\u4eab\u9501<\/h3>\n<p>Shared Lock\uff0c\u53c8\u79f0S\u9501\u3001\u8bfb\u9501\u3002\u5bf9\u4e8e\u5df2\u7ecf\u52a0\u4e86\u8bfb\u9501\u7684\u6570\u636e\uff0c\u5176\u4ed6\u4e8b\u52a1\u4ecd\u7136\u53ef\u4ee5\u52a0\u8bfb\u9501\uff0c\u4f46\u662f\u4e0d\u80fd\u52a0\u5199\u9501\u3002<\/p>\n<h3>3.1.2 \u6392\u4ed6\u9501<\/h3>\n<p>eXclusive Lock\uff0c\u53c8\u79f0X\u9501\u3001\u5199\u9501\uff0c\u5bf9\u4e8e\u5df2\u7ecf\u52a0\u4e86\u5199\u9501\u7684\u6570\u636e\uff0c\u5176\u4ed6\u4e8b\u52a1\u4e0d\u53ef\u8bfb\u4e0d\u53ef\u5199\u3002InnoDB\u5728\u53ef\u91cd\u590d\u8bfb(RR\uff0cInnoDB\u9ed8\u8ba4\u9694\u79bb\u7ea7\u522b) \u9694\u79bb\u7b49\u7ea7\u4e0b\uff0c\u5bf9\u4e8eupdate\u3001delete\u548cinsert\u8bed\u53e5\uff0c\u4f1a\u81ea\u52a8\u7ed9\u6d89\u53ca\u7684\u6570\u636e\u884c\u52a0\u6392\u5b83\u9501<\/p>\n<h2>3.2 \u6309\u7c92\u5ea6\u5206\uff1a<\/h2>\n<h3>3.2.1 \u8868\u9501<\/h3>\n<p>\u5f00\u9500\u5c0f\uff0c\u52a0\u9501\u5feb\uff0c\u9ad8\u5e76\u53d1\u4e0b\u6027\u80fd\u4f4e\u3002<br \/>\n\u6570\u636e\u5e93\u5f15\u64ce\u603b\u662f\u4e00\u6b21\u6027\u6309\u76f8\u540c\u987a\u5e8f\u83b7\u53d6\u6240\u6709\u8868\u9501\uff0c\u8981\u4e48\u5168\u90e8\u6ee1\u8db3\u8981\u4e48\u5168\u90e8\u7b49\u5f85\uff0c\u6545\u80fd\u907f\u514d\u6b7b\u9501\u3002<\/p>\n<h3>3.2.2 \u884c\u9501<\/h3>\n<p>\u5f00\u9500\u5927\uff0c\u52a0\u9501\u6162\uff0c\u9ad8\u5e76\u53d1\u4e0b\u6027\u80fd\u9ad8\u3002InnoDB\u662f\u901a\u8fc7\u7ed9\u7d22\u5f15\u4e0a\u7684\u7d22\u5f15\u9879\u52a0\u9501\u6765\u5b9e\u73b0\u884c\u9501\u7684\uff0c\u6b63\u786e\u901a\u8fc7\u7d22\u5f15\u6761\u4ef6\u68c0\u7d22\u6570\u636e\u65f6\u624d\u4f1a\u4f7f\u7528\u884c\u9501\uff0c\u5426\u5219InnoDB\u4f1a\u6267\u884c\u5168\u8868\u626b\u63cf\uff0c\u6b64\u65f6\u5219\u4f1a\u4f7f\u7528\u8868\u9501\u3002<br \/>\n\u884c\u9501\u5728\u4e8b\u52a1\u6267\u884c\u5230\u5bf9\u5e94\u8bed\u53e5\u65f6\u83b7\u5f97\uff0c\u6545\u53ef\u80fd\u53d1\u751f\u6b7b\u9501\u3002<\/p>\n<h4>3.2.2.1 \u884c\u9501\u7684\u7ec6\u5206<\/h4>\n<p>\u5728InnoDB\u9ed8\u8ba4\u7684\u53ef\u91cd\u590d\u8bfb\u9694\u79bb\u7ea7\u522b\u4e0b\uff0c\u884c\u9501\u53ef\u4ee5\u8fdb\u4e00\u6b65\u7ec6\u5206\u4e3a\uff1a<\/p>\n<ol>\n<li>\u8bb0\u5f55\u9501\uff08Record\uff09\uff0c\u53ea\u9501\u7279\u5b9a\u7684\u5355\u884c\u3002\u8bb0\u5f55\u9501\u53ea\u4f1a\u5728\u552f\u4e00\u7d22\u5f15\uff08\u5305\u62ec\u4e3b\u952e\uff09\u4e0a\u4f7f\u7528\u7b49\u503c\u67e5\u8be2\u8bed\u53e5\u65f6\u88ab\u65bd\u52a0\uff0c\u5426\u5219\u52a0\u4e34\u952e\u9501\uff1b\u53ea\u4f7f\u7528\u8054\u5408\u552f\u4e00\u7d22\u5f15\u4e2d\u7684\u4e00\u90e8\u5206\u5217\u65f6\uff0c\u4e5f\u4f1a\u5bfc\u81f4\u8fdb\u5316\u4e3a\u4e34\u952e\u9501<\/li>\n<li>\u95f4\u9699\u9501\uff08GAP\uff09\uff0c\u9501\u5f00\u533a\u95f4(a,b)\uff0c\u6b64\u5f00\u533a\u95f4\u53ef\u4ee5\u662f\u7b2c\u4e00\u6761\u8bb0\u5f55\u4e4b\u524d\u7684\u8303\u56f4\u4e5f\u53ef\u4ee5\u662f\u6700\u540e\u4e00\u6761\u8bb0\u5f55\u4e4b\u540e\u7684\u8303\u56f4\u3002\u6ce8\u610f\u95f4\u9699\u9501\u7684\u610f\u4e49\u4ec5\u5728\u4e8e\u963b\u6b62\u5728\u533a\u95f4\u4e0a\u6267\u884c\u65b0\u7684\u6570\u636e\u63d2\u5165\uff0c<strong>\u6545\u95f4\u9699\u9501\u4e4b\u95f4\u4e92\u76f8\u517c\u5bb9<\/strong>\u3001\u5171\u4eab\u95f4\u9699\u9501\u548c\u6392\u4ed6\u95f4\u9699\u9501\u4e5f\u6ca1\u6709\u5b9e\u8d28\u533a\u522b\u3002\u8be6\u60c5\u67e5\u770b<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-locking.html#innodb-gap-locks\">MySQL\u5b98\u65b9\u6587\u6863<\/a><\/li>\n<li>\u4e34\u952e\u9501\uff08Next-Key\uff09\uff0c\u9501\u5de6\u5f00\u53f3\u95ed\u533a\u95f4(a,b]\uff0c\u662f\u8bb0\u5f55\u9501\u548c\u95f4\u9699\u9501\u7684\u7ed3\u5408\u3002\u548c\u95f4\u9699\u9501\u4e00\u6837\uff0c\u6b64\u533a\u95f4\u53ef\u4ee5\u662f\u7b2c\u4e00\u6761\u8bb0\u5f55\u4e4b\u524d\u7684\u8303\u56f4\uff0c\u800c\u5bf9\u4e8e\u6700\u540e\u4e00\u6761\u8bb0\u5f55\u4e4b\u540e\u7684\u8303\u56f4\u5219\u4ecd\u7136\u662f\u4e00\u4e2a\u5f00\u533a\u95f4(x, +\u221e)\u3002<\/li>\n<li>\u63d2\u5165\u610f\u5411\u9501\uff0c\u5728\u63d2\u5165\u4e00\u6761\u8bb0\u5f55\u884c\u524d\uff0c\u7531INSERT\u64cd\u4f5c\u4ea7\u751f\u7684\u4e00\u79cd\u7279\u6b8a\u7684<strong>\u95f4\u9699\u9501<\/strong>\u3002\u63d2\u5165\u610f\u5411\u9501\u4e4b\u95f4\u4e92\u4e0d\u6392\u65a5\uff0c\u6240\u4ee5\u5373\u4f7f\u591a\u4e2a\u4e8b\u52a1\u5728\u540c\u4e00\u533a\u95f4\u63d2\u5165\u591a\u6761\u8bb0\u5f55\uff0c\u53ea\u8981\u8bb0\u5f55\u672c\u8eab\uff08\u4e3b\u952e\u3001\u552f\u4e00\u7d22\u5f15\uff09\u4e0d\u51b2\u7a81\uff0c\u90a3\u4e48\u4e8b\u52a1\u4e4b\u95f4\u5c31\u4e0d\u4f1a\u51fa\u73b0\u51b2\u7a81\u7b49\u5f85\u3002<\/li>\n<\/ol>\n<h4>3.2.2.2 \u884c\u9501\u7684\u52a0\u9501\u89c4\u5219<\/h4>\n<p><strong>\u6ce8\uff1a\u4ee5\u4e0b\u89c4\u5219\u57fa\u4e8eInnoDB\u9ed8\u8ba4\u7684\u53ef\u91cd\u590d\u8bfb\u9694\u79bb\u7ea7\u522b\u3002\u5728\u8bfb\u5df2\u63d0\u4ea4\u9694\u79bb\u7ea7\u522b\u4e0b\uff0c\u53ea\u4f1a\u52a0\u8bb0\u5f55\u9501\u3002\u53c2\u80035.5\u8282<\/strong><\/p>\n<ol>\n<li>\u52a0\u9501\u7684\u57fa\u672c\u5355\u4f4d\u662f\u4e34\u952e\u9501<\/li>\n<li>\u67e5\u8be2\u8fc7\u7a0b\u4e2d\u8bbf\u95ee\u5230\u7684\u5bf9\u8c61\u90fd\u8981\u52a0\u9501<\/li>\n<li>\u7d22\u5f15\u7b49\u503c\u67e5\u8be2\uff0c\u4e14\u7ed9\u552f\u4e00\u7d22\u5f15\u52a0\u9501\u65f6\uff0c\u4e34\u952e\u9501\u4f1a\u9000\u5316\u4e3a\u8bb0\u5f55\u9501<\/li>\n<li>\u7d22\u5f15\u7b49\u503c\u67e5\u8be2\uff0c\u4e14\u5411\u53f3\u904d\u5386\u65f6\u6700\u540e\u4e00\u4e2a\u503c\u4e0d\u6ee1\u8db3\u67e5\u8be2\u6761\u4ef6\uff0c\u4e34\u952e\u9501\u4f1a\u9000\u5316\u4e3a\u95f4\u9699\u9501<\/li>\n<li>\u7d22\u5f15\u4e0a\u7684\u8303\u56f4\u67e5\u8be2\u4f1a\u8bbf\u95ee\u5230\u4e0d\u6ee1\u8db3\u6761\u4ef6\u7684\u7b2c\u4e00\u4e2a\u503c\u4e3a\u6b62<\/li>\n<\/ol>\n<p><a href=\"https:\/\/juejin.cn\/post\/7018137095315128328\">\u89c4\u5219\u6765\u6e90<\/a><\/p>\n<h4>3.2.2.3 \u95f4\u9699\u9501\/\u4e34\u952e\u9501\u7684\u8865\u5145\u8bf4\u660e<\/h4>\n<p>\u4e00\u4e9b\u6587\u732e\u2014\u2014\u5305\u62ecMySQL\u5b98\u65b9\u6587\u6863\u2014\u2014\u6307\u51fa\u5728\u552f\u4e00\u7d22\u5f15\u7684\u7b49\u503c\u67e5\u8be2\u4e0a\u4e0d<strong>\u9700\u8981<\/strong>\u95f4\u9699\u9501\uff1a<\/p>\n<blockquote>\n<p>Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.<\/p>\n<\/blockquote>\n<p>\u4f46\u662f\uff0c<strong>\u4e0d\u9700\u8981<\/strong>\u5e76\u4e0d\u4ee3\u8868\u95f4\u9699\u9501\/\u4e34\u952e\u9501\u5728\u552f\u4e00\u7d22\u5f15\u4e0a<strong>\u4e0d\u5b58\u5728<\/strong>\uff0c\u90e8\u5206\u535a\u5ba2\u4e2d\u201c\u95f4\u9699\u9501\u5b58\u5728\u4e8e\u975e\u552f\u4e00\u7d22\u5f15\u4e2d\u201d\u7684\u8868\u8ff0\u662f\u4e0d\u51c6\u786e\u7684\uff0c\u4e0d\u80fd\u7c97\u66b4\u5730\u6839\u636e\u7d22\u5f15\u662f\u5426\u5177\u6709\u552f\u4e00\u6027\u7ea6\u675f\u6765\u5224\u65ad\u95f4\u9699\u9501\u548c\u4e34\u952e\u9501\u7684\u5b58\u5728\u6027\u3002\u4ece\u903b\u8f91\u7684\u89d2\u5ea6\u4e0a\u6765\u8bf4\uff0c\u5982\u679c\u552f\u4e00\u7d22\u5f15\u4e0a\u4e0d\u5b58\u5728\u95f4\u9699\u9501\uff0c\u90a3\u4e48MVCC\u673a\u5236\u9632\u6b62\u5e7b\u8bfb\u7684\u7279\u6027\u4fbf\u4e0d\u6210\u7acb\uff08\u53c2\u96055.5\u8282\uff09\uff1b\u800c\u4ece\u5b9e\u9a8c\u9a8c\u8bc1\u7684\u89d2\u5ea6\u4e0a\uff0c\u901a\u8fc7\u8fdb\u884c3.2.1.2\u8282\u6765\u6e90\u9875\u9762\u7684\u5b9e\u9a8c\uff0c\u80fd\u591f\u8bc1\u5b9e\u8fd9\u4e94\u6761\u52a0\u9501\u89c4\u5219\u7684\u6b63\u786e\u6027\u3002\u6839\u636e\u4e0a\u8ff0\u89c4\u5219\uff0c\u5373\u4f7f\u662f\u5728\u552f\u4e00\u7d22\u5f15\u4e2d\uff0c\u4e5f\u4f1a\u7531\u4e8e\u4e34\u952e\u9501\u9000\u5316\u6216\u4f7f\u7528\u4e86\u8303\u56f4\u67e5\u8be2\u7b49\u539f\u56e0\u5bfc\u81f4\u95f4\u9699\u9501\u51fa\u73b0\u3002<br \/>\n\u8fd9\u91cc\u8be6\u8ff0\u4e24\u4e2a\u4f8b\u5b50\uff0c\u5bf9\u4e8e\u8868\uff1a<\/p>\n<pre><code class=\"language-sql\">create table t(\n    id int(10) not null primary key,\n    c int(10) not null,\n    d int(10) not null,\nindex(c));\ninsert into t values(0, 0, 0);\ninsert into t values(5, 5, 5);\ninsert into t values(10, 10, 10);\ninsert into t values(15, 15, 15);\ninsert into t values(20, 20, 20);\ninsert into t values(25, 25, 25);\ninsert into t values(30, 30, 30);<\/code><\/pre>\n<p>\u4f8b1. \u5f53\u5728\u552f\u4e00\u7d22\u5f15id\u4e0a\u6267\u884c\u7b49\u503c\u67e5\u8be2\uff0c\u4f46\u662f\u67e5\u8be2\u76ee\u6807\u5e76\u4e0d\u5b58\u5728\u65f6\uff0c\u6839\u636e\u52a0\u9501\u89c4\u52194\uff0c\u4f1a\u51fa\u73b0\u9000\u5316\u7684\u95f4\u9699\u9501\u3002<\/p>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">session1<\/th>\n<th style=\"text-align: center;\">session 2<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">begin;<\/td>\n<td style=\"text-align: center;\">\\<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">select * from t where id = 7 for update;<\/td>\n<td style=\"text-align: center;\">\\<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">begin;<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">insert into t values(8, 8, 8);<strong>blocked<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">update t set c = c + 1 where id = 10;<strong>pass<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">rollback;<\/td>\n<td style=\"text-align: center;\">\\<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">rollback;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u5728\u67e5\u8be2\u8fc7\u7a0b\u4e2d\u5bf9\u8bbf\u95ee\u5bf9\u8c61\u533a\u95f4(5, 10]\u52a0\u4e34\u952e\u9501\uff0c\u800c\u7531\u4e8e\u627e\u5230\u7684\u7d22\u5f1510\u5e76\u4e0d\u6ee1\u8db3\u7b49\u503c\u67e5\u8be2\u8981\u6c42id = 7\uff0c\u6545\u9000\u5316\u4e3a\u95f4\u9699\u9501\uff0c\u9501\u4f4f\u533a\u95f4(5, 10)\uff0c\u5bfc\u81f4\u4f1a\u8bdd2\u63d2\u5165id=8\u7684\u6570\u636e\u963b\u585e\u3002<\/p>\n<p>\u4f8b2. \u5f53\u5728\u552f\u4e00\u7d22\u5f15id\u4e0a\u6267\u884c\u8303\u56f4\u67e5\u8be2\uff0c\u6839\u636e\u52a0\u9501\u89c4\u52195\uff0c\u53ef\u80fd\u4f1a\u51fa\u73b0\u95f4\u9699\u9501\u3002<\/p>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">session1<\/th>\n<th style=\"text-align: center;\">session 2<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">begin;<\/td>\n<td style=\"text-align: center;\">\\<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">select * from t where id &gt;= 10 and id &lt; 15 for update;<\/td>\n<td style=\"text-align: center;\">\\<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">begin;<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">insert into t values(8, 8, 8);<strong>passed<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">insert into t values(12, 12, 12);<strong>blocked<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">update t set d = d + 1 where id = 10;<strong>blocked<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">update t set d = d + 1 where id = 15;<strong>pass<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">rollback;<\/td>\n<td style=\"text-align: center;\">\\<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\\<\/td>\n<td style=\"text-align: center;\">rollback;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u5728\u67e5\u8be2\u8fc7\u7a0b\u4e2d\u9996\u5148\u5728\u533a\u95f4(5, 10]\u52a0\u4e34\u952e\u9501\uff0c\u627e\u5230\u7684\u7d22\u5f15\u662f10\uff0c\u6ee1\u8db3\u7b49\u503c\u67e5\u8be2\u7684\u8981\u6c42\uff0c\u6839\u636e\u89c4\u52193\uff0c\u6b64\u5904\u9000\u5316\u4e3a\u8bb0\u5f55\u9501[10]\uff1b\u7ee7\u7eed\u5411\u53f3\u904d\u5386\uff0c\u52a0\u4e34\u952e\u9501(10, 15]\uff0c\u6b64\u65f6\u627e\u5230\u7684\u7d22\u5f1515\u4e0d\u6ee1\u8db3id &lt; 15\u7684\u6761\u4ef6\uff0c\u904d\u5386\u505c\u6b62\uff0c\u4e14\u6839\u636e\u89c4\u52194\u4e34\u952e\u9501\u9000\u5316\u4e3a\u95f4\u9699\u9501(10, 15)\uff0c\u6574\u4e2a\u4e0a\u9501\u7684\u533a\u95f4\u5b9e\u9645\u4e0a\u662f\u7531\u8bb0\u5f55\u9501\u548c\u95f4\u9699\u9501\u7ec4\u6210\u7684[10, 15)\u3002\u6545\u5bf9id=8\u3001id=15\u7684\u64cd\u4f5c\u6210\u529f\uff0c\u5bf9id=10\u3001id=12\u7684\u64cd\u4f5c\u963b\u585e\u3002<\/p>\n<h2>3.2.2.4 \u6b7b\u9501\u7684\u89e3\u51b3\u65b9\u6cd5<\/h2>\n<ul>\n<li>\u7b80\u5355\u65b9\u6848\uff1a\u8d85\u65f6\uff0c\u8d85\u65f6\u540e\u56de\u6eda\u4e8b\u52a1<\/li>\n<li>\u590d\u6742\u70b9\u7684\u65b9\u6848\uff1a\u4e3b\u52a8\u6b7b\u9501\u68c0\u6d4b\uff0c\u57fa\u4e8e\u7b49\u5f85\u56fe(wait-for graph)\uff0c\u56fe\u4e2d\u4ee5\u4e8b\u52a1\u4e3a\u9876\u70b9\uff0c\u9501\u4e3a\u8fb9\uff0c\u5224\u65ad\u4f9d\u8d56\u56fe\u4e2d\u662f\u5426\u5b58\u5728\u73af\u5373\u53ef\u3002InnoDB\u4e5f\u4f7f\u7528\u4e86\u8fd9\u79cd\u65b9\u6cd5\u3002\u68c0\u6d4b\u5230\u6b7b\u9501\u4ee5\u540e\uff0c\u9009\u62e9\u63d2\u5165\/\u66f4\u65b0\/\u5220\u9664\u7684\u884c\u6570\u6700\u5c11\u7684\u4e8b\u52a1\u56de\u6eda\uff0c\u6b64\u6570\u636e\u4f1a\u5728\u4e8b\u52a1\u7684\u6570\u636e\u7ed3\u6784\u4e2d\u8bb0\u5f55\u3002<\/li>\n<\/ul>\n<h2>3.3 \u610f\u5411\u9501<\/h2>\n<p>\u5206\u4e3a\u610f\u5411\u5171\u4eab\u9501\uff08Intention Shared Lock\uff0cIS\uff0c\u8868\u793a\u4e8b\u52a1\u60f3\u8981\u5bf9\u8868\u4e2d\u67d0\u51e0\u884c\u8bbe\u7f6e\u5171\u4eab\u9501\uff09\u548c\u610f\u5411\u6392\u4ed6\u9501\uff08Intention eXclusive Lock\uff0cIX\uff0c\u8868\u793a\u4e8b\u52a1\u60f3\u8981\u5bf9\u8868\u4e2d\u67d0\u51e0\u884c\u8bbe\u7f6e\u6392\u4ed6\u9501\uff09\u3002\u610f\u5411\u9501\u90fd\u662f\u8868\u9501\uff0c\u4e8b\u52a1\u5728\u8bb0\u5f55\u4e0a\u52a0\u5165\u884c\u7ea7\u7684\u8bfb\u9501\/\u5199\u9501\u4e4b\u524d\uff0c\u7531InnoDB\u81ea\u52a8\u6dfb\u52a0\u5bf9\u5e94\u7684\u8868\u7ea7\u610f\u5411\u9501\u3002<br \/>\n\u5f15\u5165\u610f\u5411\u9501\u673a\u5236\u7684\u76ee\u7684\u4e3b\u8981\u662f\u8f85\u52a9\u5224\u65ad\u8868\u9501\u3001\u884c\u9501\u7684\u51b2\u7a81\u3002\u82e5\u4e0d\u5b58\u5728\u610f\u5411\u9501\u673a\u5236\uff0c\u4e8b\u52a1\u5728\u5c1d\u8bd5\u52a0\u8868\u9501\u65f6\uff0c\u9700\u8981\u9010\u884c\u626b\u63cf\u8be5\u8868\u5185\u662f\u5426\u5b58\u5728\u884c\u9501\uff0c\u8fd9\u4f1a\u5bfc\u81f4\u6027\u80fd\u4e25\u91cd\u4e0b\u964d\u3002\u5f15\u5165\u610f\u5411\u9501\u673a\u5236\u540e\uff0c\u4e8b\u52a1\u5728\u52a0\u884c\u9501\u4e4b\u524d\u5148\u7533\u8bf7\u610f\u5411\u9501\uff0c\u82e5\u6210\u529f\u83b7\u53d6\u5219\u7ee7\u7eed\uff0c\u5426\u5219\u5219\u7b49\u5f85\u76f4\u5230\u83b7\u53d6\u5230\u610f\u5411\u9501\u4e3a\u6b62\uff1b\u540e\u6765\u7684\u4e8b\u52a1\u5c1d\u8bd5\u52a0\u8868\u9501\u65f6\uff0c\u53ea\u8981\u53d1\u73b0\u8be5\u8868\u4e0a\u5b58\u5728\u610f\u5411\u9501\uff0c\u5219\u53ef\u4ee5\u7acb\u523b\u77e5\u9053\u6b64\u65f6\u8868\u5185\u5b58\u5728\u884c\u9501\uff0c\u4fbf\u53ef\u4ee5\u76f4\u63a5\u8fdb\u5165\u7b49\u5f85\u72b6\u6001\u3002<br \/>\n\u867d\u7136\u610f\u5411\u9501\u662f\u8868\u9501\uff0c\u4f46\u662f\u7531\u4e8e\u5176\u53ea\u662f\u4e00\u79cd\u201c\u610f\u5411\u201d\uff0c\u4ee3\u8868\u8868\u4e2d\u7684\u67d0\u884c\u6b63\u5728\u88ab\u8bfb\u5199\u800c\u975e\u6574\u4e2a\u8868\uff0c\u6240\u4ee5\u591a\u4e2a\u610f\u5411\u9501\u4e4b\u95f4\u5e76\u4e0d\u4f1a\u76f8\u4e92\u51b2\u7a81\uff0c\u6b64\u65f6\u8fd8\u662f\u9700\u8981\u8fdb\u4e00\u6b65\u68c0\u67e5\u884c\u9501\u662f\u5426\u51b2\u7a81\u3002\u540c\u7406\uff0c\u610f\u5411\u9501\u4e5f\u4e0d\u4e0e\u4efb\u4f55\u884c\u7ea7\u9501\u51b2\u7a81\u3002<\/p>\n<h2>3.4 \u4e50\u89c2\u9501\u4e0e\u60b2\u89c2\u9501\u601d\u60f3<\/h2>\n<p>\u8fd9<strong>\u5e76\u4e0d\u662f\u4e24\u79cd\u5177\u4f53\u5b58\u5728\u7684\u9501<\/strong>\uff0c\u800c\u662f\u4e24\u79cd\u4e0d\u540c\u7684\u5173\u4e8e\u9501\u7684\u8bbe\u8ba1\u601d\u60f3\u3002<\/p>\n<h3>\u4e50\u89c2\u9501<\/h3>\n<p>\u4e50\u89c2\u9501\u8ba4\u4e3a\u5bf9\u540c\u4e00\u6570\u636e\u7684\u5e76\u53d1\u64cd\u4f5c\u5e76\u4e0d\u603b\u662f\u53d1\u751f\uff0c\u5c5e\u4e8e\u5c0f\u6982\u7387\u4e8b\u4ef6\uff0c\u6545\u4e0d\u7528\u6bcf\u6b21\u90fd\u5bf9\u6570\u636e\u4e0a\u9501\uff0c\u4e5f\u5c31\u662f\u4e0d\u91c7\u7528\u6570\u636e\u5e93\u81ea\u8eab\u7684\u9501\u673a\u5236\uff0c\u800c\u662f\u901a\u8fc7\u7a0b\u5e8f\u6765\u5b9e\u73b0\u3002\u5e38\u89c1\u7684\u4e50\u89c2\u9501\u5b9e\u73b0\u65b9\u6cd5\u662f\u91c7\u7528CAS(Compare and Swap)\u673a\u5236\uff0c\u5728\u8868\u4e2d\u52a0\u4e00\u5217version\u4f5c\u4e3a\u7248\u672c\u53f7\uff0c\u7ea6\u5b9a\u6570\u636e\u88ab\u4fee\u6539\u65f6version\u503c\u9700\u8981\u9012\u589e\u3002\u5f53\u4e00\u4e2a\u5e94\u7528\u8fdb\u7a0b\u7b2c\u4e00\u6b21\u8fdb\u884c\u6570\u636e\u7684\u8bfb\u53d6\u65f6\uff0c\u540c\u65f6\u4e5f\u8bfb\u53d6\u6b64\u65f6version\u7684\u503c\uff0c\u793a\u610fsql\u5982\u4e0b\uff1a<\/p>\n<pre><code class=\"language-sql\">SELECT data, version FROM t WHERE id=#{id};<\/code><\/pre>\n<p>\u800c\u540e\u9700\u8981\u66f4\u65b0\u6570\u636e\u65f6\uff0c\u5219\u5c06sql\u6309\u7167\u8fd9\u79cd\u65b9\u5f0f\u5199\uff1a<\/p>\n<pre><code class=\"language-sql\">UPDATE t SET version=version+1 data=#{newdata} WHERE id=#{id} AND version=#{version};<\/code><\/pre>\n<p>\u82e5\u5728\u8fd9\u6bb5\u65f6\u95f4\u5185\u6709\u522b\u7684\u4e8b\u52a1\u66f4\u6539\u4e86\u8fd9\u6761\u6570\u636e\uff0csql\u7684\u6267\u884c\u5c31\u4f1a\u5931\u8d25\uff0c\u53ef\u4ee5\u901a\u8fc7SQL\u6267\u884c\u8fd4\u56de\u7684\u7ed3\u679c\u5f97\u77e5\u662f\u5426\u6267\u884c\u6210\u529f\u3002\u82e5\u6267\u884c\u5931\u8d25\uff0c\u5219\u91cd\u590d\u5c1d\u8bd5\uff0c\u76f4\u5230\u6210\u529f\u3002<\/p>\n<h3>\u60b2\u89c2\u9501<\/h3>\n<p>\u60b2\u89c2\u9501\u8ba4\u4e3a\u5bf9\u6570\u636e\u7684\u5e76\u53d1\u4fee\u6539\u6709\u5f88\u5927\u53ef\u80fd\u4f1a\u53d1\u751f\uff0c\u6545\u5728\u6bcf\u6b21\u83b7\u53d6\u6570\u636e\uff08\u8bfb\u5199\u90fd\u7b97\uff09\u65f6\u90fd\u8981\u52a0\u9501\uff0c\u5176\u5177\u4f53\u5b9e\u73b0\u4fbf\u662f\u6570\u636e\u5e93\u7684\u9501\u673a\u5236\u3002<\/p>\n<h2>3.5 \u81ea\u589e\u9501\uff08Auto-Inc Locking\uff09<\/h2>\n<p>\u5728InnoDB\u4e2d\uff0c\u81ea\u589e\u957f\u503c\u5fc5\u987b\u662f\u7d22\u5f15\u4e14\u5fc5\u987b\u662f\u8868\u7684\u7b2c\u4e00\u5217\u3002\u5bf9\u81ea\u589e\u957f\u5217\u8fdb\u884c\u63d2\u5165\u65f6\uff0c\u9700\u8981\u7528\u5230\u81ea\u589e\u9501\u673a\u5236\u3002<br \/>\n\u5728\u8fdc\u53e4\u7248\u672c\uff085.1.22\u4ee5\u524d\uff09\u7684MySQL\u4e2d\uff0c\u81ea\u589e\u9501\u7edf\u4e00\u91c7\u7528\u4e00\u79cd\u7279\u6b8a\u7684\u8868\u9501\u65b9\u5f0f\u5b9e\u73b0\uff0c\u4e0d\u540c\u4e8e2\u9636\u6bb5\u9501\u601d\u60f3\uff0c\u6b64\u8868\u9501\u5728\u5b8c\u6210\u6267\u884c\u81ea\u589e\u64cd\u4f5c\u7684SQL\u8bed\u53e5\u540e\u7acb\u5373\u91ca\u653e\u3002<br \/>\n\u57285.1.22\u7248\u672c\u4ee5\u540e\uff0cInnoDB\u5f15\u5165\u4e86\u65b0\u7684\u57fa\u4e8e\u4e92\u65a5\u91cf(mutex)\u7684\u81ea\u589e\u9501\u673a\u5236\uff0c\u4f7f\u5f97\u81ea\u589e\u957f\u503c\u7684\u63d2\u5165\u6027\u80fd\u5927\u5e45\u5ea6\u63d0\u9ad8\u3002\u4e0d\u8fc7\u5728\u9ed8\u8ba4\u8bbe\u7f6e\u4e0b\uff0c\u4e5f\u4e0d\u662f\u6bcf\u4e2a\u63d2\u5165\u64cd\u4f5c\u90fd\u4f1a\u4f7f\u7528\u57fa\u4e8emutex\u7684\u81ea\u589e\uff0c\u4f8b\u5982\u5bf9\u4e8e\u65e0\u6cd5\u9884\u5148\u83b7\u5f97\u63d2\u5165\u884c\u6570\u7684\u8bed\u53e5\uff0c\u8fd8\u662f\u4f1a\u4f7f\u7528\u4f20\u7edf\u7684\u57fa\u4e8e\u8868\u9501\u7684\u81ea\u589e\u9501\u673a\u5236\u3002<br \/>\n\u6ce8\u610f\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0cMySQL\u7684\u81ea\u589e\u662f\u4ece1\u5f00\u59cb\u800c\u4e0d\u662f0.<\/p>\n<h2>3.6 \u63d2\u5165\u610f\u5411\u9501(Insert Intention Lock)\u4e0eInsert\u6b7b\u9501\u95ee\u9898<\/h2>\n<p>\u63d2\u5165\u610f\u5411\u9501\u662f\u4e00\u79cd\u7279\u6b8a\u7684<strong>\u95f4\u9699\u9501<\/strong>\uff0c\u4e0d\u5c5e\u4e8e\u610f\u5411\u9501\uff1b\u7531\u4e8e\u662f\u95f4\u9699\u9501\uff0c\u5c5e\u4e8e\u884c\u9501\u800c\u975e\u8868\u9501\u3002<br \/>\n\u5728\u6267\u884cInsert\u8bed\u53e5\u4e4b\u524d\uff0cInnoDB\u5bf9\u6240\u5c5e\u7684\u95f4\u9699\u7533\u8bf7\u63d2\u5165\u610f\u5411\u9501\uff0c\u63d2\u5165\u610f\u5411\u9501\u4e4b\u95f4\u662f\u4e0d\u4e92\u65a5\u7684\uff0c\u53ef\u4ee5\u5171\u5b58\uff1b\u5f97\u5230\u63d2\u5165\u610f\u5411\u9501\u4ee5\u540e\uff0c\u5bf9\u51c6\u5907\u63d2\u5165\u7684\u6570\u636e\u884c\u7533\u8bf7\u884c\u7ea7\u6392\u5b83\u9501\uff0c\u6b64\u65f6\u53ef\u80fd\u53d1\u751f\u552f\u4e00\u952e\u51b2\u7a81\uff0c\u5373\u591a\u4e2a\u5e76\u53d1\u4e8b\u52a1\u5c1d\u8bd5\u63d2\u5165\u952e\u503c\u76f8\u540c\u7684\u6570\u636e\u3002<br \/>\n\u5728Insert\u6b65\u9aa4\u53d1\u751f\u552f\u4e00\u952e\u51b2\u7a81\u65f6\uff0c\u6240\u6709\u9677\u5165\u51b2\u7a81\u7684\u4e8b\u52a1\u90fd\u9700\u8981\u6539\u53d8\u4e0a\u9501\u6d41\u7a0b\uff0c\u53d8\u6210\u5982\u4e0b\u4e24\u6b65\uff1a\u9996\u5148\u7533\u8bf7\u5171\u4eab\u9501\uff0c\u7136\u540e\u7533\u8bf7\u6392\u5b83\u9501\u3002\u82e5\u51b2\u7a81\u53d1\u751f\u5728\u4e3b\u952e\u4e0a\uff0c\u5219RC\u9694\u79bb\u7ea7\u522b\u4e0b\u4e0a\u8ff0\u5171\u4eab\/\u6392\u4ed6\u9501\u7684\u7c92\u5ea6\u4e3a\u8bb0\u5f55\u9501\uff0cRR\u9694\u79bb\u7ea7\u522b\u4e0b\u7c92\u5ea6\u4e3a\u4e34\u952e\u9501\uff1b\u82e5\u51b2\u7a81\u53d1\u751f\u5728\u552f\u4e00\u4e8c\u7ea7\u7d22\u5f15\u4e0a\uff0c\u5219\u65e0\u8bba\u9694\u79bb\u7ea7\u522b\uff0c\u7c92\u5ea6\u5747\u4e3a\u4e34\u952e\u9501\u3002<br \/>\n\u4fee\u6539\u4e86\u4e0a\u9501\u6d41\u7a0b\u4e3a\u5148\u540e\u7533\u8bf7\u5171\u4eab\u9501\u548c\u6392\u5b83\u9501\u540e\uff0c\u8fd9\u4e9b\u51b2\u7a81\u7684\u4e8b\u52a1\u4e00\u5b9a\u90fd\u80fd\u7533\u8bf7\u5230\u5171\u4eab\u9501\uff0c\u4e14\u5728\u7533\u8bf7\u6392\u4ed6\u9501\u65f6\u963b\u585e\uff0c\u4e92\u76f8\u7b49\u5f85\u5f7c\u6b64\u7684\u5171\u4eab\u9501\u91ca\u653e\uff0c\u6b64\u65f6\u53d1\u751f\u6b7b\u9501\u3002MySQL\u7684\u6b7b\u9501\u68c0\u6d4b\u673a\u5236\u6b64\u65f6\u4f1a\u4ecb\u5165\uff0c\u56de\u6eda\u5176\u4e2d\u4e00\u4e9b\u4e8b\u52a1\uff0c\u8ba9\u5176\u4e2d\u7684\u4e00\u4e2a\u4e8b\u52a1\u5f97\u4ee5\u5b8c\u6210\u3002<\/p>\n<p><a href=\"https:\/\/www.cnblogs.com\/JiangLe\/p\/12680228.html\">https:\/\/www.cnblogs.com\/JiangLe\/p\/12680228.html<\/a><br \/>\n<a href=\"https:\/\/juejin.cn\/post\/7052880067298328589\">https:\/\/juejin.cn\/post\/7052880067298328589<\/a><\/p>\n<h2>3.7 \u95f4\u9699\u9501\u4e92\u76f8\u517c\u5bb9\u5bfc\u81f4\u7684\u6b7b\u9501\u95ee\u9898<\/h2>\n<p><a href=\"https:\/\/xiaolincoding.com\/mysql\/lock\/deadlock.html#%E4%B8%BA%E4%BB%80%E4%B9%88%E4%BC%9A%E4%BA%A7%E7%94%9F%E6%AD%BB%E9%94%81\">https:\/\/xiaolincoding.com\/mysql\/lock\/deadlock.html#%E4%B8%BA%E4%BB%80%E4%B9%88%E4%BC%9A%E4%BA%A7%E7%94%9F%E6%AD%BB%E9%94%81<\/a><\/p>\n<h2>\u53c2\u8003<\/h2>\n<p><a href=\"https:\/\/juejin.cn\/post\/7018137095315128328\">https:\/\/juejin.cn\/post\/7018137095315128328<\/a><br \/>\n<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-locking.html#innodb-gap-locks\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-locking.html#innodb-gap-locks<\/a><br \/>\n<a href=\"https:\/\/www.jianshu.com\/p\/478bc84a7721\">https:\/\/www.jianshu.com\/p\/478bc84a7721<\/a><br \/>\n<a href=\"https:\/\/segmentfault.com\/a\/1190000023869573\">https:\/\/segmentfault.com\/a\/1190000023869573<\/a><br \/>\n<a href=\"https:\/\/developer.aliyun.com\/article\/873307\">https:\/\/developer.aliyun.com\/article\/873307<\/a><br \/>\n<a href=\"https:\/\/www.cnblogs.com\/kismetv\/p\/10787228.html\">https:\/\/www.cnblogs.com\/kismetv\/p\/10787228.html<\/a><br \/>\n<a href=\"https:\/\/bbs.huaweicloud.com\/forum\/thread-83643-1-1.html\">https:\/\/bbs.huaweicloud.com\/forum\/thread-83643-1-1.html<\/a><\/p>\n<h1>4. \u4e8b\u52a1\u4e4b\u95f4\u7684\u76f8\u4e92\u5f71\u54cd\uff1f<\/h1>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u5f71\u54cd<\/th>\n<th style=\"text-align: center;\">\u63cf\u8ff0<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u810f\u8bfb<\/td>\n<td style=\"text-align: center;\">\u4e00\u4e2a\u4e8b\u52a1\u8bfb\u53d6\u4e86\u53e6\u4e00\u4e2a\u4e8b\u52a1\u672a\u63d0\u4ea4\uff08\u6216\u5c06\u8981\u56de\u6eda\uff09\u7684\u6570\u636e<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u4e0d\u53ef\u91cd\u590d\u8bfb<\/td>\n<td style=\"text-align: center;\">\u540c\u4e00\u4e2a\u4e8b\u52a1\u4e2d\u591a\u6b21\u8bfb\u53d6\u540c\u4e00\u884c\u6570\u636e\u4e0d\u4e00\u81f4\uff0c\u5373\u5176\u4ed6\u4e8b\u52a1\u5728\u8fd9\u671f\u95f4update\/delete<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u5e7b\u8bfb<\/td>\n<td style=\"text-align: center;\">\u540c\u4e00\u4e2a\u67e5\u8be2\u5728\u4e0d\u540c\u65f6\u95f4\u751f\u6210\u4e0d\u540c\u7684\u884c\u96c6\u5408\uff0c\u5373\u5176\u4ed6\u4e8b\u52a1\u5728\u8fd9\u671f\u95f4insert<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u4e22\u5931\u66f4\u65b0\uff08\u5199\u95ee\u9898\uff0c\u4e0a\u9762\u4e09\u4e2a\u662f\u8bfb\uff09<\/td>\n<td style=\"text-align: center;\">\u4e24\u4e2a\u4e8b\u52a1\u4fee\u6539\u540c\u4e00\u6570\u636e\uff0c\u5148\u63d0\u4ea4\u7684\u4e8b\u52a1\u505a\u51fa\u7684\u4fee\u6539\u88ab\u8986\u76d6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h1>5. \u4e8b\u52a1\u7684\u9694\u79bb\u7ea7\u522b\u53ca\u5b9e\u73b0\u539f\u7406\uff1f<\/h1>\n<h2>5.1 \u8868\u683c\uff1a\u6807\u51c6SQL\u5404\u9694\u79bb\u7b49\u7ea7\u4e0b\u95ee\u9898\u662f\u5426\u4f1a\u53d1\u751f<\/h2>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u9694\u79bb\u7b49\u7ea7<\/th>\n<th style=\"text-align: center;\">\u810f\u8bfb<\/th>\n<th style=\"text-align: center;\">\u4e0d\u53ef\u91cd\u590d\u8bfb<\/th>\n<th style=\"text-align: center;\">\u5e7b\u8bfb<\/th>\n<th style=\"text-align: center;\">\u4e22\u5931\u66f4\u65b0<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u8bfb\u672a\u63d0\u4ea4 (RU)<\/td>\n<td style=\"text-align: center;\">\u221a<\/td>\n<td style=\"text-align: center;\">\u221a<\/td>\n<td style=\"text-align: center;\">\u221a<\/td>\n<td style=\"text-align: center;\">\u221a<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u8bfb\u5df2\u63d0\u4ea4 (RC)<\/td>\n<td style=\"text-align: center;\">\u00d7<\/td>\n<td style=\"text-align: center;\">\u221a<\/td>\n<td style=\"text-align: center;\">\u221a<\/td>\n<td style=\"text-align: center;\">\u221a<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u53ef\u91cd\u590d\u8bfb (RR)<\/td>\n<td style=\"text-align: center;\">\u00d7<\/td>\n<td style=\"text-align: center;\">\u00d7<\/td>\n<td style=\"text-align: center;\">\u221a<\/td>\n<td style=\"text-align: center;\">\u00d7<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u4e32\u884c\u5316 (S)<\/td>\n<td style=\"text-align: center;\">\u00d7<\/td>\n<td style=\"text-align: center;\">\u00d7<\/td>\n<td style=\"text-align: center;\">\u00d7<\/td>\n<td style=\"text-align: center;\">\u00d7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>5.2 \u6807\u51c6SQL\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\uff08\u4ec5\u4f9b\u53c2\u8003\uff0c\u91cd\u70b9\u4e86\u89e3\u540e\u9762\u7684InnoDB MVCC\u65b9\u6848\uff09<\/h2>\n<p>\u8bfb\u672a\u63d0\u4ea4\uff1a\u6700\u4f4e\u9694\u79bb\u7ea7\u522b\uff0c\u4e8b\u52a1\u53ef\u4ee5\u8bfb\u5230\u53e6\u4e00\u4e8b\u52a1\u672a\u63d0\u4ea4\u7684\u7ed3\u679c\u3002\u6240\u6709\u95ee\u9898\u90fd\u53d1\u751f\u3002\u5b9e\u73b0\u65b9\u5f0f\uff1a\u4e8b\u52a1\u5728\u66f4\u65b0\u6570\u636e\u65f6\u52a0<strong>\u884c\u7ea7\u5171\u4eab<\/strong>\u9501\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\u3002<br \/>\n\u8bfb\u5df2\u63d0\u4ea4\uff1a\u53ea\u6709\u4e8b\u52a1\u63d0\u4ea4\u540e\uff0c\u66f4\u65b0\u7ed3\u679c\u624d\u53ef\u4ee5\u88ab\u5176\u4ed6\u6570\u636e\u8bfb\u53d6\u3002\u89e3\u51b3\u810f\u8bfb\u3002\u5b9e\u73b0\u65b9\u5f0f\uff1a1. \u5bf9\u8bfb\u53d6\u7684\u6570\u636e\u52a0<strong>\u884c\u7ea7\u5171\u4eab<\/strong>\u9501\uff0c\u8bfb\u5b8c\u8be5\u884c<strong>\u7acb\u5373<\/strong>\u91ca\u653e\uff1b2. \u66f4\u65b0\u6570\u636e\u65f6\uff0c\u52a0<strong>\u884c\u7ea7\u6392\u4ed6\u9501<\/strong>\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\u3002<br \/>\n\u53ef\u91cd\u590d\u8bfb\uff08InnoDB\u7684\u9ed8\u8ba4\u9694\u79bb\u7b49\u7ea7\uff09\uff1a\u4fdd\u8bc1\u4e8b\u52a1\u591a\u6b21\u8bfb\u53d6\u540c\u4e00\u6570\u636e\u65f6\u7ed3\u679c\u4e00\u81f4\uff0c\u9664\u975e\u88ab\u81ea\u5df1\u4fee\u6539\u3002\u89e3\u51b3\u810f\u8bfb\u3001\u4e0d\u53ef\u91cd\u590d\u8bfb\u3002\u5b9e\u73b0\u65b9\u5f0f\uff1a1. \u8bfb\u6570\u636e\u65f6\uff0c\u52a0<strong>\u884c\u7ea7\u5171\u4eab\u9501<\/strong>\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\uff1b2. \u66f4\u65b0\u6570\u636e\u65f6\uff0c\u52a0<strong>\u884c\u7ea7\u6392\u4ed6\u9501<\/strong>\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\u3002<br \/>\n\u53ef\u4e32\u884c\u5316\uff1a\u4e8b\u52a1\u4e32\u884c\u6267\u884c\uff0c\u6700\u9ad8\u9694\u79bb\u7ea7\u522b\uff0c\u89e3\u51b3\u6240\u6709\u95ee\u9898\uff0c\u4f46\u662f\u727a\u7272\u5e76\u53d1\u6027\u3002\u5b9e\u73b0\u65b9\u5f0f\uff1a1. \u8bfb\u6570\u636e\u65f6\uff0c\u52a0<strong>\u8868\u7ea7\u5171\u4eab\u9501<\/strong>\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\uff1b2. \u66f4\u65b0\u6570\u636e\u65f6\uff0c\u52a0<strong>\u8868\u7ea7\u6392\u4ed6\u9501<\/strong>\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\u3002<\/p>\n<h2>5.3 \u4e24\u9636\u6bb5\u9501(2 Phase Locking, 2PL)\u89c4\u5219<\/h2>\n<p>\u5728\u4e00\u4e2a\u4e8b\u52a1\u91cc\u9762\uff0c\u5206\u4e3a\u52a0\u9501(lock)\u9636\u6bb5\u548c\u89e3\u9501(unlock)\u9636\u6bb5\uff0c\u6240\u6709\u7684\u52a0\u9501\u64cd\u4f5c\u90fd\u53d1\u751f\u5728\u89e3\u9501\u64cd\u4f5c\u4e4b\u524d,\u5982\u4e0b\u56fe\u6240\u793a:<br \/>\n<div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/static.oschina.net\/uploads\/img\/201707\/17105307_MZJc.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  decoding=\"async\" data-original=\"https:\/\/static.oschina.net\/uploads\/img\/201707\/17105307_MZJc.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"2PL\" \/><\/div><br \/>\n2PL\u662f\u4e3a\u4e86\u4fdd\u8bc1\u4e8b\u52a1\u7684\u9694\u79bb\u6027\u800c\u88ab\u8bbe\u8ba1\u51fa\u6765\uff0c\u53ef\u4ee5\u4ece\u6570\u5b66\u4e0a\u8bc1\u660e\uff1a\u5982\u679c\u4e8b\u52a1\u662f\u826f\u6784(well-structured)\u7684\u4e14\u662f\u4e24\u9636\u6bb5\u7684\uff0c\u90a3\u4e48\u4efb\u4f55\u4e00\u4e2a\u5408\u6cd5\u7684\u8c03\u5ea6\u90fd\u662f\u9694\u79bb\u7684\u3002\u53ef\u4ee5\u770b\u5230\uff0c\u4e0b\u6587\u6240\u9610\u8ff0\u7684InnoDB MVCC\u673a\u5236\u4e0b\u7684\u4e8b\u52a1\u9694\u79bb\u5b9e\u73b0\u4e2d\uff0c\u6240\u6709\u7684\u89e3\u9501\u90fd\u662f\u53d1\u751f\u5728\u4e8b\u52a1\u7ed3\u675f\u65f6\uff0c\u665a\u4e8e\u4efb\u610f\u4e00\u4e2a\u52a0\u9501\u64cd\u4f5c\u3002<\/p>\n<h2>5.4 \u5f53\u524d\u8bfb\u548c\u5feb\u7167\u8bfb<\/h2>\n<p>\u5f53\u524d\u8bfb\u6307\u8bfb\u53d6\u7684\u8bb0\u5f55\u662f\u6700\u65b0\u7248\u672c\uff0c\u4f1a\u5bf9\u6b63\u5728\u8bfb\u53d6\u7684\u6570\u636e\u52a0\u9501\uff0c\u5c5e\u4e8e\u60b2\u89c2\u9501\uff1b\u5c5e\u4e8e\u5f53\u524d\u8bfb\u7684\u64cd\u4f5c\u6709\uff1aselect lock in share mode (\u5171\u4eab\u9501)\u3001select for update (\u6392\u4ed6\u9501)\u3001update (\u6392\u4ed6\u9501)\u3001insert (\u6392\u4ed6\u9501)\u3001delete (\u6392\u4ed6\u9501)\u4ee5\u53ca\u4e32\u884c\u5316\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\u4e0b\u7684\u6240\u6709\u64cd\u4f5c\u3002\u5176\u4e2d\uff0c\u4e24\u79cd\u52a0\u9501\u7684select\u8bed\u53e5\u88ab\u79f0\u4e3a\u4e00\u81f4\u6027\u9501\u5b9a\u8bfb(consistent locking read)\u3002<br \/>\n\u5feb\u7167\u8bfb<strong>\u53ea\u6709\u5728\u8bfb\u5df2\u63d0\u4ea4\u548c\u53ef\u91cd\u590d\u8bfb\u4e0b\u53ef\u884c<\/strong>\uff0c\u53c8\u79f0\u4e00\u81f4\u6027\u975e\u9501\u5b9a\u8bfb(consistent nonlocking read)\uff0c\u57fa\u4e8eMVCC\u5b9e\u73b0\uff0c\u53ef\u80fd\u8bfb\u5230\u5386\u53f2\u7248\u672c\u7684\u6570\u636e\u3002\u5c5e\u4e8e\u5feb\u7167\u8bfb\u7684\u64cd\u4f5c\u6709\uff1a\u4e0d\u52a0\u9501\u7684select\u64cd\u4f5c\u3002<\/p>\n<h2>5.5 InnoDB\u7684MVCC\u673a\u5236\u539f\u7406<\/h2>\n<h3>5.5.1 \u9690\u85cf\u5217\u4e0eundo log<\/h3>\n<p>MVCC(Multi Version Concurrency Control)\uff0c\u5373\u591a\u7248\u672c\u5e76\u53d1\u63a7\u5236\uff0c\u662f\u201c\u7ef4\u6301\u4e00\u4e2a\u6570\u636e\u7684\u591a\u4e2a\u7248\u672c\uff0c\u4f7f\u8bfb\u5199\u64cd\u4f5c\u6ca1\u6709\u51b2\u7a81\u201d\u7684\u4e00\u4e2a\u62bd\u8c61\u6982\u5ff5\uff0c\u5176\u5177\u4f53\u5b9e\u73b0\u5c31\u662f\u5feb\u7167\u8bfb\u3002\u518d\u6b21\u5f3a\u8c03<strong>\u53ea\u5728\u8bfb\u5df2\u63d0\u4ea4\u548c\u53ef\u91cd\u590d\u8bfb\u4e24\u4e2a\u9694\u79bb\u7b49\u7ea7\u4e0b\u5de5\u4f5c\u3002<\/strong><br \/>\nInnoDB\u4e2d\u4e8b\u52a1\u6709\u4e00\u4e2a\u552f\u4e00ID\uff0c\u5728\u4e8b\u52a1\u5f00\u59cb\u65f6\u7533\u8bf7\uff0c\u5e76\u4e14\u6309\u7167\u65f6\u95f4\u4e25\u683c\u9012\u589e\u3002<br \/>\n\u6bcf\u884c\u6570\u636e\u4f1a\u6709\u4e24\u4e2a\u9690\u85cf\u5217\u7528\u4e8e\u5b9e\u73b0MVCC\u673a\u5236\uff0c\u4e00\u4e2a\u662f\u6700\u8fd1\u66f4\u65b0\u672c\u884c\u6570\u636e\u7684\u4e8b\u52a1id<code>DB_TRX_ID<\/code>\uff0c\u4e00\u4e2a\u662f\u6307\u5411\u4e0a\u4e00\u4e2a\u65e7\u7248\u672c\u7684\u56de\u6eda\u6307\u9488<code>DB_ROLL_PTR<\/code>\uff0c\u5f53\u884c\u6570\u636e\u66f4\u65b0\u65f6\uff0c\u4f1a\u5c06\u65e7\u7684\u6570\u636e\u7248\u672c\u5b58\u5165\u56de\u6eda\u65e5\u5fd7undo log(\u5173\u4e8eundo log\u66f4\u8be6\u7ec6\u7684\u4ecb\u7ecd\uff0c\u89c117\u8282)\u4e2d\uff0c\u540c\u65f6\u8bb0\u5f55\u4e0b\u66f4\u65b0\u4e8b\u52a1\u7684id\u548c\u65e7\u7248\u672c\u7684\u56de\u6eda\u6307\u9488\u3002<br \/>\n\u56de\u6eda\u65e5\u5fd7\u5206\u4e3ainsert undo log\uff08\u8bb0\u5f55insert\uff0c\u53ea\u5728\u4e8b\u52a1\u56de\u6eda\u65f6\u9700\u8981\u7528\u5230\uff0c\u4e8b\u52a1\u63d0\u4ea4\u540e\u53ef\u4e22\u5f03\uff09\u548cupdate undo log\uff08\u8bb0\u5f55update\u548cdelete\uff0c\u5728\u56de\u6eda\u548c\u5feb\u7167\u8bfb\u65f6\u90fd\u9700\u8981\u7528\u5230\uff09<\/p>\n<h3>5.5.2 \u8bfb\u89c6\u56fe\u4e0e\u5feb\u7167\u8bfb\u7684\u6267\u884c\u8fc7\u7a0b<\/h3>\n<p>\u8bfb\u89c6\u56fe(Read View)\u662f\u5728\u4e8b\u52a1\u6267\u884c\u5feb\u7167\u8bfb\u7684\u77ac\u95f4\uff0c\u4ea7\u751f\u7684\u5f53\u524d\u6570\u636e\u5e93\u7cfb\u7edf\u7684\u5feb\u7167\u3002\u8bfb\u89c6\u56fe\u7528\u5217\u8868\u5f62\u5f0f<code>m_ids<\/code>\u8bb0\u5f55\u6b64\u65f6\u7cfb\u7edf\u4e2d\u6240\u6709\u6d3b\u8dc3\uff08\u5373\u672a\u63d0\u4ea4\uff09\u4e8b\u52a1\u7684ID\uff0c\u6b64\u5916\u8fd8\u8bb0\u5f55\u4e86\u6700\u5c0f\u7684\u6d3b\u8dc3ID<code>up_limit_id<\/code>\u548c\u6700\u5927\u6d3b\u8dc3ID<code>low_limit_id<\/code>\u65b9\u4fbf\u8fdb\u884c\u5feb\u901f\u5224\u65ad\u3002\u6709\u4e86\u8fd9\u4e9b\u8bb0\u5f55\u7684id\uff0c\u7ed3\u5408\u884c\u6570\u636e\u9690\u85cf\u5217\u91cc\u8bb0\u5f55\u7684\u6700\u8fd1\u4e00\u6b21\u66f4\u65b0\u7684\u4e8b\u52a1id<code>DB_TRX_ID<\/code>\uff0c\u5c31\u53ef\u4ee5\u8fdb\u884c\u53ef\u89c1\u6027\u5224\u65ad\uff0c\u51b3\u5b9a\u5f53\u524d\u4e8b\u52a1\u80fd\u770b\u5230\u54ea\u4e2a\u7248\u672c\u7684\u6570\u636e\u3002\u5224\u65ad\u7684\u89c4\u5219\u5982\u4e0b\uff1a<\/p>\n<ul>\n<li>\u82e5<code>DB_TRX_ID<\/code>\u5c0f\u4e8e<code>up_limit_id<\/code>\uff0c\u8bf4\u660e\u8be5\u6570\u636e\u7684\u6700\u8fd1\u4e00\u6b21\u66f4\u65b0\u53d1\u751f\u5728\u8bfb\u89c6\u56fe\u521b\u5efa\u4e4b\u524d\uff0c\u6545\u6570\u636e\u7684\u6700\u65b0\u7248\u672c\u53ef\u4ee5\u88ab\u770b\u89c1<\/li>\n<li>\u82e5<code>DB_TRX_ID<\/code>\u5927\u4e8e<code>low_limit_id<\/code>\uff0c\u8bf4\u660e\u8be5\u6570\u636e\u7684\u6700\u8fd1\u4e00\u6b21\u66f4\u65b0\u53d1\u751f\u5728\u8bfb\u89c6\u56fe\u521b\u5efa\u4e4b\u540e\uff0c\u6545\u6570\u636e\u7684\u6700\u65b0\u7248\u672c\u80af\u5b9a\u4e0d\u53ef\u4ee5\u88ab\u770b\u89c1\uff0c\u9700\u8981\u8bfb\u53d6\u5386\u53f2\u7248\u672c<\/li>\n<li>\u82e5<code>DB_TRX_ID<\/code>\u5904\u4e8e<code>up_limit_id<\/code>\u548c<code>low_limit_id<\/code>\u4e4b\u95f4\uff0c\u5219\u53ea\u80fd\u53bb\u67e5\u6d3b\u8dc3\u4e8b\u52a1\u5217\u8868\u3002\u82e5\u5b58\u5728\u4e8e\u5217\u8868\u4e2d\uff0c\u5219\u8bf4\u660e\u8be5\u6570\u636e\u7684\u6700\u8fd1\u4e00\u6b21\u66f4\u65b0\u53d1\u751f\u5728\u8bfb\u89c6\u56fe\u521b\u5efa\u4e4b\u540e\uff0c\u6570\u636e\u7684\u6700\u65b0\u7248\u672c\u4e0d\u53ef\u4ee5\u88ab\u770b\u89c1\uff0c\u9700\u8981\u8bfb\u53d6\u5386\u53f2\u7248\u672c\uff1b\u82e5\u4e0d\u5728\u5217\u8868\u4e2d\uff0c\u8bf4\u660e\u6700\u8fd1\u4e00\u6b21\u66f4\u65b0\u53d1\u751f\u5728\u8bfb\u89c6\u56fe\u521b\u5efa\u4e4b\u524d\uff0c\u6700\u65b0\u7248\u672c\u53ef\u4ee5\u88ab\u770b\u5230\u3002<\/li>\n<\/ul>\n<p>\u5f53\u4e00\u4e2a\u4e8b\u52a1\u8fd0\u884c\u5230\u4e00\u884c\u67e5\u8be2\u8bed\u53e5\u65f6\uff0c\u82e5\u662f\u6267\u884c\u5feb\u7167\u8bfb\uff0c\u5219\u521b\u5efa\u8bfb\u89c6\u56fe\uff08\u6ce8\u610f\uff0c\u6839\u636e\u4e0d\u540c\u7684\u9694\u79bb\u7b49\u7ea7\uff0c\u8fd9\u91cc\u53ef\u80fd\u4e0d\u521b\u5efa\u65b0\u7684\u8bfb\u89c6\u56fe\uff0c\u53c2\u89c15.6\uff09\uff0c\u968f\u540e\u67e5\u8be2\u8be5\u884c\u6570\u636e\uff0c\u83b7\u5f97\u7248\u672c\u5b57\u6bb5<code>DB_TRX_ID<\/code>\u3002\u6839\u636e\u4e0a\u6587\u7684\u89c4\u5219\uff0c\u5229\u7528\u8bfb\u89c6\u56fe\u4e0e\u7248\u672c\u5b57\u6bb5\u8fdb\u884c\u53ef\u89c1\u6027\u5224\u65ad\uff0c\u82e5\u6700\u65b0\u7248\u672c\u6570\u636e\u53ef\u89c1\uff0c\u5219\u76f4\u63a5\u8bfb\u53d6\u5e76\u8fd4\u56de\uff1b\u82e5\u4e0d\u53ef\u89c1\uff0c\u5219\u6839\u636e\u56de\u6eda\u6307\u9488\u5f00\u59cb\u67e5\u8be2undo log\u4e2d\u7684\u65e5\u5fd7\u94fe\uff0c\u76f4\u5230\u627e\u5230\u53ef\u89c1\u7684\u7248\u672c\uff0c\u518d\u8bfb\u53d6\u5e76\u8fd4\u56de\u3002<\/p>\n<h2>5.6 MVCC\u673a\u5236\u4e0bInnoDB\u7684\u4e8b\u52a1\u9694\u79bb\u5b9e\u73b0\u65b9\u5f0f<\/h2>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b<\/th>\n<th style=\"text-align: center;\">\u5b9e\u73b0\u65b9\u5f0f<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u672a\u63d0\u4ea4\u8bfb(RU)<\/td>\n<td style=\"text-align: center;\">\u4e8b\u52a1\u5bf9\u5f53\u524d\u88ab\u8bfb\u53d6\u7684\u6570\u636e\u4e0d\u52a0\u9501\uff0c\u4e14\u662f<strong>\u5f53\u524d\u8bfb<\/strong>\uff1b\u4e8b\u52a1\u5728\u66f4\u65b0\u67d0\u6570\u636e\u7684\u77ac\u95f4\uff0c\u5fc5\u987b\u5148\u5bf9\u5176\u52a0<strong>\u884c\u7ea7\u5171\u4eab\u9501<\/strong>\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\u3002<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u8bfb\u5df2\u63d0\u4ea4(RC)<\/td>\n<td style=\"text-align: center;\">\u4e8b\u52a1\u5bf9\u5f53\u524d\u88ab\u8bfb\u53d6\u7684\u6570\u636e\u4e0d\u52a0\u9501\uff0c\u4e14\u662f<strong>\u5feb\u7167\u8bfb<\/strong>\uff1b\u4e8b\u52a1\u5728\u66f4\u65b0\u67d0\u6570\u636e\u548c\u6267\u884c\u5f53\u524d\u8bfb\uff08\u4f8b\u5982select for update\uff09\u65f6\uff0c\u5fc5\u987b\u5148\u5bf9\u5176\u52a0<strong>\u884c\u7ea7\u6392\u4ed6\u9501<\/strong>\uff08Record\uff09\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\u3002<strong>\u6ce8\u610f<\/strong>\uff1a<em>RC\u9694\u79bb\u7b49\u7ea7\u4e0b\uff0c\u6bcf\u4e00\u6b21\u6267\u884c\u67e5\u8be2\u8bed\u53e5\u65f6\u90fd\u4f1a\u5efa\u7acb\u65b0\u7684\u8bfb\u89c6\u56fe\u5373\u6700\u65b0\u7684\u5feb\u7167\uff0c\u6545\u67e5\u8be2\u5f97\u5230\u7684\u662f\u5df2\u88ab\u63d0\u4ea4\u8fc7\u7684\u6700\u65b0\u7248\u672c\uff0c\u6709\u4e0d\u53ef\u91cd\u590d\u8bfb\u95ee\u9898<\/em><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u53ef\u91cd\u590d\u8bfb(RR)<\/td>\n<td style=\"text-align: center;\">\u4e8b\u52a1\u5bf9\u5f53\u524d\u88ab\u8bfb\u53d6\u7684\u6570\u636e\u4e0d\u52a0\u9501\uff0c\u4e14\u662f<strong>\u5feb\u7167\u8bfb<\/strong>\uff1b\u4e8b\u52a1\u5728\u66f4\u65b0\u67d0\u6570\u636e\u548c\u6267\u884c\u5f53\u524d\u8bfb\uff08\u4f8b\u5982select for update\uff09\u65f6\uff0c\u5fc5\u987b\u5148\u5bf9\u5176\u52a0<strong>\u884c\u7ea7\u6392\u4ed6\u9501<\/strong>\uff08Record\uff0cGAP\uff0cNext-Key\uff09\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\u3002<strong>\u6ce8\u610f<\/strong>\uff1a<em>RR\u9694\u79bb\u7b49\u7ea7\u4e0b\uff0c\u7b2c\u4e00\u6b21\u6267\u884c\u5feb\u7167\u8bfb\u65f6\u521b\u5efa\u4e00\u6b21\u8bfb\u89c6\u56fe\u5373\u5feb\u7167\uff0c\u6b64\u540e\u540c\u4e00\u4e8b\u52a1\u4e0b\u7684\u6240\u6709\u67e5\u8be2\u90fd\u901a\u8fc7\u8be5\u5feb\u7167\u8fdb\u884c\uff0c\u6545\u5728InnoDB\u5728\u8fd9\u4e2a\u7ea7\u522b\u89e3\u51b3\u4e86\u4e0d\u53ef\u91cd\u590d\u8bfb\u7684\u95ee\u9898\uff1b\u800c\u901a\u8fc7\u95f4\u9699\u9501\uff0cInnoDB\u5728\u8fd9\u4e2a\u7ea7\u522b\u89e3\u51b3\u4e86\u5e7b\u8bfb\u7684\u95ee\u9898\uff1b<\/em><\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u4e32\u884c\u5316(S)<\/td>\n<td style=\"text-align: center;\">\u4e8b\u52a1\u5728\u8bfb\u53d6\u6570\u636e\u65f6\uff0c\u5fc5\u987b\u5148\u5bf9\u5176\u52a0<strong>\u8868\u7ea7\u5171\u4eab\u9501<\/strong> \uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\uff0c\u90fd\u662f<strong>\u5f53\u524d\u8bfb<\/strong>\uff1b\u4e8b\u52a1\u5728\u66f4\u65b0\u6570\u636e\u65f6\uff0c\u5fc5\u987b\u5148\u5bf9\u5176\u52a0<strong>\u8868\u7ea7\u6392\u4ed6\u9501<\/strong>\uff0c\u4e8b\u52a1<strong>\u7ed3\u675f\u65f6<\/strong>\u91ca\u653e\u3002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u7b80\u800c\u8a00\u4e4b\uff0c\u5728MVCC\u80fd\u5de5\u4f5c\u7684RC\u548cRR\u9694\u79bb\u7b49\u7ea7\u4e0b\uff0c\u6240\u6709\u7684\u8bfb\u90fd\u662f\u5feb\u7167\u8bfb\u6545\u4e0d\u52a0\u9501\uff0c\u6240\u6709\u7684\u5199\u90fd\u52a0\u884c\u7ea7\u6392\u5b83\u9501\uff1b\u4e24\u4e2a\u9694\u79bb\u7b49\u7ea7\u7684\u533a\u522b\u5728\u4e8e\uff1a1. \u5728RC\u9694\u79bb\u7b49\u7ea7\u4e0b\uff0c\u6bcf\u6b21\u8bfb\u90fd\u5355\u72ec\u521b\u5efa\u5feb\u7167\uff0c\u800c\u5728RR\u9694\u79bb\u7b49\u7ea7\u4e0b\uff0c\u4e8b\u52a1\u4e2d\u6240\u6709\u7684\u67e5\u8be2\u90fd\u5171\u7528\u7b2c\u4e00\u6761\u67e5\u8be2\u8bed\u53e5\u7684\u5feb\u7167\uff1b2.\u5728RC\u9694\u79bb\u7b49\u7ea7\u4e0b\uff0c\u66f4\u65b0\u6570\u636e\u65f6\u53ea\u52a0Record\u8bb0\u5f55\u9501\u7528\u4e8e\u89e3\u51b3\u810f\u8bfb\u95ee\u9898\uff0c\u800c\u5728RR\u9694\u79bb\u7b49\u7ea7\u4e0b\u8fd8\u4f1a\u52a0\u95f4\u9699\u9501\/\u4e34\u952e\u9501\uff0c\u7528\u6765\u89e3\u51b3\u5e7b\u8bfb\u95ee\u9898\u3002\u800c\u4e0d\u53ef\u91cd\u590d\u8bfb\u95ee\u9898\u5219\u662f\u7531\u5feb\u7167\u673a\u5236\u89e3\u51b3\u3002<\/p>\n<p>\u5f3a\u8c03\u4e00\u4e0bRC\u7684\u5feb\u7167\u8bfb\u95ee\u9898\uff1aRC\u6bcf\u6b21\u8bfb\u4e4b\u524d\u90fd\u521b\u5efa\u65b0\u7684\u5feb\u7167\uff0c\u6240\u4ee5\u53ea\u8981\u4e24\u6b21\u67e5\u8be2\u4e4b\u95f4\u6709\u5176\u4ed6\u4e8b\u52a1\u4fee\u6539\u4e86\u6570\u636e\u7684\u503c\u5e76\u4e14\u6210\u529f\u63d0\u4ea4\uff0c\u5219\u8fd9\u4e24\u6b21\u67e5\u8be2\u5c31\u4f1a\u8fd4\u56de\u4e0d\u540c\u7684\u503c\u3002RC\u4e0b\u7684\u67e5\u8be2\u8bed\u53e5\u5728\u6570\u636e\u884c\u6ca1\u6709\u52a0\u9501\u65f6\u4f1a\u76f4\u63a5\u8bfb\u53d6\u6700\u65b0\u7684\u503c\uff0c\u82e5\u6709\u9501\u5219\u901a\u8fc7undo log\u83b7\u53d6\u5176\u5df2\u63d0\u4ea4\u7684\u6700\u65b0\u5feb\u7167\u7248\u672c(fresh snapshot)\u3002<\/p>\n<h2>\u53c2\u8003\uff1a<\/h2>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-consistent-read.html\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-consistent-read.html<\/a><br \/>\n<a href=\"https:\/\/www.modb.pro\/db\/75331\">https:\/\/www.modb.pro\/db\/75331<\/a><br \/>\n<a href=\"https:\/\/segmentfault.com\/a\/1190000025156465\">https:\/\/segmentfault.com\/a\/1190000025156465<\/a><br \/>\n<a href=\"https:\/\/juejin.cn\/post\/6871046354018238472\">https:\/\/juejin.cn\/post\/6871046354018238472<\/a><br \/>\n<a href=\"https:\/\/segmentfault.com\/a\/1190000039809030\">https:\/\/segmentfault.com\/a\/1190000039809030<\/a><br \/>\n<a href=\"https:\/\/segmentfault.com\/a\/1190000023332101\">https:\/\/segmentfault.com\/a\/1190000023332101<\/a><br \/>\n<a href=\"https:\/\/juejin.cn\/post\/6844903908146413576\">https:\/\/juejin.cn\/post\/6844903908146413576<\/a><\/p>\n<h1>6. MySQL\u7d22\u5f15\u7c7b\u578b<\/h1>\n<h2>6.1 \u6570\u636e\u7ed3\u6784\u89d2\u5ea6<\/h2>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u540d\u79f0<\/th>\n<th style=\"text-align: center;\">\u652f\u6301\u5f15\u64ce<\/th>\n<th style=\"text-align: center;\">\u6027\u8d28<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">Fulltext \u5168\u6587\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">InnoDB MyISAM<\/td>\n<td style=\"text-align: center;\">\u4e3b\u8981\u63d0\u9ad8\u5bf9\u6587\u672c\u7684\u67e5\u8be2\u6548\u7387<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">B+Tree B+\u6811\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">InnoDB MyISAM Memory<\/td>\n<td style=\"text-align: center;\">\u7565<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">Hash \u54c8\u5e0c\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">Memory InnoDB *<\/td>\n<td style=\"text-align: center;\">\uff08\u5728hash\u6876\u4e0d\u5927\u7684\u60c5\u51b5\u4e0b\uff09\u68c0\u7d22\u6548\u7387\u9ad8\uff1b\u53ea\u80fd\u4f7f\u7528\u7b49\u503c\u67e5\u8be2\u201c=\u201d<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3>6.1.1 InnoDB\u4e2d\u7684\u54c8\u5e0c\u7d22\u5f15<\/h3>\n<p>InnoDB\u4f7f\u7528\u54c8\u5e0c\u7d22\u5f15\u5bf9<strong>\u5b57\u5178<\/strong>\u8fdb\u884c\u67e5\u627e\uff0c\u54c8\u5e0c\u51b2\u7a81\u89e3\u51b3\u65b9\u6cd5\u4e3a\u62c9\u94fe\u6cd5\uff0c\u54c8\u5e0c\u51fd\u6570\u91c7\u7528\u9664\u6cd5\u6563\u5217\u65b9\u5f0f\u3002\u9700\u8981\u5f3a\u8c03\u7684\u662f\uff0cInnoDB\u4f7f\u7528\u7684\u662f\u81ea\u9002\u5e94\u54c8\u5e0c\u7d22\u5f15(Adaptive Hash Index, AHI)\u7684\uff0cInnoDB\u4f1a\u76d1\u63a7\u5bf9\u7d22\u5f15\u9875\u7684\u67e5\u8be2\uff0c\u5f53\u89c2\u5bdf\u5230\u5efa\u7acb\u54c8\u5e0c\u7d22\u5f15\u53ef\u4ee5\u5e26\u6765\u901f\u5ea6\u63d0\u5347\u65f6\u4f1a\u81ea\u52a8\u751f\u6210\u548c\u4f7f\u7528\u54c8\u5e0c\u7d22\u5f15\uff0c\u4e0d\u80fd\u4eba\u4e3a\u5e72\u9884\u662f\u5426\u5728\u4e00\u5f20\u8868\u4e2d\u751f\u6210\u54c8\u5e0c\u7d22\u5f15\u3002AHI\u76f4\u63a5\u5728\u54c8\u5e0c\u6876\u4e2d\u8bb0\u5f55\u6570\u636e\u6240\u5728\u7684\u5730\u5740\uff0c\u6545\u53ef\u4ee5\u4e00\u6b21\u6027\u627e\u5230\u7d22\u5f15\uff0c\u4e0d\u9700\u8981\u5728B+\u6811\u4e2d\u641c\u7d22\u6570\u4e2a\u5c42\u7ea7\u3002<\/p>\n<h3>6.1.2 InnoDB\u4e2d\u7684\u5168\u6587\u7d22\u5f15<\/h3>\n<p>\u987e\u540d\u601d\u4e49\uff0c\u5168\u6587\u7d22\u5f15\u6280\u672f\u4e3b\u8981\u7528\u4e8e\u89e3\u51b3\u6587\u672c\u67e5\u627e\u95ee\u9898\u3002\u4f20\u7edf\u7684B+\u6811\u7d22\u5f15\u53ef\u4ee5\u89e3\u51b3\u524d\u7f00\u6587\u672c\u7684\u68c0\u7d22\u95ee\u9898\uff0c\u4f8b\u5982<code>select * from t where text like &#039;xxx%&#039;<\/code>\uff1b\u4f46\u662f\u5bf9\u4e8e\u4ee5\u901a\u914d\u7b26\u5f00\u59cb\u7684\u6587\u672c\u68c0\u7d22\u5219\u65e0\u80fd\u4e3a\u529b\uff0c\u4f8b\u5982<code>... like &#039;%xxx%&#039;<\/code>\u3002\u800c\u5728\u5b9e\u9645\u5e94\u7528\u4e2d\uff0cB+\u6811\u7d22\u5f15\u652f\u6301\u7684\u524d\u8005\u4ec5\u80fd\u7528\u4e8e\u68c0\u7d22\u4ee5'xxx'\u5f00\u5934\u7684\u6587\u7ae0\uff0c\u8fd9\u663e\u7136\u662f\u4e0d\u6ee1\u8db3\u8981\u6c42\u7684\u3002<br \/>\nInnoDB\u7684\u5168\u6587\u7d22\u5f15\u91c7\u7528\u5168\u6587\u5012\u6392\u7d22\u5f15(full inverted index)\uff0c\u8fd9\u79cd\u7d22\u5f15\u4ee5\u8868\u7684\u5f62\u5f0f\u5b58\u50a8\u5355\u8bcd\u548c\u4ed6\u4eec\u51fa\u73b0\u7684\u4f4d\u7f6e\u4fe1\u606f\uff08\u6587\u6863id\uff0c\u7b2c\u51e0\u4e2a\u5b57\u7b26\u5f00\u59cb\uff09\u3002\u6b64\u5916\uff0c\u8fd8\u8bb0\u5f55\u4e86\u5355\u8bcd\u7b2c\u4e00\u6b21\u548c\u6700\u540e\u4e00\u6b21\u51fa\u73b0\u7684\u6587\u6863id\uff0c\u4ee5\u53ca\u6b64\u5355\u8bcd\u5728\u591a\u5c11\u4e2a\u6587\u6863\u4e2d\u5b58\u5728\u3002<\/p>\n<h2>6.2 \u7269\u7406\u5b58\u50a8\u89d2\u5ea6<\/h2>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u540d\u79f0<\/th>\n<th style=\"text-align: center;\">\u652f\u6301\u5f15\u64ce<\/th>\n<th style=\"text-align: center;\">\u4e3b\u952e\u7d22\u5f15\u6027\u8d28<\/th>\n<th style=\"text-align: center;\">\u4e8c\u7ea7\uff08\u8f85\u52a9\uff09\u7d22\u5f15\u6027\u8d28<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u805a\u7c07\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">InnoDB<\/td>\n<td style=\"text-align: center;\">\u4e3b\u952e\u7d22\u5f15\u7684\u53f6\u5b50\u8282\u70b9\u76f4\u63a5\u5b58\u653e\u6570\u636e\uff0c\u540c\u4e00\u53f6\u5b50\u8282\u70b9\u5185\u6570\u636e\u6309\u5e8f\u5b58\u653e<\/td>\n<td style=\"text-align: center;\">\u8f85\u52a9\u7d22\u5f15\u7684\u53f6\u5b50\u8282\u70b9\u8bb0\u5f55\u4e3b\u952e\u7684\u952e\u503c\uff0c\u9700\u8981\u4e8c\u6b21\u67e5\u8be2<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u975e\u805a\u7c07\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">MyISAM<\/td>\n<td style=\"text-align: center;\">\u4e3b\u952e\u7d22\u5f15\u7684\u53f6\u5b50\u8282\u70b9\u8bb0\u5f55\u6570\u636e\u6587\u4ef6\u4e2d\u7684\u504f\u79fb\u5730\u5740<\/td>\n<td style=\"text-align: center;\">\u8f85\u52a9\u7d22\u5f15\u4e0e\u4e3b\u952e\u7d22\u5f15\u76f8\u4e92\u72ec\u7acb\uff0c\u53f6\u5b50\u8282\u70b9\u8bb0\u5f55\u7684\u4e5f\u662f\u6570\u636e\u6587\u4ef6\u4e2d\u7684\u504f\u79fb\u5730\u5740<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u805a\u7c07\u7d22\u5f15\u4e0b\uff0c\u4e3b\u952e\u7d22\u5f15\u6587\u4ef6\u5c31\u662f\u6570\u636e\u6587\u4ef6\uff1b\u975e\u805a\u7c07\u7d22\u5f15\u4e0b\uff0c\u7d22\u5f15\u6587\u4ef6\u4e0e\u6570\u636e\u6587\u4ef6\u5206\u5f00\u3002<\/p>\n<h2>6.3 \u903b\u8f91\u89d2\u5ea6<\/h2>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u540d\u79f0<\/th>\n<th style=\"text-align: center;\">\u63cf\u8ff0<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u4e3b\u952e\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">\u4e00\u5f20\u8868\u53ea\u80fd\u6709\u4e00\u4e2a\uff0c\u4e0d\u91cd\u590d\uff0c\u975e\u7a7a\u3002<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u552f\u4e00\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">\u53ef\u4ee5\u6709\u591a\u4e2a\uff0c\u5141\u8bb8\u7a7a\uff0c\u4ec5\u5728\u7a7a\u7684\u60c5\u51b5\u4e0b\u53ef\u4ee5\u91cd\u590d<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u666e\u901a\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">\u666e\u901a\u7684\u7d22\u5f15<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>6.4 \u6d89\u53ca\u5230\u5217\u7684\u4e2a\u6570\u89d2\u5ea6<\/h2>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u540d\u79f0<\/th>\n<th style=\"text-align: center;\">\u63cf\u8ff0<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u5355\u5217\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">\u57fa\u4e8e\u8868\u4e2d\u4e00\u4e2a\u5b57\u6bb5\u7684\u7d22\u5f15<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u8054\u5408(\u590d\u5408)\u7d22\u5f15<\/td>\n<td style=\"text-align: center;\">\u57fa\u4e8e\u4e00\u5f20\u8868\u4e2d\u7684\u591a\u4e2a\u5b57\u6bb5\u7684\u7d22\u5f15<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h1>7. \u8054\u5408\u7d22\u5f15<\/h1>\n<h2>7.1 \u8054\u5408\u7d22\u5f15\u7684B+\u6811\u7ed3\u6784<\/h2>\n<p>\u5bf9\u4e8e\u8868\uff1a<\/p>\n<pre><code class=\"language-sql\">CREATE TABLE `t1`  (\n  `a` int(11) NOT NULL AUTO_INCREMENT,\n  `b` int(11) NULL DEFAULT NULL,\n  `c` int(11) NULL DEFAULT NULL,\n  `d` int(11) NULL DEFAULT NULL,\n  `e` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n  PRIMARY KEY (`a`) USING BTREE,\n  INDEX `index_bcd`(`b`, `c`, `d`) USING BTREE\n) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;<\/code><\/pre>\n<p>\u5176\u4e2d\u7684\u8054\u5408\u7d22\u5f15bcd\uff0c\u5176B+\u6811\u7ed3\u6784\u5982\u4e0b\u6240\u793a\uff1a<br \/>\n<div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/p1-jj.byteimg.com\/tos-cn-i-t2oaga2asx\/gold-user-assets\/2020\/2\/27\/170867cb6af0a72d~tplv-t2oaga2asx-zoom-in-crop-mark:1304:0:0:0.awebp'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  decoding=\"async\" data-original=\"https:\/\/p1-jj.byteimg.com\/tos-cn-i-t2oaga2asx\/gold-user-assets\/2020\/2\/27\/170867cb6af0a72d~tplv-t2oaga2asx-zoom-in-crop-mark:1304:0:0:0.awebp\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"B+\u6811\u7ed3\u6784\" \/><\/div><\/p>\n<h2>7.2 \u8054\u5408\u7d22\u5f15OR\u591a\u4e2a\u5355\u5217\u7d22\u5f15\uff1f<\/h2>\n<p>\u5f53\u5b58\u5728\u591a\u4e2a<strong>\u53ef\u7528<\/strong>\u7684\u5355\u5217\u7d22\u5f15\u65f6\uff08\u5373\u4e0d\u56e0\u4e3a\u4f7f\u7528OR\u7b49\u64cd\u4f5c\u5bfc\u81f4\u7d22\u5f15\u5931\u6548\uff09\uff0cMySQL\u4f1a\u6839\u636e\u67e5\u8be2\u4f18\u5316\u7b56\u7565\uff0c\u9009\u62e9\u5176\u4e2d\u4e00\u4e2a\u5355\u5217\u7d22\u5f15\u4f7f\u7528\uff1b\u5982\u679c\u540c\u65f6\u5b58\u5728\u5355\u5217\u7d22\u5f15\u548c\u8054\u5408\u7d22\u5f15\uff0c\u4e5f\u662f\u6839\u636e\u4f18\u5316\u7b56\u7565\u9009\u62e9\u4e8c\u8005\u4e4b\u4e00\u4f7f\u7528\u3002<\/p>\n<h2>7.3 \u8054\u5408\u7d22\u5f15\u7684\u6700\u5de6\u5339\u914d\u539f\u5219<\/h2>\n<p>\u8054\u5408\u7d22\u5f15\u9075\u5faa\u6700\u5de6\u5339\u914d\u539f\u5219\uff0c\u5373\u5728\u67e5\u8be2\u6761\u4ef6\u4e2d\u4f7f\u7528\u4e86\u8054\u5408\u7d22\u5f15\u7684\u7b2c\u4e00\u4e2a\u5b57\u6bb5\uff0c\u8054\u5408\u7d22\u5f15\u624d\u4f1a\u88ab\u4f7f\u7528\u3002\u5f53\u4ee3\u7684SQL\u4f18\u5316\u5668\u4f1a\u81ea\u52a8\u4f18\u5316SQL\u8bed\u53e5\u4e2d\u7684\u5b57\u6bb5\u987a\u5e8f\uff0c\u53ea\u8981\u4f7f\u7528\u4e86\u7b2c\u4e00\u4e2a\u5b57\u6bb5\u5373\u53ef\uff0c\u4e0d\u4e00\u5b9a\u975e\u8981\u6309\u987a\u5e8f\u5199\u3002<\/p>\n<h2>7.4 \u8054\u5408\u7d22\u5f15\u7684\u8303\u56f4\u67e5\u8be2\u95ee\u9898<\/h2>\n<p>\u6700\u5de6\u5339\u914d\u539f\u5219\u9047\u5230\u8303\u56f4\u67e5\u8be2\u5219\u505c\u6b62\u4f7f\u7528\u8be5\u5b57\u6bb5\u4e4b\u540e\u7684\u7d22\u5f15\uff0c\u4f8b\u5982\uff1a<\/p>\n<pre><code class=\"language-sql\">SELECT * FROM table WHERE a &gt; 1 and b = 2; <\/code><\/pre>\n<p>\u82e5\u8981\u5efa\u7acb\u8054\u5408\u7d22\u5f15\uff0c\u5e94\u5f53\u662fKEY '\u8054\u5408\u7d22\u5f15' ('b', 'a') \uff0c\u8fd9\u6837a\u548cb\u7684\u67e5\u8be2\u90fd\u53ef\u4ee5\u5229\u7528\u7d22\u5f15\uff1b\u5982\u679c\u662f(a,b)\uff0c\u90a3\u5c31\u53ea\u6709a\u80fd\u7528\u5230\u4e86<\/p>\n<h2>7.5 \u53c2\u8003<\/h2>\n<p><a href=\"https:\/\/www.cnblogs.com\/rjzheng\/p\/12557314.html\">https:\/\/www.cnblogs.com\/rjzheng\/p\/12557314.html<\/a><br \/>\n<a href=\"https:\/\/juejin.cn\/post\/6844904073955639304\">https:\/\/juejin.cn\/post\/6844904073955639304<\/a><\/p>\n<h1>8.\u7d22\u5f15\u8fc7\u591a\u7684\u5f0a\u7aef<\/h1>\n<ul>\n<li>\u7d22\u5f15\u672c\u8eab\u5f88\u5927\uff0c\u6d88\u8017\u5185\u5b58\/\u78c1\u76d8\u7a7a\u95f4\u3002<\/li>\n<li>\u7d22\u5f15\u5728\u589e\u5220\u6539\u65f6\u4e5f\u9700\u8981\u7ef4\u62a4\uff0c\u964d\u4f4e\u6548\u7387<\/li>\n<\/ul>\n<h1>9.\u7d22\u5f15\u5931\u6548\u7684\u573a\u666f<\/h1>\n<p>\u7d22\u5f15\u5931\u6548\u65f6\uff0c\u4f1a\u6267\u884c\u5168\u8868\u626b\u63cf\uff0c<strong>\u6b64\u65f6\u884c\u9501\u9000\u5316\u6210\u8868\u9501\uff0c\u610f\u5411\u9501\u673a\u5236\u6d3e\u4e0a\u7528\u573a\u3002<\/strong><\/p>\n<ol>\n<li>\u4f7f\u7528or<\/li>\n<li>\uff08\u53ea\u662f<strong>\u53ef\u80fd<\/strong>\u5bfc\u81f4\u7d22\u5f15\u5931\u6548\uff09\u4f7f\u7528!=\u6216&lt;&gt;\u8fdb\u884c\u67e5\u8be2<\/li>\n<li>\u8054\u5408\u7d22\u5f15\u9047\u5230\u8303\u56f4\u5339\u914d\u540e\u8fb9\u7684\u5217\u3002\u53c2\u80036.4.3\u30016.4.4<\/li>\n<li>\u5199SQL\u8bed\u53e5\u65f6\u82e5\u5b57\u6bb5\u4e0d\u4e00\u81f4\uff0c\u4f8b\u5982\u5bf9\u4e00\u4e2avarchar\u5b57\u6bb5\u7528\u6570\u5b57\u8fdb\u884c\u67e5\u8be2\uff0c\u6b64\u65f6\u9700\u8981\u8fdb\u884c\u7c7b\u578b\u8f6c\u6362\u800c\u4e14\u5bfc\u81f4\u7d22\u5f15\u5931\u6548\u3002<\/li>\n<li>\u5bf9\u7d22\u5f15\u5b57\u6bb5\u8fdb\u884c\u64cd\u4f5c\uff0c\u4f8b\u5982\u8ba1\u7b97\u3001\u51fd\u6570\u3001\u7c7b\u578b\u8f6c\u6362\uff0c\u5178\u578b\u4f8b\u5b50select * from t where ABS(val) = 1;<\/li>\n<li>\uff08\u53ea\u662f<strong>\u53ef\u80fd<\/strong>\u5bfc\u81f4\u7d22\u5f15\u5931\u6548\uff09\u4f7f\u7528is null \u6216\u8005is not null\uff08\u8fd9\u4e5f\u662f\u4e3a\u4ec0\u4e48\u6700\u597d\u7d22\u5f15\u5217\u8981\u662fnot NULL\uff09<\/li>\n<li>\u4f7f\u7528\u4ee5\u901a\u914d\u7b26\u5f00\u5934\u7684\u6a21\u7cca\u5339\u914d\uff0c\u4f8b\u5982&quot;%July&quot;\u4f1a\u5bfc\u81f4\u5931\u6548\uff0c\u800c&quot;July%&quot;\u5219\u4e0d\u4f1a<\/li>\n<\/ol>\n<h2>9.1 !=\u6216&lt;&gt;\u7684\u7d22\u5f15\u5931\u6548\u95ee\u9898<\/h2>\n<p>\u5728\u805a\u7c07\u7d22\u5f15\u4e0a\uff0c\u4f7f\u7528\u4e0d\u7b49\u67e5\u8be2\u4ecd\u7136\u53ef\u80fd\u8d70\u7d22\u5f15\uff1b\u6700\u7ec8\u8fd8\u662f\u8981\u770b\u4f18\u5316\u5668\u7684\u4f30\u7b97\u3002<\/p>\n<h2>9.2 NULL\u5728B+\u6811\u7d22\u5f15\u4e2d\u7684\u5b58\u50a8\u65b9\u5f0f &amp; is not null\u7684\u7d22\u5f15\u5931\u6548\u95ee\u9898<\/h2>\n<p>\u9996\u5148\u4e3b\u952e\u662f\u4e0d\u5141\u8bb8NULL\u7684\uff0c\u6240\u4ee5NULL\u53ea\u51fa\u73b0\u5728\u4e8c\u7ea7\u7d22\u5f15\u4e2d\uff1b\u5728\u4e8c\u7ea7\u7d22\u5f15\u7684B+\u6811\u91cc\uff0cNULL\u8bb0\u5f55\u90fd\u653e\u5728B+\u6811\u6700\u5de6\u4fa7\u7684\u53f6\u5b50\u8282\u70b9\u3002\u7531\u4e8e\u68c0\u7d22\u4e8c\u7ea7\u7d22\u5f15\u9700\u8981\u56de\u8868\uff0c\u4f18\u5316\u5668\u5728\u8fdb\u884c\u4f18\u5316\u65f6\u4f1a\u5927\u6982\u4f30\u8ba1\u56de\u8868\u6b21\u6570\uff0c\u4ece\u800c\u51b3\u5b9a\u662f\u5e72\u8106\u5728\u805a\u7c07\u7d22\u5f15\u4e0a\u626b\u5e93\u8fd8\u662f\u8d70\u4e8c\u7ea7\u7d22\u5f15\u3002<\/p>\n<h1>10. InnoDB\u4e3a\u4ec0\u4e48\u8981\u7528\u81ea\u589eid\u4f5c\u4e3a\u4e3b\u952e\uff1f<\/h1>\n<p>InnoDB\u4f7f\u7528B+\u6811\u805a\u7c07\u7d22\u5f15\uff0c\u6570\u636e\u8bb0\u5f55\u672c\u8eab\u88ab\u5b58\u4e8e\u4e3b\u7d22\u5f15\u7684\u53f6\u5b50\u8282\u70b9\uff0c\u540c\u4e00\u53f6\u5b50\u8282\u70b9\u5185\u6570\u636e\u6309\u4e3b\u952e\u987a\u5e8f\u5b58\u653e\u3002<br \/>\n\u6587\u4ef6\u7cfb\u7edf\u8bfb\u53d6\u78c1\u76d8\u65f6\u4e00\u822c\u4ee5\u9875\u4e3a\u5355\u4f4d\u8fdb\u884c\u8bfb\u53d6\uff0c\u4e3a\u4e86\u914d\u5408\u6587\u4ef6\u7cfb\u7edf\u5de5\u4f5c\u4ee5\u63d0\u9ad8\u8bfb\u53d6\u6548\u7387\uff0cMySQL\u5e95\u5c42\u4e5f\u662f\u4ee5\u6570\u636e\u9875\u4e3a\u5355\u4f4d\u6765\u5b58\u50a8\u6570\u636e\u548c\u8fdb\u884c\u78c1\u76d8\u7ba1\u7406\u7684\uff0c\u4e00\u4e2a\u53f6\u5b50\u8282\u70b9\u5c31\u662f\u4e00\u4e2a\u6570\u636e\u9875\uff0c\u9ed8\u8ba4\u4e3a16k\u3002\u5f53\u4e00\u4e2a\u6570\u636e\u9875\u65e0\u6cd5\u5bb9\u7eb3\u51c6\u5907\u63d2\u5165\u7684\u65b0\u6570\u636e\u65f6\uff0c\u4f1a\u5f00\u8f9f\u4e00\u4e2a\u65b0\u7684\u6570\u636e\u9875\u3002<br \/>\n\u82e5\u81ea\u589e\u4e3b\u952e\uff0c\u6bcf\u6b21\u65b0\u589e\u8bb0\u5f55\u65f6\u90fd\u5728\u5f53\u524d\u6700\u540e\u4e00\u4e2a\u53f6\u5b50\u8282\u70b9\u7684\u6570\u636e\u9875\uff0c\u5373\u4f7f\u9700\u8981\u5f00\u8f9f\u65b0\u7684\u6570\u636e\u9875\uff0c\u4e5f\u53ea\u9700\u8981\u94fe\u63a5\u5230\u6700\u540e\u4e00\u4e2a\u53f6\u5b50\u8282\u70b9\u7684\u540e\u9762\uff1b\u800c\u5982\u679c\u91c7\u7528\u968f\u673a\u4e3b\u952e\uff0c\u65b0\u7684\u6570\u636e\u5927\u6982\u7387\u9700\u8981\u63d2\u5165\u5230\u5df2\u6709\u7684\u6570\u636e\u9875\u4e2d\uff0c\u82e5\u8be5\u6570\u636e\u9875\u65e0\u6cd5\u5bb9\u7eb3\u8fd9\u884c\u65b0\u6570\u636e\uff0c\u5219\u9700\u8981\u5bf9\u8be5\u6570\u636e\u9875\u8fdb\u884c\u5206\u88c2\u64cd\u4f5c\uff0c\u8fd9\u6837\u4e00\u6765\u4e0d\u4ec5\u9700\u8981\u8fdb\u884c\u975e\u5e38\u8017\u65f6\u7684\u6570\u636e\u62f7\u8d1d\uff08\u6700\u574f\u60c5\u51b5\u4e0b\uff0c\u8be5\u76ee\u6807\u6570\u636e\u9875\u957f\u671f\u672a\u4f7f\u7528\u5df2\u88ab\u6e05\u51fa\u7f13\u5b58\u5199\u56de\u78c1\u76d8\uff0c\u6b64\u65f6\u53c8\u9700\u8981\u91cd\u65b0\u4ece\u78c1\u76d8\u8bfb\u53d6\uff09\uff0c\u8fd8\u4f1a\u4f7f\u5f97\u7d22\u5f15\u7ed3\u6784\u4e0d\u591f\u7d27\u51d1\u3001\u67e5\u8be2\u6027\u80fd\u4e0b\u964d\u3002\u540e\u7ee7\u4e0d\u5f97\u4e0d\u91cd\u5efa\u8868\u5e76\u8fdb\u884c\u9875\u9762\u4f18\u5316\u3002<\/p>\n<h2>\u53c2\u8003<\/h2>\n<p><a href=\"https:\/\/zhuanlan.zhihu.com\/p\/98818611\">https:\/\/zhuanlan.zhihu.com\/p\/98818611<\/a><\/p>\n<h1>11. \u8986\u76d6\u7d22\u5f15\u548c\u56de\u8868<\/h1>\n<p>\u8986\u76d6\u7d22\u5f15\uff1a\u7d22\u5f15\u5305\u542b\u4e86\u9700\u8981\u67e5\u8be2\u5b57\u6bb5\u7684\u503c\uff0c\u4f8b\u5982InnoDB\u7684\u4e3b\u952e\u7d22\u5f15<br \/>\n\u56de\u8868\uff1a\u7d22\u5f15\u4e0d\u80fd\u62ff\u5230\u6570\u636e\uff0c\u53ea\u62ff\u5230\u5bf9\u5e94\u4e3b\u952e\uff0c\u8fd8\u9700\u67e5\u8be2\u4e3b\u952e\u7d22\u5f15\uff0c\u4f8b\u5982InnoDB\u7684\u4e8c\u7ea7\uff08\u8f85\u52a9\uff09\u7d22\u5f15\u3002<br \/>\n\u56de\u8868\u7684\u6027\u80fd\u4f4e\u4e0b\uff0c\u56e0\u4e3a\u4e8c\u7ea7\uff08\u8f85\u52a9\uff09\u7d22\u5f15\u7684\u53f6\u5b50\u8282\u70b9\u4e2d\u5b58\u653e\u7684\u4e3b\u952e\u662f\u65e0\u5e8f\u7684\uff0c\u5f88\u53ef\u80fd\u5206\u5e03\u5728\u591a\u4e2a\u4e0d\u540c\u7684\u4e3b\u952e\u805a\u7c07\u7d22\u5f15\u7684\u53f6\u5b50\u8282\u70b9\u4e0a\uff0c\u518d\u53bb\u8bbf\u95ee\u8fd9\u4e48\u591a\u8282\u70b9\u9700\u8981\u8bfb\u53d6\u8bb8\u591a\u4e0d\u540c\u7684\u6570\u636e\u9875\uff0c\u4f1a\u5bfc\u81f4\u9891\u7e41\u7684\u78c1\u76d8IO\u3002<\/p>\n<h1>12. \u7d22\u5f15\u4f18\u5316<\/h1>\n<ol>\n<li>\u5c3d\u53ef\u80fd\u5728\u7d22\u5f15\u5217\u4e0a\u8bbe\u7f6enot null\uff0c\u800c\u662f\u8bbe\u7f6e\u4e00\u4e2a\u9ed8\u8ba4\u503c\uff0c\u4f8b\u59820\u6216\u80051970-01-01<\/li>\n<li>\u5145\u5206\u5229\u7528\u590d\u5408\u7d22\u5f15\u7684\u8303\u56f4\u67e5\u8be2\u3001\u6700\u5de6\u5339\u914d\u673a\u5236<\/li>\n<li>\u5408\u7406\u9009\u62e9\u903b\u8f91\u4e3b\u952e\u6216\u4e1a\u52a1\u4e3b\u952e\u3002<\/li>\n<\/ol>\n<blockquote>\n<p>\u903b\u8f91\u4e3b\u952e\u548c\u4e1a\u52a1\u4e3b\u952e<br \/>\n\u903b\u8f91\u4e3b\u952e\u6307\u4e3b\u952e\u503c\u4e0e\u5f53\u524d\u8868\u7684\u4e1a\u52a1\u903b\u8f91\u65e0\u5173\uff0c\u4f8b\u5982InnoDB\u7684\u81ea\u589e\u4e3b\u952e\u3002\u4e1a\u52a1\u4e3b\u952e\u5219\u76f8\u53cd\uff0c\u4f8b\u5982\u4ee5\u94f6\u884c\u5361\u53f7\u4f5c\u4e3a\u4e3b\u952e\u3002<br \/>\n\u903b\u8f91\u4e3b\u952e\u7684\u4f18\u52bf\u6709\uff1a1. \u63d2\u5165\u6027\u80fd\u9ad8\uff08\u89c1\u7b2c9\u8282\uff1aInnoDB\u81ea\u589e\u4e3b\u952e\u539f\u56e0\uff09\uff1b2. \u82e5\u51fa\u73b0\u4e1a\u52a1\u53d8\u5316\uff0c\u4f8b\u5982\u8981\u4e3a\u4f5c\u4e3a\u4e1a\u52a1\u4e3b\u952e\u7684id\u52a0\u4e00\u4f4d\u5b57\u6bcd\uff0c\u4f1a\u5bfc\u81f4\u5de8\u5927\u7684\u7ef4\u62a4\u5f00\u9500\u3002<\/p>\n<\/blockquote>\n<h1>13. MySQL\u4e3a\u4f55\u9009\u62e9B+\u6811\u4f5c\u4e3a\u7d22\u5f15 \/ B+\u6811\u5bf9\u6bd4\u5176\u4ed6\u6570\u636e\u7ed3\u6784\u7684\u4f18\u52bf<\/h1>\n<p>\u4e3b\u8981\u4ece\u67e5\u8be2\u6027\u80fd\u4f18\u52a3\uff08\u6811\u7684\u9ad8\u5ea6\uff09\u3001\u67e5\u8be2\u6027\u80fd\u7a33\u5b9a\uff08\u67e5\u8be2\u6df1\u5ea6\u4e00\u81f4\u6027\uff09\u3001\u8303\u56f4\u67e5\u8be2\/\u6392\u5e8f\/\u626b\u5e93\u6548\u7387\u7b49\u4e09\u4e2a\u89d2\u5ea6\u53bb\u601d\u8003\u3002<\/p>\n<h2>13.1 B+\u6811\u5bf9\u6bd4B\u6811\u7684\u4f18\u52bf<\/h2>\n<ol>\n<li>\u67e5\u8be2\u6548\u7387\u9ad8\uff1a\u901a\u5e38\u4e00\u4e2a\u8282\u70b9\u7684\u5927\u5c0f\u8bbe\u7f6e\u4e3a\u4e00\u4e2a\u9875\u7684\u5927\u5c0f\u3002\u7531\u4e8eB+\u6811\u7684\u975e\u53f6\u5b50\u8282\u70b9\u4e0d\u5b58\u653e\u6570\u636e\uff0c\u6545\u4e00\u4e2a\u975e\u53f6\u5b50\u8282\u70b9\u53ef\u4ee5\u5b58\u653e\u66f4\u591a\u7684\u6570\u636e\u5173\u952e\u5b57\uff0c\u6811\u7684\u9ad8\u5ea6\u663e\u8457\u964d\u4f4e\uff0c\u67e5\u8be2\u65f6\u9700\u8981\u7684\u78c1\u76d8IO\u6b21\u6570\u663e\u8457\u5c11\u3002<\/li>\n<li>\u67e5\u8be2\u6027\u80fd\u7a33\u5b9a\uff1aB+\u6811\u6570\u636e\u90fd\u5728\u53f6\u5b50\u7ed3\u70b9\uff0c\u6bcf\u6b21\u67e5\u8be2\u7684\u6df1\u5ea6\u76f8\u540c\uff1bB\u6811\u975e\u53f6\u5b50\u8282\u70b9\u4e5f\u5b58\u653e\u6570\u636e\uff0c\u67e5\u8be2\u65f6\u6df1\u5ea6\u4e0d\u4e00\u81f4\uff0c\u6027\u80fd\u4e0d\u7a33\u5b9a\u3002<\/li>\n<li>\u626b\u5e93\u6548\u7387\u9ad8\uff1aB+\u6811\u7684\u53f6\u5b50\u7ed3\u70b9\u6784\u6210\u94fe\u8868\uff0c\u626b\u5e93\u987a\u5e8f\u904d\u5386\u5373\u53ef\u3002B\u6811\u5219\u5fc5\u987b\u4e2d\u5e8f\u904d\u5386\uff0c\u5728\u53f6\u5b50\u7ed3\u70b9\u548c\u7236\u7ed3\u70b9\u4e4b\u95f4\u6765\u56de\u8bbf\u95ee\uff0c\u6548\u7387\u4f4e\u3002<\/li>\n<\/ol>\n<h3>13.1.1 \u62d3\u5c55\uff1a\u751f\u4ea7\u73af\u5883\u4e2d\u7684B+\u6811\u9ad8\u5ea6\u4e00\u822c\u662f\u591a\u5c11\uff1f<\/h3>\n<p>\u4e00\u822c\u662f2~3\u5c42\u3002\u4e0b\u6587\u8fdb\u884c\u4e00\u4e2a\u7c97\u7565\u7684\u4f30\u7b97\uff1a<br \/>\nInnoDB\u9ed8\u8ba4\u5c06\u8282\u70b9\u5927\u5c0f\u8bbe\u7f6e\u4e3a\u4e00\u4e2a\u6570\u636e\u9875\u7684\u5927\u5c0f\uff0c\u5373\u9ed8\u8ba416K\uff1bB+\u6811\u975e\u53f6\u5b50\u8282\u70b9\u53ea\u5b58\u50a8\u7d22\u5f15\u7684\u952e\u503c\u548c\u524d\u5f80\u4e0b\u4e00\u7ea7\u8282\u70b9\u7684\u6307\u9488\u3002\u8fd9\u91cc\u6211\u4eec\u8bbe\u952e\u503c\u7684\u7c7b\u578b\u4e3a<code>BIGINT<\/code>\uff0c\u5927\u5c0f8\u5b57\u8282\uff0c\u6307\u9488\u5927\u5c0f\u5728InnoDB\u4e2d\u8bbe\u7f6e\u4e3a6\u5b57\u8282\uff0c\u8fd9\u6837\u4e00\u7ec4\u7d22\u5f15\u503c+\u6307\u9488\u4e00\u5171\u5360\u752814\u5b57\u8282\u7684\u7a7a\u95f4\u3002\u7531\u4e8e\u662f\u4f30\u7b97\uff0c\u8fd9\u91cc\u5ffd\u7565InnoDB\u9875\u4e2d\u5e94\u5f53\u5305\u542b\u7684\u5934\u5c3e\u6570\u636e\uff0c\u6b64\u65f6\u4e00\u4e2a\u6570\u636e\u9875\u53ef\u4ee5\u5b58\u653e16 <em> 1024 \/ 14 = 1170\u4e2a\u7d22\u5f15\u3002\u800c\u5bf9\u4e8e\u53f6\u5b50\u8282\u70b9\uff0c\u5b9e\u9645\u5e94\u7528\u4e2d\u4e00\u6761\u6570\u636e\u5f80\u5f80\u662f1K\u5de6\u53f3\u5927\u5c0f\uff0c\u5373\u4e00\u4e2a\u6570\u636e\u9875\u53ef\u4ee5\u5b58\u653e16\u6761\u6570\u636e\u3002<br \/>\n\u8fd9\u6837\uff0c\u5bf9\u4e8e\u4e00\u4e2a2\u5c42\u7684B+\u6811\uff0c\u6839\u8282\u70b9\u5b58\u653e1170\u6761\u7d22\u5f15\uff0c\u5bf9\u5e94\u7684\u53f6\u5b50\u8282\u70b9\u6bcf\u4e2a\u53ef\u5b58\u653e16\u6761\u6570\u636e\uff0c\u4e00\u5171\u53ef\u4ee5\u5b58\u653e1170 <\/em> 16 = 18720\u6761\u6570\u636e\uff1b\u800c\u5bf9\u4e8e\u4e00\u4e2a3\u5c42\u7684B+\u6811\uff0c\u6839\u8282\u70b9\u548c\u7b2c\u4e00\u5c42\u53ef\u4ee5\u5b58\u653e1170 <em> 1170 = 1368900\u6761\u7d22\u5f15\uff0c\u53f6\u5b50\u8282\u70b9\u53ef\u4ee5\u5b58\u653e1368900 <\/em> 16 = 21902400\u6761\u6570\u636e\u3002\u5373\u4e00\u4e2a3\u5c42B+\u6811\u5df2\u7ecf\u53ef\u4ee5\u5b58\u653e\u4e24\u5343\u4e07\u6761\u6570\u636e\uff0c\u5df2\u7ecf\u80fd\u591f\u6ee1\u8db3\u5927\u90e8\u5206\u573a\u666f\u7684\u9700\u6c42\u3002\u5982\u679c\u6709\u66f4\u5927\u7684\u7d22\u5f15\u8981\u6c42\uff0c\u5c31\u5e94\u8be5\u8003\u8651\u6c34\u5e73\u5206\u8868\u4e86\u3002<\/p>\n<h3>\u53c2\u8003\uff1a<\/h3>\n<p><a href=\"https:\/\/www.nowcoder.com\/discuss\/965851?trackId=2a7vtesb832g8ojodjzyw\">https:\/\/www.nowcoder.com\/discuss\/965851?trackId=2a7vtesb832g8ojodjzyw<\/a><\/p>\n<h2>13.2 B+\u6811\u5bf9\u6bd4\u7ea2\u9ed1\u6811\u7684\u4f18\u52bf<\/h2>\n<ol>\n<li>\u67e5\u8be2\u6548\u7387\u9ad8\uff1a\u7ea2\u9ed1\u6811\u662f\u4e00\u79cd\u5e73\u8861\u4e8c\u53c9\u6392\u5e8f\u6811\uff0c\u4e8c\u53c9\u6027\u8d28\u5bfc\u81f4\u6811\u7684\u9ad8\u5ea6\u8fc7\u9ad8\uff0c\u6548\u7387\u964d\u4f4e\uff1b<\/li>\n<li>\u67e5\u8be2\u6027\u80fd\u7a33\u5b9a\uff1a\u7ea2\u9ed1\u6811\u975e\u53f6\u5b50\u8282\u70b9\u4e5f\u5b58\u653e\u6570\u636e\uff0c\u67e5\u8be2\u65f6\u6df1\u5ea6\u4e0d\u4e00\u81f4\uff0c\u6027\u80fd\u4e0d\u7a33\u5b9a\u3002<\/li>\n<li>\u63d2\u5165\u6027\u80fd\u9ad8\uff1a\u63d2\u5165\u6570\u636e\u65f6\uff0c\u7ef4\u62a4\u7ea2\u9ed1\u6811\u7ed3\u6784\u9700\u8981\u8fdb\u884c\u5de6\u65cb\/\u53f3\u65cb\uff0c\u5f80\u5f80\u4f1a\u9020\u6210\u5927\u91cf\u8282\u70b9\u7684\u4fee\u6539\uff0c\u5927\u91cf\u78c1\u76d8IO\u5e26\u6765\u6548\u7387\u964d\u4f4e\u3002<\/li>\n<\/ol>\n<h2>13.3 B+\u6811\u5bf9\u6bd4\u54c8\u5e0c\u8868\u7684\u4f18\u52bf<\/h2>\n<ol>\n<li>\u8303\u56f4\u67e5\u8be2\u6548\u7387\u9ad8\uff1aB+\u6811\u7684\u8282\u70b9\u5173\u952e\u5b57\u5177\u6709\u6709\u5e8f\u6027\uff0c\u8303\u56f4\u67e5\u8be2\u6548\u7387\u5f88\u9ad8\uff1b\u800c\u5bf9\u5efa\u7acb\u54c8\u5e0c\u7d22\u5f15\u7684\u6570\u636e\u8fdb\u884c\u8303\u56f4\u67e5\u8be2\u5219\u53ea\u80fd\u5168\u8868\u626b\u63cf<\/li>\n<li>\u6392\u5e8f\u6548\u7387\u9ad8\uff1a\u540c\u6837\u662f\u7531\u4e8e\u6709\u5e8f\u6027<\/li>\n<li>\u67d0\u4e2a\u54c8\u5e0c\u6876\u8fc7\u5927\u4ee5\u540e\u67e5\u8be2\u6027\u80fd\u4e0b\u964d\uff0c\u67e5\u8be2\u6027\u80fd\u4e0d\u4e00\u5b9a\u66f4\u597d\u4e14\u4e0d\u7a33\u5b9a<\/li>\n<\/ol>\n<h1>14. InnoDB\u548cMyISAM\u5bf9\u6bd4<\/h1>\n<h2>14.1 \u5bf9\u6bd4\u8868\u683c<\/h2>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">InnoDB<\/th>\n<th style=\"text-align: center;\">MyISAM<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u652f\u6301\u4e8b\u52a1\uff0c\u53ef\u4ee5\u4f7f\u7528commit\u548crollback\u8bed\u53e5\uff0c\u5b9e\u73b0\u4e86\u6807\u51c6\u76844\u4e2a\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b<\/td>\n<td style=\"text-align: center;\">\u4e0d\u652f\u6301\u4e8b\u52a1<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u652f\u6301\u884c\u9501<\/td>\n<td style=\"text-align: center;\">\u53ea\u6709\u8868\u9501<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u805a\u7c07\u7d22\u5f15\uff0c\u4e3b\u7d22\u5f15\u53f6\u5b50\u7ed3\u70b9\u76f4\u63a5\u5b58\u653e\u6570\u636e\uff0c\u4e3b\u7d22\u5f15\u5728\u7f13\u5b58\u4e2d\u65f6\u53ef\u4ee5\u65e0\u9700\u989d\u5916\u78c1\u76d8IO<\/td>\n<td style=\"text-align: center;\">\u975e\u805a\u7c07\u7d22\u5f15\uff0c\u4e3b\u7d22\u5f15\u53f6\u5b50\u7ed3\u70b9\u5b58\u653e\u6570\u636e\u5728\u6570\u636e\u6587\u4ef6\u4e2d\u7684\u504f\u79fb\u5730\u5740<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u4e8c\u7ea7\u7d22\u5f15\u5b58\u653e\u7684\u662f\u4e3b\u952e\u7684\u503c\uff0c\u9700\u8981\u4e8c\u6b21\u67e5\u8be2<\/td>\n<td style=\"text-align: center;\">\u4e8c\u7ea7\u7d22\u5f15\u548c\u4e3b\u7d22\u5f15\u4e92\u76f8\u72ec\u7acb\u3002<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u5fc5\u987b\u6709\u4e3b\u952e<\/td>\n<td style=\"text-align: center;\">\u65e0\u9700\u4e3b\u952e<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u652f\u6301\u5916\u952e<\/td>\n<td style=\"text-align: center;\">\u4e0d\u652f\u6301\u5916\u952e<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u6709\u5f02\u5e38\u5d29\u6e83\u6062\u590d\u673a\u5236\uff0c\u57fa\u4e8eredo log<\/td>\n<td style=\"text-align: center;\">\u65e0\u5f02\u5e38\u5d29\u6e83\u6062\u590d\u673a\u5236<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u9002\u5408\u5927\u91cfupdate<\/td>\n<td style=\"text-align: center;\">\u9002\u5408\u5927\u91cfselect<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>14.2 \u4e3a\u4f55MyISAM\u9002\u5408\u5927\u91cf\u8bfb\uff08\u5927\u91cfselect\uff09\uff1f<\/h2>\n<p>\u9996\u5148\uff0cMyISAM\u91c7\u7528\u975e\u805a\u7c07\u7d22\u5f15\uff0c\u7d22\u5f15\u6587\u4ef6\u4e0e\u6570\u636e\u6587\u4ef6\u5206\u5f00\u5bfc\u81f4\u5176\u7d22\u5f15\u6587\u4ef6\u4f53\u79ef\u66f4\u5c0f\uff0c\u53ef\u4ee5\u5728\u5185\u5b58\u4e2d\u4e00\u6b21\u6027\u7f13\u5b58\u66f4\u591a\u7d22\u5f15\uff0c\u67e5\u8be2\u66f4\u5feb\uff1b<br \/>\n\u5176\u6b21\uff0c\u7531\u4e8eMVCC\u673a\u5236\uff0cInnoDB\u67e5\u8be2\u65f6\u9700\u8981\u521b\u5efa\u8bfb\u89c6\u56fe\u8fdb\u884c\u53ef\u89c1\u6027\u5224\u65ad\uff0c\u5fc5\u8981\u65f6\u8fd8\u5f97\u53bb\u67e5\u627eundo log\u5bfb\u627e\u5386\u53f2\u7248\u672c\uff0c\u81ea\u7136\u5c31\u66f4\u6162\u3002<\/p>\n<h1>15. MySQL Binary Log \u4e0e\u4e3b\u4ece\u590d\u5236\u3001\u8bfb\u5199\u5206\u79bb<\/h1>\n<h2>15.1 Bin log \u5b9a\u4e49\u4e0e\u8bb0\u5f55\u6a21\u5f0f<\/h2>\n<p>\u4e8c\u8fdb\u5236\u65e5\u5fd7\u662fMySQL Server\u5c42\u81ea\u5e26\u7684\u65e5\u5fd7\u673a\u5236\uff0c\u4e0d\u662fredo log\u548cundo log\u90a3\u6837\u7684InnoDB\u5728\u5f15\u64ce\u5c42\u5b9e\u73b0\u7684\u65e5\u5fd7\u673a\u5236\u3002Bin log\u540d\u79f0\u4e2d\u7684\u201c\u4e8c\u8fdb\u5236\u201d\u6307\u7684\u662f\u5176\u65e5\u5fd7\u6587\u4ef6\u7684\u5b58\u50a8\u65b9\u5f0f\u662f\u4e8c\u8fdb\u5236\u6587\u4ef6\uff08\u6545\u4e0d\u80fd\u901a\u8fc7cat\u7b49\u547d\u4ee4\u67e5\u770b\uff0c\u5fc5\u987b\u901a\u8fc7MySQL\u63d0\u4f9b\u7684\u89e3\u6790\u5de5\u5177mysqlbinlog\u67e5\u770b\uff09\uff0c\u800c\u5728\u7269\u7406\/\u903b\u8f91\u65e5\u5fd7\u7684\u5206\u7c7b\u4e0a\uff0cbin log<strong>\u5c5e\u4e8e\u903b\u8f91\u65e5\u5fd7<\/strong>\u3002Bin log\u5177\u6709\u4e09\u79cd\u8bb0\u5f55\u6a21\u5f0f\uff0c\u5bf9\u5e94\u53c2\u6570<code>binlog_format<\/code>\uff0c\u5206\u522b\u662f\uff1a<\/p>\n<ul>\n<li>STATEMENT\uff1a5.1\u7248\u672c\u4ee5\u524d\u4ec5\u6709\u7684\u6a21\u5f0f\uff0c\u76f4\u63a5\u8bb0\u5f55\u5bf9\u6570\u636e\u5e93\u6267\u884c\u8fc7\u4fee\u6539\u7684SQL\u8bed\u53e5\uff1b<\/li>\n<li>ROW\uff1a\u9010\u884c\u8bb0\u5f55\u884c\u6570\u636e\u7684\u66f4\u6539\u60c5\u51b5\uff0c\u6b64\u6027\u8d28\u5bfc\u81f4\u5176\u7a7a\u95f4\u6d88\u8017\u5f80\u5f80\u8fdc\u5927\u4e8eSTATEMENT\uff1b<\/li>\n<li>MIXED\uff1a\u9ed8\u8ba4\u91c7\u7528STATEMENT\uff0c\u5bf9\u4e8eSTATEMENT\u4e0d\u80fd\u8bb0\u5f55\u7684\u5219\u6362\u6210ROW<\/li>\n<\/ul>\n<p>Bin log\u4f1a\u8bb0\u5f55\u6240\u6709\u5bf9\u6570\u636e\u5e93<strong>\u6267\u884c\u66f4\u6539\u7684\u64cd\u4f5c<\/strong>\uff0c\u8fd9\u610f\u5473\u7740select\u3001show\u7b49\u64cd\u4f5c\u80af\u5b9a\u4e0d\u4f1a\u88ab\u8bb0\u5f55\uff0c\u800c\u4e00\u4e9b\u6ca1\u6709\u5b9e\u9645\u5f71\u54cd\u7684update\u64cd\u4f5c\u2014\u2014\u4f8b\u5982\u5bf9\u4e0d\u5b58\u5728\u7684\u6570\u636e\u8fdb\u884cupdate\u2014\u2014\u5219\u4ecd\u6709\u53ef\u80fd\u88ab\u8bb0\u5f55\u3002<br \/>\nBin log\u5728\u4e8b\u52a1\u63d0\u4ea4\u65f6\u4e00\u6b21\u6027\u5199\u5165\u78c1\u76d8\u4e2d\u7684\u8bb0\u5f55\u6587\u4ef6\u3002<br \/>\nBin log\u7684\u4e3b\u8981\u4f5c\u7528\u662f\u7528\u4e8e\u6570\u636e\u6062\u590d\u548c\u4e3b\u4ece\u590d\u5236\u3002<\/p>\n<h2>15.2 \u4e3b\u4ece\u590d\u5236<\/h2>\n<p>\u4e3b\u4ece\u590d\u5236(Replication)\u662f\u7528\u6765\u5efa\u7acb\u4e00\u4e2a\u4e0e\u4e3b\u6570\u636e\u5e93\u5b8c\u5168\u4e00\u6837\u7684\u6570\u636e\u5e93\u73af\u5883\uff0c\u5373\u4ece\u6570\u636e\u5e93\u3002\u4e3b\u4ece\u590d\u5236\u6709\u4e24\u4e2a\u4e3b\u8981\u7528\u9014\uff1a<\/p>\n<ol>\n<li>\u8bfb\u5199\u5206\u79bb\uff0c\u4e3b\u8282\u70b9\u8d1f\u8d23\u5199\uff0c\u4ece\u8282\u70b9\u8d1f\u8d23\u8bfb\uff0c\u4ece\u800c\u63d0\u9ad8\u5e76\u53d1\u6027\u80fd\u3002<\/li>\n<li>\u9ad8\u53ef\u7528\uff0c\u4ece\u6570\u636e\u5e93\u53ef\u4ee5\u4f5c\u4e3a\u540e\u5907\uff0c\u5f53\u4e3b\u6570\u636e\u5e93\u6545\u969c\u65f6\uff0c\u53ef\u4ee5\u5207\u6362\u5230\u4ece\u6570\u636e\u5e93\u7ee7\u7eed\u5de5\u4f5c<\/li>\n<\/ol>\n<p>MySQL\u81ea\u5e26\u4e3b\u4ece\u590d\u5236\u529f\u80fd\uff0c\u4f9d\u8d56bin log\u673a\u5236\u5b9e\u73b0\u3002\u5176\u6b65\u9aa4\u5982\u4e0b(\u91cd\u70b9\u8bb0\u5fc6\u4e09\u4e2a\u7ebf\u7a0b)\uff1a<\/p>\n<ol>\n<li>\u4e3b\u8282\u70b9\u8fdb\u884cinsert\u3001update\u3001delete\u64cd\u4f5c\u65f6\uff0c\u6309\u987a\u5e8f\u5199\u5165binlog\u3002<\/li>\n<li>\u4ece\u8282\u70b9\u4ece\u5e93\u8fde\u63a5\u4e3b\u8282\u70b9\u4e3b\u5e93\uff0c\u4e3b\u8282\u70b9\u5bf9\u6bcf\u4e2aSlave\u5206\u522b\u521b\u5efa<strong>binlog dump\u7ebf\u7a0b<\/strong>\uff0cSlave\u5efa\u7acb<strong>IO\u7ebf\u7a0b<\/strong>\u4e0e\u4e3b\u8282\u70b9\u4fdd\u6301\u901a\u4fe1\uff0c\u5e76\u5efa\u7acb<strong>SQL\u7ebf\u7a0b<\/strong>\u51c6\u5907\u6267\u884c\u4e3b\u8282\u70b9\u63a8\u9001\u6765\u7684\u64cd\u4f5c\u3002<\/li>\n<li>\u5f53\u4e3b\u8282\u70b9\u7684binlog\u53d1\u751f\u53d8\u5316\u65f6\uff0cbinlog dump\u7ebf\u7a0b\u4f1a\u901a\u77e5\u6240\u6709\u7684\u4ece\u8282\u70b9\uff0c\u5e76\u5c06\u76f8\u5e94\u7684binlog\u5185\u5bb9\u63a8\u9001\u7ed9Slave\u8282\u70b9\u3002<\/li>\n<li>I\/O\u7ebf\u7a0b\u63a5\u6536\u5230binlog\u5185\u5bb9\u540e\uff0c\u5c06\u5185\u5bb9\u5199\u5165\u5230\u672c\u5730\u7684relay log\u3002<\/li>\n<li>SQL\u7ebf\u7a0b\u8bfb\u53d6I\/O\u7ebf\u7a0b\u5199\u5165\u7684relay log\uff0c\u5e76\u4e14\u6839\u636erelay log\u7684\u5185\u5bb9\u5bf9\u4ece\u6570\u636e\u5e93\u505a\u5bf9\u5e94\u7684\u64cd\u4f5c\u3002<\/li>\n<\/ol>\n<p>relay log\uff0c\u5373\u4e2d\u7ee7\u65e5\u5fd7\uff0c\u7528\u4e8e\u5b58\u653e\u4e3b\u8282\u70b9dump\u8fc7\u6765\u7684\u65e5\u5fd7\uff0c\u7b49\u5f85\u4ece\u8282\u70b9\u7684sql\u7ebf\u7a0b\u8bfb\u53d6\u5e76\u6267\u884c\u3002relay log\u7684\u683c\u5f0f\u4e0ebin log\u7684\u5b8c\u5168\u4e00\u81f4\uff0c\u751a\u81f3\u53ef\u4ee5\u901a\u8fc7bin log\u7684\u89e3\u6790\u5de5\u5177mysqlbinlog\u6765\u89e3\u6790\u3002\u4ece\u8282\u70b9\u7684SQL\u7ebf\u7a0b\u6267\u884c\u5b8c\u4e00\u4e2arelay log\u6587\u4ef6\u4e2d\u7684\u6240\u6709\u5185\u5bb9\u540e\u4fbf\u4f1a\u81ea\u52a8\u5220\u9664\u8be5\u6587\u4ef6\uff0c\u6545MySQL\u4e2d\u6ca1\u6709\u8bbe\u8ba1\u663e\u5f0f\u5220\u9664relay log\u6587\u4ef6\u7684\u673a\u5236\u3002<\/p>\n<h2>15.3 \u8bfb\u5199\u5206\u79bb<\/h2>\n<p>MySQL\u81ea\u5e26\u7684\u4e3b\u4ece\u590d\u5236\u673a\u5236\u53ea\u4fdd\u8bc1\u4e3b\u8282\u70b9\u5bf9\u5916\u63d0\u4f9b\u670d\u52a1\uff0c\u800c\u4ece\u8282\u70b9\u4ec5\u4f5c\u4e3a\u6570\u636e\u5907\u4efd\uff0c\u4e0d\u5bf9\u5916\u63d0\u4f9b\u670d\u52a1\u3002\u6545\u5b9e\u73b0\u8bfb\u5199\u5206\u79bb\u9700\u8981\u501f\u52a9\u4e00\u4e9b\u4e2d\u95f4\u4ef6\uff0c\u4e14\u662f\u5efa\u7acb\u5728\u4e3b\u4ece\u590d\u5236\u7684\u57fa\u7840\u4e0a\u7684\u3002<\/p>\n<p>\u7531\u4e8e\u7f51\u7edc\u5ef6\u8fdf\uff0c\u4ece\u8282\u70b9\u7684\u6570\u636e\u5e76\u4e0d\u662f\u6700\u65b0\u7684\uff0c\u6545\u8bfb\u5199\u5206\u79bb\u7684\u6846\u67b6\u5f80\u5f80\u4f1a\u4fdd\u8bc1\u540c\u4e00\u7ebf\u7a0b\u7684\u540c\u4e00\u6b21\u6570\u636e\u5e93\u8fde\u63a5\u5185\uff0c\u82e5\u5b58\u5728\u5199\u5165\u64cd\u4f5c\uff0c\u5219\u4ee5\u540e\u7684\u8bfb\u64cd\u4f5c\u90fd\u4ece\u4e3b\u8282\u70b9\u8bfb\u53d6\uff0c\u4ece\u800c\u4fdd\u8bc1\u6570\u636e\u4e00\u81f4\u6027\u3002<\/p>\n<h2>\u53c2\u8003<\/h2>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replica-logs-relaylog.html\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replica-logs-relaylog.html<\/a><br \/>\n<a href=\"https:\/\/blog.csdn.net\/cwb521sxm\/article\/details\/97303289\">https:\/\/blog.csdn.net\/cwb521sxm\/article\/details\/97303289<\/a><br \/>\n<a href=\"https:\/\/zhuanlan.zhihu.com\/p\/68035302\">https:\/\/zhuanlan.zhihu.com\/p\/68035302<\/a><br \/>\n<a href=\"https:\/\/segmentfault.com\/a\/1190000023775512\">https:\/\/segmentfault.com\/a\/1190000023775512<\/a><\/p>\n<h1>16. InnoDB Redo Log<\/h1>\n<h2>16.1 Redo Log\u7684\u5b9a\u4e49\u4e0e\u6027\u8d28<\/h2>\n<p>\u91cd\u505a\u65e5\u5fd7\uff0cInnoDB\u5f15\u64ce\u5c42\u7684\u673a\u5236\uff0c\u7528\u4e8e\u6570\u636e\u5e93\u5f02\u5e38\u5d29\u6e83\u7684\u6062\u590d\uff0c\u9632\u6b62\u5728\u6570\u636e\u5e93\u5d29\u6e83\u65f6\u5c1a\u6709\u810f\u9875\u672a\u5199\u5165\u78c1\u76d8\u3002Redo log\u4fdd\u8bc1\u4e86\u4e8b\u52a1ACID\u6027\u8d28\u4e2d\u7684D\uff0c\u5373\u6301\u4e45\u6027\u3002<br \/>\n\u662f<strong>\u7269\u7406\u65e5\u5fd7<\/strong>\uff0c\u76f4\u63a5\u8bb0\u5f55\u6570\u636e\u9875\u7684\u4fee\u6539\uff0c\u5176\u8bb0\u5f55\u5185\u5bb9\u53ef\u4ee5\u5927\u81f4\u7406\u89e3\u4e3a[\u9875\u53f7-\u504f\u79fb\u91cf-\u4fee\u6539\u540e\u7684\u503c]\u3002<br \/>\nRedo Log\u5305\u62ec\u4e24\u90e8\u5206\uff1a\u4e00\u4e2a\u662f\u5185\u5b58\u4e2d\u7684\u65e5\u5fd7\u7f13\u5b58(Redo Log buffer)\uff0c\u53e6\u4e00\u4e2a\u662f\u78c1\u76d8\u4e0a\u7684\u65e5\u5fd7\u6587\u4ef6(Redo Log<br \/>\nfile)\u3002Redo log file\u6587\u4ef6\u5927\u5c0f\u56fa\u5b9a\uff0c\u7531\u4e00\u7ec4\u65e5\u5fd7\u6587\u4ef6\u7ec4\u6210\uff0c\u4e14\u91c7\u7528\u5faa\u73af\u5199\u7684\u65b9\u5f0f\uff0c\u5f53\u5199\u5230\u7ed3\u5c3e\u65f6\u4f1a\u56de\u5230\u5f00\u5934\u8986\u76d6\u5199\uff0c\u4e0b\u56fe\u5c55\u793a\u4e86\u4e00\u4e2a\u75314\u4e2a\u6587\u4ef6\u7ec4\u6210\u7684redo log file\uff1a<br \/>\n<div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/image-static.segmentfault.com\/390\/444\/3904443652-cc3225d69e1d0476_fix732'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  decoding=\"async\" data-original=\"https:\/\/image-static.segmentfault.com\/390\/444\/3904443652-cc3225d69e1d0476_fix732\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"Redo log\u7684\u5faa\u73af\u5199\u5165\" \/><\/div><\/p>\n<p>\u56fe\u4e2d\uff0c<code>write pos<\/code>\u662f\u4e0b\u4e00\u6761redo log\u5c06\u8981\u5199\u5165\u7684\u4f4d\u7f6e\uff0c\u4e00\u8fb9\u5199\u4e00\u8fb9\u540e\u79fb\uff1b<code>checkpoint<\/code>\u5219\u662f\u4e0b\u4e00\u6761\u9700\u8981\u5237\u65b0\u5230\u6570\u636e\u6587\u4ef6\u7684redo log\u7684\u4f4d\u7f6e\uff0c\u4e5f\u662f\u4e00\u8fb9\u5237\u65b0\u4e00\u904d\u540e\u79fb\u3002\u4ece<code>write pos<\/code>\u5f00\u59cb\u5230<code>checkpoint<\/code>\u4e4b\u95f4\u7684\u90e8\u5206\u5c31\u662fredo log\u4e0a\u5269\u4f59\u7684\u7a7a\u95f4\uff0c\u4ece<code>checkpoint<\/code>\u5f00\u59cb\u5230<code>write pos<\/code>\u4e4b\u95f4\u7684\u90e8\u5206\u5c31\u662f\u5c1a\u672a\u5237\u65b0\u5165\u78c1\u76d8\u4e0aredo log file\u7684\u65e5\u5fd7\u3002\u5982\u679c<code>write pos<\/code>\u8ffd\u4e0a\u4e86<code>checkpoint<\/code>\uff0c\u90a3\u4e48redo log\u5c31\u88ab\u5199\u6ee1\u4e86\uff0c\u6b64\u65f6\u4e0d\u53ef\u518d\u6267\u884c\u65b0\u7684\u6570\u636e\u66f4\u65b0\u64cd\u4f5c\uff0c\u5fc5\u987b\u7acb\u523b\u6267\u884credo log\u6570\u636e\u9875\u7684\u5237\u65b0\u3002<\/p>\n<h2>16.2 Redo Log\u7684\u5de5\u4f5c\u6d41\u7a0b\uff1aWAL<\/h2>\n<p>\u78c1\u76d8IO\u662f\u975e\u5e38\u6162\u7684\uff0c\u5018\u82e5\u6bcf\u6b21\u66f4\u65b0\u7684\u6570\u636e\u90fd\u5fc5\u987b\u7acb\u523b\u843d\u76d8\uff0c\u6570\u636e\u5e93\u6027\u80fd\u4f1a\u6781\u5176\u4f4e\u4e0b\uff0c\u8ba9\u4eba\u65e0\u6cd5\u63a5\u53d7\u3002\u4e3a\u4e86\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898\uff0c\u6bcf\u6b21\u8fdb\u884c\u6570\u636e\u66f4\u65b0\u65f6\uff0cInnoDB\u90fd\u4e0d\u4f1a\u7acb\u523b\u5c06\u4fee\u6539\u540e\u7684\u6570\u636e\u9875\u5199\u56de\u78c1\u76d8\uff0c\u800c\u662f\u5148\u5c06\u8fd9\u4e9b\u810f\u9875\u90fd\u5b58\u5165\u5185\u5b58\u4e2d\u7684\u7f13\u5b58\u6c60(Buffer Pool)\uff0c\u8fc7\u540e\u7531InnoDB\u7684Master thread\u5b9a\u65f6\u6267\u884c\u810f\u9875\u7684\u5237\u65b0\u843d\u76d8\uff0c\u901a\u5e38\u662f\u6bcf\u79d2\u4e00\u6b21\uff0c\u4e00\u6b21\u6700\u591a\u5199100\u4e2a\u810f\u9875\u3002<\/p>\n<p>Redo log\u4e5f\u91c7\u7528\u4e86\u540c\u6837\u7684\u5148\u7f13\u5b58\u518d\u5b9a\u671f\u843d\u76d8\u7684\u601d\u60f3\u4ee5\u51cf\u5c11\u78c1\u76d8IO\u3002\u6bcf\u6761update\u8bed\u53e5\u6267\u884c\u4ece\u800c\u4ea7\u751fredo log\u65f6\uff0c\u5148\u5199\u5165\u5185\u5b58\u4e2d\u7684redo log buffer\uff0c\u8fc7\u540e\u7531InnoDB\u7684Master thread\u5b9a\u65f6\u6267\u884c\u65e5\u5fd7\u7684\u5237\u65b0\uff0c\u5c06buffer\u4e2d\u7684\u65e5\u5fd7\u5237\u65b0\u5230redo log file\u5e76\u79fb\u52a8<code>checkpoint<\/code>\u3002\u5728Master thread\u7684\u6bcf\u79d2\u5b9a\u65f6\u64cd\u4f5c\u4e2d\uff0c\u603b\u662f\u5148\u5237\u65b0redo log\uff0c\u518d\u5c1d\u8bd5\u5237\u65b0\u7f13\u5b58\u6c60\u4e2d\u7684\u810f\u9875\u3002\u8fd9\u79cd\u201c\u5148\u5199\u65e5\u5fd7\uff0c\u8fc7\u4e00\u6bb5\u65f6\u95f4\u518d\u5199\u5165\u78c1\u76d8\u201d\u7684\u6280\u672f\u5c31\u662fMySQL\u91cc\u7ecf\u5e38\u8bf4\u5230\u7684WAL(Write-Ahead Logging)\u6280\u672f\u3002<\/p>\n<p>\u4ee5\u4e8b\u52a1\u4e2d\u4e00\u6b21update\u8bed\u53e5\u7684\u6267\u884c\u4e3a\u4f8b\uff0cRedo Log\u5de5\u4f5c\u7684\u8fc7\u7a0b\u5982\u4e0b\uff1a<\/p>\n<ol>\n<li>\u5148\u5c06\u539f\u59cb\u6570\u636e\u4ece\u78c1\u76d8\u4e2d\u8bfb\u5165\u5185\u5b58\u7f13\u5b58\uff0c\u4fee\u6539\u6570\u636e\u7684\u5185\u5b58\u62f7\u8d1d<\/li>\n<li>\u751f\u6210\u4e00\u6761\u91cd\u505a\u65e5\u5fd7\u5e76\u5199\u5165Redo Log buffer\uff0c\u8bb0\u5f55\u7684\u662f\u6570\u636e\u88ab\u4fee\u6539\u540e\u7684\u503c<\/li>\n<li>\u5728\u4e09\u79cd\u60c5\u51b5\u4e0b\uff0c\u5c06Redo Log buffer\u4e2d\u7684\u5185\u5bb9\u91c7\u7528\u8ffd\u52a0\u5199\u7684\u65b9\u5f0f\u5237\u65b0\u5230Redo Log file\u3002\u8fd9\u4e09\u79cd\u60c5\u51b5\u5206\u522b\u662f\uff1a\n<ol>\n<li>Master Thread\u6bcf\u79d2\u56fa\u5b9a\u6267\u884credo log buffer\u7684\u5237\u65b0<\/li>\n<li>\u4e8b\u52a1\u63d0\u4ea4\u65f6 *\uff08\u6839\u636e\u7528\u6237\u8bbe\u7f6e\u7684\u53c2\u6570\uff0c\u4e8b\u52a1\u63d0\u4ea4\u65f6\u53ef\u80fd\u5e76\u4e0d\u4f1a\u6267\u884c\uff0c\u8be6\u89c1\u540e\u6587\uff09<\/li>\n<li>redo log buffer \u5269\u4f59\u7a7a\u95f4\u4e0d\u8db3\u4e00\u534a\u65f6<\/li>\n<\/ol>\n<\/li>\n<li>\u5b9a\u671f\u5c06\u5185\u5b58\u4e2d\u4fee\u6539\u7684\u6570\u636e\u5237\u65b0\u5230\u78c1\u76d8\u4e2d<\/li>\n<\/ol>\n<p>\u5728\u4e00\u4e2a\u4e8b\u52a1\u7684\u6267\u884c\u4e2d\uff0c\u6b65\u9aa41\u30012\u5728\u6bcf\u6761update\u8bed\u53e5\u8fd0\u884c\u65f6\u90fd\u8981\u6267\u884c\uff0c\u6b64\u5916\u6b65\u9aa43\u4e5f\u56fa\u5b9a\u4f1a\u5728\u6bcf\u79d2\u88abMaster Thread\u6267\u884c\uff0c\u6545\u5728\u4e8b\u52a1\u6267\u884c\u8fc7\u7a0b\u4e2dredo log\u5c31\u5df2\u7ecf\u9010\u6b65\u5199\u5165\u78c1\u76d8\uff0c\u5e76\u4e0d\u662f\u5728\u4e8b\u52a1\u63d0\u4ea4\u65f6\u4e00\u6b21\u6027\u5199\u5165\u6240\u6709\u5185\u5bb9\u3002<\/p>\n<h2>16.3 Redo log file\u843d\u76d8\u7684\u8be6\u7ec6\u8bf4\u660e<\/h2>\n<p>16.2\u8282\u6b65\u9aa43\u7684\u201c\u5c06buffer\u4e2d\u7684\u65e5\u5fd7\u5237\u65b0\u5230\u78c1\u76d8\u4e2d\u7684\u65e5\u5fd7\u6587\u4ef6\u201d\u5b9e\u9645\u4e0a\u4e5f\u5206\u4e3a\u4e24\u6b65\u8fdb\u884c\uff1a\u9996\u5148\uff0c\u5c06\u5185\u5b58buffer\u4e2d\u7684\u5185\u5bb9\u5199\u5165\u64cd\u4f5c\u7cfb\u7edf\u5185\u6838\u4e2d\u7684\u6587\u4ef6\u7cfb\u7edf\u7f13\u5b58\uff08\u4e0b\u6587\u7b80\u79f0os buffer\uff09\uff1b\u5176\u6b21\uff0c\u4f7f\u7528\u7cfb\u7edf\u8c03\u7528<code>fsync()<\/code>\u5c06\u6587\u4ef6\u7cfb\u7edf\u7f13\u5b58\u7684\u6570\u636e\u4ee5\u8ffd\u52a0\u7684\u65b9\u5f0f\u5199\u5165\u78c1\u76d8\u4e0a\u7684\u6587\u4ef6\u3002<\/p>\n<p>\u9700\u8981\u6ce8\u610f\u7684\u662f\uff0c\u7cfb\u7edf\u8c03\u7528<code>fsync()<\/code>\u7684\u6267\u884c\u901f\u5ea6\u53d7\u5230\u78c1\u76d8IO\u6027\u80fd\u7684\u9650\u5236\uff0c\u4e14\u76f4\u5230\u5199\u76d8\u64cd\u4f5c\u5b8c\u6210\u524d\u90fd\u5c06\u5904\u4e8e\u7b49\u5f85\u72b6\u6001\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u6bcf\u6b21\u4e8b\u52a1\u63d0\u4ea4\u540e\u90fd\u4f1a\u6267\u884c\u4e00\u6b21\u5f80os buffer\u7684\u5199\u5165\u548c<code>fsync()<\/code>\uff0c\u786e\u4fdd\u672c\u6b21\u4e8b\u52a1\u7684redo log\u88ab\u5199\u5165\u78c1\u76d8\u4e0a\u7684redo log file\uff0c\u800c\u8fd9\u4f1a\u5e26\u6765\u4e00\u4e2a\u6027\u80fd\u9690\u60a3\uff1a\u82e5\u5728\u77ed\u671f\u5185\u51fa\u73b0\u5927\u91cf\u4e8b\u52a1\u63d0\u4ea4\uff0c\u9ed8\u8ba4\u8bbe\u7f6e\u4e0b\u7684redo log\u5237\u65b0\u673a\u5236\u4f1a\u5bfc\u81f4\u5927\u91cf<code>fsync()<\/code>\u53d1\u751f\uff0c\u5e26\u6765\u7684\u65f6\u95f4\u6210\u672c\u5f80\u5f80\u662f\u4e0d\u53ef\u63a5\u53d7\u7684\u3002\u300aMySQL\u6280\u672f\u5185\u5e55\uff1aInnoDB\u5b58\u50a8\u5f15\u64ce\u300b\u4e0a\u4ecb\u7ecd\u4e86\u4e00\u4e2a\u5b9e\u9a8c\uff0c\u8bbe\u7f6e\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\u7528\u6765\u63d2\u5165\u4e00\u884c\u6570\u636e\uff0c\u7136\u540e\u4f7f\u7528CALL\u547d\u4ee4\u6267\u884c\u8be5\u8fc7\u7a0b500000\u6b21\uff0c\u4e00\u5171\u82b1\u8d392\u5206\u949f\u624d\u5b8c\u6210500000\u884c\u6570\u636e\u7684\u63d2\u5165\u3002\u8bda\u7136\uff0c\u752850\u4e07\u6b21\u4e8b\u52a1\u63d0\u4ea4\u6765\u63d2\u516550\u4e07\u884c\u6570\u636e\u5f88\u4e0d\u5408\u7406\uff0c\u4f46\u662f\u8be5\u5b9e\u9a8c\u8db3\u4ee5\u8bf4\u660e\u9ad8\u8bbf\u95ee\u4e0b<code>fsync()<\/code>\u6709\u53ef\u80fd\u6210\u4e3a\u6027\u80fd\u74f6\u9888\u3002\u4e3a\u4e86\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898\uff0cInnoDB\u63d0\u4f9b\u4e86\u53c2\u6570<code>innodb_flush_log_at_trx_commit<\/code>\uff0c\u53ef\u4ee5\u901a\u8fc7\u4fee\u6539\u8be5\u53c2\u6570\u6765\u8c03\u6574redo log\u5728\u4e8b\u52a1\u63d0\u4ea4\u65f6\u8c03\u7528<code>fsync()<\/code>\u7684\u884c\u4e3a\u3002<\/p>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u53c2\u6570\u503c<\/th>\n<th style=\"text-align: center;\">\u542b\u4e49<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">0\uff08\u5ef6\u8fdf\u5199\uff09<\/td>\n<td style=\"text-align: center;\">\u4e8b\u52a1\u63d0\u4ea4\u65f6\u4e0d\u518d\u8fdb\u884credo log\u7684\u5237\u65b0\u64cd\u4f5c\uff0c\u5b8c\u5168\u4ea4\u7531Master Thread\u6bcf\u79d2\u5199\u5165os buffer\u5e76\u8c03\u7528<code>fsync()<\/code>\u5199\u5165\u5230redo log file\u4e2d\u3002\u82e5MySQL\u5d29\u6e83\uff0c\u4f1a\u4e22\u5931\u7ea61\u79d2\u949f\u7684\u6570\u636e\u3002<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">1\uff08\u9ed8\u8ba4\uff0c\u5b9e\u65f6\u5199\uff0c\u5b9e\u65f6\u5237\uff09<\/td>\n<td style=\"text-align: center;\">\u4e8b\u52a1\u6bcf\u6b21\u63d0\u4ea4\u90fd\u4f1a\u5c06redo log buffer\u4e2d\u7684\u65e5\u5fd7\u5199\u5165os buffer\u5e76\u8c03\u7528<code>fsync()<\/code>\u5237\u65b0\u5230redo log file\u4e2d\u3002\u5373\u4f7fMySQL\u5d29\u6e83\u4e5f\u4e0d\u4f1a\u4e22\u5931\u4efb\u4f55\u6570\u636e\uff0c\u4f46\u662f\u4e8b\u52a1\u7684\u63d0\u4ea4\u6027\u80fd\u8f83\u5dee\uff0c\u76f4\u63a5\u5f71\u54cd\u6570\u636e\u5e93\u7684\u6027\u80fd\u3002<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">2\uff08\u5b9e\u65f6\u5199\uff0c\u5ef6\u8fdf\u5237\uff09<\/td>\n<td style=\"text-align: center;\">\u4e8b\u52a1\u6bcf\u6b21\u63d0\u4ea4\u65f6\u4ec5\u5c06redo log\u5199\u5165os buffer\uff0c\u4e0d\u4e3b\u52a8\u8fdb\u884c<code>fsync()<\/code>\uff0c\u7531\u6587\u4ef6\u7cfb\u7edf\u5185\u90e8\u7684\u5b9a\u65f6\u673a\u5236\u81ea\u884c\u6267\u884c<code>fsync()<\/code>\u3002\u6b64\u8bbe\u7f6e\u4e0b\u53ea\u8981OS\u4e0d\u5d29\u6e83\uff0c\u7406\u8bba\u4e0a\u6765\u8bf4\u65e5\u5fd7\u6570\u636e\u4e5f\u662f\u5b89\u5168\u7684\uff0c\u53ea\u662f\u843d\u76d8\u7684\u65f6\u70b9\u5b8c\u5168\u4ea4\u7531OS\u5904\u7406\uff0c\u4e0d\u53ef\u63a7\u5236<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>16.4 \u57fa\u4e8eRedo log\u7684\u6570\u636e\u6062\u590d\u6d41\u7a0b<\/h2>\n<p>Log Sequence Number(LSN)\uff0c\u65e5\u5fd7\u5e8f\u5217\u53f7\uff0c\u8868\u793aredo log\u5199\u5165\u7684\u603b\u91cf\uff0c\u5355\u4f4d\u662f\u5b57\u8282\u3002\u4f8b\u5982\uff0c\u5f53\u524dredo log\u7684LSN\u662f1000\uff0c\u4e00\u4e2a\u65b0\u7684\u4e8b\u52a1\u5199\u5165\u4e86100\u5b57\u8282\u7684redo log\u540e\uff0cLSN\u5c31\u53d8\u4e3a1100\u3002<br \/>\n\u6bcf\u6b21\u542f\u52a8InnoDB\u65f6\uff0c\u65e0\u8bba\u4e0a\u6b21\u662f\u5426\u5f02\u5e38\u5173\u95ed\uff0c\u90fd\u4f1a\u68c0\u67e5redo log\u5e76\u5c1d\u8bd5\u8fdb\u884c\u6062\u590d\u64cd\u4f5c\u3002\u4ece<code>checkpoint<\/code>\u5f00\u59cb\u5230LSN\u4e3a\u6b62\u7684redo log\u5c31\u662f\u9700\u8981\u5237\u65b0\u5230\u6570\u636e\u9875\u4e0a\u8fdb\u884c\u6062\u590d\u7684\u5185\u5bb9\u3002<br \/>\n\u6b64\u5916\uff0c\u9664\u4e86redo log\u672c\u8eab\u4f1a\u4fdd\u5b58LSN\uff0c\u5728\u6bcf\u4e2a\u6570\u636e\u9875\u7684\u5934\u90e8\u4e5f\u90fd\u6709\u4e00\u4e2a\u503c<code>FIL_PAGE_LSN<\/code>\u8bb0\u5f55\u8be5\u9875\u7684LSN\uff0c\u5373\u8be5\u9875\u6700\u540e\u4e00\u6b21\u5237\u65b0\u65f6LSN\u7684\u503c\u3002\u901a\u8fc7\u6bd4\u8f83\u6570\u636e\u9875\u7684LSN\u548credo log\u7684LSN\uff0c\u5c31\u53ef\u4ee5\u51b3\u5b9a\u4e00\u4e2a\u6570\u636e\u9875\u662f\u5426\u9700\u8981\u8fdb\u884c\u6062\u590d\u3002<\/p>\n<h2>16.5 Redo log\u548cBin log\u7684\u5bf9\u6bd4<\/h2>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u533a\u522b\u9879<\/th>\n<th style=\"text-align: center;\">Redo log<\/th>\n<th style=\"text-align: center;\">Bin log<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u7269\u7406\/\u903b\u8f91\u5206\u7c7b<\/td>\n<td style=\"text-align: center;\">\u7269\u7406\u65e5\u5fd7<\/td>\n<td style=\"text-align: center;\">\u903b\u8f91\u65e5\u5fd7<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u78c1\u76d8\u65e5\u5fd7\u6587\u4ef6\u7684\u8bb0\u5f55\u65b9\u5f0f\u4e0e\u6587\u4ef6\u5927\u5c0f<\/td>\n<td style=\"text-align: center;\">\u7531\u4e00\u7ec4\u6587\u4ef6\u7ec4\u6210\u7684\u603b\u5927\u5c0f\u56fa\u5b9a\u7684\u8bb0\u5f55\u6587\u4ef6\uff0c\u91c7\u7528\u5faa\u73af\u5199\u7684\u65b9\u5f0f<\/td>\n<td style=\"text-align: center;\">\u4e00\u4e2a\u6587\u4ef6\u5199\u6ee1\u4ee5\u540e\u521b\u5efa\u65b0\u7684\u6587\u4ef6\u5199\u540e\u7eed\u5185\u5bb9\uff0c\u91c7\u7528\u8ffd\u52a0\u5199\u7684\u65b9\u5f0f<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u5b9e\u73b0\u5c42\u7ea7<\/td>\n<td style=\"text-align: center;\">\u7531InnoDB\u5f15\u64ce\u5c42\u5b9e\u73b0\uff0c\u5b58\u50a8\u5f15\u64ce\u8d1f\u8d23\u5199\uff0c\u5e76\u4e0d\u662f\u6240\u6709\u5f15\u64ce\u90fd\u6709<\/td>\n<td style=\"text-align: center;\">\u7531MySQL Server\u5c42\u5b9e\u73b0\uff0c\u6267\u884c\u5668\u8d1f\u8d23\u5199\uff0c\u6240\u6709\u5f15\u64ce\u90fd\u53ef\u4ee5\u4f7f\u7528<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u7528\u9014<\/td>\n<td style=\"text-align: center;\">\u7528\u4e8e\u5f02\u5e38\u5d29\u6e83\u7684\u6062\u590d(crash-safe)<\/td>\n<td style=\"text-align: center;\">\u7528\u4e8e\u4e3b\u4ece\u590d\u5236\u548c\u6570\u636e\u6062\u590d<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u4fdd\u62a4\u5bf9\u8c61<\/td>\n<td style=\"text-align: center;\">\u9488\u5bf9\u4e8b\u52a1\uff0c\u4fdd\u8bc1\u4e8b\u52a1\u7684\u6301\u4e45\u6027<\/td>\n<td style=\"text-align: center;\">\u9488\u5bf9\u6570\u636e\u5e93\uff0c\u4fdd\u62a4\u6570\u636e\u5e93<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u5199\u5165\u65f6\u95f4<\/td>\n<td style=\"text-align: center;\">\u5728\u4e8b\u52a1\u8fdb\u884c\u4e2d\u4e0d\u65ad\u5730\u88ab\u5199\u5165<\/td>\n<td style=\"text-align: center;\">\u4e8b\u52a1\u63d0\u4ea4\u540e\u4e00\u6b21\u6027\u5199\u5165<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u5e42\u7b49\u6027<\/td>\n<td style=\"text-align: center;\">\u5e42\u7b49<\/td>\n<td style=\"text-align: center;\">\u4e0d\u5e42\u7b49<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>16.6 \u4e3a\u4ec0\u4e48\u53ea\u9760bin log\u4e0d\u80fd\u505a\u5230\u5d29\u6e83\u6062\u590d(crash-safe)\uff1f<\/h2>\n<p>\u5047\u8bbe\u6ca1\u6709redo log\u673a\u5236\uff0cInnoDB\u5728\u64cd\u4f5c\u6570\u636e\u65f6\u8fd8\u662f\u5c06\u6570\u636e\u62f7\u8d1d\u5230\u5185\u5b58\u4e2d\u7684\u7f13\u51b2\u6c60(Buffer Pool)\u8fdb\u884c\u4fdd\u5b58\u548c\u6539\u5199\uff0c\u968f\u540e\u5b9a\u671f\u7531Master thread\u5237\u65b0\u843d\u76d8\u3002\u5728\u8fd9\u79cd\u60c5\u51b5\u4e0b\u5018\u82e5\u53d1\u751fMySQL\u5d29\u6e83\uff0c\u7f13\u5b58\u6c60\u4e2d\u672a\u843d\u76d8\u7684\u6570\u636e\u90fd\u4f1a\u4e22\u5931\uff0c\u6b64\u65f6\u5982\u679c\u60f3\u5355\u7eaf\u4f9d\u9760Bin log\u8fdb\u884c\u6570\u636e\u6062\u590d\u5f88\u53ef\u80fd\u4f1a\u51fa\u73b0\u9519\u8bef\uff0c\u7a76\u5176\u539f\u56e0\uff0c\u5728\u4e8eBin log<strong>\u662f\u5168\u91cf\u65e5\u5fd7\uff0c\u65e0\u6cd5\u5224\u65ad\u65e5\u5fd7\u4e2d\u7684\u54ea\u4e9b\u5185\u5bb9\u5df2\u7ecf\u843d\u76d8<\/strong>\u3002<\/p>\n<p>\u4e3e\u4e2a\u4f8b\u5b50\uff0c\u5047\u8bbeBin Log\u4ee5STATEMENT\u7684\u65b9\u5f0f\u8bb0\u5f55\uff0c\u8bb0\u5f55\u4e0b\u4e86\u5982\u4e0bSQL\uff1a<\/p>\n<pre><code class=\"language-sql\">update abc set d = 233 where e = 233;  \/*\u8fd9\u53e5\u7684\u5185\u5bb9\u4e0d\u91cd\u8981*\/\nupdate t set c = c + 1 where id = 1;<\/code><\/pre>\n<p>\u5bf9\u4e8e\u5d29\u6e83\u7684\u65f6\u70b9\u5b58\u5728\u4e24\u79cd\u60c5\u51b5\uff1a\u7b2c\u4e00\uff0cMaster thread\u5728\u6267\u884c\u7f13\u5b58\u5237\u76d8\u65f6\uff0c\u521a\u6267\u884c\u5b8c\u7b2c\u4e00\u53e5\u5c31\u5d29\u4e86\uff0c\u6b64\u65f6\u76d8\u4e0a\u6570\u636e\u7684c\u662f\u6ca1\u6709\u589e\u52a0\u8fc7\u7684\uff1b\u7b2c\u4e8c\uff0c\u6267\u884c\u5b8c\u7b2c\u4e8c\u53e5sql\u7684\u843d\u76d8\u540e\u624d\u5d29\u6e83\uff0c\u6b64\u65f6\u76d8\u4e0a\u6570\u636e\u7684c\u662f\u5df2\u7ecf\u88ab\u589e\u52a0\u540e\u7684\u3002<br \/>\n\u7531\u4e8e\u7b2c\u4e8c\u6761\u8bed\u53e5\u5e76\u4e0d\u662f<code>set c = \u67d0\u4e2a\u7acb\u5373\u6570<\/code>\u800c\u662f\u8ba9c\u81ea\u589e\uff0c\u5728\u5c1d\u8bd5\u8fdb\u884c\u6570\u636e\u6062\u590d\u65f6\uff0c\u6211\u4eec\u4f1a\u53d1\u73b0\u65e0\u6cd5\u786e\u5b9a\u76d8\u4e0a\u7684c\u662f\u5426\u5df2\u7ecf\u8fdb\u884c\u4e86\u81ea\u589e\uff0c\u6b63\u662f\u56e0\u4e3a\u8fd9\u4e2a\u539f\u56e0\uff0c\u6211\u4eec\u65e0\u6cd5\u5224\u65ad\u8be5\u4ece\u4f55\u5904\u5f00\u59cb\u91cd\u65b0\u6267\u884cbin log\u4e2d\u8bb0\u5f55\u7684sql\u3002\u5982\u679c\u4ece\u9519\u8bef\u7684\u4f4d\u7f6e\u5f00\u59cb\u6062\u590d\u6570\u636e\uff0c\u5728\u8bbe\u7f6e\u4e86\u4e3b\u4ece\u590d\u5236\u7684\u60c5\u51b5\u4e0b\u8fd8\u53ef\u80fd\u5bfc\u81f4\u4ece\u8282\u70b9\u548c\u4e3b\u8282\u70b9\u7684\u6570\u636e\u4e0d\u4e00\u81f4\uff0c\u9020\u6210\u66f4\u4e25\u91cd\u7684\u540e\u679c\u3002\u56e0\u6b64\u5728\u6ca1\u6709redo log\u7684\u573a\u5408\u4e0b\uff0cBin log\u7684\u5168\u91cf\u65e5\u5fd7\u6027\u8d28\u4f7f\u5176\u53ea\u9002\u7528\u4e8e\u6062\u590d\u8bef\u5220\u6570\u636e\u6216\u8005\u8bef\u5220\u8868\u7684\u60c5\u51b5\uff0c\u56e0\u4e3a\u8fd9\u4e9b\u8bef\u64cd\u4f5c\u7684\u5220\u9664\u8bed\u53e5\u5728\u65e5\u5fd7\u4e2d\u76f8\u5f53\u5bb9\u6613\u5b9a\u4f4d\u3002<br \/>\n\u6b63\u56e0\u5982\u6b64\uff0credo log\u5728\u8bbe\u8ba1\u65f6\u5c31\u91c7\u7528\u4e86\u8bb0\u5f55\u7269\u7406\u65e5\u5fd7+\u4f7f\u7528checkpoint\u8bb0\u5f55\u843d\u76d8\u8fdb\u5ea6\u7684\u65b9\u6848\uff0c\u4ece\u800c\u5b9e\u73b0\u4e86crash-safe\u3002<\/p>\n<h2>16.7 \u4e24\u9636\u6bb5\u63d0\u4ea4(2-Phase Commit, 2PC)<\/h2>\n<h3>16.7.1 \u95ee\u9898\u7684\u5f15\u5165\uff1a\u5206\u5e03\u5f0f\u4e00\u81f4\u6027\u95ee\u9898<\/h3>\n<p>\u5bf9\u4e8e\u9700\u8981\u4e3b\u4ece\u590d\u5236\u7684\u573a\u666f\uff0cMySQL\u540c\u65f6\u5f00\u542f\u4e86Bin log\u548cRedo log\u540e\uff0c\u5c31\u5b58\u5728\u4e24\u4e2a\u65e5\u5fd7\u4e4b\u95f4\u7684\u4e00\u81f4\u6027\u95ee\u9898\u3002\u5f53\u4e00\u4e2a\u4e8b\u52a1\u51c6\u5907\u63d0\u4ea4\u65f6\uff0c\u65e0\u8bba\u662f\u5148\u5199Redo log\u518d\u5199Bin log\uff0c\u8fd8\u662f\u5148\u5199Bin log\u518d\u5199Redo log\uff0c\u90fd\u4f1a\u6709\u53d1\u751f\u5d29\u6e83\u65f6\u53ea\u5199\u4e86\u5176\u4e2d\u4e00\u4e2a\u800c\u5c1a\u672a\u6765\u5f97\u53ca\u5199\u53e6\u4e00\u4e2a\u7684\u53ef\u80fd\u6027\u3002\u4e0b\u9762\u5bf9\u8fd9\u4e24\u79cd\u60c5\u51b5\u5206\u522b\u8ba8\u8bba\uff1a<\/p>\n<p>\u5047\u8bbe\u5728\u4e8b\u52a1\u5b8c\u6210\u65f6\uff0c\u5148\u5199Redo log\u540e\u5199Bin log\uff0c\u800c\u5d29\u6e83\u53d1\u751f\u5728\u5199\u5b8cRedo log\u4e4b\u540e\u3001\u5199\u5165Bin log\u4e4b\u524d\uff0c\u90a3\u4e48\u4e3b\u8282\u70b9\u5728\u91cd\u542f\u540e\u53ef\u4ee5\u6839\u636eRedo log\u6210\u529f\u6062\u590d\u6570\u636e\uff0c\u4f46\u662f\u4ece\u8282\u70b9\u901a\u8fc7\u4e3b\u4ece\u590d\u5236\u673a\u5236\u83b7\u5f97\u7684Bin log\u5e76\u6ca1\u6709\u8fd9\u6761\u4e8b\u52a1\u7684\u65e5\u5fd7\uff0c\u5bfc\u81f4\u4ece\u8282\u70b9\u6bd4\u4e3b\u8282\u70b9\u5c11\u6570\u636e\uff1b<br \/>\n\u53cd\u8fc7\u6765\u4e5f\u662f\u7c7b\u4f3c\u7684\uff0c\u5047\u8bbe\u5728\u4e8b\u52a1\u5b8c\u6210\u65f6\uff0c\u6211\u4eec\u5148\u5199Bin log\u540e\u5199Redo log\uff0c\u800c\u5d29\u6e83\u53d1\u751f\u5728\u5199\u5b8cBin log\u4e4b\u540e\u3001\u5199\u5165Redo log\u4e4b\u524d\uff0c\u6b64\u65f6\u4e3b\u8282\u70b9\u91cd\u542f\u540e\u627e\u4e0d\u5230\u8be5\u4e8b\u52a1\u5bf9\u5e94\u7684redo log\uff0c\u65e0\u6cd5\u6062\u590d\u8be5\u4e8b\u52a1\u7684\u6570\u636e\uff0c\u800c\u62ff\u5230Bin log\u8fdb\u884c\u590d\u5236\u7684\u4ece\u8282\u70b9\u53cd\u800c\u6709\u4e86\u8be5\u4e8b\u52a1\u6570\u636e\u3002<\/p>\n<p>\u901a\u8fc7\u4e0a\u8ff0\u5206\u6790\u53ef\u4ee5\u53d1\u73b0\uff0c\u5982\u679c\u53ea\u662f\u7b80\u5355\u7684\u8c03\u6574Bin log\u548cRedo log\u7684\u5199\u5165\u987a\u5e8f\uff0c\u4e00\u65e6\u4e3b\u8282\u70b9\u5728\u6267\u884c\u4e8b\u52a1\u671f\u95f4\u5d29\u6e83\uff0c\u6211\u4eec\u65e0\u8bba\u5982\u4f55\u90fd\u65e0\u6cd5\u4fdd\u8bc1\u4e3b\u4ece\u8282\u70b9\u6570\u636e\u4e00\u81f4\uff0c\u8fd9\u5c31\u662f\u4e8b\u52a1\u7684\u5206\u5e03\u5f0f\u4e00\u81f4\u6027\u95ee\u9898\u3002<\/p>\n<h3>16.7.2 \u4e24\u9636\u6bb5\u63d0\u4ea4\u4e0b\u7684Update\u8fc7\u7a0b<\/h3>\n<p>\u4e3a\u4e86\u89e3\u51b3\u5206\u5e03\u5f0f\u4e00\u81f4\u6027\u95ee\u9898\uff0c\u4eba\u4eec\u63d0\u51fa\u4e86\u4e24\u9636\u6bb5\u63d0\u4ea4\u8fd9\u4e00\u6982\u5ff5\u3002\u5728MySQL\u4e2d\uff0c\u4e24\u9636\u6bb5\u63d0\u4ea4\u4f53\u73b0\u5728\u5bf9redo log\u8fdb\u884c\u72b6\u6001\u6807\u8bb0\u3002\u66f4\u65b0\u5185\u5b58\u7f13\u5b58\u4e2d\u7684\u6570\u636e\u540e\uff0c\u9996\u5148\u7531\u5b58\u50a8\u5f15\u64ce\u5199redo log\u5e76\u6807\u8bb0\u4e3aprepare\u72b6\u6001\uff0c\u968f\u540e\u7531<strong>\u6267\u884c\u5668<\/strong>\u5199bin log\uff0c\u6700\u540e\u5c06redo log\u6807\u8bb0\u4e3acommit\u72b6\u6001\uff0c\u6b64\u540e\u53ef\u4ee5\u63d0\u4ea4\u4e8b\u52a1\u3002<br \/>\n<div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/s2.51cto.com\/oss\/202112\/26\/e63e55bf8f8bc767f71bbc85cc5c0c12.jpg'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  decoding=\"async\" data-original=\"https:\/\/s2.51cto.com\/oss\/202112\/26\/e63e55bf8f8bc767f71bbc85cc5c0c12.jpg\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"Update\u8fc7\u7a0b\" \/><\/div><\/p>\n<h3>16.7.3 \u4e24\u9636\u6bb5\u63d0\u4ea4\u4e0b\u5d29\u6e83\u6062\u590d\u7684\u5b9e\u73b0<\/h3>\n<p>\u5728\u5199\u5165redo log\u5e76\u5c06redo log\u6807\u8bb0\u4e3aprepare\u72b6\u6001\u65f6\uff0c\u4f1a\u8bb0\u5f55XID\uff0c\u5373\u5f53\u524d\u4e8b\u52a1id\uff0c\u540c\u65f6\u5728\u5199binlog\u65f6\u4e5f\u4f1a\u8bb0\u5f55XID\u3002\u5728\u8bb0\u5f55\u4e86XID\u7684\u60c5\u51b5\u4e0b\uff0c\u5c31\u53ef\u4ee5\u5728\u5d29\u6e83\u6062\u590d\u4fdd\u8bc1\u4e00\u81f4\u6027\uff0c\u5177\u4f53\u7684\u6062\u590d\u903b\u8f91\u5982\u4e0b\uff1a<\/p>\n<ol>\n<li>\u5982\u679credo log\u91cc\u9762\u7684\u4e8b\u52a1\u662f\u5b8c\u6574\u7684\uff0c\u4e5f\u5c31\u662f\u5df2\u7ecf\u6709\u4e86commit\u6807\u8bc6\uff0c\u5219\u76f4\u63a5\u63d0\u4ea4<\/li>\n<li>\u5982\u679credo log\u91cc\u9762\u7684\u4e8b\u52a1\u5904\u4e8eprepare\u72b6\u6001\uff0c\u5219\u67e5\u627ebin log\uff0c\u67e5\u770b\u662f\u5426\u5b58\u5728\u4e0eredo log\u76f8\u540c\u7684XID\uff0c\u5982\u679c\u5b58\u5728\u8bf4\u660ebin log\u5b8c\u6574\uff0c\u5219\u63d0\u4ea4\u4e8b\u52a1\uff0c\u5426\u5219\u56de\u6eda\u4e8b\u52a1\uff0c\u4e0d\u6062\u590d\u6b64\u6761\u6570\u636e\u3002<\/li>\n<\/ol>\n<p>\u63a5\u4e0b\u6765\u5206\u4e09\u4e2a\u60c5\u51b5\u8ba8\u8bba\uff0c\u8bc1\u660e\u4ee5\u4e0a\u89c4\u5219\u7684\u6b63\u786e\u6027\uff1a<\/p>\n<ol>\n<li>\u5982\u679c\u5728\u5199\u5165redo log\u4e4b\u524d\u5d29\u6e83\uff0c\u90a3\u4e48\u6b64\u65f6redo log\u4e0ebin log\u4e2d\u90fd\u6ca1\u6709\u5173\u4e8e\u8be5\u4e8b\u52a1\u7684\u5185\u5bb9\uff0c\u5df2\u7ecf\u5177\u6709\u4e00\u81f4\u6027\uff1b<\/li>\n<li>\u5982\u679c\u5728\u5199\u5165redo log prepare\u9636\u6bb5\u540e\u5d29\u6e83\uff0c\u6267\u884c\u5d29\u6e83\u6062\u590d\u65f6\uff0c\u7531\u4e8eredo log\u6ca1\u6709\u88ab\u6807\u8bb0\u4e3acommit\uff0c\u9700\u8981\u67e5\u627ebin log\u5bfb\u627e\u662f\u5426\u5b58\u5728\u4e0e\u6b64\u6761redo log\u76f8\u540c\u4e2d\u7684XID\uff0c\u6b64\u65f6\u80af\u5b9a\u65e0\u6cd5\u627e\u5230\uff0c\u90a3\u4e48\u56de\u6eda\u8be5\u4e8b\u52a1\uff0c\u4e0d\u6062\u590d\u5176\u6570\u636e<\/li>\n<li>\u5982\u679c\u5728\u5199\u5165bin log\u540e\u5d29\u6e83\uff0c\u6267\u884c\u5d29\u6e83\u6062\u590d\u65f6\uff0c\u7531redo log\u4e2d\u7684XID\u53ef\u4ee5\u627e\u5230\u5bf9\u5e94\u7684bin log\uff0c\u5219\u6b63\u5e38\u8fdb\u884c\u63d0\u4ea4\uff0c\u6062\u590d\u8be5\u4e8b\u52a1\u7684\u6570\u636e\u3002<\/li>\n<\/ol>\n<p>\u5728\u8fd9\u6837\u7684\u673a\u5236\u4e0b\uff0c\u4e24\u9636\u6bb5\u63d0\u4ea4\u80fd\u5728\u5d29\u6e83\u6062\u590d\u65f6\uff0c\u80fd\u591f\u5bf9\u63d0\u4ea4\u4e2d\u65ad\u7684\u4e8b\u52a1\u8fdb\u884c\u8865\u507f\uff0c\u6765\u786e\u4fddredo log\u4e0ebin log\u7684\u6570\u636e\u4e00\u81f4\u6027\u3002<\/p>\n<h2>\u53c2\u8003<\/h2>\n<p><a href=\"https:\/\/juejin.cn\/post\/6987557227074846733\">https:\/\/juejin.cn\/post\/6987557227074846733<\/a><br \/>\n<a href=\"https:\/\/segmentfault.com\/a\/1190000023827696\">https:\/\/segmentfault.com\/a\/1190000023827696<\/a><br \/>\n<a href=\"https:\/\/cloud.tencent.com\/developer\/article\/1679325\">https:\/\/cloud.tencent.com\/developer\/article\/1679325<\/a><br \/>\n<a href=\"https:\/\/spongecaptain.cool\/post\/database\/logicalandphicallog\/\">https:\/\/spongecaptain.cool\/post\/database\/logicalandphicallog\/<\/a><br \/>\n<a href=\"https:\/\/www.51cto.com\/article\/696677.html\">https:\/\/www.51cto.com\/article\/696677.html<\/a><br \/>\n<a href=\"https:\/\/blog.csdn.net\/qq_33591903\/article\/details\/122030252\">https:\/\/blog.csdn.net\/qq_33591903\/article\/details\/122030252<\/a><\/p>\n<h1>17. TODO: Undo log<\/h1>\n<h2>17.1 \u5173\u4e8eundo log<\/h2>\n<p>\u56de\u6eda\u65e5\u5fd7\uff0c\u662f\u903b\u8f91\u65e5\u5fd7\uff0c\u7528\u4e8e\u56de\u6eda\u4e8b\u52a1\u5bf9\u6570\u636e\u5e93\u7684\u4fee\u6539\uff0c\u4e5f\u7528\u4e8e\u5b9e\u73b0MVCC\u7684\u5feb\u7167\u8bfb\u3002\u7531\u4e8e\u5176\u662f\u903b\u8f91\u65e5\u5fd7\uff0c\u56de\u6eda\u8fc7\u7a0b\u4e5f\u662f\u903b\u8f91\u7684\uff0c\u53ef\u4ee5\u7406\u89e3\u4e3a\u5bf9\u4e8e\u6bcf\u4e00\u6761Insert\uff0c\u56de\u6eda\u65f6\u6267\u884c\u5bf9\u5e94\u7684Delete\u3001\u5bf9\u4e8e\u6bcf\u4e00\u6761Update\uff0c\u56de\u6eda\u65f6\u6267\u884c\u53cd\u5411\u7684Update\u5c06\u539f\u59cb\u6570\u636e\u66f4\u65b0\u56de\u6765\u3002<\/p>\n<blockquote>\n<p>\u4e3a\u4ec0\u4e48\u4e0d\u80fd\u662f\u7269\u7406\u65e5\u5fd7\uff1f<br \/>\n\u7269\u7406\u65e5\u5fd7\u4f8b\u5982Bin log\u8bb0\u5f55\u4e00\u4e2a\u6570\u636e\u9875\u7684\u53d8\u5316\uff0c\u901a\u8fc7\u7269\u7406\u65e5\u5fd7\u8fdb\u884c\u6570\u636e\u6062\u590d\u65f6\uff0c\u4f1a\u5c06\u6574\u4e2a\u6570\u636e\u9875\u6062\u590d\u5230\u539f\u6765\u7684\u60c5\u51b5\u3002\u5047\u8bbe\u4e00\u4e2a\u573a\u666f\uff1a\u4e00\u4e2a\u6570\u636e\u9875\u4e0a\u540c\u65f6\u6709\u591a\u4e2a\u4e8b\u52a1\u64cd\u4f5c\u4e0d\u540c\u4f4d\u7f6e\u7684\u6570\u636e\uff0c\u800c\u5176\u4e2d\u67d0\u4e00\u4e2a\u4e8b\u52a1\u5931\u8d25\u9700\u8981\u56de\u6eda\uff0c\u6b64\u65f6\u82e5\u91c7\u7528\u7269\u7406\u65e5\u5fd7\u56de\u6eda\u6574\u4e2a\u9875\uff0c\u4f1a\u5bfc\u81f4\u5176\u4ed6\u4e8b\u52a1\u4f5c\u51fa\u7684\u4fee\u6539\u5931\u6548\u3002<br \/>\n\u2014\u2014\u300aMySQL\u6280\u672f\u5185\u5e55\uff1aInnoDB\u5b58\u50a8\u5f15\u64ce\u300b<\/p>\n<\/blockquote>\n<p><strong>\u6ce8\u610f\uff1a<\/strong><br \/>\nInnoDB\u5b9e\u9645\u4e0a\u662f\u5c06undo log\u5f53\u505a\u4e00\u79cd\u6570\u636e\u6765\u7ef4\u62a4\u548c\u4f7f\u7528\u7684\uff0c\u5b58\u653e\u5728\u6570\u636e\u5e93\u7684\u4e00\u4e2a\u7279\u6b8a\u6bb5\u4e2d\uff0c\u6240\u4ee5undo log\u5728\u53d8\u5316\u65f6\u4e5f\u9700\u8981\u5199redo log\u3002<\/p>\n<h2>17.2 purge\u4e0eundo log\u7684\u5206\u7c7b<\/h2>\n<h2>17.3 \u5bf9\u6bd4\u8868\u683c<\/h2>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u533a\u522b\u9879<\/th>\n<th style=\"text-align: center;\">Redo log<\/th>\n<th style=\"text-align: center;\">Undo log<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u7528\u9014<\/td>\n<td style=\"text-align: center;\">\u7528\u4e8e\u5f02\u5e38\u5d29\u6e83\u7684\u6062\u590d<\/td>\n<td style=\"text-align: center;\">\u7528\u4e8e\u4e8b\u52a1\u56de\u6eda\u3001\u5b9e\u73b0MVCC\u7684\u5feb\u7167\u8bfb<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u8bb0\u5f55\u5185\u5bb9<\/td>\n<td style=\"text-align: center;\">\u7269\u7406\u683c\u5f0f\u65e5\u5fd7\uff0c\u8bb0\u5f55\u7269\u7406\u6570\u636e\u9875\u7684\u4e8c\u8fdb\u5236\u4fee\u6539\u5185\u5bb9<\/td>\n<td style=\"text-align: center;\">\u903b\u8f91\u65e5\u5fd7\uff0c\u4ee5\u8868\u7684\u5f62\u5f0f\u8bb0\u5f55\u884c\u6570\u636e\u4fee\u6539\u7684\u5386\u53f2\u7248\u672c<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h1>18. MySQL\u7684\u5185\u90e8\u6784\u9020\/MYSQL\u6267\u884cSQL\u8bed\u53e5\u8fc7\u7a0b<\/h1>\n<ol>\n<li>\u670d\u52a1(server)\u5c42\uff1a\n<ol>\n<li>\u8fde\u63a5\u5668\uff1a\u7ba1\u7406\u8fde\u63a5\uff0c\u9a8c\u8bc1\u8eab\u4efd\u548c\u6743\u9650  <\/li>\n<li>\u67e5\u7f13\u5b58\uff1a\u4ee5\u952e\u503c\u5bf9\u65b9\u5f0f\u5b58\u653eSQL\u8bed\u53e5\u548c\u7ed3\u679c\u3002\u7531\u4e8e\u5931\u6548\u592a\u9891\u7e41\uff0c\u76ee\u524d\u5df2\u7ecf\u88ab\u9ed8\u8ba4\u5173\u95ed\u3001\u79fb\u9664\u3002\u4f18\u5316\u5668\u5bf9\u8bed\u53e5\u8fdb\u884c\u4f18\u5316\u4ee5\u540e\u4ee5\u540e\u4f1a\u91cd\u65b0\u67e5\u7f13\u5b58<\/li>\n<li>\u5206\u6790\u5668\uff1a\u8bed\u6cd5\u68c0\u67e5\u548c\u5206\u6790\uff0c\u751f\u6210\u8bed\u6cd5\u6811<\/li>\n<li>\u4f18\u5316\u5668\uff1a\u5bf9\u8bed\u53e5\u8fdb\u884c\u903b\u8f91\u4f18\u5316\uff08\u4f8b\u5982\u62c6\u89e3\u5e26NOT\u7684\u548c\u53d6\u8303\u5f0f\/\u6790\u53d6\u8303\u5f0f\uff0c\u5e38\u91cf\u8868\u8fbe\u5f0f\u7684\u8ba1\u7b97\u3001\u5316\u7b80\u548c\u4f20\u9012\uff09\u548c\u4ee3\u4ef7\u4f18\u5316\uff08\u5bf9\u4e8e\u8054\u5408\u7d22\u5f15\u3001\u4e8c\u7ea7\u7d22\u5f15\u4e0a\u7684\u8303\u56f4\u67e5\u8be2\uff0c\u4f30\u7b97\u5224\u65ad\u662f\u5229\u7528\u7d22\u5f15\u8fd8\u662f\u5168\u8868\u626b\u63cf\uff1b\u786e\u5b9a\u591a\u4e2a\u8868join\u7684\u987a\u5e8f\uff09\uff0c\u751f\u6210\u6267\u884c\u8ba1\u5212\u3002\u53ef\u4ee5\u7528Explain\u6765\u67e5\u770b\u8ba1\u5212<\/li>\n<li>\u6267\u884c\u5668\uff1a\u64cd\u4f5c\u5b58\u50a8\u5f15\u64ce\uff0c\u8fd4\u56de\u7ed3\u679c<\/li>\n<\/ol>\n<\/li>\n<li>\u5b58\u50a8\u5f15\u64ce\u5c42\uff1a\u6570\u636e\u5e93\u5f15\u64ce\uff0c\u5b58\u50a8\u6570\u636e\uff0c\u63d0\u4f9b\u8bfb\u5199\u63a5\u53e3<br \/>\n<h2>\u53c2\u8003<\/h2>\n<p><a href=\"https:\/\/cloud.tencent.com\/developer\/article\/1678069\">https:\/\/cloud.tencent.com\/developer\/article\/1678069<\/a><\/p>\n<\/li>\n<\/ol>\n<h1>19. \u6570\u636e\u5e93\u7684\u5206\u5e93\u5206\u8868<\/h1>\n<h1>19.1 \u5206\u8868<\/h1>\n<p>\u5782\u76f4\u5206\u8868\uff1a\u5c06\u67d0\u4e9b\u6570\u636e\u8f83\u5927\u7684\u5217\u62c6\u51fa\uff0c\u4e0e\u4e3b\u952e\u7b49\u7ec4\u6210\u72ec\u7acb\u7684\u8868\u3002<br \/>\n\u6c34\u5e73\u5206\u8868\uff1a\u4f8b\u5982\u6309\u6027\u522b\u5206\u4e24\u4e2a\u7528\u6237\u8868\u3002\u51cf\u5c11\u5355\u8868\u7684\u6570\u636e\u91cf<br \/>\n\u5782\u76f4\u548c\u6c34\u5e73\u5206\u8868\u90fd\u53ef\u4ee5\u51cf\u5c0f\u5355\u8868\u7684\u8bbf\u95ee\u8d1f\u62c5\uff0c\u63d0\u9ad8\u67e5\u8be2\u6027\u80fd\uff0c\u51cf\u5c0f\u8868\u9501\u9020\u6210\u7684\u51b2\u7a81\uff1b<br \/>\n\u5782\u76f4\u5206\u8868\u8fd8\u53ef\u4ee5\u51cf\u5c0f\u8868\u7684\u4f53\u79ef\uff0c\u51cf\u5c11\u78c1\u76d8IO\u3002<\/p>\n<h1>19.2 \u5206\u5e93<\/h1>\n<p>\u5782\u76f4\u5206\u5e93\uff1a\u6309\u7167\u4e1a\u52a1\u5c06\u8868\u5206\u7c7b\u5230\u4e0d\u540c\u7684\u6570\u636e\u5e93\u4e0a\u9762\uff0c\u6bcf\u4e2a\u5e93\u53ef\u4ee5\u653e\u5728\u4e0d\u540c\u7684\u670d\u52a1\u5668\u4e0a\u3002\u4f8b\u5982\u5546\u54c1\u4fe1\u606f\u8bbf\u95ee\u91cf\u5927\uff0c\u800c\u5e97\u94fa\u4fe1\u606f\u8bbf\u95ee\u91cf\u76f8\u5bf9\u8f83\u4f4e\uff0c\u5219\u5c06\u5546\u54c1\u4fe1\u606f\u76f8\u5173\u7684\u8868\u653e\u5165\u5546\u54c1\u5e93\uff0c\u5e97\u94fa\u4fe1\u606f\u76f8\u5173\u7684\u8868\u653e\u5165\u5e97\u94fa\u5e93\u3002<br \/>\n\u6c34\u5e73\u5206\u5e93\uff1a\u4f8b\u5982\u6839\u636e\u5e97\u94faID\u7684\u5947\u5076\u6027\u5206\u4e3a\u4e24\u4e2a\u5e93\u3002<\/p>\n<h1>20. filesort<\/h1>\n<p>\u5916\u90e8\u6392\u5e8f\uff0c\u7b97\u6cd5\u4f7f\u7528\u7684\u662f\u5f52\u5e76\u6392\u5e8f\u3002\u5f53\u4e00\u6761SQL\u8bed\u53e5\u9700\u8981\u5bf9\u67e5\u8be2\u7ed3\u679c\u8fdb\u884c\u6392\u5e8f\uff0c\u800c\u6392\u5e8f\u7684\u5b57\u6bb5\u6ca1\u6709\u5efa\u7acb\u7d22\u5f15\u6216\u7531\u4e8e\u6700\u5de6\u5339\u914d\u539f\u5219\u65e0\u6cd5\u5728\u8be5\u5b57\u6bb5\u4f7f\u7528\u7d22\u5f15\u65f6\uff0c\u5c31\u4f1a\u501f\u7528\u5916\u90e8\u6587\u4ef6\u7cfb\u7edf\u8fdb\u884c\u5f52\u5e76\u6392\u5e8f\u3002<\/p>\n<h1>21. \u9ad8\u5e76\u53d1\u65b9\u6848\/\u6570\u636e\u5e93\u4f18\u5316<\/h1>\n<ol>\n<li>\u5728\u670d\u52a1\u5c42\u4e0e\u6570\u636e\u5e93\u5c42\u4e4b\u95f4\u52a0\u7f13\u5b58<\/li>\n<li>\u5206\u5e03\u5f0f\u67b6\u6784\uff0c\u5206\u6563\u8ba1\u7b97\u538b\u529b<\/li>\n<li>\u6570\u636e\u5e93\u4f18\u5316\uff1a\u5728\u5e38\u68c0\u7d22\u3001\u6392\u5e8f\u5b57\u6bb5\u521b\u5efa\u7d22\u5f15<\/li>\n<li>\u6570\u636e\u5e93\u4f18\u5316\uff1a\u4e3b\u4ece\u590d\u5236\u3001\u8bfb\u5199\u5206\u79bb<\/li>\n<li>\u6570\u636e\u5e93\u4f18\u5316\uff1a\u6c34\u5e73\u3001\u5782\u76f4\u5206\u8868<\/li>\n<\/ol>\n<h1>22. MySQL\u57fa\u672c\u6570\u636e\u7c7b\u578b<\/h1>\n<h2>22.1 \u6570\u503c\u7c7b\u578b<\/h2>\n<h3>22.1.1 \u6574\u6570\u7c7b\u578b<\/h3>\n<p>TINYINT\u3001SMALLINT\u3001MEDIUMINT\u3001INT\u3001BIGINT\uff0c\u5206\u522b\u4e3a1\u5b57\u8282\u30012\u5b57\u8282\u30013\u5b57\u8282\u30014\u5b57\u8282\u30018\u5b57\u8282\u3002\u4efb\u4f55\u6574\u6570\u7c7b\u578b\u90fd\u53ef\u4ee5\u52a0\u4e0aUNSIGNED\u5c5e\u6027\uff0c\u8868\u793a\u65e0\u7b26\u53f7\u6574\u6570\u3002<br \/>\n\u6574\u6570\u7c7b\u578b\u5360\u7528\u7684\u7a7a\u95f4\u662f\u56fa\u5b9a\u7684\uff0c\u5bf9\u4e8e\u4efb\u4f55\u6574\u6570\u7c7b\u578b\u58f0\u660e\u5b57\u6bb5\u957f\u5ea6\u65f6\uff0c\u5b9e\u9645\u4e0a\u53ea\u9650\u5236\u5176\u663e\u793a\u957f\u5ea6\u3002\u4f8b\u5982\u58f0\u660e\u67d0\u5b57\u6bb5int(5)\uff0c\u5176\u5360\u7528\u7a7a\u95f4\u4ecd\u7136\u662f4\u5b57\u8282\uff1b\u5728\u8be5\u5b57\u6bb5\u4e0a\u63d2\u5165\u6570\u503c123\uff0c\u7531\u4e8e\u4e0d\u8db35\u4f4d\uff0c\u6545\u5728print\u65f6\u4f1a\u8865\u524d\u5bfc0\uff0c\u663e\u793a'00123'\u3002\u53ef\u89c1\u6574\u6570\u7c7b\u578b\u7684\u58f0\u660e\u5b57\u6bb5\u957f\u5ea6\u6bd4\u8f83\u9e21\u808b\uff0c\u57fa\u672c\u6ca1\u6709\u4f7f\u7528\u573a\u666f\u3002<\/p>\n<h3>22.1.2 \u5c0f\u6570\u7c7b\u578b<\/h3>\n<p>FLOAT 4 \u5b57\u8282\uff0cDOUBLE 8 \u5b57\u8282\u3002<br \/>\nDEMICAL(P,D)\u7528\u4e8e\u5b58\u50a8\u4fdd\u7559\u7cbe\u786e\u5ea6\u7684\u5c0f\u6570\u3002P\u662f\u8868\u793a\u6709\u6548\u6570\u5b57\u6570\u7684\u7cbe\u5ea6\uff0c\u8303\u56f4\u4e3a1~65\uff1bD\u662f\u8868\u793a\u5c0f\u6570\u70b9\u540e\u7684\u4f4d\u6570\uff0cD\u7684\u8303\u56f4\u662f0~30\u3002\u76f8\u6bd4\u8d77FLOAT\u548cDOUBLE\uff0c\u4e0d\u5b58\u5728\u6d6e\u70b9\u6570\u8fd0\u7b97\u8bef\u5dee\u3002<\/p>\n<h2>22.2 \u5b57\u7b26\u4e32\u7c7b\u578b<\/h2>\n<h3>22.2.1 \u5e38\u7528\u5b57\u7b26\u4e32\u7c7b\u578b<\/h3>\n<p>CHAR\uff1a\u5b9a\u957f\u5b57\u7b26\u4e32\uff0c\u6839\u636e\u58f0\u660e\u7684\u5b57\u6bb5\u957f\u5ea6\u5206\u914d\u56fa\u5b9a\u5927\u5c0f\u7684\u7a7a\u95f4\uff0c\u6700\u5927\u7a7a\u95f4255\u5b57\u8282\u3002\u63d2\u5165\u65f6\u4f1a\u5220\u9664\u540e\u7f6e\u7a7a\u683c\uff0c\u82e5\u957f\u5ea6\u5c0f\u4e8e\u58f0\u660e\u957f\u5ea6\uff0c\u5219\u5b58\u653e\u65f6\u7528\u7a7a\u683c\u8865\u5145\u7a7a\u4f4d\uff0c\u68c0\u7d22\u8fd4\u56de\u7ed3\u679c\u65f6\u4f1a\u5220\u9664\u540e\u9762\u7684\u7a7a\u683c\uff1b\u4e5f\u7531\u4e8e\u5b9a\u957f\uff0c\u5b58\u50a8\u6548\u7387\u66f4\u9ad8\u3002<br \/>\nVARCHAR\uff1a\u53d8\u957f\u5b57\u7b26\u4e32\uff0c\u58f0\u660e\u7684\u5b57\u6bb5\u957f\u5ea6\u53ea\u9650\u5236\u5176\u6700\u5927\u957f\u5ea6\uff0c\u6839\u636e\u5f53\u524d\u5b58\u653e\u7684\u5b57\u7b26\u5185\u5bb9\u957f\u5ea6\u6765\u52a8\u6001\u5206\u914d\u7a7a\u95f4\uff0c\u6700\u5927\u7a7a\u95f465535\u5b57\u8282\u3002\u4f46\u4f1a\u5728\u6570\u636e\u5f00\u5934\u4f7f\u7528\u989d\u59161~2\u4e2a\u5b57\u8282\u5b58\u50a8\u5b57\u7b26\u4e32\u957f\u5ea6\uff08\u5217\u957f\u5ea6\u5c0f\u4e8e255\u5b57\u8282\u65f6\u4f7f\u75281\u5b57\u8282\u8868\u793a\uff0c\u5426\u52192\u5b57\u8282\uff09\uff0c\u5728\u7ed3\u5c3e\u4f7f\u75281\u5b57\u8282\u8868\u793a\u5b57\u7b26\u4e32\u7ed3\u675f\uff0c\u5b9e\u9645\u6700\u5927\u53ef\u752865532\u3002<br \/>\nTEXT\uff1a\u957f\u6587\u672c\uff0c\u4e5f\u662f\u53d8\u957f\u5b57\u7b26\u4e32\uff0c\u6700\u5927\u7a7a\u95f465535\u5b57\u8282\u3002\u76f8\u6bd4\u8f83VARCHAR\uff0c\u4e0d\u80fd\u8bbe\u7f6e\u9ed8\u8ba4\u503c\u3002\u8fd8\u6709\u66f4\u5927\u7684MEDIUMTEXT(16MB)\u548cLONGTEXT(4GB)<br \/>\nBLOB\uff1a\u4e8c\u8fdb\u5236\u683c\u5f0f\u7684\u957f\u6587\u672c\uff0c\u53ef\u4ee5\u7528\u4e8e\u5b58\u653e\u56fe\u7247\u3001\u97f3\u9891\u7b49\u6570\u636e\u3002\u4e0eTEXT\u5bf9\u5e94\uff0c\u4e5f\u5b58\u5728MEDIUM\u548cLONG\u7248\u672c\u3002<\/p>\n<p>\u6ce8\uff1a\u5bf9\u4e8eCHAR\u548cVARCHAR\uff0c\u5176\u5b57\u6bb5\u957f\u5ea6\u662f<strong>\u5b57\u7b26\u4e2a\u6570<\/strong>\uff0c\u800c\u4e0d\u662f\u6240\u5360\u7a7a\u95f4\u7684\u4ee5\u5b57\u8282\u8ba1\u7b97\u7684\u5927\u5c0f\u3002\u5b57\u6bb5\u957f\u5ea6\u4e0e\u5b57\u8282\u957f\u5ea6\u4e0d\u80fd\u76f4\u63a5\u5212\u7b49\u53f7\uff0c\u4e0e\u9009\u62e9\u7684\u7f16\u7801\u548c\u5b9e\u9645\u5b58\u653e\u5185\u5bb9\u6709\u5173\u3002\u4e3e\u4f8b\uff1a\u5728GBK\u7f16\u7801\u4e0b\u5b58\u653e\u6c49\u5b57\uff0c\u6bcf\u4e2a\u5b57\u7b26\u5360\u75282\u5b57\u8282\uff0cCHAR(2)\u5360\u75284\u4e2a\u5b57\u8282\u7684\u7a7a\u95f4\uff1b\u800c\u5728UTF-8\u7f16\u7801\u4e0b\u5b58\u653eASCII\u5b57\u7b26\uff0c\u6bcf\u4e2a\u5b57\u7b26\u53ea\u5360\u75281\u5b57\u8282\uff0cCHAR(2)\u5c31\u53ea\u5360\u75282\u5b57\u8282\u7684\u7a7a\u95f4\uff0c\u800cUTF-8\u7f16\u7801\u5b58\u653e\u6c49\u5b57\u65f6\u4e00\u4e2a\u6c49\u5b57\u5360\u75283\u5b57\u8282\uff0cCHAR(2)\u5c31\u5360\u75286\u5b57\u8282\u4e86\u3002<\/p>\n<h3>22.2.2 CHAR\u548cVARCHAR\u7684\u9009\u62e9<\/h3>\n<p>CHAR\u662f\u56fa\u5b9a\u957f\u5ea6\uff0c\u6240\u4ee5\u5b83\u7684\u5904\u7406\u901f\u5ea6\u6bd4VARCHAR\u7684\u901f\u5ea6\u8981\u5feb\uff0c\u4f46\u662f\u5b83\u7684\u7f3a\u70b9\u5c31\u662f\u6d6a\u8d39\u5b58\u50a8\u7a7a\u95f4\u3002\u6240\u4ee5\u5bf9\u5b58\u50a8\u4e0d\u5927\uff0c\u4f46\u5728\u901f\u5ea6\u4e0a\u6709\u8981\u6c42\u7684\u53ef\u4ee5\u4f7f\u7528CHAR\u7c7b\u578b\uff0c\u53cd\u4e4b\u53ef\u4ee5\u4f7f\u7528VARCHAR\u7c7b\u578b\u6765\u5b9e\u73b0\u3002<\/p>\n<p>\u5b58\u50a8\u5f15\u64ce\u5bf9\u4e8e\u9009\u62e9CHAR\u548cVARCHAR\u7684\u5f71\u54cd:<br \/>\n\u5bf9\u4e8eMyISAM\u5b58\u50a8\u5f15\u64ce\uff0c\u6700\u597d\u4f7f\u7528\u56fa\u5b9a\u957f\u5ea6\u7684\u6570\u636e\u5217\u4ee3\u66ff\u53ef\u53d8\u957f\u5ea6\u7684\u6570\u636e\u5217\u3002\u8fd9\u6837\u53ef\u4ee5\u4f7f\u6574\u4e2a\u8868\u9759\u6001\u5316\uff0c\u4ece\u800c\u4f7f\u6570\u636e\u68c0\u7d22\u66f4\u5feb\uff0c\u7528\u7a7a\u95f4\u6362\u65f6\u95f4\u3002<br \/>\n\u5bf9\u4e8eInnoDB\u5b58\u50a8\u5f15\u64ce\uff0c\u6700\u597d\u4f7f\u7528\u53ef\u53d8\u957f\u5ea6\u7684\u6570\u636e\u5217\uff0c\u56e0\u4e3aInnoDB\u6570\u636e\u8868\u7684\u5b58\u50a8\u683c\u5f0f\u4e0d\u5206\u56fa\u5b9a\u957f\u5ea6\u548c\u53ef\u53d8\u957f\u5ea6\uff0c\u56e0\u6b64\u4f7f\u7528CHAR\u4e0d\u4e00\u5b9a\u6bd4\u4f7f\u7528VARCHAR\u66f4\u597d\uff0c\u4f46\u7531\u4e8eVARCHAR\u662f\u6309\u7167\u5b9e\u9645\u7684\u957f\u5ea6\u5b58\u50a8\uff0c\u6bd4\u8f83\u8282\u7701\u7a7a\u95f4\uff0c\u6240\u4ee5\u5bf9\u78c1\u76d8I\/O\u548c\u6570\u636e\u5b58\u50a8\u603b\u91cf\u6bd4\u8f83\u597d\u3002<\/p>\n<h2>22.3 \u65e5\u671f\u548c\u65f6\u95f4\u7c7b\u578b<\/h2>\n<p>DATE\uff1a\u53ea\u5b58\u50a8\u65e5\u671f\uff0cYYYY-MM-DD<br \/>\n\u5bf9\u4e8e\u5b8c\u6574\u7684\u65e5\u671f-\u65f6\u95f4\uff0c\u5219\u6709DATETIME\u548cTIMESTAMP\u3002<\/p>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: center;\">\u533a\u522b\u9879<\/th>\n<th style=\"text-align: center;\">DATETIME<\/th>\n<th style=\"text-align: center;\">TIMESTAMP<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">\u5927\u5c0f<\/td>\n<td style=\"text-align: center;\">8\u5b57\u8282<\/td>\n<td style=\"text-align: center;\">4\u5b57\u8282<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u65f6\u95f4\u8303\u56f4<\/td>\n<td style=\"text-align: center;\">1000-01-01 00:00:00\/9999-12-31 23:59:59<\/td>\n<td style=\"text-align: center;\">1970-01-01 00:00:00\/2038 \uff08\u5b9e\u9645\u4e0a\u662f\u8bb0\u5f55\u4e00\u4e2a4\u5b57\u8282\u7684int\u4f5c\u4e3a\u79d2\u6570\uff0c\u6240\u4ee5\u65f6\u95f4\u8303\u56f4\u6bd4\u8f83\u5c0f\uff09<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u65f6\u533a\u8f6c\u6362<\/td>\n<td style=\"text-align: center;\">\u65e0\u8f6c\u6362<\/td>\n<td style=\"text-align: center;\">\u8f6c\u6362\u6210UTC\u65f6\u95f4<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: center;\">\u5b58\u5165NULL\u65f6\u7684\u53cd\u5e94<\/td>\n<td style=\"text-align: center;\">\u5b57\u9762\u610f\u4e49\u4e0a\u5b58\u5165NULL<\/td>\n<td style=\"text-align: center;\">\u81ea\u52a8\u5b58\u5165\u5f53\u524d\u65f6\u95f4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>\u53c2\u8003\uff1a<\/h2>\n<p><a href=\"http:\/\/c.biancheng.net\/view\/7175.html\">http:\/\/c.biancheng.net\/view\/7175.html<\/a><br \/>\n<a href=\"https:\/\/kalacloud.com\/blog\/difference-between-mysql-datetime-and-timestamp-datatypes\/\">https:\/\/kalacloud.com\/blog\/difference-between-mysql-datetime-and-timestamp-datatypes\/<\/a><\/p>\n<h1>23. SQL\u8fde\u63a5\uff08JOIN\uff09<\/h1>\n<ul>\n<li>\u5185\u8fde\u63a5 INNER JOIN\uff1a<br \/>\n\u4ea4\u96c6\u3002<\/li>\n<li>\u5de6\u5916\u8fde\u63a5 LEFT (OUTER) JOIN\uff1a<br \/>\n\u8fd4\u56de\u5de6\u8868\u7684\u6240\u6709\u884c\uff0c\u82e5\u67d0\u884c\u5728\u53f3\u8868\u6ca1\u6709\u5339\u914d\u503c\uff0c\u5219\u5c5e\u4e8e\u53f3\u8868\u7684\u5b57\u6bb5\u4f1a\u662fNULL\u3002<\/li>\n<li>\u53f3\u5916\u8fde\u63a5 RIGHT (OUTER) JOIN\uff1a<br \/>\n\u7c7b\u4f3c\u5de6\u5916\u8fde\u63a5\u3002<\/li>\n<li>\u5168\u5916\u8fde\u63a5 FULL (OUTER) JOIN\uff1a<br \/>\n\u5de6\u5916\u8fde\u63a5+\u53f3\u5916\u8fde\u63a5\u3002\u8fd4\u56de\u5de6\u53f3\u8868\u7684\u6240\u6709\u884c\u3002<\/li>\n<li>\u4ea4\u53c9\u8fde\u63a5 CROSS JOIN\uff1a<br \/>\n\u7b1b\u5361\u5c14\u79ef\uff0c\u5de6\u8868\u4e2d\u7684\u6bcf\u4e00\u884c\u548c\u53f3\u8868\u4e2d\u7684\u6bcf\u4e00\u884c\u7ec4\u5408\uff0c\u5de6\u8868\u6709M\u884c\u53f3\u8868\u6709N\u884c\u90a3\u4e48\u7ed3\u679c\u5c31\u6709MN\u884c<\/li>\n<\/ul>\n<h1>24. \u4e3a\u4ec0\u4e48\u4e0d\u5efa\u8bae\u4f7f\u7528<code>select *<\/code> \uff1f<\/h1>\n<p>\u4e09\u4e2a\u4e3b\u8981\u95ee\u9898\uff1a<\/p>\n<ul>\n<li>\u78c1\u76d8IO\u5927<\/li>\n<li>\u6d6a\u8d39\u7f51\u7edc\u5e26\u5bbd<\/li>\n<li>\u3010\u91cd\u8981\u3011\u53ef\u80fd\u5bfc\u81f4\u65e0\u610f\u4e49\u7684\u56de\u8868\u3002\u4f8b\u5982\uff0c\u5bf9(name, phone)\u6784\u5efa\u8054\u5408\u7d22\u5f15\uff0c\u5f53<code>select name, phone<\/code>\u65f6\uff0c\u8fd9\u4e2a\u4e8c\u7ea7\u7d22\u5f15\u5b9e\u9645\u4e0a\u505a\u5230\u4e86\u8986\u76d6\u7d22\u5f15\uff0c\u53ef\u4ee5\u76f4\u63a5\u901a\u8fc7\u7d22\u5f15\u5f97\u5230\u9700\u8981\u7684\u6570\u636e\uff1b\u800c\u5982\u679c<code>select *<\/code>\uff0c\u4e3a\u4e86\u62ff\u5230\u4e0d\u5305\u542b\u4e8e\u6b64\u8054\u5408\u7d22\u5f15\u4e2d\u7684\u5176\u4ed6\u5217\u7684\u6570\u636e\uff0c\u5fc5\u987b\u56de\u8868\u3002<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>1. \u6570\u636e\u5e93\u7684\u4e09\u8303\u5f0f 1NF\uff1a\u5217\u5177\u6709\u539f\u5b50\u6027\uff0c\u4e0d\u53ef\u62c6\u5206\uff08\u4e5f\u53ef\u4ee5\u8868\u8ff0\u4e3a\u201c\u8868\u90fd\u662f\u4e8c\u7ef4\u7684\u201d\uff09 2NF\uff1a\u4e00\u4e2a\u8868\u5fc5\u987b\u6709\u4e00\u4e2a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":180,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[17,21],"class_list":["post-179","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-10","tag-innodb","tag-mysql"],"_links":{"self":[{"href":"https:\/\/cococat.top\/index.php\/wp-json\/wp\/v2\/posts\/179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cococat.top\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cococat.top\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cococat.top\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cococat.top\/index.php\/wp-json\/wp\/v2\/comments?post=179"}],"version-history":[{"count":0,"href":"https:\/\/cococat.top\/index.php\/wp-json\/wp\/v2\/posts\/179\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cococat.top\/index.php\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/cococat.top\/index.php\/wp-json\/wp\/v2\/media?parent=179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cococat.top\/index.php\/wp-json\/wp\/v2\/categories?post=179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cococat.top\/index.php\/wp-json\/wp\/v2\/tags?post=179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}