MySQL面试题及答案
数据库的几大范式
- 第一范式（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锁
- 间隙锁实质上是对索引前后的间隙上锁，不对索引本身上锁，在索引记录之间，或者在索引之前，或者索引之后的区间上加锁，就是gap锁，例如：
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
由于在c1=10和c2=20之间已经加上gap锁，所以不管数据表中是否有c1=15这条数据，这个 sql都会阻止试图插入c1=15的事务。 - 一个GAP锁可能会锁一个索引、多个索引、或者空索引。
- GAP锁权衡了性能和并发，并且它只用作于特定的隔离级别。
- 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 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
刷入 bin log 有以下几种模式
- STATMENT
基于 SQL 语句的复制(statement-based replication, SBR)，每一条会修改数据的 SQL 语句会记录到 bin log 中
【优点】：不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能
【缺点】：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等 - ROW
基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了
【优点】：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题
【缺点】：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨 - MIXED
基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log
关于Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念以及关系
Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的
Undo log 记录的是数据操作前的样子
redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）
bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）
从准备更新一条数据到事务的提交的流程描述
首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
在数据被缓存到缓存池的同时，会写入 undo log 日志文件
更新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redo log buffer 中
完成以后就可以提交事务，在提交的同时会做以下三件事
（第一件事）将redo log buffer中的数据刷入到 redo log 文件中
（第二件事）将本次操作记录写入到 bin log文件中
（第三件事）将 bin log 文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记
MySql缓存数据一致性问题
MySQL所有操作都是基于内存的，在恢复时也是将数据从redo log中刷入 Buffer Pool，什么时候会入库呢，其实 MySQL 会有一个后台线程，它会在某个时机将我们Buffer Pool中的脏数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了。
SQL执行过程
- 建立连接
- 查询缓存
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。 - 分析器
分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。
其次做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足 MySQL 语法。 - 优化器
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。 - 执行器
开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误
如果有权限，就打开表继续执行。打开表的时候，优化器就会根据表的引擎定义，去使用这个引擎提供的接口。
索引原理
把无序的数据变成有序的查询
- 把创建索引的列进行排序
- 对排序的结果生成倒排表
- 在倒排表内容上拼上指向记录存储的地址
- 查询时，先查询排倒表，拿到数据地址指向，从而获取到具体数据
聚簇索引与非聚簇索引区别
- 取簇索引：索引与数据按一定顺序存放在一起，且数据物理地址存入顺序与索引顺序一致。
- 非聚簇索引：索引与数据不存放在一起，索引指向数据物理地址。
B+树叶子节点存储主键索引及值，且是有序的链表形式，指向当前行数据存储的物理地址，所以B+树既是聚簇索引也是非聚簇索引
什么是回表
普通索引使用B+树在叶子节点存储索引本身及主键值，当使用普通索引查询所有数据时，会先查询到主键，再通过主键查询行数据，所以叫回表，当只查询主键值和普通索引列时，就不用回表。也可以把要查询的所有列组成组合索引，避免了回表，这也叫索引覆盖。
MySQL索引结构，各自优劣
Hash 索引 与 B+树 索引
Hash索引在单条等值查询时有很大的优势，但存在大量重复key时，效率也会变低，会发生Hash碰撞问题
B+树 在各个场景查询效率波动不大
索引类型
- PROMARY KEY(主键索引)：不允许出现相同的值
- UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
- INDEX(普通索引)：允许出现相同的索引内容
- fulltext index(全文索引)：可以针对值中的某个单词，效率不高
- 前缀索引：如果数据太长，可选择前N个字建立索引
- 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一
MySQL索引采用最左匹配规则
给 （a, b）两个字段建立组合索引，有以下四种情况：
- where a = ? and b = ?;
- where a = ?;
- where b = ?;
- where b = ? and a = ?;
只有第三种情况不会走索引，至于第四种情况为什么会走索引，MySQL有优化器
索引失效情况
- like 以%开头，索引无效
- or语句前后没有同时使用索引
- 组合索引，不是使用第一列索引
- 数据类型出现隐式转化
- 在索引字段上使用not，<>，!=，如 key<>0 改为 key>0 or key<0 可生效
- 对索引字段进行计算操作、字段上使用函数
- 当全表扫描速度比索引速度快时，mysql会使用全表扫描
希望不会误人子弟，同时也欢迎大家指正和补充，我会及时更新
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
