MySQL
<h2>1 基础架构：一条sql查询语句是如何执行的</h2>
<h3>mysql基本架构</h3>
<p><img src="https://s2.ax1x.com/2020/03/11/8Ab4zQ.md.png" alt="mysql基本架构" /></p>
<p>大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。</p>
<p>Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服
务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都
在这一层实现，比如存储过程、触发器、视图等。</p>
<p>而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、
Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成
为了默认存储引擎。</p>
<p>执行 create table 建表的时候，如果不指定引擎类型，默认使用InnoDB。不同的存储引擎共用一个Server 层。</p>
<h2>3(8) 事务隔离</h2>
<p>事务具有原子性、一致性、隔离性、持久性，简称ACID。在MySQL中，事务支持是在引擎层中实现的。
当数据库上有多个事务同时执行的时候，就可能出现脏读、不可重复读、幻读的问题，为了解决这些问题，就有了“隔离级别”的概念。
我们知道，隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。事务隔离级别包括：读未提交、读提交、可重复读和串行化。</p>
<ul>
<li>读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到；</li>
<li>读提交是指，一个事务提交之后，它做的变更才会被其他事务看到；</li>
<li>可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。</li>
<li>串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。</li>
</ul>
<h3>事务隔离级别的实现</h3>
<p>在实现上，1. “读未提交”隔离级别下直接返回记录上的最新值；2. “串行化”隔离级别下直接用加锁的方式来避免并行访问；3.读提交和可重复读的实现类似，都是通过多版本并发控制(MVCC)实现的。</p>
<h3>MVCC和回滚日志</h3>
<p>InnoDB里每个事务都有一个唯一的事务id，叫做transaction id。它是事务开始时向系统事务系统申请的，是按申请顺序严格递增的。</p>
<p>每行数据是有多个数据版本的，每次事务更新数据的时候，都会生成一个新的数据版本。并且把transaction id赋值给这个数据版本的事务ID，记做 row trx_id。同时，旧的数据版本需要保留，并且在新的数据版本中，能够有信息能够直接拿到他。这就是数据库的多版本并发控制（MVCC）。</p>
<p>MVCC 主要应用于 Read Commited 和 Repeatable read 两个事务隔离级别。</p>
<p><img src="https://s1.ax1x.com/2020/03/15/83wfyR.jpg" alt="行状态变更" />
图中虚线框里表示同一行数据的四个版本，当前最新版本是V4，k的值是22，是被事务id为25的事务更新的。
实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。三个虚线箭头U1、U2、U3就是回滚日志(undo log)，数据版本V1、V2、V3并不是物理上真实存在的，而是需要经过当前版本和undo log计算出来。
回滚日志可用于快照读和事务失败后的数据回滚。MySQL在合适的时机(不需要的时候）会清理undoLog。</p>
<h3>可重复读和读提交的具体实现</h3>
<p>一个事务只需要在它启动的时候，找到所有已提交事务id的最大值，记为 up_limit_id；然后声明，如果一个数据版本的row_trx_id大于up_limmit_id，我就不认，必须找到它的上一个版本。当然，如果一个事务自己更新的数据，它自己还是要认的。</p>
<p>还是从上图来说，如果一个事务启动时，它的up_limit_id 是18，那么当它访问这一行数据时，就会从V4通过U3计算出V3，在可重复读级别下，这个事务读到的k值就是11。</p>
<p>InnoDB利用“所有数据都有多个版本”这个特性，事务只要记住启动时的已提交事务id的最大值，就实现了秒级创建快照的能力。</p>
<p>我们再通过一个例子来仔细看看：
<a href="https://imgchr.com/i/83cGGV"><img src="https://s1.ax1x.com/2020/03/15/83cGGV.jpg" alt="事务执行流程" /></a>
先做如下假设：事务A开始之前，1. 这一行的数据是(1,1);2. 已提交的最大事务id是99；3. 事务A、B、C、的版本号分别是100、101、102；4. (1,1)这一行数据的row_trx_id是90(当前数据版本号）。</p>
<ol>
<li>事务C执行更新后，数据从(1,1)变成(1,2)，这行数据的最新版本号row_trx_id 变成102，90变成历史版本号；</li>
<li>事务B执行完之后，数据从(1,2)变成(1,3)，这行数据的最新版本号变成101，102变成历史版本号；</li>
<li>事务A要读取数据，它的up_limit_id是99，从当前数据版本101开始读起，发现大于up_limit_id；读上一个数据版本102，还是大于；再读上一个数据版本，数据版本是90，满足条件，把当前数据(1,1)读出来。这样就保证了事务启动时读到的一样，称之为一致性读。</li>
</ol>
<p>如果只有这三个事务的话，在事务A提交后，数据版本90(1,1)就可以删除了，因为没有事务再需要它。</p>
<h3>更新逻辑</h3>
<p>我们发现事务B是将(1,2)更新成(1,3)，那它更新的时候到底是读哪一个版本的数据来更新的？为什么读到的不是(1,1)这个数据？因为事务B最开始的查询操作拿到的数据也是(1,1)。</p>
<p>这里我们需要知道一条规则：<strong>更新数据都是先读后写的，而这个读，只能是读当前的值，称为当前读。</strong></p>
<p>所以，事务B更新的时候读到的数据是当前最新版本的值(1,2)，然后更新成(1,3)。事务B最后的查询Q1，读到的值是(1,3)。因为执行Q1的时候，最新的数据版本是101，自己的数据版本也是101，是在事务中自己更新自己，查到的数据就是(1,3)。</p>
<p>什么时候会是当前读呢？<strong>1.更新数据update的时候；2.select语句加锁的时候(如加上，lock in share mode或者for update)；</strong>
什么时候是一致性读？普通查询的时候。</p>
<p>可重复读的核心就是<strong>一致性读</strong>；而事务更新数据的时候只能是<strong>当前读</strong>，如果当前记录的行锁被其它事务占用，就需要进入锁等待状态。</p>
<p>读提交和可重复读的逻辑类似，他们的区别是：</p>
<ul>
<li>在可重复读级别下，只需要在事务开始的时候找到up_limit_id，之后事务的查询都用这个up_limit_id；</li>
<li>在读提交级别下，每个语句执行前都会重新计算up_limit_id 的值。</li>
</ul>
<h2>4(5) 深入浅出索引</h2>
<p>索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。</p>
<h3>InnoDB索引模型</h3>
<p>在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。这是为了减少访问盘的次数，一个10亿条记录的表，最多只用读三次盘就可以查到了。每一个索引在 InnoDB 里面对应一棵 B+ 树。
索引的类型可以分为主键索引和普通索引。主键索引（聚簇索引）的叶子节点存储的是整行数据，普通索引（二级索引）的叶子节点存储的是主键的值。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。</p>
<ul>
<li>回表：由于查找结果所需要的的数据只有在主键索引上有，我们通过普通索引查到主键值，然后回到主键索引树搜索的过程，叫做回表。</li>
<li>覆盖索引：如果我们查找需要的数据已经在普通索引树上有了，就不需要回表，直接返回结果，这就叫覆盖索引。</li>
<li>最左前缀原则：最左前缀指的是联合索引的最左前N个字段。</li>
<li>索引下推：在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数。</li>
</ul>
<p>由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以覆盖索引是一个常用的性能优化手段。在适当的情况下，通过联合索引，可以有效使用覆盖索引进行优化。
索引项是按照索引定义里面的字段顺序排序的，在建立联合索引时，我们可以通过调整索引内的字段顺序来减少维护一个索引。</p>
<h2>6 全局锁和表锁</h2>
<p>根据加锁的范围，MySQL里面的锁大致可分为全局锁、表锁和行锁三类。</p>
<ul>
<li>全局锁是对整个数据库实例加锁。全局锁的典型使用场景是做全库逻辑备份，如果不加锁可能会导致数据不一致。（支持事务的存储引擎可以不用全局锁备份库，有逻辑备份工具mysqldump）</li>
<li>MySQL表级别的锁有两种：一种是元数据锁（MDL），一种是表锁。表锁是在数据库引擎不支持行锁的时候才会用到。元数据锁的作用是，保证读写的正确性，它是会自动加上的。当对一个表做增删改查的时候加MDL读锁，当对表结构做变更的时候，加MDL写锁。MDL会直到事务提交才释放，在做表结构变更时，要注意不要锁住线上查询和更新。</li>
</ul>
<h2>7 行锁功过</h2>
<p>MySQL行锁是在存储引擎层实现的，行锁，顾名思义就是针对数据表中行记录的锁。但并不是所有引擎都支持行锁，不支持行锁意味着所有并发控制只能使用表锁控制。
<strong> 两阶段锁：</strong>
要了解行锁先从两阶段锁说起，在InnoDB事务中，行锁是在需要时才添加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这就是两阶段锁协议。
如果你的事务中需要多个行锁，需要把可能最可能造成冲突，最可能影响并发的锁尽量往后放。
<strong> 死锁和死锁检测：</strong></p>
<ul>
<li>死锁：当系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待资源的状态，称为死锁。</li>
<li>死锁解决策略：
<ol>
<li>直接进入等待，直到超时，这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置，默认50s；</li>
<li>发起死锁检测，发现死锁后，主动回滚死锁链条中的某一事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为on，表示开启这个逻辑，默认为on。</li>
</ol></li>
</ul>
<p>一般我们采用的是死锁检测的方式，因为进行等待的时间太长了，设置的时间过短又可能造成误伤，把没发生死锁的事务当做死锁处理。</p>
<ul>
<li>死锁检测： 当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现循环等待，也就是死锁。</li>
</ul>
<p>每个新来的被堵住的线程，都要判断会不会由于自己的加入而导致了死锁，这是一个复杂度为O(n)的操作。若大量并发操作同一行数据，虽然最终检测没有发生死锁，但是这期间消耗了大量cpu资源，导致系统处理能力降低。</p>
<p>如何解决这种热点行更新导致的性能问题呢？</p>
<ol>
<li>若确保这个业务一定不会出现死锁，可以临时把死锁检测关掉；</li>
<li>控制并发度，比如同一行同时最多只有N个线程在更新；</li>
<li>如果有能修改MySQL源码的人，对于同行更新，在进入引擎之前排队；</li>
<li>从设计上优化，通过将一行改成逻辑上的多行来减少冲突，这个逻辑上的多行汇总在一起就变成了之前的一行；</li>
</ol>
<h2>9 普通索引和唯一索引如何选择</h2>
<blockquote>
<p>普通索引：索引列上的值可以重复；唯一索引：索引列上的值不可重复；</p>
</blockquote>
<h3>查询过程</h3>
<p>对于这样一条查询语句：select id from T where k=5 ，索引建在列k上，从B+树的根节点开始搜索</p>
<ul>
<li>对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。</li>
<li>对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。</li>
</ul>
<p>对于 InnoDB 来说，数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。对性能的影响微乎其微。</p>
<p>如果 k=5 这条记录刚好是这一页的最后一条记录，那么需要读取下一数据页到内存中，但是一个数据页可以放近千个 key ，这种情况是非常少的，所以，平均来说，性能差异还是微乎其微。</p>
<h3>更新过程</h3>
<h4>change buffer：</h4>
<blockquote>
<p>当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。</p>
</blockquote>
<p>需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。</p>
<h4>purge：</h4>
<blockquote>
<p>将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为purge。</p>
</blockquote>
<p>除了访问这个数据页会触发 purge 外，系统有后台线程会定期 purge。在数据库正常关闭（shutdown）的过程中，也会执行 purge 操作。</p>
<p>显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。</p>
<h4>buffer change 在什么条件下能使用</h4>
<p>对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。</p>
<p>因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。</p>
<h3>更新流程</h3>
<p>如果要在这张表中插入一个新记录(4,400)，InnoDB 的处理流程是这样的：</p>
<p>第一种情况是，这个记录要更新的目标页在内存中:</p>
<ul>
<li>对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；</li>
<li>对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。</li>
</ul>
<p>这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU 时间。</p>
<p>另一种情况是，这个记录要更新的目标页不在内存中：</p>
<ul>
<li>对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；</li>
<li>对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。</li>
</ul>
<p>将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机
磁盘访问，所以对更新性能的提升是会很明显的。普通索引在这种情况下更新效率更佳。</p>
<h4>chang buffer 适合在什么场景使用</h4>
<p>通过以上分析，change buffer 对更新过程的加速作用，也清楚了 change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。</p>
<p>但是，普通索引并不是在所有场景下，change buffer 都能起到加速作用。实际上，只有在写多读少的情况下，才会提升效率。
这是因为写多读少情况下，可以避免频繁读取磁盘，而是将更新操作缓存下来，当读取数据时一次性写入磁盘。而写一次就读一次的情况下，数据都已经从磁盘读入内存，直接更新效率更佳，也不用产生维护 change buffer 的额外开销。</p>
<p>综上所述，一般情况下，我们建议尽量使用普通索引，这样可以利用到 chang buffer 提高更新效率。如果在更新之后，立刻有查询操作，建议关闭 change buffer。</p>
<h3>redo log 和 change buffer</h3>
<p>redo log 和 change buffer 容易混淆。为了便于区分，可从下图分析：
<img src="https://s2.ax1x.com/2020/03/11/8AbhRg.png" alt="change_buffer和redo_log" />
更新两条记录id1和id2，page1在内存中，page2不在内存中。虚线箭头，是后台操作，不影响更新的响应时间。</p>
<ol>
<li>Page 1 在内存中，直接更新内存；</li>
<li>Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一
行”这个信息；</li>
<li>将上述两个动作记入 redo log 中（图中 3 和 4）。</li>
</ol>
<p>redo log 中记录了更新操作，包括change buffer 更新和直接更新内存。</p>
<p>如果在更新后不久，要执行 select * from t where k in (k1, k2)，内存中的数据都还在。
那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了。读取数据如下图所示：
<img src="https://s2.ax1x.com/2020/03/11/8Ab2If.png" alt="读取数据" /></p>
<ol>
<li>读 Page 1 的时候，直接从内存返回。WAL 之后如果读数据，也不用更新到磁盘，而是可以直接从内存中读(内存中的数据最新，磁盘数据不是最新)。</li>
<li>要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。</li>
</ol>
<p>可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。</p>
<p>所以，对比redo log 和 change buffer 这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。</p>
<h2>10 MySQL有时候为什么会选错索引</h2>
<p>扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。</p>
<h3>扫描行数判断</h3>
<p>MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。</p>
<p>这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。
<img src="https://s2.ax1x.com/2020/03/11/8AbHZq.png" alt="显示区分度" /></p>
<p>mysql 选错索引，归咎于没能准确的判断出扫描行数。扫描行数是通过 “基数”预估的。从性能方面考虑，基数是通过采样统计的方法计算的。InnoDB默认选择N个数据页，计算平均数，然后乘以总页数，就得到了总基数。</p>
<p>既然是统计信息（基数）不对，那就要修正，通过 analyze table t 命令，可以用来计算统计信息。</p>
<p>所以在实践中，如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。</p>
<h3>小结</h3>
<blockquote>
<p>对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修 改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。</p>
</blockquote>
<h2>11 如何给字符串字段加索引</h2>
<h3>使用前缀索引</h3>
<p>使用前缀索引，可能会增加扫描行数，影响到性能。但是定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。</p>
<p>实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。</p>
<p>可以使用下面这个语句，算出这个列上有多少个不同的值：</p>
<pre><code class="language-sql">select count(distinct email) as L from SUser;</code></pre>
<p>然后，依次选取不同长度的前缀来看这个值：</p>
<pre><code class="language-text">select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;</code></pre>
<p>你需要预先设定一个可以接受的损失比例，比如5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值。</p>
<p>但是，使用前缀索引就用不上覆盖索引对查询性能的优化了，必须回表去主键索引树上搜索，因为系统不确定前缀索引的定义是否截断了完整信息。这是需要考虑的一个点。</p>
<h3>其它方式</h3>
<p>对于前缀区分度好的，使用前缀索引的效果可能还不错。但是，遇到前缀的区分度不够好的情况时（如身份证号），就不适用于使用前缀索引就。</p>
<ul>
<li>倒序存储：如果字符串的从后面看，区分度很好的话，可以将数据倒序存储，这样区分度就很好了。</li>
<li>使用hash字段：可以在表上再创建一个整数字段，来保存字符串的校验码，同时在这个字段上创建索引。</li>
</ul>
<p>两种方式的异同：</p>
<ul>
<li>相同点：
<ol>
<li>都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，与原顺序不同了，
同样地，hash 字段的方式也只能支持等值查询。</li>
</ol></li>
<li>不同点：
<ol>
<li>从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。</li>
<li>在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。</li>
<li>从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。</li>
</ol></li>
</ul>
<h3>小结</h3>
<p>字符串创建索引的方式有以下几种，在实际应用中，你要根据业务字段的特点选择使用哪种方式。</p>
<ol>
<li>直接创建完整索引，这样可能比较占用空间；</li>
<li>创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；</li>
<li>倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；</li>
<li>创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。</li>
</ol>
<h2>12 为什么SQL会“抖”一下</h2>
<ul>
<li>flush：把内存里的数据写入磁盘的过程，称为flush。</li>
<li>脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。</li>
<li>干净页：内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。</li>
</ul>
<p>不论是脏页还是干净页，都在内存中。我们用孔乙己的例子来说明一下，如图：
<img src="https://s2.ax1x.com/2020/03/11/8AbWi8.png" alt="mysql更新模型1" />
<img src="https://s2.ax1x.com/2020/03/11/8AbgdP.png" alt="mysql更新模型2" />
平时执行很快的更新操作，其实就是在写内存和日志，而MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。</p>
<p>那么，什么情况下会引发MySQL的flush过程呢？</p>
<ol>
<li>第一种场景是，粉板（redo log日志）满了，记不下了。</li>
<li>第二种场景是，系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给新的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。</li>
<li>第三种情况是，MySQL 认为系统“空闲”的时候，后台系统自动flush。</li>
<li>第四种情况是，MySQL 正常关闭的情况，这时候 MySQL 会把内存的脏页都 flush 到磁盘上。</li>
</ol>
<p>后两种对数据库性能没有太大影响，因为一种是在MySQL空闲的时候flush，另一种是MySQL本来就要关闭了。主要分析一下前两种。</p>
<p>第一种是“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现
这种情况的时候，整个系统就不能再接受更新了，所有的更新都阻塞住了。</p>
<p>第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。</p>
<p>InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
第一种是，还没有使用的；
第二种是，使用了并且是干净页；
第三种是，使用了并且是脏页。</p>
<p>InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，第一种未被使用的页面很少。当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页，就必须将脏页先刷到磁盘，变成干净页后才能复用。</p>
<p>所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：</p>
<ol>
<li>一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；</li>
<li>日志写满，更新全部堵住，写性能跌为 0。</li>
</ol>
<p>InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。可以根据缓冲池中的脏页比例和redo log 的
写盘速度按照一定的比例（全力刷脏页的比例）来控制刷脏页的速度。</p>
<h2>13 表数据删除了，表文件大小不变</h2>
<p>表数据既可以存在共享表空间里，也可以是单独的文件。由参数innodb_file_per_table 控制，默认为ON。</p>
<ol>
<li>这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；</li>
<li>这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。</li>
</ol>
<p>将 innodb_file_per_table 设置为 ON，是推荐做法，我们接下来的讨论都是基于这个设置展开的。</p>
<p>我们在删除整个表的时候，可以使用 drop table 命令回收表空间。但是，我们遇到的更多的删除数据的场景是删除某些行。</p>
<h3>数据删除流程</h3>
<p>先来看一下 InnoDB 中一个索引的示意图。
<img src="https://s2.ax1x.com/2020/03/11/8AbIMj.png" alt="B+树索引示意图" />
假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。如果我们删掉了一个数据页上的所有记录，整个数据页都可以被复用，但磁盘大小不变。</p>
<p>数据页的复用跟记录的复用是不同的。</p>
<p>记录的复用，只限于符合范围条件的数据。比如删除掉ID为400的记录，只有插入ID为400的记录才会被复用，插入ID为800的记录不会复用原来的位置。而当整个页从 B+ 树里面摘掉以后，可以复用到任何位置。</p>
<p>如果我们用delete删除掉整个表，结果就是所有的数据页都会被标记为可复用，但是磁盘大小不会变小。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。实际上，不止是删除数据会造成空洞，更新、插入数据也会。</p>
<p>如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。</p>
<p>也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。</p>
<p>重建表有三种方式，optimize table、analyze table 和 alter table，它们的区别是：</p>
<ul>
<li>alter table t engine = InnoDB（也就是 recreate）是真正的重建表；</li>
<li>analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；</li>
<li>optimize table t 等于 recreate+analyze。</li>
</ul>
<h2>14 count(*)为什么这么慢</h2>
<p>在不同的MySQL引擎中，count(*)有不同的实现方式。</p>
<ul>
<li>MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；</li>
<li>而InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。</li>
</ul>
<p>这里说的是没加过滤条件的count(*)。</p>
<p>为什么InnoDB不跟MyISAM一样，也把数字存起来呢？这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB表“应该返回多少行”也是不确定的。</p>
<ul>
<li>MyISAM表虽然count(*)很快，但是不支持事务；</li>
<li>show table status命令虽然返回很快，但是不准确；</li>
<li>InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。</li>
</ul>
<p>count(*)、count(主键id)、count(字段)和count(1)等不同用法的性能，有哪些差别。</p>
<p>楚count()的语义是，count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。</p>
<p>所以，count(*)、count(主键id)和count(1) 都表示返回满足条件的结果集的总行数，但是count(主键id)会将主键id取出来给server层，另外两个不取值，直接返回，所以效率还是比另两个低一些；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。</p>
<p>count星 是做了专门优化的，按照效率排序的话，count(字段)<count(主键id)<count(1)≈count星，所以我建议你，尽量使用count(*)。</p>
<h2>23 mysql 是怎么保证数据不丢失的</h2>
<h3>23.1 binlog 的写入机制</h3>
<p>binlog 的写入逻辑比较简单：执行事务的时候，先把日志写入binlog cache，事务提交的时候，再把 binlog cache 写入 binlog 文件中，然后清空binlog cache。
一个事务的 binlog 是不能拆开的，无论这个事务多大，也要确保一次性写入，可通过binlog_cache_size 参数来设置 binlog 大小，如果超过了参数规定的大小，那就要暂存磁盘。
<img src="https://s2.ax1x.com/2020/03/11/8AbfJS.png" alt="binlog写盘状态" />
可以看到，每个线程都有自己的binlog cache，但共用一份binlog文件。</p>
<ul>
<li>图中write指的是把日志写入文件系统的page cache，并没有持久化到磁盘，速度快；</li>
<li>图中fsync指的是将数据持久化到磁盘，会占IOPS。</li>
</ul>
<p>write和fsync的时机是由参数sync_binlog控制的：</p>
<ol>
<li>sync_binlog=0，表示每次提交事务都只write，不fsync；</li>
<li>sync_binlog=1，每次提交事务都会执行fsync；</li>
<li>sync_binlog=N，每次提交事务只write，但积累N次事务后，会fsync。
在遇到IO瓶颈时，会将N设为一个比较大的值(100-1000之间)，但发生异常重启，会丢失最近N个事务的binlog日志。
<h3>23.2 redo log写入机制</h3>
<p>事务执行过程中，生成的redo log要先写入 redo log buffer，跟binlog一样，也并不是每次写入 redo log buffer 都会持久化到磁盘。</p></li>
</ol>
<p>redolog 的三种存储状态：
<img src="https://s2.ax1x.com/2020/03/11/8Aboss.png" alt="redo log 存储状态" /></p>
<ol>
<li>存储在redo log buffer中；</li>
<li>执行write操作，还没执行fsync，存储在文件系统的 page cache 中；</li>
<li>持久化到磁盘，对应的是hard disk；
innodb_flush_log_at_trx_commit 参数控制了redo log 的写入策略：</li>
<li>设为0时，每次事务提交都把redo log写入 redo log buffer；</li>
<li>设为1时，每次事务提交都把redo log 持久化到磁盘；</li>
<li>设为2时，每次事务提交只把redo log写入 page cache；
InnoDB有一个后台进程，每个1秒，会把redo log buffer的日志，调用write方法写入文件系统的 page cache中，然后再调用fsync方法刷新到磁盘。
事务执行过程中的redo log也是直接写入 redo log buffer中，所以没提交的日志，也有可能持久化到磁盘。
每秒后台都会在后台线程中持久化，InnoDB就认为redo log在事务提交时不需要fsync，只要把日志write入文件系统中的page cache就可以了。</li>
</ol>
<h3>23.3 组提交机制</h3>
<p>我们常说的MySQL"双1"配置，指的是将sync_binlog和innodb_flush_log_at_trx_commit都设为1，也就是说，一个事务完整提交前，要经过两次写盘操作，一次是binlog，一次是redo log (prepare阶段)。
这就意味着，当MySQL的tps是两万的时候，每秒就会写盘4万次，但实际情况并不是如此，而是比4万少很多，这就要说到组提交(group commit)了。</p>
<p>日志逻辑序号LSN：LSN是单调递增的，用来对应 redo log 的一个个写入点，每次写入长度为 length 的 redo log，LSN的值就会加上length。</p>
<p><img src="https://s2.ax1x.com/2020/03/11/8AbTLn.png" alt="组提交" />
从图中可以看到：</p>
<ol>
<li>trx1是第一个到的，会被选为这组的leader；</li>
<li>当trx1开始准备写盘时，这个组里面已经有了三个事务，这时LSN也变成了160；</li>
<li>当trx1去写盘的时候，带的LSN就是160，因此等trx1返回时，所有LSN小于等于160的 redo log 已经写入磁盘；</li>
<li>这时trx2和trx3就可以直接返回了。</li>
</ol>
<p>因此，一次组提交里面，组员越多，就越节约IOPS。在并发更新场景下，第一个事务写完redo log buffer 之后，接下来的fsync方法越晚调用，组员可能越多，节约IOPS资源的效果越好。</p>
<p>再来看看两阶段提交是如何拖延时间，使fsync更晚的被调用的：</p>
<p><img src="https://s2.ax1x.com/2020/03/11/8Abbd0.png" alt="两阶段提交" />
实际上，'写binlog'是分为两部分的：</p>
<ol>
<li>先把binlog从binlog cache 写到 binlog文件中；</li>
<li>调用fsync持久化到磁盘；</li>
</ol>
<p>mysql为了组提交的效果更好，将redo log的fsync操作延缓到上面步骤1 之后，两阶段提交就变成这样：
<img src="https://s2.ax1x.com/2020/03/11/8AbqoV.png" alt="两阶段提交细化" />
这样一来，binlog也可以进行组提交了，在执行上图中第4步把binlog 刷新到磁盘时，如果有多个binlog已经写完，也是会一起持久化的，这样就减少了IOPS的消耗；</p>
<p>不过第3步通常执行得很快，binlog的write和fsync之间的时间间隔很少，一起持久化的组员很少，因此binlog的组提交效果没有redo log的那么好。</p>
<p>若想提高binlog的组提交效果，可以通过设置 binlog_group_commit_sync_delay和binlog_group_commit_sync_delay_count来实现，前者表示延迟多少微秒才调用fsync，后者表示积累多少次以后才调用fsync。两个条件只要满足一个就生效。</p>
<p>通过以上分析，WAL机制(先写日志，再刷磁盘)主要得益于两个方面：</p>
<ol>
<li>redo log和binlog都是顺序写，磁盘的顺序写比随机写要快；</li>
<li>组提交机制，可以大大降低磁盘的IOPS消耗。</li>
</ol>
<h2>24 mysql如何保证主备一致</h2>
<h3>24.1 mysql主备基本原理</h3>
<p><img src="https://s2.ax1x.com/2020/03/11/8AbOiT.png" alt="主备切换流程" />
在状态1中，客户端读写都是直接访问节点A，节点B只是只是A的备库（B设置为只读），将A的更新都同步过来，到本地执行。保证了A、B数据的一致性。
当需要切换的时候，就切换到状态2，客户端与节点B交互，A是B的备库。</p>
<p>当一个写操作在节点A执行时，节点A到B的数据同步流程是怎样的呢？
<img src="https://s2.ax1x.com/2020/03/11/8AbjWF.png" alt="主备同步流程" />
上图包含了binlog和redo log的写入机制相关内容，主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。
主库与备库之间维持一个长连接，一个事务日志的完整同步过程是：</p>
<ol>
<li>在备库B上通过 change master 命令，设置主库A的连接信息以及要从哪个位置开始请求binlog，包括文件名和日志偏移量；</li>
<li>在备库B上执行 start slave 命令，这时候备库会启动两个线程，io-thread 和 sql-thread。其中io-thread负责与主库建立连接；</li>
<li>主库A校验完连接信息后，开始按照备库B传过来的位置，从本地读取binlog，发送给备库B；</li>
<li>备库B拿到binlog后，写到本地，称为中转日志(relay log)；</li>
<li>sql-thread 读取中转日志，解析出里面的命令，并执行。</li>
</ol>
<p><strong>binlog的三种格式：</strong></p>
<ol>
<li>statement格式，记录的是语句原文，执行完之后可能会出现主备数据不一致的问题</li>
<li>row格式，记录每一行数据被修改的形式，日志的内容很大</li>
<li>mixed格式，statement格式和row格式的混合，中和了两种格式的优点，mysql会自动判断语句是否可能会引起主备不一致，是的话就用row，否的话用statement。</li>
</ol>
<p>在互为主备的模式下（切换的时候不用修改主备关系），会产生循环复制问题，循环复制问题可以通过设置server_id来解决。</p>
<h2>25 mysql如何保证高可用</h2>
<p>正常情况下，只要主库执行更新生成的所有binlog都能传到备库并正确执行，备库就能达到跟主库一致的状态，这就是最终一致性。
但是，只有最终一致性是不够的。</p>
<h3>25.1 主备延迟</h3>
<p>与数据同步相关的时间点主要有三个：</p>
<ol>
<li>主库执行完一个事务，写入binlog，这个时间点记为T1；</li>
<li>主库将binlog同步给备库，备库接收完binlog的时间点记为T2；</li>
<li>备库根据同步过来的binlog，执行完这个事务的时间点记为T3。
所谓主备延迟指的是，同一个事务在主库执行完的时间T1和备库执行完的时间T3的差值，即 T3-T1。</li>
</ol>
<p>可以在备库上执行 show slave status命令，返回结果里面seconds_behind_master的值就表示当前备库延迟了多少秒。</p>
<p>网络正常情况下，主库同步给备库的时间是非常短的，即T2-T1很小；主备延迟主要来自于接收完binlog日志到执行完事务这段时间，即T3-T2。
主备延迟最直接的表现是，备库消费中转日志(relay log)的速度比主库产生binlog的速度要慢。</p>
<p><strong>主备延迟的来源：</strong></p>
<ol>
<li>在某些情况下，备库采用的机器的性能比主库机器的性能差；（解决方式：对称部署）</li>
<li>备库的读压力很大，耗费了大量资源；（解决方式：一主多从）</li>
<li>大事物引起的主备延迟，比如一个语句执行了10分钟，主从延迟就会有10分钟；</li>
<li>备库的并行复制能力差；</li>
</ol>
<p><strong>主备切换流程：</strong></p>
<ul>
<li>可靠性优先
<ol>
<li>判断备库的seconds_behind_master是否小于某个值(如5秒),是的话继续，否则重试；</li>
<li>把主库改成只读状态，即把readonly设为true；</li>
<li>判断备库seconds_behind_master的值，直到它为0为止；</li>
<li>把备库改成可读写状态，即把readonly设为false；</li>
<li>把业务请求切换到备库。</li>
</ol></li>
</ul>
<p>可以看到，在这个主备切换过程中是有不可用时间的，从步骤2到步骤5完成之前这段时间系统是不可用的；这里面主要耗时的是步骤3，这也是为什么在步骤1要将主备延迟小于某个值才开始后续操作。</p>
<ul>
<li>可用性优先
将步骤4和5直接放在最前面，这就是可用性优先切换，但是这样会有数据不一致的风险，所以建议不要用这种方式。</li>
</ul>
<p>如果主备延迟很大，并且主库突然宕机。在可靠性优先的情况下，系统会有很长一段时间不可用，这是不能接受的；
在可用性优先的情况下，先将备库设为只读并切换过来，但是由于很多数据还没同步完，客户端会认为数据丢失，这也是不可接受的；所以我们要尽量缩短主备延迟的时间。</p>
<p><strong>多线程复制：</strong>
为什么需要多线程复制呢？这是因为单线程复制能力全面低于多线程复制能力，对于更新压力较大的主库，备库可能一直追不上主库，导致主备延迟的值越来越大。</p>
<p>在多线程复制时，需要满足两个基本要求：</p>
<ol>
<li>不能造成更新覆盖，即更新同一行的两个事务，必须放在同一个worker中；</li>
<li>同一个事务不能拆开，必须放在同一个worker中。
<h2>34 可不可以使用join？</h2></li>
</ol>
<p>MySQL执⾏join语句的两种可能算法(对Index Nested-Loop Join和Block Nested-Loop Join)，这两种算法是由能否使⽤被驱动表的索引决定的。⽽能否⽤上被驱
动表的索引，对join语句的性能影响很⼤。</p>
<ol>
<li>如果可以使⽤被驱动表的索引，join语句还是有其优势的；</li>
<li>不能使⽤被驱动表的索引，只能使⽤Block Nested-Loop Join算法，这样的语句就尽量不要使⽤；</li>
<li>在使⽤join的时候，应该让⼩表做驱动表。</li>
</ol>
<p>所谓的小表是指，在决定哪个表做驱动表的时候，应该是两个表按照各⾃的条件过滤，过滤完成之后，计算参与join的各个
字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。</p>