一、MySQL基础

1、什么是视图？

视图是建立已有表基础上的虚拟表，不具有数据，只是包含使用时动态检索数据的查询（视图的创建和删除只影响视图本身，不影响对应的基表；对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然）

2、存储过程

一组经过预先编译的SQL语句的封装，也就是为了以后的使用而保存的一条或多条MySQL语句的集合。

3、关系型和非关系型数据库的区别？

（1）关系型数据库
①容易理解，采用了关系模型来组织数据，能够体现出数据之间、表之间的关联关系。
②可以保持数据的一致性。
③数据更新的开销比较小。
④支持复杂查询（带 where 子句的查询）
⑤支持事务，便于提交或者回滚
⑥表格的形式存在，以行和列的形式存取数据
（2）非关系型数据库
①无需经过SQL层的解析，读写效率高，性能很高。
②基于键值对，读写性能很高，易于扩展
③可以支持多种类型数据的存储，如图片，文档等等。

4、drop、truncate 和 delete 的区别

（1）drop
将表所占用的空间全释放掉
（2）truncate
一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器，执行速度快。
（3）delete
每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作
（4）区别
① 执行速度：drop> truncate > delete
② 如果想删除部分数据用 delete
③ 如果想删除表，当然用 drop；如果想保留表而将所有数据删除，如果和事务无关，用 truncate 即可；
④ 如果和事务有关，或者想触发 trigger，还是用 delete；如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入/插入数据。

5、MySQL 的两个 kill 命令

（1）kill query + 线程 id
表示终止这个线程中正在执行的语句
（2）kill connection + 线程 id
这里 connection 可缺省，表示断开这个线程的连接
（3）kill 不掉线程的原因
① kill命令被堵了，还没到位
② kill命令到位了，但是没被立刻触发
③ kill命令被触发了，但执行完也需要时间

6、MySQL 临时表的用法和特性

（1）只对当前session可见。
（2）可以与普通表重名。
（3）增删改查用的是临时表。
（4）show tables 不显示普通表。
（5）在实际应用中，临时表一般用于处理比较复杂的计算逻辑。
（6）由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理时临时表的重名问题，在线程退出的时候，临时表会自动删除。

7、什么是临时表，何时删除临时表

MySQL 在执行 SQL 语句的过程中，通常会临时创建一些存储中间结果集的表，临时表只对当前连接可见，在连接关闭时，临时表会被删除并释放所有表空间。

8、varchar 和 char 的区别

（1）相同点
①char(n)，varchar(n)中的n都代表字符的个数
②超过char，varchar最大长度n的限制后，字符串会被截断
（2）不同点
①char不论实际存储的字符数都会占用n个字符的空间；而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255），因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）
②能存储的最大空间限制不一样：char的存储上限为255字节
③char在存储时会截断尾部的空格，而varchar不会

9、BLOB和TEXT有什么区别？

（1）BLOB是一个二进制对象，可以容纳可变数量的数据。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB
（2）TEXT是一个不区分大小写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。
（3）BLOB 保存二进制数据，TEXT 保存字符数据。

10、关系型数据库介绍

关系型数据库就是一种建立在关系模型的基础上的数据库，关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。
关系型数据库中，我们的数据都被存放在了各种表中，表中的每一行就存放着一条数据。
常见的关系型数据库：MySQL、PostgreSQL、Oracle、SQL Server、SQLite等

二、存储引擎

1、InnoDB 数据页结构

（1）File Header：表示页的一些通用信息，占固定的38字节。
（2）page Header：表示数据页专有信息，占固定的56字节。
（3）Inimum+Supermum：两个虚拟的伪记录，分别表示页中的最小记录和最大记录，占固定的26字节。
（4）User Records：真正存储我们插入的数据，大小不固定。
（5）Free Space：页中尚未使用的部分，大小不固定。
（6）Page Directory：页中某些记录的相对位置，也就是各个槽对应的记录在页面中的地址偏移量。
（7）File Trailer：用于检验页是否完整，占固定大小 8 字节。

2、能说下MyISAM和InnoDB的区别吗？

（1）MyISAM的特点
①不支持事务操作；②不支持外键；③默认的锁是表级锁，所以并发性能比较差，加锁比较快，锁冲突比较少，不太容易发生死锁的情况；④索引和数据是分开存储的，支持的索引类型：全局索引(Full-Text)、B-Tree 索引、R-Tree 索引；⑤数据库所在主机如果宕机，MyISAM 的数据文件容易损坏，而且难以恢复；⑥查询性能较高，适用于查询较多的情况。⑦5.1版本之前的默认引擎
（2）InnoDB的特点
①支持事务操作，具有事务ACID特性，默认的隔离级别是可重复读(repetable-read)、通过MVCC（并发版本控制）来实现的；②支持外键；③默认的锁是行级锁，并发性能比较好，会发生死锁的情况；④InnoDB的表数据与索引数据是存储在一起的，都位于B+树的叶子节点上；⑤有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性；⑥InnoDB 和 MyISAM 支持的索引类型相同，但具体实现因为文件结构的不同有很大差异；⑦增删改性能较高
（3）MyISAM和InnoDB的区别
image.png
①锁粒度：由于锁粒度不同，InnoDB 比 MyISAM 支持更高的并发；InnoDB 的锁粒度为行锁、MyISAM 的锁粒度为表锁、行锁需要对每一行进行加锁，所以锁的开销更大，但是能解决脏读和不可重复读的问题，相对来说也更容易发生死锁。
②可恢复性上：由于 InnoDB 是有事务日志的，所以在产生由于数据库崩溃等条件后，可以根据日志文件进行恢复，而 MyISAM 则没有事务日志。
③查询性能上：MyISAM 要优于 InnoDB，因为 InnoDB 在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而 MyISAM 可以直接定位到数据所在的内存地址，可以直接找到数据。
④表结构文件上：MyISAM 的表结构文件包括：.frm(表结构定义),.MYI(索引),.MYD(数据)；而 InnoDB 的表数据文件为:.ibd和.frm(表结构定义)
⑤事务：InnoDB 支持事务，MyISAM 不支持事务
⑥外键：InnoDB 支持外键，而 MyISAM 不支持
⑦索引：InnoDB 是聚簇索引，MyISAM 是非聚簇索引
⑧行数：InnoDB 不保存表的具体行数；MyISAM 用一个变量保存了整个表的行数，select count(*) from table速度快于InnoDB
（4）一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？
①如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；
②如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。
（5）哪个存储引擎执行 select count(*) 更快，为什么?
MyISAM更快
①在MyISAM存储引擎中，把表的总行数存储在磁盘上，当执行上述语句时，可以直接返回总数据。
②在InnoDB存储引擎中，没有将总行数存储在磁盘上，当执行上述语句时，会全表扫描统计总数量。（为什么没将总行数存储到磁盘上？由于MVCC，InnoDB 表“应该返回多少行”是不确定的）

3、InnoDB引擎的四大特性是什么？

（1）插入缓冲（Insert buffer)
用于非聚集索引的插入和更新操作。
先判断插入的非聚集索引是否在缓存池中，如果在则直接插入，否则插入到 Insert Buffer 对象里。再以一定的频率进行 Insert Buffer 和辅助索引叶子节点的 merge 操作，将多次插入合并到一个操作中，提高对非聚集索引的插入性能。
（2）二次写 (Double write)
①由两部分组成，一部分是内存中的double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为 2MB。
②在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过 memcpy 函数将脏页先复制到内存中的该区域，之后通过doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免操作系统缓冲写带来的问题。
（3）自适应哈希索引 (Adaptive Hash Index)
根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。
（4）缓存池
①提高数据库的性能
②通过参数innodb_buffer_pool_size可以设置缓存池的大小，参数innodb_buffer_pool_instances 可以设置缓存池的实例个数。
③存储内容：索引页、数据页、undo页、插入缓冲 (insert buffer)、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息 (lock info）和数据字典信息 (data dictionary）。

4、Innodb为什么要用自增id作为主键？

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构。

三、索引

1、MySQL 使用索引的原因？

（1）根本原因
为了提高数据查询的效率
（2）扩展
①创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
②帮助引擎层避免排序和临时表
③将随机 IO 变为顺序 IO，加速表和表之间的连接。

2、MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？

（1）InnoDB 存储引擎
B+ 树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。
（2）MyISAM 存储引擎
B+ 树索引的叶子节点保存数据的物理地址，叶子节点的 data 域存放的是数据记录的地址，索引文件和数据文件是分离的。

3、InnoDB 为什么设计 B+ 树索引？

（1）考虑因素
① InnoDB 需要执行的场景和功能需要在特定查询上拥有较强的性能。
② CPU 将磁盘上的数据加载到内存中需要花费大量时间。
（2）为什么选择 B+ 树
①哈希索引虽然能提供O（1）复杂度查询，但对范围查询和排序却无法很好的支持，最终会导致全表扫描。
②B 树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机 IO。
③而 B+ 树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机 IO。

4、什么是覆盖索引和索引下推？

（1）覆盖索引
①定义：在某个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。
②优点：可以减少树的搜索次数，显著提升查询性能，是一个常用的性能优化手段。
（2）索引下推（index condition pushdown)
可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数（MySQL 5.6 引入）

5、Mysql索引在什么情况下会失效

https://mp.weixin.qq.com/s/evn-n7p_476m-BGmSXY-BA
https://mp.weixin.qq.com/s/lEx6iRRP3MbwJ82Xwp675w
（1）查询条件包含or可能导致索引失效
（2）如果字段类型是字符串，where时一定用引号括起来，否则索引失效
（3）like通配符可能导致索引失效
（4）联合索引，查询时的条件列不是联合索引中的第一个列，索引失效
（5）在索引列上使用mysql的内置函数，索引失效
（6）对索引列运算（如，+、-、*、/），索引失效
（7）索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效
（8）索引字段上使用is null， is not null，可能导致索引失效
（9）左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效
（10）mysql估计使用全表扫描要比使用索引快,则不使用索引

6、如何给字符串加索引？

（1）直接创建完整索引
这样可能会比较占用空间。
（2）创建前缀索引
节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。
（3）倒序存储，再创建前缀索引
用于绕过字符串本身前缀的区分度不够的问题。
（4）创建 hash 字段索引
查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

7、聚簇和非聚簇索引是什么？

（1）聚簇索引
B+树是左小右大的顺序存储结构，节点只包含id索引列，而叶子节点包含索引列和数据，这种数据和索引在一起存储的索引方式叫做聚簇索引（一张表只能有一个聚簇索引）
（2）非聚簇索引
聚簇索引(二级索引)保存的是主键id值

8、你知道什么是覆盖索引和回表吗？

（1）覆盖索引
指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引（不需要回表查询）
确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”即可
（2）回表
根据二级索引的id值回到聚簇索引上查找

9、说说你对 MySQL 索引的理解？

（1）索引是帮助MySQL高效获取数据的数据结构，所以说索引的本质是数据结构
（2）一般以索引文件的形式存储在磁盘上
（3）基本使用：增删改查
（4）优点：提高数据检索效率，降低数据库IO成本；降低数据排序的成本，降低CPU的消耗
（5）缺点：①索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存；②大大提高了查询速度，同时却会降低更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段

10、为什么说B+tree比B树更适合实际应用中操作系统的文件索引和数据库索引？

B+tree的磁盘读写代价更低，B+tree的查询效率更加稳定。
主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

11、B-树与B+树的区别？

（1）B-树内部节点是保存数据的；而B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据。
（2）B+树相邻的叶子节点之间是通过链表指针连起来的，B-树却不是。
（3）查找过程中，B-树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束
（4）B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次。

12、都知道数据库索引采用B+树而不是B树，原因也有很多，主要原因是什么？

B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

13、什么时候需要建立数据库索引呢？

在最频繁使用的、用以缩小查询范围的字段、需要排序的字段上建立索引。

14、为什么使用索引（索引的优点）？

（1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
（2）大大加快数据的检索速度（主要原因）。
（3）帮助服务器避免排序和临时表
（4）将随机IO变为顺序IO。
（5）加速表和表之间的连接。

四、数据库调优

1、数据库为什么要进行分库和分表呢？

减小数据库的单库单表负担，提高查询性能，缩短查询时间。

2、假如你所在的公司选择MySQL数据库作数据存储，一天五万条以上的增量，预计运维三年，你有哪些优化手段？

（1）设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
（2）选择合适的表字段数据类型和存储引擎，适当的添加索引。
（3）MySQL库主从读写分离。
（4）找规律分表，减少单表中的数据量提高查询速度。
（5）添加缓存机制。
（6）不经常改动的页面，生成静态页面。
（7）尽量避免使用select *，书写高效率的SQL。

3、MySQL优化了解吗？说一下从哪些方面可以做到性能优化？

（1）为搜索字段创建索引
（2）避免使用select *，列出需要查询的字段
（3）垂直分库分表
（4）选择正确的存储引擎

五、事务

1、事务的隔离级别

image.png

2、数据库事务的特性(ACID)

（1）原子性（atomicity）
指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚
（2）一致性（consistency）
指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态，这种状态是语义上的而不是语法上的，跟具体的业务有关（合法状态指满足预定的约束的状态，简单地说状态是自定义的，满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的。如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务操作之前的状态）
（3）隔离性（isolation）
指一个事务的执行不能被其他事务干扰 ，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
（4）持久性（durability）
指一个事务一旦被提交，它对数据库中数据的改变就是永久性的 ，接下来的其他操作和数据库故障不应该对其有任何影响。

3、ACID特性靠什么保证的呢？

A原子性：由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性：一般由代码层面来保证
I隔离性：由MVCC和锁机制来保证
D持久性：由内存和redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log中恢复

4、数据库并发事务会带来哪些问题？

（1）脏读
当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
（2）幻读
一般是T1在某个范围内进行增加或者删除操作，而T2读取该范围导致读到的数据是修改之间的了，强调范围。
image.png
（3）不可重复读
T2 读取一个数据，然后T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

5、什么是事务？

事务是逻辑上的一组操作，要么都执行，要么都不执行。
举例：假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作就是：将小明的余额减少 1000 元，将小红的余额增加 1000 元。事务会把这两个操作就可以看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都要失败。不会出现小明余额减少而小红的余额却并没有增加的情况。

6、MySQL 的默认隔离级别是什么?

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

  1. #查看默认隔离级别
  2. SELECT @@transaction_isolation;

MySQL InnoDB 的 REPEATABLE-READ（可重读）并不保证避免幻读，需要应用使用加锁读来保证，而这个加锁度使用到的机制就是Next-Key Locks。

六、日志

1、MySQL 的 redo log 和 binlog 区别？

存储内容：binlog记载的是update/delete/insert这样的SQL语句；而redo log记载的是物理修改的内容。
功能：binlog主要用于主从复制和数据恢复；redo log主要用于崩溃恢复，保证数据的持久化。
写入顺序：redo log事务开始的时候，就开始记录每次的变更信息；而binlog是在事务提交的时候才记录。
image.png

2、为什么需要 redo log？

redo log 主要用于 MySQL 异常重启后的一种数据恢复手段，确保了数据的一致性。
其实是为了配合 MySQL 的 WAL 机制。因为 MySQL 进行更新操作，为了能够快速响应，所以采用了异步写回磁盘的技术，写入内存后就返回。但是这样，会存在 crash后内存数据丢失的隐患，而 redo log 具备 crash safe 的能力。

3、为什么 redo log 具有 crash-safe 的能力，是 binlog 无法替代的？

（1）redo log 可确保 InnoDB 判断哪些数据已经刷盘，哪些数据还没有
① redo log 和 binlog 有一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存全量日志。
② 当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 InnoDB 判断哪些数据已经刷盘，哪些数据还没有。
③ 但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，因为是循环写！数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。
（2）如果 redo log 写入失败，说明此次操作失败，事务也不可能提交
① redo log 每次更新操作完成后，就一定会写入日志，如果写入失败，说明此次操作失败，事务也不可能提交。
② redo log 内部结构是基于页的，记录了这个页的字段值变化，只要crash后读取redo log进行重放，就可以恢复数据。

4、当数据库 crash 后，如何恢复未刷盘的数据到内存中？

image.png

5、redo log 写入方式？

（1）redo log包括两部分内容，分别是内存中的日志缓冲redo log buffer和磁盘上的日志文件 redo log file。
（2）MySQL 每执行一条 DML 语句，会先把记录写入 redo log buffer（用户空间） ，再保存到内核空间的缓冲区 OS-buffer 中，后续某个时间点再一次性将多个操作记录写到 redo log file（刷盘） 。这种先写日志，再写磁盘的技术，就是WAL。
（3）可以通过参数innodb_flush_log_at_trx_commit进行配置：

  • 0：称为延迟写，事务提交时不会将redo log buffer中日志写入到OS buffer，而是每秒写入OS buffer并调用写入到redo log file中。
  • 1：称为实时写，实时刷，事务每次提交都会将redo log buffer中的日志写入OS buffer并保存到redo log file中。

  • 2：称为实时写，延迟刷。每次事务提交写入到OS buffer，然后是每秒将日志写入到redo log file。

    6、redo log 的执行流程?

    image.png
    （1）MySQL 客户端将请求语句 update T set a =1 where id =666，发往 MySQL Server 层；
    （2）MySQL Server 层接收到 SQL 请求后，对其进行分析、优化、执行等处理工作，将生成的 SQL 执行计划发到 InnoDB 存储引擎层执行；
    （3）InnoDB 存储引擎层将a修改为1的这个操作记录到内存中；
    （4）记录到内存以后会修改 redo log 的记录，会在添加一行记录，其内容是需要在哪个数据页上做什么修改；
    （5）此后，将事务的状态设置为 prepare ，说明已经准备好提交事务了；
    （6）等到 MySQL Server 层处理完事务以后，会将事务的状态设置为 commit，即提交该事务；
    （7）在收到事务提交的请求以后，redo log 会把刚才写入内存中的操作记录写入到磁盘中，从而完成整个日志的记录过程。

    7、什么是两阶段提交？为什么需要两阶段提交呢?

    （1）什么是两阶段提交
    MySQL 将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，这就是”两阶段提交”（让这两个状态保持逻辑上的一致，redolog 用于恢复主机故障时的未更新的物理数据，binlog 用于备份操作）
    image.png
    （2）为什么需要两阶段提交
    保证 redo log 和 binlog 数据的安全一致性
    ①先写 redo log，crash 后 bin log 备份恢复时少了一次更新，与当前数据不一致。
    ②先写 bin log，crash 后，由于 redo log 没写入，事务无效，所以后续 bin log 备份恢复时，数据不一致。

    8、MySQL 怎么知道 binlog 是完整的?

    一个事务的 binlog 是有完整格式的：
    statement 格式的 binlog，最后会有 COMMIT；
    row 格式的 binlog，最后会有一个 XID event。

    9、binlog 日志的三种格式

    （1）Statement格式——基于SQL语句的复制(statement-based replication,SBR)
    每一条会修改数据的 SQL 都会记录在 binlog 中
    ①优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
    ②缺点：必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。
    （2）Row格式——基于行的复制(row-based replication,RBR)
    不记录 SQL 语句上下文相关信息，仅保存哪条记录被修改。
    ①优点：rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节，不会出现某些特定情况下的存储过程、或 function、或trigger的调用和触发无法被正确复制的问题。
    ②缺点：可能会产生大量的日志内容。
    （3）Mixed格式——混合模式复制(mixed-based replication,MBR)
    ① Statement 与 Row 的结合。一般的语句修改使用 statment 格式保存 binlog，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog
    ② MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式。

    10、redo log日志格式

    redo log buffer (内存中)是由首尾相连的四个文件组成的，它们分别是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。
    image.png
    （1）write pos
    当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
    （2）checkpoint
    当前要擦除的位置，往后推移并且循环的，擦除记录前要把记录更新到数据文件。
    （3）说明
    ① write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。
    ② 如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
    ③ 有了 redo log，当数据库发生宕机重启后，可通过 redo log 将未刷盘的数据（check point之后的数据）恢复，保证已经提交的事务记录不会丢失，这种能力称为crash-safe。

    11、MySQL 是如何保证数据不丢失的？

    （1）只要 redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据
    （2）在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

    12、什么是binlog？

    binlog记录了数据库表结构和表数据变更，比如update/delete/insert/truncate/create，但不会记录select

    13、binlog一般用来做什么？

    主从复制：MySQL在公司使用的时候往往都是一主多从结构的，从服务器需要与主服务器的数据保持一致
    恢复数据：数据库的数据被干掉了，可以通过binlog来对数据进行恢复

    14、redo log 是什么呢？

    redo log是重做日志，指事务中修改了的数据，将会备份存储。记录了数据页上的改动；是Innodb存储引擎独有的；发生数据库服务器宕机、或者脏页未写入磁盘，可以通过redo log恢复。

    15、什么是WAL技术，好处是什么

    WAL，全称是Write-Ahead Logging，它的关键点就是日志先写内存，再写磁盘。MySQL执行更新操作后，在真正把数据写入到磁盘前，先记录日志。
    好处：不用每一次操作都实时把数据写盘，就算crash后也可以通过redo log恢复，所以能够实现快速响应SQL语句。

    16、如果数据库误操作, 如何执行数据恢复?

    数据库在某个时候误操作，就可以找到距离误操作最近的时间节点的bin log，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。

    17、binlog什么时候刷新到磁盘呢？

    由参数sync_binlog控制：

  • sync_binlog为0时，表示MySQL不控制binlog的刷新，而是由系统自行判断何时写入磁盘。选这种策略，一旦操作系统宕机，缓存中的binlog就会丢失。

  • sync_binlog为N时，每N个事务，才会将binlog写入磁盘。
  • sync_binlog为1时，则表示每次commit，都将binlog 写入磁盘。

    18、undo log 是什么？它有什么用

    undo log叫做回滚日志，用于记录数据被修改前的信息。
    undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，这样发生错误时才可以回滚。

七、锁

1、说一下 mysql 的行锁和表锁？

MyISAM 只支持表锁；InnoDB 支持表锁和行锁，默认为行锁。
表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

2、说一下乐观锁和悲观锁？

乐观锁：一个事务获得悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行。每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
悲观锁：允许多个事务同时对数据进行变动。每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

八、多版本并发控制MVCC

1、什么是MVCC？

（1）MVCC叫做多版本并发控制，实际上就是保存了数据在某个时间节点的快照。
（2）表中的每行数据实际上隐藏了两列，一列是创建时间版本号，一列是过期(删除)时间版本号，每开始一个新的事务，版本号都会自动递增。
image.png

  • 小明去执行查询select * from user where id<=3;，此时current_version=3
  • 小红在这时候开启事务去修改id=1的记录update user set name='张三三' where id=1;，此时current_version=4
  • 还有小黑在删除id=2的数据，current_version=5

image.png
（3）MVCC原理：查找创建版本小于或等于当前事务版本，删除版本为空或者大于当前事务版本

  • 小明真实的查询语句是 select * from user where id<=3 and create_version<=3 and (delete_version>3 or delete_version is null);，所以小明最后查询到的id=1的名字还是’张三’，并且id=2的记录也能查询到。这样做是为了保证事务读取的数据是在事务开始前就已经存在的，要么是事务自己插入或者修改的。

九、主从复制

1、说说mysql主从同步(复制)怎么做的吧？

（1）原理
image.png
① master提交完事务后，写入binlog
② slave连接到master，获取binlog
③ master创建dump线程，推送binglog到slave
④ slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中
⑤ slave再开启一个sql线程读取relay log事件并在slave执行，完成同步
⑥ slave记录自己的binglog
（2）全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。
（3）半同步复制
从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

2、Mysql主从同步怎么做（主从复制的原理）

image.png
（1）主库的更新SQL被写到binlog
（2）从库发起连接，连接到主库。
（3）此时主库创建一个binlog dump thread，把binlog的内容发送到从库。
（4）从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
（5）从库还会创建一个SQL线程，从relay log里面读取内容，从ExecMasterLog_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

十、其它知识点

1、说一下一条 MySQL 语句执行的步骤

Server 层按顺序执行 SQL 的步骤为：
（1）客户端请求 -> 连接器（验证用户身份，给予权限）
（2）查询缓存（存在缓存则直接返回，不存在则执行后续操作）
（3）分析器（对 SQL 进行词法分析和语法分析操作）
（4）优化器（主要对执行的 SQL 优化选择最优的执行方案方法）
（5）执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）-> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

2、MySQL 的 change buffer 是什么？

（1）概述
当需要更新一个数据页时，如果数据页在内存中就直接更新；而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中。
这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作
（2）注意
唯一索引的更新就不能使用 change buffer，只有普通索引可以使用 change buffer
（3）适用场景
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

3、原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么？如何解决？

（1）原因（范围从大到小）
① MySQL 数据库本身被堵住了，比如：系统或网络资源不够。
② SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。
③ 索引使用不当，没有走索引。
④ 表中数据的特点导致的，走了索引，但回表次数庞大。
（2）解决
① 考虑采用 force index 强行选择一个索引
② 考虑修改语句，引导 MySQL 使用我们期望的索引。
③ 可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
④ 如果确定是索引根本没必要，可以考虑删除索引

4、如何理解 MySQL 的边读边发

（1）如果客户端接受慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间会很长。服务端并不需要保存一个完整的结果集，取数据和发数据的流程都是通过一个 next_buffer 来操作的。
（2）内存的数据页都是在 Buffer_Pool中操作的

5、MySQL 的大表查询为什么不会爆内存？

（1）由于 MySQL 是边读边发，因此对于数据量很大的查询结果来说，不会在服务器端保存完整的结果集，所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会爆内存。
（2）InnoDB 引擎内部，由于有淘汰策略，InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。对冷数据的全扫描，影响也能做到可控制。

6、MySQL的执行过程

客户端请求 —-> 连接器（验证用户身份，给予权限） —-> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） —-> 分析器（对SQL进行词法分析和语法分析操作） —-> 优化器（主要对执行的sql优化选择最优的执行方案方法） —-> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） —-> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）
（1）连接器
在 MySQL 客户端登陆才能使用，一般使用mysql -u 用户名 -p 密码进行MySQL登录，和服务端建立连接。在完成TCP 握手后，连接器会根据你输入的用户名和密码验证你的登录身份：如果用户名或者密码错误，MySQL 就会提示 Access denied for user，来结束执行；如果登录成功后，MySQL 会根据权限表中的记录来判定你的权限。
（2）查询缓存
MySQL 在得到一个执行请求后，会首先去查询缓存中查找，是否执行过这条 SQL 语句，之前执行过的语句以及结果会以key-value对的形式，被直接放在内存中。key 是查询语句，value 是查询的结果。
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果就会被放入查询缓存中。

查询缓存不建议使用，因为对于更新频繁的数据来说，查询缓存命中率比较低

（3）分析器
根据SQL语句进行解析，先做词法分析（识别出里面的字符串是什么，代表什么），再做语法分析（根据语法规则，判断你输入的这个SQL语句是否满足 MySQL 语法）
（4）优化器
判断你使用了哪种索引，使用了何种连接，确定效率最高的执行方案。
（5）执行器
首先会判断是否具有执行这条语句的权限，如果有权限，就打开表继续执行

7、SQL执行顺序

  2. SELECT DISTINCT
  3.     < select_list >
  4. FROM
  5.     < left_table > < join_type >
  6. JOIN < right_table > ON < join_condition >
  7. WHERE
  8.     < where_condition >
  9. GROUP BY
  10.     < group_by_list >
  11. HAVING
  12.     < having_condition >
  13. ORDER BY
  14.     < order_by_condition >
  15. LIMIT < limit_number >

FROM（对FROM两边的表执行连接，形成笛卡尔积，产生一个虚表VT1）；
ON（对FROM的结果进行ON筛选，创建 VT2）；
JOIN（如果是left join就把ON过滤的左表添加进来，如果是right join，就把右表添加进来，从而生成新的虚拟表 VT3）；
WHERE（对上一步生产的虚拟表引用 WHERE 筛选，生成虚拟表 VT4）；
GROUP BY（对VT4中的记录进行分组操作，产生虚拟表 VT5）；
HAVING（紧跟GROUP BY后面，使用 HAVING 过滤，会把符合条件的放在 VT6）；
SELECT（将VT6中的结果按照SELECT进行查询，生成 VT7）；
DISTINCT（对VT7生成的记录进行去重操作，生成 VT8）；
ORDER BY（按照 order_by_condition 排序 VT8，此时返回的一个游标，而不是虚拟表）

8、MySQL架构

（1）连接层
最上层是一些客户端和连接服务。主要完成连接处理、授权认证、及相关的安全方案。
（2）服务层
主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等。
（3）引擎层
存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。
（4）存储层
主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互。