MySQL基础篇8终结篇 mysql的基础问题总结

1. 数据库基础架构相关问题:

1.0 逻辑架构图

1.1 长短连接

1.1.1 啥是长连接, 啥是短连接?

• 长连接是指连接成功后, 如果客户端持续有请求, 则一直使用一个连接;
• 短连接是指每次执行完很少的的几次查询就断开连接, 下次再重新建立一个;

1.1.2 为啥建议使用长连接

• 建立连接的过程通常比较复杂, 所以建议在使用中减少建立连接的动作, 也就是尽量使用长连接.

1.1.3 为啥使用全部使用长连接后, 有时MySQL的内存上涨很快, 如何解决这个问题呢.

why

• mysql 在执行过程中临时使用的内存是管理在连接对象里面的. 这些资源会在断开连接的时候才会释放.
• so 如果长连接积累下来, 可能导致内存占用太大. 被系统强杀(OOM), 从现象来看就是mysql异常重启了.

how

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连
2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

1.2 查询缓存

1.2.1 啥是查询缓存?

• 之前执行过的查询语句会以key-value 对存在内存中, key是查询语句, value是查询结果. 如果缓存命中, 直接返回.
• 若未命中, 则交给分析起继续执行查询.

1.2.2 到底用不用查询缓存?

总的来说, 是因为弊大于利, 所以不推荐.

1. 对于库中的表来说, 每次更新表都会清空数据, 所以对于更新压力大的数据库来说, 命中率会很低,
2. 但是当你只维护一张静态表, eg一个系统配置表, 很就才会更新一次, 那当我没说, 这张表还是很适合使用查询缓存的.

1.2.3 如何启动查询缓存?

• 参数query_cache_type设置为DEMAND,默认sql的语句都不使用查询缓存. 可以用SQL_CACHE显式指定.

select SQL_CACHE * from T where ID=10；

• 如果你用的是mysql8.0 当我没说. 因为8.0将查询缓存整块功能都删除了.

1.3 各种器

1.3.1 分析器是干啥的?

• 词法分析. 分析sql语句中的字符串都是啥意思. eg 从关键字select分析出是一个查询语句, 从把"T"识别成"表名T", 把"ID"识别成"列ID".
• 语法分析. 主要是判断sql语句是否满足mysql的语法.

1.3.2 优化器是干啥的?

• 当表中有多个索引的时候, 决定使用哪个索引.
• 当一个语句存在夺标关联的时候, 决定各个表连接顺序.

1.3.3 执行器是干啥的?

• 验权. 判断一下用户对表T有无执行权限. 如果没有返回err.
• 调用引擎提供的接口. 执行sql语句
• 记录rows_examined. 在数据库的蛮查询日志中存在rows_examined字段. 执行器每次调用引擎获取数据行时累加

1.4 一些问题

1.4.1 如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”, 是哪个阶段爆出来的?

2 数据库日志系统相关问题:

2.1 redo log

2.1.1 啥是WAL技术?

• 全称是 Writt-Ahead Loggin. 核心是先写日志, 再写磁盘.
• 具体来说. 当你更新一条记录时. innodb引擎会先把记录写到redo log中, 同时更新内存, 此时更新done. 当在适时的时候. innodb会将一批操作同一写入磁盘.

2.1.2 康康redo log 的设计

• 固定大小. 比如可以配置成一组4个文件, 每个文件大小为1GB. 整个redolog为4GB. 总共可以记录U4GB的操作. 循环写入.
• write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头.
• checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件
• write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的
• 如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下

2.1.3 啥是crash-safe

• 保证即使数据库发生异常重启, 之前提交的记录不会丢失. 这个能力叫做crash-safe

2.2 binlog

2.2.1 为啥会有两份日志呢?

1. 最开始的没有innodb. myisam没有crash-safe能力. binlog只用于归档
2. innodb以插件的形式引入的. 使用redolog 来实现crash-safe能力

2.2.2 redo 与 bin 区别是啥?

1. redolog是innodb 特有的. binlog是mysql server层实现的, 谁都有.
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

2.3 两阶段提交

2.3.1 update的执行流程

2.3.2 为啥有要有两阶段提交.

• 为了让两份日志之间的逻辑一致.
• 两阶段提交是跨体统位置数据逻辑一致时的常用的一个方案.

2.4 一些问题

2.4.1 innodb_flush_log_at_trx_commit参数

• 设置为1, 每次事务的redolog 都会直接持久化到磁盘
• 建议设置为1

2.4.2 sync_binlog参数

• 每次事务的binlog都会直接持久化到磁盘
• 建议设置为1

3 事务隔离

3.1 隔离性和隔离级别是啥?

• 为了解决脏读, 不可重复读, 幻读的问题, 有了隔离级别的概念.
• 隔离的级别越高. 效率越低, 所以我们需要在二者之间找到一个平衡点.

3.2 数据库上有多个事务同时执行会出现查问题是啥?

• 脏读(dirty read):读取未提交数据
• 不可重复度(non-repeatable read):前后多次读取，数据内容不一致
• 幻读(phantom read):数据总量不一致

3.3 隔离的级别都有哪些?

• 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

3.4 如何设置和查看事务的隔离级别?

设置: transaction-isolation 的值设置成 READ-COMMITTED

查看: show variables like 'transaction_isolation'

3.5 事务隔离是咋实现的?

undo log:

实际上每条记录在更新的时候都会记录一条回滚操作. 记录上最新的值, 通过回滚操作都可以得到前一个状态的值.

何时删除undolog :

当系统里没有比这个回滚日志更咋哦的read-view的时候.

为啥不建议使用长事务?

1. 长事务意味着系统里面会存在很老的事务视图。会保留大量的回滚日志. 这就会导致大量占用存储空间。
2. 长事务还占用锁资源, 可能会拖垮整个库

3.6 事务的启动方式是啥?

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback
2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接

建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中

3.7 场景例子1

复现上图1

复现上图2

4 锁

4.1 mysql从颗粒度上分为哪三类?

• 全局锁: 对整个数据库实例加锁, 加锁之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句
• 表级锁:有两种, 表锁和元数据锁
• 行锁: 引擎提供的锁.

4.2 全局锁加锁方法的执行命令是啥? 主要的应用场景是啥?

命令: Flush tables with read lock (FTWRL)

场景: 全库逻辑备份

通过FRWRL确保不会有其他线程对数据库做更新, 然后对整个库进行备份. 让整个库处于只读状态:

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

4.3 做整库备份为啥要加全局锁?

为了使视图逻辑一致

4.4 MySQL的自带备份工具, 使用什么参数可以确保一致性视图, 在什么场景下不适用?

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。

4.5 不建议使用set global readonly = true的方法加全局锁有哪两点原因?

• readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大
• 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高

4.6表级锁有哪两种类型? 各自的使用场景是什么?

• 表锁:　lock tables ... read/write.
  • 可以使用unlock tables释放, 也可以在客户端断开的时候自动释放.
  • lock tables 除了限制别的线程读写意外, 也限定了本线程接下来的操作对象.
  • 举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。
• MDL: 元数据锁. 不需要显式使用, 在访问一个表的时候会自动被加上.
  • 作用: 保证了读写的正确性.
  • 当对一个表做DML 会加上MDL读锁, 做DDL 会加上MDL写锁.

4.7 MDL中读写锁之间的互斥关系怎样的?

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

4.8 如何安全的给小表增加字段?

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。

如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 kill 可能未必管用，因为新的请求马上就来了。

比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

4.9 如果发现应用程序里有 lock tables 这样的语句可能情况是哪些?

1. 可能系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
2. 可能引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。

4.10 备份一般都会在备库上执行，当备库用–single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎么样？

假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来：

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);

启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)；

设置一个保存点，这个很重要（Q3）；

show create 是为了拿到表结构 (Q4)，然后正式导数据 （Q5），回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁 （Q6）

DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。

参考答案如下：

1. 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
2. 如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
3. 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
4. 从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

4.11 两阶段锁的概念是什么? 对事务使用有什么帮助?

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

4.12 死锁的概念是什么? 举例说明出现死锁的情况.

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

出现死锁

4.13 死锁的处理策略有哪两种?

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

4.14 等待超时处理死锁的机制什么?有什么局限?

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

4.15 死锁检测处理死锁的机制是什么? 有什么局限?

发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑

主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务

4.16 有哪些思路可以解决热点更新导致的并发问题?

1. 确保这个业务一定不会出现死锁，可以临时把死锁检测关掉
2. 控制并发度, 通过中间件 或者 直接在server层加入队列
3. 将一行改成逻辑上的多行来减少锁冲突

5 索引