mysql面试常见问题

1.数据库原理相关：

参考数据库原理知识梳理

1.1 数据库三范式

• 1NF：强调数据表的原子性，每一列必须是不可拆分的最小单元。
• 2NF：实体的属性必须完全依赖于关键字。每个表只描述一件事。
• 3NF：任何非主属性不能对主键子集依赖。表中的每一列只与主键直接相关而不是间接相关。

1.2 数据库模型的三要素

• 数据结构
• 数据操作
• 完整性约束

1.3事务的本质

1. 事务的本质是：当应用程序访问数据库的时候,事务能够简化我们的编程模型,不需要我们去考虑各种各样的潜在错误和并发问题。可以想一下当我们使用事务时,要么提交,要么回滚。因此事务本质上是为了应用层服务的，而不是伴随着数据库系统天生就有的。
2. 事务是数据库操作的最小逻辑工作单元，是一系列SQL操作的集合

事务的特性：原子性、一致性、隔离性、可持久化。（ACID）

• 1.原子性：事务执行的语句要么全部成功要么全部失败即不执行。
• 2.可持久化：事务执行的结果必须要被保存在磁盘这类可持久存储的设备中(断电不失)。
• 3.一致性：事务执行前后，数据库处于一致性状态，即数据库不存在中间状态。
• 4.隔离性：并发场景下，不同事务操作相同数据，并发事务所做的修改隔离，要么是另一个事务修改前，要么是另一个事务修改后，事务不存在中间状态。

2. mysql相关

2.1 MySQL 的架构设计

MySQL 的架构设计

mysql的存储引擎架构将查询处理、其它系统任务、数据的存储和提取等几个模块相分离。

2.2 mysql的事物隔离级别

2.2.1 事务的隔离级别

• ISOLATION_DEFUALT：后端数据库默认隔离级别。
• ISOLATION_READ_UNCOMMITED：RU 读未提交，最低级别，允许读尚未提交的数据变更，可能会出现脏读、幻读、不可重复读。
• ISOLATION_READ_COMMITED：RC 读已提交，允许读取并发事务已提交的数据，可以阻止脏读，但可能会出现幻读、不可重复读。
• ISOLATION_REPEATABLE_READ：RR 可重复读，同一字段，多次读取结果都是一致的，除非数据本身被修改，可阻止脏读、不可重复读，但仍有幻读。
• ISOLATION_SERIALIZABLE：可串行，最高隔离级别，完全服从ACID，没有脏读、幻读、不可重复读，但速度慢，完全锁定事务。

2.2.2 脏读、幻读、不可重复读

首先需要明确的一点时，产生这些现象的场景一定是多个事务并发的请求处理了mysql中的数据，当然并发的读不会产生这些现象。这些现象的产生一定伴随着不同事物的并发读写（资源上的冲突）。
因此在处理资源冲突时，我们常见的方式就是加锁。mysql在施加不同的锁之后就会出现这些问题。
InnoDB存储引擎支持事务，其特点是行锁设计，支持外键，并支持非锁定锁，即默认读取操作不会产生锁。

• 脏读：一个事务读取了被另一个事务改写但尚未提交的数据，如果第二个事务的数据改变后被回滚，第一个事务就会读取到无效的数据，这是称这个并发场景下的错误现象为脏读。

产生脏读的原因：只加了乐观锁（共享锁），事务二在修改记录时，还是允许其它事物去查看。就有可能产生脏读。

• 幻读：当事务T1连续读取几行数据后，另外一个并发事务T2修改（新增或删除）了一些记录，幻读就发生了，第一个事务T1中出现了一些原来没有的额外数据记录（新增或删除）。

产生幻读的原因：没有加间隙锁，事务T1能锁住选中的几行，但是无法锁住这几行的范围。如T1锁住了1-10行。但是事务T2在1-10行间插入了一行

• 不可重复读：不可重复读发生在一个事务执行多次查询，但每次查询的结果都不同，通常由于另外一个事务在中途做了更新。

产生不可重复读的原因：不同事务对同一记录的修改没有做并发版本控制，无法确定事务逻辑的先后顺序，可以理解为对同一行数据的多次修改，但是这些修改没有先后顺序，这行记录的最终结果不确定。

• 串行：是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。 但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用

2.3 mysql的各种锁

InnoDB的几种锁：共享锁（S锁）、排他锁(X锁)、记录锁(Record Locks)、Gap锁(间隙锁)…

2.3.1 mysql的表锁和行级所

1. 表锁：开销最小的锁。
   写锁和读锁：只有没有写锁时，其它用户才能获取读锁，读锁之间时不相互阻塞的。
   行级锁：最大程度支持并发处理（开销也大）行级锁只在存储引擎层面实现。
2. mysql死锁：两个或多个事务在同一资源上相互占用，并请求锁定对方占有的资源。
   (解决mysql死锁的办法：死锁检测和死锁超时，回滚最少行级排他锁的事务）

2.3.2 mysql的事务是如何实现的

mysql的事务只针对InnoDB，因为只有它支持事务MyIsAM不支持。所以mysql的事务是通过InnoDB的redo log日志和锁来保证的：

• 事务的隔离性是通过数据库锁的机制实现。
• 事务的持久性是通过Redo Log来实现。
• 事务的原子性和一致性是通过Undo Log实现的

实现过程：

1. 在操作任务数据之前，首先将数据备份到Undo Log中，然后再进行数据的修改操作。
2. 出现错误时执行Roll Back，系统可以利用Undo Log恢复到事务开始之前的状态。
3. Redo Log是记录新数据的备份，事务提交之前，只将Redo Log持久化即可。
4. 系统崩溃时，数据库未持久化，但Redo Log已经持久化，系统可以根据Redo Log将数据恢复并提交。

先写日志（redolog->binlog->数据刷盘），binlog是mysql原有的，redo log是InnoDB存储引擎所独有的。
MySQL事务和持久化原理

2.3.3 MVCC

MVCC解决了快照读和写之间的并发问题

Multi-Version Concurrency Control多版本并发控制，是一种乐观锁，用于RR(可重复读)、RC(读已提交)隔离级别。使用了行级锁。MVCC实现了非阻塞的读操作，写操作也只锁必要的行。它是通过保存某个时间点的快照来实现的。
InnoDB的MVCC，是通过在每行记录的后面保存两个隐藏的列来实现的。一个保存了行的创建时间，一个保存了行的过期时间（或删除时间）。存储的为系统版本号，每开始一个新的事务，系统版本号就会递增。
InnoDB采用MVCC来支持高并发，并且通过间隙锁（next-key locking）策略防止幻读的出现。
执行查询sql时会生成一致性视图read-view,事务id数组，ID从小到达，未提交->已创建，查询的不同版本的数据结果需要跟read-view做比对从而得到快照结果。
MVCC通过保存数据在某个时间点的快照来实现的，基本特征如下：

• 每行数据都存在一个版本，每次数据更新时都更新该版本。
• 修改时Copy出当前版本随意修改，各个事务之间互不干扰。
• 保存时比较版本号，如果成功commit则覆盖原记录；失败则放弃copy。

2.3.4 InnoDB引擎的MVCC策略

当执行查询sql时会生成一致性视图read-view，它由执行查询时所有未提交事务id数组（数组里最小id为min_id）和已创建的最大事务id(max_id)组成，查询的数据结果需要跟read-view做比对从而得到快照结果。

2.4 WAL和持久化

1. 脏页：内存数据跟磁盘数据页不一致的时候称为这个内存页为“脏页”，（注：mysql叶子节点，页大小为：16k）
2. WAL：Write Ahead Log 策略（先日志后写数据），事务日志采用的是追加的方式，速度要更快。事务日志持久化后，内存中被修改的数据慢慢刷回磁盘。修改数据需要写两次磁盘。
3. redo log作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。
4. bin log作用：用于复制。在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。

2.5 bin log和redo log的一致性问题

事务提交时利用两阶段提交，redo log记录事务Prepare，bin log写入并持久化，redo log增加commit标签。

• 数据库在记录此事务的binlog之前和过程中发生crash，此事务并没有成功提交，则会回滚此事务的操作。与此同时，因为在binlog中也没有此事务的记录，所以从库也不会有此事务的数据修改。
• 数据库在记录此事务的binlog之后发生crash，此时，即使是redo log中还没有记录此事务的commit标签，扫描最后一个binlog文件，并提取其中的事务ID（xid），InnoDB会将那些状态为Prepare的事务（redo log没有记录commit标签）的xid和bin log中提取的xid做比较，如果在binlog中存在，则提交该事务，否则回滚该事务。

2.6 索引

2.6.1 mysql索引及性能相关

Mysql 索引选用的是B+树,平衡二叉树的高度太高,查找可能需要较多的磁盘IO。B树索引占用内存较高(非叶子节点存储数据)。B+树, 主要是查询效率高，O(logN)，可以充分利用磁盘预读的特性，多叉树，深度小，叶子结点有序且存储数据。

1. 强制索引：例如 select * from t force index(a_index) where a between 1 and 1000;
2. 使用前缀索引(字符串部分索引)，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
3. 覆盖索引，直接在索引中就能查询到想要的数据，无需再更具索引，命中的id回表。

每个innodb的表都有个特殊的索引来存放数据-聚集索引，指定主键后，innodb使用它来作为聚集索引，所有的非clustered index都是二级索引，innodb中，每个二级索引的记录中包含了行的主键列，innodb使用这个值去clustered index中搜索行。

4. 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器可能会决定放弃走树搜索功能。隐式类型转换可能因为要求在索引字段上做函数操作而导致了全索引扫描。
5. show processlist 命令可以查看客户端语句的状态。

2.7 mysql删除一个表，存储空间为什么没减少？

如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。

2.8 mysql集群

mysql三种主从复制：

• 同步复制：待所有slave都收到BinLog，并且接受完，Master才认为事务提交成功，性差
• 异步复制：只要Master事务提交成功。后台线程就异步的把Binlog同步给Slave。速度快，但可能会丢失数据
• 半同步复制：Master提交事务，同时把Binlog同步给Slave，只要部分（可设置）收到Binlog即可

2.9 binlog

binlog的三种格式：row格式、statement格式、mixed格式

• row格式：只记录每行数据修改的细节。
• statement格式：每一条修改数据的sql都会被记录在binlog中（减少binlog的日志量，节约IO，提高性能）。
• mixed格式：以上两种模式的混合使用，一般语句的修改使用statement格式保存binlog，当statement无法完成主从复制时采用row模式来记录。比如表结构变更会采用statement，而更新或删除会用row。

2.10 mysql 慢查询原理和处理方法：

1. 索引：

• 未建索引、建了还慢(where中用了!=,or,函数操作等；like语句%开头；字符串没有加‘’；索引字段区分度低 如性别；未匹配最左前缀)
  建议：索引下推：对区分度不大的字段建立联合索引；覆盖索引；对于字符串只用前N位做前缀索引；索引字段避免函数操作；
• 有时索引过多的话，没有使用期望的索引，可以force index 走强制索引；analyze table

3. 等待MDL锁(matedatalock);读写锁之间互斥；show procelist 查看table状态；
4. 等待flush、行锁(部分事务未提交)
5. 大表场景(分库、分表；读写分离)