MySQL 面试之基础知识篇

一、基础概念

1、MySQL是一个传统的关系型数据库。广泛应用于OLTP场景（支持事务）；

拓展
OLTP:联机事务处理，是传统的关系型数据库的主要应用，用于基本的事务处理；【日常处理】
OLAP:联机分析处理，数据仓库系统的主要应用；支持复杂的分析操作，侧重决策支持，并且提供易懂的查询结果；【数据分析】

2、数据库三范式

• 数据库表中的字段都是单一属性，不可再分。强调的是列的原子性；数据库表的每一列都是不可分割的原子数据项；
• 要求数据库表中的每个实例或行必须可以被唯一区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一标识属性列被称为主键
• 确保每列都和主键列直接相关，而不是间接相关不存在其他表的非主键信息；

DML：数据操作语言，用于检索或者修改数据。我们平常最常用的增删改查就是DML；
DDL：数据定义语言，用于操作数据结构，比如创建表，删除表，更改索引都是DDL；
DCL：数据控制语言，用于定义数据库用户的权限，比如创建用户，删除用户都是DCL；

二、MySQL特性

1、存储引擎
Innodb引擎：提供了对数据库ACID事务的支持，并且还提供了行级锁和外键的约束。
MyISAM引擎：不支持事务，也不支持行级锁和外键；
MEMORY引擎：所有数据都在内存中，数据处理速度快，但是安全不高；

2、事务ACID和隔离级别

1. 事务：

• 特性：原子性，一致性，隔离性，持久性
  原子性：要么全部执行成功，要么全部不执行；
  一致性：事务前后数据的完整性必须保持一致；
  隔离性：当多个事务同时触发时，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离；
  持久性：事务完成之后的改变是永久的；

数据通过原子性、隔离性、持久性来保证一致性；

• 原子性如何实现的？？
  InnoDB,在干活之前，先将要做的事情记录到一个叫undo log的日志文件中，如果失败了或者主动rollback，就可以通过undo log的内容，将事务回滚；

undo log：逻辑日志，记录的是sql执行相关的信息；

• 持久性是如何实现的？？

持久化：通俗解释，把数据写到磁盘上，也称为落盘；

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。所以被称之为基于磁盘的数据库系统。由于CPU速度和磁盘速度不匹配，通常会用缓冲池技术来提高数据库的整体性能；缓冲池其实就是一块内存区域；
“读”操作首先将磁盘读到的页放在缓冲池中，下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，则读取缓冲池的该页，否则直接读取磁盘的；
“修改”操作首先修改缓冲池中的页，然后再以一定的频率刷新到磁盘上；

“脏页”
修改过程中，如果缓冲池中的页已经被修改了，但是还没有刷新到磁盘上，那么我们就成缓冲池中的这页是“脏页”；

• 如果MySQL宕机，那么Buffer pool中修改的数据不是会丢失吗？？
  Innodb引入了redo log来解决这个问题。当数据修改时，会先在redo log记录这次操作，然后再修改缓冲池中的数据，当事务提交时，会调用fsync接口对redo log进行刷盘；
  如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复；
  redo log是持久化的核心；

2. 隔离级别：

• 4种隔离级别：读未提交、读已提交、可重复读、串行化；
  读未提交：读到还没有提交的数据；
  读已提交：读到其他事务已经提交的数据；
  可重复读：在同一事务中多次读取同一数据的结果是一样的；【MySQL默认的隔离级别】
  串行化：最高事务隔离级别，不管多少事务，后访问的事务必须等前一个事务执行完成，才能继续执行；多个事务互不干扰，不会出现并发一致性问题；
  
  事务并发带来的并发一致性问题：
• 丢失更新：一个事务的更新操作会被另一个事务的更新操作所覆盖；
• 脏读：读到了其他事务未提交的数据；未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库，也就是不存在的数据。读到了不一定最终存在的数据，这就是脏读；
• 不可重复读：在同一事务内，不同的时刻读到的同一批数据可能是不一样的；多用于数据的更新；
• 幻读：针对数据插入操作来说。假设事务A对某些行的内容做了修改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚修改的对于某些数据未起作用，但其实是事务B刚插入进来的；

3. 锁的分类
   从锁粒度来讲：表级锁、行级锁
   从强度上讲：共享锁/读锁、排它锁/写锁；

MySQL服务器层并没有实现行锁机制，行锁只在存储引擎层实现；
InnoDB支持行锁he表锁；而MyISAM支持表锁；

表锁：

• 概念：顾名思义其实就是锁定整张表；不管什么存储引擎，对于表锁的策略都是一样的；是开销最小的锁机制；
• 优点：表锁直接锁定整个表，所以可以很好的避免死锁问题；
• 缺点：锁的粒度大带来的就是锁资源争用的概率也会最高，导致并发率降低；

行锁

• 概念：锁住某一行；

• 分类：

  • 记录锁：基于唯一索引的，锁住的是改行的索引记录；即使没有设置索引，也会有innodb自己的“隐式主键”来进行锁定；

begin;

//username没有索引，加写锁；
select * from user where username = 'haha' for update;

另一个事务；
begin ;

update user set username = '22' where user_id = '100';//修改一条记录会发现这条记录一直在请求，最后超时关闭事务；

结论：行锁锁住的是索引，而不是单独的一条记录。会因为索引失效而直接退化为表锁；所以如果两个事务分别操作两条不同记录拥有相同的索引，某个事务会因为行锁被另一个事务占用而发生等待；

• 间隙锁：锁定一段范围内的索引记录

begin ;

select * from user where user_id between 100 and 200 for update;//对user_id在100到200范围（不包括100,200这两个值）内的加锁，

如果此时新填一个user_id等于150的记录，会告诉你添加失败
 1062 - Duplicate entry '4-4' for key 'PRIMARY'

共享锁/读锁：允许事务读操作；多个事务在同一时刻可以读取同一个资源；
排它锁/写锁：一个写锁会阻塞其他的读锁和写锁，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源；

拓展：
意向锁：允许事务在行级上的锁和表级上的锁同时存在；

• 如何加锁 ？

锁机制：InnoDB是可以随时加锁，但不代表可以随时解锁；只有事务commit或者rollback才可以释放锁，而且所有的锁在同一时刻被释放；

对于常见的DDL语句（create,alter）InnoDB会自动给相应的表加表级锁；
对于常见的DML语句（update,delete,insert）InnoDB会自动给相应的记录加写锁；
对于普通的select语句，InnoDB不会加任何锁；
InnoDB也支持通过特定的语句进行显示锁定：两种模式：
select …… lock in share mode加共享锁/读锁；
select …… for updates 加排它锁/写锁；

三、索引

大家都知道加上索引sql语句会变快，为什么？这个问题大家却回答的没有逻辑性；

不过没关系，一一分析一下几个大问题

1、索引是什么
2、什么能够成为索引？
3、索引为什么能加快速度？
4、mysql的索引是什么，为什么选择B+Tree?

• 索引是什么？
  索引你是一种特殊的文件，他们包含着对数据表里所有记录的引用指针；
  索引是一种数据结构，而且索引是一个文件，是要占据物理空间的；

• 索引的优缺点
  优点：
  可以大大加快数据的检索速度；
  通过使用索引，可以再查询的过程中，使用优化隐藏器，提高系统的性能；
  缺点：
  时间方面：创建索引和维护索引需要耗费时间，而且索引也需要动态的维护，会降低增/删/改的执行效率；
  空间方面：索引需要占物理空间；

• 创建索引的方式

alter table table_name add index index_name(column_list);

create index index_name on table_name(column_list);

• MySQL有几种索引类型？
  普通索引：一个索引只包含单个列，一个表可以有多个单列索引；
  唯一索引：索引猎德值必须是唯一的，但允许有空值；
  复合索引：多列值组成一个索引，专门用于组合搜索，效率大于索引合并；
  聚簇索引：并不是单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引都在同一个结构中保存了b+Tree和数据行；
  非聚簇索引：不是聚簇索引，就是非聚簇索引；

• 索引的底层实现方式
  Hash索引：对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针；
  B-Tree索引：
  
  B+Tree
  

• 为什么索引结构默认选择B+Tree？（这种问题就是分析其他索引的缺点，说出B+Tree的优点即可）
  Hash索引虽然可以快速定位，但是没有顺序，IO复杂度高；
  MySQL基于Hash实现，只有Memory存储引擎显式支持哈希索引；
  Hash不支持范围查询；
  如果有大量重复键值的情况下，哈希索引的效率很低，以为存在哈希碰撞问题；

B+Tree非叶子节点不存储数据，只有叶子节点才存储数据；
B+树是为粗盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按照键值的大小顺序存放在同一层的叶子节点，各叶子节点之间通过双向链表进行连接；

四、面试中的对比区分

1、varchar与char的区别？
char是一种固定长度的类型，varchar则是一种可变长度的类型。比如char(100)和varchar(100)，char无论字符串长短，在磁盘上，都会占用固定的100个字符大小。但是varchar，存多少就是多少，最大不能超过100；

拓展：为什么varchar建议不要超过255？
当定义varchar长度小于等于255时，长度标识位需要一个字节；
当大于255时，长度标识位需要两个字节，并且建立的索引会失效；

2、那如果varchar完全代替char可以吗？
不能，varchar更灵活，但是会额外用一个字节或者两个字节来存储长度信息，而char固定长度则节约了一个字节；其次，char存储空间都是一次性分配的，存储是固定连续的，而varchar存储的长度是可变的，当varchar更改前后数据长度不一致时，就不可避免的出现碎片的问题；需要进行碎片消除作业，也是额外的成本；

3、varchar(11)和int(11)存50，有什么区别？
varchar中代表存11个字符，而 int 只是代表显示的长度，不会影响存储空间。
int（5）实际是：00001；用来填充不足位用的；要和zerofill联合使用；

4、delete和truncate的区别 ？

• delete是删除行，truncate是整表删除。
• truncate之后，会释放空间；delete之后，不会释放空间，因为delete只是在行上标记删除，后续可以复用；
• delete是DML，会产生redo log；truncate是DDL则不会；
• truncate效率更高；
• truncate 之后，id从头开始，但是delete不会；

拓展：删除表数据后表的大小却没有变动，这是为什么？
在使用delete删除数据时，其实对应的数据行并不是真正的删除，是逻辑删除，InnoDB仅仅是将其标记成可复用的状态，所以表空间不会变小

5、主键和外键分别是什么？
主键是表中的一个或多个字段，它的值用于唯一标识表中的某一条记录；
外键是某张表b的主键，在另外一张a表中被使用，那么a中该字段的使用范围，取决于b；外键约束主要用来维护两个表之间数据的一致性；外键可以是一对一的，也可以是一对多的；

一张表一定是有主键的，即使自己不主动设置，InnoDB存储引擎会将第一列非空唯一索引的列作为主键，如果没有的话会自动生成一列为6字节的主键【PRIMARY KEY】
外键：使两张表关联，保证数据的一致性和实现一些级联操作；【FOREIGN KEY】

InnoDB存储引擎选择主键：
判断表中是否有非空的唯一索引，如果有，则该列为主键；
如果没有，InnoDB存储引擎会自动创建一个6字节大小的指针row_id作为主键

外键【FOREIGN KEY】的使用条件：
两个表必须是InnoDB表；
外键列必须建立索引；现在的版本会自动创建索引；
外键关系的两个字段的数据类型相似或者时可以相互转换的类型；比如int和tinyint可，但是int和varchar不可以；
外键可以使得两张表关联；

关于外键的操作：

现在有这两张表
CREATE TABLE `example1` (
  `stu_id` int(11) NOT NULL DEFAULT '0',
  `name` VARCHAR(11) NOT NULL DEFAULT '',
  `course_id` int(11) NOT NULL DEFAULT '0',
  `grade` float DEFAULT NULL,
  PRIMARY KEY (`stu_id`,`course_id`)
) engine=INNODB;
CREATE TABLE `example2` (
  `id` int(11) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `f_ck` (`stu_id`,`course_id`),
  CONSTRAINT `f_ck` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example1` (`stu_id`, `course_id`)
) engine=INNODB;

看的出来example2中的stu_id和courese_id是example1的外键，example1是父表，example2是字表；

此时我们去删除父表中的数据，没删成功；
delete from example1 where stu_id = 1
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`example2`, CONSTRAINT `f_ck` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example1` (`stu_id`, `course_id`))

我们先删除字表中的数据，在删除附表中的数据；发现删除成功了
delete from example2 where stu_id = 2;
delete from example1 where stu_id = 2;
> Affected rows: 1

两个表形成关联，必须子表的数据删除后，才能删除父表中的对应数据

事件触发限制：
on delete和on update，可设置参数cascade(跟随外键改动)、restrict(默认的，拒绝主表的相关操作，想删除或者更新父表的数据，先删除相关子表的数据)、set null(设空值)、set default（设置默认值）；

先删除原来的外键，然后加上新的事件触发限制
alter table example2 drop foreign key f_ck;
alter table example2 add CONSTRAINT `f_ck` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example1` (`stu_id`, `course_id`) ON DELETE CASCADE ON UPDATE CASCADE;//跟随主键改动删除或修改的时候

此时我们修改父表中的数据

update example1 set stu_id=3,course_id=3 where stu_id=1;

发现example1和example2都发生了改变；因为我们cascad,.主表中的数据发生变化，字表中的也会发生变化；

6、为什么推荐使用自增id作为主键？

• 普通索引的B+树上存放的是主键索引的值，如果该值较大，会导致普通索引的存储空间较大；
• 使用自增id做主键索引新插入数据只要放在该页的最尾端就可以，直接按照顺序插入，不用刻意维护；
• 页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，如果主键索引不为自增id,那么数据就可能从页的中间插入，页的数据会频繁的变动，导致页分裂维护成本高；

7、MyISAM与InnoDB的区别是什么？

• InnoDB支持事务,MyISAM不支持；
• InnoDB支持外键，MyISAM不支持；
• InnoDB是聚簇索引，使用B+Tree作为索引结构，数据文件是和索引绑在一起的，必须有主键；
  MyISAM是非聚簇索引，索引和数据文件时分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的；
• InnoDB不保存表的具体行数，MyISAM用一个变量保存了整个表的行数；
• InnoDB有redo log(重做日志文件)日志文件，MyISAM没有；
• InnoDB存储文件有.frm,ibd(数据和索引文件存在一起),
  而MyISAM有frm,MYD,MYI（MYD:数据文件，MYI：索引文件）；
• InnoDB支持表，行锁；而MyISAM支持表级锁；
• InnoDB必须有唯一索引，如果没有指定的话，InnoDB会自己生成一个隐藏列Row_id来充当默认主键，MyISAM可以没有；

8、InnoDB事务为什么要两阶段提交？
先写redo log后写binlog。假设在redo log洗完，binlog还没有写完的时候，MySQL进程异常重启，这时候binlog里面就没有记录这个语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同；
先写binlog后写redo log。如果在binlog写完之后crash,由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行C的值是0.但是binlog里面已经记录了“把C从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行C的值是1，与原库的值不同；

binlog是Server层的二进制日志；redo log是innodb引擎特有的日志；
binlog记录的这个语句的原始逻辑，只能用于归档，而且没有crash-safe能力；追加写入，不会覆盖以前的日志；
redo log是物理日志，记录的是“在某个数据上做了什么修改”；redo log有crash-safe能力；是循环写入的；

所以，使用两阶段提交，就是为了保证数据的一致性；

举个例子：

记录1：给id = 100这一行的age字段加1
记录2：给id = 100这一行的age字段加1
假设在记录1刷盘后，记录2未刷盘时，数据库崩溃。重启后，只通过bin log无法判断那条记录已经写入磁盘，哪条没有写入磁盘；但是redo log不一样，rodo log会将刷入磁盘的数据，都会从redo  log直接抹掉，数据库重启后，直接把redo log中的数据恢复至内存就可以了；

9、WAI是什么？有什么好处？checkpoint

“脏页” ，如果缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了；所以为了避免数据丢失的问题，提供了WAL；

WAL：（write ahead log，预写日志）
当事务提交时，先写重做日志（redo log），再修改页（先修改缓冲池，再刷新磁盘）;
当由于发生宕机而导致的数据丢失时，通过redo log来完成数据的恢复，redo log就是持久性的重要；

好处：读和写可以完全的并发执行，不会互相阻塞；

有了redo log，InnoDB就可以保证即使数据库发生了异常重启，之前提交的记录也不会丢失，这个能力称为crash-safe;

每个InnoDB存储引擎至少有1个重做日志组（redo log group），每个文件组至少有2个重做日志文件（redo log file），默认的是ib_logfile0,ib_logfile1;每个redo log file大小不一，循环写入的方式运行；

CheckPoint：
解决问题：缓冲池不够用时，将脏页刷新到磁盘；
redo log不可用时，将脏页刷新到磁盘；
缩短数据库的回复时间；

checkpoint其实就是在redo log file找到一个位置，将这个位置前的页都刷新到磁盘中去，这个位置就称为checkpoint检查点；

1、缩短数据库的恢复时间：当数据库发生宕机时，数据库不需要重做所有的日志，只需要将checkpoint点之后的redo log进行恢复就行了，这显然大大缩短了恢复的时间；
2、缓冲池不够用时，将脏页刷新到磁盘：LRU算法，
3、redo log不够用时，将脏页刷新到磁盘：循环写入的

write pos 是当前redo log记录的位置，随着不断的写入磁盘，write pos就不断的后移，写到file3之后会回到file0开头；
write pos和checkpoint之间就是redo log file还空着的部分，可以用来记录更新的操作，如果write pos追上checkpoint，就表示redo log file满了，这个时候不能再执行新的更新，得先停下来覆盖一些redo log，把checkpoint再推进一些；
两种方式：sharp checkpoint：在数据库关闭时将所有的脏页都刷新回磁盘；innodb_fast_shutdown =1 ;
fuzzy checkpoint:innodb存储引擎内部使用这种模式，只刷新一部分脏页，而不是刷新所有的脏页回磁盘。

10、聚集索引和非聚集索引
B+Tree的叶子节点存储额整行数据的是主键索引，也被称之为聚簇索引；
聚集索引就是按照每张表的主键构造一颗B+树，同时叶子结点存放的即为表中一行一行的数据；
聚集索引一般是加在主键上的；

聚簇索引和辅助索引的不同之处就是：叶子节点存放的是否是一整行的信息；
聚簇索引的叶子节点包含完整的行数据，而非聚簇索引的叶子节点存储的是每行数据的辅助索引键+该行数据对应的聚簇索引键（主键值）;

对于MyISAM无论是主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，普通索引是非聚簇索引。