数据库面试知识点总结

1. MySQL 基础题

1.1 执行⼀条 select / update 语句，在 MySQL 中发生了什么？

（1）整体流程如下图所示：

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层，

• Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
• 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

（2）执行流程：

1. 连接MySQL：TCP 三次握手——连接器验证用户名和密码——连接器获取用户权限，然后后面的权限逻辑判断都基于此时读取到的权限（管理员中途修改用户权限重启该用户生效）【使用 showprocesslist 查看MySQL服务器被多少客户端连接】
2. 查询缓存：若有则直接返回数据。
3. 解析 SQL：⾸先是词法分析。根据输⼊的字符串识别出关键字，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。之后是语法分析，语法解析器会根据语法规则，判断这个 SQL 语句是否满足 MySQL 语法。
4. 执行 SQL：首先是预处理阶段，预处理器检查 SQL 查询语句中的表或者字段是否存在，并将select * 中的 * 符号，扩展为表上的所有列；然后优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。最后执行器就会和存储引擎交互了，交互是以记录为单位的，每查询到⼀条记录则返回给客户端，再接着查下⼀条记录。而对于update，需要先写入 redo log 缓冲区中，然后再写入 undo 数据页中，事务提交之后写⼊ binlog 日志缓存区内，所有存区数据会在合适时间进行磁盘写入。

1.2 MySQL 一行记录是怎么存储的？

（1）我们都知道 MySQL 的数据都是保存在磁盘的，那具体是保存在哪个文件呢？

• MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。InnoDB 是我们常用的存储引擎，也是 MySQL 默认的存储引擎。
• 我们也可以了解到一张数据库表的数据是保存在「 表名字.ibd 」的文件里的，这个文件也称为独占表空间文件。

（2）表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构大致如下图：

（3）InnoDB 行格式有哪些？

• 行格式（row_format），就是一条记录的存储结构。InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和Compressed 行格式。
  • Redundant 是很古老的行格式了，MySQL 5.0 版本之前用的行格式，现在基本没人用了。
  • 由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从MySQL 5.1 版本之后，行格式默认设置成 Compact。
  • Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

（4）COMPACT 行格式长什么样？

• 可以看到，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。
  • 记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。
  • 记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer，我们来看下这三个字段是什么。

• row_id：如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。
• trx_id：事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。
• roll_pointer：这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

（5）经过上述的简单了解可以延申出以下面试题：

• 面试题1：MySQL 的 NULL 值是怎么存放的？

  • MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。
  • NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。

• 面试题2：MySQL 怎么知道 varchar(n) 实际占用数据的大小？

  • MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。

• 面试题3：varchar(n) 中 n 最大取值为多少？

  • 一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。
  • 如果一张表只有一个 varchar(n) 字段，且允许为 NULL，字符集为 ascii。varchar(n) 中 n 最大取值为 65532。
  • 计算公式：65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532。
  • 如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

• 面试题4：行溢出后，MySQL 是怎么处理的？

  • 如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。
  • Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。
  • Compressed 和 Dynamic 这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。

（6）更加深入的解释可以参考文章：MySQL 一行记录是怎么存储的？

2. 三大范式

（1）第一范式（确保每列保持原子性）：

• 如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第⼀范式。1NF 是关系型数据库最基本的条件，否则不能称之为关系型数据库。

（2）第二范式（确保表中的每列都和主键相关）：

• 第⼆范式在第⼀范式的基础之上更进⼀层。第⼆范式需要确保数据库表中的每⼀列都和主键相关，而不能只与主键的某⼀部分相关（主要针对联合主键而言）。
• 也就是说在⼀个数据库表中，⼀个表中只能保存⼀种数据，不可以把多种数据保存在同⼀张数据库表中，通俗讲就是说该分表的时候就 分表，不要将多个表内容合并为⼀个表。

（3）第三范式（确保每列都和主键列直接相关，而不是间接相关）：

• 第三范式需要确保数据表中的每⼀列数据都和主键直接相关，而不能间接相关。通俗讲就是说当涉及到其他表项内容时设置 外键 进行关联，而不要加入其他表项内容。

3. 数据库引擎

3.1 Innodb

（1）存储结构（详细的介绍见博客《MySQL索引特性》）：

• InnoDB 的数据是按「数据页」为单位来读写的，默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来，物理上不连续，但是逻辑上连续。
• 数据页内包含用户记录，InnoDB 在查找某条记录时，并不能直接找到对应的行记录，而是只能获取到记录所在的页，然后将整个页面加载到内存中，在内存中遍历找到具体行。每个记录之间用单向链表的方式组织起来，为了在数据页内高效查询记录，设计了⼀个页目录，且主键值是有序的，因此可以通过二分查找法的方式进行检索从而提高效率。

（2）Buffer Pool：Innodb 存储引擎设计了⼀个缓冲池（Buffer Pool），来提高数据库的读写性能。Buffer Pool 里有三种结构来管理数据。

• Free Page（空闲页），表示此页未被使用，位于 Free 链表；
• Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。
• Dirty Page（脏页），表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写⼊磁盘后，内存数据和磁盘数据⼀致，那么该页就变成了干净页。

（3）简单的 LRU 算法并没有被 MySQL 使用，因为其无法避免下面这两个问题：

• 预读失效：MySQL 在加载数据页时，会提前把它相邻的数据页⼀并加载进来，目的是为了减少磁盘 IO。但是这些被提前加载进来的数据页，可能并没有被访问，相当于这个预读是白做了，这就是预读失效。MySQL 改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域 和 young 区域。
  • young 区域在 LRU 链表的前半部分，old 区域则是在后半部分。young 区域占整个 LRU 链表长度的比例可以通过参数来设置，⼀般 young 区域为 old 区域的 2 倍大小。
  • 划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。
• Buffer Pool 污染：当某⼀个 SQL 语句扫描大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。解决方案是只有同时满足「被访问」与「在 old 区域停留时间超过 N秒」两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool 污染的问题 。另外，MySQL 针对 young 区域其实做了⼀个优化，为了防止 young 区域节点频繁移动到头部。young区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会。

3.2 MyISAM

（1）存储结构：

• MyISAM 的数据是顺序存储的。索引的 B+ 树叶节点存放数据记录的地址，可以直接定位到数据，因此查找速度很快。

（2）MyISAM与innodb的区别：

4. 数据库索引

• 先查询二级索引（又叫非聚集索引，⼆级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据）中的 B+Tree 的索引值，找到对应的叶子节点，然后获取主键值，之后再通过主键索引中的B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个B+Tree 才能查到数据。
• 在⼆级索引的 B+Tree 就能查询到结果的方式就叫「覆盖索引」，也就是只需要查⼀个 B+Tree 就能找到数据。
• 全文索引：MySQL 之全文索引。

4.1 索引分类

（1）根据底层数据结构划分：索引是提高查询效率的数据结构，而MySQL中用到了B+Tree和散列表（Hash表）作为索引的底层数据结构（其实也用到了跳表实现全文索引，但这不是重要考点）。详细的介绍见博客《MySQL索引特性》。

1. hash索引：MySQL并没有显式支持Hash索引，而是作为内部的⼀种优化。具体在Innodb存储引擎里，会监控对表上⼆级索引的查找，如果发现某⼆级索引被频繁访问，⼆级索引成为热数据，就为之建立hash索引。
2. B+树索引：这个是MySQL索引的基本实现方式。读取⼀个节点相当于⼀次磁盘 I/O 操作。B+Tree 相比于二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

（2）根据数据与索引的存储关联性划分：索引又可以分成聚簇索引和非聚簇索引（⼆级索引），它们区别就在于叶子节点存放的是什么数据：

• 聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
• 二级索引的叶子节点存放的是主键值，而不是实际数据。
• 因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎⼀定会为表创建⼀个聚簇索引，且由于数据在物理上只会保存⼀份，所以聚簇索引只能有⼀个。

（3）InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键；
• 如果没有主键，就选择第⼀个不包含 NULL 值的唯⼀列作为聚簇索引的索引键；
• 在上面两个都没有的情况下，InnoDB 将自动生成⼀个隐式自增 id 列作为聚簇索引的索引键；

（4）唯一索引、主键的区别：

1. 主键是⼀种约束，是逻辑键，实际不存在；唯⼀索引是⼀种索引，是物理键，实际存在。
2. 主键创建后⼀定包含唯⼀索引；唯⼀索引并不⼀定是主键。
3. 唯⼀索引列允许空值；而主键列不允许为空值。
4. 主键可以被其他表引用为外键；而唯⼀索引不能。
5. ⼀个表有且只能创建⼀个主键；但可以创建多个唯⼀索引。
6. 主键和唯⼀索引都可以有多列。

4.2 索引优缺点

（1）索引的优点：

• 加快数据的检索速度。
• 减少查询中分组和排序的时间。
• 通过创建主键索引，可以保证数据库表中每一行数据的唯⼀性。
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

（2）索引的缺点：

• 占用额外空间。
• 一开始增加索引时，需要创建索引表。
• 新增或删除数据时，需要维护索引表。

4.3 索引使用场景

（1）索引的适用场景：

• 等值查询
• 范围查询
• 匹配最左前缀（联合索引）

（2）适合建立索引：

• 经常作为查询条件的字段，且列上的不同值较多；
• 频繁进行排序或分组（即进⾏ group by 或 order by 操作）的列。建立索引之后在 B+Tree 中的记录都是排序好的；
• 如果待排序的列有多个，可以在这些列上建立联合索引；

（3）不适合建立索引：

• 更新频繁的字段。
• 不会作为查询条件的字段。
• 表记录很少的时候。

4.4 优化索引方法

（1）使用覆盖索引：

• 假设只需要查询商品的名称、价格，可以建立⼀个联合索引，即「商品ID、名称、价格」作为⼀个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表，也减少了大量的 I/O操作。

（2）主键索引最好是自增的：

• InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。如果使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟⼀个新页面，因此这种插入数据的方法效率非常高。
• 如果使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从⼀个页面复制数据到另外⼀个页面，通常将这种情况称为页分裂。页分裂可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

（3）索引最好设置为 NOT NULL：

• 索引列存在 NULL 会导致优化器在做索引选择的时候难以优化，比如进行索引统计时，count 会忽略值为 NULL 的行。NULL 值是⼀个没意义的值，但是它会占用物理空间。防止索引失效

（4）索引失效情况：

• 以 % 开头的 like 查询。
• 对索引列进行函数运算，正则表达式。
• 联合索引的情况下，不满足最左原则。
• MySQL 估计使用索引比全表扫描更慢的情况。
• 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
• 使用负向查询（not ，not in， not like ，<> ,!= ,!> ,!< ） 不会使用索引。

4.5 数据库为什么用 B+ 树做索引

（1）要设计⼀个 MySQL 的索引数据结构，不仅仅考虑数据结构增删改的时间复杂度，更重要的是要考虑磁盘 I/0 的操作次数。因为索引和记录都是存放在硬盘，硬盘是⼀个非常慢的存储设备，在查询数据的时候，最好能用尽可能少的磁盘 I/0 的操作完成。

• 二分查找树虽然是⼀个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在⼀种极端的情况，每当插入的元素都是树内最大的元素，就会导致⼆分查找树退化成⼀个链表，此时查询复杂度就会从 O(logn) 降低为 O(n)。
• 为了解决二分查找树退化成链表的问题，就出现了自平衡⼆叉树，保证查询操作的时间复杂度⼀直维持在 O(logn) 。但是它本质上还是⼀个⼆叉树，每个节点只能有 2 个子节点，随着元素的增多，树的高度会越来越高。而树的高度决定于磁盘 I/O 操作的次数。
• B 树 和 B+树 都是通过多叉树的方式，将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。

（2）B 与 B+ 树差异（详细的介绍见博客《MySQL索引特性》的4.5小节）：

• 单点查询：
  • B 树进行单个索引查询时，最快可以在 O(1) 的时间代价内就查到，从平均时间代价来看，会比 B+ 树稍快⼀些。但是 B 树的查询波动会比较大，因为每个节点既存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶⼦节点才能找到索引。
  • B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比既存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
• 范围查询：因为 B+ 树所有叶子节点间还有⼀个双向链表进行连接，这种设计对范围查找非常有帮助。而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。
• 插入和删除效率：B+ 树有大量的冗余节点，当删除⼀个节点的时候，直接从叶子节点中删除，甚⾄可以不动非叶子节点，这样删除⾮常快。B+ 树的插入也是⼀样，有冗余节点，插⼊可能存在节点的分裂（如果节点饱和），但是最多只涉及树的⼀条路径。而且 B+ 树会自动平衡，不需要更多复杂的算法。因此，B+ 树的插入和删除效率更高。

因此，存在大量范围检索的场景，适合使用 B+树，比如mysql。而对于大量的单个索引查询的场景，可以考虑 B 树，比如 nosql 的MongoDB。

4.6 联合索引与最左匹配原则

（1）如果频繁地使用相同的几个字段查询，就可以考虑建立这几个字段的联合索引来提高查询效率。

• 比如对于联合索引 test_col1_col2_col3，实际建立了 (col1)、(col1, col2)、(col, col2, col3) 三个索引。联合索引的主要优势是减少结果集数量：如果根据 col1、col2、col3 的单列索引进行查询，需要分别得到num[i] 个结果，然后再取交集；而如果使用联合索引查询，只会得到很少的⼀段数据。

（2）最左匹配原则：

• 这些索引能够被包含 col1、(col1 col2)、(col1 col2 col3) 的查询利用到，但是不能够被 col2、(col2、col3) 的等值查询利用到。这与底层实现有关。联合索引的最左匹配原则，在遇到范围查询（>、<、between、like 包括like '林%'这种）的时候，就会停止匹配，也就是范围列可以用到联合索引，但是范围列后⾯的列无法用到联合索引。但是如果是 >=、<= 时可以继续走索引。

（3）最左匹配实践文章：最左匹配原则。

4.7 count(…) 查询效率

（1）查询效率排序如下：（都是对记录进行逐条判断，后面两个有额外判断是否为NULL的步骤）

• count(1)、 count(*)、 count(主键字段) 在执行的时候，如果表立存在⼆级索引，优化器就会选择⼆级索引进行扫描。
• 所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的⼆级索引进行扫描，相比于扫描主键索引效率会高一些。
• 不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。

（2）使用 MyISAM 引擎时，执行 count 函数只需要 O(1 )复杂度，这是因为每张 MyISAM 的数据表都有⼀个 meta 信息存储了row_count值，由表级锁保证⼀致性，所以直接读取 row_count 值就是 count 函数的执行结果。

（3）而 InnoDB 存储引擎是支持事务的，同⼀个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的，所以无法像 MyISAM⼀样，只维护⼀个 row_count 变量。而当带上 where 条件语句之后，MyISAM 跟 InnoDB 都需要扫描表来进行记录个数的统计。

（4）如何优化 count(*)？

• 近似值：可以使用 show table status 或者 explain 命令来进行估算。执行 explain 命令效率是很高的，因为它并不会真正的去查询。
• 额外表保存计数值：如果是想精确的获取表的记录总数，我们可以将这个计数值保存到单独的⼀张计数表中。当我们在数据表插入⼀条记录的同时，将计数表中的计数字段 + 1。也就是说，在新增和删除操作时，我们需要额外维护这个计数表。

5. 关系型数据库和非关系型数据库

5.1 关系型数据库（SQL）

（1）关系型数据库是使用 关系模型（二维表格模型）来组织数据的数据库。常见关系型数据库：

1. Oracle
2. MySql
3. Microsoft SQL Server
4. SQLite

（2）优势：

1. 采用二维表结构容易理解，全部由表结构组成，文件格式⼀致；
2. 支持通用的SQL（结构化查询语⾔）语句。可以在多个表之间做繁杂的查询；
3. 提供对事务的支持，同时提供事务的回滚、并发控制和死锁；
4. 数据存储在磁盘中，安全可靠。

（3）不足：

1. 高并发读写能力差。一台数据库的最大连接数有限，且硬盘 I/O 有限，不能同时满足很多⼈连接；
2. 海量数据情况下读写效率低。对大数据量的表进行读写操作时，响应时间长；
3. 可扩展性不足。无法通过简单的添加硬件和服务节点来拓展性能和负荷能力；
4. 数据模型灵活度低。关系型数据库的数据模型定义严格，无法快速容纳新的数据类型；

5.2 非关系型数据库（NOSQL）

（1）非关系型数据库被称为 NoSQL。数据通常 以对象的形式 存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定，常用于存储非结构化的数据。常见的NOSQL数据库：

1. 键值数据库：Redis
2. 列族数据库：HBase
3. 文档数据库：MongoDB
4. 图形数据库：Neo4j

（2）优势：

1. 存储数据的格式可以是 key-value 形式、文档形式、图片形式等。而关系型数据库只支持基础类型；
2. 速度快，效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3. 海量数据的维护和处理简单，成本低；
4. 具有扩展简单、高并发、高稳定性、成本低廉的优势；
5. 可以实现数据的分布式处理。

（3）不足：

1. 暂时不提供 SQL 支持，学习和使用成本较高；
2. 没有事务处理，无法保证数据的完整性和安全性。适合处理海量数据，但是不⼀定安全；
3. 复杂表关联查询不易实现。

6. 数据库连接池

（1）好处：

1. 资源重用。在内部对象池中，维护⼀定数量的数据库连接，并对外暴露数据库连接的获取和返回法昂法，由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销。
2. 更快的系统响应速度。数据库连接池在初始化过程中，往往已经创建了若⼲数据库连接于池内备用。此时连接池的初始化操作均已完成，对于业务请求而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而缩短了系统整体响应时间。
3. 统一的连接管理，避免数据库连接泄漏。在较为完备的数据库连接池中，可根据预先的连接超时设定，强制收回被占用的连接，从而避免了常规数据库连接操作中可能出现的资源泄漏。

7. 事务

（1）事务的定义：⼀个事务是⼀组对数据库中数据操作的集合。无论集合中有多少操作，对于用户来说，只是对数据库状态的⼀个原子改变。详细的介绍见博客《MySQL事务管理》。

7.1 ACID 的特性与实现

（1）四大特性如下：

1. 原子性（Atomicity）：指⼀个事务中的操作，要么全部成功，要么全部失败，如果失败就回滚到事务开始前的状态。
2. 一致性（Consistency）：指事务必须使数据库从⼀个⼀致性状态变换到另⼀个⼀致性状态。比如转账，A账户和B账户相互转账，无论如何操作，A、B账户的总⾦额都必须是不变的
3. 隔离性（Isolation）：指当多个用户并发的访问数据库时，事务之间的并发是隔离的。比如两个并发的事务T1和T2，T1要么在T2开始前执行，要么在T2结束后执行。
4. 持久性（Durability）：指事务⼀旦被提交，数据库中数据的改变就是永久性的。

（2）回滚日志：发生错误或者需要回滚的事务能够成功回滚（原子性）

• Innodb 存储引擎层生成的日志，当事务尝试对数据进行修改时，会先记录到回滚日志 undo log 中，然后再对数据库中的对应行进行写入。在异常发生时，对已经执行的操作进行回滚。主要用于事务回滚和MVCC。

（3）重做日志：在事务提交后，数据没来得及写回磁盘就宕机时，能够成功恢复数据（持久性）

• Innodb 存储引擎层生成的日志，重做日志由两部分组成，一是内存中的重做日志缓冲区，另⼀个就是在磁盘上的重做日志文件。当事务尝试对数据进行修改时，会先将数据从磁盘读入内存，并更新内存中缓存的数据，然后生成⼀条记录并写入重做日志缓存，当事务真正提交时，会将重做日志缓存中的内容刷新到重做日志文件，再将内存中的数据更新到磁盘上。
• 在 InnoDB 中，重做日志都是以 512 字节的块的形式进行存储的，同时因为块的大小与磁盘扇区大小相同，所以重做日志的写入可以保证原子性，不会由于机器断电导致重做日志仅写入一半而留下脏数据。除了所有对数据库的修改会产生重做日志，因为回滚日志也是需要持久存储的，它们也会创建对应的重做日志，在发生错误后，数据库重启时会从重做日志中找出未被更新到数据库磁盘中的日志重新执行以满足事务的持久性。

（4）binlog （归档日志）：是 Server 层生成的日志，binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询的操作。

• 主要用于数据备份和主从复制。

（5）redo log 和 binlog 有什么区别？

• 适用对象不同：binlog 是 Server 层实现的日志，所有存储引擎都可以使用；redo log 是 Innodb存储引擎实现的日志；
• 文件格式不同：binlog 有 3 种格式类型，分别是 STATEMENT（默认格式，记录操作）、ROW（记录数据行）、 MIXED（自动混合使用）；redo log 记录的是在某个数据页做了什么修改
• 写入方式不同：binlog 是追加写，写满⼀个文件，就创建⼀个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。redo log 是循环写，日志空间大小是固定的，全部写满就从头开始，保存未被刷入磁盘的脏页日志。
• 用途不同：binlog 用于数据备份、主从复制；redo log 用于断电等故障恢复。

MVCC（多版本并发控制）或锁机制保证隔离性，而⼀致性则是通过持久性+原子性+隔离性来保证；

7.2 两阶段提交

（1）在持久化 redo log 和 binlog 这两份日志的时候，如果出现半成功的状态，就会造成主从环境的数据不一致性。

• 这是因为 redo log 影响主库的数据，binlog 影响从库的数据，所以 redo log 和 binlog 必须保持⼀致才能保证主从数据⼀致。
• MySQL 为了避免出现两份日志之间的逻辑不⼀致的问题，使用了「两阶段提交」来解决，两阶段提交其实是分布式事务⼀致性协议，它可以保证多个逻辑操作要么全部成功，要么全部失败，不会出现半成功的状态。两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是分别是「准备（Prepare）阶段」和「提交（Commit）阶段」，每个阶段都由协调者（Coordinator）和参与者（Participant）共同完成。注意，不要把提交（Commit）阶段和 commit 语句混淆了，commit 语句执⾏的时候，会包含提交（Commit）阶段。

（2）在 MySQL 的 InnoDB 存储引擎开启 binlog 的情况下，MySQL 会同时维护 binlog 日志与 redo log，当客户端执行 commit 语句或者在自动提交的情况下，为了保证这两个日志的⼀致性，MySQL 内部开启⼀个 XA 事务，分两阶段来完成 XA 事务的提交，就是将 redo log 的写入拆成了两个步骤：prepare
和 commit，中间再穿插写入binlog，具体如下：

• prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
• commit 阶段：把 XID 写入到 binlog，然后将 binlog 刷新到磁盘，接着调用存储引擎的提交事务接口，将 redo log 状态设置为 commit；
• 异常发生时比较 redolog 和 binlog 日志中的 XID，若⼀致则提交事务，否则回滚事务。

（3）两阶段提交虽然保证了两个日志文件的数据⼀致性，但是性能很差，主要有两个方面的影响：

• 磁盘 I/O 次数高：每个事务提交都会进行两次 fsync（刷盘），⼀次是 redo log 刷盘，另⼀次是binlog 刷盘。
• 锁竞争激烈：两阶段提交虽然能够保证「单事务」两个日志的内容⼀致，但在「多事务」的情况下，却不能保证两者的提交顺序⼀致，因此，在两阶段提交的流程基础上，还需要加⼀个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序⼀致。

（4）组提交：

• MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成⼀个，从而减少磁盘 I/O 的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将这 10 个事务⼀次性⼀起刷盘的时间成本则近似于 1。

• 引入了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个过程：

  • flush 阶段：多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；
  • sync 阶段：对 binlog 文件做 fsync 操作（多个事务的 binlog 合并⼀次刷盘）；
  • commit 阶段：各个事务按顺序做 InnoDB commit 操作；

• 上面的每个阶段都有⼀个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第⼀个进入队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。对每个阶段引入了队列后，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程，可以看的出来，锁粒度减小了，这样就使得多个阶段可以并发执行，从而提升效率。

7.3 隔离性问题

（1）若不考虑隔离性则会出现以下问题：

1. 脏读：指⼀个事务在处理数据的过程中，读取到另⼀个 未提交 事务的数据
2. 不可重复读：指对于数据库中的某个数据（同⼀个数据项），⼀个事务内的多次查询却返回了不同的结果。这是由于在查询过程中，数据被另外⼀个事务修改并 提交 了。可通过 锁行 解决
3. 幻读：指事务非独立执行时发生的⼀种现象。例如事务T1对⼀个表中所有的行的某个数据做了修改，这时事务T2又向这个表中插入了一行数据项，而这个数据项的数据是以修改前的值 提交 到数据库的。而操作事务T1的用户如果查看刚刚修改的数据，会发现还有一行没有修改，其实这行是事务T2刚刚添加的，就好像产生幻觉⼀样，这就是幻读（针对⼀整批数据，即数据的个数）。可通过锁表 解决

（2）Next-Key锁 解决幻读：

• 为了解决“当前读”中的幻读问题，MySQL事务使用了Next-Key锁。Next-Key锁是行锁和间隙锁的合并。间隙锁指的是锁加在不存在的空闲空间，可能是第⼀个索引记录之前或最后⼀个索引之后的空间。
• 当InnoDB扫描索引记录的时候，会首先对选中的索引记录加上行锁，再对索引记录两边的间隙（向左扫描扫到第⼀个比给定参数小的值， 向右扫描扫描到第⼀个比给定参数大的值， 然后以此为界，构建一个区间）加上间隙锁。如果⼀个间隙被事务T1加了锁，其它事务是不能在这个间隙插⼊记录的。这样就防止了幻读。

7.4 隔离级别

（1）四种隔离级别如下（详细的介绍见博客《MySQL事务管理》的第5小节）：

1. 读未提交（Read uncommitted）：这种事务隔离级别下，select 语句不加锁。可能读到不⼀致的数据，即脏读。这是并发最高，一致性最差的隔离级别。
2. 读已提交（Read committed）：每次查询返回当前的快照。有不可重复读的问题。
3. 可重复读（Repeatable read）：多次读取同⼀范围的数据会返回第⼀次查询的快照。MySQL默认隔离级别。
4. 串行化（Serializable）：InnoDB 隐式地将全部的查询语句加上共享锁，解决了幻读的问题。

（2）读已提交、可重复读实现方式：MVCC。

8. 数据库中的锁

（1）按锁的粒度划分：

• 表级锁：表级锁分为表共享锁和表独占锁。表级锁开销小，加锁快，锁定粒度大，发生锁冲突最高，并发度最低。
• 页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的⼀种锁，一次锁定相邻的⼀组记录。
• 行级锁：行级锁分为共享锁和排他锁。行级锁是MySQL中锁定粒度最细的锁。InnoDB引擎支持行级锁和表级锁，只有在通过索引条件检索数据的时候，才使用行级锁，否就使用表级锁。行级锁开销大，加锁慢，锁定粒度最小，发生锁冲突的概率最低，并发度最高。

（2）按锁级别划分：

• 共享锁：共享锁又叫读锁，如果事务T对A加上共享锁，则其他事务只能对A再加共享锁，不能加其他锁。共享锁的事务只能读数据，不能写数据。
• 排它锁：排他锁又叫写锁，如果事务T对A加上排它锁，则其他事务都不能对A加任何类型的锁。获得排它锁的事务既能读数据，又能写数据
• 意向锁：意向锁是⼀种不与行级锁冲突的表级锁。意向锁是由数据库引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InnoDB 会先获取该数据行所在数据表的对应意向锁。意向锁的目的是为了快速判断表里是否有记录被加锁。

8.1 意向锁（Intention Locks）

（1）意向锁分为两种：

• 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）。事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
• 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）。事务要获取某些行的 X 锁，必须先获得表的 IX 锁。

（2）注意：

• 上图的排他 / 共享锁指的都是表锁！！！意向锁不会与行级的共享 / 排他锁互斥！！！
• 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。
• 事务 B 检测到事务 A 持有某表的意向排他锁，就可以得知事务 A 必然持有该表中某些数据行的排他锁，那么事务 B 对该表的加锁请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排他锁。

8.2 多版本并发控制（MVCC）

（1）MySQL 的大多数事务型存储引擎实现的都不是简单的行锁。而是基于提升并发性能的考虑，实现了多版本并发控制（MVCC）。可以认为，MVCC是行锁的⼀个变种，但它在很多情况下避免了加锁操作，减少了开销。MVCC实现了非阻塞的读操作，写操作也只锁定必要的行。详细的介绍见博客《MySQL事务管理》的第6小节。

（2）MVCC 实现方式：Read View

• Read View 有四个重要的字段：

• creator_trx_id ：指的是创建该 Read View 的事务的事务 id。
• m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，“活跃事务”指的就是启动了但还没提交的事务。
• min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
• max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下⼀个事务的 id 值，也就是全局事务中最⼤的事务 id 值 + 1；

（3）对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下⾯两个隐藏列：

• trx_id，当⼀个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写⼊到 undo 日志中，然后这个隐藏列是个指针，指向每⼀个旧版本记录，于是就可以通过它找到修改前的记录。

（4）在创建 Read View 后，一个事务去访问记录的时候，除自己的更新记录总是可见之外，还有如下情况：

• 如果当前行的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。

• 如果当前行的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建Read View 后启动的事务生成的，所以该版本的记录对当前事务不可见。

• 如果当前行的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：

  • 如果当前行的 trx_id 在 m_ids 列表中，表示⽣成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
  • 如果当前行的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

• 当记录不可见时会通过roll_pointer指针找到可见的第⼀个记录读取。这种通过「版本链」来控制并发事务访问同⼀个记录时的行为就叫 MVCC（多版本并发控制）。

（5）读已提交隔离级别是在每次读取数据时，都会生成⼀个新的 Read View。意味着，事务期间的多次读取同⼀条数据，前后两次读的数据可能会出现不⼀致，因为这期间另外⼀个事务可能修改了该记录，并提交了事务。

（6）可重复读隔离级别是在执行第⼀个查询语句后生成⼀个 Read View，然后整个事务期间都在用这个Read View。

（7）快照读与当前读：

1. 快照读：读取历史数据的方式，如普通的select语句。
2. 当前读：读取数据库当前版本数据的方式，是特殊的读操作。插入、更新、删除操作，都属于当前读，处理的都是当前的数据，需要加锁。不过 select … for update 语句就不是快照读了，而是当前读了

9. 查询优化方法

（1）避免向数据库请求不需要的数据：

• 在访问数据库时，应该只请求需要的行和列。请求多余的行和列会消耗MySql服务器的CPU和内存资源，并增加网络开销。避免使用 SELECT * 这种方式进行查询，应该只返回需要的列。例如在处理分页时，应该使用 LIMIT 限制MySql只返回⼀页的数据，而不是向应用程序返回全部数据后，再由应用程序过滤不需要的行。

（2）优化查询数据的方式：

• 查询数据的方式有全表扫描、索引扫描、范围扫描、唯⼀索引查询、常数引用等。这些查询方式，速度从慢到快，扫描的行数也是从多到少。可以通过 EXPLAIN 语句中的 type 列反应查询采用的是哪种方式。
• 通常可以通过添加合适的索引改善查询数据的方式，使其尽可能减少扫描的数据行，加快查询速度。例如，当发现查询需要扫描大量的数据行但只返回少数的行，那么可以考虑使用覆盖索引，即把所有需要用到的列都放到索引中。这样存储引擎无须回表获取对应行就可以返回结果了。
• 当⼀行数据被多次使用时可以考虑将数据行缓存起来，避免每次使用都要到MySql查询。
• update 语句应使用索引查询，否则全表查询会对所有记录加锁（非表锁），甚至导致业务停滞。

（3）分解查询：

• 可以将⼀个大查询切分成多个小查询执行，每个小查询只完成整个查询任务的一小部分，每次只返回一小部分结果。
• 分解关联查询，即对每个要关联的表进行单表查询，然后将结果在应用程序中进行关联。

（4）优化 LIMIT 分页：

• 处理分页会使用到 LIMIT，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。例如对于LIMIT 10000，20这样的查询，MySql需要查询10020条记录，将前面10000条记录抛弃，只返回最后的20条。这样的代价非常高，如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。
• 优化此类分页查询的⼀个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要与原表做⼀次关联操作返回所需的列。对于偏移量很大的时候，这样的效率提升非常大。

（5）优化 UNION 查询：

• 除非确实需要服务器消除重复的行，否则⼀定要 使用UNION ALL。如果没有ALL关键字，MySql会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯⼀性检查。这样做的代价非常高。

（6）group by 查询原理：

• 在MySQL 中，GROUP BY 的实现同样有多种(三种)方式，其中有两种方式会利用现有的索引信息来完成 GROUP BY，另外⼀种为完全无法使用索引的场景下使用。GroupBy会默认按照分组的字段进行排序；如果不需要排序，可使用order by null。

（7）使用松散(Loose)索引扫描实现 GROUP BY：

• MySQL 完全利用索引扫描来实现GROUP BY ，并不需要扫描所有满足条件的索引键即可完成操作得出结果。
• Extra信息中显示：Using index for group-by 要利用到松散索引扫描实现 GROUP BY，需要至少满足以下几个条件:
  • GROUP BY 条件字段必须在同⼀个索引中最前面的连续位置;
  • 在使用GROUP BY 的同时，只能使用 MAX 和 MIN 这两个聚合函数;
  • 如果引用到了该索引中 GROUP BY 条件之外的字段条件的时候，必须以常量形式存在; 松散索引扫描需要读取的键值数量与分组的数量⼀样多，尽可能读取最少数量的关键字。

（8）使用紧凑(Tight)索引扫描实现 GROUP BY：

• 和松散索引扫描的区别是需要读取所有满足条件的索引值，之后取数据完成操作。
• Extra中不显示for group-by，在 MySQL 中，首先会选择尝试通过松散索引扫描来实现 GROUP BY 操作，当发现某些情况无法满足松散索引扫描实现 GROUP BY 的要求之后，才会尝试通过紧凑索引扫描来实现。（比如GROUP BY 条件字段并不连续或者不是索引前缀部分的时候）

（9）使用临时表实现 GROUP BY：

• 当无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成 GROUP BY 操作。Extra：Using temporary; Using filesort

（10）union、join 区别：

1. union 在数据库运算中会过滤掉重复数据，并且合并之后是根据行合并的；
2. union all 不对数据进行过滤重复数据处理；
3. union 之后列数不变（两张表 union 保证列数和列结构⼀样）[1]
4. join 是进行表关联运算的，两个表要有⼀定的关系（某列可连接）。根据某⼀列进行笛卡尔运算和条件过滤，如果A表有3列，B表有3列，join 之后可能是5列。

（11）mysql 深度分页：

• mysql 分页查询是我们常⻅的需求，但是随着页数的增加查询性能会逐渐下降，尤其是到深度分页的情况。可以把分页分为两个步骤，1.定位偏移量，2.获取分页条数的数据。所以当数据较⼤页数较深时就涉及⼀次需要耗费较长时间的操作。所以mysql深度分页的问题该如何解决呢 ？

  • 以结果作为条件，已查询条件的变化换取分页的不变。分页查询⼀般都是逐渐往后翻页的，那么可以很清晰的知道，在当前查询页的最后⼀条数据的位置，那么，以此位置再查询N条，以位置的推移换取页数的不变，减少其偏移量的计算。
  • 采用子查询模式，其原理依赖于覆盖索引，当查询的列均是索引字段时，性能较快，因为其只用遍历索引本身。

• 针对复杂的查询逻辑，⼀般从数据的 偏移量着手，减少偏移量的定位时间。

• 简单的查询逻辑，可以从索引覆盖的思想着手，先确定查询数据的主键id，再由id找相关的数据。

10. 主从复制

（1）具体详细过程如下：

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

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

（2）从库是不是越多越好？

• 不是的。因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。所以在实际使用中，⼀个主库⼀般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是⼀主多从的 MySQL 集群结构。

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

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