MySQL面试题及答案

转自： https://blog.csdn.net/feihyhl/article/details/118057332

数据库的几大范式

• 第一范式（1NF）无重复的列
  所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
• 第二范式（2NF）属性完全依赖于主键 [ 消除部分子函数依赖 ]
  如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键， 则称为第二范式模式。
• 第三范式（3NF）属性不依赖于其它非主属性 [ 消除传递依赖 ]
  如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R为第三范式模式。满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。

DDL、DML、DCL分别指什么

• DML：Data Manipulation Language 数据操纵语言，如insert,delete,update,select(增、删、改、查)
• DDL：Data Definition Language 数据库定义语言，如 create procedure之类
• DCL：Data Control Language 数据库控制语言，如grant,deny,revoke等，只有管理员才有这样的权限。

TRUNCATE TABLE与DELETE区别

Truncate table 表名 速度快,而且效率高，区别如下:

• TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
• DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
• TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
• 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
• TRUNCATE TABLE 不能用于参与了索引视图的表。

left join，right join，inner join区别

left\right join是外部连接，inner join是内连接
外部连接有主表与从表，主表在left中是左侧表，right中是右侧表，主表数据会全部显示，从表数据则只显示关联部分匹配的数据，无匹配的数据用null补全
内连接则只显示两表关联条件匹配的数据

数据库事务ACID（原子性、一致性、隔离性、持久性）

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。（执行单个逻辑功能的一组指令或操作称为事务）

• 原子性
  原子性是指事务是一个不可再分割的工作单元，事务中的操作要么都发生，要么都不发生。
• 一致性
  一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
• 隔离性
  多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。
• 持久性
  意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

其中一致性依托于另外三大特性

事务的基本特性（ACID），靠什么保证

• 原子性： 由undo log日志保证，它记录了回滚日志，事务回滚时撤销已经成功的SQL
• 一致性： 由其它三大特性来保证
• 隔离性： 由MVCC来保证
• 持久性： 由内存 + redo log保证，mysql修改数据时在内存 + redo log记录这次操作，宕机时由 redo log恢复

MySQL事务类型

• 扁平事务(Flat Transactions)
• 带有保存点的扁平事务(Flat Transactions with Savepoints)
• 链事务(Chained Transactions)
• 嵌套事务(Nested Transactions)
• 分布式事务(Distributed Transactions)

事务隔离级别

• 读未提交
• 读已提交
• 可重复读
• 串行化

脏读、幻读、不可重复读分别是什么

• 脏读 ：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
• 幻读 : 是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。 同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象 发生了幻觉一样。
• 不可重复读 ：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读

MySQL如何防止幻读

MySQL InnoDB支持三种行锁定方式：

• 行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。
• 间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。
• Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。

快照读时，使用mvcc来避免幻读
当前读时，使用next-key Lock来避免幻读

MySQL的RR是通过GAP解决的，那什么是GAP锁？

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁，用唯一索引查询唯一的行数据，并不会产生gap锁

1. 间隙锁实质上是对索引前后的间隙上锁，不对索引本身上锁，在索引记录之间，或者在索引之前，或者索引之后的区间上加锁，就是gap锁，例如：
   SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
   由于在c1=10和c2=20之间已经加上gap锁，所以不管数据表中是否有c1=15这条数据，这个 sql都会阻止试图插入c1=15的事务。
2. 一个GAP锁可能会锁一个索引、多个索引、或者空索引。
3. GAP锁权衡了性能和并发，并且它只用作于特定的隔离级别。
4. GAP主要通过两个方面来实现的：

防止间隙内有新数据被插入
防止已存在的数据，更新成间隙内的数

如何关闭GAP间隙锁？

可以把隔离级别降为读已提交，或者开启参数innodb_locks_unsafe_for_binlog（默认为0）。
在my.cnf里面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1

快照读和当前读是什么

• 快照读：
  普通的 select * from table where ?
• 当前读：
  select * from table where ? lock in share mode;
  select * from table where ? for update;
  insert into table values (…);
  update table set ? where ?;
  delete from table where ?;

什么是MVCC

MVCC的原理与copyonwrite类似，全称是Multi-Version Concurrent Control，即多版本并发控制。

• 基本原理
  MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。
• 基本特征
  每个事务操作都存在一个版本，每次数据更新时都更新该版本。
  修改时Copy出当前版本随意修改，各个事务之间无干扰。
  保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

InnoDB存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚 借助 undo log 实现）。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较，若事务成功提交，则更新版本号。
每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

undo log

undo log 记录事务回滚操作日志，可分为两种
insert undo log
update undo log
insert undo log是insert操作中产生的undo log，因为只对本事务可见，该类undo log在事务提交后就可以删除，不需要进行purge操作
update undo log是delete和update操作产生的undo log。此类undo log是MVCC的基础，在本事务提交后不能简单的删除，需要放入purge队列purge_sys->purge_queue，等待purge线程进行最后的删除。

什么是两阶段提交

当有数据修改时，mysql会先将数据写入到 redo log 中，状态为prepare，再将数据写入到 binlog 中，再提交事务，修改 redo log 中的状态为 commit。

为什么要使用两阶段提交

先写redolog再写binlog
如果在一条语句redolog之后崩溃了，binlog则没有记录这条语句。系统在crash recovery时重新执行了一遍binlog便会少了这一次的修改。恢复的数据库少了这条更新。
先写binlog再写redolog
如果在一条语句binlog之后崩溃了，redolog则没有记录这条语句（数据库物理层面并没有执行这条语句）。系统在crash recovery时重新执行了一遍binlog便会多了这一次的修改。恢复的数据库便多了这条更新。

Crash recovery

在做Crash recovery时，分为以下3种情况：

• binlog有记录，redolog状态commit：正常完成的事务，不需要恢复；
• binlog有记录，redolog状态prepare：在binlog写完提交事务之前的crash，恢复操作：提交事务。（因为之前没有提交）
• binlog无记录，redolog状态prepare：在binlog写完之前的crash，恢复操作：回滚事务（因为crash时并没有成功写入数据库）

redo和binlog这两种日志有以下三点不同

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

索引原理

把无序的数据变成有序的查询

1. 把创建索引的列进行排序
2. 对排序的结果生成倒排表
3. 在倒排表内容上拼上指向记录存储的地址
4. 查询时，先查询排倒表，拿到数据地址指向，从而获取到具体数据

聚簇索引与非聚簇索引区别

• 取簇索引：索引与数据按一定顺序存放在一起，且数据物理地址存入顺序与索引顺序一致。
• 非聚簇索引：索引与数据不存放在一起，索引指向数据物理地址。
  B+树叶子节点存储主键索引及值，且是有序的链表形式，指向当前行数据存储的物理地址，所以B+树既是聚簇索引也是非聚簇索引

什么是回表

普通索引使用B+树在叶子节点存储索引本身及主键值，当使用普通索引查询所有数据时，会先查询到主键，再通过主键查询行数据，所以叫回表，当只查询主键值和普通索引列时，就不用回表。也可以把要查询的所有列组成组合索引，避免了回表，这也叫索引覆盖。

MySQL索引结构，各自优劣

Hash 索引 与 B+树 索引
Hash索引在单条等值查询时有很大的优势，但存在大量重复key时，效率也会变低，会发生Hash碰撞问题
B+树 在各个场景查询效率波动不大

索引类型

• PROMARY KEY(主键索引)：不允许出现相同的值
• UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
• INDEX(普通索引)：允许出现相同的索引内容
• fulltext index(全文索引)：可以针对值中的某个单词，效率不高
• 前缀索引：如果数据太长，可选择前N个字建立索引
• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

MySQL索引采用最左匹配规则

给 （a, b）两个字段建立组合索引，有以下四种情况：

1. where a = ? and b = ?;
2. where a = ?;
3. where b = ?;
4. where b = ? and a = ?;
   只有第三种情况不会走索引，至于第四种情况为什么会走索引，MySQL有优化器

索引失效情况

• like 以%开头，索引无效
• or语句前后没有同时使用索引
• 组合索引，不是使用第一列索引
• 数据类型出现隐式转化
• 在索引字段上使用not，<>，!=，如 key<>0 改为 key>0 or key<0 可生效
• 对索引字段进行计算操作、字段上使用函数
• 当全表扫描速度比索引速度快时，mysql会使用全表扫描

希望不会误人子弟，同时也欢迎大家指正和补充，我会及时更新

转自： https://blog.csdn.net/feihyhl/article/details/118057332