怎么面试程序员的MySQL水平?

面试的宗旨

首先面试最本质的目的是招收合理的人来工作,所以应该要避免的问题是“面试内容是造火箭,结果工作内容却是搬砖”。所以要问的问题最好是贴近生产环境的。

这里说明一下,通常业务中用的最多的是Innodb,所以以下问题都是基于Innodb的。

问题一:考察数据隔离和事务

第一阶段:
MySQL的隔离级别有如下四种,默认的级别是什么?
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

REPEATABLE-READ。这个应该是常识,大家都知道的。这里需要让面试者解释一下隔离级别的意义,因为后面的问题是基于这个概念的。如果面试者忘了概念建议提示一下,继续下一个问题。

READ-UNCOMMITTED:表示可以读取到未提交的数据
READ-COMMITTED:可以读取到已经提交的数据
REPEATABLE-READ:可重复读,每次读取的结果都是一样的。
SERIALIZABLE 完全串行化的读,每次读都需要获得共享锁,读写相互都会阻塞

这里很多面试官会问一个REPEATABLE-READ怎么解决幻读的问题。当时有点蒙,“幻读”是什么东西。其实本质上很简单,幻读其实就是MySQL怎么确保“可重复读”这个功能的。具体的名词叫数据多版本并发控制(MVCC)。不过这个应该是比较底层的知识,属于Nice to have了。

第二阶段
有一张表user_count,这个表记录的是不同国家用户的个数。现在如下代码会在以上四个级别的隔离中分别执行一次,请问结果分别是什么?注意,以下代码是在5000个线程中执行的,所以是并发执行的。
数据样例:
+——–+——–+
| region | number |
+——–+——–+
| us | 0 |
| cn | 0 |
+——–+——–+
代码:
@dec_multi_thread_run(5000)
def do():
try:
# get old num from table 
num = “select number from user_count where region = ‘cn'”
# update to new value
“update user_count set number = %s where region = ‘cn’ ” % (num +1)
except:
rollback()
else:
commit()

首先问这个问题的话,面试者肯定知道答案不是5000,而是小于5000。大部分人会根据隔离级别越来越高判断结果的值会越来越小,而真实结果如下。

read uncommitted,3271
read committed,2638
repeatable read,2551
serializable,3052

对于前三个结果递减比较容易解释,因为随着事物隔离级别变高越不可能读到脏数据。但对于serializable,直观的结果应该是返回5000,因为序列化级别是不会读到脏数据的。但实际上这里考察的是一个会出现死锁的问题。serializable级别会对读取加上一个共享锁,也就是说在多线程下会出现很多事务同时获取到了共享锁。然后当要执行update操作去获取排它锁时就会出现死锁了。也就是说某些更新会失败造成结果小于5000。这里考察到了serializable具体的底层执行方式。

如果问的深入一点,可以问下MySQL检查死锁的方式以及怎么自动解除死锁的。当然,这个也是Nice to have的。在看参考资料1的时候,有提到对高并发业务的优化。具体的方式就是关闭死锁检测,然后用一个较短的timout时间替代。不过我没有亲自验证,有兴趣的同学可以自行验证一下。

第三阶段
怎么优化上面的代码,使结果为5000

这个比较简单,主要是考察一下面试者知不知道锁的概念。 select 语句后面加一个for update就解决问题了。

如果把语句改成update user_count set number = number + 1 where region = ‘cn’ 在四个事物级别下结果分别是多少?

结果都是5000,因为MySQL会默认在insert,update,delete时增加一个排他锁,Nice to have。

问题二:索引

第一阶段:
有一个user表,id是自增主键,记录的是用户的信息。数据量是1000万行,请建立索引并优化用到的查询。
+——–+————–+———-+———-+————–+
| id | country \ user_name \ sex \ create_time |
+——–+————–+———-+———-+————–+
| 1 | cn | alice | female | 1559476160 |
| 2 | us | bob | male | 1559476170 |

用到的查询语句
— 查找叫Alice的人
select * from user where user_name = “alice”;
— 查找国家是us叫Alice的人
select * from user where country = “us” and user_name = “alice”;
— 查找所有叫Alice的女性
select * from user where sex = “female” and user_name = “alice”;
— 展示最新注册的10个用户
select * from user order by create_time desc limit 10;

最优解是只用一个联合索引(user_name, country)并调整一下语句2和语句3的查询顺序,username放在前面。

针对第一个语句,只查询user_name也能利用到联合索引。针对语句2完美利用了联合索引。针对语句三不用对sex建立索引,因为区分度太低没有意义。针对语句4自增id默认就是按照时间递增的,所以没有必要再增加索引了。

重点理解联合索引的最左原则​:联合索引的顺序遵循左前缀原理,以最左边
的为起点任何连续的索引都能匹配上。

​示例:​以 index(a,b,c) 为例:相当于创建了(a)单列索引,(a,b)联合索引以及(a,b,c)联合索引

第二阶段:
通常在设计数据库的时候都会用到自增id,具体的原因是什么?

答案是增加数据插入的速度。知道这里就够了。下一个阶段是Nice to Have

第三阶段:
MySQL索引的数据结构是什么?为什么要用这个结构?

因为MySQL存储的数据结构用的是B+树。B+树和B树(多路平衡查找树)的主要区别是B+树所有的数据都是存储在叶子节点的。通常B+树的层级都是很少地,单个非根节点会存储很多的数据,这样可以减少读取索引的次数。因为机械磁盘擅长的是大块地顺序读写,一次性读到越多的数据越有利于快速查找,而且B+树有个好处,所有的叶子节点都是指针相连的,可以快速地进行全表便利。所以是个特别适合数据库的数据结构。

● 关于 MySQL 的知识点与面试常见问题都在这里

● 步步深入MySQL:架构->查询执行流程->SQL解析顺序!

● 一份软件工程行业生存指南

Author: victor

阅读次数 116

发表评论

电子邮件地址不会被公开。 必填项已用*标注