{"id":57,"date":"2021-03-13T12:18:22","date_gmt":"2021-03-13T04:18:22","guid":{"rendered":"http:\/\/daishen.ltd\/?p=57"},"modified":"2021-03-13T12:18:22","modified_gmt":"2021-03-13T04:18:22","slug":"%e5%ad%98%e5%82%a8%e5%bc%95%e6%93%8e","status":"publish","type":"post","link":"https:\/\/www.daishen.ltd\/?p=57","title":{"rendered":"\u5b58\u50a8\u5f15\u64ce"},"content":{"rendered":"<p><meta charset=\"UTF-8\"><meta name=\"viewport\" content=\"width=device-width initial-scale=1\"><br \/>\n<title>\u5b58\u50a8\u5f15\u64ce<\/title><\/p>\n<h1>1\u3001\u7b80\u4ecb<\/h1>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u76f8\u5f53\u4e8eLinux\u6587\u4ef6\u7cfb\u7edf\uff0c\u53ea\u4e0d\u8fc7\u6bd4\u6587\u4ef6\u7cfb\u7edf\u5f3a\u5927\n<\/code><\/pre>\n<h1>2\u3001\u529f\u80fd\u4e86\u89e3<\/h1>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u6570\u636e\u8bfb\u5199\n\u6570\u636e\u5b89\u5168\u548c\u4e00\u81f4\u6027\n\u63d0\u9ad8\u6027\u80fd\n\u70ed\u5907\u4efd\n\u81ea\u52a8\u6545\u969c\u6062\u590d\n\u9ad8\u53ef\u7528\u65b9\u9762\u652f\u6301\n\u7b49.\n<\/code><\/pre>\n<h1>3\u3001\u5b58\u50a8\u5f15\u64ce\u79cd\u7c7b\uff08\u7b14\u8bd5\uff09<\/h1>\n<h2>3.1 \u4ecb\u7ecd(Oracle MySQL)<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">InnoDB\nMyISAM\nMEMORY\nARCHIVE\nFEDERATED\nEXAMPLE\nBLACKHOLE\nMERGE\nNDBCLUSTER\nCSV\n<\/code><\/pre>\n<h2>3.2 \u5f15\u64ce\u79cd\u7c7b\u67e5\u770b<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-dart\" lang=\"dart\">show engines;\n\u5b58\u50a8\u5f15\u64ce\u662f\u4f5c\u7528\u5728\u8868\u4e0a\u7684\uff0c\u4e5f\u5c31\u610f\u5473\u7740\uff0c\u4e0d\u540c\u7684\u8868\u53ef\u4ee5\u6709\u4e0d\u540c\u7684\u5b58\u50a8\u5f15\u64ce\u7c7b\u578b\u3002\nPerconaDB:\u9ed8\u8ba4\u662fXtraDB\nMariaDB:\u9ed8\u8ba4\u662fInnoDB\nmysql:\u9ed8\u8ba4\u662fInnoDB\n\u7b2c\u4e09\u65b9\u7684\u5b58\u50a8\u5f15\u64ce\u652f\u6301:\nTokuDB    \nRocksDB\nMyRocks\n\u4ee5\u4e0a\u4e09\u79cd\u5b58\u50a8\u5f15\u64ce\u7684\u5171\u540c\u70b9:\u538b\u7f29\u6bd4\u8f83\u9ad8,\u6570\u636e\u63d2\u5165\u6027\u80fd\u6781\u9ad8\uff0c\u5176\u4ed6\u529f\u80fd\u548cInnoDB\u5dee\u4e0d\u591a\n\u73b0\u5728\u5f88\u591a\u7684NewSQL,\u4f7f\u7528\u6bd4\u8f83\u591a\u7684\u529f\u80fd\u7279\u6027.\n<\/code><\/pre>\n<h2>3.3 InnoDB\u4e2aMyISAM\u5b58\u50a8\u5f15\u64ce\u7684\u66ff\u6362<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u73af\u5883: centos 5.8 ,MySQL 5.0\u7248\u672c,MyISAM\u5b58\u50a8\u5f15\u64ce,\u7f51\u7ad9\u4e1a\u52a1(LNMP),\u6570\u636e\u91cf50G\u5de6\u53f3\n\u73b0\u8c61\u95ee\u9898: \u4e1a\u52a1\u538b\u529b\u5927\u7684\u65f6\u5019,\u975e\u5e38\u5361;\u7ecf\u5386\u8fc7\u5b95\u673a,\u4f1a\u6709\u90e8\u5206\u6570\u636e\u4e22\u5931.\n\u95ee\u9898\u5206\u6790:\n1.MyISAM\u5b58\u50a8\u5f15\u64ce\u8868\u7ea7\u9501,\u5728\u9ad8\u5e76\u53d1\u65f6,\u4f1a\u6709\u5f88\u9ad8\u9501\u7b49\u5f85\n2.MyISAM\u5b58\u50a8\u5f15\u64ce\u4e0d\u652f\u6301\u4e8b\u52a1,\u5728\u65ad\u7535\u65f6,\u4f1a\u6709\u53ef\u80fd\u4e22\u5931\u6570\u636e\n\u804c\u8d23\n1.\u76d1\u63a7\u9501\u7684\u60c5\u51b5:\u6709\u5f88\u591a\u7684\u8868\u9501\u7b49\u5f85\n2.\u5b58\u50a8\u5f15\u64ce\u67e5\u770b:\u6240\u6709\u8868\u9ed8\u8ba4\u662fMyISAM\n\u89e3\u51b3\u65b9\u6848:\n1.\u5347\u7ea7MySQL 5.6.10\u7248\u672c\n2. \u8fc1\u79fb\u6240\u6709\u8868\u5230\u65b0\u73af\u5883\n3. \u5f00\u542f\u53cc1\u5b89\u5168\u53c2\u6570\n<\/code><\/pre>\n<h1>4\u3001InnoDB\u5b58\u50a8\u5f15\u64ce\u4ecb\u7ecd<\/h1>\n<p><img decoding=\"async\" src=\"https:\/\/\/\/upload-images.jianshu.io\/upload_images\/16956686-a2c9c97b5ccd84ff.png?imageMogr2\/auto-orient\/strip|imageView2\/2\/w\/919\/format\/webp\" referrerpolicy=\"no-referrer\" alt=\"img\"><\/p>\n<p>image.png<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u5728MySQL5.5\u7248\u672c\u4e4b\u540e\uff0c\u9ed8\u8ba4\u7684\u5b58\u50a8\u5f15\u64ce\uff0c\u63d0\u4f9b\u9ad8\u53ef\u9760\u6027\u548c\u9ad8\u6027\u80fd\u3002\n<\/code><\/pre>\n<h2>4.1 \u4f18\u70b9<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-php\" lang=\"php\">1\u3001\u4e8b\u52a1\uff08Transaction\uff09\n2\u3001MVCC\uff08Multi-Version Concurrency Control\u591a\u7248\u672c\u5e76\u53d1\u63a7\u5236\uff09\n3\u3001\u884c\u7ea7\u9501(Row-level Lock)\n4\u3001ACSR\uff08Auto Crash Safey Recovery\uff09\u81ea\u52a8\u7684\u6545\u969c\u5b89\u5168\u6062\u590d\n5\u3001\u652f\u6301\u70ed\u5907\u4efd(Hot Backup)\n6\u3001Replication: Group Commit , GTID (Global Transaction ID) ,\u591a\u7ebf\u7a0b(Multi-Threads-SQL ) \n7\u3001\u5916\u952e\n<\/code><\/pre>\n<h2>4.2 \u7b14\u8bd5\u9898<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u8bf7\u4f60\u5217\u4e3eMySQL InnoDB\u5b58\u50a8\u4f18\u70b9\uff1f\n1.\u652f\u6301\u884c\u9501\uff0c\u91c7\u7528MVCC\u6765\u652f\u6301\u9ad8\u5e76\u53d1\uff0c\u6709\u53ef\u80fd\u6b7b\u9501\n2.\u652f\u6301\u4e8b\u52a1\n3.\u652f\u6301\u5916\u952e\n4.\u652f\u6301\u5d29\u6e83\u540e\u7684\u5b89\u5168\u6062\u590d\n\n\u8bf7\u4f60\u5217\u4e3e InooDB\u548cMyIsam\u7684\u533a\u522b\uff1f\n1) \u4e8b\u52a1\u652f\u6301\nMyISAM\u4e0d\u652f\u6301\u4e8b\u52a1\uff0c\u800cInnoDB\u652f\u6301\u3002InnoDB\u7684AUTOCOMMIT\u9ed8\u8ba4\u662f\u6253\u5f00\u7684\uff0c\u5373\u6bcf\u6761SQL\u8bed\u53e5\u4f1a\u9ed8\u8ba4\u88ab\u5c01\u88c5\u6210\u4e00\u4e2a\u4e8b\u52a1\uff0c\u81ea\u52a8\u63d0\u4ea4\uff0c\u8fd9\u6837\u4f1a\u5f71\u54cd\u901f\u5ea6\uff0c\u6240\u4ee5\u6700\u597d\u662f\u628a\u591a\u6761SQL\u8bed\u53e5\u663e\u793a\u653e\u5728begin\u548ccommit\u4e4b\u95f4\uff0c\u7ec4\u6210\u4e00\u4e2a\u4e8b\u52a1\u53bb\u63d0\u4ea4\u3002\nMyISAM\u662f\u975e\u4e8b\u52a1\u5b89\u5168\u578b\u7684\uff0c\u800cInnoDB\u662f\u4e8b\u52a1\u5b89\u5168\u578b\u7684\uff0c\u9ed8\u8ba4\u5f00\u542f\u81ea\u52a8\u63d0\u4ea4\uff0c\u5b9c\u5408\u5e76\u4e8b\u52a1\uff0c\u4e00\u540c\u63d0\u4ea4\uff0c\u51cf\u5c0f\u6570\u636e\u5e93\u591a\u6b21\u63d0\u4ea4\u5bfc\u81f4\u7684\u5f00\u9500\uff0c\u5927\u5927\u63d0\u9ad8\u6027\u80fd\u3002\n2) \u5b58\u50a8\u7ed3\u6784\nMyISAM\uff1a\u6bcf\u4e2aMyISAM\u5728\u78c1\u76d8\u4e0a\u5b58\u50a8\u6210\u4e09\u4e2a\u6587\u4ef6\u3002\u7b2c\u4e00\u4e2a\u6587\u4ef6\u7684\u540d\u5b57\u4ee5\u8868\u7684\u540d\u5b57\u5f00\u59cb\uff0c\u6269\u5c55\u540d\u6307\u51fa\u6587\u4ef6\u7c7b\u578b\u3002.frm\u6587\u4ef6\u5b58\u50a8\u8868\u5b9a\u4e49\u3002\u6570\u636e\u6587\u4ef6\u7684\u6269\u5c55\u540d\u4e3a.MYD (MYData)\u3002\u7d22\u5f15\u6587\u4ef6\u7684\u6269\u5c55\u540d\u662f.MYI (MYIndex)\u3002\nInnoDB\uff1a\u6240\u6709\u7684\u8868\u90fd\u4fdd\u5b58\u5728\u540c\u4e00\u4e2a\u6570\u636e\u6587\u4ef6\u4e2d\uff08\u4e5f\u53ef\u80fd\u662f\u591a\u4e2a\u6587\u4ef6\uff0c\u6216\u8005\u662f\u72ec\u7acb\u7684\u8868\u7a7a\u95f4\u6587\u4ef6\uff09\uff0cInnoDB\u8868\u7684\u5927\u5c0f\u53ea\u53d7\u9650\u4e8e\u64cd\u4f5c\u7cfb\u7edf\u6587\u4ef6\u7684\u5927\u5c0f\uff0c\u4e00\u822c\u4e3a2GB\u3002\n3) \u5b58\u50a8\u7a7a\u95f4\nMyISAM\uff1a\u53ef\u88ab\u538b\u7f29\uff0c\u5b58\u50a8\u7a7a\u95f4\u8f83\u5c0f\u3002\u652f\u6301\u4e09\u79cd\u4e0d\u540c\u7684\u5b58\u50a8\u683c\u5f0f\uff1a\u9759\u6001\u8868(\u9ed8\u8ba4\uff0c\u4f46\u662f\u6ce8\u610f\u6570\u636e\u672b\u5c3e\u4e0d\u80fd\u6709\u7a7a\u683c\uff0c\u4f1a\u88ab\u53bb\u6389)\u3001\u52a8\u6001\u8868\u3001\u538b\u7f29\u8868\u3002\n<\/code><\/pre>\n<h1>5. \u5b58\u50a8\u5f15\u64ce\u67e5\u770b<\/h1>\n<h2>5.1 \u4f7f\u7528 SELECT \u786e\u8ba4\u4f1a\u8bdd\u5b58\u50a8\u5f15\u64ce<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">SELECT @@default_storage_engine;\n## 5.2 \u5b58\u50a8\u5f15\u64ce(\u4e0d\u4ee3\u8868\u751f\u4ea7\u64cd\u4f5c)\n\u4f1a\u8bdd\u7ea7\u522b:\nset default_storage_engine=myisam;\n\u5168\u5c40\u7ea7\u522b(\u4ec5\u5f71\u54cd\u65b0\u4f1a\u8bdd):\nset global default_storage_engine=myisam;\n\u91cd\u542f\u4e4b\u540e,\u6240\u6709\u53c2\u6570\u5747\u5931\u6548.\n\u5982\u679c\u8981\u6c38\u4e45\u751f\u6548:\n\u5199\u5165\u914d\u7f6e\u6587\u4ef6\nvim \/etc\/my.cnf\n[mysqld]\ndefault_storage_engine=myisam\n\u5b58\u50a8\u5f15\u64ce\u662f\u8868\u7ea7\u522b\u7684,\u6bcf\u4e2a\u8868\u521b\u5efa\u65f6\u53ef\u4ee5\u6307\u5b9a\u4e0d\u540c\u7684\u5b58\u50a8\u5f15\u64ce,\u4f46\u662f\u6211\u4eec\u5efa\u8bae\u7edf\u4e00\u4e3ainnodb.\n<\/code><\/pre>\n<h2>5.3 SHOW \u786e\u8ba4\u6bcf\u4e2a\u8868\u7684\u5b58\u50a8\u5f15\u64ce\uff1a<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">SHOW CREATE TABLE City\\G;\nSHOW TABLE STATUS LIKE 'CountryLanguage'\\G\n<\/code><\/pre>\n<h2>5.4 INFORMATION_SCHEMA \u786e\u8ba4\u6bcf\u4e2a\u8868\u7684\u5b58\u50a8\u5f15\u64ce<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">[world]&gt;select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');\nMaster [world]&gt;show table status;\nMaster [world]&gt;show create table city;\n<\/code><\/pre>\n<h2>5.5 \u4fee\u6539\u4e00\u4e2a\u8868\u7684\u5b58\u50a8\u5f15\u64ce<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-mysql\" lang=\"mysql\">db01 [oldboy]&gt;alter table ocp.oder engine innodb;\n\u6ce8\u610f\uff1a\u6b64\u547d\u4ee4\u6211\u4eec\u7ecf\u5e38\u4f7f\u7528\u4ed6\uff0c\u8fdb\u884cinnodb\u8868\u7684\u788e\u7247\u6574\u7406\n<\/code><\/pre>\n<h2>5.6 \u5e73\u5e38\u5904\u7406\u8fc7\u7684MySQL\u95ee\u9898&#8211;\u788e\u7247\u5904\u7406<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u73af\u5883:centos7.4,MySQL 5.7.20,InnoDB\u5b58\u50a8\u5f15\u64ce\n\u4e1a\u52a1\u7279\u70b9:\u6570\u636e\u91cf\u7ea7\u8f83\u5927,\u7ecf\u5e38\u9700\u8981\u6309\u6708\u5220\u9664\u5386\u53f2\u6570\u636e.\n\u95ee\u9898:\u78c1\u76d8\u7a7a\u95f4\u5360\u7528\u5f88\u5927,\u4e0d\u91ca\u653e\n\u5904\u7406\u65b9\u6cd5:\n\u4ee5\u524d:\u5c06\u6570\u636e\u903b\u8f91\u5bfc\u51fa,\u624b\u5de5truncat\u8868,\u7136\u540e\u5bfc\u5165\u8fdb\u53bb\n\u73b0\u5728:\n\u5bf9\u8868\u8fdb\u884c\u6309\u6708\u8fdb\u884c\u5206\u8868(partition,\u4e2d\u95f4\u4ef6)\n\u4e1a\u52a1\u66ff\u6362\u4e3atruncate\u65b9\u5f0f\n\u5b9a\u671f\u8fdb\u884c\u788e\u7247\u6574\u7406\n<\/code><\/pre>\n<h2>5.6  \u6269\u5c55:\u5982\u4f55\u6279\u91cf\u4fee\u6539<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u9700\u6c42:\u5c06zabbix\u5e93\u4e2d\u7684\u6240\u6709\u8868,innodb\u66ff\u6362\u4e3atokudb\nselect concat(\"alter table zabbix.\",table_name,\" engine tokudb;\") from\ninformation_schema.tables where table_schema='zabbix' into outfile '\/tmp\/tokudb.sql';\n<\/code><\/pre>\n<h1>6\u3001InnoDB\u5b58\u50a8\u5f15\u64ce\u7269\u7406\u5b58\u50a8\u7ed3\u6784<\/h1>\n<h2>6.0 \u6700\u76f4\u89c2\u7684\u5b58\u50a8\u65b9\u5f0f(\/data\/mysql\/data)<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">ibdata1\uff1a\u7cfb\u7edf\u6570\u636e\u5b57\u5178\u4fe1\u606f(\u7edf\u8ba1\u4fe1\u606f)\uff0cUNDO\u8868\u7a7a\u95f4\u7b49\u6570\u636e\nib_logfile0 ~ ib_logfile1: REDO\u65e5\u5fd7\u6587\u4ef6\uff0c\u4e8b\u52a1\u65e5\u5fd7\u6587\u4ef6\u3002\nibtmp1\uff1a \u4e34\u65f6\u8868\u7a7a\u95f4\u78c1\u76d8\u4f4d\u7f6e\uff0c\u5b58\u50a8\u4e34\u65f6\u8868\nfrm\uff1a\u5b58\u50a8\u8868\u7684\u5217\u4fe1\u606f\nibd\uff1a\u8868\u7684\u6570\u636e\u884c\u548c\u7d22\u5f15\n<\/code><\/pre>\n<h2>6.1 \u8868\u7a7a\u95f4(Tablespace)<\/h2>\n<h3>6.1.1\u3001\u5171\u4eab\u8868\u7a7a\u95f4<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">ibdata1:\u6574\u4e2a\u5e93\u7684\u7edf\u8ba1\u4fe1\u606f+Undo\nibd    :\u6570\u636e\u884c\u548c\u7d22\u5f15\n\u9700\u8981\u5c06\u6240\u6709\u6570\u636e\u5b58\u50a8\u5230\u540c\u4e00\u4e2a\u8868\u7a7a\u95f4\u4e2d \uff0c\u7ba1\u7406\u6bd4\u8f83\u6df7\u4e71\n5.5\u7248\u672c\u51fa\u73b0\u7684\u7ba1\u7406\u6a21\u5f0f\uff0c\u4e5f\u662f\u9ed8\u8ba4\u7684\u7ba1\u7406\u6a21\u5f0f\u3002\n5.6\u7248\u672c\u4ee5\uff0c\u5171\u4eab\u8868\u7a7a\u95f4\u4fdd\u7559\uff0c\u53ea\u7528\u6765\u5b58\u50a8:\u6570\u636e\u5b57\u5178\u4fe1\u606f,undo,\u4e34\u65f6\u8868\u3002\n5.7 \u7248\u672c,\u4e34\u65f6\u8868\u88ab\u72ec\u7acb\u51fa\u6765\u4e86\n8.0\u7248\u672c,undo\u4e5f\u88ab\u72ec\u7acb\u51fa\u53bb\u4e86\n<\/code><\/pre>\n<p>\u5177\u4f53\u53d8\u5316\u53c2\u8003\u5b98\u65b9\u6587\u6863:<br \/>\n<a href=\"https:\/\/links.jianshu.com\/go?to=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.6%2Fen%2Finnodb-architecture.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-architecture.html<\/a><br \/>\n<a href=\"https:\/\/links.jianshu.com\/go?to=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Finnodb-architecture.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-architecture.html<\/a><br \/>\n<a href=\"https:\/\/links.jianshu.com\/go?to=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.8%2Fen%2Finnodb-architecture.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.8\/en\/innodb-architecture.html<\/a><\/p>\n<h3>6.1.2 \u5171\u4eab\u8868\u7a7a\u95f4\u8bbe\u7f6e<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u5171\u4eab\u8868\u7a7a\u95f4\u8bbe\u7f6e(\u5728\u642d\u5efaMySQL\u65f6\uff0c\u521d\u59cb\u5316\u6570\u636e\u4e4b\u524d\u8bbe\u7f6e\u5230\u53c2\u6570\u6587\u4ef6\u4e2d)\n[(none)]&gt;select @@innodb_data_file_path;\n[(none)]&gt;show variables like '%extend%';\n\ninnodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend\ninnodb_autoextend_increment=64\n<\/code><\/pre>\n<h3>6.1.3 \u72ec\u7acb\u8868\u7a7a\u95f4<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-cpp\" lang=\"cpp\">\u4ece5.6\uff0c\u9ed8\u8ba4\u8868\u7a7a\u95f4\u4e0d\u518d\u4f7f\u7528\u5171\u4eab\u8868\u7a7a\u95f4\uff0c\u66ff\u6362\u4e3a\u72ec\u7acb\u8868\u7a7a\u95f4\u3002\n\u4e3b\u8981\u5b58\u50a8\u7684\u662f\u7528\u6237\u6570\u636e\n\u5b58\u50a8\u7279\u70b9\u4e3a\uff1a\u4e00\u4e2a\u8868\u4e00\u4e2aibd\u6587\u4ef6\uff0c\u5b58\u50a8\u6570\u636e\u884c\u548c\u7d22\u5f15\u4fe1\u606f\n\u57fa\u672c\u8868\u7ed3\u6784\u5143\u6570\u636e\u5b58\u50a8\uff1a\nxxx.frm\n\u6700\u7ec8\u7ed3\u8bba\uff1a\n                \u5143\u6570\u636e            \u6570\u636e\u884c+\u7d22\u5f15\nmysql\u8868\u6570\u636e    =\uff08ibdataX+frm\uff09+ibd(\u6bb5\u3001\u533a\u3001\u9875)\n        DDL             DML+DQL\n\nMySQL\u7684\u5b58\u50a8\u5f15\u64ce\u65e5\u5fd7\uff1a\nRedo Log: ib_logfile0  ib_logfile1\uff0c\u91cd\u505a\u65e5\u5fd7\nUndo Log: ibdata1 ibdata2(\u5b58\u50a8\u5728\u5171\u4eab\u8868\u7a7a\u95f4\u4e2d)\uff0c\u56de\u6eda\u65e5\u5fd7\n\u4e34\u65f6\u8868:ibtmp1\uff0c\u5728\u505ajoin union\u64cd\u4f5c\u4ea7\u751f\u4e34\u65f6\u6570\u636e\uff0c\u7528\u5b8c\u5c31\u81ea\u52a8\n<\/code><\/pre>\n<h3>6.1.4 \u72ec\u7acb\u8868\u7a7a\u95f4\u8bbe\u7f6e\u95ee\u9898<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-dart\" lang=\"dart\">db01 [(none)]&gt;select @@innodb_file_per_table;\n+-------------------------+\n| @@innodb_file_per_table |\n+-------------------------+\n|                      1 |\n+-------------------------+\n\n\u72ec\u7acb\u8868\u7a7a\u95f4\u8fc1\u79fb  \n(1)\u521b\u5efa\u548c\u539f\u8868\u7ed3\u6784\u4e00\u81f4\u7684\u7a7a\u8868\n(2)\u5c06\u7a7a\u8868\u7684ibd\u6587\u4ef6\u5220\u9664\nalter table city dicard tablespace;\n(3)\u5c06\u539f\u8868\u7684ibd\u62f7\u8d1d\u8fc7\u6765,\u5e76\u4e14\u4fee\u6539\u6743\u9650\n(4)\u5c06\u539f\u8868ibd\u8fdb\u884c\u5bfc\u5165\nalter table city import tablespace;\n<\/code><\/pre>\n<h3>6.1.5 \u771f\u5b9e\u7684\u5b66\u751f\u6848\u4f8b<\/h3>\n<p>\u6848\u4f8b\u80cc\u666f:<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u786c\u4ef6\u53ca\u8f6f\u4ef6\u73af\u5883:\n\u8054\u60f3\u670d\u52a1\u5668\uff08IBM\uff09 \n\u78c1\u76d8500G \u6ca1\u6709raid\ncentos 6.8\nmysql 5.6.33  innodb\u5f15\u64ce  \u72ec\u7acb\u8868\u7a7a\u95f4\n\u5907\u4efd\u6ca1\u6709\uff0c\u65e5\u5fd7\u4e5f\u6ca1\u5f00\n\n\u5f00\u53d1\u7528\u6237\u4e13\u7528\u5e93:\njira(bug\u8ffd\u8e2a) \u3001 confluence(\u5185\u90e8\u77e5\u8bc6\u5e93)    ------&gt;LNMT\n<\/code><\/pre>\n<p>\u6545\u969c\u63cf\u8ff0:<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u65ad\u7535\u4e86\uff0c\u542f\u52a8\u5b8c\u6210\u540e\u201c\/\u201d \u53ea\u8bfb\n\u627e\u4e00\u5757\u65b0\u786c\u76d8\uff0c\u628a\u574f\u786c\u76d8\u53d6\u51fa\u6765\uff0c\u5230\u53e6\u4e00\u53f0\u597d\u7684\u670d\u52a1\u5668\u4e2d\uff0c\u7528dd\u547d\u4ee4\u62f7\u8d1d  dd if=\/dev\/sda of=\/dev\/sdc\nfsck  \u91cd\u542f,\u7cfb\u7edf\u6210\u529f\u542f\u52a8,mysql\u542f\u52a8\u4e0d\u4e86\u3002\n\u7ed3\u679c\uff1aconfulence\u5e93\u5728  \uff0c jira\u5e93\u4e0d\u89c1\u4e86\n<\/code><\/pre>\n<p>\u5b66\u5458\u6c42\u52a9\u5185\u5bb9:<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u6c42\u52a9\uff1a\n\u8fd9\u79cd\u60c5\u51b5\u600e\u4e48\u6062\u590d\uff1f\n\u6211\u95ee\uff1a\n\u6709\u5907\u4efd\u6ca1\n\u6c42\u52a9\uff1a\n\u8fde\u4e8c\u8fdb\u5236\u65e5\u5fd7\u90fd\u6ca1\u6709\uff0c\u6ca1\u6709\u5907\u4efd\uff0c\u6ca1\u6709\u4e3b\u4ece\n\u6211\u8bf4\uff1a\n\u6ca1\u62db\u4e86\uff0cjira\u9700\u8981\u786c\u76d8\u6062\u590d\u4e86\u3002\n\u6c42\u52a9\uff1a\n1\u3001jira\u95ee\u9898\u62c9\u5012\u4e2d\u5173\u6751\u4e86\n2\u3001\u80fd\u4e0d\u80fd\u6682\u65f6\u628aconfulence\u5e93\u5148\u6253\u5f00\u7528\u7740\n\u5c06\u751f\u4ea7\u5e93confulence\uff0c\u62f7\u8d1d\u52301:1\u865a\u62df\u673a\u4e0a\/var\/lib\/mysql,\u76f4\u63a5\u8bbf\u95ee\u65f6\u8bbf\u95ee\u4e0d\u4e86\u7684\n\n\u95ee\uff1a\u6709\u6ca1\u6709\u5de5\u5177\u80fd\u76f4\u63a5\u8bfb\u53d6ibd\n\u6211\u8bf4\uff1a\u6211\u67e5\u67e5\uff0c\u6700\u540e\u53d1\u73b0\u6ca1\u6709\n<\/code><\/pre>\n<p>\u6211\u60f3\u51fa\u4e00\u4e2a\u529e\u6cd5\u6765\uff1a<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u8868\u7a7a\u95f4\u8fc1\u79fb:\ncreate table xxx\nalter table  confulence.t1 discard tablespace;\nalter table confulence.t1 import tablespace;\n\u865a\u62df\u673a\u6d4b\u8bd5\u53ef\u884c\u3002\n<\/code><\/pre>\n<p>\u5904\u7406\u95ee\u9898\u601d\u8def:<\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">confulence\u5e93\u4e2d\u4e00\u5171\u6709107\u5f20\u8868\u3002\n1\u3001\u521b\u5efa107\u548c\u548c\u539f\u6765\u4e00\u6a21\u4e00\u6837\u7684\u8868\u3002\n\u4ed6\u67092016\u5e74\u7684\u5386\u53f2\u5e93\uff0c\u6211\u8ba9\u4ed6\u53bb\u4ed6\u540c\u65f6\u7535\u8111\u4e0a mysqldump\u5907\u4efdconfulence\u5e93\nmysqldump -uroot -ppassw0rd -B  confulence --no-data &gt;test.sql\n\u62ff\u5230\u4f60\u7684\u6d4b\u8bd5\u5e93\uff0c\u8fdb\u884c\u6062\u590d\n\u5230\u8fd9\u6b65\u4e3a\u6b62\uff0c\u8868\u7ed3\u6784\u6709\u4e86\u3002\n2\u3001\u8868\u7a7a\u95f4\u5220\u9664\u3002\nselect concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '\/tmp\/discad.sql';\nsource \/tmp\/discard.sql\n\u6267\u884c\u8fc7\u7a0b\u4e2d\u53d1\u73b0\uff0c\u670920-30\u4e2a\u8868\u65e0\u6cd5\u6210\u529f\u3002\u4e3b\u5916\u952e\u5173\u7cfb\n\u5f88\u7edd\u671b\uff0c\u4e00\u4e2a\u8868\u4e00\u4e2a\u8868\u5206\u6790\u8868\u7ed3\u6784\uff0c\u5f88\u75db\u82e6\u3002\nset foreign_key_checks=0 \u8df3\u8fc7\u5916\u952e\u68c0\u67e5\u3002\n\u628a\u6709\u95ee\u9898\u7684\u8868\u8868\u7a7a\u95f4\u4e5f\u5220\u6389\u4e86\u3002\n3\u3001\u62f7\u8d1d\u751f\u4ea7\u4e2dconfulence\u5e93\u4e0b\u7684\u6240\u6709\u8868\u7684ibd\u6587\u4ef6\u62f7\u8d1d\u5230\u51c6\u5907\u597d\u7684\u73af\u5883\u4e2d\nselect concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '\/tmp\/discad.sql';\n4\u3001\u9a8c\u8bc1\u6570\u636e\n\u8868\u90fd\u53ef\u4ee5\u8bbf\u95ee\u4e86\uff0c\u6570\u636e\u633d\u56de\u5230\u4e86\u51fa\u73b0\u95ee\u9898\u65f6\u523b\u7684\u72b6\u6001\n<\/code><\/pre>\n<h1>8\u3001\u4e8b\u52a1\u7684ACID\u7279\u6027<\/h1>\n<p><strong>Atomic\uff08\u539f\u5b50\u6027\uff09<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u6240\u6709\u8bed\u53e5\u4f5c\u4e3a\u4e00\u4e2a\u5355\u5143\u5168\u90e8\u6210\u529f\u6267\u884c\u6216\u5168\u90e8\u53d6\u6d88\u3002\u4e0d\u80fd\u51fa\u73b0\u4e2d\u95f4\u72b6\u6001\u3002\n<\/code><\/pre>\n<p><strong>Consistent\uff08\u4e00\u81f4\u6027\uff09<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u5982\u679c\u6570\u636e\u5e93\u5728\u4e8b\u52a1\u5f00\u59cb\u65f6\u5904\u4e8e\u4e00\u81f4\u72b6\u6001\uff0c\u5219\u5728\u6267\u884c\u8be5\u4e8b\u52a1\u671f\u95f4\u5c06\u4fdd\u7559\u4e00\u81f4\u72b6\u6001\u3002\n<\/code><\/pre>\n<p><strong>Isolated\uff08\u9694\u79bb\u6027\uff09<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u4e8b\u52a1\u4e4b\u95f4\u4e0d\u76f8\u4e92\u5f71\u54cd\u3002\n<\/code><\/pre>\n<p><strong>Durable\uff08\u6301\u4e45\u6027\uff09<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u4e8b\u52a1\u6210\u529f\u5b8c\u6210\u540e\uff0c\u6240\u505a\u7684\u6240\u6709\u66f4\u6539\u90fd\u4f1a\u51c6\u786e\u5730\u8bb0\u5f55\u5728\u6570\u636e\u5e93\u4e2d\u3002\u6240\u505a\u7684\u66f4\u6539\u4e0d\u4f1a\u4e22\u5931\u3002\n<\/code><\/pre>\n<h1>9\u3001\u4e8b\u52a1\u7684\u751f\u547d\u5468\u671f\uff08\u4e8b\u52a1\u63a7\u5236\u8bed\u53e5\uff09<\/h1>\n<h2>9.1 \u4e8b\u52a1\u7684\u5f00\u59cb<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">begin\n\u8bf4\u660e:\u57285.5 \u4ee5\u4e0a\u7684\u7248\u672c\uff0c\u4e0d\u9700\u8981\u624b\u5de5begin\uff0c\u53ea\u8981\u4f60\u6267\u884c\u7684\u662f\u4e00\u4e2aDML\uff0c\u4f1a\u81ea\u52a8\u5728\u524d\u9762\u52a0\u4e00\u4e2abegin\u547d\u4ee4\u3002\n<\/code><\/pre>\n<h2>9.2 \u4e8b\u52a1\u7684\u6807\u51c6\u8bed\u53e5<\/h2>\n<pre><code class=\"language-mysql\" lang=\"mysql\">DML :\ninsert\nupdate\ndelete\nmysql&gt; use wor1d;\nmysq1&gt; update city set countrycode= 'CHN' where id=1;\nmysql&gt; update city set countrycode= ' CHN' where id=2 ;\nmysql&gt; update city set countrycode= 'CHN' where id=3 ;\n<\/code><\/pre>\n<p>&nbsp;<\/p>\n<h2>9.3 \u4e8b\u52a1\u7684\u7ed3\u675f<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">commit\uff1a\u63d0\u4ea4\u4e8b\u52a1\n\u5b8c\u6210\u4e00\u4e2a\u4e8b\u52a1\uff0c\u4e00\u65e6\u4e8b\u52a1\u63d0\u4ea4\u6210\u529f \uff0c\u5c31\u8bf4\u660e\u5177\u5907ACID\u7279\u6027\u4e86\u3002\nrollback \uff1a\u56de\u6eda\u4e8b\u52a1\n\u5c06\u5185\u5b58\u4e2d\uff0c\u5df2\u6267\u884c\u8fc7\u7684\u64cd\u4f5c\uff0c\u56de\u6eda\u56de\u53bb\n<\/code><\/pre>\n<h2>9.4 \u81ea\u52a8\u63d0\u4ea4\u7b56\u7565\uff08autocommit\uff09<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">db01 [(none)]&gt;select @@autocommit;\ndb01 [(none)]&gt;set autocommit=0;\ndb01 [(none)]&gt;set global autocommit=0;\n\u6ce8\uff1a\n\u81ea\u52a8\u63d0\u4ea4\u662f\u5426\u6253\u5f00\uff0c\u4e00\u822c\u5728\u6709\u4e8b\u52a1\u9700\u6c42\u7684MySQL\u4e2d\uff0c\u5c06\u5176\u5173\u95ed\n\u4e0d\u7ba1\u6709\u6ca1\u6709\u4e8b\u52a1\u9700\u6c42\uff0c\u6211\u4eec\u4e00\u822c\u4e5f\u90fd\u5efa\u8bae\u8bbe\u7f6e\u4e3a0\uff0c\u53ef\u4ee5\u5f88\u5927\u7a0b\u5ea6\u4e0a\u63d0\u9ad8\u6570\u636e\u5e93\u6027\u80fd\n(1)\nset autocommit=0;   \t\u4f1a\u8bdd\u7ea7\u522b\n\u5373\u65f6\u751f\u6548\uff0c\u53ea\u5f71\u54cd\u5f53\u524d\u767b\u5f55\u4f1a\u8bdd\nset global autocommit=0;\u5168\u5c40\u7ea7\u522b\n\u65ad\u5f00\u7a97\u53e3\u91cd\u8fde\u540e\u751f\u6548\uff0c\u5f71\u54cd\u5230\u6240\u6709\u65b0\u5f00\u7684\u4f1a\u8bdd\n(2)\u6c38\u4e45\u4fee\u6539\uff0c\u9700\u8981\u91cd\u542f\u64cd\u4f5c\nvim \/etc\/my.cnf\nautocommit=0     \n<\/code><\/pre>\n<h2>9.5  \u9690\u5f0f\u63d0\u4ea4\u8bed\u53e5<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">\u7528\u4e8e\u9690\u5f0f\u63d0\u4ea4\u7684 SQL \u8bed\u53e5\uff1a\nbegin \na\nb\ncreate database\n\nSET AUTOCOMMIT = 1\n\n\u5bfc\u81f4\u63d0\u4ea4\u7684\u975e\u4e8b\u52a1\u8bed\u53e5\uff1a\nDDL\u8bed\u53e5\uff1a \uff08ALTER\u3001CREATE \u548c DROP\uff09\nDCL\u8bed\u53e5\uff1a \uff08GRANT\u3001REVOKE \u548c SET PASSWORD\uff09\n\u9501\u5b9a\u8bed\u53e5\uff1a\uff08LOCK TABLES \u548c UNLOCK TABLES\uff09\n\u5bfc\u81f4\u9690\u5f0f\u63d0\u4ea4\u7684\u8bed\u53e5\u793a\u4f8b\uff1a\nTRUNCATE TABLE\nLOAD DATA INFILE\nSELECT FOR UPDATE\n<\/code><\/pre>\n<h2>9.6 \u5f00\u59cb\u4e8b\u52a1\u6d41\u7a0b\uff1a<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">1\u3001\u68c0\u67e5autocommit\u662f\u5426\u4e3a\u5173\u95ed\u72b6\u6001\nselect @@autocommit;\n\u6216\u8005\uff1a\nshow variables like 'autocommit';\n2\u3001\u5f00\u542f\u4e8b\u52a1,\u5e76\u7ed3\u675f\u4e8b\u52a1\nbegin\ndelete from student where name='alexsb';\nupdate student set name='alexsb' where name='alex';\nrollback;\n\nbegin\ndelete from student where name='alexsb';\nupdate student set name='alexsb' where name='alex';\ncommit;\n<\/code><\/pre>\n<h1>10. InnoDB \u4e8b\u52a1\u7684ACID\u5982\u4f55\u4fdd\u8bc1?<\/h1>\n<h2>10.0 \u4e00\u4e9b\u6982\u5ff5<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">redo log ---&gt; \u91cd\u505a\u65e5\u5fd7 ib_logfile0~1   50M   , \u8f6e\u8be2\u4f7f\u7528\nredo log buffer ---&gt; redo\u5185\u5b58\u533a\u57df\nibd     ----&gt; \u5b58\u50a8 \u6570\u636e\u884c\u548c\u7d22\u5f15 \nbuffer pool ---&gt;\u7f13\u51b2\u533a\u6c60,\u6570\u636e\u548c\u7d22\u5f15\u7684\u7f13\u51b2\nLSN : \u65e5\u5fd7\u5e8f\u5217\u53f7 \n\u78c1\u76d8\u6570\u636e\u9875,redo\u6587\u4ef6,buffer pool,redo buffer\nMySQL \u6bcf\u6b21\u6570\u636e\u5e93\u542f\u52a8,\u90fd\u4f1a\u6bd4\u8f83\u78c1\u76d8\u6570\u636e\u9875\u548credolog\u7684LSN,\u5fc5\u987b\u8981\u6c42\u4e24\u8005LSN\u4e00\u81f4\u6570\u636e\u5e93\u624d\u80fd\u6b63\u5e38\u542f\u52a8\nWAL : write ahead log \u65e5\u5fd7\u4f18\u5148\u5199\u7684\u65b9\u5f0f\u5b9e\u73b0\u6301\u4e45\u5316\n\u810f\u9875: \u5185\u5b58\u810f\u9875,\u5185\u5b58\u4e2d\u53d1\u751f\u4e86\u4fee\u6539,\u6ca1\u5199\u5165\u5230\u78c1\u76d8\u4e4b\u524d,\u6211\u4eec\u628a\u5185\u5b58\u9875\u79f0\u4e4b\u4e3a\u810f\u9875.\nCKPT:Checkpoint,\u68c0\u67e5\u70b9,\u5c31\u662f\u5c06\u810f\u9875\u5237\u5199\u5230\u78c1\u76d8\u7684\u52a8\u4f5c\nTXID: \u4e8b\u52a1\u53f7,InnoDB\u4f1a\u4e3a\u6bcf\u4e00\u4e2a\u4e8b\u52a1\u751f\u6210\u4e00\u4e2a\u4e8b\u52a1\u53f7,\u4f34\u968f\u7740\u6574\u4e2a\u4e8b\u52a1.\n<\/code><\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/\/\/upload-images.jianshu.io\/upload_images\/16956686-babda887346b7427.png?imageMogr2\/auto-orient\/strip|imageView2\/2\/w\/767\/format\/webp\" referrerpolicy=\"no-referrer\" alt=\"img\"><\/p>\n<p>image<\/p>\n<h2>10.1 redo log<\/h2>\n<h3>10.1.1 Redo\u662f\u4ec0\u4e48\uff1f<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">redo,\u987e\u540d\u601d\u4e49\u201c\u91cd\u505a\u65e5\u5fd7\u201d\uff0c\u662f\u4e8b\u52a1\u65e5\u5fd7\u7684\u4e00\u79cd\u3002\n<\/code><\/pre>\n<h3>10.1.2 \u4f5c\u7528\u662f\u4ec0\u4e48\uff1f<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u5728\u4e8b\u52a1ACID\u8fc7\u7a0b\u4e2d\uff0c\u5b9e\u73b0\u7684\u662f\u201cD\u201d\u6301\u4e45\u5316\u7684\u4f5c\u7528\u3002\u5bf9\u4e8eAC\u4e5f\u6709\u76f8\u5e94\u7684\u4f5c\u7528\n\n(1)\u8bb0\u5f55\u4e86\u5185\u5b58\u6570\u636e\u9875\u7684\u53d8\u5316.\n(2)\u63d0\u4f9b\u5feb\u901f\u7684\u6301\u4e45\u5316\u529f\u80fd(WAL)\n(3)CSR\u8fc7\u7a0b\u4e2d\u5b9e\u73b0\u524d\u6eda\u7684\u64cd\u4f5c(\u78c1\u76d8\u6570\u636e\u9875\u548credo\u65e5\u5fd7LSN\u4e00\u81f4)\n<\/code><\/pre>\n<h3>10.1.3 redo\u65e5\u5fd7\u4f4d\u7f6e<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">redo\u7684\u65e5\u5fd7\u6587\u4ef6\uff1aiblogfile0 iblogfile1\n<\/code><\/pre>\n<h3>10.1.4 redo buffer<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">redo\u7684buffer:\u6570\u636e\u9875\u7684\u53d8\u5316\u4fe1\u606f+\u6570\u636e\u9875\u5f53\u65f6\u7684LSN\u53f7\nLSN\uff1a\u65e5\u5fd7\u5e8f\u5217\u53f7  \u78c1\u76d8\u6570\u636e\u9875\u3001\u5185\u5b58\u6570\u636e\u9875\u3001redo buffer\u3001redolog\n<\/code><\/pre>\n<h3>10.1.5 redo\u7684\u5237\u65b0\u7b56\u7565<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">commit;\n\u5237\u65b0\u5f53\u524d\u4e8b\u52a1\u7684redo buffer\u5230\u78c1\u76d8\n\u8fd8\u4f1a\u987a\u4fbf\u5c06\u4e00\u90e8\u5206redo buffer\u4e2d\u6ca1\u6709\u63d0\u4ea4\u7684\u4e8b\u52a1\u65e5\u5fd7\u4e5f\u5237\u65b0\u5230\u78c1\u76d8\n\nMySQL\u5728\u542f\u52a8\u65f6,\u5fc5\u987b\u4fdd\u8bc1redo\u65e5\u5fd7\u6587\u4ef6\u548c\u6570\u636e\u6587\u4ef6LSN\u5fc5\u987b\u4e00\u81f4, \u5982\u679c\u4e0d\u4e00\u81f4\u5c31\u4f1a\u89e6\u53d1CSR,\u6700\u7ec8\u4fdd\u8bc1\u4e00\u81f4\n<\/code><\/pre>\n<h3>10.1.6 MySQL CSR\u2014\u2014\u524d\u6eda<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">MySQL\u5728\u542f\u52a8\u65f6,\u5fc5\u987b\u4fdd\u8bc1redo\u65e5\u5fd7\u6587\u4ef6\u548c\u6570\u636e\u6587\u4ef6LSN\u5fc5\u987b\u4e00\u81f4, \u5982\u679c\u4e0d\u4e00\u81f4\u5c31\u4f1a\u89e6\u53d1CSR,\u6700\u7ec8\u4fdd\u8bc1\u4e00\u81f4\n\u60c5\u51b5\u4e00:\n\u6211\u4eec\u505a\u4e86\u4e00\u4e2a\u4e8b\u52a1,begin;update;commit.\n1.\u5728begin ,\u4f1a\u7acb\u5373\u5206\u914d\u4e00\u4e2aTXID=tx_01.\n2.update\u65f6,\u4f1a\u5c06\u9700\u8981\u4fee\u6539\u7684\u6570\u636e\u9875(dp_01,LSN=101),\u52a0\u8f7d\u5230data buffer\u4e2d\n3.DBWR\u7ebf\u7a0b,\u4f1a\u8fdb\u884cdp_01\u6570\u636e\u9875\u4fee\u6539\u66f4\u65b0,\u5e76\u66f4\u65b0LSN=102\n4.LOGBWR\u65e5\u5fd7\u5199\u7ebf\u7a0b,\u4f1a\u5c06dp_01\u6570\u636e\u9875\u7684\u53d8\u5316+LSN+TXID\u5b58\u50a8\u5230redobuffer\n5. \u6267\u884ccommit\u65f6,LGWR\u65e5\u5fd7\u5199\u7ebf\u7a0b\u4f1a\u5c06redobuffer\u4fe1\u606f\u5199\u5165redolog\u65e5\u5fd7\u6587\u4ef6\u4e2d,\u57fa\u4e8eWAL\u539f\u5219,\n\u5728\u65e5\u5fd7\u5b8c\u5168\u5199\u5165\u78c1\u76d8\u540e,commit\u547d\u4ee4\u624d\u6267\u884c\u6210\u529f,(\u4f1a\u5c06\u6b64\u65e5\u5fd7\u6253\u4e0acommit\u6807\u8bb0)\n6.\u5047\u5982\u6b64\u65f6\u5b95\u673a,\u5185\u5b58\u810f\u9875\u6ca1\u6709\u6765\u5f97\u53ca\u5199\u5165\u78c1\u76d8,\u5185\u5b58\u6570\u636e\u5168\u90e8\u4e22\u5931\n7.MySQL\u518d\u6b21\u91cd\u542f\u65f6,\u5fc5\u987b\u8981redolog\u548c\u78c1\u76d8\u6570\u636e\u9875\u7684LSN\u662f\u4e00\u81f4\u7684.\u4f46\u662f,\u6b64\u65f6dp_01,TXID=tx_01\u78c1\u76d8\u662fLSN=101,dp_01,TXID=tx_01,redolog\u4e2dLSN=102\nMySQL\u6b64\u65f6\u65e0\u6cd5\u6b63\u5e38\u542f\u52a8,MySQL\u89e6\u53d1CSR.\u5728\u5185\u5b58\u8ffd\u5e73LSN\u53f7,\u89e6\u53d1ckpt,\u5c06\u5185\u5b58\u6570\u636e\u9875\u66f4\u65b0\u5230\u78c1\u76d8,\u4ece\u800c\u4fdd\u8bc1\u78c1\u76d8\u6570\u636e\u9875\u548credolog LSN\u4e00\u503c.\u8fd9\u65f6MySQL\u6b63\u957f\u542f\u52a8\n\u4ee5\u4e0a\u7684\u5de5\u4f5c\u8fc7\u7a0b,\u6211\u4eec\u628a\u5b83\u79f0\u4e4b\u4e3a\u57fa\u4e8eREDO\u7684\"\u524d\u6eda\u64cd\u4f5c\"\n<\/code><\/pre>\n<h2>11.2 undo \u56de\u6eda\u65e5\u5fd7<\/h2>\n<h3>11.2.1 undo\u662f\u4ec0\u4e48\uff1f<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">undo,\u987e\u540d\u601d\u4e49\u201c\u56de\u6eda\u65e5\u5fd7\u201d\n<\/code><\/pre>\n<h3>11.2.2 \u4f5c\u7528\u662f\u4ec0\u4e48\uff1f<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">\u5728\u4e8b\u52a1ACID\u8fc7\u7a0b\u4e2d\uff0c\u5b9e\u73b0\u7684\u662f\u201cA\u201d \u539f\u5b50\u6027\u7684\u4f5c\u7528\n\u53e6\u5916CI\u4e5f\u4f9d\u8d56\u4e8eUndo\n\u5728rolback\u65f6,\u5c06\u6570\u636e\u6062\u590d\u5230\u4fee\u6539\u4e4b\u524d\u7684\u72b6\u6001\n\u5728CSR\u5b9e\u73b0\u7684\u662f,\u5c06redo\u5f53\u4e2d\u8bb0\u5f55\u7684\u672a\u63d0\u4ea4\u7684\u65f6\u5019\u8fdb\u884c\u56de\u6eda.\nundo\u63d0\u4f9b\u5feb\u7167\u6280\u672f,\u4fdd\u5b58\u4e8b\u52a1\u4fee\u6539\u4e4b\u524d\u7684\u6570\u636e\u72b6\u6001.\u4fdd\u8bc1\u4e86MVCC,\u9694\u79bb\u6027,mysqldump\u7684\u70ed\u5907\n\n(1)\u8bb0\u5f55\u4e86\u6570\u636e\u4fee\u6539\u4e4b\u524d\u7684\u72b6\u6001\n(2)rollback\u5c06\u5185\u5b58\u7684\u6570\u636e\u4fee\u6539\u6062\u590d\u5230\u4fee\u6539\u4e4b\u524d\n(3)\u5728CSR\u4e2d\u5b9e\u73b0\u672a\u63d0\u4ea4\u6570\u636e\u7684\u56de\u6eda\u64cd\u4f5c\n(4)\u5b9e\u73b0\u4e00\u81f4\u6027\u5feb\u7167,\u914d\u5408\u9694\u79bb\u7ea7\u522b\u4fdd\u8bc1MVCC,\u5b9e\u73b0\u8bfb\u548c\u5199\u7684\u64cd\u4f5c\u4e0d\u4f1a\u4e92\u76f8\u963b\u585e\n<\/code><\/pre>\n<h2>11.3 \u6982\u5ff5\u6027\u7684\u4e1c\u897f:<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">redo\u600e\u4e48\u5e94\u7528\u7684\nundo\u600e\u4e48\u5e94\u7528\u7684\nCSR(\u81ea\u52a8\u6545\u969c\u6062\u590d)\u8fc7\u7a0b\nLSN :\u65e5\u5fd7\u5e8f\u5217\u53f7\nTXID:\u4e8b\u52a1ID\nCKPT(Checkpoint)\n<\/code><\/pre>\n<h2>11.4 \u9501<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-ruby\" lang=\"ruby\">\u201c\u9501\u201d\u987e\u540d\u601d\u4e49\u5c31\u662f\u9501\u5b9a\u7684\u610f\u601d\u3002\n\u201c\u9501\u201d\u7684\u4f5c\u7528\u662f\u4ec0\u4e48\uff1f\n\u5728\u4e8b\u52a1ACID\u8fc7\u7a0b\u4e2d\uff0c\u201c\u9501\u201d\u548c\u201c\u9694\u79bb\u7ea7\u522b\u201d\u4e00\u8d77\u6765\u5b9e\u73b0\u201cI\u201d\u9694\u79bb\u6027\u548c\"C\" \u4e00\u81f4\u6027 (redo\u4e5f\u6709\u53c2\u4e0e).\n\u60b2\u89c2\u9501:\u884c\u7ea7\u9501\u5b9a(\u884c\u9501)\n\u8c01\u5148\u64cd\u4f5c\u67d0\u4e2a\u6570\u636e\u884c,\u5c31\u4f1a\u6301\u6709&lt;\u8fd9\u884c&gt;\u7684(X)\u9501.\n\u4e50\u89c2\u9501: \u6ca1\u6709\u9501\ninnoDB\u662f\u884c\u7ea7\u9501\n\nrow-level lock   \u884c\u7ea7\u9501\ngap\nnext-lock\n\n<\/code><\/pre>\n<h2>11.5 \u9694\u79bb\u7ea7\u522b<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">\u67e5\u770b\uff1a\nselect @@tx_isolation;\n\n\u5f71\u54cd\u5230\u6570\u636e\u7684\u8bfb\u53d6,\u9ed8\u8ba4\u7684\u7ea7\u522b\u662f RR\u6a21\u5f0f.\ntransaction_isolation   \u9694\u79bb\u7ea7\u522b(\u53c2\u6570)\n\u8d1f\u8d23\u7684\u662f,MVCC,\u8bfb\u4e00\u81f4\u6027\u95ee\u9898\nRU  : \u8bfb\u672a\u63d0\u4ea4,\u53ef\u810f\u8bfb,\u4e00\u822c\u90e8\u8bae\u53d9\u51fa\u73b0\nRC  : \u8bfb\u5df2\u63d0\u4ea4,\u53ef\u80fd\u51fa\u73b0\u5e7b\u8bfb,\u53ef\u4ee5\u9632\u6b62\u810f\u8bfb.\nRR  : \u53ef\u91cd\u590d\u8bfb,\u529f\u80fd\u662f\u9632\u6b62\"\u5e7b\u8bfb\"\u73b0\u8c61 ,\u5229\u7528\u7684\u662fundo\u7684\u5feb\u7167\u6280\u672f+GAP(\u95f4\u9699\u9501)+NextLock(\u4e0b\u952e\u9501)(\u9ed8\u8ba4\u7ea7\u522b)(\u5fc5\u987b\u7d22\u5f15\u652f\u6301)\nSR  : \u53ef\u4e32\u884c\u5316,\u53ef\u4ee5\u9632\u6b62\u6b7b\u9501,\u4f46\u662f\u5e76\u53d1\u4e8b\u52a1\u6027\u80fd\u8f83\u5dee\n\u8865\u5145: \u5728RC\u7ea7\u522b\u4e0b,\u53ef\u4ee5\u51cf\u8f7bGAP+NextLock\u9501\u7684\u95ee\u9898,\u4f46\u662f\u4f1a\u51fa\u73b0\u5e7b\u8bfb\u73b0\u8c61,\u4e00\u822c\u5728\u4e3a\u4e86\u8bfb\u4e00\u81f4\u6027\u4f1a\u5728\u6b63\u5e38select\u540e\u6dfb\u52a0for update\u8bed\u53e5.\u4f46\u662f,\u8bf7\u8bb0\u4f4f\u6267\u884c\u5b8c\u4e00\u5b9a\u8981commit \u5426\u5219\u5bb9\u6613\u51fa\u73b0\u6240\u7b49\u5f85\u6bd4\u8f83\u4e25\u91cd.\n\u4f8b\u5982:\n[world]&gt;select * from city where id=999 for update;\n[world]&gt;commit;\n\n\/\/\u8bbe\u7f6eread uncommitted\u7ea7\u522b\uff1a\nset session transaction isolation level read uncommitted;\n\n\/\/\u8bbe\u7f6eread committed\u7ea7\u522b\uff1a\nset session transaction isolation level read committed;\n\n\/\/\u8bbe\u7f6erepeatable read\u7ea7\u522b\uff1a\nset session transaction isolation level repeatable read;\n\n\/\/\u8bbe\u7f6eserializable\u7ea7\u522b\uff1a\nset session transaction isolation level serializable;\n<\/code><\/pre>\n<h2>11.6 \u67b6\u6784\u6539\u9020\u9879\u76ee<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">\u9879\u76ee\u80cc\u666f:\n2\u53f0  IBM X3650   32G  ,\u539f\u6765\u4e3b\u4ece\u5173\u7cfb,2\u5e74\u591a\u6ca1\u6709\u4e3b\u4ece\u4e86,\"\u5c0f\u95ee\u9898\"\u4e0d\u65ad(\u9501,\u5b95\u673a\u540e\u7684\u5b89\u5168)\nMySQL 5.1.77   \u9ed8\u8ba4\u5b58\u50a8\u5f15\u64ce MyISAM  \n\u6570\u636e\u91cf: 60G\u5de6\u53f3 ,\u6bcf\u5468\u5168\u5907,\u6ca1\u6709\u5f00\u4e8c\u8fdb\u5236\u65e5\u5fd7\n\u67b6\u6784\u65b9\u6848:\n    1. \u5347\u7ea7\u6570\u636e\u5e93\u7248\u672c\u52305.7.20 \n    2. \u66f4\u65b0\u6240\u6709\u4e1a\u52a1\u8868\u7684\u5b58\u50a8\u5f15\u64ce\u4e3aInnoDB\n    3. \u91cd\u65b0\u8bbe\u8ba1\u5907\u4efd\u7b56\u7565\u4e3a\u70ed\u5907\u4efd,\u6bcf\u5929\u5168\u5907,\u5e76\u5907\u4efd\u65e5\u5fd7\n    4. \u91cd\u65b0\u6784\u5efa\u4e3b\u4ece\n\u7ed3\u679c:\n    1.\u6027\u80fd\n    2.\u5b89\u5168\u65b9\u9762\n    3.\u5feb\u901f\u6545\u969c\u5904\u7406\n<\/code><\/pre>\n<h1>12 InnoDB\u5b58\u50a8\u5f15\u64ce\u6838\u5fc3\u7279\u6027-\u53c2\u6570\u8865\u5145<\/h1>\n<h2>12.1 \u5b58\u50a8\u5f15\u64ce\u76f8\u5173<\/h2>\n<h3>12.1.1 \u67e5\u770b<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-dart\" lang=\"dart\">show engines;\nshow variables like 'default_storage_engine';\nselect @@default_storage_engine;\n<\/code><\/pre>\n<h3>12.1.2 \u5982\u4f55\u6307\u5b9a\u548c\u4fee\u6539\u5b58\u50a8\u5f15\u64ce<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">(1) \u901a\u8fc7\u53c2\u6570\u8bbe\u7f6e\u9ed8\u8ba4\u5f15\u64ce\ndefault_storage_engine=innodb\n(2) \u5efa\u8868\u7684\u65f6\u5019\u8fdb\u884c\u8bbe\u7f6e\n(3) alter table t1 engine=innodb;\n<\/code><\/pre>\n<h2>12.2. \u8868\u7a7a\u95f4<\/h2>\n<h3>12.2.1 \u5171\u4eab\u8868\u7a7a\u95f4<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">innodb_data_file_path\n\u4e00\u822c\u662f\u5728\u521d\u59cb\u5316\u6570\u636e\u4e4b\u524d\u5c31\u8bbe\u7f6e\u597d\n\u4f8b\u5b50:\ninnodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend\n<\/code><\/pre>\n<h3>12.2.2 \u72ec\u7acb\u8868\u7a7a\u95f4<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-dart\" lang=\"dart\">show variables like 'innodb_file_per_table';\n<\/code><\/pre>\n<h2>12.3. \u7f13\u51b2\u533a\u6c60<\/h2>\n<h3>12.3.1 \u67e5\u8be2<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">select @@innodb_buffer_pool_size;\nshow engine innodb status\\G\ninnodb_buffer_pool_size \n\u4e00\u822c\u5efa\u8bae\u6700\u591a\u662f\u7269\u7406\u5185\u5b58\u7684 75-80%\n<\/code><\/pre>\n<h2>12.4. innodb_flush_log_at_trx_commit  (\u53cc\u4e00\u6807\u51c6\u4e4b\u4e00)<\/h2>\n<h3>12.4.1 \u4f5c\u7528<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">innodb_flush_log_at_trx_commit=1\uff0c\u6bcfcommit\u4e00\u6b21\uff0c\u4ecemysql mem\u5f80OS buffer\u5237\u5199\u4e00\u6b21\uff0c\u4eceOS buffer\u5411\u78c1\u76d8\u5237\u5199\u4e00\u6b21\ninnodb_flush_log_at_trx_commit=0\uff0c\u6bcf\u79d2\u4ecemysql mem\u5f80OS buffer\uff0c\u6bcf\u79d2\u4eceOS buffer\u5411\u78c1\u76d8\u5237\u5199\u4e00\u6b21\ninnodb_flush_log_at_trx_commit=2\uff0c\u6bcfcommit\u4e00\u6b21\uff0c\u4ecemysql mem\u5f80OS buffer\u5237\u5199\u4e00\u6b21\uff0c\u6bcf\u79d2\u4eceOS buffer\u5411\u78c1\u76d8\u5237\u5199\u4e00\u6b21\n\n\u4e3b\u8981\u63a7\u5236\u4e86innodb\u5c06log buffer\u4e2d\u7684\u6570\u636e\u5199\u5165\u65e5\u5fd7\u6587\u4ef6\u5e76flush\u78c1\u76d8\u7684\u65f6\u95f4\u70b9\uff0c\u53d6\u503c\u5206\u522b\u4e3a0\u30011\u30012\u4e09\u4e2a\u3002\n<\/code><\/pre>\n<h3>12.4.2 \u67e5\u8be2<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-css\" lang=\"css\">select @@innodb_flush_log_at_trx_commit;\n<\/code><\/pre>\n<h3>12.4.3 \u53c2\u6570\u8bf4\u660e:<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">1\uff0c\u6bcf\u6b21\u4e8b\u7269\u7684\u63d0\u4ea4\u90fd\u4f1a\u5f15\u8d77\u65e5\u5fd7\u6587\u4ef6\u5199\u5165\u3001flush\u78c1\u76d8\u7684\u64cd\u4f5c\uff0c\u786e\u4fdd\u4e86\u4e8b\u52a1\u7684ACID\uff1bflush  \u5230\u64cd\u4f5c\u7cfb\u7edf\u7684\u6587\u4ef6\u7cfb\u7edf\u7f13\u5b58  fsync\u5230\u7269\u7406\u78c1\u76d8.\n0\uff0c\u8868\u793a\u5f53\u4e8b\u52a1\u63d0\u4ea4\u65f6\uff0c\u4e0d\u505a\u65e5\u5fd7\u5199\u5165\u64cd\u4f5c\uff0c\u800c\u662f\u6bcf\u79d2\u949f\u5c06log buffer\u4e2d\u7684\u6570\u636e\u5199\u5165\u6587\u4ef6\u7cfb\u7edf\u7f13\u5b58\u5e76\u4e14\u79d2fsync\u78c1\u76d8\u4e00\u6b21\uff1b\n2\uff0c\u6bcf\u6b21\u4e8b\u52a1\u63d0\u4ea4\u5f15\u8d77\u5199\u5165\u6587\u4ef6\u7cfb\u7edf\u7f13\u5b58,\u4f46\u6bcf\u79d2\u949f\u5b8c\u6210\u4e00\u6b21fsync\u78c1\u76d8\u64cd\u4f5c\u3002\n<\/code><\/pre>\n<h2>12.5. Innodb_flush_method=(O_DIRECT, fdatasync)<\/h2>\n<p>&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/\/\/upload-images.jianshu.io\/upload_images\/16956686-b557f16a20a5dad2.png?imageMogr2\/auto-orient\/strip|imageView2\/2\/w\/679\/format\/webp\" referrerpolicy=\"no-referrer\" alt=\"img\"><\/p>\n<p><img decoding=\"async\" src=\"D:\\BaiduNetdiskDownload\\\u6570\u636e\u5e93DBA\\FSYNC.png\" referrerpolicy=\"no-referrer\" alt=\"image-20201125131858015\"><\/p>\n<p>&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"D:\\BaiduNetdiskDownload\\\u6570\u636e\u5e93DBA\\O_DIRECT.png\" referrerpolicy=\"no-referrer\" alt=\"image-20201125132052018\"><\/p>\n<p>&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"D:\\BaiduNetdiskDownload\\\u6570\u636e\u5e93DBA\\O_DSYNC.png\" referrerpolicy=\"no-referrer\" alt=\"image-20201125132301772\"><\/p>\n<h3>12.5.1 \u4f5c\u7528<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-shell\" lang=\"shell\">\u63a7\u5236\u7684\u662f,log buffer \u548cdata buffer,\u5237\u5199\u78c1\u76d8\u7684\u65f6\u5019\u662f\u5426\u7ecf\u8fc7\u6587\u4ef6\u7cfb\u7edf\u7f13\u5b58\n<\/code><\/pre>\n<h3>12.5.2 \u67e5\u770b<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-dart\" lang=\"dart\">show variables like '%innodb_flush%';\n<\/code><\/pre>\n<h3>12.5.3 \u53c2\u6570\u503c\u8bf4\u660e<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">O_DIRECT  :\u5efa\u8bae\u6a21\u5f0f\uff0c\u6570\u636e\u7f13\u51b2\u533a\u5199\u78c1\u76d8,\u4e0d\u8d70OS buffer\nfsync :\u65e5\u5fd7\u548c\u6570\u636e\u7f13\u51b2\u533a\u5199\u78c1\u76d8,\u90fd\u8d70OS buffer\nO_DSYNC  :\u65e5\u5fd7\u7f13\u51b2\u533a\u5199\u78c1\u76d8,\u4e0d\u8d70 OS buffer\n<\/code><\/pre>\n<h3>12.5.4 \u4f7f\u7528\u5efa\u8bae<\/h3>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">\u6700\u9ad8\u5b89\u5168\u6a21\u5f0f\ninnodb_flush_log_at_trx_commit=1\nInnodb_flush_method=O_DIRECT\n\u6700\u9ad8\u6027\u80fd:\ninnodb_flush_log_at_trx_commit=0\nInnodb_flush_method=fsync\n<\/code><\/pre>\n<h2>12.6. redo\u65e5\u5fd7\u6709\u5173\u7684\u53c2\u6570<\/h2>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-undefined\" lang=\"undefined\">innodb_log_buffer_size=16777216\t\t\u65e5\u5fd7\u7f13\u51b2\u533a\u5927\u5c0f\ninnodb_log_file_size=50331648\t\t\u65e5\u5fd7\u6587\u4ef6\u5927\u5c0f\ninnodb_log_files_in_group = 3\t\tredo\u4e2a\u6570\uff0c\u9ed8\u8ba4\u4e24\u4e2a\n<\/code><\/pre>\n<h2>12.7. \u810f\u9875\u5237\u5199\u7b56\u7565<\/h2>\n<pre><code>innodb_max_dirty_pages_pct=75\t\t\u6700\u5927\u810f\u9875\u5185\u5b58\u5360\u7528\u6bd4\u5229\n<\/code><\/pre>\n<p>\u8fd8\u6709\u54ea\u4e9b\u673a\u5236\u4f1a\u89e6\u53d1\u5199\u78c1\u76d8\uff1f<\/p>\n<p>CSR<\/p>\n<p>redo\u6ee1\u4e86<\/p>\n<h1>13.\u6269\u5c55(\u81ea\u5df1\u6269\u5c55\uff0c\u5efa\u8bae\u662f\u5b98\u65b9\u6587\u6863\u3002)<\/h1>\n<p>&nbsp;<\/p>\n<pre><code class=\"language-csharp\" lang=\"csharp\">RR\u6a21\u5f0f(\u5bf9\u7d22\u5f15\u8fdb\u884c\u5220\u9664\u65f6):\nGAP:          \u95f4\u9699\u9501\nnext-lock:    \u4e0b\u4e00\u952e\u9501\u5b9a\n\n\u4f8b\u5b50:\nid\uff08\u6709\u7d22\u5f15\uff09\n1 2 3 4 5 6 \nGAP\uff1a\n\u5728\u5bf93\u8fd9\u4e2a\u503c\u505a\u53d8\u66f4\u65f6\uff0c\u4f1a\u4ea7\u751f\u4e24\u79cd\u9501\uff0c\u4e00\u79cd\u662f\u672c\u884c\u7684\u884c\u7ea7\u9501\uff0c\u53e6\u4e00\u79cd\u4f1a\u57282\u548c4\u7d22\u5f15\u952e\u4e0a\u8fdb\u884c\u67b7\u9501\nnext-lock\uff1a\n\u5bf9\u7b2c\u516d\u884c\u53d8\u66f4\u65f6\uff0c\u4e00\u79cd\u662f\u672c\u884c\u7684\u884c\u7ea7\u9501\uff0c\u5728\u7d22\u5f15\u672b\u5c3e\u952e\u8fdb\u884c\u52a0\u9501\uff0c6\u4ee5\u540e\u7684\u503c\u5728\u8fd9\u65f6\u662f\u4e0d\u80fd\u88ab\u63d2\u5165\u7684\u3002\n\u603b\u4e4b\uff1a\nGAP\u3001next lock\u90fd\u662f\u4e3a\u4e86\u4fdd\u8bc1RR\u6a21\u5f0f\u4e0b\uff0c\u4e0d\u4f1a\u51fa\u73b0\u5e7b\u8bfb\uff0c\u964d\u4f4e\u9694\u79bb\u7ea7\u522b\u6216\u53d6\u6d88\u7d22\u5f15\uff0c\u8fd9\u4e24\u79cd\u9501\u90fd\u4e0d\u4f1a\u4ea7\u751f\u3002\nIX IS X S\u662f\u4ec0\u4e48?\n<\/code><\/pre>\n<h1>17.\u5c0f\u7ed3<\/h1>\n<h2>1.\u8868\u7a7a\u95f4<\/h2>\n<h3>1.1 \u8bf4\u660e<\/h3>\n<p>\u72ec\u7acb\u8868\u7a7a\u95f4:5.6\u5f00\u59cb\u7684\u9ed8\u8ba4\u8868\u7a7a\u95f4,-\u4e00\u4e2a\u8868-\u4e00\u4e2aibd\u6587\u4ef6,\u5b58\u50a8\u6570\u636e\u884c\u548c\u7d22\u5f15<br \/>\n\u5171\u4eab\u8868\u7a7a\u95f4:5.5\u9ed8\u8ba4\u7684\u6a21\u5f0f\uff0c\u6240\u6709\u8868\u7684\u884c\u548c\u7d22\u5f15\u90fd\u5b58\u50a8\u5230ibdatal<br \/>\n\u8bf4\u660e:\u4ece5.6\u5f00\u59cb,\u4e0d\u518d\u4f7f\u7528\u5171\u4eab\u8868\u7a7a\u95f4\u6a21\u5f0f<br \/>\n5.6\u7248\u672c \u6570\u636e\u5b57\u5178\u4fe1\u606f+ UNDO + tmp<br \/>\n5.7\u7248\u672c \u628atmp\u72ec\u7acb\u4e86<br \/>\n8.0\u7248\u672c \u628aUND0\u72ec\u7acb<\/p>\n<h3>1.2\u8868\u7a7a\u95f4\u8fc1\u79fb<\/h3>\n<p>(1)\u521b\u5efa\u548c\u539f\u8868\u7ed3\u6784\u76f8\u540c\u7684\u8868<br \/>\n(2)\u65b0\u5efa\u8868\u7684ibd\u5220\u9664<br \/>\nalter table t1 discard tablespace ;<br \/>\n(3)\u62f7\u8d1d\u539f\u8868ibd\u5230\u65b0\u4f4d\u7f6e<br \/>\n(4)\u5bfc\u5165ibd\u5230\u65b0\u8868<br \/>\nalter table t1 import tablespace ;<\/p>\n<h3>1.3 ibtmp1 \u4fdd\u5b58\u4e34\u65f6\u8868<\/h3>\n<h3>1.4 undo\u91cd\u505a\u7684\u65e5\u5fd7\u5b58\u50a8\u4f4d\u7f6e<\/h3>\n<h2>2.ACID<\/h2>\n<p>A \u539f\u5b50\u6027\uff1a\u901a\u8fc7undo\u4fdd\u8bc1\uff0c\u672a\u63d0\u4ea4\u7684\u8981\u53d6\u6d88\u6389\u8981\u7528undo\u56de\u6eda\uff0c\u8981\u4e48\u5168\u6210\u529f\uff0c\u8981\u4e48\u5168\u5931\u8d25<br \/>\nC \u4e00\u81f4\u6027\uff1a\u901a\u8fc7redo\u548cundo\uff0c\u4e00\u8d77\u4fdd\u8bc1\u4e00\u81f4\u6027<br \/>\nI \u9694\u79bb\u6027\uff1a\u901a\u8fc7\u9501\u673a\u5236\uff08\u884c\u7ea7\u9501\uff0c\u9694\u79bb\u7ea7\u522b\uff09<br \/>\nD \u6301\u4e45\u6027\uff1aredo\u673a\u5236\uff0c\u65e5\u5fd7\u4f18\u5148\u5199\uff0c\u5df2\u63d0\u4ea4\u7684\u4e8b\u52a1\u4e00\u5b9a\u4e0d\u4f1a\u4e22\uff08\u628a\u65e5\u5fd7\u5199\u5165\u78c1\u76d8\uff0c\u4e0d\u662f\u628a\u6570\u636e\u5199\u5165\u78c1\u76d8\uff09<\/p>\n<h2>3.redo<\/h2>\n<p>\u5185\u5b58\u6570\u636e\u9875\u7684\u53d8\u5316\u8fc7\u7a0b\u7684\u65e5\u5fd7\uff0c\u6570\u636e\u9875\u4e00\u65e6\u53d8\u66f4\uff0c\u5c31\u4f1a\u8bb0\u5f55\u4e00\u6761\u65e5\u5fd7<\/p>\n<p>\uff081\uff09\u8bb0\u5f55 \u5185\u5b58\u6570\u636e\u9875\u53d8\u5316\u65e5\u5fd7<br \/>\n\uff082\uff09\u63d0\u4f9b \u5feb\u901f\u7684\u4e8b\u52a1\u63d0\u4ea4<br \/>\n\uff083\uff09CSR  \u8fc7\u7a0b\u4e2d\uff0credo\u63d0\u4f9b\u524d\u6eda\u529f\u80fd<\/p>\n<h2>4.undo<\/h2>\n<p>\uff081\uff09\u8bb0\u5f55\u6570\u636e\u4fee\u6539\u4e4b\u524d\u7684\u72b6\u6001<br \/>\n\uff082\uff09\u63d0\u4f9b\u4e8b\u52a1\u5de5\u4f5c\u8fc7\u8fc7\u7a0b\u4e2d\u56de\u6eda\u64cd\u4f5c(rollback)<br \/>\n\uff083\uff09CSR\u4e2d\u5c06\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1\u8fdb\u884c\u56de\u6eda<\/p>\n<h2>5.\u9694\u79bb\u7ea7\u522b<\/h2>\n<p>\u67e5\u770b\u9694\u79bb\u7ea7\u522b\uff1a<\/p>\n<pre><code>select @@tx_isolation;\n<\/code><\/pre>\n<p>RU\uff1a\u8bfb\u672a\u63d0\u4ea4\uff0c\u4f1a\u6709\u810f\u8bfb\uff0c\u5e7b\u8bfb\uff0c\u4e0d\u53ef\u91cd\u590d\u8bfb<\/p>\n<p>RC\uff1a\u8bfb\u5df2\u63d0\u4ea4\uff0c\u4f1a\u6709\u5e7b\u8bfb\uff0c\u4e0d\u53ef\u91cd\u590d\u8bfb\uff0c\u5728\u5927\u90e8\u5206\u4e92\u8054\u7f51\u4f01\u4e1a\u4e2d\u662f\u53ef\u4ee5\u5bb9\u5fcd\u7684<\/p>\n<p>RR\uff1a\u53ef\u91cd\u590d\u5ea6\uff0c\u6709\u53ef\u80fd\u51fa\u73b0\u5e7b\u8bfb\uff08MVCC,undo\u5feb\u7167\uff09\uff0c\u53ef\u4ee5\u901a\u8fc7GAP+Next LOCK\u6765\u9632\u6b62\u5e7b\u8bfb\uff08\u7d22\u5f15\u9501\uff09<\/p>\n<p>SR\uff1a\u53ef\u4e32\u884c\u5316\uff0c\u53ef\u4ee5\u89e3\u51b3\u6240\u6709\u95ee\u9898\uff0c\u53ef\u4ee5\u6709\u6548\u9632\u6b62\u6b7b\u9501\uff0c\u4f46\u662f\u56e0\u4e3a\u4e32\u884c\u5904\u7406\uff0c\u5e76\u53d1\u5ea6\u4f4e<\/p>\n<h2>6.\u4e0d\u53ef\u91cd\u590d\u8bfb\uff08RC\u6a21\u5f0f\u4e0b\uff09<\/h2>\n<p>\u4e0d\u53ef\u91cd\u590d\u8bfb\uff1a\u4e0d\u540c\u7a97\u53e3\uff0c\u6ca1\u63d0\u4ea4\u7684\u65f6\u5019\uff0c\u8bfb\u53d6\u6570\u636e\u4e0d\u540c<\/p>\n<p>\u5e7b\u8bfb\uff1a\u4e00\u4e2a\u7a97\u53e3\u8fdb\u884c\u6279\u91cfupdate\u7684\u65f6\u5019\u672a\u63d0\u4ea4\uff0c\u53e6\u4e00\u4e2a\u7a97\u53e3\u63d2\u5165\u65b0\u6570\u636e\u5e76\u63d0\u4ea4\uff0c\u6b64\u65f6update\u63d0\u4ea4\u4e0d\u4f1a\u66f4\u6539\u65b0\u63d2\u5165\u7684\u6570\u636e<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5b58\u50a8\u5f15\u64ce 1\u3001\u7b80\u4ecb &nbsp; \u76f8\u5f53\u4e8eLinux\u6587\u4ef6\u7cfb\u7edf\uff0c\u53ea\u4e0d\u8fc7\u6bd4\u6587\u4ef6\u7cfb\u7edf\u5f3a\u5927 2\u3001\u529f\u80fd\u4e86\u89e3 &nbsp;  [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-57","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/57","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=57"}],"version-history":[{"count":1,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/57\/revisions"}],"predecessor-version":[{"id":58,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=\/wp\/v2\/posts\/57\/revisions\/58"}],"wp:attachment":[{"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=57"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=57"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.daishen.ltd\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=57"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}