网站建设使用什么软件比较好,有创意的汽车广告,网络工程专业就业方向与就业前景,网站页面设计模板MySQL数据库 - 面试宝典 又到了 金三银四、金九银十 的时候了#xff0c;是时候收藏一波面试题了#xff0c;面试题可以不学#xff0c;但不能没有#xff01;#x1f941;#x1f941;#x1f941; 一个合格的 计算机打工人 #xff0c;收藏夹里必须有一份 MySQL 八…MySQL数据库 - 面试宝典 又到了 金三银四、金九银十 的时候了是时候收藏一波面试题了面试题可以不学但不能没有 一个合格的 计算机打工人 收藏夹里必须有一份 MySQL 八股文 面试题 特别是即将找工作的计算机人希望本篇博客对你有帮助 祝各位计算机人都可以在就业季里顺利通过面试找到 钱多事少离家近 的满意工作 面试题系列 博客导航 JavaSE 八股文 面试宝典 MySQL 八股文 面试宝典 ⇦当前位置 Spring、MyBatis、SpringMVC 八股文 面试宝典 加班中 ...未完待续 ... 如果有一天你 厌倦上班、厌倦996 想要 考研备考 的话408 全套初复试笔记汇总 传送门 后续随着自己的学习也会陆续补充新的面试题如果对大家起到帮助的话求赞 、求收藏、求关注 第1章 数据库基础
01. 简述数据库三大范式 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值就说明该数据库表满足了第一范式。 数据库第二范式关系模式必须满足第一范式并且所有非主属性都完全依赖于主码。注意符合第二范式的关系模型可能还存在数据冗余、更新异常等问题。关系模型学号姓名专业编号专业名称中学号-姓名而专业编号-专业名称不满足数据库第二范式 数据库第三范式关系模型满足第二范式所有非主属性对任何候选关键字都不存在传递依赖。 即每个属性都跟主键有直接关系而不是间接关系。 接着以学生表举例对于关系模型学号姓名年龄性别所在院校院校地址院校电话院校地址院校电话和学号不存在直接关系因此不满足第三范式。 02. 简述MySQL的架构 MySQL可以分为应用层、逻辑层、数据库引擎层、物理层。 应用层负责和客户端响应客户端请求建立连接返回数据。逻辑层包括SQK接口解析器优化器Cache与buffer。数据库引擎层有常见的MyISAM,InnoDB等等。物理层负责文件存储日志等等。 03. 简述执行SQL语言的过程 客户端首先通过连接器进行身份认证和权限相关如果是执行查询语句的时候会先查询缓存但MySQL 8.0 版本后该步骤移除。没有命中缓存的话SQL 语句就会经过解析器分析语句包括语法检查等等。通过优化器将用户的SQL语句按照 MySQL 认为最优的方案去执行。执行语句并从存储引擎返回数据。 第2章 存储引擎及数据类型
2.1 存储引擎的概念
存储引擎其实就是 如何存储数据、如何为存储的数据建立索引和如何更新、查询数据 等技术的实现方法 。
相关的sql语句
// 1. 查询支持的全部存储引擎
SHOW ENGINES;// 2. 查询默认的存储引擎
SHOW VARIABLES LIKE storage_engine%;MySQL支持的存储引擎 InnoDB (默认)MyISAMMemory 2.2 面试题
01. 简述InnoDB存储引擎 ① 定义 InnoDB 是 MySQL 的 默认事务型引擎 对事务处理的能力优于其它存储引擎。 ② 特点 支持 外键约束 支持 表级锁、行级锁 采用 MVCC 来支持高并发并且实现了四个 隔离级别 (默认级别可重复读)间隙锁策略 防止幻读间隙锁使 InnoDB 不仅仅锁定查询涉及的行还会对索引中的间隙进行锁定防止幻行的插入。 ③ 分析 优点事务管理、崩溃恢复能力、并发控制。 是唯一支持ACID事务的标准 MySQL 存储引擎能自动从灾难中恢复适合多重并发的更新请求 缺点读写效率稍差占用数据空间较大。 02. 简述Memory存储引擎 ① 定义 Memory存储引擎将所有数据都保存在内存不需要磁盘 IO。所以生命周期很短一般为 一次性 。 ② 特点 文件存储形式一张 Memory 表对应一个磁盘文件 (文件名与表名相同)索引类型默认适用 哈希索引 速度比 B树索引 快锁Memory 表使用表级锁因此并发写入的性能较低 03. 简述MyISAM存储引擎 ① 定义 MySQL5.1及之前MyISAM 是默认存储引擎。 独立于操作系统当建立一个MyISAM存储引擎的表时就会在本地磁盘建立三个文件例如建立tb_demo表 tb_demo.frm 存储表结构tb_demo.MYD 存储数据tb_demo.MYI 存储索引 ② 特点 不支持事务、表不支持外键Myisam支持表级锁不支持行级锁 ③ 分析 优点占用空间小处理速度快 缺点不支持事务的完整性和并发性 (适合查询频繁不适合对于增删改要求高的情况) 04. 简述Myisam和Innodb的区别
InnoDB存储引擎MyISAM存储引擎主要应用主要面向 在线事务处理 方面主要面向 在线分析处理 方面支持事务、外键、行锁、自动增加列AUTO_INCREMENT属性不支持事务、表锁和全文索引应用场景适合频繁修改以及设计到安全性较高的应用适合查询以及插入为主的应用清空整个表的时候一行一行的删除MYISAM则会新建表只缓存索引文件数据文件交给操作系统独立于操作系统MYD存放数据文件MYI存放索引文件第3章 索引
3.1 索引的概念
3.2 面试题
01. 索引的基本原理 索引的定义用于快速找到特定记录的一种数据结构。如果没有索引执行查询时需要遍历整张表。 索引的原理把无序的数据变成有序的查询。 索引检索的实现过程 把创建了索引的列的内容进行排序对排序结果生成倒排表在倒排表内容上拼上数据地址链在查询的时候先拿到倒排表内容再取出数据地址链从而拿到具体数据 02. 索引的设计原则 在进行索引设计的时候应该保证 索引字段占用的空间越小越好这只是一个大的方向还有一些 适合情况 ① 定义有外键的数据列一定要创建索引② 适合索引的列是出现在where字句中的列或者连接子句中指定的列③ 在选择索引列的时候越短越好可以指定某些列的一部分没必要用全部字段的值 不适合情况 ① 更新频繁的字段不要有索引② 大文本、大对象不要创建索引③ 基数较小的表索引效果差没必要创建索引③ 不要给表中的每一个字段都创建索引并不是索引越多越好④ 创建索引的列不要过多可以创建 组合索引但是组合索引的列的个数不建议太多 03. 索引的类型
索引定义普通索引不应用任何限制条件的索引唯一索引使用 unique 参数设置唯一索引主键索引特殊的唯一索引在一张表中只能定义一个主键索引使用关键字primary key 来设置全文索引使用 fulltext 参数来设置通过建立倒排索引可以极大的提升检索效率单列索引只对应一个字段的索引多列索引在表的多个字段上创建索引04. 索引的数据结构 总 索引的数据结构和具体存储引擎的实现有关mysql中使用较多的索引有hash索引B树索引 innodb 存储引擎的索引实现为 B树索引memory 存储引擎的索引实现为 hash索引 。 分 B树是一个绝对平衡的多叉排序树 在B树上的常规检索从根节点到叶子节点的搜索效率基本相当不会出现大幅波动而且基于索引的顺序扫描时也可以利用双向指针快速左右移动效率非常高。因此B树索引被广泛应用于 数据库、文件系统 等场景。 哈希索引就是采用一定的哈希算法把键值换算成新的哈希值检索时不需要类似B树那样从根节点到叶子节点逐级查找只 需一次哈希算法即可立刻定位到相应的位置速度非常快。 如果是等值查询那么哈希索引明显有绝对优势因为只需要经过一次算法即可找到相应的键值前提是键值都是唯一的。如果 键值不是唯一的就需要先找到该键所在位置然后再根据链表往后扫描知道找到对应的数据 如果是范围查询检索这时候哈希索引就毫无用武之地了因为原先是有序的键值经过哈希算法后有可能变成不连续的了 就没办法再利用索引完成范围查询检索 哈希索引也没办法利用索引完成排序以及like这样的部分模糊查询 哈希索引也不支持多列联合索引的最左匹配规则 B树索引的关键字检索效率比较平均不像B树那样波动大在有大量重复键值情况下哈希索引的效率也是极低的因此存在 哈希碰撞问题。 05. Hash索引 哈希索引对于每一行数据计算一个哈希码并将所有的哈希码存储在索引中同时在哈希表中保存指向每个数据行的指针。 只有 Memory 引擎显式支持哈希索引。 Hash索引不支持范围查询无法用于排序也不支持部分索引列匹配查找。 06. 自适应Hash索引 InnoDB对于频繁使用的某些索引值会在内存中基于 B-Tree 索引之上再创键一个哈希索引这也被称为自适应Hash索引。 10. 聚簇、非聚簇索引 mysql的索引类型跟存储引擎是相关的 innodb存储引擎数据文件跟索引文件全部放在ibd文件中 myisam的数据文件放在myd文件中索引放在myi文件中 其实区分聚簇索引和非聚簇索引非常简单只要判断数据跟索引是否存储在一起就可以了。 innodb存储引擎在进行数据插入的时候数据必须要跟索引放在一起如果有主键就使用主键没有主键就使用唯一键没有唯 一键就使用6字节的rowid因此跟数据绑定在一起的就是聚簇索引而为了避免数据冗余存储其他的索引的叶子节点中存储的都 是聚簇索引的key值因此innodb中既有聚簇索引也有非聚簇索引而myisam中只有非聚簇索引。 11. 聚集索引、稀疏索引 聚集索引按每张表的主键构建一棵B树数据库中的每个搜索键值都有一个索引记录每个数据页通过双向链表连接。表数据访问更快但表更新代价高。 稀疏索引不会为每个搜索关键字创建索引记录。搜索过程需要我们首先按索引记录进行操作并按顺序搜索直到找到所需的数据为止。 12. 辅助索引、回表查询 辅助索引是非聚集索引叶子节点不包含记录的全部数据包含了一个书签用来告诉InnoDB哪里可以找到与索引相对应的行数 据。 通过辅助索引查询先通过书签查到聚集索引再根据聚集索引查对应的值需要两次也称为回表查询。 13. 联合索引、最左匹配原则 联合索引是指对表上的多个列的关键词进行索引。 对于联合索引的查询如果精确匹配联合索引的左边连续一列或者多列则mysql会一直向右匹配直到遇到范围查询, ,between,like就停止匹配。Mysql会对第一个索引字段数据进行排序在第一个字段基础上再对第二个字段排序。 14. 覆盖索引 覆盖索引指一个索引包含或覆盖了所有需要查询的字段的值不需要回表查询即索引本身存了对应的值。 16. 主键索引、非主键索引查询 对于select * from 主键XX基于主键的普通查询仅查找主键这棵树 对于select * from 非主键XX基于非主键的查询有可能存在回表过程回到主键索引树搜索的过程称为回表因为非主键索引叶子节点仅存主键值无整行全部信息。 17. 问非主键索引的查询一定会回表吗 不一定当查询语句的要求字段全部命中索引不用回表查询。 如select 主键 from 非主键XX此时非主键索引叶子节点即可拿到主键信息不用回表。 第4章 事务
01. 事务四大特征 事务四大特征原子性一致性隔离性和持久性。 ① 原子性Atomicity 一个原子事务的所有操作要么都完成要么都不完成。 这意味着工作单元中的每项任务都必须正确执行。如果有任一任务执行失败则整个工作单元或事务就会被终止。 即此前对数据所作的任何修改都将被撤销。 如果所有任务都被成功执行事务就会被提交即对数据所作的修改将会是永久性的。 ② 一致性Consistency 事务执行前后数据库状态保持一致。它必须由事务系统和应用开发人员共同来保证 事务系统通过保证事务的原子性隔离性和持久性来满足这一要求 应用开发人员则需要保证数据库有适当的约束(主键引用完整性等)并且保证所实现的业务逻辑不会导致数据的不一致 ③ 隔离性Isolation 多个并发事务对数据块进行操作事务间互不干扰。 换言之在事务或工作单元执行完毕之前其所访问的数据不能受系统其他部分的影响。 ④ 持久性Durability 事务执行完毕对数据的修改是永久的即使系统故障也不会丢失。 持久性表示在某个事务的执行过程中对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。 这样可以保证所作的修改在任何系统瘫痪时不至于丢失。 02. ACID靠什么保证 原子性由undolog日志来保证它记录了需要回滚的日志信息事务回滚时撤销已经执行成功的sql 一致性是由其他三大特性保证程序代码要保证业务上的一致性 隔离性是由MVCC来保证 持久性由redolog日志来保证mysql修改数据的时候会在redolog中记录一份日志数据就算数据没有保存成功只要日志保存成功了数据仍然不会丢失 03. SQL的事务隔离级别 MySQL定义了四种隔离级别包括一些具体规则用于限定事务内外哪些改变是可见的哪些改变是不可见的。 低级别的隔离一般支持更高的并发处理并且拥有更低的系统开销。 ① REPEATABLE READ 可重复读 MySQL数据库默认的隔离级别。该级别解决了READ UNCOMMITTED隔离级别导致的问题。 一个事务执行过程中看到的数据总是和事务启动时看到的数据是一致的。 不过这会导致另外一个棘手问题“幻读”。InnoDB存储引擎通过 多版本并发控制机制 解决了幻读问题。 ② READ COMMITTED 读取提交内容 大多数数据库系统的默认隔离级别但是不是MySQL的默认隔离级别 一个事务开始时只能“看见”已经提交事务所做的改变 一个事务从开始到提交前所做的任何数据改变都是不可见的除非已经提交。 这种隔离级别也支持所谓的“不可重复读”。这意味着用户运行同一个语句两次看到的结果是不同的。 ③ READ UNCOMMITTED 读取未提交内容 在这个隔离级别所有事务都可以“看到”未提交事务的执行结果。 读取未提交数据也被称为“脏读” 读的可能不是最终数据。 本隔离级别很少用于实际应用因为它的性能也不必其他性能好多少而别的级别还有其他更多的优点。 ④ SERIALIZABLE 可串行化 该级别是最高级别的隔离级。 它通过强制事务排序使之不可能相互冲突从而解决幻读问题。 简而言之SERIALIZABLE是在每个读的数据行上加锁。 在这个级别可能导致大量的超时Timeout和锁竞争Lock Contention现象实际应用中很少使用到这个级别但如果用户的应用为了数据的稳定性需要强制减少并发的话也可以选择这种隔离级。 06. 事务并发出现的问题 丢失修改脏读当前事务可以查看到别的事务未提交的数据。不可重读在同一事务中使用相同的查询语句同一数据资源莫名改变了。幻读在同一事务中使用相同的查询语句莫名多出了一些之前不存在的数据或莫名少了一些原先存在的数据。 第5章 锁
01. 解决数据库死锁 ① 预先检测到死锁的循环依赖并立即返回一个错误 ② 当查询的时间达到锁等待超时的设定后放弃锁请求 02. mysql锁的类型 基于锁的属性分 共享锁、排他锁。 基于锁的粒度分类 行级锁innodb 、表级锁 innodb 、myisam、页级锁 innodb引擎、记录锁、间隙锁、临键锁。 基于锁的状态分类 意向共享锁、意向排它锁。 共享锁share lock 共享锁又称读锁简称 S 锁当一个事务为数据加上读锁之后其他事务只能对该数据加读锁而不能 对数据加写锁直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据读取数 据的时候不支持修改避免出现重复读的问题。 排他锁exclusive lock排他锁又称写锁简称 X 锁当一个事务为数据加上写锁时其他请求将不能再为数据加任何锁 直到该锁释放之后其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候不允许其他人同时修改也不允许其他人读 取避免了出现脏数据和脏读的问题。 表锁table lock表锁是指上锁的时候锁住的是整个表当下一个事务访问该表的时候必须等前一个事务释放了锁才能进 行对表进行访问特点粒度大加锁简单容易冲突 行锁行锁是指上锁的时候锁住的是表的某一行或多行记录其他事务访问同一张表时只有被锁住的记录不能访问其他的记 录可正常访问特点粒度小加锁比表锁麻烦不容易冲突相比表锁支持的并发要高 记录锁Record lock:记录锁也属于行锁中的一种只不过记录锁的范围只是表中的某一条记录记录锁是说事务在加锁后锁 住的只是表的某一条记录加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题也避免了在修改的事务未提交前 被其他事务读取的脏读问题 页锁页级锁是 MysQL 中锁定粒度介于行级锁和表级锁中间的一种锁表级锁速度快但冲突多行级冲突少但速度慢。所 以取了折衷的页级一次锁定相邻的一组记录。特点开销和加锁时间界于表锁和行锁之间会出现死锁锁定粒度界于表锁和行 锁之间并发度一般。 间隙锁是属于行锁的一种间隙锁是在事务加锁后其锁住的是表记录的某一个区间当表的相邻ID之间出现空隙则会形成一个 区间遵循左开右闭原则。范围查询并且查询未命中记录查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ重 复读的事务级别中。 临键锁Next-Key lock)也属于行锁的一种并且它是INNODB的行锁默认算法总结来说它就是记录锁和间隙锁的组合临 键锁会把查询出来的记录锁住同时也会把该范围查询内的所有间隙空间也会锁住再之它会把相邻的下一个区间也会锁住。 03. 共享锁、排它锁 共享锁也称为读锁相互不阻塞多个客户在同一时刻可以同时读取同一个资源而不相互干扰。 排他锁也称为写锁会阻塞其他的写锁和读锁确保在给定时间内只有一个用户能执行写入并防止其他用户读取正在写入的同一 资源。 04. 按粒度的锁分类 表级锁: 对当前操作的整张表加锁,实现简单加锁快但并发能力低。 行锁: 锁住某一行如果表存在索引那么记录锁是锁在索引上的如果表没有索引那么 InnoDB 会创建一个隐藏的聚簇索引 加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小并发度高但加锁的开销也最大加锁慢会出现死锁。 Gap 锁也称为间隙锁: 锁定一个范围但不包括记录本身。其目的是为了防止同一事物的两次当前读出现幻读的情况。 Next-key Lock 行锁gap锁。 05. 乐观锁和悲观锁 乐观锁对于数据冲突保持一种乐观态度操作数据时不会对操作的数据进行加锁只有到数据提交的时候才通过一种机制来验 证数据是否存在冲突。 悲观锁对于数据冲突保持一种悲观态度在修改数据之前把数据锁住然后再对数据进行读写在它释放锁之前任何人都不能 对其数据进行操作直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁然后才可以对数据进行操作一般数据库 本身锁的机制都是基于悲观锁的机制实现的。 06. MVCC 1、MVCC 多版本并发控制 MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能用更好的方式去处理读写冲突做到即使有读写冲突时也能做到不加锁非阻塞并发读。 其存在目的是在保证数据一致性的前提下提供一种高并发的访问性能。 对数据读写在不加读写锁的情况下实现互不干扰,从而实现数据库的隔离性在事务隔离级别为读提交和可重复读中使用到。 2、当前读 像select lock in share mode(共享锁)、select for update、update、insert、delete(排他锁)这些操作都是一种当前读为什么叫当前读 就是它读取的是记录的最新版本读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁。 3、快照读提高数据库的并发查询能力 像不加锁的select操作就是快照读即不加锁的非阻塞读 快照读的前提是隔离级别不是串行级别串行级别下的快照读会退化成当前读 之所以出现快照读的情况是基于提高并发性能的考虑快照读的实现是基于多版本并发控制即MVCC 可以认为MVCC是行锁的一个变种但它在很多情况下避免了加锁操作降低了开销 既然是基于多版本即快照读可能读到的并不一定是数据的最新版本而有可能是之前的历史版本 4、当前读、快照读、MVCC关系 MVCC多版本并发控制指的是维持一个数据的多个版本使得读写操作没有冲突快照读是MySQL为实现MVCC的一个非阻塞读功能。MVCC模块在MySQL中的具体实现是由三个隐式字段undo日志、read view三个组件来实现的。 07. MVCC解决的问题 数据库并发场景有三种分别为 1、读读不存在任何问题也不需要并发控制 2、读写有线程安全问题可能会造成事务隔离性问题可能遇到脏读、幻读、不可重复读 3、写写有线程安全问题可能存在更新丢失问题 MVCC是一种用来解决读写冲突的无锁并发控制也就是为事务分配单项增长的时间戳为每个修改保存一个版本版本与事务 时间戳关联读操作只读该事务开始前的数据库的快照所以MVCC可以为数据库解决一下问题 1、在并发读写数据库时可以做到在读操作时不用阻塞写操作写操作也不用阻塞读操作提高了数据库并发读写的性能 2、解决脏读、幻读、不可重复读等事务隔离问题但是不能解决更新丢失问题 第6章 慢查询
01. 慢查询的处理办法 1、开启慢查询日志准确定位到哪个sql语句出现了问题 2、分析sql语句看看是否load了额外的数据可能是查询了多余的行并且抛弃掉了可能是加载了许多结果中并不需要的列对语句进行分析以及重写 3、分析语句的执行计划然后获得其使用索引的情况之后修改语句或者修改索引使得语句可以尽可能的命中索引 4、如果对语句的优化已经无法进行可以考虑表中的数据量是否太大如果是的话可以进行横向或者纵向的分表。 02. MySQL优化流程 通过慢日志定位执行较慢的SQL语句利用explain对这些关键字段进行分析根据分析结果进行优化 03. EXPLAIN的关键字段 explain关键字用于分析sql语句的执行情况可以通过他进行sql语句的性能分析。 type表示连接类型从好到差的类型排序为 system系统表数据已经加载到内存里。const常量连接通过索引一次就找到。eq_ref唯一性索引扫描返回所有匹配某个单独值的行。ref非主键非唯一索引等值扫描const或eq_ref改为普通非唯一索引。range范围扫描在索引上扫码特定范围内的值。index索引树扫描扫描索引上的全部数据。all全表扫描。 key显示MySQL实际决定使用的键。 key_len显示MySQL决定使用的键长度长度越短越好 Extra额外信息 Using filesortMySQL使用外部的索引排序很慢需要优化。Using temporary使用了临时表保存中间结果很慢需要优化。Using index使用了覆盖索引。Using where使用了where。 第7章 日志
01. MySQL的日志log redo log: 存储引擎级别的logInnoDB有MyISAM没有该log关注于事务的恢复.在重启mysql服务的时候根据redo log进 行重做从而使事务有持久性。 undo log是存储引擎级别的logInnoDB有MyISAM没有保证数据的原子性该log保存了事务发生之前的数据的一个版 本可以用于回滚是MVCC的重要实现方法之一。 bin log数据库级别的log关注恢复数据库的数据。 02. redo log、binlog的区别 ① redo log 是InnoDB引擎特有的只记录该引擎中表的修改记录。 binlog是MySQL的Server层实现的会记录所有引擎对数据库的修改。 ② redo log是物理日志记录的是在具体某个数据页上做了什么修改 binlog是逻辑日志记录的是这个语句的原始逻辑。 ③ redo log是循环写的空间固定会用完 binlog是可以追加写入的binlog文件写到一定大小后会切换到下一个并不会覆盖以前的日志。 05. crash-safe InnoDB通过redo log保证即使数据库发生异常重启之前提交的记录都不会丢失这个能力称为crashsafe。 06. WAL技术 WAL的全称是Write-Ahead Logging它的关键点就是先写日志再写磁盘。 事务在提交写入磁盘前会先写到redo log里面去。 如果直接写入磁盘涉及磁盘的随机I/O访问涉及磁盘随机I/O访问是非常消耗时间的一个过程相比之下先写入redo log后面再找合适的时机批量刷盘能提升性能。 07. 两阶段提交 为了保证binlog和redo log两份日志的逻辑一致最终保证恢复到主备数据库的数据是一致的采用两阶段提交的机制。 执行器调用存储引擎接口存储引擎将修改更新到内存中后将修改操作记录redo log中此时redo log处于prepare状态。存储引擎告知执行器执行完毕执行器生成这个操作对应的binlog并把binlog写入磁盘。执行器调用引擎的提交事务接口引擎把刚刚写入的redo log改成提交commit状态更新完成。 08. 实现主备一致 MySQL通过binlog二进制日志实现主备一致。 binlog记录了所有修改了数据库或可能修改数据库的语句而不会记录select、show这种不会修改数据库的语句。 在备份的过程中主库A会有一个专门的线程将主库A的binlog发送给备库B进行备份。其中binlog有三种记录格式 ① statement 记录对数据库进行修改的语句本身有可能会记录一些额外的相关信息。 优点binlog日志量少IO压力小性能较高。 缺点由于记录的信息相对较少在不同库执行时由于上下文的环境不同可能导致主备不一致。 ② row 记录对数据库做出修改的语句所影响到的数据行以及对这些行的修改。 比如当修改涉及多行数据会把涉及的每行数据都记录到binlog。 优点能够完全的还原或者复制日志被记录时的操作。缺点日志量占用空间较大IO压力大性能消耗较大。 ③ mixed 混合使用上述两种模式一般的语句使用statment方式进行保存 如果遇到一些特殊的函数则使用row模式进行记录。 MySQL自己会判断这条SQL语句是否可能引起主备不一致如果有可能就用row格式否则就用statement格式。 但是在生产环境中一般会使用row模式。 09. 问只靠binlog可以数据库崩溃时恢复吗 不可以。 历史原因 InnoDB在作为MySQL的插件加入MySQL引擎家族之前就已经是一个提供了崩溃恢复和事务支持 的引擎了。InnoDB接入了MySQL后发现既然binlog没有崩溃恢复的能力那引入InnoDB原有的 redo log来保证崩溃恢复能力。 实现原因binlog没有记录数据页修改的详细信息不具备恢复数据页的能力。binlog记录着数据行的增删改 但是不记录事务对数据页的改动这样细致的改动只记录在redo log中。当一个事务做增删改时 其实涉及到的数据页改动非常细致和复杂包括行的字段改动以及行头部以及数据页头部的改动 甚至btree会因为插入一行而发生若干次页面分裂那么事务也会把所有这些改动记录下来到redo log中。因为数据库系统进程crash时刻磁盘上面页面镜像可以非常混乱其中有些页面含有一些 正在运行着的事务的改动而一些已提交的事务的改动并没有刷上磁盘。事务恢复过程可以理解为 是要把没有提交的事务的页面改动都去掉并把已经提交的事务的页面改动都加上去这样一个过 程。这些信息都是binlog中没有记录的只记录在了存储引擎的redo log中。操作写入binlog可细分为write和fsync两个过程write指的就是指把日志写入到文件系统的page cache并没有把数据持久化到磁盘,fsync才是将数据持久化到磁盘的操作。通过参数设置 sync_binlog为0的时候表示每次提交事务都只write不fsync。此时数据库崩溃可能导致部分提交 的事务以及binlog日志由于没有持久化而丢失。 第8章 MySQL复制
01. 主从复制 MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。 MySQL默认采用异步复制方式可以方便的实现数据的多处自动备份不仅能增加数据库的安全性还能进行读写分离提升数据库负载性能。 主从复制流程 ① 在事务完成之前主库在binlog上记录这些改变完成binlog写入过程后主库通知存储引擎提交事物 ② 从库将主库的binlog复制到对应的中继日志即开辟一个I/O工作线程 I/O线程在主库上打开一个普通的连接然后开始binlog dump process将这些事件写入中继日志。 从主库的binlog中读取事件如果已经读到最新了线程进入睡眠并等待ma主库产生新的事件。 读写分离即只在MySQL主库上写只在MySQL从库上读以减少数据库压力提高性能。 02. 主从同步的应用场景 1、在业务复杂的系统中有这么一个情景有一句sql语句需要锁表导致暂时不能使用读的服务那么就很影响运行中的业 务使用主从复制让主库负责写从库负责读这样即使主库出现了锁表的情景通过读从库也可以保证业务的正常运作。 2、做数据的热备 3、架构的扩展。业务量越来越大I/O访问频率过高单机无法满足此时做多库的存储降低磁盘I/O访问的频率提高单个 机器的I/O性能。 第9章 数据库优化 暂无
