关于mysql:想进阿里必须啃透的12道MySQL面试题

篇幅所限本文只写了 12 道经典 MySQL 面试题，像其余的 Redis，SSM 框架，算法，计网等技术栈的面试题前面会继续更新，集体整顿的 1000 余道面试八股文会放在文末给大家白嫖，最近有面试须要刷题的同学能够间接翻到文末支付。

1. 能说下 myisam 和 innodb 的区别吗？

myisam 引擎是 5.1 版本之前的默认引擎，反对全文检索、压缩、空间函数等，然而不反对事务和行级锁，所以个别用于有大量查问大量插入的场景来应用，而且 myisam 不反对外键，并且索引和数据是离开存储的。

innodb 是基于聚簇索引建设的，和 myisam 相同它反对事务、外键，并且通过 MVCC 来反对高并发，索引和数据存储在一起。

2. 说下 mysql 的索引有哪些吧，聚簇和非聚簇索引又是什么？

索引依照数据结构来说次要蕴含 B + 树和 Hash 索引。

假如咱们有张表，构造如下：

create table user(id int(11) not null,
  age int(11) not null,
  primary key(id),
  key(age)
);

B+ 树是左小右大的顺序存储构造，节点只蕴含 id 索引列，而叶子节点蕴含索引列和数据，这种数据和索引在一起存储的索引形式叫做聚簇索引，一张表只能有一个聚簇索引。假如没有定义主键，InnoDB 会抉择一个惟一的非空索引代替，如果没有的话则会隐式定义一个主键作为聚簇索引。

这是主键聚簇索引存储的构造，那么非聚簇索引的构造是什么样子呢？非聚簇索引 (二级索引) 保留的是主键 id 值，这一点和 myisam 保留的是数据地址是不同的。

最终，咱们一张图看看 InnoDB 和 Myisam 聚簇和非聚簇索引的区别

3. 那你晓得什么是笼罩索引和回表吗？

笼罩索引指的是在一次查问中，如果一个索引蕴含或者说笼罩所有须要查问的字段的值，咱们就称之为笼罩索引，而不再须要回表查问。

而要确定一个查问是否是笼罩索引，咱们只须要 explain sql 语句看 Extra 的后果是否是“Using index”即可。

以下面的 user 表来举例，咱们再减少一个 name 字段，而后做一些查问试试。

explain select * from user where age=1; // 查问的 name 无奈从索引数据获取
explain select id,age from user where age=1; // 能够间接从索引获取

4. 锁的类型有哪些呢

mysql 锁分为 共享锁 和排他锁，也叫做读锁和写锁。

读锁是共享的，能够通过 lock in share mode 实现，这时候只能读不能写。

写锁是排他的，它会阻塞其余的写锁和读锁。从颗粒度来辨别，能够分为 表锁 和行锁 两种。

表锁会锁定整张表并且阻塞其余用户对该表的所有读写操作，比方 alter 批改表构造的时候会锁表。

行锁又能够分为 乐观锁 和乐观锁，乐观锁能够通过 for update 实现，乐观锁则通过版本号实现。

5. 你能说下事务的根本个性和隔离级别吗？

事务根本个性 ACID 别离是：

原子性 指的是一个事务中的操作要么全副胜利，要么全副失败。

一致性 指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比方 A 转账给 B100 块钱，假如两头 sql 执行过程中零碎解体 A 也不会损失 100 块，因为事务没有提交，批改也就不会保留到数据库。

隔离性 指的是一个事务的批改在最终提交前，对其余事务是不可见的。

持久性 指的是一旦事务提交，所做的批改就会永恒保留到数据库中。

而隔离性有 4 个隔离级别，别离是：

read uncommit 读未提交，可能会读到其余事务未提交的数据，也叫做脏读。

用户原本应该读取到 id= 1 的用户 age 应该是 10，后果读取到了其余事务还没有提交的事务，后果读取后果 age=20，这就是脏读。

read commit 读已提交，两次读取后果不统一，叫做不可反复读。

不可反复读解决了脏读的问题，他只会读取曾经提交的事务。

用户开启事务读取 id= 1 用户，查问到 age=10，再次读取发现后果 =20，在同一个事务里同一个查问读取到不同的后果叫做不可反复读。

repeatable read 可反复复读，这是 mysql 的默认级别，就是每次读取后果都一样，然而有可能产生幻读。

serializable 串行，个别是不会应用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

6. 那你说说什么是幻读，什么是 MVCC？

要说幻读，首先要理解 MVCC，MVCC 叫做多版本并发管制，实际上就是保留了数据在某个工夫节点的快照。

咱们每行数据实际上暗藏了两列，创立工夫版本号，过期 (删除) 工夫版本号，每开始一个新的事务，版本号都会主动递增。

还是拿下面的 user 表举例子，假如咱们插入两条数据，他们实际上应该长这样。

这时候假如小明去执行查问，此时 current_version=3

select * from user where id<=3;

同时，小红在这时候开启事务去批改 id= 1 的记录，current_version=4

update user set name='张三三' where id=1;

执行胜利后的后果是这样的

如果这时候还有小黑在删除 id= 2 的数据，current_version=5，执行后后果是这样的。

因为 MVCC 的原理是查找创立版本小于或等于以后事务版本，删除版本为空或者大于以后事务版本，小明的实在的查问应该是这样

select * from user where id<=3 and create_version<=3 and (delete_version>3 or delete_version is null);

所以小明最初查问到的 id= 1 的名字还是 ’ 张三 ’，并且 id= 2 的记录也能查问到。这样做是 为了保障事务读取的数据是在事务开始前就曾经存在的，要么是事务本人插入或者批改的。

明确 MVCC 原理，咱们来说什么是幻读就简略多了。举一个常见的场景，用户注册时，咱们先查问用户名是否存在，不存在就插入，假设用户名是惟一索引。

1. 小明开启事务 current_version= 6 查问名字为 ’ 王五 ’ 的记录，发现不存在。
2. 小红开启事务 current_version= 7 插入一条数据，后果是这样：

3. 小明执行插入名字 ’ 王五 ’ 的记录，发现惟一索引抵触，无奈插入，这就是幻读。

7. 那 ACID 靠什么保障的呢？

A 原子性由 undo log 日志保障，它记录了须要回滚的日志信息，事务回滚时撤销曾经执行胜利的 sql

C 一致性个别由代码层面来保障

I 隔离性由 MVCC 来保障

D 持久性由内存 +redo log 来保障，mysql 批改数据同时在内存和 redo log 记录这次操作，事务提交的时候通过 redo log 刷盘，宕机的时候能够从 redo log 复原

8. 那你晓得什么是间隙锁吗？

间隙锁是可反复读级别下才会有的锁，联合 MVCC 和间隙锁能够解决幻读的问题。咱们还是以 user 举例，假如当初 user 表有几条记录

当咱们执行：

begin;
select * from user where age=20 for update;

begin;
insert into user(age) values(10); #胜利
insert into user(age) values(11); #失败
insert into user(age) values(20); #失败
insert into user(age) values(21); #失败
insert into user(age) values(30); #失败

只有 10 能够插入胜利，那么因为表的间隙 mysql 主动帮咱们生成了区间(左开右闭)

(negative infinity，10],(10,20],(20,30],(30,positive infinity)

因为 20 存在记录，所以 (10,20]，(20,30] 区间都被锁定了无奈插入、删除。

如果查问 21 呢？就会依据 21 定位到 (20,30) 的区间(都是开区间)。

须要留神的是惟一索引是不会有间隙索引的。

9. 那分表后的 ID 怎么保障唯一性的呢？

因为咱们主键默认都是自增的，那么分表之后的主键在不同表就必定会有抵触了。有几个方法思考：

1. 设定步长，比方 1 -1024 张表咱们设定 1024 的根底步长，这样主键落到不同的表就不会抵触了。
2. 分布式 ID，本人实现一套分布式 ID 生成算法或者应用开源的比方雪花算法这种
3. 分表后不应用主键作为查问根据，而是每张表独自新增一个字段作为惟一主键应用，比方订单表订单号是惟一的，不论最终落在哪张表都基于订单号作为查问根据，更新也一样。

10. 你们数据量级多大？分库分表怎么做的？

首先分库分表分为垂直和程度两个形式，一般来说咱们拆分的程序是先垂直后程度。

垂直分库

基于当初微服务拆分来说，都是曾经做到了垂直分库了

垂直分表

如果表字段比拟多，将不罕用的、数据较大的等等做拆分

程度分表

首先依据业务场景来决定应用什么字段作为分表字段(sharding_key)，比方咱们当初日订单 1000 万，咱们大部分的场景来源于 C 端，咱们能够用 user_id 作为 sharding_key，数据查问反对到最近 3 个月的订单，超过 3 个月的做归档解决，那么 3 个月的数据量就是 9 亿，能够分 1024 张表，那么每张表的数据大略就在 100 万左右。

比方用户 id 为 100，那咱们都通过 hash(100)，而后对 1024 取模，就能够落到对应的表上了。

11. 分表后非 sharding_key 的查问怎么解决呢？

1. 能够做一个 mapping 表，比方这时候商家要查问订单列表怎么办呢？不带 user_id 查问的话你总不能扫全表吧？所以咱们能够做一个映射关系表，保留商家和用户的关系，查问的时候先通过商家查问到用户列表，再通过 user_id 去查问。
2. 打宽表，一般而言，商户端对数据实时性要求并不是很高，比方查问订单列表，能够把订单表同步到离线（实时）数仓，再基于数仓去做成一张宽表，再基于其余如 es 提供查问服务。
3. 数据量不是很大的话，比方后盾的一些查问之类的，也能够通过多线程扫表，而后再聚合后果的形式来做。或者异步的模式也是能够的。

List<Callable<List<User>>> taskList = Lists.newArrayList();
for (int shardingIndex = 0; shardingIndex < 1024; shardingIndex++) {taskList.add(() -> (userMapper.getProcessingAccountList(shardingIndex)));
}
List<ThirdAccountInfo> list = null;
try {list = taskExecutor.executeTask(taskList);
} catch (Exception e) {//do something}

public class TaskExecutor {public <T> List<T> executeTask(Collection<? extends Callable<T>> tasks) throws Exception {List<T> result = Lists.newArrayList();
        List<Future<T>> futures = ExecutorUtil.invokeAll(tasks);
        for (Future<T> future : futures) {result.add(future.get());
        }
        return result;
    }
}

12. 说说 mysql 主从同步怎么做的吧？

首先先理解 mysql 主从同步的原理

1. master 提交完事务后，写入 binlog
2. slave 连贯到 master，获取 binlog
3. master 创立 dump 线程，推送 binglog 到 slave
4. slave 启动一个 IO 线程读取同步过去的 master 的 binlog，记录到 relay log 中继日志中
5. slave 再开启一个 sql 线程读取 relay log 事件并在 slave 执行，实现同步
6. slave 记录本人的 binglog

因为 mysql 默认的复制形式是异步的，主库把日志发送给从库后不关怀从库是否曾经解决，这样会产生一个问题就是假如主库挂了，从库解决失败了，这时候从库升为主库后，日志就失落了。由此产生两个概念。

全同步复制

主库写入 binlog 后强制同步日志到从库，所有的从库都执行实现后才返回给客户端，然而很显然这个形式的话性能会受到重大影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志胜利后返回 ACK 确认给主库，主库收到至多一个从库的确认就认为写操作实现。

13. 那主从的提早怎么解决呢？

1. 针对特定的业务场景，读写申请都强制走主库
2. 读申请走从库，如果没有数据，去主库做二次查问

本文就先写到这里，面试中常问的一些题目我都有整顿的，前面会继续更新，须要 PDF 的好兄弟能够转发本文 + 关注后【点击此处】即可支付