MySQL面试前准备

发布于 2022-4-22 21:48
浏览
0收藏

作者 |花花和Java
来源 | 花花和Java(ID:gh_02ed7983d1ec)

1 MySQL事务特性是什么?怎样实现ACID特性?

MySQL事务指:一组操作要么同时成功要同时失败。所以事务四大特性:一致性、原子性、持久性、隔离性。

事务原子性:

MySQL中含有undo log日志又叫做回滚日志。用于记录数据变化的逻辑。当系统发送错误或者rollback时候,根据undo log更改为原来未修改的数据。

持久性:

在数据库中数据存储在磁盘中,如果每次读写都操作磁盘效率很低。InnoDB中含有缓存,每次查询时候先在查缓存,查询到数据返回,查询不到数据去数据库查。每次更新数据,先更新缓存,然后缓存按照checkpoint规则刷新到数据库中。MySQL中含有redo log,将更改后的记录到redo log缓存中,当提交事务后,将redo log缓存异步刷新到redo log磁盘中。即使内存的数据没有同步到数据库,或者同步数据一半数据库宕机。在重启后利用redo log恢复新的数据。

隔离性:

MySQL的隔离级有,读未提交、读已提交、可重复读。实现是通过锁机制+mvcc来实现。读未提交数据:串行化:select在无索引对全表加锁,主键索引对行加记录锁,联合索引在范围加锁(间隙锁),读已提交数据(RC):是通过mvcc(并发版本控制)来实现,read view每次快照读都会创建read view。可重复读:是通过mvcc(并发版本控制)来实现,read view每次快照读会使用第一次创建的read view。

一致性:

一致性是事务最终目的,前面的原子性、隔离性、永久性保证了数据库一致性的实现。

2 MySQL的事务隔离级别有哪些?解决什么问题?默认隔离级别是什么?

  • 读未提交:读取到未提交事务数据。会产生脏读、不可重复读;
  • 幻读 读已提交: 读取到别人已经提交数据。产生不可重复读、幻读 ;
  • 可重复读:在一次事务中,两次相同的读取。读取不到别人修改已提交事务数据,能读取到别人新增数据。能够产生幻读 ;
  • 序列化: 不会产生脏读、不可重复读、幻读问题。
  • MySQL中默认隔离级别是可重复读。

3 MySQL中如何解决脏读和幻读?解决原理?幻读、脏读如何避免?

将MySQL隔离级别设置为读已提交可以解决脏读问题。在RR隔离级别通过mvcc+锁解决部分幻读问题。串行化完全可以解决幻读问题。

4 MySQL中索引有哪些(组合索引、二级索引)?这些索引有什么区别?

  • 主键索引:一种特殊唯一索引,不允许有null。(聚集索引,其他为非聚集索引、二级索引)
  • 普通索引:没有任何限制索引。
  • 唯一索引:不允许重复但是可以允许为null。
  • 组合索引:是多个字段组合成索引,遵循最左前缀法。全文索引:一种搜索索引。

5 索引的底层结构是什么样(B+和hash)?

基于InnoDB,索引是基于B+树和自适应hash。主键索引的B+结构,节点都是主键值,然后叶子节点是完整的数据。非主键索引,节点都是索引值,叶子节点都是主键值。B+一般高是3-4层,故需要3-4次查询。而hash索引时间复杂度为O(1),InnoDB引擎监控各个索引页的查询,如果发现hash索引可以提升速度,建立hash索引。

6 索引为什么使用B+树,而不去使用红黑树和B树,平衡二叉查找树?

降低磁盘读写代价:

因为b+树的分支节点存储都是索引值,并没有数据值,在一块相同的磁盘读取到的索引值数量更多,从而降低磁盘io次数

查询效率更加稳定:

因为数据存储在主键索引的叶子节点。所以每次查询时候都会从根节点到叶子节点获取数据。每次查询数据路径长度相同,每一个数据查询效率相当。

方便扫库:

因为数据全部存储在b+树的叶子节点,而且是双链表的,所以进行扫库,仅仅扫描下叶子节点就可以解决。

二 如何创建索引,创建索引需要注意什么?

对查询频率高,数据量大的表建立索引。使用短索引,因为索引创建后也是磁盘存储,短索引也可以降低磁盘的IO. 将where条件后面的创建组合索引,注意最左前缀。尽量使用唯一索引,效率高。

1 如何查询sql执行计划(慢查询执行情况)?执行计划的各个字段表示什么含义?type一般到什么级别合理?

使用explain + sql来查看sql执行计划。主要字段如下:id: 查询序列号。select_type: 查询类型(简单表,关联查询、子查询) table: 查询表 type: 访问类型,查询索引执行情况,最重要的指标 possible_keys: 可能使用索引 key: 实际使用索引 key_len: 索引长度 rows:扫描函数 extra: 执行情况描述。

查询type最少保证range级别,最好达到ref级别。

2 索引失效,举例sql失效场景,为什么?(in、联合索引失效、like、二级索引失效情况)

like查询使用%开头出现索引失效,尾部模糊匹配索引有效。在组合索引中,索引含有多个列。查询从最左的索引列开始,中间不能跳过索引列,即左前缀法则。如果违反最左前缀法则索引失效。在索引列上进行运算操作,导致索引失效。字符串不加单引号,也会索引失效。用or进行查询时候,or前有索引后没索引。则查询时候索引失效。

3 如何优化sql查询?

对大数据量的表,如果能使用唯一索引,加入唯一索引。尽量使用组合索引形成索引覆盖。防止回表查询,提高性能。count优化:因为很多业务中都有分页操作,然而数据量大,需要遍历全表计数统计,效率低。可以在分页时候不展示总记录数,或者redis维护总记录数,在新增和删除时候对数据进行动态更新。order by优化:使用有序的数据进行排序,不需要额外排序,效率高。group by优化:实际也会排序,可以执行order by null 禁止 排序。

4 MySQL中有哪些锁?什么时候走行锁,什么时候走表锁?InnoDB锁算法?

按照操作方式分类:共享锁(读)、排它锁(写)。select不会默认加共享锁,需要手动加锁。按照锁粒度分:行级锁、表锁。按照使用方式分:乐观锁、悲观锁。

InnoDB支持行锁和表锁,手动添加排它锁,如果该字段是索引是行级锁,如果该字段不是索引是表锁。MyISAM仅支持表锁,需要手动添加表锁。

InnoDB锁算法如下:记录锁、间隙锁、next-key

5 MySQL锁粒度?什么是 GAP Lock ?

锁分为表锁行锁,行锁分为记录锁、间隙锁(gap lock)、next-key临键锁(记录锁和间隙锁)。gap lok又叫做间隙锁

6 MySQL的MVCC(多版本并发控制)是什么?是为了解决什么问题而提出来的?如何实现?

多版本并发控制,保存数据历史版本。通过版本号来决定数据是否显示。通过不加锁来实现事务的隔离级别。一般解决不可重复读、幻读,是采用锁机制。mvcc的机制就是用来解决这个问题,取代锁,降低系统开销。

mvcc是由隐藏字段(trx_id、row_id、roll_ptr)、undo log(存放数据历史版本)、read view(trx_list事务链表、up_limit_id事务中最小id、low_limit_id事务待分配id)实现。事务的id是依次递增的,在每次快照读时候会生成一个read view。然后根据当前行最后修改的事务id,和trx_list列表比较。如果trx_id小于trx_list列表最小值,说明说明当前的read view创建前,数据修改操作已完成,即读取最新数据。如果trx_id大于等于trx_list列表待分配事务,说明产生read view时候,修改事务还没提交,即读取不到最后修改数据的值。如果trx_id大于等于trx_list最小值,小于trx_list最大值,如果trx_id在trx_list中,说明产生read view没有提交事务,则读取不到被修改数据。如果如果trx_id不在trx_list中,说明read view创建时候,trx_id已提交,可以读取到修改数据。

7 MySQL中死锁什么?举几个例子。

两个或者多个进程之间存在资源互斥。现实开发目前没有。

8 MySQL具体什么时候加锁,加什么锁?

在mysql中任何隔离级别,简单select不添加锁,在insert、update、delete添加排它锁(写锁)。

三 主从复制实现原理

1. MySQL中数据丢失如何恢复?具体什么操作?

2. MySQL分布式锁如何实现?

  • 基于版本号和时间戳来实现。

3.MySQL一致性哈希?

4. MySQL 如何平滑扩展?

5. 用过哪些sql函数?

  • avg、count、max、min、sum

6. MySQL的三大范式?

第一范式:

  • 每一列都是最小的不可再分割的,满足第一范式。

第二范式:

  • 满足第一范式基础上,所有非主属性必须完全依赖主属性(可以是一个单独属性,也可以是多个属性组合)

第三范式:

  • 满足第二范式基础上,属性不依赖其他非主属性。

7. 磁盘上表的存储数据格式?

8. MySQL的char和varchar区别、效率?为什么有了varchar还要char?

varchar设置字段,字段长度可以发生改变,相比char效率更高。char在设置字段长度是不可以改变。对一些数据存储长度相似,使用char,数据内存一次性分配完毕,减少内存碎片化。

9. MySQL 存储引擎 InnoDB 和 MylSAM 的区别

InnoDB :

  • 支持事务和行级锁、支持外键而且数据和索引是在一起的。

MylSAM:

  • 不支持事务,支持表锁,数据和索引是分开的。

收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐