数据库MySQL面试总结-持续更新中思维导图模板

事务特性ACID

A: atomicity 原子性事务内SQL要么同时成功要么同时失败，基于UndoLog实现。

C：consistency 一致性是事务的目的，AID均是实现C的手段，系统从一个正确态转移到另一个正确态，由应用通过AID来保证，并非数据库的责任。

I：isolation隔离性控制事务并发执行时数据的可见性，基于锁和MVCC实现。

D：durability持久化通过redo log保证，提交后一定存储成功不会丢失，基于RedoLog实现。 修改数据时，mysql是先把这条记录的页找到，然后加载到内存，将对应记录进行修改，而为了防止内存修改完之后mysql挂掉，引入redo log，记录这次在某个页做了某个修改，及时mysql挂了也能根据redo log恢复。

数据结构

B+树优点：

1. 相较于二叉树，一个Node节点存储信息更多，树高更低

2. 对比B树，B+非叶子不存数据，相同数据量下，B+数更加矮壮

3. 叶子节点之间组成一个双向链表，便于范围查询

B+树数据存储

首先根节点常驻内存，每个节点对应一个数据页也即一次磁盘IO，所以每次查询需要N-1次IO，N为树高 非叶子节点存储索引键值和子节点指针 叶子节点存储键值以及 1. （聚簇索引（每个表只有一个聚簇索引）的情况）具体数据（或在myisam中是存指向数据地址的指针） 2. （非聚簇索引的情况）主键

只在聚簇索引保存具体行数据

存储引擎差别

Innodb

同：都是B+树

异：

MyISAM

异：

为什么用自带自增主键？

由于主键需要有序，若使用随机会影响插入性能，原因是生成的随机ID，在插入时存在导致页分裂的可能，就需要移动数据页

隔离级别

RUC

脏读

RC

不可重复读

RR

幻读问题（存在当前读的幻读，快照读没有幻读的问题）

可以通过开启 next key lock 解决当前读（select for update）的幻读

S

行锁实现原理

通过给索引项加锁来实现，意味着只有通过索引条件检索数据才会被加上行锁，否则InnoDB将使用表锁

算法分类

1. Record Lock 单行上锁

Record lock单条索引记录上加锁，Record lock锁住的永远是索引，而非记录本身。 索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引; 如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引

<ol><li>2. Gap Lock 锁定一个范围，不包含记录本身</li></ol>

Gap Lock锁定的是索引之间的间隙，并不是记录本身。例如有一个索引有3，5，6,10和20这几个值，他们之前的间隙包括（-∞，3）、（3，5）、（5，6）、（10，20）、（20，+∞），对于Gap Lock锁定就是这几个范围。间隙锁和间隙锁之间是互不冲突，间隙锁目的是阻止间隙内的记录插入。

3. Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身

意向锁

当执行一条加行锁的语句，innodb除了在这条记录上增加了行级X锁之前，还对所在表添加了一个意向排它锁。 这个时候如果我们有表级锁操作，如：alter table、drop table、lock table 的操作时，会先检查对应表是否存在意向排它锁，若存在则等待锁释放。 这样是为了避免在加表锁时在表里逐行判断是否有行锁

MVCC

解决脏读、不可重复读、幻读问题（只是快照读的幻读问题，还有当前读的幻读问题需要通过next-key lock解决，通过锁定查询对应索引的行和间隙，锁范围）

通过read view + undo log实现

read view

creator_trx_id 当前事务id

m_ids 当前系统中所有的活跃事务的 id，还没提交

min_trx_id 当前系统中，所有活跃事务中事务 id 最小的那个事务，也就是 m_id 数组中最小的事务 id

max_trx_id 当前系统中事务的 id 值最大的那个事务 id 值再加 1，也就是系统中下一个要生成的事务 id

原理：针对RC，生成语句级快照；针对RR，生成事务级快照

RC：总是读取当前记录最新版本号的数据，（版本号在事务commit之后才会生成），即每次都获取一个新的read view

RR：总是读取当前事务的版本，即使当前记录被其他事务修改了版本，也只会读取当前事务版本的数据，即每次事务只获取一个read view

Log文件

undolog（innodb特有）

redolog（innodb特有）

是物理日志，记录的是“在某个数据页上做了什么修改”

是循环写的，空间固定会用完

相关参数：innodb_flush_log_at_trx_commit 设置为1表示每次事务提交都将redolog刷到磁盘，影响性能但是安全

binlog（属于server层）

是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2这一行的 c 字段加 1 ”

是可以追加写入的。“追加写”是指 binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

相关参数：sync_binlog 设置N则每N次写binlog直接刷到磁盘

RedoLog、UndoLog在整个执行过程中的流程

迁移

整体方案

1. 增量双写

同步

逻辑简单，影响性能，老数据写入才更新新数据

异步

由于是异步，要注意时序问题，可以老数据写入后发消息，异步线程收到消息后，直接查老库数据写入新库，或者设置version

需要有一个任务定时对比两个库，diff的case用老数据覆盖新

2. 存量迁移

双写diff符合预期后，进行存量迁移，通过db脚本或代码进行

3. 双读

存量迁移后，diff全量数据比较困难，这时我们可以双读+异步diff。读老库然后异步读新库，diff后上报，再在监控中发现问题

4. 切读

上面的稳定后，切读，但是双写不能停，防止出问题没有退路

5. 下线双写双读

稳定后

常见优化

最左匹配（前缀）

1. 对于 like 'C%'，由于在B+树结构的索引中，索引项是按照索引定义里面出现的字段顺序排序的，索引在查找的时候，可以快速定位到 ID 为 100的张一，然后直接向右遍历所有张开头的人，直到条件不满足为止。

2. 对于联合索引（A,B,C)，同理索引建立时是按ABC顺序的，例如只有A一样的情况下，B才是有序的

3. 所以联合索引最左边最好是区分度最高的

覆盖索引

不对索引进行函数或表达式计算

查执行计划

执行计划说明

extra

Using temporary

排序没有走索引、使用union、子查询连接查询等case

Using filesort

没有用索引排序，利用排序列+行指针放到到sort buffer，然后进行快排； 如果数据集大小超过buffer大小，那么会形成多个排序完成的小文件，再归并排序，消耗CPU，需要优化; 也可以通过sortbuffer调大，减少创建临时文件的成本，当然最好是做优化使用索引排序

order by使用索引

1. ORDER BY的索引优化 SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]; 在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。 2. WHERE + ORDER BY的索引优化 SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]; 建立一个联合索引(columnX,sort)来实现order by 优化。 注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化 SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort]; 3. WHERE+ 多个字段ORDER BY SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10; 建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

Using index

覆盖索引

Using index condition

索引下推（有这个标志不代表一定发生下推）

发生在联合索引的前提下，例如用到联合索引第一个字段是范围查询或者Like等，正常是不会用到后续字段做联合查询的，但是开启索引下推后，引擎层会继续利用余下的索引字段做过滤（主要的核心点就在于把数据筛选的过程放在了存储引擎层去处理） ICP 的目的就是为了减少回表导致的磁盘 I/O，用在二级索引，是把index filter放在引擎层去做，引擎层用where条件利用索引直接过滤掉不符合的，减少回表数据量

rows

type

all < index < range ~ index_merge < ref < eq_ref < const < system

key

深分页优化

SELECT * FROM field_auto WHERE id >= (SELECT id FROM field_auto LIMIT 100000, 1) LIMIT 10；子查询只查主键，再根据主键查记录数据

1. 使用索引覆盖+子查询优化

利用主键id+分页条件先查出开始位置的id值，根据查到的id查询对应数量数据

2. 起始位置重定义

每次记录这次查出来数据的最大id，下一次请求时传入，只查询比这个id大的数据，业务场景是一些瀑布流新闻这种，只能一页一页刷的。

3. 降级策略

判断请求分页大小，对于太大的分页值直接返回空数据，因为太大说明是刷数据，实际业务深分页的场景比较少

读写分离

分库分表

原因

单数据库连接数有限

IO瓶颈

读IO瓶颈：热点数据多，数据库缓存放不下，查询时产生大量磁盘IO，查询速度较慢，导致活跃连接数变多，可以采用主从，读写分离，分库分表来解决

写IO瓶颈：出现大量写，只能分库分表

CPU瓶颈

查询存在大量函数或非索引字段查询，可以分库分表

方案

1. MyCat

接入方便，代码耦合度低；需要单独部署，黑盒，排查问题复杂

2. Sharding-Jdbc

客户端接入，对业务有一定侵入，但排查问题方便

步骤

确定重构的目标，对未来有个预期，以订单场景为例

如预期日订单1000W

确定Key，订单分库分表主要用于下单和查询，按user_id的频率最高，所以选择user_id；另外订单号查询频率也较高，所以在order_id中掺杂了user_id，比如order_id前半部分是user_id，后面是订单号，这样针对订单号查询可以先解析出user_id然后再查，

分库分表数量

比如可以分成2的N次幂个库/表，可以使MOD和&结果一样，&效率更高

另外直接查库效率不高，所以上层可以加redis，存储用户活跃的若干条订单，若用户查询的超过缓存？

问题

数据倾斜

可以不用简单的hash分表，而是通过一致性hash，这样数据倾斜时，只会影响一小部分数据

数据多维度查询

通过ES+HBASE

将索引与数据存储隔离。可能参与条件检索的字段都会在ES中建一份索引，例如商家，商品名称，订单日期等。所有订单数据全量保存到HBase中。我们知道HBase支持海量存储，而且根据rowkey查询速度超快。而ES的多条件检索能力非常强大。可以说，这个方案把ES和HBase的优点发挥地淋漓尽致。 看一下该方案的查询过程：先根据输入条件去ES相应的索引上查询符合条件的rowkey值，然后用rowkey值去HBase查询，后面这一步查询速度极快，查询时间几乎可以忽略不计

数据库写入降级方案

例如大促时订单生成较多，将同步改为异步发消息，较小服务器压力，增大吞吐量，消费时可以放到redis中，每隔一段时间或者一定数量订单再写入数据库，可以配置开关进行降级

常见问题

走了索引还是慢，可能原因是数据确实大 可以通过删除旧数据，旧数据可以放到hive 也可以走缓存、或者es，通过空间换时间

死锁问题？

表现

表锁行锁 gap锁 next key 锁都会有死锁的可能性

如何避免？

1. 以固定顺序访问表、行，比如两个更新数据的事务，事务A更新数据的顺序为1，2;事务B更新数据的顺序为2，1。这样更可能会造成死锁。

2. 由于大事务造成死锁的可能性更大，尽量把大事务拆小，减少范围查询更新

3. 同一个事务中，尽量一次性获取所有需要的锁资源

4. 降低隔离级别，在业务场景允许的情况下，可以使用rc的隔离级别，避免gap锁死锁

5. 建立合适的索引，避免表锁

如何排查？

1. 通过应用业务日志定位到问题代码，找到相应的事务对应的sql，mysql自带的死锁检测 Deadlock found when trying to get lock 死锁被检测到后会回滚，报错会体现在业务异常日志中

2. 确定数据库的隔离级别，如RC，那么可以排除gap lock导致的死锁

3. 找dba 执行 show InnoDB STATUS看看最近死锁的日志。

一条SQL查询语句执行的过程

连接池，分为客户端的连接池和MySQL本身的连接池

维护一定的连接数，避免频繁建立、销毁连接

MySQL Server 层

连接器

管理连接和权限校验等

查询缓存

鸡肋，由于失效非常频繁，8.0已经移除

解析器

预处理

优化器

选择最优执行计划，根据成本最小（CPU/IO成本最小），如选择合适的索引，生成执行计划

IO成本

即从磁盘把数据加载到内存，MySQL以页的形式读取数据，并不是单独读取某条记录，即局部性原理，所以IO成本主要和页的大小有关

CPU成本

数据读到内存后还要确认是否满足条件和排序等消耗CPU的操作，CPU成本和行数有关

存储引擎层 （数据的存取和检索，真正执行的地方，是表维度的不是数据库维度，即同一个库不同表的存储引擎可以不同）

buffer pool

是一块内存区域，当数据库操作数据的时候，把硬盘上的数据加载到buffer pool，不直接和硬盘打交道，操作的是buffer pool里面的数据

和undo log/redo log/redo log buffer/binlog一起使用，后续会把数据刷到硬盘上

更新语句执行过程

1. 先执行查询，过程如上

2. server层拿到查询的结果，修改这个结果，并调用引擎层接口写入新数据

3. 引擎将这行数据更新到内存，同时将这个更新记录到redolog，此时redolog处于prepare状态，表示事务可以提交了

4. server层执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

二阶段提交 pre - commit，保证redolog和binlog一致

崩溃恢复规则 redo log 和 binlog 有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log： 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交； 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。 binlog无记录，回滚事务 binlog有记录，提交事务。 有了两阶段提交可以保证利用redolog崩溃恢复和利用binlog生成的备份数据一致

常用命令

删表

delete

删除表数据

DML

可以回滚

truncate

删除表数据

DDL

不能回滚

drop

删除表数据、表结构