Mysql面试题以及答案

1 基础

1.1、MySQL有哪些数据库类型？

数值类型
有包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示 1 字节、2 字节、3 字节、4 字节、8 字节的整数类型。

1）任何整数类型都可以加上 UNSIGNED 属性，表示无符号整数。

2）任何整数类型都可以指定长度，但它不会限制数据的合法长度，仅仅限制了显示长度。

字符串类型

包括 VARCHAR、CHAR、TEXT、BLOB。

注意：VARCHAR(n) 和 CHAR(n) 中的 n 并不代表字节个数，而是代表字符的个数。

CHAR 和 VARCHAR 是 MySQL 中用于存储字符串的两种不同数据类型，它们之间的主要区别在于存储方式和性能：

CHAR（固定长度）:
- CHAR 类型用于存储固定长度的字符串。当声明一个 CHAR 类型的字段时，需要指定一个长度（例如 CHAR(10)），这个长度是最大字符数。
- 如果存储的字符串长度小于指定的长度，MySQL 会用空格填充剩余的空间。在检索 CHAR 类型字段时，尾随空格会被去除。
- CHAR 类型的性能通常较好，尤其是当所有值都接近指定长度时。这是因为它有固定的存储大小，便于数据库快速计算记录的物理位置。
- CHAR 类型适用于存储长度相对固定的数据，如密码哈希、性别字段等。
VARCHAR（可变长度）:
- VARCHAR 类型用于存储可变长度的字符串。VARCHAR 同样需要指定最大长度（例如 VARCHAR(255)）。
- 存储 VARCHAR 类型的字符串时，只占用必要的空间加上一个额外的字节或两个额外字节（取决于最大长度是否超过255）来记录字符串的实际长度，不足的部分不会用空格填充。
- VARCHAR 类型在存储长度有很大变化的字符串时更为高效，因为它不会浪费空间。
- VARCHAR 类型适用于存储长度可变的数据，如名字、地址、描述等。

其他区别包括：

存储空间：由于 CHAR 是固定长度，它可能会浪费存储空间，因为短字符串会用空格填充到指定的长度。VARCHAR 只存储实际需要的空间，因此通常更节省空间。
性能：CHAR 字段在某些情况下可能比 VARCHAR 快，特别是在字段长度变化不大时。但是，如果字段通常包含较短的字符串，而指定了较长的 CHAR 长度，那么 VARCHAR 可能会更高效。
尾随空格：CHAR 类型在存储时末尾的空格会被保留，但是在检索时会被去除。而 VARCHAR 类型会保留字符串末尾的空格。

在选择使用 CHAR 还是 VARCHAR 的时候，需要考虑数据的特性和存储需求。如果数据的长度几乎固定，可以选择 CHAR；如果数据的长度差异较大，应选择 VARCHAR。

日期和时间类型

常用于表示日期和时间类型为 DATETIME、DATE 和 TIMESTAMP。

尽量使用 TIMESTAMP，空间效率高于 DATETIME。

TIMESTAMP范围: 1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC

DATETIME范围: 1000-01-01 00:00:00 至 9999-12-31 23:59:59

1.2、什么是三大范式？

数据库设计中的三大范式（Normal Forms，简称NF）是关系型数据库设计的基本原则，旨在通过规范化（Normalization）的方式减少数据冗余和提高数据完整性。以下是三大范式的概述：

第一范式（1NF）:
- 定义：一个关系型数据库表的每一列都是不可分割的基本数据项，即表中的所有字段都是原子性的，每一列都只包含原子数据项。
- 目的：确保每一列的原子性，消除复合属性和多值属性，使得每一行的每一列都包含单一数据值。
- 举例：如果一个表中有一个字段存储了多个电话号码（以逗号分隔），那么这个表就不满足第一范式。
第二范式（2NF）:
- 定义：在第一范式的基础上，非主键字段完全依赖于主键，不存在部分依赖。
- 目的：消除部分依赖，即数据表中的所有非主键字段必须依赖于整个主键，而不是依赖于主键的一部分（如果主键是由多个字段组成的复合主键）。
- 举例：如果一个表的主键由两个字段组成，而表中的某些非主键字段仅依赖于这个复合主键的一部分，那么就需要将这些字段移到另一个表中，以确保它们依赖的是完整的主键。
第三范式（3NF）:
- 定义：在第二范式的基础上，消除了非主键字段对其他非主键字段的传递依赖（也称为对主键的间接依赖）。
- 目的：确保数据表中的每一列都只依赖于主键，消除字段之间的传递依赖，进一步减少数据冗余。
- 举例：如果一个表中有两个非主键字段，其中一个字段可以通过另一个非主键字段推导出来，那么存在传递依赖。解决办法是将能够推导出其他字段的那个字段单独移到一个新表中。

满足这三个范式的数据库设计可以有效地避免数据冗余，简化数据结构，提高数据的一致性和完整性。然而，过度范式化可能导致查询效率降低，因为可能需要连接更多的表。因此，实际数据库设计中可能需要在规范化和性能之间做出权衡。在某些情况下，为了提高查询性能，可能会适当进行反范式化操作。

2、索引

2.1、索引的几种类型或分类？

从物理结构上可以分为聚集索引和非聚集索引

简单来说，聚集索引就是基于主键创建的索引，除了主键索引以外的其他索引，称为非聚集索引，也叫做二级索引。

        由于在InnoDB引擎里面，一张表的数据对应的物理文件本身就是按照B+树来组织的一种索引结构，而聚集索引就是按照每张表的主键来构建一颗B+树，然后叶子节点里面存储了这个表的每一行数据记录。
        所以基于InnoDB这样的特性，聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式。
        同时也意味着每个表里面必须要有一个主键，如果没有主键，InnoDB会默认选择或者添加一个隐藏列作为主键索引来存储这个表的数据行。一般情况是建议使用自增id作为主键，这样的话id本身具有连续性使得对应的数据也会按照顺序存储在磁盘上，写入性能和检索性能都很高。否则，如果使用uuid这种随机id，那么在频繁插入数据的时候，就会导致随机磁盘IO，从而导致性能较低。
        需要注意的是，InnoDB里面只能存在一个聚集索引，原因很简单，如果存在多个聚集索引，那么意味着这个表里面的数据存在多个副本，造成磁盘空间的浪费，以及数据维护的困难。
由于在InnoDB里面，主键索引表示的是一种数据存储结构，所以如果是基于非聚集索引来查询一条完整的记录，最终还是需要访问主键索引来检索。

从应用上可以划分为一下几类

主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；

唯一索引：索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；

普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；

组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；

全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

在设计数据库索引时，应遵循一些基本原则以确保索引具有高效的性能和实用性。以下是一些关键的索引设计原则：

选择性高的字段：
- 优先为那些具有高选择性的字段创建索引。选择性是指不同值的数量占总行数的比例。具有高唯一值的列（如主键）通常是很好的索引候选。
根据查询优化：
- 索引应基于最常用的查询操作进行建立，考虑WHERE子句中的列、JOIN操作中的列以及ORDER BY和GROUP BY子句中的列。
避免冗余索引：
- 避免创建重复和冗余的索引。如果一个列已经是另一个复合索引的前缀，则通常不需要为该列单独创建索引。
复合索引设计：
- 当查询条件中经常包含多个列时，考虑创建复合索引。在创建复合索引时，将选择性最高的列放在最前面。
索引维护：
- 随着数据的变化，索引会发生碎片化，应定期进行索引维护，如重建或重新组织索引。
限制索引数量：
- 过多的索引会增加插入、更新和删除操作的开销，因为这些操作都需要维护索引。因此，避免在不经常用于查询的列上建立索引。
考虑数据类型和大小：
- 较小的数据类型通常更适合索引，因为它们在磁盘上占用更少的空间，可以减少I/O操作。例如，INT比CHAR或VARCHAR更适合索引。
使用前缀索引：
- 对于文本字段（如VARCHAR、TEXT），如果全文索引不适用或不必要，可以使用前缀索引以节约空间，并提升效率。
考虑索引的存储成本：
- 索引虽然可以提升查询性能，但也占用存储空间。需要权衡索引带来的性能提升和额外的存储成本。
测试和分析：
- 使用EXPLAIN或其他工具分析查询计划，验证索引的实际效果，并根据实际的查询负载对索引策略进行调整。
适当反范式化：
- 在某些情况下，为了提高查询性能，可以适当反范式化数据库设计，并创建冗余的数据和索引。
避免对频繁修改的列进行索引：
- 如果某个列的数据频繁更新，每次更新都需要维护索引，可能会降低整体性能。

正确的索引设计需要综合考虑表的数据量、数据分布、数据访问模式（如查询、插入、更新的频率）以及应用的具体业务需求。设计索引的过程往往是一个不断调整和优化的过程。

2.2、为什么 B+ 树比 B 树更适合应用于数据库索引？

MySQL数据库索引通常使用的是B+树，而不是B树，这是因为B+树相比于B树具有一些特性使其更适合作为数据库索引的数据结构。下面列出了B+树相比于B树的几个优点：

查询性能稳定：在B+树中，所有的数据都存储在叶子节点，他们的高度是相同的，因此查询使用时间趋于一致。
叶子节点顺序访问：由于B+树的叶子节点之间是相互连接的，这使得顺序访问变得非常高效。这对于数据库中的范围查询特别重要，因为这些查询经常需要访问顺序排列的数据。
磁盘读写优化：B+树的非叶子节点不保存数据，只保存指向子节点的指针，这意味着相比于B树的同一高度，B+树可以有更多的分支，这样树的高度更低，查询时需要的磁盘I/O操作也就更少。
由于B+树的内部节点不存储实际的数据，只存储键值和子节点指针，这使得每个内部节点可以有更多的子节点。这种结构使得B+树可以更加紧凑，从而减少了树的高度，提高了空间利用率。
更好的删除性能：在B树中，删除操作可能会引起数据在父节点和子节点之间的移动，而在B+树中，数据只在叶子节点中移动，这通常会简化删除操作。

由于这些优点，B+树成为了数据库索引的首选数据结构。在实际的数据库系统中，例如MySQL的InnoDB存储引擎，使用B+树作为其主要索引（聚簇索引）和辅助索引的数据结构。

2.3、InnoDB 为何推荐使用自增主键？

InnoDB存储引擎推荐使用自增主键的原因主要与其索引结构和数据存储方式有关。InnoDB 使用聚簇索引（Clustered Index）来存储表中的数据，聚簇索引的特点是表中的数据按照主键的顺序存储在磁盘上。这种存储方式对于某些类型的查询和操作有显著的性能优势。以下是使用自增主键的几个主要原因：

插入性能：自增主键意味着新记录总是在索引的最后一个位置插入，这样的顺序插入减少了页的分裂和行移动，因为每次插入时不需要重新排序现有的数据，从而提高了插入操作的效率。
聚簇索引优势：在InnoDB中，聚簇索引包含了行的全部数据。如果主键是自增的，数据在磁盘上就是顺序存储的，这对于按照主键顺序访问行的查询非常高效，因为它们可以顺序读取磁盘上的数据块。
减少页分裂：非自增的主键（例如随机的GUID/UUID或者基于非自增字段的复合主键）可能导致中间插入，这样的操作会引起更多的页分裂，从而影响性能和空间利用效率。
避免外部碎片：自增主键可以减少数据的外部碎片，因为新数据总是添加到索引的末尾。
简化辅助索引结构：在InnoDB中，辅助索引（Secondary Index）包含对应的主键列作为指向聚簇索引记录的指针。如果主键比较简单（如自增的整数），辅助索引的大小也会相对较小。

3 存储结构

3.1、什么是 InnoDB 的页、区、段？

InnoDB是MySQL默认的存储引擎之一，它使用了一种叫做多版本并发控制（MVCC）的技术来提高并发性能，并且支持事务处理、行级锁定和外键。InnoDB存储引擎在物理文件层面上对数据进行组织和管理时，使用了页（Page）、区（Extent）、段（Segment）这几个概念。

以下是对这些概念的简要解释：

页（Page）:
- 页是InnoDB磁盘管理的基本单位，也是数据存储和读写的最小单元。InnoDB默认的页大小是16KB，但是在MySQL 5.6及以后的版本中，页大小可以配置为4KB、8KB、16KB、32KB或64KB。
- 数据页中可以包含行记录、索引等信息。
区（Extent）:
- 一个区是由连续的页组成的集合，通常由64个连续的页组成，因此一个区的大小通常为1MB（16KB * 64）。区是分配空间的单位，InnoDB会一次性分配一个区的空间，提高空间的分配效率。
段（Segment）:
- 段是由一个或多个区组成的更高级别的数据结构，是InnoDB存储结构中的逻辑单元。每个段可以是索引段（用于存储索引信息）或数据段（用于存储实际的行数据）。
- 根据段存储的内容不同，可以进一步分为表空间段、索引段、回滚段等。

在InnoDB的存储组织中，表数据和索引实际上是存储在不同类型的段中，这些段又是由多个区组成的，而每个区由多个页组成。当一个表或索引的数据超出当前段的容量时，InnoDB会分配新的区来扩展该段的空间。

通过这种组织方式，InnoDB能够有效地管理底层的磁盘空间，优化数据的存储和访问性能。此外，InnoDB的页结构也使得它能够支持高效的缓存和读写操作，以及行级的锁定和MVCC。

B+ 树的叶子节点存放的是我们的具体数据，非叶子结点是索引页。所以 B+ 树将数据分为了两部分，叶子节点部分和非叶子节点部分，也就我们要介绍的段 Segment，也就是说 InnoBD 中每一个索引都会创建两个 Segment 来存放对应的两部分数据。

Segment 是一种逻辑上的组织，其层次结构从上到下一次为 Segment、Extent、Page。

4、事务

数据库事务的四种特性_jdbc的数据库事务不包括下面哪个特性-CSDN博客

悲观锁和乐观锁_悲观锁性能一定比乐观锁差吗-CSDN博客

4.1 Innodb的事务实现原理？

原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。
持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
隔离性：通过锁以及MVCC,使事务相互隔离开。
一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

4.2 redo 和undo日志

在MySQL中，尤其是使用InnoDB存储引擎时，redo日志和undo日志是支持事务处理和故障恢复的关键组件。

Redo 日志（Redo Log）： Redo日志是用来保证事务的持久性（Durability），它是一个重做日志，记录了所有修改数据库状态的操作。在事务被提交之前，这些修改操作的记录会被写入redo日志。如果数据库发生故障（比如系统崩溃或断电），在重启后可以使用redo日志重新执行（redo）这些操作，以确保这些修改被应用到数据库中，从而达到事务的持久性。

Redo日志是循环使用的，它通常由两个或更多的固定大小文件组成，这些文件统称为redo log buffer。当buffer满了或事务提交时，buffer中的日志会被刷新（flush）到磁盘上的日志文件中。
Undo 日志（Undo Log）： Undo日志主要是用来实现事务的原子性（Atomicity）和隔离性（Isolation）。Undo日志记录了事务中所做的所有修改的反向操作。如果一个事务需要被回滚（rollback），MySQL可以使用undo日志来撤销事务所做的修改，保持数据的一致性。

Undo日志存储在undo tablespace中，当一个事务被提交后，相关的undo日志并不会立即被删除，因为它们可能还会被其他事务所需要。MySQL会在适当的时候自动清理和回收undo空间。

总结一下，redo日志和undo日志是InnoDB存储引擎提供事务特性的重要机制。Redo日志确保事务的持久性，即使在系统崩溃后也能够保证事务提交的修改能够被恢复。而undo日志则支持事务的原子性和隔离性，允许事务在出现错误时撤销修改，并提供并发事务的数据隔离能力。

4.3 MVVC

在MySQL中，尤其是使用InnoDB存储引擎时，MVCC（多版本并发控制）是一种用来实现事务的隔离性和非锁定读取的技术。MVCC 允许多个事务同时对同一数据进行读取和修改，而不会彼此阻塞，从而提高了系统的并发性能。现在我们详细介绍下它的工作原理：

数据版本化

MVCC 通过为数据行保存多个版本来工作，每个版本都有自己的时间戳。这些时间戳与事务的版本号相对应。在InnoDB存储引擎中，时间戳是通过系统版本号来实现的，每个事务开始时都会获得一个唯一的系统版本号。

读操作

一致性非锁定读（Consistent Nonlocking Read）：在默认隔离级别（REPEATABLE READ）下，当一个事务进行读取操作时，它只会看到在该事务开始之前已经提交的修改，或者是该事务自身所做的修改。这意味着，无论其他事务如何修改数据，当前事务都能看到一个一致的数据快照。这通过使用undo日志来实现，即使数据在物理上已经被另一个事务修改了，当前事务仍然可以通过undo日志看到旧版本的数据。

写操作

Create Read View: 当一个事务需要修改数据时，InnoDB会创建一个新版本的数据行，并把修改应用到这个新版本上。同时，InnoDB还会保留旧版本的数据行，这样其他事务就可以根据需要访问它。这个新旧版本的管理是通过undo日志来实现的。

保存点和垃圾回收

MVCC 通过使用undo日志来为每个数据行提供多个版本，当不再需要这些旧版本时（例如，没有更早的事务需要访问这些版本），系统会自动清理它们以避免空间浪费。这个清理过程被称为垃圾回收（Purge）。每当提交一个事务时，系统都会检查是否可以清理一些旧的版本。

MVCC机制的主要优势是它允许读取操作在不加锁的情况下与写入操作并发执行，大大减少了锁竞争，从而提高了数据库系统的并发性能。这是InnoDB存储引擎在多用户环境下能够高效工作的关键之一。

5、锁

MySQL中的锁主要用于管理对数据库中数据的并发访问，以保证事务的隔离性和数据的一致性。以下是MySQL中常见的几种锁：

全局锁（Global Lock）：通常通过FLUSH TABLES WITH READ LOCK命令来实现，它会对整个数据库实例加锁，通常用于进行全库备份等操作。
表级锁（Table Lock）：在MySQL中，表级锁是最基本的锁策略，它会锁定整张表。MyISAM和MEMORY存储引擎使用表级锁。表级锁分为读锁（共享锁）和写锁（排它锁）。
行级锁（Row Lock）：行级锁是最细粒度的锁，它允许对数据库表中单独的行进行加锁。InnoDB和XtraDB存储引擎支持行级锁。行级锁同样分为共享锁（读锁）和排它锁（写锁）。
意向锁（Intention Locks）： InnoDB实现了意向锁，这是一种表级锁，用于表示事务想要在表的行上加排它锁或共享锁。意向锁分为意向共享锁（IS）和意向排它锁（IX）。
记录锁（Record Lock）：记录锁是行级锁的一种，它会锁定索引记录，而不是整行数据。在InnoDB存储引擎中，记录锁直接作用于索引记录上。
间隙锁（Gap Lock）：间隙锁是InnoDB特有的一种锁，它锁定一个范围，但不包括记录本身，主要用来解决幻读问题。
临键锁（Next-Key Lock）：临键锁是InnoDB存储引擎的默认行锁，它是记录锁和间隙锁的组合，锁定一个范围，并且包括记录本身。
自增锁（Auto-Increment Lock）：自增锁是InnoDB存储引擎用于管理AUTO_INCREMENT属性的特殊表级锁，它确保了自增值的唯一性和连续性。
死锁（Deadlock）：死锁并不是一种锁的类型，而是指两个或多个事务在相互等待对方释放锁的情况，导致事务永远无法继续执行。MySQL会自动检测死锁并解决，通常是通过回滚其中一个事务来释放锁资源。
元数据锁（Metadata Lock）：元数据锁是MySQL中用来管理对数据库对象（如表）元数据的修改。当一个事务对表进行操作时，它会自动获取表的元数据锁，以防止表结构在使用中被修改。