MySql面试题整理

MySQL

优化

定位慢查询

表象：页面加载过慢、接口压测响应时间过长(超过1s)

可能的查询语句

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

两种方式定位

1. 开源工具测试
   • 调试工具 Arthas
   • 运维工具 Skywalking Prometheus
2. 开启慢日志（调试模式:上线后一般不会开启这个慢日志）
   • MySQL自带慢日志
   • 配置/etc/my.cnf
     • slow_query_log = 1 开启慢日志查询开关
     • long_query_time =2 设置慢日志的时间，超过时间就会记录
   • 重启MySQL服务器进行测试
     • 查看慢日志 /var/lib/localhost-slow.log

SQL执行计划

一个SQL语句执行很慢，怎么分析

采用 EXPLAIN 或者 DESC 命令获取MySQL如何执行SELECT语句的信息 ，在查询语句前加上关键字

• 查询实际命中的索引和索引占用的大小
  • key 当前sql实际命中的索引
  • key_len索引占用的实际大小
• 获取这条语句的连接类型
  • type(由好到差 从上到下)
    • NULL
    • system 查询系统中的表
    • const 根据主键查询
    • eq_ref 主键索引查询或唯一索引查询
    • ref 索引查询
    • range 范围查询
    • index 索引树查询
    • all 全盘扫描
• 获取优化建议
  • extra
    • Using where; Using Index 查找使用了索引，需要的数据都在索引中能找到，不需要回表查询数据
    • Using index codition 查找使用了索引，但是需要回表查询数据

索引

存储引擎

索引底层数据结构

什么是索引？

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

什么是索引

• 索引（index）是帮助MySQL高效获取数据的数据结构(有序)
• 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

二叉树和红黑树

当数据量存储很大的时候，仍然要花费好长的时间找数据

B Ttree

多叉路衡查找树，相对于 二叉树，B树每个节点都可以有多个分叉，即多叉。

B+ 树

在BTree基础上进行的优化，适合实现外存储索引结构

InnoDB存储引擎 采用B+Tree实现其索引结构

• 阶数更多，路径更短
• 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
• B+树便于扫库和区间查询，叶子节点是一个双向链表

聚簇和非聚簇索引

聚集索引(Clustered Index)

将数据存储与索引放到了一块，索引结构的叶子节点保存了 行数据

必须有,而且只有一个

聚簇索引选取规则

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

二级索引(Secondary Index) 非聚簇索引

将数据与索引分开存储，索引结构的叶子节点关联的是对应的 主键

可以存在多个

回表查询

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

MySQL超大分页处理

覆盖索引是什么

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经 全部 能够找到 。

• 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
• 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

超大分页处理

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

索引创建原则

• 数据量较大，且查询比较频繁的表 重要
• 常作为查询条件、排序、分组的字段 重要
• 字段内容区分度高
• 内容较长，使用前缀索引
• 尽量联合索引 重要
• 要控制索引的数量 重要
• 如果索引不能存储NULL值，在创建表时使用NOT NULL 约束它

索引失效场景

违反最左前缀法则

​ 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列

违反最左前缀法则，索引失效

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效

范围查询右边的列，不能使用索引

不要在索引列上进行运算操作， 索引将失效

字符串不加单引号，造成索引失效

以%开头的Like模糊查询，索引失效

• 违反最左前缀法则
• 范围查询右边的列，不能使用索引
• 不要在索引列上进行运算操作， 索引将失效
• 字符串不加单引号，造成索引失效。(类型转换)
• 以%开头的Like模糊查询，索引失效

SQL优化经验

• 表的设计优化
  • ​ 参考阿里开发手册
    • 比如设置合适的数值（tinyint int bigint），要根据实际情况选择
    • 比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低
• 索引优化 参考优化创建原则和索引失效
• SQL语句优化
  • SELECT语句务必指明字段名称（避免直接使用select * ）
    • Select * 有可能回表查询
  • SQL语句要避免造成索引失效的写法
    • 索引失效 就可能会增加数据排序，回表，造成cpu效率的浪费
  • 尽量用union all代替union union会多一次过滤，效率低
  • 避免在where子句中对字段进行表达式操作
  • Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要以小表为驱动
• 主从复制、读写分离
  • 如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。
  • 读写分离解决的是，数据库的写入，影响了查询的效率。
• 分库分表

其他面试题

事务相关

事务特性

事务是什么呢？

ACID

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

• 脏读

  • 一个事务读到另外一个事务还没有提交的数据。

• 不可重复读

  • 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

• 幻读

  • 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

隔离级别

undo log 和 redo log的区别

• redo log 记录的是数据页的物理变化，服务宕机可用来同步数据 事务持久性
• undo log 记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据 原子性 一致性

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

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

redo log 日志文件由两部分组成

• 重做日志缓冲（redo log buffer）
• 重做日志文件（redo log file）

当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚 和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是逻辑日志。

• 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然
• 当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

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的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
  • update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。
  • undo log 版本链

    • 

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

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

  • 当前读和快照读

    • 当前读 ： 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
    • 快照读： 简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读
      • Read Committed：每次select，都生成一个快照读。
      • Repeatable Read：开启事务后第一个select语句才是快照读的地方。

  • ReadView 核心字段

    • m_ids 当前活跃的事务ID集合
    • min_trx_id 最小活跃事务ID
    • max_trx_id 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
    • creator_trx_id ReadView创建者的事务ID

  • 版本链访问规则

    • 

  • 生成ReadView时机

    • READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
    • REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

RC

RR

主从同步原理

分担访问压力

二进制文件

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

1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
3. slave重做中继日志中的事件，将改变它自己的数据。

分库分表

解决存储压力

时机：

前提，项目业务数据逐渐增多，或业务发展比较迅速 单表的数量达1000w或20G以后

优化已解决不了性能问题（主从读写分离、查询索引…）

IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）

• 垂直拆分

  • 垂直分库 ： 以表为依据，根据业务将不同表拆分到不同库中

    • 

    • 按业务对数据分级管理、维护、监控、拓展

    • 在高并发下，提高磁盘IO和数据量连接数

  • 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中

    • 

    • 冷热数据分离

    • 减少IO过渡争抢，两表互不影响

• 水平拆分

  • 水平分库： 将一个库的数据拆分到多个库中。

    • 特点
      • 解决了单库大数量，高并发的性能瓶颈问题
      • 提高了系统的稳定性和可用性
    • 路由规则
      • 根据id节点取模
      • 按id也就是范围路由，节点1(1-100万 ),节点2(100万-200万)
    • 

  • 水平分表： 将一个表的数据拆分到多个表中(可以在同一个库内)。

    • 特点：
      • 优化单一表数据量过大而产生的性能问题
      • 避免IO争抢并减少锁表的几率
    • 

  • 分库后的问题

    • 分布式事务一致性问题
    • 跨节点关联查询
    • 跨节点分页、排序函数
    • 主键避重
    • 使用 sharding-shphere 或 mycat 中间件来避免这些问题的产生

• 具体拆分策略

  • 水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题
  • 水平分表，解决单表存储和性能的问题(水平分库 和水平分表需要使用中间件来解决拆分后的问题)
  • 垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数
  • 垂直分表，冷热数据分离，多表互不影响