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）等。

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

在 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 也包含间隙锁）。

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

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，再具体文档中的位置）}

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