Mysql面试题汇总

本文最后更新于2020年6月3日,可能由于时间等因素导致内容失效,请自行辨别或联系作者。

[wm_toggle_box]

[wm_toggle_item title="前言" active="true"]

我不建议初学者直接看此面试题汇总,而是在有一定基础的前提下,把面试题当作一种简化、回顾。适合在面试之前,对照题目过一遍,如果有不了解的知识点,再详细学习。此汇总,只针对我个人的现状,肯定有取舍,会不完整,同时可能也存在错误的地方。共汇总39题。

[/wm_toggle_item]

[wm_toggle_item title="Mysql架构"]

Mysql的构成?

Server层:主要包括连接器、查询缓存(8.0后被删除了)、分析器、优化器、执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图,函数等,还有一个通用的日志模块 (bin log 归档日志)。

存储引擎:主要负责数据的存储和读取,采用可以替换的插件式架构,支持 InnoDB、MyISAM、Memory 等多个存储引擎,其中 InnoDB 引擎有自有的日志模块 redo log 模块。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

Server 层基本组件的构成及作用?

连接器:身份认证和权限相关的功能

查询缓存:(MySQL 8.0 版本后移除)

分析器:

    • 第一步,词法分析,一条 SQL 语句有多个字符串组成,首先要提取关键字,比如 select,提出查询的表,提出字段名,提出查询条件等等。做完这些操作后,就会进入第二步。
    • 第二步,语法分析,主要就是判断你输入的 sql 是否正确,是否符合 MySQL 的语法。

优化器:按它认为的最优的执行方案去执行

执行器:执行sql,执行前会校验该用户有没有权限,如果没有权限,就会返回错误信息,如果有权限,就会去调用引擎的接口,返回接口执行的结果。

一条SQL在Mysql中的执行过程?

查询过程:权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎

更新(insert、update、delete)过程:分析器—-》权限校验—-》执行器—》引擎—》redo log prepare—》binlog—》redo log commit

具体可参考:一条SQL语句在MySQL中如何执行的

[/wm_toggle_item]

[wm_toggle_item title="存储引擎"]

InnoDB与MyISAM的区别?

InnoDB 支持事务,MyISAM 不支持事务。

InnoDB 支持外键,而 MyISAM 不支持。

InnoDB 是聚簇索引,MyISAM 是非聚簇索引。

InnoDB 不保存表的具体行数,执行select count(*) 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。

InnoDB 最小的锁粒度是行锁,MyISAM 最小的锁粒度是表锁。

一些命令
-- 查看支持的存储引擎
SHOW ENGINES

-- 准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'life_users'

[/wm_toggle_item]

[wm_toggle_item title="数据类型"]

CHAR 和 VARCHAR 的异同?

相同:

char(n),varchar(n)中的n都代表字符的个数

超过char,varchar最大长度n的限制后,字符串会被截断

不同:

char不论实际存储的字符数都会占用n个字符的空间,而varchar只会占用实际字符应该占用的字节空间加1(实际长度length,0<=length<255)或加2(length>255)。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度(如果列声明长度大于255则使用两个字节来保存长度)。

能存储的最大空间限制不一样:char的存储上限为255字节

char在存储时会截断尾部的空格,而varchar不会

[/wm_toggle_item]

[wm_toggle_item title="索引"]

索引是什么?

索引是帮助MySQL高效获取数据的数据结构。(索引是在存储引擎层面实现的。不是所有的存储引擎都支持所有的索引类型。)

为什么要使用索引?

通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

可以大大加快 数据的检索速度(大大减少的检索的数据量),  这也是创建索引的最主要的原因。

帮助服务器避免排序和临时表。

将随机IO变为顺序IO。

数据库索引分成几类?具体讲一下

从索引的实现上,分为:聚集索引与非聚集索引

聚集索引:索引中的数据物理存放地址和索引的顺序是一致的,就像根据拼音查字典.将数据存储与索引放到了一块,找到索引也就找到了数据。聚集索引一个表只能有一个。

非聚集索引:索引的逻辑顺序和实际存储的物理顺序没有任何联系,就像根据偏旁查字典.数据存储和索引分开放,索引结构的叶子节点指向了数据的对应行。

从应用(逻辑)层次来分:普通索引,唯一索引,复合索引、主键索引

普通索引:最基本的索引,没有任何限制

唯一索引:用来建立索引的列的值必须是唯一的,允许空值

复合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并

主键索引:主键索引是一种特殊的唯一索引,不允许有空值

从存储结构上来划分:BTree索引(B-Tree或B+Tree索引),Hash索引,full-index全文索引,R-Tree索引等

创建索引的两种方式?
create index indexName on tableName(columnName(length))

alter table 'tableName' add index indexName('column')
为什么索引结构默认使用B+Tree,而不是Hash,二叉树,红黑树?

B-tree:因为B树不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出),指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低;

Hash:

虽然可以快速定位,但是没有顺序,不支持范围查询;

没有办法利用索引完成排序;不支持多列联合索引的最左前缀匹配规则;

如果有大量重复键值的情况下,哈希索引的效率会很低

二叉树:树的高度不均匀,不能自平衡,查找效率跟数据有关(树的高度),并且IO代价高。

红黑树:树的高度随着数据量增加而增加,IO代价高。

Mysql中B+树的一个节点大小为多大?

1页。“页”是mysql自定义的单位,innodb引擎一页默认的大小是16K。

B+ Tree的叶子节点都可以存哪些东西?

InnoDB的B+ Tree可能存储的是整行数据,也有可能是主键的值。

那这两者有什么区别吗?

在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引。

聚簇索引和非聚簇索引,在查询数据的时候有区别吗?

有,聚簇索引查询会更快。因为聚簇索引数据存储与索引放到了一块,找到索引也就找到了数据。而非聚簇索引则还需要进行一次回表的操作。当然,如果是通过覆盖索引的话,也可以只查询一次,不需要回表。

在创建索引的时候都会考虑哪些因素?

索引并不是越多越好,要根据查询,有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是使用了索引还是全表扫描。

应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描。

值分布很稀少的字段不适合建索引,例如“性别”这种只有两三个值的字段。

什么是最左前缀匹配?

在创建多列索引时,根据业务需求,where子句中使用最频繁的一列放在最左边,因为MySQL索引查询会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候,如(key1,key2,key3),相当于创建了(key1)、(key1,key2)和(key1,key2,key3)三个索引,这就是最左匹配原则。

什么情况下,创建了索引,但不走索引查询?

查询条件使用函数在索隐列上面计算

由于统计的失误,导致系统没有走索引,而是走了全表扫描

like是以%开始时

使用NOT IN和<>操作

联合索引查询条件不符合最左匹配原则

数据类型出现隐式转换等等

为什么非主键索引结构叶子节点存储的是主键值?

保证数据一致性和节省存储空间。

[/wm_toggle_item]

[wm_toggle_item title="SQL"]

count(*) 、count(1) 和 count(列名) 的区别?

执行效果上:

count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL

count(1)包括了所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL

count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空的计数,即某个字段值为NULL时,不统计

执行效率上:

列名为主键,count(列名)会比count(1)快

列名不为主键,count(1)会比count(列名)快

如果表多个列并且没有主键,则 count(1) 的执行效率优于 count(*)

如果有主键,则 select count(主键)的执行效率是最优的

如果表只有一个字段,则 select count(*) 最优

MySQL中 in 和 exists 的区别?怎么选择?

in:in查询相当于多个or条件的叠加

exists:exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录;反之,如果exists里的条件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为false

如果查询的两个表大小相当,那么用in和exists差别不大。

如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。

UNION和UNION ALL的区别?

UNION在进行表连接后会筛选掉重复的数据记录(效率较低),而UNION ALL则不会去掉重复的数据记录

UNION会按照字段的顺序进行排序,而UNION ALL只是简单的将两个结果合并就返回

[/wm_toggle_item]

[wm_toggle_item title="事务"]

什么是事务?

事务是逻辑上的一组操作,要么都执行,要么都不执行。

数据库事务的几个特性?

原子性:事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用

一致性:在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏

隔离性:一个事务的执行不能其它事务干扰

持久性

并发事务带来哪些问题?

脏读:假设事务A正在访问数据库并对数据做了修改(UPDATE),而还没有进行提交(COMMIT),此时,事务B也访问了这个数据,并使用了这个数据,那这个数据就是“脏数据”(事务A UPDATE之前的旧的数据)。

不可重复读:即:一个事务内两次读到的数据是不一样的。

幻读:假设事务A读取了几行数据,接着另外一个事务B插入了一条数据,在随后的查询中,事务A就会发现,多了一些原本不存在的记录。

丢失修改:例如:事务A读取某表中的数据C=20,事务B也读取C=20,事务A修改C=C-1,事务B也修改C=C-1,最终结果C=19,事务A的修改被丢失。

幻读和不可重复读的区别?

不可重复读的重点是修改;幻读的重点在于新增或者删除

数据库的几种事务隔离级别?各级别解决/出现了什么问题?

读未提交:最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。

读提交:允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。

重复读(MySQL InnoDB 存储引擎的默认支持的隔离级别):可以阻止脏读和不可重复读,但幻读仍有可能发生。

序列化:都能解决,但是消耗大,一般不采取。

具体可参考:腾讯面试:一条SQL语句执行得很慢的原因有哪些?—不看后悔系列

MVCC是什么?底层是如何实现的?

MVCC:多版本并发控制。MVCC是在并发访问数据库时,通过对数据做多版本管理,避免因为写锁的阻塞而造成读数据的并发阻塞问题。

InnoDB 的 MVCC,是通过在每行记录后面保存两个隐藏的列来实现。这两个列,一个保存了行的创建时间,一个保存行的过期时间(删除时间)。当然存储的并不是真实的时间,而是系统版本号(system version number)。每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。

系统版本号:是一个递增的数字,每开始一个新的事务,系统版本号就会自动递增。
事务版本号:事务开始时的系统版本号。
创建版本号:创建一个数据行的快照时的系统版本号;
删除版本号:如果该快照的删除版本号大于当前事务版本号表示该快照有效,否则表示该快照已经被删除了。

事务是如何通过日志来实现的?

事务的隔离性是通过锁实现,而事务的原子性、一致性和持久性则是通过事务日志实现 。

事务日志包括:重做日志redo和回滚日志undo。

redo log(重做日志) 实现持久化和原子性

在innoDB的存储引擎中,事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。

在系统启动的时候,就已经为redo log分配了一块连续的存储空间,以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。所有的事务共享redo log的存储空间,它们的Redo Log按语句的执行顺序,依次交替的记录在一起。

事务开启时,事务中的操作,都会先写入存储引擎的日志缓冲中,在事务提交之前,这些缓冲的日志都需要提前刷新到磁盘上持久化,也就是“日志先行”。

当事务提交之后,在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。

此时如果数据库崩溃或者宕机,那么当系统重启进行恢复时,就可以根据redo log中记录的日志,把数据库恢复到崩溃前的一个状态。未完成的事务,可以继续提交,也可以选择回滚。

undo log(回滚日志) 实现一致性

undo log 主要为事务的回滚服务。在事务执行的过程中,除了记录redo log,还会记录一定量的undo log。

undo log记录了数据在每个操作前的状态,如果事务执行过程中需要回滚,就可以根据undo log进行回滚操作。

单个事务的回滚,只会回滚当前事务做的操作,并不会影响到其他的事务做的操作。

二种日志均可以视为一种恢复操作,redo_log是恢复提交事务修改的页操作,而undo_log是回滚行记录到特定版本。二者记录的内容也不同,redo_log是物理日志,记录页的物理修改操作,而undo_log是逻辑日志,根据每行记录进行记录。

[/wm_toggle_item]

[wm_toggle_item title="锁机制"]

Mysql中有哪几种锁?

从对数据操作的类型分类:共享锁(读锁)、排他锁(写锁)

从对数据操作的粒度分类:表级锁、页面锁、行级锁

共享锁和排他锁?

共享锁(S):又称为读锁。若事务T对数据对象A加上S锁,则事务T只能读A;其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这就保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

排他锁(X):又称为写锁。若事务T对数据对象A加上X锁,则只允许T读取和修改A,其它任何事务都不能再对A加任何类型的锁,直到T释放A上的锁。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。

悲观锁和乐观锁?

乐观锁会“乐观地”假定大概率不会发生并发更新冲突,访问、处理数据过程中不加锁,只在更新数据时再根据版本号或时间戳判断是否有冲突,有则处理,无则提交事务。用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式。

悲观锁会“悲观地”假定大概率会发生并发更新冲突,访问、处理数据前就加排他锁,在整个数据处理过程中锁定数据,事务提交或回滚后才释放锁。另外与乐观锁相对应的,悲观锁是由数据库自己实现了的,要用的时候,我们直接调用数据库的相关语句就可以了。

[/wm_toggle_item]

[wm_toggle_item title="性能优化"]

大表/宽表如何优化?

限制查询范围,避免全表查询;

读写分离;

垂直拆分表:指数据表列的拆分,把一张列比较多的表拆分为多张表。【优点】可以使得列数据变小,在查询时减少读取的Block数,减少I/O次数。此外,垂直分区可以简化表的结构,易于维护。【缺点】主键会出现冗余,需要管理冗余列,并会引起Join操作,可以通过在应用层进行Join来解决。此外,垂直分区会让事务变得更加复杂;

水平分区:保持数据表结构不变,通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中。

一条SQL语句执行得很慢的原因有哪些?

大多数情况下很正常,偶尔很慢,则有如下原因

数据库在刷新脏页,例如 redo log 写满了需要同步到磁盘。

执行的时候,遇到锁,如表锁、行锁。

这条 SQL 语句一直执行的很慢,则有如下原因

没有用上索引:例如该字段没有索引;由于对字段进行运算、函数操作导致无法用索引。

数据库选错了索引。

[/wm_toggle_item]

[wm_toggle_item title="其他问题"]

什么是回表?

回到主键索引树搜索的过程

什么是覆盖索引?

select的数据列只要从索引中就能获取到

什么是延迟关联?

通过覆盖索引返回需要的主键,再根据主键关联原表

一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ?

如果表的类型是MyISAM,那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中。

如果表的类型是InnoDB,那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中。

为什么一定要设一个主键?

不设置的话,innodb也会生成隐藏的列,作为自增主键,反正都会生成,还不如设置一个,还能利用主键索引,提高查询效率。

为什么主键推荐自增而不是UUID?

innodb 中的主键是聚簇索引。如果主键是自增的,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如果不是自增主键,那么可能会在中间插入,就会引发页的分裂,产生很多表碎片。

UUID是字符串,比整型消耗更多的存储空间。

在B+树中进行查找时需要跟经过的节点值比较大小,整型数据的比较运算比字符串更快速。

[/wm_toggle_item]

[wm_toggle_item title="参考"]

mysql的聚簇索引和非聚簇索引

Mysql索引面试题

InnoDB事务实现

MySQL 三万字精华总结 + 面试100 问,吊打面试官绰绰有余

[/wm_toggle_item]

[/wm_toggle_box]

为TA充电
共{{data.count}}人
人已赞赏
数据结构编程语言

数据结构之链表

2020-5-26 12:01:35

Java基础编程语言

HashMap源码解析与面试问题汇总

2020-5-28 17:37:29

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
今日签到
有新私信 私信列表
搜索