MySQL

典型回答 MySQL的Buffer Pool是一个内存区域，用于缓存数据页，从而提高查询性能。读写过程涉及到数据的从磁盘到内存的读取，以及在内存中的修改和写回磁盘。 ✅什么是buffer pool？ 读过程 当我们在MySQL执行一个查询请求的时候，他的过程是这样的： MySQL首先检查Buffer Pool中是否存在本次查询的数据。如果数据在Buffer Pool中，就直接返回结果。 如果数据不在Buffer Pool中，MySQL会从磁盘读取数据。 读取的数据页被放入Buffer Pool，同时MySQL会返回请求的数据给应用程序。 读的过程比较简单的，而Buffer Pool的写的过程就有点复杂了 写过程 当我们执行一次更新语句，如INSERT、UPDATE或DELETE等时，会进行以下过程 当应用程序执行写操作时，MySQL首先将要修改的数据页加载到Buffer Pool中。 在Buffer Pool中，对数据页进行修改，以满足写请求。这些修改只在内存中进行，不会立即写回磁盘。 如果Buffer Pool中的数据页被修改过，MySQL会将这个页标记为“脏页”（Dirty Page）。 脏页被写回磁盘，此时写入操作完成，数据持久化。 但是需要注意的是，脏页写回磁盘是由一个后台线程进行的，在MySQL服务器空闲或负载较低时，InnoDB会进行脏页刷盘，以减少对用户线程的影响，降低对性能的影响。（https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-flushing.html ） （图源自：✅InnoDB的一次更新事务是怎么实现的？） 当脏页的百分比达到innodb_max_dirty_pages_pct_lwm变量定义的低水位标记时，将启动缓冲池刷新。缓冲池页的默认低水位标记为10%。innodb_max_dirty_pages_pct_lwm值设为0会禁用这种提前刷新行为。 InnoDB还使用了一种适应性刷新算法，根据redo log的生成速度和当前的刷新率动态调整刷新速度。其目的是通过确保刷新活动与当前工作负载保持同步，来平滑整体性能。 当然，我们也可以手动触发脏页的刷新到磁盘，例如通过执行SET GLOBAL innodb_buffer_pool_dump_now=ON 来进行一次脏页刷新。 还有一种情况，就是在MySQL服务器正常关闭或重启时，所有的脏页都会被刷新到磁盘。这样才能保证数据可以持久化下来。

典型回答 **COUNT(1)**** 和 ****COUNT(*)**表示的是直接查询符合条件的数据库表的行数。而**COUNT(列名)**表示的是查询符合条件的列的值不为NULL的行数。 除了查询得到结果集有区别之外，在性能方面COUNT(*)是约等于COUNT(1)的，但是COUNT(*_)_是SQL92定义的标准统计行数的语法，因为他是标准语法，所以MySQL数据库对他进行过很多优化。 扩展知识 COUNT 关于COUNT函数，在MySQL官网中有详细介绍： 简单翻译一下： 1、COUNT(expr) ，返回SELECT语句检索的行中expr的值不为NULL的数量。结果是一个BIGINT值。 2、如果查询结果没有命中任何记录，则返回0 3、但是，值得注意的是，COUNT(*) 的统计结果中，会包含值为NULL的行数。 即以下表记录 1 2 3 4 5 6 7 8 create table #bla(id int,id2 int) insert #bla values(null,null) insert #bla values(1,null) insert #bla values(null,1) insert #bla values(1,null) insert #bla values(null,1) insert #bla values(1,null) insert #bla values(null,null) 使用语句count(*),count(id),count(id2)查询结果如下： 1 2 3 select count(*),count(id),count(id2) from #bla results 7 3 2 COUNT(*)的优化 前面提到了COUNT(*)是SQL92定义的标准统计行数的语法，所以MySQL数据库对他进行过很多优化。那么，具体都做过哪些事情呢？ 这里的介绍要区分不同的执行引擎。MySQL中比较常用的执行引擎就是InnoDB和MyISAM。 MyISAM和InnoDB有很多区别，其中有一个关键的区别和我们接下来要介绍的COUNT(*)有关，那就是MyISAM不支持事务，MyISAM中的锁是表级锁；而InnoDB支持事务，并且支持行级锁。 因为MyISAM的锁是表级锁，所以同一张表上面的操作需要串行进行，所以，MyISAM做了一个简单的优化，那就是它可以把表的总行数单独记录下来，如果从一张表中使用COUNT(*)进行查询的时候，可以直接返回这个记录下来的数值就可以了，当然，前提是不能有where条件。 MyISAM之所以可以把表中的总行数记录下来供COUNT(*)查询使用，那是因为MyISAM数据库是表级锁，不会有并发的数据库行数修改，所以查询得到的行数是准确的。 ...

典型回答 首先看看B+树有哪些特点： B+树是一棵平衡树，每个叶子节点到根节点的路径长度相同，查找效率较高； B+树的所有关键字都在叶子节点上，因此范围查询时只需要遍历一遍叶子节点即可； B+树的叶子节点都按照关键字大小顺序存放，因此可以快速地支持按照关键字大小进行排序； B+树的非叶子节点不存储实际数据，因此可以存储更多的索引数据； B+树的非叶子节点使用指针连接子节点，因此可以快速地支持范围查询和倒序查询。 B+树的叶子节点之间通过双向链表链接，方便进行范围查询。 那么，使用B+树实现索引，就有以下几个优点： 支持范围查询，B+树在进行范围查找时，只需要从根节点一直遍历到叶子节点，因为数据都存储在叶子节点上，而且叶子节点之间有指针连接，可以很方便地进行范围查找。 支持排序，B+树的叶子节点按照关键字顺序存储，可以快速支持排序操作，提高排序效率； 存储更多的索引数据，因为它的非叶子节点只存储索引关键字，不存储实际数据，因此可以存储更多的索引数据； 在节点分裂和合并时，IO操作少。B+树的叶子节点的大小是固定的，而且节点的大小一般都会设置为一页的大小，这就使得节点分裂和合并时，IO操作很少，只需读取和写入一页。 有利于磁盘预读。由于B+树的节点大小是固定的，因此可以很好地利用磁盘预读特性，一次性读取多个节点到内存中，这样可以减少IO操作次数，提高查询效率。 有利于缓存。B+树的非叶子节点只存储指向子节点的指针，而不存储数据，这样可以使得缓存能够容纳更多的索引数据，从而提高缓存的命中率，加快查询速度。 扩展知识 为什么不用红黑树或者B树？ 因为B+树的特点是只有叶子节点存储数据，而非叶子节点不存储数据，并且节点大小固定，还有就是叶子结点之间通过双向链表链接的，所以，使用B+树实现索引有很多好处，比如我们前面提到的支持范围查询、有利于磁盘预读、有利于优化排序等等。而这些是红黑树和B树做不到的。 B+树索引和Hash索引有什么区别？ B+ 树索引和哈希索引是常见的数据库索引结构，它们有以下几个主要区别： B+ 树索引将索引列的值按照大小排序后存储，因此B+ 树索引适合于范围查找和排序操作；而哈希索引是将索引列的值通过哈希函数计算后得到一个桶的编号，然后将桶内的记录保存在一个链表或者树结构中。因此，哈希索引适合于等值查询，但不适合范围查询和排序操作。 B+ 树索引在插入和删除数据时需要调整索引结构，这个过程可能会涉及到页分裂和页合并等操作，因此B+ 树索引的维护成本比较高；而哈希索引在插入和删除数据时只需要计算哈希值并插入或删除链表中的记录，因此哈希索引的维护成本相对较低。 B+ 树索引在磁盘上是有序存储的，因此在进行区间查询时可以利用磁盘预读的优势提高查询效率；而哈希索引在磁盘上是无序存储的，因此在进行区间查询时可能会需要随机访问磁盘，导致查询效率降低。 B+ 树索引在节点中存储多个键值对，因此可以充分利用磁盘块的空间，提高空间利用率；而哈希索引由于需要存储哈希值和指针，因此空间利用率相对较低。

典型回答 在 MySQL 5.6 之前，InnoDB 索引构建期间会对表进行排它锁定，这意味着在索引构建期间，其他会话不能读取或修改表中的任何数据。这将导致长时间阻塞和性能问题。 自 MySQL 5.6 开始，InnoDB 使用一种称为“Online DDL”的技术，允许在不阻塞其他会话的情况下创建或删除索引。Online DDL 针对不同的操作，有多种实现方式，包括COPY，INSTANT以及INPLACE 因为DDL有很多种操作，比如创建索引、增加字段、增加外键等，所以不同的操作支持的类型也不一样，具体支持方式可以在mysql官方可以看到（https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html ），拿索引创建举例： 从上表中可以看到，当我们创建、删除或者重命名一个索引的时候，是会用到in place的模式。 需要注意的是，虽然Online DDL 可以减少锁定时间和对性能的影响，但在索引构建期间仍然可能出现锁定和阻塞。例如，在添加索引时，如果表中有许多未提交的事务，则需要等待这些事务提交后才能开始索引构建。**因此，建议在非高峰期进行此类操作，以避免影响用户的正常使用。**在进行任何DDL操作之前，最好进行充分的测试和规划，并且确保有备份和回滚计划，以防意外情况的发生。 扩展知识 Online DDL原理 ✅什么是OnlineDDL

典型回答 数据库表的行格式决定了一行数据是如何进行物理存储的，进而影响查询和DML操作的性能。 在InnoDB中，常见的行格式有4种： COMPACT ：是MySQL 5.0之前的默认格式，除了保存字段值外，还会利用空值列表保存null值，还会记录变长字段长度列表和记录头信息。 COMPACT 适合处理大量包含可变长度列（如VARCHAR、VARBINARY、BLOB和TEXT类型）的数据。 对于可变长度列，前768字节的数据存储在B树节点的索引记录中，超出部分存储在溢出页中。大于或等于768字节的固定长度列会被编码为可变长度列，并可以存储在页外。 REDUNDANT ：Redundant 是 MySQL5.0 版本之前 InnoDB 的行记录存储方式，用的比较少，Redundant 行格式会把该条记录中所有列 (包括隐藏列) 的长度信息都存储到 ‘字段长度偏移列表’ 中。 DYNAMIC：DYNAMIC格式在MySQL 5.7版本引入，是COMPACT格式的改进版。它保持了COMPACT格式的优点，同时在存储大的可变长度列时更加灵活，能够动态地选择存储在页内或页外。DYNAMIC格式适用于大部分的应用场景，并在存储空间和性能上做了一定的平衡。其结构和COMPACT大致相同； COMPRESSED：是MySQL 5.1中InnoDB的新特性之一，它可以在存储数据时对数据进行压缩，从而减小磁盘占用空间。它的缺点是增加了CPU的使用，可能会降低一些查询的性能。COMPRESSED 行格式是在 DYNAMIC 行格式的基础上添加了页外压缩功能。在存储时，如果发现数据可以通过压缩减小存储空间，就会使用压缩方式来存储数据。在查询时，会自动解压缩数据并返回结果。 行格式 紧凑的存储特性 增强的可变长度列存储 大索引键前缀支持 压缩支持 支持的表空间类型 所需文件格式 REDUNDANT 否 否 否 否 system, file-per-table, general Antelope or Barracuda COMPACT 是 否 否 否 system, file-per-table, general Antelope or Barracuda DYNAMIC 是 是 是 否 system, file-per-table, general Barracuda COMPRESSED 是 是 是 是 file-per-table, general Barracuda

典型回答 InnoDB中的REPEATABLE READ这种隔离级别通过间隙锁+MVCC解决了大部分的幻读问题，但是并不是所有的幻读都能解读，想要彻底解决幻读，需要使用Serializable的隔离级别。 RR中，通过间隙锁解决了部分当前读的幻读问题，通过增加间隙锁将记录之间的间隙锁住，避免新的数据插入。 RR中，通过MVCC机制的，解决了快照读的幻读问题，RR中的快照读只有第一次会进行数据查询，后面都是直接读取快照，所以不会发生幻读。 但是，如果两个事务，事务1先进行快照读，然后事务2插入了一条记录并提交，再在事务1中进行update新插入的这条记录是可以更新成功的，这就是发生了幻读。 还有一种场景，如果两个事务，事务1先进行快照读，然后事务2插入了一条记录并提交，在事务1中进行了当前读之后，再进行快照读也会发生幻读。 扩展知识 MVCC解决幻读 MVCC，是Multiversion Concurrency Control的缩写，翻译过来是多版本并发控制，和数据库锁一样，他也是一种并发控制的解决方案。它主要用来解决读-写并发的情况。 ✅如何理解MVCC？ 我们知道，在MVCC中有两种读，一种是快照读、一种是当前读。 所谓快照读，就是读取的是快照数据，即快照生成的那一刻的数据，像我们常用的普通的SELECT语句在不加锁情况下就是快照读。 SELECT * FROM xx_table WHERE … 在 RC 中，每次读取都会重新生成一个快照，总是读取行的最新版本。 在 RR 中，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照。 那么也就是说，如果在RR下，一个事务中的多次查询，是不会查询到其他的事务中的变更内容的，所以，也就是可以解决幻读的。 如果我们把事务隔离级别设置为RR，那么因为有了MVCC的机制，就能解决幻读的问题： 有这样一张表： 1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT, gmt_create DATETIME NOT NULL, age INT NOT NULL, name VARCHAR(16) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; INSERT INTO users(gmt_create,age,name) values(now(),18,'Hollis'); INSERT INTO users(gmt_create,age,name) values(now(),28,'HollisChuang'); INSERT INTO users(gmt_create,age,name) values(now(),38,'Hollis666'); 执行如下事务时序： ...

典型回答 一次InnoDB的update操作，涉及到BufferPool、BinLog、UndoLog、RedoLog以及物理磁盘，完整的一次操作过程基本如下： 1、在Buffer Pool中读取数据：当InnoDB需要更新一条记录时，首先会在Buffer Pool中查找该记录是否在内存中。如果没有在内存中，则从磁盘读取该页到Buffer Pool中。 2、记录UndoLog：在修改操作前，InnoDB会在Undo Log中记录修改前的数据。Undo Log是用来保证事务原子性和一致性的一种机制，用于在发生事务回滚等情况时，将修改操作回滚到修改前的状态，以达到事务的原子性和一致性。UndoLog的写入最开始写到内存中的，然后由1个后台线程定时刷新到磁盘中的。 3、在Buffer Pool中更新：当执行update语句时，InnoDB会先更新已经读取到Buffer Pool中的数据，而不是直接写入磁盘。同时，InnoDB会将修改后的数据页状态设置为“脏页”（Dirty Page）状态，表示该页已经被修改但尚未写入磁盘。 4、记录RedoLog Buffer：InnoDB在Buffer Pool中记录修改操作的同时，InnoDB 会先将修改操作写入到 redo log buffer 中。 5、提交事务：在执行完所有修改操作后，事务被提交。在提交事务时，InnoDB会将Redo Log写入磁盘，以保证事务持久性。 6、写入磁盘：在提交过程后，InnoDB会将Buffer Pool中的脏页写入磁盘，以保证数据的持久性。但是这个写入过程并不是立即执行的，是有一个后台线程异步执行的，所以可能会延迟写入，总之就是MYSQL会选择合适的时机把数据写入磁盘做持久化。 7、记录Binlog：在提交过程中，InnoDB会将事务提交的信息记录到Binlog中。Binlog是MySQL用来实现主从复制的一种机制，用于将主库上的事务同步到从库上。在Binlog中记录的信息包括：事务开始的时间、数据库名、表名、事务ID、SQL语句等。 需要注意的是，在binlog和redolog的写入过程中，其实是分成了2阶段的，通过2阶段提交的方式来保证一致性的。 扩展知识 二阶段提交 ✅什么是事务的2阶段提交？

典型回答 和InnoDB最大的不同，MyISAM是采用了一种索引和数据分离的存储方式，也就是说，MyISAM中索引文件和数据文件是独立的。 因为文件独立，所以在MyISAM的索引树中，叶子节点上存储的并不是数据，而是数据所在的地址。所以，MyISAM 存储引擎实际上不支持聚簇索引的概念。在 MyISAM 中，所有索引都是非聚簇索引。 ✅什么是聚簇索引和非聚簇索引？ 也就是说，在MyISAM中，根据索引查询的过程中，必然需要先查到数据所在的地址，然后再查询真正的数据，那么就需要有两次查询的过程。而在InnoDB中，如果基于聚簇索引查询，则不需要回表，因为叶子节点上就已经包含数据的内容了。 因为MyISAM是先出的，正式因为存在这个问题，所以后来的InnoDB 引入了聚簇索引的概念提高了数据检索的效率，特别是对于主键检索。

典型回答 我们都知道，数据库是是支持多个事务同时执行的，而这些同时执行的事务能不能看到别的事务更改的数据呢？能看到哪些数据？这就是事务隔离级别来规定的。 所谓事务隔离级别，是数据库管理系统（DBMS）中用于定义事务处理过程中不同事务之间可见性和相互影响程度的一套标准。 事务隔离级别的主要目的是平衡数据的一致性和系统的并发性，一般来说更高的隔离级别虽然能提高数据的一致性，但同时也可能降低系统的并发能力。 SQL-92 标准定义了 4 种隔离级别来解决脏读、幻读、不可重复读等这些异常情况，从高到底依次为：可串行化(Serializable)、可重复读(Repeatable reads)、提交读(Read committed)、未提交读(Read uncommitted)。 **未提交读(Read uncommitted)**是最低的隔离级别。通过名字我们就可以知道，在这种事务隔离级别下，一个事务可以读到另外一个事务未提交的数据。这种隔离级别下会存在幻读、不可重复读和脏读的问题。 **提交读(Read committed)**也可以翻译成读已提交，通过名字也可以分析出，在一个事务修改数据过程中，如果事务还没提交，其他事务不能读该数据。所以，这种隔离级别是可以避免脏读的发生的。 可重复读(Repeatable reads)，由于提交读隔离级别会产生不可重复读的读现象。所以，比提交读更高一个级别的隔离级别就可以解决不可重复读的问题。这种隔离级别就叫可重复读。但是这种隔离级别没办法彻底解决幻读。 **可串行化(Serializable)**是最高的隔离级别，前面提到的所有的隔离级别都无法解决的幻读，在可串行化的隔离级别中可以解决。 注意！！！以上是SQL-92标准中对事务隔离级别以及需要解决的读现象（脏读、幻读、不可重复读）的定义，但是不同的数据库在实际实现的过程中，是有一些细微差异的。 扩展知识 MySQL如何实现隔离级别 ✅MySQL如何实现不同隔离级别？ 事务隔离级别相关命令 1.查看当前会话隔离级别 select @@tx_isolation; 在MySQL 8.0中：SELECT @@transaction_isolation; 2.查看系统当前隔离级别 select @@global.tx_isolation; 在MySQL 8.0中：SELECT @@global.transaction_isolation; 3.设置当前会话隔离级别 set session transaction isolation level repeatable read; 4.设置系统当前隔离级别 set global transaction isolation level repeatable read; 5.命令行，开始事务时 set autocommit=off 或者 start transaction 为什么MySQL默认的事务隔离级别是RR？ 为什么MySQL默认使用RR隔离级别？ ...

典型回答 ✅InnoDB的一次更新事务是怎么实现的？ 看本文之前，请先了解一下binlog、redolog等，知道他们的作用及写入过程。 看到这里默认大家了解以上背景了，那么，正文开始： MySQL的主从复制，是基于binlog实现的，主要过程是这样的： 1、从服务器在开启主从复制后，会创建出两个线程：I/O线程和SQL线程 2、从服务器的I/O线程，会尝试和主服务器建立连接，相对应的，主服务中也有一个binlog dump线程， 是专门来和从服务器的I/O线程做交互的。 3、从服务器的I/O线程会告诉主服务的dump线程自己要从什么位置开始接收binlog 4、主服务器在更新过程中，将更改记录保存到自己的binlog中，根据不同的binlog格式，记录的内容可能不一样。 5、在dump线程检测到binlog变化时，会从指定位置开始读取内容，然后会被slave的I/O线程把他拉取过去。 这里需要注意，有些资料上面说这里是主服务器向从服务器推的，但是，实际上是从服务器向主服务器拉的。（https://dev.mysql.com/doc/refman/8.0/en/replication-implementation.html ） 拉的模式，从库可以自行管理同步进度和处理延迟。 6、从服务器的I/O线程接收到通知事件后，会把内容保存在relay log中。 7、从服务器还有一个SQL线程，他会不断地读取他自己的relay log中的内容，把他解析成具体的操作，然后写入到自己的数据表中。 扩展知识 复制方式 MySQL目前支持多种复制方式，其中包括了全同步复制、异步复制和半同步复制。 异步复制：这是MySQL默认的复制方式，在异步复制的方式中主库在执行完事务操作以后，会立刻给客户端返回。他不需要关心从库是否完成该事务的执行。 这种方式会导致一个问题，那就是当主库出现故障时，主库虽然事务执行完了，但是可能还没来得及把数据同步给从库，就挂了。那么当从库升级为主库之后，他会丢失了这次事务的变更内容。 全同步复制：全同步复制的这个方式中，当主库执行完一个事务之后，他会等待所有的从库完成数据复制之后，才会给客户端反馈。 这种方式安全性可以保障了，但是性能很差。如果从库比较多的话，会导致整个过程更加长。 半同步复制：半同步复制是介于全同步复制和异步复制之间的一种方案。他在执行完一个事务之后，也不会立刻给客户端反馈，但是也不会等所有从库都完成事务，而是等其中一个从库完成接收到事件之后，再反馈给客户端。 在半同步复制这个方案中，会在事务提交的2阶段都完成之后，等待从库接收到binlog，然后再返回成功。 ✅InnoDB的一次更新事务是怎么实现的？ 在上面这篇中我们画过的一张图，如果把半同步复制的过程也加进去，那么就会变成： 主从延迟 ✅什么是数据库的主从延迟，如何解决？ 并行复制 ✅MySQL的并行复制原理