mysql面试题(拿捏面试官)

基础

什么是DML、DDL、DCL?

DQL数据查询语言
	DQL基本结构是由select,from,where子句组成的查询块
DML数据操纵语言:
	就是我们经常使用到的SELECT、UPDATE、INSERT、DELETE。主要用来对数据库的数据进行一些操作。
DDL数据库定义语言:
	其实就是我们在创建表的时候用到的一些sql,比如说create、alter等。DDL主要是用在定义或者改变表的结构,数据类型,表之间的链接和约束等初始化工作上。
DCL数据库控制语言:
	是用来设置或者更改数据库用户或者角色权限的语句,包括(grant,deny,revoke等)语句。这个比较少用到

Mysql表存储引擎 MyISAM与InnoDB

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

1、InooDB支持事务，而MyISAM不支持事务

2、InnoDB支持行级锁，而MyISAM支持表级锁

3、InnoDB支持MVCC，而MyISAM不支持

4、InnoDB支持外键，而MyISAM不支持 5、InnoDB不支持全文索引，而MyISAM支持

mysql优化

• 加索引
• 避免返回不必要的数据
• 优化sql结构
• 主从架构，提升读性能
• 分库分表

mysql优化一般步骤,以及执行计划(explain)

• 通过mysql慢查询日志定位到那些执行慢的sql
• explain分析低效sql的执行计划(这点非常重要，日常开发中用它分析Sql，会大大降低Sql导致的线上事故)

分库分表

• 水平分库: 以字段为依据,按照一定策略(hash,range等),将一个库中的数据拆分到多个库中

• 水平分表: 以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

• 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

• 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

常用的分库分表中间件

• sharding-jdbc
• Mycat

如何选择合适的分布式主键策略

• 数据库自增长序列或字段。

• UUID

• 雪花算法

• Redis生成ID

事务

事务有哪些特性?

事务是由mysql的引擎来实现的,我们常见的 InnoDB 引擎它是支持事务的

• 原子性（*Atomicity*）:一个事务中的所有操作,要么全部成功,要么全部失败
• 一致性（*Consistency*）:数据库的完整性不会因为事务的执行而受到破坏。
• 隔离性（*Isolation*）:数据库允许多个并发事务同时进行读写的能力,互不干扰,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• 持久性（*Durability*）:事务处理结束后,对数据的修改就是永久的。

InnoDB引擎通过什么技术来保证事务的四大特性?

• 原子性和持久性是通过 redo log (重做日志)来保证的

• 一致性是通过 undo log (回滚日志)来保证的

• 隔离性是通过 MVVC (多版本并发控制) 或锁机制来控制的

并行事务会应发的问题

MySQL 服务端是允许多个客户端连接的，这意味着 MySQL 会出现同时处理多个事务的情况。

那么在同时处理多个事务的时候，就可能出现脏读（*dirty read*）、不可重复读（*non-repeatable read*）、幻读（*phantom read*）的问题。

• 脏读:

​ 一个事务读到了另一个 未提交事务修改过的数据。就意味着发生了 脏读现象。

• 不可重复读

  在一个事务中多次读取同一个数据,如果出现前后两次读到的数据不一样的情况,就意味着发生了[不可重复读现象]。

假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库中读取小林的余额数据，然后继续执行代码逻辑处理，在这过程中如果事务 B 更新了这条数据，并提交了事务，那么当事务 A 再次读取该数据时，就会发现前后两次读到的数据是不一致的，这种现象就被称为不可重复读。

• 幻读:

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

事务的隔离级别

• 脏读：读到其他事务未提交的数据；
• 不可重复读：前后读取的数据不一致；
• 幻读：前后读取的记录数量不一致。

这三个现象的严重性排序如下：

• 读未提交（*read uncommitted*），指一个事务还没提交时，它做的变更就能被其他事务看到；
• 读提交（*read committed*），指一个事务提交之后，它做的变更才能被其他事务看到；
• 可重复读（*repeatable read*），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
• 串行化（*serializable* ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

针对不同的隔离级别，并发事务时可能发生的现象也会不同。

事务的传播机制

事务传播行为（propagation behavior）指的就是当一个事务方法被另一个事务方法调用时，这个事务方法应该如何进行。

例如：A事务方法调用methodB事务方法时，B是继续在调用者A的事务中运行呢，还是为自己开启一个新事务运行，这就是由B的事务传播行为决定的。

Spring七种传播行为

Spring事务机制主要包括声明式事务和编程式事务，此处侧重讲解声明式事务，编程式事务在实际开发中得不到广泛使用，仅供学习参考。

日志

日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

binlog

binlog用于记录数据执行的写入操作(不包含查询)信息,以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

• 逻辑日志 ：可以简单理解为记录的就是sql语句 。
• 物理日志 ：mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更 。

binlog 是通过追加的方式进行写入的，可以通过max_binlog_size 参数设置每个 binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog的使用场景

1. 主从复制: 在Master开启binlog,然后将binlog发送到各个slave,slave端重放binlog从而达到主从数据一直
2. 数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。

binlog的刷盘时机

对于InnoDb存储引擎而言,只有在事务提交时才会记录binlog,此时记录还在内存中,那么binlog是什么时候刷到磁盘中的呢?

mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；
• 1：每次 commit 的时候都要将 binlog 写入磁盘；
• N：每N个事务，才会将 binlog 写入磁盘。

binlog日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW。日志格式通过 binlog-format 指定。

redo log

redo log 适用于崩溃恢复。

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)。

mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。

在计算机操作系统中，用户空间( user space )下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间( kernel space )缓冲区( OS Buffer )。

undo log

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况 。

实际上， 原子性 底层就是通过 undo log 实现的。undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。

同时， undo log 也是 MVCC(多版本并发控制)实现的关键。

索引

二叉查找树

首先，让我们先看一张图：

从图中可以看到，我们为 user 表（用户信息表）建立了一个二叉查找树的索引。

二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

如果我们需要查找 id=12 的用户信息，利用我们创建的二叉查找树索引，查找流程如下：

• 将根节点作为当前节点，把 12 与当前节点的键值 10 比较，12 大于 10，接下来我们把当前节点>的右子节点作为当前节点。
• 继续把 12 和当前节点的键值 13 比较，发现 12 小于 13，把当前节点的左子节点作为当前节点。
• 把 12 和当前节点的键值 12 对比，12 等于 12，满足条件，我们从当前节点中取出 data，即 id=12，name=xm。

利用二叉查找树我们只需要 3 次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要 6 次才能找到。

平衡二叉树

上面我们讲解了利用二叉查找树可以快速的找到数据。但是，如果上面的二叉查找树是这样的构造：

这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找 id=17 的用户信息，我们需要查找 7 次，也就相当于全表扫描了。

导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。

为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。

平衡二叉树又称 AVL 树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1。

下面是平衡二叉树和非平衡二叉树的对比：

由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。

平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。

平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

B 树

我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？

可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘 IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的 B 树。

B 树（Balance Tree）即为平衡树的意思，下图即是一棵 B 树：

图中的 p 节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。

图中的每个节点称为页，页就是我们上面说的磁盘块，在 MySQL 中数据读取的基本单位都是页，所以我们这里叫做页更符合 MySQL 中索引的底层数据结构。

从上图可以看出，B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的 B 树为 3 阶 B 树，高度也会很低。

基于这个特性，B 树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

假如我们要查找 id=28 的用户信息，那么我们在上图 B 树中查找的流程如下：

• 先找到根节点也就是页 1，判断 28 在键值 17 和 35 之间，那么我们根据页 1 中的指针 p2 找到页 3。
• 将 28 和页 3 中的键值相比较，28 在 26 和 30 之间，我们根据页 3 中的指针 p2 找到页 8。
• 将 28 和页 8 中的键值相比较，发现有匹配的键值 28，键值 28 对应的用户信息为（28，bv）。

B+ 树

B+ 树是对 B 树的进一步优化。让我们先来看下 B+ 树的结构图：

根据上图我们来看下 B+ 树和 B 树有什么不同：

①B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。

之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。

如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。

另外，B+ 树的阶数是等于键值的数量的，如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。

一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。

②因为 B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。

那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而 B 树因为数据分散在各个节点，要实现这一点是很不容易的。

有心的读者可能还发现上图 B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

其实上面的 B 树我们也可以对各个节点加上链表。这些不是它们之前的区别，是因为在 MySQL 的 InnoDB 存储引擎中，索引就是这样存储的。

也就是说上图中的 B+ 树索引就是 InnoDB 中 B+ 树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。

通过上图可以看到，在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

聚集索引 VS 非聚集索引

在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。

这里我们着重介绍innodb中的聚集索引和非聚集索引。

\1. 聚集索引（聚簇索引）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。

\2. 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。
明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

锁

mysql死锁问题,如何解决

死锁是指两个或者两个以上的事务在执行过程中因争抢锁资源而造成的相互等待的现象

死锁发生要满足以下四个条件：

1.互斥条件：一个资源每次只能被一个进程使用

2.拥有了资源不去释放

3.不剥夺条件：进程已获得的资源，在未使用之前不能强行剥夺

4.循环等待条件：相互等待

排查死锁的步骤：

• 查看死锁日志show engine innodb status;
• 找出死锁Sql
• 分析sql加锁情况
• 模拟死锁案发
• 分析死锁日志
• 分析死锁结果

在高并发情况下，如何做到安全的修改同一行数据？

要安全的修改同一行数据,就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案。

悲观锁和乐观锁

悲观锁

悲观锁就是对数据的修改抱有悲观的态度的并发控制方式。认为数据每时每刻都有可能变动。

悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外~ 比如，可以使用select…for update

select * from User where name=‘jay’ for update

以上这条sql语句会锁定了User表中所有符合检索条件（name=‘jay’）的记录。本次事务提交之前，别的线程都无法修改这些记录。

• 优点：1.悲观锁利用数据库中的锁机制来实现数据变化的顺序执行，这是最有效的办法
• 缺点：1.一个事务用悲观锁对数据加锁之后，其他事务将不能对加锁的数据进行除了查询以外的所有操作，如果该事务执行时间很长，那么其他事务将一直等待，那势必影响我们系统的吞吐量。

乐观锁

是相对悲观锁而言的,乐观锁假设数据一般情况下不会造成冲突,所以在数据进行提交更新的,才会正式对数据的冲突与否进行检测,如果发现冲突了,则返回用户错误的信息,让用户决定如何去做。 通过version

• 优点：1.乐观锁不在数据库上加锁，任何事务都可以对数据进行操作，在更新时才进行校验，这样就避免了悲观锁造成的吞吐量下降的劣势。
• 缺点：1.乐观锁因为时通过我们人为实现的，它仅仅适用于我们自己业务中，如果有外来事务插入，那么就可能发生错误。

共享/排它锁(Shared and Exclusive Locks)

简单个锁太过简单粗暴,就连读操作也无法并行。于是出现了共享锁与排它锁

• 共享锁(S) ,读取数据时加S锁
• 排它锁(X) ,修改数据时加X锁

共享锁与排他锁：

• 共享锁之间不互斥，读读可以并行
• 排他锁与任何锁互斥，写读，写写不可以并行

意向锁(Intention Locks)

InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁的特点：

①首先，意向锁，是一个表级别的锁(table-level locking)；

②意向锁分为：

意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁

意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

记录锁(Record Locks)

记录锁，它封锁索引记录，例如：

select * from t where id=1 for update; 它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

需要说明的是： select * from t where id=1; 则是快照读(SnapShot Read)，它并不加锁，具体在《17.什么是快照读？》中做了详细阐述。