Java面试 —— MySQL相关

数据库基础

什么是事务？

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功。即使一个操作失败，事务也不会成功。如果所有操作成功则事务提交，其修改将作用于其他数据库进程。如果操作失败，则事务将回滚，该事务所有操作的影响都将被取消。

事务的特性：（ACID）

事务具有4个特征。分别是原子性、一致性、隔离性、持久性。简称事务的ACID特性

1. 原子性（atomicity）： 即不可分割性，事务要么全部执行，要么就全部不被执行。
2. 一致性（consistency）：事务的执行使得数据库从一种正确状态转换成另一种正确状态。如果数据库系统自运行过程中发生了故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所做的修改有一部分已经写入物理数据库，这时数据库就处于一种不正确的状态，也就是不一致状态。即执行事务前后，数据保持一致。
3. 隔离性（isolation）： 在事务正确提交之前不允许把该事务对数据的任何改变提交给任何其他事务。即事务之间是互不干扰的，各并发事务之间是独立。
4. 持久性（durability）：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

ACID实现原理/Mysql中事务的原理

Mysql中的事务满足ACID的特性，其事务的原理就是InnoDB如何保持ACID特性。
MySQL ACID是如何实现的

ACID中，AID是手段，C是目的。

• 原子性（A），需要保证多个DML操作的原子性，要么都成功要么都失败。失败就意味着要对原本操作的数据进行回滚。InnoDB通过UNDO_LOG实现原子性，UNDO_LOG表记录数据修改过程，把修改之前的数据快照保存到UNDO_LOG中，一旦出现错误就直接从UNDO_LOG读取数据，进行反向操作，从而达到原子性目的。
• 一致性（Consistency），即数据的完整性约束没有被破坏。数据库层面提供了比如说主键的唯一约束，字段类型和长度的一些保障。
• 隔离性（I），即在多个事务对同一个数据进行并发访问时，如何避免多个事务之间的干扰导致数据混乱的一个问题。 InnoDB实现了四种隔离级别。通过版本控制（MVCC）实现了读已提交（RC）和可重复读（RR）两种隔离级别，解决了脏读、不可重复度的问题。然后使用行锁/表锁的方式解决了幻读的问题。RC级别下，每次执行快照读生成一次ReadView，可以解决脏读，不能解决不可重复读、幻读问题。RR隔离级别下，可以解决不可重复读，能解决部分幻读问题。
• 持久性（D），通过REDO_LOG实现。MySQL是硬盘性数据库，为了匹配上CPU速度，MySQL设计了Buffer Pool来提高性能。MySQL对数据的修改前，会将数据加载到Buffer Pool来，对内存进行修改后再写回磁盘。这个过程可能会由于数据库宕机，数据就会丢失，导致数据的不一致性。MySQL使用REDO_LOG解决这一问题，每次修改数据时，除了在内存中修改数据，还会在REDO_LOG 中记录这次操作。当事务提交时，会将redo_log进行刷盘。当数据库宕机重启时，会将REDO_LOG内容恢复到数据库中，在根据UNDO_LOG和BIN_LOG决定回滚还是提交数据。

事务隔离级别

并发事务分类

脏写

脏读

不可重复读

幻读

四种隔离级别

按问题的严重性：
脏写 > 脏读 > 不可重复读 > 幻读

舍弃一部分隔离性来换取一部分性能

READ UNCONMITTED 未提交读
READ COMMITTED 已提交读
REPEATABLE READ 可重复读
SERIALIZABLE 可串行化

Mysql默认事务隔离级别是可重复读

隔离级别的作用： 控制读取数据的时机

解决脏读、不可重复读、幻读问题

行锁、间隙锁、临键锁

记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

InnoDB如何解决幻读问题

幻读，指的是一个事务在前后两次相同的范围查询中得到的查询结果不相同。InnoDB中引入了间隙锁和next-key lock 来解决幻读问题。

行锁可以保障当前行被获得所的事务锁定，不被其他事务访问。

而间隙锁则保障一个区间范围内的索引记录不被其他事务访问，直到持有锁的事务释放掉锁。比如以下的插入SQL会被阻塞。

next-key lock（临键锁） 作用相当于记录锁+间隙锁的，会锁住多个索引区间。每个记录的非唯一索引列都存在一把next-key lock，当某个事务持有某条记录的next-key lock时会锁住一段左开右闭区间的数据。如下id>4会锁住多个区间。（4，7], (7, 10], (10, +无穷]。 next-key lock是mysql中默认的行锁算法，

InnoDB引擎和MyIsam 的区别

存储引擎的本质就是定义了数据存储的方式以及数据读写的逻辑。
MySQL5.5以前，默认引擎MyISAM。MySQL5.5之后，默认引擎为InnoDB。

• 数据的存储方式。InnoDB采用数据与索引存放在一同一个文件的形式，而MyISAM则是采用数据与索引分离的形式。
• 对于事务的支持方式不同。前者支持事务，并实现了四种隔离级别。后者不支持事务。
• 对锁的支持不同。前者实现了表锁、行锁、间隙锁和临键锁，而后者支持表锁。
• 对外键的支持不同。前者不支持外键，后者支持。

binlog和redolog的区别

• 使用场景不同。binlog用于数据备份、数据恢复和数据同步（主从同步）。例如在主从同步中master节点的变更会写入到binlog中，然后再把binlog中的数据通过网络传输给slave节点实现数据同步。redolog主要用于保障事务特性ACID中的一致性和持久性，在数据库崩溃时，通过redolog可以实现对未提交的事务进行回滚，对提交了的事务进行持久化，从而保证数据的完整性。此外，通过合理地配置redolog的大小和数量，可以优化MySQL的性能。
• 记录的内容不一样。前者记录的是数据库的逻辑变化，提供了三种记录格式——statement、row以及mixed。后者记录的物理变化，即数据页的变化结果。
• 记录的时机不同。binlog是在主线程中生成逻辑变化，写入到磁盘中，是语句级别的记录方式。而redolog是在InnoDB存储引擎层面的操作，是在MySQL后台线程中生成并且写入到磁盘中，是事务级别的记录方式。

X锁和S锁

对于同一条记录来说，
S锁与S锁不互斥。
S锁与X锁互斥。
X锁与X锁互斥
上述说的是不同事务，但是**同一一个事务对某一个记录加S锁后这个仍可对这条记录加X锁 **
对记录加锁，不论对记录加的是S锁还是X 锁。正常的select * from table where查询条件仍可以访问。
解决超卖问题，在数据层面添加的是X锁

你解释一下MVCC

MVCC过程中会加锁吗

MySQL中的InnoDB存储引擎是支持MVCC的典型例子，它通过以下机制实现MVCC：

快照读（Snapshot Read）：读取操作读取符合条件的数据版本，不会加锁。
当前读（Current Read）：例如SELECT … FOR UPDATE或SELECT … LOCK IN SHARE MODE，这些操作需要对数据进行加锁。
尽管MVCC可以减少锁的使用，但在某些情况下，还是会使用锁来确保数据的一致性，例如：

间隙锁（Gap Lock）：用于防止幻读（Phantom Read）问题。
行锁（Record Lock）：在更新或删除操作时，对被修改的行加锁。
临键锁（Next-Key Lock）：结合行锁和间隙锁，用于处理范围查询中的并发问题。

常用SQL 语句

使用mysql root -p  提示权限不够： Access denied for user 'ODBC'@'localhost' (using password: YES)

msyql -u root -p

use goodsadmin; -- 使用数据库

alter table person add column card_id int(12); --增加列card_id
create table IDCard(card_id int(12) primary key, card_code varchar(25)); --创建IDcard 表


alter table 表名 rename to 新表名;  -- 修改表名
alter table 表名 character set 字符编码;  --修改表格的字符编码
alter table person modify card_id varchar(16);  --修改card_id的数据类型
alter table 表名 change 列名 新列名 新数据类型;  --- 修改某一列的列名、数据类型
alter table 表名 drop 列名;  --删除某一列


show tables;  -- 查询当前数据的所有表
desc 表名;   --查看表结构

-- 添加外键约束：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key (从表外键字段) references 主表(主键字段);
alter table person add constraint fk_person_IDcard_card_id foreign key (card_id) references IDCard(card_id);    --不加单引号

-- SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
select p.* ,c.* from  person p inner join idcard c on p.card_id = c.card_id where p.id = 1; --内连接查询


-- select <字段名> FROM<表1> LEFT JOIN <表2> [ON 子句]

-- 左连接 如果数据不存在，左表记录会出现，而右表为null填充




alter table person add column nation_id int(12);
create table nation(nation_id int(12) primary key, nation_name varchar(12));
alter table person add constraint fk_person_nation_nation_id foreign key (nation_id) references nation(nation_id);


---增加某列
ALTER TABLE skill ADD COLUMN createdTime TIMESTAMP not null DEFAULT now();

索引

主键索引

数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引

二级索引（Secondary Index）又称为辅助索引，是因为二级索引的叶子节点的data域存储的是主键。也就是说，通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等索引属于二级索引。

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

聚簇索引与非聚簇索引

聚簇索引

索引结构与数据记录存放在同一个结点中。
优点：

1. 查询速度非常快。相比于非聚簇索引，聚簇索引少了一次读取数据的IO操作。定位到索引的节点，就相当于定位到了数据。
2. 对排序查找和范围查找优化。聚簇索引将数据按主键顺序物理存储，因此基于主键的范围查询（如 BETWEEN）和排序查询（如 ORDER BY）效率很高。
3. 节省存储空间。减少冗余数据：聚簇索引将数据和索引存储在一起，不需要额外存储一份数据，只需存储索引节点，节省了存储空间

缺点

1. 插入性能影响。

• 频繁页分裂：如果主键不是单调递增的，新数据插入到 B+Tree 的中间位置时，可能会导致页分裂（Page Split），影响插入性能。
• 页分裂开销：页分裂需要移动数据、更新索引节点和分配新页，这些操作都会增加 I/O 开销和 CPU 负载。

2. 更新和删除开销大：

• 记录移动和索引更新：更新主键会导致记录移动，可能需要重新调整 B+Tree 结构，删除记录也可能导致页面的合并操作，这些都会增加操作开销。
• 辅助索引维护：更新主键和删除记录操作会影响所有包含主键的辅助索引，需要同步更新这些索引，增加了维护开销

3. 对主键长度敏感：
   主键长度影响深度：聚簇索引的主键长度过长会导致 B+Tree 的深度增加，从而增加索引的维护开销和查询开销。

非聚簇索引

非聚簇索引(Non-Clustered Index)即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

优点

1. 独立于数据存储。

• 灵活性高：非聚簇索引存储在单独的结构中，独立于数据存储，可以在一个表上创建多个非聚簇索引，提高查询的灵活性和效率。
• 不影响物理存储顺序：非聚簇索引不会改变数据的物理存储顺序，因此可以在不影响数据插入、更新和删除性能的情况下添加索引。
  加速查询：

2. 提高特定查询性能：通过非聚簇索引，可以快速定位到特定列的值，从而加速特定查询的性能，如 WHERE 子句中的查找。

• 支持多列索引：可以为多列创建非聚簇索引，优化多列查询。

3. 减少 I/O 操作。

• 覆盖索引：如果查询所需的列都包含在非聚簇索引中，可以直接从索引中获取数据，而无需访问数据表，减少了 I/O 操作。

4. 辅助索引。
   辅助聚簇索引：在使用聚簇索引的表中，非聚簇索引可以作为辅助索引，提供额外的查询优化路径。

缺点

1. 增加存储开销。

• 索引维护：每个非聚簇索引都是一个单独的结构，需要额外的存储空间来维护索引数据。
• 冗余数据：非聚簇索引中存储的索引键值和指向数据记录的指针会增加存储开销。

2. 维护开销。

• 更新和插入开销：数据插入、更新和删除操作需要同时更新相应的非聚簇索引，增加了维护开销和复杂性。
• 索引碎片：频繁的插入、更新和删除操作可能导致索引碎片，影响查询性能，需要定期重建和优化索引。
  查询开销增加：

3. 回表查询。

• 在使用非聚簇索引进行查询时，如果索引列不覆盖所有查询列，需要进行回表查询（lookup），通过索引定位到数据记录的地址，再访问数据表，增加了查询开销。
• 复合索引的顺序：复合索引（多列索引）的使用顺序很重要，如果查询条件不符合索引的顺序，可能无法利用索引优化查询。

适用场景
非聚簇索引适用于以下场景：

1. 需要针对多个列进行频繁查询的场景：非聚簇索引可以为多个列创建索引，优化不同的查询需求。
2. 查询列与数据表主键不一致的场景：当查询条件涉及的列不是主键列时，非聚簇索引可以显著提高查询性能。
3. 覆盖索引查询：查询所需的列全部包含在非聚簇索引中，可以避免回表，提高查询效率。

MyISAM: 叶节点的 data 域存放的是数据记录的物理地址，称为“非聚簇索引”。检索时，需要先通过索引找到物理地址，然后通过物理地址读取数据。
InnoDB: 主索引的叶节点 data 域存放的是完整的数据记录，称为“聚簇索引”。辅助索引的 data 域存放的是主键，检索时，先通过辅助索引找到主键，再通过主键查找主索引找到数据记录。

正确使用索引的一些建议

哪些情况需要建立索引

1. 主键自动创建唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度 查询中统计或者分组的字段

哪些情况不适合建立索引

1. 表记录太少
2. 频繁更新的字段不适合建立索引
3. 经常增删改的表 如果某个数据列包含许多重复内容，为他建立索引就没有太大效果。

尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

避免索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

1. 使用 SELECT * 进行查询; 建议使用索引覆盖，即查询列与索引列一致。
2. 创建了组合索引，但查询条件未遵守最左匹配原则;
3. 在索引列上进行计算、函数、自动或手动类型转换等操作;
4. 以 % 开头的 LIKE 查询比如 like '%abc’会使得索引失效 而导致全表扫描。
5. 查询条件中使用 or，且 or的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
6. 发生隐式转换;

B树与B+树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。
目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

• B树的所有节点既存放键（key）也存放数据（data），而B+树只有叶子结点存放key和data，其他节点只存放key。
• B树的叶子结点都是独立的；B+树的叶子结点有一条引用链指向与它相邻的叶子结点。
• B树的检索过程相当于对范围内的每个节点的关键字做二分查找，可能还没达到叶子结点，就已经找到目标关键字结束检索了。而B+树的检索效率就比较稳定，任何查找都是从根节点到叶子结点的过程，叶子结点的顺序检索很明显。

左图是 B 树，右图是 B+ 树。data 就是文件地址。

图解B树和B+树

为什么主键的长度过长或者非单调递增会导致主索引频繁分裂

1、如果主键字段长度过长，会有以下影响：

• 增加 B+Tree 的深度：
  B+Tree 的每个节点中可以存储的键值数量是有限的，与每个键值的长度直接相关。键值越长，每个节点能存储的键值越少。因此，主键长度过长会导致 B+Tree 的深度增加，从而增加索引操作（如插入、删除、查找）的 I/O 操作次数。
• 增加磁盘空间占用：
  主键较长不仅会增加索引的存储空间，而且由于 InnoDB 的辅助索引也存储主键值，所有辅助索引的大小也会相应增加，从而增加磁盘空间的占用。
  2、如果主键字段是非单调递增的，会有以下影响
  （1）、增加页分裂（Page Split）频率：
• InnoDB 的 B+Tree 结构要求数据按照主键顺序存储。如果主键是单调递增的，新数据会被插入到当前最后一个叶节点中，这样可以避免频繁的页分裂。
• 如果主键是非单调递增的，新数据可能会插入到 B+Tree 的中间某个节点中，这会导致现有节点的分裂。页分裂会引起大量的页分配和数据移动，导致性能下降。
  （2）降低插入性能：
• 页分裂不仅增加了 I/O 操作次数，还会引起大量的锁竞争和资源争用，进而影响插入操作的性能。
  为了避免这些问题，在设计 InnoDB 表时，建议：

（1）使用较短且单调递增的主键：

• 使用自增的整数类型作为主键（如 INT 或 BIGINT）。这不仅可以减少主键的长度，还能确保数据按照插入顺序存储，减少页分裂的频率。
  （2）避免使用随机主键：
• 避免使用 UUID 或其他随机生成的主键。如果必须使用，可以考虑将其作为辅助索引，而非主键。

为什么索引使用B+树

• B+树中，所有数据记录节点都是按照键值大小顺序存放在同一层叶子结点上，而非叶子结点只存储key信息，这样可以大大加大每个节点存储的key值的数量，降低B+树的高度，从而减少磁盘IO访问次数。一般我们存储的数据在百万级别的话，B+树的高度都是三层左右。
• 除此之外，B+树每次都要访问到叶子结点，查询效率稳定为树的高度。

为什么B+树高度就小，是因为什么，B树为什么就高一些

这是因为B+树中，非叶子结点值存储键值和指向子节点的指针，叶子结点才存储数据。而B树中节点既存储键值、指向字节点的指针、也存储数据。所以在节点大小相同的条件下，B+树能存储的key就越多，因而B+树的高度就就，B树就高一些。

一般我们存储的数据在百万级别的话，B+树的高度都是三层左右
千万行的数据这个B+树索引大概也就3到4层吧。

InnoDB存储引擎操作最小数据大小为16K，B+树中，非叶子节点的占用14字节数据（非叶子节点=主键id为bigint类型占用8字节+索引6字节），因此每层可存储节点161024/14=1170。叶子节点存储数据，假设占用内存大小为1K，那么可存16个数据节点。综上，三层B+树可存数据为：11701170*16=21902400（两千万条数据）

Truncate

TRUNCATE与DELETE的区别

1、DELETE FROM 表名 # 逐行删除每一条记录、

TRUNCATE [TABLE] 表名 # 先删除表后在重新创建 表（效率高）
2、 TRUNCATE不知道删除了几条数据，官方文档的说明是：通常的结果是“ 0行受到影响 ”，这应该被解释为“ 没有信息。”。 而DELETE知道。

3、 TRUNCATE 重置auto_increment的值，delete不会。

为了实现高性能，TRUNCATE绕过了删除数据的DML方法。因此它不能被回滚，不会导致ON DELETE 触发器触发，并且不能对InnoDB具有父子外键关系的表执行

1、 数据库的三大范式

第一范式： 属性不可分。关系中每一个数据不可再分(不能以集合/序列等作为属性)，也就是关系中没有重复的列（比如电话号码这个属性既存在一个手机号又存在一个家庭号码，这种情况就不属于第一范式，除非把手机号作为一个列，家庭号码也作为单独一列。）;

第二范式： 消除部分依赖。在1NF基础之上,消除非主属性对键的部分依赖,则称它为符合2NF;（把学生编号，课程标号，成绩单独拿出来作为一个表）。属性完全依赖于主键。

第三范式： 在2NF基础之上,消除非主属性对键的传递依赖,称为符合3NF;（要确定这个学生的院系，首先要经过学号来确定班级，通过班级来确定院系，所以院系对学号存在传递依赖；把院系拿出来单独作为一个表就可以了）。
使属性不依赖于其它非主属性。 也就是说， 如果存在非主属性对于码的传递函数依赖

100道MySQL数据库经典面试题解析