MySQL Q&A与知识点

1、基础知识

什么是主键？

它用来唯一标识一条记录（一个字段）。每个表都必须有且只能有一个主键，主键的取值不允许为空，而且在表中必须是唯一的（当然还可以有复合主键）。

什么是索引？

在MySQL中，索引是一种数据结构，用于加速数据库表中的数据检索。它可以看作是一个快速查找表，其中包含列值和对应的行地址列表。

MySQL使用B+树索引来实现快速查找和排序。B+树是一种平衡树结构，其中每个节点包含多个关键字和对应的指针。通过在树中移动并比较关键字，可以快速定位所需的数据行。

使用索引可以大大提高查询效率，并使数据访问更加快速和可靠。但是，每个索引都需要占用额外的存储空间，并且在插入、更新或删除数据时也会影响性能。因此，需要仔细考虑何时使用索引以及如何设计索引来最大化其效果。

索引有哪些类型？

聚焦索引、辅助索引、联合索引、唯一索引

使用注意事项：

1、选择区分度大的字段

2、最左匹配原则

3、索引列保持干净，不要参与运算

4、尽量横向拓展，而不是新增索引

主键、外键、索引的区别

简单记：

主键：唯⼀标识⼀条记录，不允许重复，不允许为空
外键：外键表示另⼀张表的主键，允许重复，可以是空值
索引：没有重复值，但可以有⼀个空值

详细：

• 主键：主键是用于唯一标识表中每个记录的一列或多列。主键的值必须唯一且不能为空，通常使用自增长整数作为主键。主键可以保证数据的完整性并提高查询效率。
• 外键：外键是一个指向另一个表的字段，它定义了两个表之间的关系。外键通常是在一个表中创建一个指向另一个表的列，该列约束了连接这两个表的数据的有效性，确保只有存在于另一个表中的行才能被插入。外键可以用来维护表之间的关联关系，并保证数据的一致性。
• 索引：索引是一种数据结构，它可以加速数据库中大量数据的查找。索引可以根据某个列的值来快速定位表中的记录。通过对经常搜索的列创建索引，可以大大提高查询效率。但是，索引也会占用磁盘空间、增加插入和更新操作的开销以及降低写操作的性能。

什么是事务

在MySQL中，事务是一组原子性、一致性、隔离性和持久性操作的集合。这些操作被视为单个逻辑工作单元，并且必须完全执行或完全不执行。

原子性:指一组操作要么全部成功，要么全部失败。如果其中任何一个操作失败，整个事务都将回滚到最初状态。

一致性:指在事务执行之前和之后，数据库必须保持一致状态。这意味着对于任何给定数据，它应该始终显示相同的值。

隔离性:指多个并发事务之间互不干扰，即每个事务的操作都不会影响其他正在进行的事务。

持久性:指一旦事务完成并提交，其结果将永久保存在数据库中，即使系统故障也不会丢失。

因此，使用事务能够确保数据库操作的完整性和可靠性，特别是在高并发的情况下，可以避免数据的混乱和错误。

假设我们有一个银行应用程序，其中用户可以从其账户中转移资金到其他账户。这个过程涉及两个账户的更新，即源账户和目标账户。

在没有事务保护的情况下，如果在更新源账户后出现错误，但更新目标账户仍然继续，那么就会导致数据不一致。例如，如果在转移500元给目标账户之前，源账户的余额只有400元，那么就需要回滚整个操作，以便恢复原始状态。

通过使用事务，可以确保将所有相关操作视为单个逻辑工作单元。因此，在任何一个操作失败时，整个事务都可以回滚到其初始状态，以避免数据不一致性。

事务隔离

事务隔离是指多个并发事务之间互相隔离的程度。每个事务操作都会对数据库产生影响，如果多个事务同时对同一个数据进行操作，就可能会产生一些问题，例如脏读、不可重复读和幻读等问题。

MySQL提供了四种事务隔离级别，分别是：

• READ UNCOMMITTED(未提交读)
• READ COMMITTED(已提交读)
• REPEATABLE READ(可重复读)
• SERIALIZABLE(串行化)

不同的隔离级别提供了不同的隔离程度，越高的隔离级别可以保证数据的一致性，但会给系统带来一定的性能开销。

在MySQL中，默认的事务隔离级别是可重复读。开发者可以根据实际需求选择合适的隔离级别，在保证数据一致性的前提下尽可能提高系统的并发访问能力。

2、MySQL与MongoDB的区别

答：MySQL和MongoDB其实有挺大不同的。

类型上

MySQL是关系型数据库，使用表来存储数据，而MongoDB是非关系型数据库，使用文档来存储数据。这意味着MySQL中的数据是以行和列的形式存储的，而MongoDB中的数据以JSON样式的文档存储。

存储方式上

然后在MySQL中，不同存储引擎会采用不同的数据存储方式（例如InnoDB，这是MySQL默认使用的引擎，其支持外键和事务）

MongoDB采用虚拟内存+持久化存储的设计，MongoDB会把数据按照页分成多个块，在读写数据时只将需要的页加载至内存，提高性能

同时，MongoDB会周期性地将内存中的数据写入磁盘，以确保数据的一致性和可靠性

数据处理方式

在MySQL中，不同的存储引擎（如InnoDB、MyISAM、MEMORY等）具有不同的数据处理特点，主要包括以下几个方面：

1. 事务支持：不同的存储引擎是否支持事务处理是一个重要的区别。比如，InnoDB支持事务，而MyISAM则不支持。
2. 并发控制：存储引擎对并发访问的支持程度也是一个重要的因素。比如，InnoDB使用行级锁来保证并发数据访问，而MyISAM只支持表级锁定，所以在高并发情况下，InnoDB通常会更好地处理数据。
3. 索引类型：不同的存储引擎支持不同类型的索引，如B树、哈希和全文索引等。这也可能影响查询性能和数据处理能力。
4. 数据缓存：不同的存储引擎也有不同的数据缓存策略和机制。例如，InnoDB使用缓冲池来管理数据行缓存，而MyISAM使用操作系统文件系统缓存来缓存数据块。

MongoDB则基于内存，将热数据（最近被频繁访问的数据）存储在物理内存中，从而达到高速读写的目的

海量数据的存储

MySQL效率相对较低，但是MongoDB不⽀持事务

3、什么是慢查询

​ 在MySQL中，慢查询指的是执行时间较长的SQL语句。通常情况下，如果一个SQL查询的执行时间超过了一定的阈值（默认超时时间为long_query_time，10秒），那么就可以将其视为慢查询。

优化手段：

• 通过运行语句，定位查询较慢的sql
• 在查询时查询区分度最高的字段以提升速度
• 使用EXPLAIN关键字显示MySQL如何通过索引来处理select语句以及连接表的，以帮助优化语句
• 使用order by limit形式的sql语句，让排序的表优先查（将排序和限制条件写在子查询中，然后在外层查询中将子查询的结果进行排序和限制）
• 根据具体的业务场景进行优化

4、谈谈表的优化

在最开始建立表的时候就要考虑拆分逻辑

字段优化：

1、尽量使用TINYINT，SMALLINT，MEDIUM_INT****替代INT类型，如果是非负则加上UNSIGNED

2、VARCHAR的长度只分配真正需要的空间

3、尽量使用整数或者枚举替代字符串类型

4、时间类型尽量使⽤TIMESTAMP而非DATETIME

5、尽量少使用NULL，很难进行查询优化而且还占用额外索引空间

5、什么是B树？什么是B+树？

B树是一种平衡多路查找树，它可以保证所有叶节点到根节点的路径长度相同。

B树也称B-树,它是一颗多路平衡查找树。我们描述一颗B树时需要指定它的阶数，阶数表示了一个结点最多有多少个孩子结点，一般用字母m表示阶数。当m取2时，就是我们常见的二叉搜索树。

在B树中，每个节点可以存储多个关键字，并且每个节点会将关键字按照大小顺序排列。通过这种方式，B树可以快速地进行查找、插入和删除操作，并保证数据的有序性。B树通常被用来实现外部存储器上的文件系统或数据库索引等场景。

(图文转载自nullzx)

上图是一颗阶数为4的B树。在实际应用中的B树的阶数m都非常大（通常大于100），所以即使存储大量的数据，B树的高度仍然比较小。每个结点中存储了关键字（key）和关键字对应的数据（data），以及孩子结点的指针。我们将一个key和其对应的data称为一个记录。但为了方便描述，除非特别说明，后续文中就用key来代替（key, value）键值对这个整体。在数据库中我们将B树（和B+树）作为索引结构，可以加快查询速度，此时B树中的key就表示键，而data表示了这个键对应的条目在硬盘上的逻辑地址。

B+树基于B树的思想，也是一种多路查找树。与B树不同，B+树除了叶子节点外，其他节点均不存储数据，只存储索引信息。并且，B+树中的叶子节点会形成一个有序链表，便于范围查询操作。在B+树中，节点的度数较高，可以存储更多的关键字，从而降低了树的高度，提高了搜索效率。因此，B+树通常被用来实现内存和磁盘之间的索引，例如数据库中的B+树索引。

除此之外B+树还有以下的要求:

1）B+树包含2种类型的结点：内部结点（也称索引结点）和叶子结点。根结点本身即可以是内部结点，也可以是叶子结点。根结点的关键字个数最少可以只有1个。

2）B+树与B树最大的不同是内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中。

3） m阶B+树表示了内部结点最多有m-1个关键字（或者说内部结点最多有m个子树），阶数m同时限制了叶子结点最多存储m-1个记录。

4）内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。

5）每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。

6、为什么MySQL用B+不用B?主要原因是什么？

主要原因:
B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低

7、文件索引和数据库索引为什么使用B+树？

(1) 方便扫库
B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因。

(2) B+tree的磁盘读写代价更低
B+tree的内部结点并没有指向关键字具体信息的指针(红色部分)，因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一块盘中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说I/O读写次数也就降低了;

(3) B+tree的查询效率更加稳定
由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每个数据的查询效率相当

8、MySQL中为什么要有事务回滚机制？

恢复机制是通过回滚日志 (undo log) 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。

当事务已经被提交之后，就无法再次回滚了.

回滚日志作用:

​ 1、能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息

​ 2、在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因

9、InnoDB与MyISAM的区别

事务: InnoDB是事务型的，可以使用Commit和Rollback语句。

在MySQL中，Commit语句用于提交一个事务，并将所有对数据库的更改永久保存到数据库中。

当使用事务处理时，在一个事务内进行的所有数据操作都可以被视为一个单独的操作单元。此时，Commit语句可以将整个事务中的所有操作一次性提交到数据库中，从而确保这些操作能够被永久保存下来。

如果没有执行Commit语句，则数据库不会将事务中所做的更改保存下来，这些更改仅仅是暂时存在于内存中，直到执行了Commit语句，才会将它们写入磁盘并持久化到数据库中。

需要注意的是，一旦执行了Commit语句，就不能再回滚这个事务中所做的更改了。因此，建议在确认所有更改和操作都正确无误之后再执行Commit语句，以避免不必要的数据损失。

索引: InnoDB聚族索引关键字中存放的是数据，而MyISAM聚簇索引中存放的是数据对应的地址。

并发: MyISAM只支持表级锁，而InnoDB还支持行级锁。

外键: InnoDB支持外键。

备份: InnoDB支持在线热备份。

崩溃恢复: MyISAM崩溃后发生损坏的概率比InnoDB高很多，而且恢复的速度也更慢。

其它特性: MyISAM支持压缩表和空间数据索引。

10、MySQL锁

基本分类

全局锁

对整个数据库实例加锁。适用场景：全库逻辑备份（就是把库中的表都select出来存为文本）

命令：Flush tables with read lock（FTWRL）

该目录会使得以下操作阻塞：（整个库处于只读状态）

• 数据更新语句（数据的增删改）
• 数据定义语句（包括建表、修改表结构等）
• 更新类事务的提交语句

表级锁（表锁、元数据锁、行锁）

11、乐观锁和悲观锁

定义

乐观锁：认为对同一数据的并发操作操作不会经常发生，其属于小概率事件，不用每次都对数据上锁

也就是不采用数据库自身的锁机制，而是通过程序实现锁（通常采用版本号、时间戳等机制实现）

悲观锁：使用数据库自身的锁机制来保证数据操作的排他性

适用场景

乐观锁适用于"读多写少"的场景，优点是使用程序实现，不存在死锁问题

悲观锁适用于写操作多的场景，因为写操作具有排他性，使用悲观锁可以防止读写或者写写冲突，缺点是加锁时间比较长，可能会长时间限制其他用于的访问（即并发访问性能不好）

12、幻读

概念

幻读是指在同一个事务内多次查询同一个表时，前后两次查询得到的记录数不一致的情况。

举个例子，假设有一个表格T，事务A首先查询表格T并获得结果集R1，此时另一个事务B向表格T插入了一条新的记录（该记录符合A所查询的条件），接着事务A再次查询表格T并获得结果集R2，发现结果集R2比结果集R1多了一条记录，这就是幻读。

为什么会出现幻读？

这种情况通常发生在一个事务内先后执行了插入或删除操作的情况下。

行锁只能锁定存在的行，对于新插入的操作没有限制

幻读带来的问题

1、破坏了行锁的语义

2、破坏了数据一致性

如何避免幻读？

可以使用加间隙锁的方式来避免出现幻读

什么是间隙锁？其如何避免幻读？

间隙锁是专用于解决幻读问题的锁，它锁了行与行之间的间隙，可以阻塞新插入的操作（在可重复读级别（默认级别）下间隙锁才生效）

缺点：降低并发度、可能导致死锁

13、死锁

死锁是指两个或更多的事务在等待彼此持有的资源（如行或表）而被阻塞的情况，从而导致它们无法继续执行。这种情况下，所有事务都被永久地“死锁”，因为它们都无法继续执行。

例如，如果事务A正在等待锁定某一行，并且事务B同时正在等待锁定与事务A已经持有的同一行，那么这两个事务就会在彼此之间形成一个死锁。在这种情况下，MySQL将会选择其中一个事务作为“牺牲品”，并回滚该事务以解除死锁。