doc/面经/问答/Mysql.md

mysql内连接，外连接，左连接，右连接

• left join（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
• right join（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
• inner join（内连接）：只返回两个表中连接字段相等的行。
• full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。在mysql中不存在全连接，但是他可以等同于左连接合并右连接

mysql如何提升查询性能

1. 合理使用索引：索引是提升查询性能的重要手段，可以通过在常用查询字段上创建索引来加速查询速度。需要注意的是，索引的创建和使用需要谨慎，过多或不合理的索引可能会降低性能。在使用索引时，可以通过 EXPLAIN 分析查询计划，避免全表扫描和不必要的索引使用。
2. 优化 SQL 语句：优化 SQL 语句可以通过多种方式实现，例如避免使用通配符查询，优化复杂查询语句，避免不必要的连接查询和子查询，合理使用聚合函数和 GROUP BY 子句等。可以通过使用数据库性能分析工具（如 MySQL 自带的慢查询日志）来定位慢查询，并对其进行优化。
3. 缓存和缓冲技术：通过使用缓存和缓冲技术可以减轻数据库的负载，提高查询性能。可以使用数据库自带的查询缓存功能，或者使用外部缓存技术如 Redis、Memcached 等进行数据缓存，减少对数据库的频繁查询。
4. 合理配置数据库参数：MySQL 有丰富的配置参数可以进行调优，例如调整缓冲池大小、设置合理的连接数、开启合适的日志等，可以根据实际需求和硬件资源来配置数据库参数，以达到最佳性能。
5. 可以考虑使用分库分表技术

mysql底层数据结构

1. B+ 树索引：MySQL 使用 B+ 树作为默认的索引结构。索引数据最好能按顺序排列，这样可以使用「二分查找法」高效定位数据，而且还要支持排序操作。这就天然要求我们使用平衡二叉树作为默认的数据结构，而平衡二叉树本身是一个B树，每个节点只能有两个子节点那么当节点个数越多的时候，树的高度也会相应变高，这样就会增加磁盘的 I/O 次数，从而影响数据查询的效率。B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。B+ 树就是对 B 树做了一个升级，他和B树相比有如下几个优点
   1. B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树查询效率更高，因为IO次数更少
   2. B+ 树在删除根节点的时候，由于存在冗余的节点，所以不会发生复杂的树的变形，B 树则不同，B 树没有冗余节点，删除节点的时候非常复杂
   3. B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助
2. 数据文件：MySQL 数据库将数据存储在磁盘上的数据文件中，底层由表空间构成，表空间由段（segment）、区（extent）、页（page）、行（row）组成

为什么分区？

页要连续，顺序IO读写快，每个区都维护一个链表，代表空闲区的指针

• FREE链表：同一个段中，所有页都是空闲的区对应的XDES Entry结构会被加入到这个链表。注意和直属于表空间的FREE链表区别开了，此处的FREE链表是附属于某个段的。
• NOT_FULL链表：同一个段中，仍有空闲空间的区对应的XDES Entry结构会被加入到这个链表。
• FULL链表：同一个段中，已经没有空闲空间的区对应的XDES Entry结构会被加入到这个链表。

为什么分段？

区分叶子节点和非叶子节点

页？

稀疏索引，分组

1. InnoDB 存储引擎：InnoDB 是 MySQL 的一种常用存储引擎，它使用了多版本并发控制（MVCC）和行级锁来实现高并发性和事务支持。InnoDB 存储引擎的核心数据结构包括页（page）、记录（record）、索引（index）和事务日志（transaction log）等。

nysql低层文件结构

• db.opt，用来存储当前数据库的默认字符集和字符校验规则。
• t_order.frm ，t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
• t_order.ibd，t_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

联合索引定义要注意哪些点

在 MySQL 中定义联合索引时，需要注意以下几点：

1. 列顺序：联合索引中的列顺序非常重要，它会影响索引的效果。通常情况下，应该将被频繁用于查询的列放在联合索引的前面，这样可以使索引在查询时更加有效。例如，如果查询中经常同时使用列 A 和列 B 进行查询，那么联合索引应该按照 (A, B) 的顺序定义，而不是 (B, A)。

2. 列选择：不要将过多的列包含在联合索引中，因为索引的大小对数据库性能有影响。过大的索引可能会导致磁盘 I/O 操作增加，并且会占用额外的存储空间，导致性能下降

   • 索引选择：不是所有的列都适合联合索引。联合索引在使用时会按照索引列的顺序进行匹配，因此只有查询中包含了索引的前缀列时，索引才能被充分利用。因此，应该选择那些在查询中经常一起使用的列进行联合索引定义。

   • 更新性能：联合索引在更新操作时可能会导致性能下降。因为联合索引涉及多个列，更新其中的一个列可能需要更新整个索引。因此，在定义联合索引时应该考虑更新性能的影响，避免频繁的更新操作导致性能下降。

数据库ACID

• 原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样，就好比买一件商品，购买成功时，则给商家付了钱，商品到手；购买失败时，则商品在商家手中，消费者的钱也没花出去。
• 一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。
• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。也就是说，消费者购买商品这个事务，是不影响其他消费者购买的。
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

• 持久性是通过 redo log （重做日志）来保证的；
• 原子性是通过 undo log（回滚日志） 来保证的；
• 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证；

数据库隔离级别

数据库隔离级别是指在多用户并发访问数据库时，数据库管理系统 (DBMS) 为了保护数据的一致性、隔离性和并发性，采取的一系列措施和规定。MySQL 支持以下四种隔离级别：

1. 读未提交 (Read Uncommitted)：最低的隔离级别，事务可以读取到其他事务未提交的数据。这种隔离级别可能导致脏读（Dirty Read），即读取到其他事务未提交的数据，可能会引起数据的不一致性。
2. 读提交 (Read Committed)：事务只能读取到其他事务已经提交的数据，保证了数据的一致性。但在该隔离级别下，可能会出现不可重复读（Non-repeatable Read）问题，即在同一事务内多次读取同一行数据时，可能会得到不同的结果。
3. 可重复读 (Repeatable Read)：事务在执行期间对同一行数据进行读取时，会保持一致的结果。其他事务对该行数据的修改只有在当前事务提交后才能生效。这种隔离级别可以避免不可重复读问题，但仍然可能出现幻读（Phantom Read）问题，即在同一事务内多次执行相同的查询时，可能会得到不同的结果。(事务B插入一个数据，事务A进行更新，然后A事务就会读取到这个新插入的数据)
4. 串行化 (Serializable)：最高的隔离级别，事务会对数据库中的数据加锁，防止其他事务对数据的并发访问。这种隔离级别可以避免幻读问题，但可能会导致性能下降，因为事务需要等待其他事务释放锁。

数据库隔离级别如何实现

• 读未提交：因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
• 读提交：通过 Read View 来实现的，「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View
• 可重复读：通过 Read View 来实现的，「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。
• 串行化：通过加读写锁的方式来避免并行访问；

数据库MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种数据库管理系统（DBMS）中的并发控制机制，用于在多用户并发访问数据库时保障事务的隔离性和一致性。MVCC通过在数据库中为每个事务创建多个版本的数据副本，使多个事务可以同时读取和修改数据库中的数据，而不会相互干扰。

在MVCC模型中，每个事务在读取和修改数据时，都会看到数据库中的一个一致性的快照（snapshot），而不是实际的物理数据。每个事务都有自己的事务ID（Transaction ID），数据库中的每条记录也会保存多个版本，每个版本都包含了一个事务ID和一个时间戳（Timestamp）。当事务执行读取操作时，只会看到在其事务开始之前提交的数据版本，而不会看到在其事务开始之后提交的数据版本。这样可以避免脏读、不可重复读和幻读等并发访问问题。

加入索引有什么讲究

1. 精选索引列：选择适合索引的列是关键。频繁在查询条件中出现，或者是查询条件的所有列。这样可以防止回表
2. 注意索引的顺序：索引的顺序对查询性能有影响。对于联合索引，索引列的顺序应该根据查询的实际情况来选择。通常，将基数（Distinct count）较高的列放在前面，可以减少索引的选择性，提高查询性能。
3. 考虑索引的类型：MySQL支持多种类型的索引，如普通索引、唯一索引、主键索引、全文索引等。根据查询的需求和数据的特点选择合适的索引类型。例如，对于需要唯一性约束的列，可以使用唯一索引或主键索引；对于文本字段的模糊查询，可以考虑使用全文索引。
4. 避免过度索引：过度索引可能导致性能下降和维护成本增加。因此，避免在相同的列上创建多个重复的索引，避免对低基数列（Distinct count较低的列）添加索引，避免创建不必要的索引。

B树的特点

1. 平衡二叉树节点最多有两个子树，而 B 树每个节点可以有多个子树，M 阶 B 树表示该树每个节点最多有 M 个子树
2. 平衡二叉树每个节点只有一个数据和两个指向孩子的指针，而 B 树每个中间节点有 k-1 个关键字（可以理解为数据）和 k 个子树（ k介于阶数 M 和 M/2 之间，M/2 ⬆️向上取整）
3. B 树的所有叶子节点都在同一层，并且叶子节点只有关键字，指向孩子的指针为 null

索引的分类

• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）（非聚簇索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

MySQL为什么使用B+树而不是使用B树

• B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
• B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
• B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

MySQL 有哪些锁？

表级锁

• 表锁
• 元数据锁
• 意向锁

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

• AUTO-INC 锁

行级锁

• Record Lock

记录锁，锁住单行数据

• Gap Lock

间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的

• Next-Key Lock

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的

• 插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

加锁规则

唯一索引等值查询：

• 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
• 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

非唯一索引等值查询：

• 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
• 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

• 唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。
• 非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。

其实理解 MySQL 为什么要这样加锁，主要要以避免幻读角度去分析，这样就很容易理解这些加锁的规则了。

InnoDB 和 MyISAM 引擎的区别

InnoDB和MyISAM是MySQL数据库中常见的两种存储引擎，它们有一些重要的区别。

1. 事务支持：InnoDB引擎支持事务，可以使用ACID（原子性、一致性、隔离性和持久性）特性来确保数据的完整性和一致性。而MyISAM引擎不支持事务，它的操作是自动提交的，无法回滚。
2. 锁的等级：InnoDB引擎在高并发读写操作下表现更好。它支持行级锁定，可以同时处理多个并发操作，从而提高了数据库的并发性能。相比之下，MyISAM引擎只支持表级锁定，当某个操作在修改表时，其他操作必须等待，可能导致并发性能下降。
3. 外键约束：InnoDB引擎通过外键约束来保持数据完整性，可以定义外键关系。而MyISAM引擎不支持外键约束，需要在应用层面来维护数据的完整性。
4. 崩溃恢复：InnoDB引擎具有崩溃恢复能力，可以保证数据库在崩溃后更好地恢复。MyISAM引擎在崩溃后恢复较差，可能会导致数据丢失或损坏。（因为使用了表级锁，非聚簇索引。而InnoDB使用redo log恢复）
5. 全文搜索功能：MyISAM引擎支持全文索引和全文搜索功能，可以对文本进行高效的全文搜索。而InnoDB引擎在MySQL 5.6版本之前不支持全文索引，需要借助其他插件或引擎来实现全文搜索。
6. 索引结构：InnoDB引擎使用聚簇索引（Clustered Index），将数据存储在主键的索引树中，可以提高查询效率。而MyISAM引擎使用非聚簇索引（Non-clustered Index），索引文件和数据文件分开存储。

什么是全文索引

使用like%xxx%进行模糊查询时，字段的索引就会失效。因此，在数据量大的情况下，通过此种方式查询的效率极低。这个时候，就可通过全文索引（Full-Text Search）来进行优化。

全文索引（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。

全文索引一般是通过倒排索引实现的，倒排索引如同 B+Tree 一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射，这通常利用关联数组实现，拥有两种表现形式：

    inverted file index：{单词，单词所在文档的id}

    full inverted index：{单词，（单词所在文档的id，再具体文档中的位置）}

创建索引之后可以在索引中查找与关键词匹配的文档列表。可以使用布尔检索、向量空间模型或其他检索算法来计算文档与关键词的相关性，并返回匹配度高的文档结果。

binlog与主从复制

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

binlog日志可以用于在主从数据库之间复制数据，从而实现数据的高可用和负载均衡等功能。 MySQL的binlog日志有三种格式，分别是Statement格式、Row格式和Mixed格式。它们之间的区别如下：

• STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
• ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
• MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

MySQL 集群的主从复制过程梳理成 3 个阶段：

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
• 回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程如下：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。
• 当一个事务在 MySQL 主库上被回滚时，相应的回滚操作会被写入 binlog，以确保主从复制和数据一致性。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

从库是不是越多越好？

不是的。

因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。

所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

MySQL 主从复制还有哪些模型？

主要有三种：

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
• 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

undo log

undo log（回滚日志），它保证了事务的 ACID 特性中的原子性（Atomicity）。

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

• 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
• 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
• 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

• 通过 trx_id 可以知道该记录是被哪个事务修改的；
• 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

版本链如下图：

因此，undo log 两大作用：

• 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
• 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

Buffer Pool？

Innodb 存储引擎缓冲池（Buffer Pool）

有了 Buffer Poo 后：

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
• 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

Buffer Pool 缓存什么？

InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，申请物理内存，接着将虚拟地址和物理地址建立映射关系。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

Undo 页是记录什么？

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

查询一条记录，就只需要缓冲一条记录吗？

不是的。

当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

redo log

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。

后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

过程如下图：

什么是 redo log？

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

被修改 Undo 页面，需要记录对应 redo log 吗？

需要的。

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

不过，在内存修改该 Undo 页面后，需要记录对应的 redo log。

redo log 和 undo log 区别在哪？

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务，如下图：

所以有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出来， redo log 保证了事务四大特性中的持久性。

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。

磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。

针对「顺序写」为什么比「随机写」更快这个问题，可以比喻为你有一个本子，按照顺序一页一页写肯定比写一个字都要找到对应页写快得多。

可以说这是 WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」，提升语句的执行性能。这是因为 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上 。

至此， 针对为什么需要 redo log 这个问题我们有两个答案：

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
• 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

产生的 redo log 是直接写入磁盘的吗？

不是的。

实际上， 执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。

所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘如下图：

redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能。

redo log 什么时候刷盘？

缓存在 redo log buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

主要有下面几个时机：

• MySQL 正常关闭时；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
• 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制，下面会说）。

redo log和binlog区别

binlog和redolog都是用于MySQL数据库的日志。它们都可以用于数据恢复，但是它们的使用场景和恢复方法有所不同。

binlog是MySQL的二进制日志，它记录了所有对MySQL数据库的修改操作，包括插入、更新和删除等。binlog可以用于恢复MySQL数据库到指定的时间点或者指定的事务。具体来说，可以使用mysqlbinlog命令将binlog文件解析成SQL语句，然后再执行这些SQL语句，从而恢复MySQL数据库的状态。

redolog是MySQL的重做日志，它记录了所有对MySQL数据库的修改操作，但是只记录了物理操作，比如页的修改。redolog可以用于恢复MySQL数据库的崩溃恢复，即在MySQL崩溃后，通过重做日志，将数据库恢复到最近一次提交的状态。具体来说，可以使用innodb_recovery命令来进行崩溃恢复，该命令会根据重做日志来恢复数据库。

因此，binlog和redolog都可以用于数据恢复，但是它们的使用场景和恢复方法有所不同。binlog主要用于数据恢复到指定时间点或者指定事务，而redolog主要用于MySQL的崩溃恢复。

mysql优化慢SQL

开启慢查询日志

首先开启慢查询日志，由参数slow_query_log决定是否开启，在MySQL命令行下输入下面的命令：

set global slow_query_log=on;

或者可以使用druid连接池去监控

其次设置一个慢查询阈值

set global long_query_time=1;

只要你的SQL实际执行时间超过了这个阈值，就会被记录到慢查询日志里面。这个阈值默认是10s，线上业务一般建议把long_query_time设置为1s，如果某个业务的MySQL要求比较高的QPS，可设置慢查询为0.1s。

最后通过

show global variables like 'slow_query_log_file'

语句可以找到慢查询的日志

使用explain分析sql执行计划

• select_type

• type

• Extra：

关键字优化

• 因为只有在内连接的时候，and就一并筛选，而Where是将两张表先合并一起，再做筛选
• 优化value的插入方式，即insert （a, b） (1, ,2,3,4)
• 避免索引失效
• 避免使用not null not in 之类的关键字，均会导致索引失效

CHAR和VARCHAR的区别

在MySQL中，CHAR和VARCHAR都是用于存储文本数据的数据类型，它们之间有以下区别：

1. 存储方式：CHAR类型是固定长度的，当存储数据时，会使用固定的空间。例如，如果定义一个CHAR(10)类型的字段，无论实际存储的数据长度是多少，都会占用10个字符的空间。而VARCHAR类型是可变长度的，只会占用实际存储的数据长度加上额外的存储空间（1或2个字节用于记录数据长度），对于较短的数据，它占用的空间会更小。
2. 存储效率：由于CHAR类型是固定长度的，所以在存储和检索时效率相对较高。而VARCHAR类型由于长度可变，可能会造成额外的存储空间和内存开销，所以在存储和检索大量数据时，效率可能稍低。
3. 数据填充：对于CHAR类型，如果实际存储的数据长度小于定义的长度，会使用空格进行填充以达到指定长度。而VARCHAR类型不会进行填充。
4. 索引和排序：在MySQL中，对于CHAR类型的列，可以更好地利用索引和排序的性能，因为它的长度固定，查询时可以更快地定位数据。而VARCHAR类型由于长度可变，可能会影响索引和排序的效率。

索引失效场景

• 最左前缀原则：要求建立索引的一个列都不能缺失，否则会出现索引失效

• 索引列上的计算，函数、类型转换（列类型是字符串在条件中需要使用引号，否则不走索引）、均会导致索引失效：，索引的数据结构只能对原值做索引

• 索引列中使用 is not null 会导致索引列失效：在where 语句种筛选 idx is null 时，由于索引字段不为空，所以该条件失效，无法查询；在where 语句种筛选 idx is not null 时，由于索引字段本身不为空，所以该条件也失效，会造成全表扫描；

• 索引列中使用 like 查询的前以 % 开头会导致索引列失效。

• 索引列用 or 连接时会导致索引失效：如果条件中有or，只要其中一个条件没有索引，其他字段有索引也不会使用。

mysql分库分表历史数据如何查询

• 直接进行迁移：记录时间戳，使用定时任务去扫描
• 平滑迁移：只插入，旧表数据不动，插入时候直接加上扩容倍数。查询直接分批查询。

mysql分库分表物理层面优化

• IO瓶颈

磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。

网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

• CPU瓶颈

SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。

单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

• 存储瓶颈

关系型数据库多以B+树类型的索引，在数据量超过阈值的情况，索引深度的增加使得磁盘访问的IO次数增加，导致性能下降，同时，高并发访问使得集中数据库成为系统瓶颈。

既然Hash比B+树更快，为什么MySQL用B+树来存储索引呢？

MySQL中存储索引用到的数据结构是B+树，B+树的查询时间跟树的高度有关，是log(n)，如果用hash存储，那么查询时间是O(1)。

采用Hash来存储确实要更快，但是采用B+树来存储索引的原因主要有以下两点：

一、从内存角度上说，数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次性装入内存，B+树的设计可以允许数据分批加载。

二、从业务场景上说，如果只选择一个数据那确实是hash更快，但是数据库中经常会选中多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。

B+树插入流程

B+树是B树的一种变形形式，一颗B+树包含根节点、内部节点和叶子节点，B+树上的叶子节点存储关键字以及相应记录的地址，叶子节点以上各层作为索引使用。

一棵m阶的B+树定义如下：

• 1）根结点最少包含1个关键字个数，最多包含m-1个关键字；
• 2）B+树内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中；
• 3）内部结点最少有ceil(m / 2) （向上取整）- 1个关键字，最多有m-1个关键字（或者说内部结点最多有m个子树）；
• 4）内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它，叶子结点中的记录也按照key的大小排列；
• 5）每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序连接； 如下图是一颗标准的5阶B+树：

​

下面我们通过一个简单的示例说明B+树的详细构建过程：

我们以构建一个5阶B+树为例，根据B+树特性，最少包含2个关键字，最多可以包含4个关键字，当不满足上面条件的时候，可能就需要进行分裂操作了。

数据库三大范式

• 第一范式：列不可再分
• 第二范式：属性完全依赖主键
• 第三范式：属性不依赖于其他非主键，属性直接依赖于主键

limit与深度翻页

最常见的分页写法就是使用limit，在分页查询时，会在 LIMIT 后面传两个参数，一个是偏移量(offset)，一个是获取的条数(limit)。

实现方式是先查询offset+limit条数据，再将offset条数据丢弃给用户返回剩下的limit条数据。比如limit 10000,10实际上是mysql查找到前10010条数据，之后丢弃前面的10000行后再返回

这样子当偏移量很小时，查询速度很快，但是随着 offset 变大时，查询速度会越来越慢，因为查找的数据越来越多

优化

• 方式一：

select * from t1 where id >= 300000 order by id limit 10

避免了扫描前offset条记录

但是每次查询都需要拿到上一页的最大/小id。比如当前在第3页，需要查询第5页的数据就没办法了

• 方式二：

结合普通limit与方式一，解决方式二的问题，但是offset要尽量小

select * from t1 where id > 300000 order by id limit 10, 10

• 方式三：

select * from t1 where id > (select id from t1 order by id limit 300000, 1) limit 10

MySQL中 IS NULL、IS NOT NULL、不等于， 能用上索引吗？

MySQL中决定使不使用某个索引执行查询的依据就是成本够不够小，如果null值很多，还是会用到索引的。

MySQL允许在唯一索引字段中添加多个NULL值

在sql server中，唯一索引字段不能出现多个null值 在mysql 的innodb引擎中，是允许在唯一索引的字段中出现多个null值的。 根据NULL的定义，NULL表示的是未知，因此两个NULL比较的结果既不相等，也不不等，结果仍然是未知。根据这个定义，多个NULL值的存在应该不违反唯一约束，所以是合理的，在oracel也是如此。

mysql索引为什么不能为null

会引起歧义，就不允许

为什么是2千万？

假设

• 非叶子节点内指向其他页的数量为 x
• 叶子节点内能容纳的数据行数为 y
• B+ 数的层数为 z

一页是16K，页又一些File Header (38 byte)、Page Header (56 Byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录，大概 1k 左右。 剩下 15k 用于存数据，主键我们假设是 Bigint (8 byte), 而页号也是固定的（4Byte）那么索引页中的一条数据也就是 12byte。

所以非叶子节点内指向其他页的数量为 x ， x=15*1024/12≈1280 行。

叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k。

但是叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如，字段的类型，字段的数量。每行数据占用空间越大，页中所放的行数量就会越少。

这边我们暂时按一条行数据 1k 来算，那一页就能存下 15 条，Y = 15*1024/1000 ≈15。

算到这边了，是不是心里已经有谱了啊。

根据上述的公式，Total =x^(z-1) *y，已知 x=1280，y=15：

• 假设 B+ 树是两层，那就是 z = 2， Total = （1280 ^1 ）*15 = 19200
• 假设 B+ 树是三层，那就是 z = 3， Total = （1280 ^2） *15 = 24576000 （约 2.45kw）