【Mysql】面试题汇总

1. 存储引擎

1-1. MySQL 支持哪些存储引擎？默认使用哪个？

答：

MySQL 支持的存储引擎包括 InnoDB、MyISAM、Memory 等。

Mysql 5.5 之前默认的是MyISAM，Mysql 5.5 之后默认的是InnoDB。

可以通过 show engines 查看 Mysql 支持的所有存储引擎。

1-2. MySQL 存储引擎架构了解吗？

答：

存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式。

MySQL 的存储引擎设计的是可拔插式的。存储引擎是基于表的，不同的表可以使用不同的存储引擎。

1-3. MyISAM 和 InnoDB 有什么区别？

答：

InnoDB 支持事务，MyISAM不支持事务。
InnoDB 支持行锁和表锁，MyISAM只支持表锁。
InnoDB 支持外键，MyISAM不支持外键。
InnoDB 支持MVCC，MyISAM不支持MVCC。
InnoDB 支持数据库异常崩溃后的安全恢复，MyISAM不支持。 todo: 原因好像MyISAM没有 undo、redo 日志文件

1-4. MyISAM 和 InnoDB 如何选择？

答：

如果对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，InnoDB 存储引擎是比较合适的选择。

对事务的完整性、并发性要求不是很高，以读操作和插入操作为主，MyISAM存储引擎是比较合适的选择。

2. 索引

2-1. 索引是什么，有什么作用？

答：

索引是一种用于快速查询和检索数据的数据结构（有序）。

作用：当数据量比较大时，使用索引可以极大地提高数据检索的效率，通过索引项对数据进行排序，降低了数据排序的成本。

2-2. MySQL 中的索引是怎么实现的，为什么选B+树作为索引的数据结构？

答：

MySQL 中默认的存储引擎是 InnoDB ， InnoDB使用 B+Tree 的数据结构来存储的索引。

B+Tree 是一颗多叉的平衡搜索树，每个节点是可以存储多个值，它是B树的一个变种。

B+Tree 与 B 树的主要区别：

B 树每个节点存储数据和指针，B+Tree 树非叶子节点只存储指针，这样每个非叶子节点存储的指针就会更多，进而树的高度就会更低，进而减少磁盘 I/O 次数。
B树的叶子节点没有指针相连， B+Tree 叶子节点形成一个单向链表，更适合范围查询。而Mysql 对B+Tree进行一个优化，多了一个指向相邻节点的指针。这样可以满足字段的降序排序。

B+Tree 与 Hash主要区别：

Hash查找的效率很高，但无法做范围查询。

综合以上的区别，所以为什么选B+树这种数据结构来实现索引。

2-3. 索引的分类有哪些？

答：

按「数据结构」分类：B+tree索引、Hash索引、全文索引。
按「物理存储」分类：聚簇索引、二级索引。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

在这里插入图片描述

2-4. 什么时候需要 / 不需要索引？

答：

什么时候适用索引？

字段有唯一性限制的，比如商品编码
经常用于 where 查询条件的字段，以及 group by 和 order by 的字段。因为建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不适用索引？

查询条件用不到的字段，因为索引是会占用物理空间。
存在大量重复数据的字段。
经常更新的字段不用创建索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引。
表数据太少的时候，不需要创建索引。

2-5. 索引什么时候会失效？

答：

联合索引不满足最左匹配原则
索引字段使用运算操作
索引字段使用头部模糊查找
字符串类型的字段没有加引号，发生了类型转换
使用or连接条件有一侧的字段没有索引时
范围查询时，右边的索引会失效

2-6. 什么是最左匹配原则？

答：

比如现在有一个联合索引，它是由多个字段组成的。

此时查询条件的字段一定要包含创建这个联合索引字段时候最左边的那个字段，那么索引才生效。

没有最左边的字段为什么会失效？

因为联合索引首先是按最左边的这个字段进行排序的，如果相同再按之后的字段排序，如果没有第一个字段，那么此时查找的时候，对于其他字段来说就不是有序的了。

2-7. 索引的优化方法有哪些？

答：

使用覆盖索引。覆盖索引就是我们要查询的字段，它已经包括在查询条件的联合索引中了。这样就不用回表查询了。
对于一些大字符串的字段，可以建立前缀索引，节省空间。
主键索引最好自增，这样每次插入一条新记录，都是追加操作。如果不是自增，会出现页分裂。

2-8. 表的一行数据大小为1k，有五千万条记录，主键是bigint类型，求B+树的高是多少？

答：

Mysql页中的指针大小固定是6个字节，因为主键是bigint类型，所以是8个字节。一页的大小固定是16k。

首先假设一页中有 n 个数据，n + 1 个指针。一页可以存储的总字节 : 16 * 1024 = n * 8 + (n + 1) * 6

求出n后，进而知道了一个页的指针个数。

假设树高为2，则可以存储的总记录数：（n + 1）* （16K / 1K） = （n + 1）* 16

假设树高为3，则可以存储的总记录数：（n + 1）*（n + 1） * 16

假设树高为x，那么将5000万带入上述公式后：5000万 = （n + 1）^ x * 16

从而求出树高x。

2-9. 什么是聚簇索引，什么是二级索引，什么是回表查询？

答：

聚簇索引：B+树的叶子节点保存了整行的数据，聚簇索引有且只能有一个。
二级索引：B+树的叶子节点保存对应的主键，二级索引可以有多个。
回表查询：先通过二级索引找到对应的主键值，然后根据主键值找到聚簇索引中的行数据。

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

2-10. 什么是覆盖索引？

答：

覆盖索引是指：查询的列在一个索引中能够全部找到，从而避免的回表查询。

案例：

id 为主键，为主键索引。name 为普通索引

select * from user where id = 1; # 使用了覆盖索引
select id, name from user where name = 'Aram'; # 使用了覆盖索引
select id, name, gender from user where name = 'Aram'; # 没有使用覆盖索引

3. 锁

3-1. Mysql有哪些锁？

答：

全局锁：整个数据库就处于只读状态。场景：数据库逻辑备份
表级锁
- 表锁
  - 表共享读锁：所有客户端都只能读数据，不能写数据
  - 表独占写锁：只有上锁的客户端可以读写数据，其他都不能读写数据。
- 元数据锁（MDL）系统自动加 避免DDL和CRUD冲突
  - MDL读锁：CRUD操作时，加MDL读锁
  - MDL写锁：对表结构进行变更操作，加MDL写锁
- 意向锁 系统自动加 快速判断表里的记录是否有行锁，避免行锁与表锁冲突
  - 意向共享锁：与 表共享读锁 兼容，与表独占写锁 互斥
  - 意向排他锁：与 表共享读锁 互斥，与表独占写锁 互斥
行级锁 InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。
- 行锁：锁定单个行记录。行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。
  - 共享锁：select … in share mode 会加共享锁，与排他锁互斥
  - 排他锁：insert、update、delete、select … for update 会加排他锁。与共享锁、排他锁互斥
- 间隙锁：锁定索引之间的间隙，目的解决幻读问题。
- 临键锁：行锁和间隙锁组合，同时锁住索引项，并锁住索引项前面的间隙

3-2. 什么SQL语句会加行级锁？

答：

update、insert、delete、 select … for update 会加行锁的排他锁

select … in share mode 会加行锁的共享锁

当事务提交了，锁就会被释放

3-3. 行级锁有哪些？

答：

行锁：对索引项加锁

间隙锁：对索引的间隙加锁

临键锁：行锁 + 间隙锁，锁定索引项和索引项前面的间隙

3-4. Mysql是怎么加行级锁的？

答：

加锁的对象是索引项，加锁的基本单位是 next-key lock

next-key lock 在一些场景下会优化成行锁或间隙锁。

用唯一索引进行等值查询的时候

查询记录存在，优化成行锁，确保这个记录不会被删除，防止幻读。
查询记录不存在，在索引树找到第一条大于该查询记录的记录，将该记录的next-key lock优化成间隙锁，确保不会将查询记录插进来，防止幻读。

没有加索引的查询的时候

没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了

3-5. update条件字段没加索引会锁全表？

答：

会，没有索引会全表扫描，那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

3-6. 行锁 + 间隙锁可以防止删除操作的幻读吗？

3-7. 加了什么锁会导致死锁？

3-8. 死锁了怎么办？

3-9. 意向锁是什么？有什么作用？它是表级锁还是行级锁？

答：

意向锁是一种表级锁，它是用来避免行锁和表锁发生冲突，可以快速判断表中记录是否有行锁。

当对记录加行锁时，会自动的对表加上意向锁。

3-10. 备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

答：

InnoDB 存储引擎默认的事务隔离级别是可重复读，那么可以在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。

这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

4. 事务

4-1. 事务有哪些特性？

答：

InnoDB 引擎是支持事务的，MyISAM 引擎不支持事务

事务的四大特性：

原子性：事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败
一致性：事务操作的前后，数据库要保持一致性的状态。比如从转账业务角度来看，A向B转账之后，他们账号的总额与转账之前的总额要保持一致
隔离性：数据库是支持多个事务并发执行的，事务不会受到其他并发执行事务的影响
持续性：事务对数据的操作是永久性的，即便系统故障也不会丢失。

4-2. 并发的事务操作会有什么问题？

答：

脏读：一个事务读取了另一个事务未提交的数据。如果另一个事务进行回滚，则当前读取的数据就是脏数据。
不可重复读：在同一个事务内，前后读取同一条数据不一致。
幻读：在一个事务内，前后进行查询时，发现两次读取的记录数量不一样。

4-3. 事务的隔离级别有哪些？

答：

读未提交：一个事务读取另一个事务未提交的数据
读已提交：一个事务读取另一个事务提交的数据
可重复读：在同一个事务内，前后读取同一条数据保持一致
串行化：所有的事务串行执行

4-4. 什么是MVCC？

答：

MVCC全称是多版本并发控制，是数据库中用来实现事务隔离的一种技术，维护了一个数据的多个版本。

MVCC的具体实现，还需要依赖于数据库记录中的隐式字段、undo log日志、readView。

事务id（隐式字段）满足相应的规则（readView中的访问规则）就会读取到对应的版本数据（存放在undo log日志）

4-5. 在可重复读的隔离级别下，是如何解决幻读的？

答：

针对快照读，也就是普通的 select 语句，是通过 MVCC 解决的。因为开启事务后创建的ReadView在后续的数据查询中会一直沿用，所以不会出现幻读的问题

针对当前读，除了快照读（普通select语句）之外的语句，都是当前读，每次执行前都会查询最新的记录，其它也就是select…for update等语句，是通过next-key lock(记录锁+间歇锁)解决的。

比如执行了select * from t_stu where id >2 for update,这个时候就会为id>2的记录加上next-key lock,在本事务提交之前，其它事务都没有办法对id>2的记录进行修改操作，也就解决了幻读问题

4-6. ReadView在MVCC里如何工作？

答：

4-7. Mysql是如何实现可重复读的？

答：

5. 优化

5-1. 在Mysql中如何定位慢查询？

慢查询出现的原因：

聚合查询
多表查询
表数据量过大查询
深度分页查询

表现现象：

页面加载过慢、接口压测响应时间过长（超过1s）

方案一：开源工具

调试工具：Arthas
运维工具：Prometheus、Skywalking

方案二：Mysql自带的慢日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒)的所有SQL语句的日志如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：

在这里插入图片描述

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

var/lib/mysql/localhost-slow.log

在这里插入图片描述

答：

在这里插入图片描述

5-2. 如果有个SQL语句执行很慢，应该如何分析？

答：

慢查询出现的原因：

在这里插入图片描述

如果 Extra 是 Using index condition 则表明有优化的空间的

type：这条sql的连接类型，性能由好到差为 NULL、system、const、eq_ref、ref、range、index、all
NULL：查询时没有用到表
const：根据主键查询
eq_ref：主键索引查询或唯一索引查询
ref：索引查询
range：范围查询
index：全索引树扫描
all：全表扫描

最低的要求是：range，如果是 index 或 all 就需要进行优化了。

在这里插入图片描述

答：

在这里插入图片描述

5-3. Mysql超大分页应该如何处理？

答：

当执行 SELECT ... LIMIT N, M 语句时，MySQL实际上会检索前N+M行数据，然后扔掉前N行，返回后面M行。

随着N的增大，MySQL需要检索的数据量和丢弃的数据量也会增大，导致性能问题。

优化思路：分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

例如：

select * from user limit 9000000, 10; # 查询9000010条记录，扔掉前9000000行，返回后面10行# 覆盖索引加子查询
select * 
from user u, (select id from user order by id limit 9000000, 10) a 
where u.id = a.id;