首页 > 技术交流 > MySQL常见面试题汇总

MySQL常见面试题汇总

头像
心君D悦子
编辑于 2021-04-20 17:19:15 APP内打开
赞 8 | 收藏 67 | 回复2 | 浏览1173
作为一名Java程序员,对MySQL也是需要掌握的,这里罗列了我在笔面试期间总结的题目,当然很多题目都是来自于其他小伙伴的面经,这里感谢各位大家的贡献。本文章将分为多个模块,分别是基础语法、索引、锁与事物、视图、存储引擎、存储过程等方面进行题目汇总,如果有错误请指出更正,互相学习,因为我也是没工作经验的初学者,谢谢大家~!

基础语法
1、表的连接分为几类?
①内连接;②外连接;③全连接

2、三种连接有什么区别?
①内连接取得是两表的交集部分
②外连接默认取得的是单表中的所有记录,添加where条件可取的交集以外的部分。如果是左连接,则默认检索出左表的所有记录,而右表中的记录必须在左表中也有满足条件的记录才能够检索出来;右连接同理,相反。
③全连接获得的是笛卡尔积,两表连接的所有记录

3、对一个自增表插入5条数据,然后删除最后两条,重启mysql服务,这时再添加一条数据,此时id是多少?
id是4,因为自增id的值只保存到内存中,不会保存到磁盘,每次重启,都会从当前id最大值+1开始

4、数据库的三范式是什么?
第一范式:数据不可再分,比如年级班级字段,此时的表就不满足第一范式,因为数据可再分,分为年级和班级字段。
第二范式:非主键字段的数据完全函数依赖于主键。
第三范式:去掉第二范式中的传递函数依赖。

5、如何获取当前数据库版本?
①控制台直接输入mysql -V
②进入mysql,输入select version();
③进入mysql,输入status或\s

6、char 和 varchar 的区别是什么?
①char是固定长度,如果插入的数据长度小于定义的长度,则使用空格填充;varchar是可变长度,插入多长就存多长。
②存储的容量不同。对char来说,最多能存放的字符个数255,和编码无关;而对于varchar来说,最多能存放65532个字符。

7、float 和 double 的区别是什么?
float数值类型用于表示单精度浮点数值,而double数值类型用于表示双精度浮点数值。double运算速度比float慢得多,存储的空间也是float的两倍。

存储过程

1、什么是存储过程?
存储的是预编译的SQL语句,可以加快运行速度。如果某个操作需要多次执行SQL,使用存储过程效率较高

2、存储过程的优点?
①执行效率高,因为已预编译
②减少网络通信量,当对数据库进行复杂操作时,例如同时对多个表进行增删改查操作时,可以将这些复杂操作用存储过程封装起来放到数据库中,通过存储过程名直接调用,避免使用多条sql进行多次通信,而封装到一个存储过程中只需进行1次通信即可。
③安全性高,存储过程需要有一定权限的用户执行,且存储过程比多条sql稳定,只要数据库不出现问题,存储过程也不会出现什么问题
④复用性高,减少开发人员的工作量

3、如何创建存储过程
create procedure 存储过程名(参数列表)
begin
存储过程体;
end
参数列表可以使用In模式、Out模式、Inout模式

视图

1、什么是视图?
视图是将一段查询sql封装为一个虚拟的表,这个虚拟表只会保存sql逻辑,不会保存任何查询结果,也不会占据空间,所以每次进行查询时都是重新执行sql。可以将视图理解为一个函数,想要调用时直接调用即可

2、视图的作用
①封装复杂sql,提高复用性
②对数据库重构,而不影响程序的运行
③只会展现视图结果中的数据,而不会展现引用表中的数据,提高了数据安全性

3、视图的缺点?
①性能不佳,每次执行视图都会重新执行语句
②修改限制,需要对数据进行修改时,必须把视图转化为基本表进行修改,但是对于复杂的视图,可能是不可修改的

索引

1、索引是什么?
索引就是一种数据结构,能够帮助我们快速检索数据库中的数据

2、MySQL中有无hash索引?
有,memory存储引擎中就是使用的hash索引,同时innodb支持自适应hash索引

3、使用hash索引的劣势?
①范围查找不合适
②较大概率产生哈希碰撞,即哈希冲突,对哈希算法要求较高
③需要大量的存储空间

4、索引一般采用的是什么数据结构?
一般采用的是hash索引和B+tree索引

5、hash索引和B+tree索引的区别?
①hash索引适合等值查询,无法进行范围查询,而B+tree均适合
②hash索引没办法利用索引完成排序
③hash索引不支持最左匹配规则
④hash索引存在哈希碰撞问题,对哈希算法要求较高
⑤hash索引采用的是哈希表,无序;而B+tree索引有序

6、B tree和B+tree的区别?
①B树中所有节点都会存储数据行;而B+树只有叶子节点存储数据行,且所有数据均在叶子节点上
②B树中不会出现重复的key关键字;而B+树会出现重复的key关键字

7、B+树相比B树的优点?
①磁盘读写代价低:B树因为非叶子节点存放了数据行,占用了大量的存储空间;而B+树的非叶子节点不存在数据行,因此在相同大小的磁盘块中,B+树能容纳的key数量也越多,一次性读入内存进行查找的key也就越多,读写次数降低了
②查询效率更加稳定:因为只有叶子节点存放了数据行,因此不管查找任何key,都要走一条从根节点到叶子节点的路,查询效率较为稳定
③适合范围查询:B+树中所有的叶子节点使用链表相连,直接扫一遍叶子节点即可;而B树需要找到具体的数据再进行中序遍历查找,效率低下

8、B+tree的叶子节点可以存储哪些东西?
可以存储具体的数据行和主键id

9、这两个有什么区别(聚簇索引和非聚簇索引)?
如果B+tree的叶子节点存放的是数据行,则称为聚簇索引,也就是主键索引;如果B+tree的叶子节点存放的是主键id,则成为非聚簇索引,也称为非主键索引或普通索引

10、聚簇索引和非聚簇索引在查询上的区别?
聚簇索引在查询时只会进行查一次,因为整个数据行都在索引当中。非聚簇索引在查询时可能会回表,回不回表看当前已有索引是否>=查询的字段

11、什么是索引覆盖?
索引覆盖指一个查询语句的字段只用从当前索引中就能够取得,不必再从数据表中读取,即无需回表。
比如select id, name from table where name = 'zhangsan',id是主键索引,name是普通索引,我们根据name进行检索,以为name是普通索引,因此叶子节点保存的是主键id和当前的name属性,而select语句中查询的也正是id和name,因此这里的索引就覆盖了查询的数据,可以直接返回结果,无需回表查询。如果select id, name, sex; 就需要回报查询了

12、什么是回表?
与索引覆盖互逆,当一个查询语句的字段不能从索引中直接取得,那么需要从其他数据表中获取,这个过程称之为回表

13、创建联合索引时,他们之间的顺序如何选择?
①过滤性好的字段放在最前面,因为再使用后面的索引时,查询将会变少
②需要进行范围查询的字段应放在最后面,因为范围查询后面的字段索引失效
③使用较为频繁的字段尽可能往前放,这样可以避免一些不必要的索引查询

14、什么是最左前缀匹配?
在联合索引中,最左优先,从左边开始依次匹配,查询从索引的最左前列开始并不跳过索引中的列,直到匹配至范围查询的字段为止

15、MySQL5.6中,对索引做了哪些优化?
索引下推

16、什么是索引下推?
在多个条件查询中(一般在普通索引中使用),使用索引下推不会忽略其他条件,在本次查询中就过滤出来,无需再回表进行其他条件的判断,可以有效减少回表的次数,大大提升了查询的效率。
如有(name, age)索引时:
查询name like '陈%' and age > 20
如果不使用索引下推:将满足name like '陈%'的所有记录返回,再根据age的条件进行第二次回表查询
如果使用了索引下推:在判断name like '陈%'后,并没有忽略age字段,在索引内部将不满足age条件的记录直接跳过,此时就无需回表二次查询

17、什么是谓词下推?
在进行表关联时,先获取SQL语句中涉及到的字段(即select语句后面的字段),再进行表关联。这样做可以避免表的字段过多,先进行表关联会导致浪费,因为无效数据过多。采用谓词下推可以避免关联过多的字段从而造成的浪费

18、explain常用参数有哪些?
①type:可以查看查询的类型,看看是否ALL全表扫描
②possible_keys:已有的索引,列出可能用到的索引
③key:真正使用的索引
④key_len:索引长度,查看是否充分地用上了索引,越大越好
⑤rows:物理扫描的行数,越小越好
⑥Extra:额外重要信息

19、使用索引的优点
查找速度变快,降低排序成本

20、使用索引的缺点
消耗内存空间,插入删除更新速度变慢

21、索引失效的情况
①不满足最左匹配原则,比如创建的索引是a,b,c,但进行查询时where条件的顺序是b c或者a c,也就是检索条件不是从a开始,或者中间有断层,都会导致索引失效
②where筛选的条件使用了函数、计算和类型转换等
③范围查询右边的字段索引失效
④使用不等于<>
⑤使用不为空not null
⑥like查询中的首字母为通配符%,即'%zhangsan'等等,也可以理解为%也是范围查询
⑦类型不匹配,假设name字段是varchar类型的,而检索时未加引号,虽然检索能成功,但其实是全表扫描

22、mysql 索引是怎么实现的?
myisam和innodb均是采用b+索引来进行实现的。就是一颗二叉排序树,但两个索引实现也有区别。
myisam只支持非聚簇索引,即数据和索引存储在不同文件,因此对索引进行检索时,即使找到了对应的数据,这只是一个主键id,所以仍需进行回表查询,效率较低。
而innodb既支持聚簇索引也支持非聚簇索引。聚簇索引即主键索引,非聚簇索引即普通索引。聚簇索引的数据与索引是存放在一起的,因此我们根据索引进行检索时,只要找到了,就可以直接返回,因为聚簇索引保存的是数据行,不需要回表扫描;普通索引的话就是非聚簇索引,与上面的myisam实现类似,同样需要进行回表扫描,当然前提是不存在索引覆盖的情况下。

23、怎么验证 mysql 的索引是否满足需求?
可以使用explain进行sql分析,查看索引是否使用到,查看是否进行了全表扫描,还能查看一些排序是否使用了Filesort,这些情况都是需要避免的

24、使用UUID作为主键会有什么问题?
对于聚簇索引来说,主键会进行排序,但是由于uuid的无序性,每次添加可能会产生巨大的io压力,B+树可能也会频繁分裂和调整,可以将uuid设计为逻辑主键,物理主键还是使用自增id

25、为什么索引能加快查找速度?
因为索引已经为我们排好序了,查找时可以直接使用写好的二分查找进行查询,可以避免全表扫描

26、非聚簇索引与聚簇索引的非叶子节点存放的是什么?
存放的是索引key值和向下的指针

27、如何查找慢sql并优化?
开启慢查询日志slow_query_log,可以设置执行时间超过long_query_time的加入日志,系统运行一段时间后,查看日志文件中频繁出现的sql,对这些sql进行详细的优化

存储引擎

1、mysql有哪些常用的存储引擎?
myisam、innodb、memory

2、myisam和innodb的区别?
①innodb支持外键、事物和哈希索引,而myisam都不支持
②innodb默认使用的是行锁(不过只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁),适合高并发的情况下使用,但可能产生死锁;而myisam使用的是表锁,不会产生死锁情况
③innodb使用的是聚簇索引,既缓存数据,也缓存索引,并且数据和索引存储于同一文件;myisam使用的是非聚簇索引,只缓存索引,不缓存数据,并且索引和数据存储于不同的文件
④mysql自带表使用的是myisam存储引擎
⑤innodb不支持全文索引;myisam支持全文索引

锁与事物

1、隔离级别与锁的关系
①读未提交(一级封锁协议):读取数据不加共享锁,在修改的数据上加排他锁,事物结束时才释放,且其他事物不能写,但可以读(因为读不用共享锁,可随时读取),所以导致了在一个事物中读取了另一个事物未提交的数据。
②读已提交(二级封锁协议):每个快照读都会生成并获取最新的读视图Read View。但会导致不可重复读:在一个事物中,对读取的数据加了共享锁,一旦读完该行,立刻释放共享锁锁,如果此时有其他事物对数据进行了修改,那么再次读取则会出现在一个事物中多次读取的数据不一致,也就是不可重复读。
③可重复读:第一次读取数据的快照读会生成一个读视图read view,以后每次简单查询时都使用此快照,所以保证了可重复读(在一个事物中,无论何时读取,结果总是一样的)。但可重复读会导致幻读。幻读指的是在进行范围查询时,前后读取到的记录数不一样,在A事物未完成前,B事物新增了数据,因为B事物可以使用当前读,在新增的数据上加排它锁进行写数据,并释放排它锁,再当A事物进行读时,会读取到新增的数据。
④串行化:对表直接加上表锁,一个事物操作表时,另一个事物不能读也不能写。
扩展:
①一级封锁协议:在要修改的数据上加排它锁,直到事物结束后才释放。
②二级封锁协议:在一级封锁协议的基础上,读取数据需加共享锁,读完后立即释放
③三级封锁协议:在一级封锁协议的基础上,读取数据需加共享锁,直到事物结束后才释放。
快照读:(前提是非串行化级别,因为串行级别下的快照度会变为当前读)读取的是历史数据,因此可能是过期的数据,不用加锁。像不加锁的select操作就是快照读,即不加锁的非阻塞读
当前读:读取的是记录数据的最新版本,并且当前读返回的记录都会加锁(排它锁),保证其他事物不会再并发的修改这条记录。像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读

2、乐观锁与悲观锁是什么?如何实现?
悲观锁:适用于写操作较多的场景。具有强烈的独占和排他特性,不管是读操作还是写操作,均将要操作的记录加排它锁,不允许其他事物进行读和写。适用于读较多的场景
乐观锁:适用于读操作较多的场景。每次拿数据的时候认为别人不会修改数据,所以不会上锁,但在提交更新的时候会判断一下在此期间别人有无修改这个数据,如果修改了,则返回错误的信息,进行相应的处理。
悲观锁的实现主要是读锁和写锁。
读锁:myisam引擎下使用lock table tablename read;进行加读锁,使用unlock tables;进行读锁释放;innodb引擎下可以在select语句最后加上lock in share mode来加意向共享锁(读锁)。
写锁:myisam引擎下使用lock table tablename write;进行加写锁,使用unlock tables;进行读锁释放;innodb引擎下可以在select语句最后加上for update来加意向排它锁(写锁)
乐观锁的实现:
①version方式(添加版本号):在数据表中加上一个数据版本号version字段,当数据被修改时,version值加一。在提交更新的时候,会检查当前version与刚才读到的version值是否相等,如果相等才会更新,否则重试更新操作,知道相等为止。
②时间戳方式:在数据表中加上一个时间戳字段,与version方式类似,当数据被修改时,时间戳更新。在提交更新时,检查当前时间戳与刚才读到的时间戳是否相等,如果相等才更新,否则重试。

3、解释一下记录锁、间隙锁、临键锁?
这三个锁都是行锁。
如果想update一个含有索引的字段进行where精确检索(也可以使用select的for update加锁),那么会对这个行记录上加记录锁record lock;
如果进行where范围检索,则会将该范围中的所有不存在的行记录上加间隙锁;
而临键锁(next-key lock)是记录锁和临键锁的合体,假设查询0<id<10的记录,而数据表中只有2, 3, 5, 8记录,因此这2, 3, 5, 8记录将会加记录锁,而1, 4, 6, 7, 9则会加临键锁。如果这是一个更新操作(也就是加的写锁),那么在锁释放前,尽管操作数据表中没有的字段如1, 4, 6, 7, 9这些,均会被阻塞

4、说一下 mysql 的行锁和表锁?
myisam只支持表锁,每次进行查询,都会加表锁,也就是对整张表进行加锁,其他客户端不能对其再做任何操作,只有等当前锁释放。
innodb支持行锁,也支持表锁,默认是行锁。当对一个数据进行操作时,只会对该行进行上锁,其他行是没有任何影响的,其他事物能操作其他行。但前提是进行检索时用到了索引,如果未使用索引,则会进行全表扫描,意味着此时加的是表锁。如果索引失效了,也会将行锁升级为表锁。

5、什么是间隙锁?
当进行索引字段的范围筛选查询时,例如id<5,而此时的字段只有1,2,4,那么innodb不仅仅会对1,2,4锁对应的行加上行锁,也会对0,3对应的行也加上行锁,因此其他事物对0,3行数据进行操作时,也会进入阻塞状态。

6、innodb中有哪些锁?
读锁(共享锁)、写锁(排它锁)、意向锁和MDL锁
行锁的种类:行记录锁(record lock)、间隙锁(gap lock)、临键锁(next-key lock,也就是行记录锁与间隙锁的组合)

7、介绍一下事物的四大特性?
①原子性:事物是最小的执行单位,不允许分割,确保该动作要么全部完成,要么都不完成
②一致性:执行事物前后,数据要保持一致,多个事物对同一个数据的读取结果是相同的
③隔离性:并发访问数据库时,一个用户的事物不被其他事物所干扰,各个事物之间是独立的
④持久性:一个事物提交后,对数据库的改变是永久性的

8、事物的隔离级别有哪些?并且分别会导致什么现象?
①读未提交:导致脏读。读取了未提交的数据。
②读已提交:解决了脏读,导致不可重复读。在一个事务中,两次的查询结果不一致,有另一个事物对数据进行了修改,并提交了。
③可重复度:解决了不可重复读。导致幻读。在一个事物中,两次查询中数据的笔数不一致,有另一个事物插入了新的数据,导致本事物会发现查询到了新插入的数据,仿佛出现了幻觉。
④串行化:最高的事物隔离级别,所有事物串行执行,各个事物之间不会产生干扰,完全符合ACID。

9、MySQL默认隔离级别是什么?
可重复读

10、Oracle默认隔离级别是什么?
读已提交

11、可重复读隔离级别下是如何解决幻读问题的?
快照读可以解决部分幻读问题,也就是在第一次select时生成一个快照,以后每次查询都使用此快照,也就是使用MVCC多版本控制来避免幻读的;
如果是当前读,可以通过临键锁或间隙锁来避免幻读,也就是对范围查询的间隙加间隙锁,当想要对间隙进行插入数据时,会阻塞,因此一定程度上也避免了幻读。












2条回帖

回帖
加载中...
话题 回帖

相关热帖

技术交流近期热帖

近期精华帖

热门推荐