笔记：MySQL面试金典

MySQL面试金典

MySQL基础面试题总结

MySQL执行一条语句的内部执行过程

1. 建立连接：客户端先通过连接器连接到MySQL服务器

2. 查缓存：查询缓存中是否有执行过此语句，有则直接返回缓存数据，没有则进入分析器

3. 分析器分析：对查询语句进行语法分析和词法分析，判断SQL语言的正确性，如果语句不正确则直接返回给客户端错误信息，如果正确则进入优化器

4. 优化器优化语句：对查询语句进行优化处理（比如：一个表里面有多个索引，优化器会判别哪个索引性能更好，然后调整查询条件）

5. 执行器执行：执行语句并进行查询对比，直到查询到满足条件的所有数据，然后返回数据结果

MySQL提示“不存在此列”是执行到哪个节点报出的

此错误是执行到分析器阶段报出的，MySQL会在分析器阶段检查SQL语句的正确性

MySQL查询缓存功能的优缺点

MySQL查询缓存功能是在连接器之后发生的

优点：效率高

缺点：失效频繁导致缓存命中率较低；任何更新表操作都会清空查询缓存

如何关闭MySQL的查询缓存功能

MySQL查询缓存默认是开启的，配置querycachetype=DEMAND关闭查询缓存。

在MySQL8.0之后直接删除了查询缓存功能

MySQL常用引擎都有哪些

MySQL常用引擎有InnoDB、MyISAM、Memory等，从MySQL5.5.5版本开始默认存储引擎是InnoDB

MySQL可以针对表级别设置数据库引擎吗？怎么设置？

可以针对不同的表设置不同的引擎。在 create table 语句中使用 engine=引擎名（比如Memory）来设置此表的存储引擎。完整代码如下：

create table student(
   id int primary key auto_increment,
   username varchar(120),
   age int
) ENGINE=Memory

常用的存储引擎InnoDB和MyISAM有什么区别

他们的最大区别是InnoDB支持事务，MyISAM不支持事务；主要区别有：

• InnoDB 支持崩溃后安全恢复，MyISAM 不支持崩溃后安全恢复；

• InnoDB 支持行级锁，MyISAM 不支持行级锁，只支持到表锁；

• InnoDB 支持外键，MyISAM 不支持外键；

• InnoDB 主键查询性能高于 MyISAM；

• MyISAM 性能比 InnoDB 高；

• MyISAM 支持 FULLTEXT 类型的全文索引，InnoDB 不支持 FULLTEXT 类型的全文索引，但是InnoDB 可以使用 sphinx 插件支持全文索引，并且效果更好

InnoDB有哪些特性

插入缓冲（insert buffer）

对于非聚集索引（索引项顺序存储，但索引项对应的内容却是随机存储的）的插入和更新，不是每一次直接插入索引页中，而是首先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入，否则先放入一个插入缓冲区中。

就好像欺骗数据这个非聚集的索引已经插入到叶子节点了，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常将多个插入合并到一个操作中，这就大大提高了对非聚集索引执行插入和修改操作的性能。

注：非聚集索引和聚集索引是什么可参考https://zhuanlan.zhihu.com/p/39293940

两次写（double write）

两次写给InnoDB带来的是可靠性，主要用来解决部分写失败（partial page write）。

两次写由两部分组成：一是内存中的doublewrite buffer，大小为2M；二是物理磁盘上的共享表空间中连续的128个页（即两个区），大小为2M。

当缓冲池的作业刷新时，并不直接写硬盘，而是通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次写，每次写入1M到共享空间表的物理磁盘，然后马上调用fsync函数，同步磁盘。如下图所示：

自适应哈希索引（adaptive hash index）

由于InnoDB不支持hash索引，但在某些情况下hash索引的效率很高，于是出现了自适应哈希索引功能，InnoDB存储引擎会监控对表上索引的查找，如果观察到建立hash索引可以提高性能的时候，则自动建立hash索引。

一张自增表中有三条数据，删除了两条数据之后重启数据库，再增加一条数据A，此数据A的ID是几？

对于InnoDB引擎ID=2（MySQL8版本之前），对于MyISAM引擎ID=4

MySQL中什么情况会导致自增主键不能连续

1. 唯一主键冲突会导致自增主键不连续

2. 事务回滚也会导致自增主键不连续

InnoDB中自增主键能不能被持久化

自增主键能不能被持久化指的是MySQL重启之后InnoDB能不能恢复重启之间的自增列。

在MySQL8.0之后InnoDB把自增主键保存到redo log中，当MySQL重启之后就会从redo log日志中恢复。（在MySQL8.0之前InnoDB是不支持持久化的）

独立表空间和共享表空间及其区别

独立表空间：每一个表都会生成以独立的文件方式来进行存储。

共享表空间：指的是数据库的所有的表数据，索引问价你全部放在一个文件夹，默认这个共享表空间的文件路径在data目录下。

区别：对于共享表来说，即使表删除了，空间也不会删除，所以表依然很大，而独立表空间如果删除表就会清除空间。

如何设置独立表空间

通过设置参数innodbfileper_table=ON来设置独立表空间，从MySQL5.6.6版本之后，该值默认是ON

如何进行表空间收缩

使用重建表可以收缩表空间，重建表有以下三种方式：

1. alter table t engine=InnoDB

2. optmize table t

3. truncate table t

重建表的执行流程

1. 建立一个临时文件，扫描表t主键的所有数据页

2. 用数据页中表t记录生成的B+树，存储到临时文件中

3. 生成临时文件的过程中，将所有对t的操作记录在一个日志文件（row log）中

4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表t相同的数据文件

5. 用临时文件替换表t的数据文件

表的结构信息存在哪里

表结构定义占有的存储空间比较小，在MySQL8之前，表结构的定义信息存在以.frm为后缀的文件里，在MySQL8之后，则允许把表结构的定义信息存在系统数据表之中。

什么是索引覆盖

指的是索引上的信息足够满足查询请求，不需要再回到主键上取数据。

如果把一个InnoDB表的主键删除，是不是没有主键，就没有办法进行回表查询了？

可以回表查询，如果把主键删掉，那么InnoDB会自己生成一个长度为6字节的rowid作为主键

（注意：回表查询指的是先通过普通索引的值定位聚集索引值，再通过聚集索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低）

执行一个update语句之后，再去执行hexdump命令直接查看ibd文件内容，为什么没有看到数据有改变呢

可能是因为update语句执行完成后，InnoDB只保证写完了redo log、内存，可能还没来得及将数据写到磁盘

内存表和临时表有什么区别

内存表：指的是使用memory引擎的表，数据（存储在内存中）会在系统重启的时候被清空，但是表结构还在

临时表：可以使用各种引擎类型。如果使用的引擎是InnoDB或MyISAM引擎的临时表，写数据的时候是写到磁盘上的。

并发事务会带来哪些问题

修改丢失、脏读、不可重复读、幻读

什么是脏读和幻读

脏读：指的是一个事务在处理过程中读取了另一个事务未提交的数据

幻读：指的是同一个事务内多次查询返回的结果集不一样

为什么会出现幻读？幻读会带来什么问题？

行锁只能锁定存在的行，对于新插入的操作没有限定，所以就有可能产生幻读。

幻读带来的问题如下：

1. 对行锁语义的破坏

2. 破坏了数据一致性

如何避免幻读

使用间隙锁的方式来避免出现幻读。

间隙锁用于锁住行与行之间的间隙，能够阻塞新插入的操作。

间隙锁的引入会产生一些新的问题：比如降低并发，可能导致死锁等

如何查看MySQL的空闲连接

在MySQL的命令行中使用show processlist查看所以连接，其中Command列显示为Sleep的表示空闲连接。

MySQL的字符串类型和取值有哪些

类型	取值范围
CHAR(N)	0~255
VARCHAR(N)	0~65536
TINYBLOB	0~255
BLOB	0~65535
MEDUIMBLOB	0~167772150
LONGBLOB	0~4294967295
TINYTEXT	0~255
TEXT	0~65535
MEDIUMTEXT	0~167772150
LONGTEXT	0~4294967295
VARBINARY(N)	0~N个字节的变长字节字符集
BINARY(N)	0~N个字节的定长字节字符集

varchar和char的区别是什么？分别适用于哪些场景？

区别：varchar的长度是可变的，而char是固定长度；varchar可能会造成存储碎片。

char适用于长度较短的字段和固定长度的字段（如：身份证号、手机号等），反之则适用varchar

MySQL存储金额应该使用哪种类型？为什么？

MySQL存储金额应该使用decimal，因为使用其他数据类型（如float）有精度问题

limit 3, 2的含义是什么？

从第3条数据后（第4条数据开始），查找2条数据（查找到第4、5条数据）

now()和current_data()有什么区别

now()返回的是当前时间（包含日期和时分秒），current_date()返回当前日期

如何去重计算总条数

使用distinct去重，使用count统计总条数，具体实现脚本如下:

select count(distinct f) from t

lastinsertid函数功能是什么？有什么特点？

功能：用于查询最后一次自增表的编号

特点：查询时不需要指定表名（使用select last_insert_id()即可查询）

注意：lastinsertid()始终以最后一条自增编号为主，各表的自增ID都能够覆盖这个值（比如A表的最大编号为10，lastinsertid()查询出来的值为10，这时表B插入了一条数据，它的最大编号为3，这个时候lastinsertid()查询出来的值就是3）

删除表的数据有几种方式？他们有什么区别？

删除数据有两种方式：delete和truncate

脚本使用示例：

delete from t where username="redis"

tunrcate table t

区别：

1. delete可以添加where条件删除部分数据，truncate不能添加where条件只能删除整张表

2. delete的删除信息会在MySQL日志中记录，而truncate的删除信息不被记录在MySQL的日志中，因此delete的信息可以被找回而truncate的信息无法被找回；

3. truncate因为不记录日志所以执行效率比delete快

MySQL中支持几种模糊查询？它们有什么区别？

MySQL中支持两种模糊查询：regexp和like。like是对任意多字符匹配或任意单字符进行模糊匹配；regexp则支持正则表达式的匹配方式，功能比lile丰富。

使用示例：

select * from person where user_name like "%SQL%"

select from person where user_name regexp ".SQL*."

MySQL支持枚举吗？如何实现？它的用途是什么？

支持枚举，可以通过如下方式实现

create table t(
    sex enum('boy','grid') default 'unknown'
);

枚举的作用：预定义结果值，当插入数据不在枚举值范围内，则插入失败，提示错误truncated from column 'xxx' at row n

count(column)和count(*)有什么区别

count(column)统计不会统计列值为null的数据，而count()则会统计所有信息，所以最终的统计结果可能会不同。

为什么InnoDB不把总条数记录下来，查询的时候直接返回呢

因为InnoDB使用了事务实现，而事务的设计使用了多版本并发控制，即使在同一时间进行查询，得到的结果也可能不同，所以InnoDB不能把结果直接保存下来，因为这样是不准确的。

能否使用show table status中的表行数作为表的总行数直接使用？为什么？

不能，因为show table status中的表行数是通过采样统计估算出来的，官方文档说误差可能在40%左右。

InnoDB 和 MyISAM 执行 select count(*) from t，哪个效率更高？为什么？

MyISAM 效率最高，因为 MyISAM 内部维护了一个计数器，直接返回总条数，而 InnoDB 要逐行统计。

在MySQL中有对count(*)做优化吗？做了哪些优化？

count()在不同的MySQL引擎中的实现方式是不同的，在*没有where条件的情况下：

MyISAM引擎会把表的总行数存储在磁盘上，因此count(*)的时候会直接返回这个行数，执行效率很高

InnoDB引擎中count(*)就比较麻烦了，需要把数据一行一行的从引擎中读出来，然后累计基数（在InnoDB中，MySQL优化器会找到最小的那颗索引树来遍历，这样就能在保证逻辑正确的前提下，尽量少的扫描数据，从而优化了count()的执行效率）

在InnoDB引擎中count(*)、count(1)、count(主键)、count(字段)哪个性能更高？

count(字段)<count(主键 id)<count(1)≈count(*)

对于count(*)来说，并不会把全部字段取出来，而是专门做了优化，不取值，直接按行累加

对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字"1"进去，判断不为空则按行累加

对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的ID都取出来，返回给server层。server层拿到id后，判断不为空则按行累加

对于count(字段)来说，如果这个字段是定义为not null的话，一行行地从记录里面读出这个字段，如果定义不能null则直接按行累加，如果字段定义允许为null，则需要把值出，如果值不为null则按行累加

MySQL中内连接、左连接、右连接右什么区别

内连接 把匹配的关联数据显示出来

左连接 把左边的表全部显示出来，右边的表显示出符合条件的数据

右连接 把右边的表全部显示出来，左边的表显示出符合条件的数据

什么是视图？如何创建视图？

视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增、改、查操作。视图通常是一个表或者多个表的行或列的子集。视图创建脚本如下：

create view vname as
select column_names
from table_name
where condition

视图有哪些优点

1. 相对于多表查询来说，获取数据更容易

2. 视图能够对机密数据提供安全保护

3. 视图的修改不会影响基本表，提供了独立的操作单元，比较轻量

MySQL中"视图"的概念有几个？分别代表什么含义？

在MySQL中"视图"概念有两个：

1. MySQL中的普通视图（也就是我们最常用的view），它的查询和普通表一样

2. InnoDB实现的MVCC（Multi-Version Concurrency Control）多版本并发控制时用到的一致性读视图，它没有物理结构。（注意：含义待补充）

使用delete误删数据怎么找回

可以用Flashback工具通过闪回把数据恢复过来

Flashback恢复数据的原理是什么

原理是修改binlog的内容，拿回原库重放，从而实现数据找回