深入理解MySQL

<h1 cid="n0" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 2.5rem; margin-top: 2em; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 2.75rem; letter-spacing: -1.5px; white-space: pre-wrap; position: relative;"><span md-inline="plain">深入理解</span><span md-inline="code" spellcheck="false"><code style="font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; vertical-align: initial; font-size: 0.875em; background: rgba(0, 0, 0, 0.05); padding-top: 2px; padding-bottom: 2px;">MySQL</code></span></h1><h2 cid="n2" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.63rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.875rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">一、索引</span></h2><h3 cid="n3" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">MySQL 索引数据结构为什么要用B+树？</span></h3><ul cid="n4" mdtype="list" style="margin-top: 0px; margin-bottom: 1.5rem; padding-left: 1.875rem; list-style: square; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><li cid="n68" mdtype="list_item" style="position: relative;"><p cid="n69" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">二叉树：这事要从二叉树说起，在搜索数据中，二叉树可是使复杂度从O（n）,转化为O（logn）,性能得到很大提升。不过存在树退化成链表的问题。</span></p></li><li cid="n72" mdtype="list_item" style="position: relative;"><p cid="n70" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">平衡二叉树：为解决以上的问题，出现了平衡二叉树（AVL），树的左右高度差不好过1，不会退化成链表。问题是旋转耗时，性能较低。尤其是删除节点时，AVL需要维护从跟节点到该节点路径上的所有节点的平衡，旋转的量级为O（logn）。</span></p></li><li cid="n75" mdtype="list_item" style="position: relative;"><p cid="n73" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">红黑树：红黑树只要求大致的平衡，只保证从根节点到叶子节点的最长路径不大于最短路径的两倍。这样在插入或删除节点的时候，只需要改变一个红黑节点节点，复杂度为O(logn);尽管查询效率由于树的不严格平衡效率相比AVL有所降低，但是总体效率还是大大提升的。所以，红黑树用的更广法。</span><span md-inline="softbreak"> </span><span md-inline="plain">不过红黑树对于磁盘操作来说，高度还是太高了。树的高度决定了io的次数，也就制约了性能。</span></p></li><li cid="n78" mdtype="list_item" style="position: relative;"><p cid="n76" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">B树，对于磁盘相关的查找操作，就引入了B树，一种多路平衡查找树。因为每个节点可以有多路，从而使树高大大降低，从而减少io次数。</span></p><blockquote cid="n166" mdtype="blockquote" style="margin-top: 1rem; margin-bottom: 1rem; border-left-width: 2px; border-left-color: rgb(71, 77, 84); padding-left: 30px; color: rgb(157, 162, 166);"><p cid="n163" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0px; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">mongo db就使用的B树结构。</span></p></blockquote></li><li cid="n82" mdtype="list_item" style="position: relative;"><p cid="n80" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">B+树是B树的一个变种。由于B树每个节点都存储数据，所以树的高度依然较高。</span><span md-inline="softbreak"> </span><span md-inline="plain">B+树节点不存数据，只记录索引。将所有data都存在叶子节点，从而使树高更低，更有效的减少io次数。</span><span md-inline="softbreak"> </span><span md-inline="plain">相比B树，有以下优点。</span><span md-inline="softbreak"> </span><span md-inline="plain">更少的IO次数；查询性能稳定，IO次数恒定；方便范围查询，叶子节点作为链表方便遍历。</span></p></li><li cid="n86" mdtype="list_item" style="position: relative;"><p cid="n84" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">那么为什么Mongo db使用B树，而MySQL使用B+树就清楚了。</span><span md-inline="softbreak"> </span><span md-inline="plain">MySQL 场景需要范围查询和多表关联，Mongodb一般不需要这样的场景，故而选择了适合自己场景的数据结构。</span></p></li></ul><h3 cid="n5" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">Innodb 的索引类型</span></h3><p cid="n109" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">从结构上来说，Innodb索引类型分为聚簇索引和非聚簇索引。</span><span md-inline="softbreak"> </span><span md-inline="plain">聚集索引的顺序就是数据的物理存储顺序，而非聚集索引的顺序和数据物理排列无关。</span></p><h3 cid="n6" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">聚簇索引一定是主键吗？</span></h3><p cid="n111" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。</span></p><h3 cid="n110" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">非聚簇索引和聚簇索引的区别</span></h3><p cid="n113" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">非聚簇索引的叶子节点存的是索引和聚簇索引的id，而聚簇索引的叶子结点存的是记录本身。</span><span md-inline="softbreak"> </span><span md-inline="plain">所以非聚簇索引要先查到聚簇索引，再根据聚簇索引id查到记录，效率相对较低。</span></p><h3 cid="n112" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">唯一索引与普通索引的区别</span></h3><p cid="n114" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">这个要说到change buffer。</span><span md-inline="softbreak"> </span><span md-inline="plain">由于对索引数据的增删改不仅要改变记录本身，还要操作索引文件。为了减少对索引文件的频繁io操作，Innodb实现了change buffer 。用来缓存对索引文件的操作。将一部分索引文件加到内存缓存起来，如果操作命中缓存，则将操作缓存起来，待系统空闲时再合并进行io请求，从而提高效率。</span><span md-inline="softbreak"> </span><span md-inline="plain">由于唯一索引增删时从要校验是否唯一，无法使用change buffer。从而使普通索引增删改的效率要比唯一索引高。</span></p><h2 cid="n7" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.63rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.875rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">二、MVCC及事务隔离级别</span></h2><p cid="n108" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">多版本并发控制MVCC（ Multi-Version Concurrency Control ），多个版本记录共存，实现读不加锁</span></p><h3 cid="n9" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">mvcc的实现方式</span></h3><p cid="n10" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">Innodb,在每行数据后面添加两个隐藏值，分别是创建版本号和删除版本号。每开启一个新事物，版本号就会递增。</span><span md-inline="softbreak"> </span><span md-inline="plain">Insert时，将当前事务版本号填入创建版本号</span><span md-inline="softbreak"> </span><span md-inline="plain">Delete时，将当前事务版本号填入删除版本号。</span><span md-inline="softbreak"> </span><span md-inline="plain">Update时，插入一条新的记录，将当前事务版本号添加至新纪录的创建版本号。并将当前事务版本号添加到旧记录的删除版本号</span><span md-inline="softbreak"> </span><span md-inline="plain">那么在读时，如果是事务隔离级别为读可重复读</span><span md-inline="softbreak"> </span><span md-inline="plain">Select时，读取创建版本号《= 当前事务版本号，删除版本号为空，或》当前事务版本号。</span><span md-inline="softbreak"> </span><span md-inline="plain">如果事务隔离级别为读已提交。Select时，读取创建版本号为最近一次的版本号。</span></p><h3 cid="n11" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="code" spellcheck="false"><code style="font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; vertical-align: initial; font-size: 0.875em; background: rgba(0, 0, 0, 0.05); padding-top: 2px; padding-bottom: 2px;">MVCC</code></span><span md-inline="plain">举例</span></h3><p cid="n12" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">举个简单的例子：</span></p><ol start="" cid="n13" mdtype="list" style="margin-top: 0px; margin-bottom: 1.5rem; padding-left: 1.875rem; list-style-position: initial; list-style-image: initial; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><li cid="n14" mdtype="list_item" style="position: relative;"><p cid="n15" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">一个事务A（</span><span md-inline="code" spellcheck="false"><code style="font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; vertical-align: initial; font-size: 0.875em; background: rgba(0, 0, 0, 0.05); padding-top: 2px; padding-bottom: 2px;">txnId=100</code></span><span md-inline="plain">）修改了数据X，使得X=1，并且commit了</span></p></li><li cid="n16" mdtype="list_item" style="position: relative;"><p cid="n17" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">另外一个事务B（</span><span md-inline="code" spellcheck="false"><code style="font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; vertical-align: initial; font-size: 0.875em; background: rgba(0, 0, 0, 0.05); padding-top: 2px; padding-bottom: 2px;">txnId=101</code></span><span md-inline="plain">）开始尝试读取X，但是还X=1。但B没有提交。</span></p></li><li cid="n18" mdtype="list_item" style="position: relative;"><p cid="n19" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">第三个事务C（</span><span md-inline="code" spellcheck="false"><code style="font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; vertical-align: initial; font-size: 0.875em; background: rgba(0, 0, 0, 0.05); padding-top: 2px; padding-bottom: 2px;">txnId=102</code></span><span md-inline="plain">）修改了数据X，使得X=2。并且提交了</span></p></li><li cid="n20" mdtype="list_item" style="position: relative;"><p cid="n21" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">事务B又一次读取了X。这时</span></p></li></ol><ul cid="n23" mdtype="list" style="margin-top: 0px; margin-bottom: 1.5rem; padding-left: 1.875rem; list-style: square; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><li cid="n24" mdtype="list_item" style="position: relative;"><p cid="n25" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">如果事务B是Read Committed。那么就读取X的最新commit的版本，也就是X=2</span></p></li><li cid="n26" mdtype="list_item" style="position: relative;"><p cid="n27" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">如果事务B是Repeatable Read。那么读取的就是当前事务（</span><span md-inline="code" spellcheck="false"><code style="font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; vertical-align: initial; font-size: 0.875em; background: rgba(0, 0, 0, 0.05); padding-top: 2px; padding-bottom: 2px;">txnId=101</code></span><span md-inline="plain">）之前X的最新版本，也就是X被</span><span md-inline="code" spellcheck="false"><code style="font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; vertical-align: initial; font-size: 0.875em; background: rgba(0, 0, 0, 0.05); padding-top: 2px; padding-bottom: 2px;">txnId=100</code></span><span md-inline="plain">提交的版本，即X=1。</span><span md-inline="softbreak"> </span><span md-inline="plain">注意，这里B不论是Read Committed，还是Repeatable Read，都不会被锁，都能立刻拿到结果。这也就是MVCC存在的意义。</span></p></li></ul><h3 cid="n29" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">快照读和当前读</span></h3><p cid="n30" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="code" spellcheck="false"><code style="font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; vertical-align: initial; font-size: 0.875em; background: rgba(0, 0, 0, 0.05); padding-top: 2px; padding-bottom: 2px;">MVCC</code></span><span md-inline="plain">中，读取的可能是历史数据。也叫做快照读。在时效敏感的业务中，不适用。</span><span md-inline="softbreak"> </span><span md-inline="plain">在时效敏感的业务中，需要读取最新的数据，即当前读。</span><span md-inline="softbreak"> </span><span md-inline="code" spellcheck="false"><code style="font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; vertical-align: initial; font-size: 0.875em; background: rgba(0, 0, 0, 0.05); padding-top: 2px; padding-bottom: 2px;">MVCC</code></span><span md-inline="plain">中（SELECT FROM）读到的数据是旧有的行，其他事务可能已经做了删除或修改。所以在本事务中的修改可能修改的是无效数据。从而是修改无效。</span><span md-inline="softbreak"> </span><span md-inline="plain">所以需要使用当前读，select from lock in share mode/for update</span><span md-inline="softbreak"> </span><span md-inline="plain">update ,insert ,delete 使用的是当前读</span></p><h2 cid="n31" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.63rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.875rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">三、Inno db中的锁机制</span></h2><h3 cid="n33" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">为什么要加锁？</span></h3><p cid="n116" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">锁机制用于解决对共享资源的并发访问。如并发问题，丢失更新。</span><span md-inline="softbreak"> </span><span md-inline="plain">Inno db 锁从颗粒度角度来说，可以分为行锁和表锁。行锁是针对于索引加的锁，而非记录。所以只有通过索引检索数据，才使用行锁。否则使用表锁。</span><span md-inline="softbreak"> </span><span md-inline="plain">行锁效率高，会出现死锁。表锁相反。</span></p><h3 cid="n34" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">什么是乐观锁和悲观锁？</span></h3><p cid="n35" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">乐观锁和悲观锁是两种锁的设计理念。乐观锁假定大概率不会发生更新冲突，所以在数据访问处理的过程中不加锁，只有更新是才检测版本号或时间戳是否存在冲突。</span><span md-inline="softbreak"> </span><span md-inline="plain">悲观锁假定大概率会发生更新冲突，在访问数据，处理数据之前就加排他锁，整个处理过程锁定数据，只有事务提交或回滚才释放锁。</span><span md-inline="softbreak"> </span><span md-inline="plain">悲观锁的实现，比如写锁，即排他锁。</span><span md-inline="softbreak"> </span><span md-inline="plain">乐观锁的是实现，依靠表设计和代码来实现，比如在商品表内添加version版本字段或时间戳字段。</span></p><h3 cid="n36" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">Innodb有三种行锁算法</span></h3><p cid="n37" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">记录锁：record Lock，锁定一个行记录；间隙锁：Gap Lock，锁定一个区间；记录锁+间隙锁：Next-Key Lock 锁定行记录+区间；</span><span md-inline="softbreak"> </span><span md-inline="plain">3.4.1 记录锁就是锁住一行记录；</span><span md-inline="softbreak"> </span><span md-inline="plain">3.4.2. 间隙锁只有在事务隔离级别 RR 中才会产生；</span><span md-inline="softbreak"> </span><span md-inline="plain">3.4.3. 唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；</span><span md-inline="softbreak"> </span><span md-inline="plain">3.4.4. 普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；</span><span md-inline="softbreak"> </span><span md-inline="plain">3.4.5. 间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现 幻读 现象；</span><span md-inline="softbreak"> </span><span md-inline="plain">3.4.6. 普通索引的间隙，优先以普通索引排序，然后再根据主键索引排序</span></p><h3 cid="n38" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">幻读的问题</span></h3><p cid="n39" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">Innodb是怎么在可重复读隔离级别下解决幻行的？</span><span md-inline="softbreak"> </span><span md-inline="plain">间隙锁。</span></p><h2 cid="n40" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.63rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.875rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">四、redo log/binlog/undo log</span></h2><h3 cid="n42" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">redo log</span></h3><p cid="n43" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">实现方式：Innodb存储引擎</span><span md-inline="softbreak"> </span><span md-inline="plain">数据库并不是每次操作都写到磁盘(随机IO)，而是先将操作写到内存，过一段时间，再将所有操作一起写入磁盘（顺序IO），这样可以减少IO操作。</span><span md-inline="softbreak"> </span><span md-inline="plain">redo log就提供这样的功能，每句sql都写到redo log 的内存里，并标记为prepare。当事务提交后，会将此条redolog置为commit状态。之后再进行写入磁盘。</span><span md-inline="softbreak"> </span><span md-inline="plain">这样就减少了IO操作，并保证一旦落库，即使数据库宕机，也能从redolog恢复已提交的事务。</span><span md-inline="softbreak"> </span><span md-inline="plain">redolog缓存是固定大小的循环缓存.</span><span md-inline="softbreak"> </span><span md-inline="plain">redolog 可以保证事务的持久性。当发生故障时，如果有数据没有写入磁盘，在重启数据库时，可以根据redolog重做数据，从而保证事务的持久性。</span></p><h3 cid="n44" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">binlog</span></h3><p cid="n124" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">实现方式：数据库server层</span><span md-inline="softbreak"> </span><span md-inline="plain">binlog 是对数据更新进行的记录，以事务的形式，保存在磁盘中。</span></p><h4 cid="n45" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.12rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; clear: both; overflow-wrap: break-word; color: white; line-height: 1.375rem; white-space: pre-wrap; position: relative;"><span md-inline="plain">主要作用</span></h4><ol cid="n126" mdtype="list" style="margin-top: 0px; margin-bottom: 1.5rem; padding-left: 1.875rem; list-style-position: initial; list-style-image: initial; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><li cid="n131" mdtype="list_item" style="position: relative;"><p cid="n132" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">主从复制</span></p></li><li cid="n139" mdtype="list_item" style="position: relative;"><p cid="n137" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">数据恢复</span></p></li><li cid="n142" mdtype="list_item" style="position: relative;"><p cid="n140" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">增量备份</span></p></li></ol><h4 cid="n125" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.12rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; clear: both; overflow-wrap: break-word; color: white; line-height: 1.375rem; white-space: pre-wrap; position: relative;"><span md-inline="plain">三种模式</span></h4><ol cid="n127" mdtype="list" style="margin-top: 0px; margin-bottom: 1.5rem; padding-left: 1.875rem; list-style-position: initial; list-style-image: initial; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><li cid="n146" mdtype="list_item" style="position: relative;"><p cid="n147" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">statement:保存SQL语句</span></p></li><li cid="n150" mdtype="list_item" style="position: relative;"><p cid="n148" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">row:每行被修改成了何种数据。</span></p></li><li cid="n153" mdtype="list_item" style="position: relative;"><p cid="n151" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 0.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative;"><span md-inline="plain">mixed Level:混合使用，自动判断</span></p></li></ol><h3 cid="n46" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">undo log</span></h3><p cid="n47" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">实现方式：Inno db存储引擎，是逻辑表，即在数据记录中增加列和版本字段。</span><span md-inline="softbreak"> </span><span md-inline="plain">作用：</span><span md-inline="softbreak"> </span><span md-inline="plain">1.实现数据回滚</span><span md-inline="softbreak"> </span><span md-inline="plain">2.实现MVCC</span></p><h3 cid="n48" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">redolog 和binlog 更新顺序</span></h3><p cid="n49" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">两个日志记录的顺序：</span><span md-inline="softbreak"> </span><span md-inline="plain">更新的行如果不在内存，从磁盘取出 -&gt; 修改内存中的值 -&gt; 写入redo-log状态为prepare -&gt; 写bin log -&gt; 提交事务redo-log进行commit</span></p><h3 cid="n123" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.17rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; font-weight: bold; clear: both; overflow-wrap: break-word; color: rgb(222, 222, 222); line-height: 1.5rem; letter-spacing: -1px; white-space: pre-wrap; position: relative;"><span md-inline="plain">innodb_flush_log_at_trx_commit和sync_binlog</span></h3><p cid="n52" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">Log Buffer(日志缓冲区)是一块内存区域用来保存要写入磁盘上的日子文件的数据。主要包括InnoDB存储引擎层日志：redo日志和undo日志。</span></p><h4 cid="n53" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.12rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; clear: both; overflow-wrap: break-word; color: white; line-height: 1.375rem; white-space: pre-wrap; position: relative;"><span md-inline="plain">innodb_flush_log_at_trx_commit参数:</span></h4><p cid="n54" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">0: 由MySQL的main_thread每秒将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。</span></p><p cid="n55" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">1：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。</span></p><p cid="n56" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">2：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并由存储引擎的main_thread 每秒将日志刷新到磁盘。</span></p><h4 cid="n57" mdtype="heading" style="break-after: avoid-page; break-inside: avoid; orphans: 4; font-size: 1.12rem; margin-top: 0px; margin-bottom: 1.5rem; font-family: &quot;Lucida Grande&quot;, Corbel, sans-serif; clear: both; overflow-wrap: break-word; color: white; line-height: 1.375rem; white-space: pre-wrap; position: relative;"><span md-inline="plain">sync_binlog参数</span></h4><p cid="n58" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">0 ：存储引擎不进行binlog的刷新到磁盘，而由操作系统的文件系统控制缓存刷新。</span></p><p cid="n59" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">1：每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新，这种方式最安全，但性能较低。</span></p><p cid="n60" mdtype="paragraph" style="line-height: inherit; orphans: 4; margin-top: 0px; margin-bottom: 1.5rem; overflow-wrap: break-word; white-space: pre-wrap; position: relative; color: rgb(184, 191, 198); font-family: &quot;Helvetica Neue&quot;, Helvetica, Arial, &quot;Segoe UI Emoji&quot;, sans-serif; font-size: 16px;"><span md-inline="plain">n：当提交的日志组=n时，存储引擎调用文件系统的sync操作进行一次缓存的刷新。</span></p>