mysql面试题

最新推荐文章于 2024-08-10 10:51:21 发布

RobotZero

最新推荐文章于 2024-08-10 10:51:21 发布

阅读量134

点赞数

分类专栏： MySQL面试题文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Zeng_1997/article/details/120226029

版权

MySQL面试题专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1、描述一下数据库事务隔离级别？

ACID：

原子性：undo log（MVCC）

一致性: 最核心和最本质的要求

隔离性:锁，mvcc（多版本并发控制）

持久性：redo log

数据库的事务隔离级别有四种，分别是读未提交、读已提交、可重复读、序列化，不同的隔离级别下会产生脏读、幻读、不可重复读等相关问题，因此在选择隔离级别的时候要根据应用场景来决定，使用合适的隔离级别。

各种隔离级别和数据库异常情况对应情况如下：

隔离级别	脏读	不可重复读	幻读
READ- UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE- READ	×	×	√
SERIALIZABLE	×	×	×

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交)：事务的修改，即使没有提交，对其他事务也都是可见的。事务能够读取未提交的数据，这种情况称为脏读。
READ-COMMITTED(读取已提交)：事务读取已提交的数据，大多数数据库的默认隔离级别。当一个事务在执行过程中，数据被另外一个事务修改，造成本次事务前后读取的信息不一样，这种情况称为不可重复读。
REPEATABLE-READ(可重复读)：这个级别是MySQL的默认隔离级别，它解决了脏读的问题，同时也保证了同一个事务多次读取同样的记录是一致的，但这个级别还是会出现幻读的情况。幻读是指当一个事务A读取某一个范围的数据时，另一个事务B在这个范围插入行，A事务再次读取这个范围的数据时，会产生幻读
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。

2、MVCC的实现原理

详见mvcc文档

3、mysql幻读怎么解决的

事务A按照一定条件进行数据读取，期间事务B插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B新插入的数据称之为幻读。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

INSERT into user VALUES (1,'1',20),(5,'5',20),(15,'15',30),(20,'20',30);

假设有如下业务场景：

时间	事务1	事务2
	begin；
T1	select * from user where age = 20;2个结果
T2		insert into user values(25,‘25’,20);commit;
T3	select * from user where age =20;2个结果
T4	update user set name=‘00’ where age =20;此时看到影响的行数为3
T5	select * from user where age =20;三个结果

执行流程如下：

1、T1时刻读取年龄为20 的数据，事务1拿到了2条记录

2、T2时刻另一个事务插入一条新的记录，年龄也是20

3、T3时刻，事务1再次读取年龄为20的数据，发现还是2条记录，事务2插入的数据并没有影响到事务1的事务读取

4、T4时刻，事务1修改年龄为20的数据，发现结果变成了三条，修改了三条数据

5、T5时刻，事务1再次读取年龄为20的数据，发现结果有三条，第三条数据就是事务2插入的数据，此时就产生了幻读情况

此时大家需要思考一个问题，在当下场景里，为什么没有解决幻读问题？

其实通过前面的分析，大家应该知道了快照读和当前读，一般情况下select * from …where …是快照读，不会加锁，而 for update,lock in share mode,update,delete都属于当前读，如果事务中都是用快照读，那么不会产生幻读的问题，但是快照读和当前读一起使用的时候就会产生幻读。

如果都是当前读的话，如何解决幻读问题呢？

truncate table user;
INSERT into user VALUES (1,'1',20),(5,'5',20),(15,'15',30),(20,'20',30);

时间	事务1	事务2
	begin;
T1	select * from user where age =20 for update;
T2		insert into user values(25,‘25’,20);此时会阻塞等待锁
T3	select * from user where age =20 for update;

此时，可以看到事务2被阻塞了，需要等待事务1提交事务之后才能完成，其实本质上来说采用的是间隙锁的机制解决幻读问题。

4、sql join原理?

MySQL是只支持一种Join算法Nested-Loop Join(嵌套循环连接)，并不支持哈希连接和合并连接，不过在mysql中包含了多种变种，能够帮助MySQL提高join执行的效率。

1、Simple Nested-Loop Join

这个算法相对来说就是很简单了，从驱动表中取出R1匹配S表所有列，然后R2，R3,直到将R表中的所有数据匹配完，然后合并数据，可以看到这种算法要对S表进行RN次访问，虽然简单，但是相对来说开销还是太大了。

2、Index Nested-Loop Join

索引嵌套联系由于非驱动表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。这也就是平时我们在做关联查询的时候必须要求关联字段有索引的一个主要原因。

这种算法在链接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择。

在索引嵌套链接的方式下，如果非驱动表的关联键是主键的话，这样来说性能就会非常的高，如果不是主键的话，关联起来如果返回的行数很多的话，效率就会特别的低，因为要多次的回表操作。先关联索引，然后根据二级索引的主键ID进行回表的操作。这样来说的话性能相对就会很差。

3、Block Nested-Loop Join

在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，而是会优先使用Block Nested-Loop Join的算法。

Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程，也就是join buffer，使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问join buffer。

在MySQL当中，我们可以通过参数join_buffer_size来设置join buffer的值，然后再进行操作。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

5、说明一下数据库索引原理、底层索引数据结构，叶子节点存储的是什么，索引失效的情况？

索引的实现原理，底层数据结构，叶子节点存储数据需要看视频了解。

索引失效的情况：

1、组合索引不遵循最左匹配原则

2、组合索引的前面索引列使用范围查询(<,>,like),会导致后续的索引失效

3、不要在索引上做任何操作（计算，函数，类型转换）

4、is null和is not null无法使用索引

5、尽量少使用or操作符，否则连接时索引会失效

6、字符串不添加引号会导致索引失效

7、两表关联使用的条件字段中字段的长度、编码不一致会导致索引失效

8、like语句中，以%开头的模糊查询

9、如果mysql中使用全表扫描比使用索引快，也会导致索引失效

6、mysql如何做分库分表的？

使用mycat或者shardingsphere中间件做分库分表，选择合适的中间件，水平分库，水平分表，垂直分库，垂直分表

在进行分库分表的时候要尽量遵循以下原则：

1、能不切分尽量不要切分；

2、如果要切分一定要选择合适的切分规则，提前规划好；

3、数据切分尽量通过数据冗余或表分组来降低跨库 Join 的可能；

4、由于数据库中间件对数据 Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取尽量少使用多表 Join。

7、数据存储引擎有哪些？

大家可以通过show engines的方式查看对应的数据库支持的存储引擎。

8、描述一下InnoDB和MyISAM的区别？

区别	Innodb	MyISAM
事务	支持	不支持
外键	支持	不支持
索引	即支持聚簇索引又支持非聚簇索引	只支持非聚簇索引
行锁	支持	不支持
表锁	支持	支持
存储文件	frm，ibd	frm,myi,myd
具体行数	每次必须要全表扫描统计行数	通过变量保存行数