MySQL

索引

什么是索引

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

索引的底层结构

• MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

• 阶数更多，路径更短

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

聚簇索引和非聚簇索引

• 聚簇索引(聚集索引):数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个

• 非聚簇索引(二级索引):数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个

回表查询

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

覆盖索引

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到

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

联合索引的注意事项

• 最左匹配原则

• 联合索引范围查询

索引下推

select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，b字段需要回表。

索引下推优化， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

优化索引的方法

• 前缀索引优化；
• 覆盖索引优化；
• 主键索引最好是自增的；（雪花算法）
• 防止索引失效；

索引创建的原则

主键索引

唯一索引

根据业务创建的索引

1）针对数据量较大，且查询比较频繁的表建立索引

2）针对常作为查询条件、排序、分组操作字段建立索引

3）尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4）如果字符串类型的字段，字段长度较长，可以针对字段的特点，简历前缀索引

5）尽量使用联合索引，减少单列索引查询时联合索引可以覆盖索引，节省存储空间，避免回表

6）控制索引数量，索引越多，维护索引结构的代价就越大

7）索引列不能存储NULL值，在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用户查询

什么情况下索引会失效

1.违反最左前缀法则

2.对左边列使用>、<范围查询

3.不要在索引列上进行运算操作

4.字符串不加单引号

5.对索引使用函数

6.以%开头的LIKE模糊查询

7.在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

索引的分类

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

什么时候适用索引？

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引

聚集索引和二级索引

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

MySQL 使用 like “%x“，索引一定会失效吗？

不一定，如果能形成索引覆盖的话就不会失效。

B+ 树是如何进行查询的？

通过二分法快速定位到符合页内范围包含查询值的页。

为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？

B+Tree vs B Tree：B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据。

B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找。

B+Tree vs 二叉树：而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

B+Tree vs Hash：

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

MySQL超大分页

问题:在数据量比较大时，limit分页查询，需要对数据进行排序，效率低

解决方案:覆盖索引+子查询

如何定位慢查询

1.介绍一下当时产生问题的场景(我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟)

2.我们系统中当时采用了运维工具 (Skywalking )，可以监测出哪个接口，最终因为是sql的问题

3.在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中(调试阶段)

一个SQL执行很慢，如何分析

可以采用MySQL自带的分析工具EXPLAIN

• 通过key和key len检查是否命中了索引(索引本身存在是否有失效的情况)
• 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
• 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

InnoDB的存储

InnoDB 的数据是按「数据页」为单位来读写的，默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来，物理上不连续，但是逻辑上连续。

count(*) 和 count(1) 有什么区别？哪个性能最好？

因为count(字段)会去判断是否为null，只返回非null的个数。

B 树& B+树

• B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
• 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

事务

事务有哪些特性？

原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成。

一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。

隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

• 持久性是通过 redo log （重做日志）来保证的；
• 原子性是通过 undo log（回滚日志） 来保证的；
• 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证；

并行事务会引发什么问题？

在同时处理多个事务的时候，就可能出现脏读、不可重复读、幻读的问题。

脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

事务的隔离级别有哪些？

• 读未提交（*read uncommitted*），指一个事务还没提交时，它做的变更就能被其他事务看到；
• 读提交（*read committed*），指一个事务提交之后，它做的变更才能被其他事务看到；
• 可重复读（*repeatable read*），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
• 串行化（*serializable* ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

MySQL InnoDB 引擎的可重复读是怎么解决幻读的？

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

这四种隔离级别具体是如何实现的？

• 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
• 对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
• 对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

Read View 在 MVCC 里如何工作的？

• m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
• min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
• max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
• creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

可重复读是如何工作的？

可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

即使之前某个事务提交了，再读的时候也是之前的那个Read View。判断min_trx_id也不会读到提交的新数据。

读提交是如何工作的？

读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。 当之前某个事务提交了，再读的时候会重新生成一个Read View，判断min_trx_id就会读到提交的新数据。

锁

MySQL 有哪些锁？

• 全局锁
• 表级锁
• 行级锁

全局锁应用场景是什么？

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

使用全局锁会影响业务，那有什么其他方式可以避免？

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

MySQL 里面表级别的锁有这几种：

• 表锁；
• 元数据锁（MDL）;
• 意向锁；
• AUTO-INC 锁；

行级锁

• Record Lock
• Gap Lock
• Next-Key Lock
• 插入意向锁

update 没加索引会锁全表？

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

关键还得看这条语句在执行过程种，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

如何避免这种事故的发生？

我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。

如何避免死锁？

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

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

日志

binlog

binlog是记录增、删、改操作的，主要是记录三种模式：statement、row、mixed。

statement记录是sql语句。但是sql语句可能会在now的时候出现问题。解决方案：在前面set timestamp=***，设置好timestamp，再now()就可以正常了。

row是记录行数据。但是如果有加一列的这种操作，可能会导致全表都得生成binlog。

--start-datetime=“2023-06-30 00:00:00” --stop-datetime="2023-08-01 00:00:00"

redolog

redo log不同于bin log的是，bin log记录的是写入的逻辑日志，而redo log是记录物理日志的，即哪个位置的数据发生了什么变化。

bin log会不断去累加，而redo log是一种文件组的环形策略，写满了之后会从头开始替换，不会一直累加。

redo log主要是为了数据恢复的故障恢复，bin log是为了数据库的主从。

在事务提交的时候redo log必须刷盘，必须写到磁盘中。

undo log

undolog主要用于事务回滚操作，

对不同的操作记录的undolog的内容不同：

• 对增加操作记录新增的id
• 对修改的操作记录改动前的值
• 对删除的操作，需要记录删除前的样子，并且还不能直接把主索引树种的记录删除，因为删了就没法link到undolog中了，所以是给原记录打一个delete的标志，然后把列值记录到undolog中。

insert的undo链表和update/delete的undo链表是不同的。

undolog是如何刷盘的？

buffer pool中有undolog页，对undo页的修改页都会记录到redolog。redo log 会每秒刷盘，提交事务时也会刷盘。数据页和 undo 页都是靠这个机制保证持久化的

为什么不能用binlog作为故障恢复？

因为故障恢复本身是要把产生线上影响的数据恢复回来，这是myssql先写内存后异步刷盘导致的问题。

如果写内存后，没有刷盘就崩溃了此时需要将写入内存的脏页恢复到磁盘上，这是因为脏页虽然在内存中，但是可能已经提供服务出去了。

因而故障恢复本质就是脏页的恢复，redolog 能保证事务提交的时候已经刷盘到日志文件，专门就用来恢复脏页。 binlog 并不做这个保证，并且记录的是全量的写sql日志，如果用其做故障恢复，要全量删库然后运行 binlog ，过于兴师动众。

为什么不直接在事务提交的时候刷盘到ibd文件？

因为写ibd文件和写redolog文件不一样，redolog是记录数据页的变动，是顺序写的，而写ibd文件是随机写的，可能位置差距非常大。

InnoDB 是如何存储数据的？

InnoDB 的数据是按「数据页」为单位来读写的，InnoDB 数据页的默认大小是 16KB

数据页中的记录按照「主键」顺序组成单向链表。页目录就是由多个槽组成的，槽相当于分组记录的索引，我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录。

为什么需要 undo log？

在执行增删改操作的时候，一个事务在执行过程中，在还没有提交事务之前，如果 MySQL 发生了崩溃，则需要回滚操作。undolog就是记录事务提交之前的数据。从而保证事务的原子性。

undolog也是实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

Buffer Pool 缓存什么？

InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。

Undo 页是记录什么？

InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

查询一条记录，就只需要缓冲一条记录吗？

不是的。

当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

为什么需要 redo log ？

当Buffer Pool内存中的脏页数据还没有写入磁盘的时候丢失了，为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。

什么是 redo log？

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

redo log 保证了事务四大特性中的持久性。

redo log 和 undo log 区别在哪？

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
• 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

产生的 redo log 是直接写入磁盘的吗？

redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘如下图：

redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能。

redo log 什么时候刷盘？

• MySQL 正常关闭时；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
• 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制，下面会说）。

innodb_flush_log_at_trx_commit 参数控制的是什么？

redo log 文件写满了怎么办？

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫 ：ib_logfile0 和 ib_logfile1 。

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置，如下图：

如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

为什么需要 binlog ？

事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

为什么有了 binlog， 还要有 redo log？

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

而且binlog可以用于主从复制。

binlog 什么时候刷盘？

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

为什么需要两阶段提交？

• 如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。MySQL 重启后，通过 redo log 能将 Buffer Pool 中 id = 1 这行数据的 name 字段恢复到新值 xiaolin，但是 binlog 里面没有记录这条更新语句，在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库的这一行 name 字段是旧值 jay，与主库的值不一致性；
• 如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。由于 redo log 还没写，崩溃恢复以后这个事务无效，所以 id = 1 这行数据的 name 字段还是旧值 jay，而 binlog 里面记录了这条更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，那么这一行 name 字段是新值 xiaolin，与主库的值不一致性；

两阶段提交的过程是怎样的？

其他

SQL优化的方式

• 表的设计优化

  1）设置合适的数值类型（tinyint、int、bigint），根据实际情况选择

• 索引优化

• SQL语句优化

  1）SELECT 指明字段名称，使用覆盖索引，避免使用SLEECT *

  2）SQL避免索引失效

  3）尽量使用union all代替union，union会多一次过滤，效率低

  4）避免在where子句对字段进行表达式操作

  5）Join优化 能用innerjoin就不用left join或者right join，如必须使用一定以小表为驱动，内连接会对两个表进行优化优先把小表放到外边

• 主从复制、读写分离

  1）读的操作比较多的时候，为了避免写的操作所造成的性能影响，可以采用读写分离的结构，读写分离解决的是数据库的写入，影响了查询的效率

• 分库分表

主从同步原理

MySQL主从复制的核心就是二进制日志binlog(DDL(数据定义语言)和DML(数据操纵语言)

（1）主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中

（2）从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 Relay Log。

（3）从库重做中继日志中的事件，将改变反映它自己的数据

执行一条SQL语句，期间发生了什么？

1.连接MySQL，MySQL使用TCP连接，需要经过三次捂手进行连接和4次挥手进行断开连接。

2.查询缓存

3.解析SQL，解析SQL语句的关键词，判断语法是否正确

4.执行SQL

​ 4.1预处理器：

​ 4.2优化器，优化索引等

​ 4.3执行器

执行一条 update 语句，期间发生了什么？

• 客户端先通过连接器建立连接，连接器自会判断用户身份；
• 因为这是一条 update 语句，所以不需要经过查询缓存，但是表上有更新语句，是会把整个表的查询缓存清空的，所以说查询缓存很鸡肋，在 MySQL 8.0 就被移除这个功能了；
• 解析器会通过词法分析识别出关键字 update，表名等等，构建出语法树，接着还会做语法分析，判断输入的语句是否符合 MySQL 语法；
• 预处理器会判断表和字段是否存在；
• 优化器确定执行计划，因为 where 条件中的 id 是主键索引，所以决定要使用 id 这个索引；
• 执行器负责具体执行，找到这一行，然后更新。

主从复制是怎么实现？

MySQL 集群的主从复制过程梳理成 3 个阶段：

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
• 回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程如下：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

为什么不推荐使用外键与级联？

增加了复杂性： a. 每次做 DELETE 或者 UPDATE 都必须考虑外键约束，会导致开发的时候很痛苦, 测试数据极为不方便;

对分库分表不友好：因为分库分表下外键是无法生效的。

drop、delete 与 truncate 区别？

drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。

truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。

delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。

NoSQL 数据库有哪些类型？

键值：Redis 和 DynanoDB 是两款非常流行的键值数据库。

文档：文档数据库中的数据被存储在类似于 JSON（JavaScript 对象表示法）对象的文档中，非常清晰直观。MongoDB 就是一款非常流行的文档数据库。

图形：图形数据库的典型使用案例包括社交网络、推荐引擎、欺诈检测和知识图形。Neo4j 和 Giraph 是两款非常流行的图形数据库。

宽列：宽列存储数据库非常适合需要存储大量的数据。Cassandra 和 HBase 是两款非常流行的宽列存储数据库。

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

Federated：用于访问远程数据库表，可以在不同的MySQL之间进行数据查询。

MEMORY：将表数据存储在内存中，读写速度很快，但重启会丢失。适合用于数据仓库和只读使用。

InnoDB：默认数据引擎1，支持持ACID，适合用于事务性应用和高并发环境

MyISAM：不支持事务处理，但在读密集型应用中性能较好。适合用于临时表和缓存数据。

BLACKHOLE：不存储数据，写入的数据不会保存，但是可以将数据传递给其他MySQL服务器，适用于数据复制和数据传输场景。

CSV：用于读取CSV格式的数据文件，不支持索引，适合导入和导出。

MyISAM 和 InnoDB 有什么区别？

• InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。

• MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。

• MyISAM 不支持外键，而 InnoDB 支持。

• MyISAM 不支持 MVCC，而 InnoDB 支持。

• 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。

• InnoDB 的性能比 MyISAM 更强大。

并发事务的控制方式有哪些？

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

锁 控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。

MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

行级锁的使用有什么注意事项？

当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。

InnoDB 有哪几类行锁？

记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。

间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。

临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身

当前读和快照读有什么区别？