MySQL面试

MySQL面试

一.MySQL慢查询

• 聚合查询
• 多表查询
• 表数据量过大查询
• 深度分页查询
  表现：页面加载过慢，接口压测响应时间过长（超过1s）

如何定位慢查询？
方案一：开源工具

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

方案二：MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过两秒，就会视为慢查询，记录慢查询日志
long_query_time=2

二.MySQL执行慢分析

可以使用EXPLAIN或者DESC命令获取MYSQL如何执行SELECT语句的信息

• possible_key 当前sql可能会使用到的索引
• key 当前sql实际命中的索引
• key_len 索引所占大小（字节数）
• Extra 额外的优化建议
  
• type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all

• NULL 查询没用到表
• system 查询系统内置表，往往不需要磁盘IO
• const 表中最多只有一行匹配的记录。一般用在主键索引或者唯一键索引上的等值查询（如果是多字段索引，则需要全匹配）
• eq_ref 主键索引查询或者唯一索引查询（返回一条数据）
• ref 索引查询
• range 索引上的范围查询
• index 索引树扫描
• all 全盘扫描

Q&A
Q:如果一个查询SQL执行很慢，该如何分析？
A:如果一个sql执行很慢，我们通常使用mysql自动的执行计划explain来查询这条sql的执行情况，比如可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，可以判断索引的失效情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个，可以通过extra建议来判断，是否出现回表查询，可以尝试添加索引或者修改返回字段来修护

三.MySQL索引

索引是帮助MySQL高效获取数据的数据结构（有序），在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

Q&A
1. 什么是索引
索引是mysql中高效获取数据的数据结构，主要用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗
2. mysql为什么选择B+树作为数据结构存储
二叉树：退化成链表，时间复杂度不稳定
红黑树：mysql中一张表中的数据过大，效率不高
B树：相比于B树，B+树磁盘的读写代价更低（非叶子节点只存储指针），B+树的查找效率更稳定（数据都在叶子节点），B+树更适合扫库和区间（叶子节点有双向指针）
3. 索引的底层数据结构
mysql的默认的存储引擎InnoDB采用B+树的数据结构来存储索引，选择B+树的原因是：第一阶数更多，路径更短（相比二叉树与红黑树），第二，磁盘的读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据，第三，B+树便于扫库和区间查询，叶子节点是一个双向链表
4. 聚簇索引和非聚簇索引
聚簇索引主要是指数据与索引放在一起，B+树的叶子节点保存了整行数据，有且只有一个，一般情况下主键是作为聚簇索引
非聚簇索引（二级索引）指数据与索引分开存储，B+树的叶子节点保存对应的主键（可以有多个），我们一般自己定义的索引都是非聚簇索引
5. 回表查询
回表查询就是执行了两次B+树查询，先通过非聚簇索引查询到主键值，再通过主键查询到对应的数据，整个过程就是回表
6. 覆盖索引
覆盖索引是指select查询语句使用了索引，需要返回的列再索引中必须全部找到（不需要进行回表查询），如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高
7. mysql超大分页如何处理
超大分页一般都是数据量比较大时，使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，解决方案是覆盖查询+子查询，先分页查询大数据的id字段（覆盖查询），再通过子查询过滤，只查询这个id列表的数据就行了
8. 索引创建的原则有哪些
1）数据量比较大（超过10万），并且查询较为频繁的表
2）常作为查询条件、排序、分组的字段
3）字段内容区分度高（字段不能都是某几个值）
4）内容较长的字段，选择前缀索引
5）尽量使用复合索引
6）要控制索引的数量
7）如果索引列不能存储null值，在创建表时需要使用not null约束它
9. 什么情况下索引会失效
复合索引在没有遵循最左匹配法则时，第二个时模糊查询时，%号在前面也会导致索引失效，如果在索引的字段上进行了运算操作或者类型转化也会导致索引失效，如果使用了复合索引，并且使用了范围查询，在范围查询的右边的条件索引也会失效，通常情况下，想判断sql是否出现索引失效的情况，可以使用explain生成执行计划来分析
10. 谈谈对sql优化的经验
1）表的创建时，主要参考阿里的开发手册，在定义字段的时候需要结合字段的内容来选择合适的类型
2）在使用索引时，遵守索引创建原则，避免索引失效
3）在sql语句方面，select语句务必指明字段名称，尽量不要直接使用select *，SQL语句避免出现索引失效，如果是聚合查询，union all 代替 union，从而避免一次过滤，能使用inner join 尽量不使用left join 和 right join
4）主从复制，读写分离
5）分库分表

四.事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要不全部成功，要不全部失败

事务的四大特性（ACID）

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要不全部成功，要不全部失败
• 一致性（Consistency）：当一个事务完成时，所有的数据必须保持一致状态
• 隔离性（Isolation）：事务的隔离性是指在并发环境中，并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间
• 持久性（Durability）：事务一旦提交或者回滚，在数据库的变动是永久的

事务并发带来的问题：

• 脏读：一个事务读到了另一个事务未提交的数据
• 不可重复读：一个事务先后读取同一条记录，读取出的数据不相同
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，而在插入数据时，数据行又已经存在，好像出现了幻觉

事务的隔离级别

• 读未提交：允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
• 读已提交：允许读取已经提交的数据，可能会导致幻读和不可重复读
• 可重复读：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己修改的，可能出现幻读
• 串行化：最高隔离级别

undo log 和 redo log

• 缓冲区： 主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池里的数据（若没有，则从磁盘加载），并以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
• 数据页： 是InnoDB存储引擎磁盘管理的最小单元，每个页默认大小未16KB，页中存储的是行数据

redo log：
记录的是事务提交数据页的物理修改，是用来实现事务持久性的
该日志文件由两部分组成：重做日志缓存（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者是在磁盘中，当事务提交后会把所有修改信息都存在该日志文件中，用于数据同步与落盘
undo log：
回滚日志，用于记录数据被修改前的信息，undo log是逻辑日志，主要用于事务执行失败后进行回滚和MVCC（多版本并发控制），undo log可以实现事务的一致性和原子性
MVCC（多版本并发控制）

MVCC全称Multi-Version Concurrency Control，多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，MVCC的实现依赖数据库记录中的隐藏字段、undo log 日志、readView

• 表的隐藏字段

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或者最后一次修改这条记录的事务ID
DB_ROLL_PTR	回滚指针，指向该条记录的上一个版本，配置 undo log，指向上一个版本
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

• undo log
  回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志
  insert语句产生的日志只在数据回滚时需要，事务提交以后便删除
  update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除

undo log版本链：不同事务或者相同事务对同一记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录

• readview
  ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃事务（未提交的）id

• 当前读
  读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode(共享锁)，select … for update、update、insert、delete(排他锁)都是一种当前读。
• 快照读
  简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读
• Read Committed：每次select，都生成一个快照读
• Repeatable Read：开启事务后第一个select语句才是快照读

Q&A

1. 事务中的隔离性是如何保证的？
   事务的隔离性是由锁和MVCC实现的
   其中mvcc的意思是多版本并发控制，是指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要分为三个部分，隐藏字段、undo log和readview读视图
   隐藏字段是指在mysql中每个表都设置了隐藏字段，其中包括trx_id（事务id），记录每一次操作的事务id，是自增的，另一个字段是roll_pointer（回滚指针），指向上一个版本的事务版本记录地址
   undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
   ReadView解决的是一个事务查询版本选择的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问哪一个事务版本，不同的隔离级别快照读是不一样的，最终的访问结果也不一样，如果是RC隔离级别，每一次执行快照读时都生成ReadView，如果时RR隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用

五.MySQL主从同步原理

二进制（BINLOG）记录了所有的DDL（数据库定义）语句和DML（数据库操纵语言）语句，但不包括数据查询（SELECT，SHOW）语句
Q&A

1. MySQL主从同步原理
   MySQL主从复制的核心就是二进制日志，二进制日志记录了所有的DDL语句和DML语句，具体的主从同步过程大概的流程时作用的
   首先，Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog，然后从主库读取二进制日志文件，写入从库的中继日志文件Relay log，最后slave重做中继日志中的事件，更新数据

六.MySQL分库分表

（待补充）

七.MySQL锁

（待补充）