网站导航
> 文档中心 > 【MySQL数据库】2022年MySQL必知必会，基础内容与常见面试题

【MySQL数据库】2022年MySQL必知必会，基础内容与常见面试题

网友: 文档中心 2022-05-13 09:06:41

大家好，我是ly甲烷😜
经过三次整理修改，更有逻辑更易记忆的MySQL面试总结，👇👇
有用可以收藏❤️，我相信这个早晚能帮到你

文章目录

  • 一、基础
    • 数据库三大范式
    • exists 和 in 的区别
    • 连接查询
  • 二、引擎
    • myISAM 和 InnoDB的区别？
    • Mysql的LRU算法
      • 为什么需要LRU算法
      • LRU算法介绍
      • LRU算法问题
      • MySQL中的LRU算法
    • InnoDB锁机制
    • MyISAM引擎查询快的原因
  • 三、事务
    • 事务的四大特性ACID
    • 脏读、不可重复读、幻读
      • 脏读
      • 不可重复读
      • 幻读
    • 事务隔离级别
    • MVCC
  • 四、索引
    • 索引简介
    • 什么是索引？
    • 索引的优缺点
    • 索引使用场景
      • where
      • order by
      • 索引覆盖
    • 创建索引的原则
    • 索引类型
    • 索引数据结构
    • 使用索引查询一定能提高性能吗
    • 百万级别数据如何删除
    • B树、B+树
      • 区别
      • 使用B树的好处
      • 使用B+树的好处
    • 聚簇索引
    • 联合索引
      • 概念
      • 为什么需要注意联合索引的顺序？
    • 索引失效
  • 五、日志
    • MySQL中的日志
      • 1. 重做日志redo log
      • 2. 回滚日志
      • 3. 二进制日志 bin log
      • 4. 错误日志
      • 5. 查询日
      • 6. 慢查询日志
    • MySQL中如何保持数据不丢失
  • 六、优化
    • 数据库结构优化
    • 大表优化

在这里插入图片描述

一、基础

数据库三大范式

  1. 第一范式：每个列都不可再拆分🌌
  2. 第二范式： 满足第一范式的基础上，👉非主键完全依赖于主键👈，不能是只依赖于主键的一部分 。【消除非主键对主键的部分依赖】
  3. 第三范式：在第二范的基础上，👉非主键只依赖与主键👈，不依赖于其他主键。【消除传递依赖】

📙1.每列不可再分，这很基本也很合理
📙2.非主键完全依赖主键，我是主键我得能找到你呀，所有人必须完全依赖我
📙2.非主键只依赖主键，所有人只能依赖我，免得还得托人找你

exists 和 in 的区别

select * from A  where id in(select id from B)

in 和 exists的查询方式

  • 使用in： in查询先查子查询的表，之后，将内外表做个笛卡尔积，然后按条件进行筛选。
  • 使用exists：直接查询主查询的外表，然后根据表的每一条记录，依次判断where后面的子表的条件是否成立。（用到了数据库索引）

结论：

  1. 如果两个表大小相当，用in和用exists差别不大
  2. 如果一个大表、一个小表、👉主查询是小表的用exists 、子查询是小表的用in
    因为in查询条数是主表和子表记录的笛卡尔积， 但是in() 里的子表是缓存在内存中的。
    exists查询条数是主表的记录数。但是查子表用了数据库索引，没有在内存中快。

not in 和 not exists：
not in 为了证明数据确实不存在，需要扫描全表，没有用到索引
not exists 的子查询依然能用表上的索引，所有无论哪个表大，not exists 都比not in 要快

📙内表小，用 in 。直接把符合条件的内表记录加载到内存里，查外表和所有内表记录一比，很快啊
📙内表大，用 exists 。内表太大，放内存不合适。直接查外表，用索引看看内表满不满足条件就行了

连接查询

内连接： inner ——左右两个都有，能接一起的才有
左外连接：left outer——以左边表内容为主，左边表内容全有，右边表能和左边表接一起的才有， 左边表有而右边表无右边内容为null
右外连接：right outer——以右边的表为主，右边表的内容全有
全外连接： full outer—— 即以from [Book] full outer join [Student]中先以Book表进行左外连接，然后以Student表进行右外连接。
交叉连接：cross ——笛卡尔积 n * m 拼接
在这里插入图片描述
在这里插入图片描述

二、引擎

myISAM 和 InnoDB的区别？

  1. ✊ InnoDB支持事务，而MyISAM不支持。（这是MySQL把默认引擎换成InnoDB的关键原因）
  2. 👉InnoDB支持MVCC多版本并发控制
  3. 👉InnoDB支持外键
  4. 👉InnoDB最小锁粒度是行锁，MyISAM是表锁
  5. ✌️ InnoDB不保存表的具体行数，而MyISAM用一个变量保存了整个表的行数
  6. 👉InnoDB主键索引是聚簇索引，主键索引的叶子节点存储着行数据，因此主键索引非常高效。非主键索引叶子节点存的是主键索引，可能得回表查询【满足覆盖索引得话就不用回表查询】 。MyISAM索引的叶子节点存的是数据地址，需要再一次寻址才能得到数据
  7. InnoDB存储文件所有表都存在一个数据文件中，MyISAM有三个文件：表定义文件、数据文件、索引文件

📙InnoDB有：事务、MVCC、外键、行锁、聚簇索引 ， 所有表数据存在一个数据文件中
📙MyISAM专门存了count(*)，有三个文件：表定义文件，数据文件，索引文件

Mysql的LRU算法

为什么需要LRU算法

MySQL在查询数据时，对于InnoDB存储引擎而言，会先将磁盘上的数据以页为单位加载进内存，以缓存页的形式存放在Buffer Pool中。
Buffer Pool是InnoDB的一块内存缓冲区，在MySQL启动时，会按照配置的缓存页的大小，将Buffer Pool缓存区初始化为许多个缓存页，默认情况下，缓存页大小为16KB。
刚开始Buffer Pool 中这些缓存页都是处于空闲状态。随着运行时间越来越长，Buffer Pool 满了。就得淘汰掉之前的缓存页。

📙查询数据以缓存页的形式放入Buffer Pool，满了得置换

LRU算法介绍

LRU是Least Recently Used的简写， 用来淘汰最久未使用的数据。
它通过维护一个链表，每当访问了某个数据时，就将这个数据加入到链表头部，如果数据本身存在于链表中，九江数据冲链表中间移动到链表头部，这样链表尾部的数据就一定是最久未被使用的数据了，在缓存不足时将其淘汰。
InnoDB存储引擎层内部维护了一个链表，链表中的元素就算指向缓存页的指针。

LRU算法问题

全表扫描
当出现全表扫描的时候，InnoDB会将该表中的数据页全部从磁盘文件加载进缓存中。如果刚好这个表很大且是一个不常用的表，那可能把常用的缓存页给淘汰了。最终导致，Buffer Pool缓存的命中率明显下降，SQL性能也明显下降，由于之前的缓存页要去磁盘读，磁盘IO性能也下降。
所有，简单的LRU算法，碰到全表扫描时，就会存在性能下降问题，这在高并发场景下可能成为性能瓶颈

InnoDB 预读
预读是InnoDB引擎的一个优化机制，当你从磁盘上读取某个数据也，InnoDB可能会将与这个数据页相邻的其他数据页页读取到Buffer Pool中。
（从磁盘读取数据是随机IO，性能差，InnoDB猜测你可能需要下一页的数据，就会连着读取多个数据页，这是顺序读取，性能高）

在这两种情况下会触发预读机制：

  1. 顺序的访问了上一个区的多个数据页，这个数量超过一个阈值，InnoDB就会认为你对下一个区的数据也感兴趣，触发预读，默认值为56
  2. 如果Buffer Pool 中已经缓存了同一个区数据页的个数超过13时，InnoDB就会将这个区的其他数据页页读取到Buffer Pool中。默认关闭的

📙全表扫描， 一次全表扫描，把之前常用的缓存页全置换了，那可不行
📙预读机制， 为了更快，可能顺序多读一页，结果读了一页没用的，把别人常用的干掉了，不合理

MySQL中的LRU算法

MySQL的优化思路是，对数据进行冷热分离，将LRU链表分成两个部分，一部分用来存冷数据，也就是刚从磁盘读进来的数据，另一部分用来存热点数据，也就是经常被访问到的数据。冷数据默认占LRU链表的37%

优化过的LRU链表工作流程：
从磁盘读取数据页后先放在冷数据区的头部，如果这些缓存页在1秒之后被访问，就将缓存页移动到热数据区的头部，如果一秒之后没访问，就不动它。1秒这个数值，由参数innodb_old_blocks_time控制。
由预读和全表扫描带来的缓存页1秒后没人访问，它们就会一直呆在冷数据区，再需要淘汰的时候，首先淘汰它们
MySQL还做了优化，就是如果缓存页处在热数据区的前 1/4区域，就不用把它移动到头部了，因为移动链表需要加锁，会存在锁竞争

📙 冷热分离，冷链37%，用一次不用的就在这呆着等会淘汰了，1s后还用就进热链
📙 本来就在热链前面的（1/4）数据就不要频繁动了，知道你是常用的

InnoDB锁机制

行锁： 共享锁、排他锁
意向锁：一个事务要给整张表加排他锁的时候，首先得先判断表中是否已经被加上行锁或表锁。判断表中是否加了行锁，得一行行遍历，所以引入意向锁。加行锁的时候也加上意向锁，直接看有没有意向锁就可以知道有没有行锁了

📙 意向锁：行锁标志位，有意向锁代表表里有行锁

MyISAM引擎查询快的原因

InnoDB 支持事务， 所以查询的时候会有一个MVCC的比较。会消耗一部分性能
查询的时候如果走了索引，如果不是主键索引的话可能会回表。
MyISAM只加载索引地址进入内存，可以加载的索引数量多，如果数据量很大体现更明显。
如果数据量小直接走主键的聚簇索引，InnoDB查询速度可能更快

📙InnoDB事比较多点，但是他索引和数据在一块，如果能直查到，速度还是很快的。
📙MyISAM 啥都不管，查询是原子操作，没用事务，直接把索引加载到内存，然后去查磁盘，数据多的话还是快点，但是不支持事务很伤啊

三、事务

事务的四大特性ACID

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态转变为另一种一致性状态。
原子性：一个事务是不可再分割的整体，要么都执行，要么都不执行
一致性：一个事务执行前后都必须处于一致性状态
隔离性：一个事务不受其他事务的干扰，多个事务互相隔离
持久性：一个事务一旦提交了，则永久的持久化到本地。

📙ACID： 原子性、一致性、隔离性、持久性

脏读、不可重复读、幻读

脏读

一个事务读取到了其他事务没有提交的数据。
解决方法：读取是加共享锁，读取完立马就能释放锁，更新时加排他锁，更新提交后才能释放锁

不可重复读

一个事务对同一行记录两次读取结果不同。比如在两次读这行数据的中间其他人改了这个数据
解决方法：加共享锁和排他锁，都是在事务提交后才才释放锁

幻读

一个事务对同一范围的两次查询结果不同，
比如，事务1修改了一个范围内的数据。事务2在事务1刚修改完数据还没提交的时候又给这个范围插入了一条数据，然后事务1发现范围内还有没修改的数据行。像出现了幻觉
解决方法：需要加范围锁

事务隔离级别

【MySQL数据库】2022年MySQL必知必会，基础内容与常见面试题
读未提交最不安全，读已提交就不会出现脏读，可重复读不会出现不可重复读，可串行化最安全

📙脏读：读未提交。——写的时候提交才释放锁，读的时候读完立马释放锁，可以解决这个问题
📙不可重复读：读已提交的，但是一个事务内两次读的不一样。——读写都是提交后才释放锁，可以避免这个问题
📙幻读：一个事务范围读，两次结果不同。——由于加的是行锁，读写都提交才释放锁，不存在不可重复读，但是在事务两次读之间是可以插入新数据的，造成幻读。直接串行化可以解决幻读，用MVCC也可以

MVCC

MVCC即多版本并发控制，MVCC 在 MySQL InnoDB 中实现主要是为了提高数据库并发性能，做到读可不加锁，读写不冲突。并发性能很高。
MVCC 中默认的读是非锁定的一致性读， 也称快照读。读取的是记录的可见版本， 当读取的的记录正在被别的事务并发修改时， 会读取记录的历史版本。

  1. 当前读和快照读：
    当前读：共享锁和排他锁这些操作都是当前读，读取的是记录的最新版本，读取时要保证其他并发事务不能修改当前记录，会对读取的记录加锁
    快照读：不加锁的select操作就是快照读，串行级别下快照读会退化成当前读。快照读读到的不一定是数据的最新版本，可能是历史版本。

  2. 隐式字段
    每行记录除了我们自定义的字段外，还要数据库隐式定义的DB_TRX_ID，DB_ROLL_PRY，DB_ROW_ID
    DB_TRX_ID ： 6byte，最近修改事务ID，记录创建/最后依次修改 该记录的事务ID
    DB_ROLL_PRY： 7byte，回滚指针，指向这条记录的上一个版本 （存储于rollback segment里）
    DB_ROW_ID: 6byte， 隐含的自增ID，如果没有主键，InnoDB会自动以DB_ROW_ID产生一个簇族索引
    实际还有一个删除flag隐藏字段

  3. undo日志
    insert undo log： 代表事务在insert 新纪录时产生的undo log， 只在事务回滚时需要，并且事务提交后可以立即被丢弃。
    update undo log： 事务进行修改删除时产生的undo log， 不仅在事务回滚的时候需要，在快照读的时候也需要，所以不能随便删除，只要在快速读或事务回滚不涉及该日志时，对应日志才会被purge线程统一清除。

  4. MVCC更新过程
    开始是这样，假设 隐含ID = 1， 事务ID和回滚ID为空
    在这里插入图片描述
    当事务1更改该行的值时：
    ①加排他锁
    ②记录redo log
    ③ 记录undo log（把修改前的值copy的undo log， 即下图下行）
    ④修改当前行的值，填写事务编号，使回滚指针指向undo log中修改前的行
    在这里插入图片描述
    当再来一个事务2来更改改行的值时， 与事务1相同，但此时undo log 中有两行记录，并通过回归指针连在一起。 但InnoDB中存在 purge线程，它会查询那些比现在最老的活动事务还早的undo log 并删除它们
    在这里插入图片描述

  5. Read View 读视图
    Read View 就是事务进行快照读操作的时候生产的读视图，主要用来做可见性判断，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID。

📙MVCC： 多版本并发控制，可以使得只有写写的时候加锁，读读、读写、写读都可以不加锁。
InnoDB是在undo log中实现多版本并发，通过undolog可以找回数据的历史版本。
找回的数据历史版本可以提供给用户读,也可以在回滚的时候覆盖数据页上的数据

四、索引

索引简介

面试官：说说你对数据库索引的理解？

📙答： 数据库索引是对数据库表中一列或多列进行排序的一种数据结构，通过索引可以加快数据检索，索引相当于图书的目录，可以根据目录中页码快速查找到所需内容。当然维护索引也会耗费时间和占用物理空间。
索引的数据结构和具体的存储引擎相关，常用的InnoDB和MyISAM默认索引都是 B+树，而Menony是Hash表。索引类型有普通索引、唯一索引、主键索引、组合索引、全文索引，平时用的比较多的是主键索引和组合索引。在索引使用过程中，可能会出现回表、覆盖索引、最左匹配、索引下推等问题【引出索引相关问题】

回表：InnoDB的只有主键索引是聚簇索引，找到索引就找到行数据了。其他索引都是非聚簇索引，叶子节点存的是主键索引，如果不满足覆盖索引的话就得回表查询，去查主键索引

索引覆盖：如果要查询得字段，都建立过索引，也就是我用非聚簇索引查到得数据已经包含了需要查询的字段，那就不用回表了

最左匹配：对于联合索引，MySQL会左往右按顺序匹配。
= 和 in 的匹配MySQL会自动优化成能识别的顺序，> < between like不能优化
假设有个联合索引是（a, b, c, d） 查询条件是where a = 1 and b = 2 and c > 3 and d = 4 这样 d就用不到索引，
如果索引是（a,d,b,c）的话就都可以用到索引

索引下推：减少回表次数，提高查询效率。
没用索引下推的时候，对于联合索引且不是主键索引(a, b) , 假设查询语句是 where a = 1 and b = 2 用到了索引，但是MySQL是server先获取满足 a = 1 的第一条记录，然后回表查询该记录完整数据，并返回给server层。server层判断该记录 b 是否 = 2，如果不是就丢弃此数据。 其实这过程很不对劲，联合索引本身存的是主键索引的位置还要是索引的列信息（a,b列的信息），这边本身就有b的值，可以判断b是否==2， 还去回表查主键再查出整条数据，排除b != 2 的。
该有的总会有，索引下推，数据库服务器把判断条件传递给存储引擎，如果b不满足就不回表查询了

📙索引原理就是把无序的数据变成有序的查询。

什么是索引？

索引是一种特殊的文件，包含着对数据库表里所有记录的引用指针，也是要占用物理内存空间的
通俗的说，索引就相当于目录，为了方便查找书中的内容。通过对内容建立索引形成目录

索引的优缺点

优点：可以大大加快数据的检索速度

缺点：

  1. 创建何维护索引都需要耗费时间，并且表中数据增删改的时候，索引也得动态维护，会降低增删改的速度。
  2. 索引也得占据物理空间

索引使用场景

where

where后面按索引字段进行检索

order by

当我们用order by 查询某个字段排序时，如果没有建立索引，会进行全表查询再排序，
如果建立了索引，索引本身就是有序的，可以直接取按顺序取出，还可以分页取出
order by 的字段出现在where条件中才能用索引，否则索引失效

索引覆盖

如果要查询的字段都建立过索引，那么引擎会直接再索引表中查询，不会访问原始数据

创建索引的原则

  1. 最左前缀匹配原则
  2. 较频繁作为查询条件的才去做创建索引
  3. 频繁更新的字段不适合做索引，频繁插入、删除数据的表也不适合加索引
  4. 查询很少涉及的列，重复值多的列，定义为text、image和bit 的数据类型不要建立索引
  5. 尽量扩展索引而不是新建索引，比如表中已经有了a索引，现在要加入（a,b）索引，那么只需修改原来的索引即可
  6. 定义有外键的数据列一定要建立索引——【外键列没有索引的时候，对表的并发DML操作容易引起阻塞，降低并行度】

最左前缀匹配原则
联合索引必须得按顺序进行匹配，比如联合索引是 （name，age） 那查where age = 18 就不能用索引， 查age = 18 and name = "张三"可以用的索引（等号会被MySQL优化成能识别的顺序）

📙最左前缀匹配、频繁查询字段建索引、排序建索引、外键建索引，扩展索引而不是新建索引。
📙频繁更新、查询很少、重复值多，text，image不建索引。

索引类型

  1. 主键索引——不允许重复，不允许null，一个表只能有一个主键
  2. 唯一索引——数据列不允许重复，可以未null值，一个表可以创建多个唯一索引
  3. 普通索引——基本的索引类型，没有唯一性限制，允许为null值
  4. 组合索引——多个列值组合成一个索引，专门用于组合搜索，效率大于索引合并
  5. 全文索引——是目前搜索引擎使用的一种关键技术，对文本内容进行分词、搜索

索引数据结构

InnoDB底层的索引数据结构是B+树，原因是B+树相对其他平衡二叉树同样数据量下层数更小，层数越小磁盘IO次数越少，性能越高

使用索引查询一定能提高性能吗

通常情况下，通过索引查询数据库比全表扫描要快。但是是有代价的，索引需要物理空间存储，也需要维护。不必要的索引反而会使查询变慢

百万级别数据如何删除

由于索引需要额外的维护成本，修改、删除数据都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，降低增删改的执行效率。
删除百万级别数据库的时候，查询MySQL官方手册可以知道删除数据的速度和创建的索引的数量成正比
所以应该先删除索引，然后删除数据，删除完数据以后，重新创建索引，因为此时数据较少了所以创建索引也会很快
如果直接删除数据，索引一直跟着被修改，很慢，万一再删除失败导致回滚就更坑了

📙百万数据删除，先删除索引，再删数据，再建索引。

B树、B+树

区别

  1. B树中键和值存在内部节点和叶子节点，B+内部节点只有键，叶子节点才存键和值
  2. B+树的叶子节点有一条链相连，这样进行范围查询的时候可以直接过去，而B树的叶子节点是独立的，范围查询就得不停的中序遍历

使用B树的好处

B树可以在内部节点存键和值，因此把频繁访问的数据放在靠近根节点的地方。将会大大提高热点数据的查询效率。
这使得B树在重复多次查询的场景中更加高效

使用B+树的好处

由于B+树内部节点只有键，内部节点可以存放更多的键，使得B+树相对B树更加矮胖，进一步减少IO次数
B+树查询效率更加稳定，都是得查到叶子节点
B+树的叶子节点由一条链相连，所以当需要全数据遍历，或者范围查询的时候，B+树只需要O(logN)的时间找到最小的节点，然后通过链遍历即可

B+树在满足簇族索引和覆盖索引的时候不需要回表查询
B+树叶子节点可能存的是当前key值+主键索引，也可能是+整行数据，这就是聚簇索引和非聚簇索引
InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引，如果没有唯一键，就隐式的生成一个键来建立聚簇索引

B树与B+树： https://blog.csdn.net/weixin_44179010/article/details/124014522
📙B树比其他平衡二叉树的树高更低，树高越低，磁盘IO次数越少，效率越高。
📙B+树是B树的变种，主要区别是B树中键和值存在于内部节点和叶子节点， B+树内部节点只有键，叶子节点存键和值。
📙B+树叶子节点有一条链相连，这样范围查询可以直接遍历过去，B树想进行范围查询只能中序遍历
B树好处是把热点数据放靠近根节点的地方查的快，B+树好处是比B树更矮，查询必走到叶子节点，更稳定，范围查询方便

聚簇索引

聚簇索引就是数据存储和索引放到了一块，找到索引也就找到了数据
非聚簇索引：数据和索引分开存储，叶子节点存的是主键索引，需要回表查询

联合索引

概念

MySQL可以使用多个字段同时建立索引，叫做联合索引，在联合索引中，如果想要命中索引，需要按照建立索引的字段顺序挨个使用，否则无法命中

为什么需要注意联合索引的顺序？

MySQL使用索引时需要索引有序，假设建立了“name，age，school”的联合索引，那么索引的排序就是先按name排序，
如果name相同按 age 排序，age相同再按 school 排序
查询的时候，此时索引仅按照name 严格有序，因此必须首先使用 name字符进行等值查询，
之后对于匹配的列，是按照age 字段严格有序，此时可以使用age 字段用作索引查找，
以此类推。因此建立联合索引的时候应该注意索引列的顺序，将查询需求频繁或者字段选择性高的列放在最前面，当然特例可以定制

这就是最左匹配原则

索引失效

  1. 查询条件油 or ，除非所以的查询条件都建立有索引，否则索引失效
  2. like 查询以 %开头
  3. 字符串的索引字段，在查询的时候必须加引号
  4. 索引列上参与计算会导致索引失效
  5. 违背最左原则
  6. 如果MySQL估计全表查询要比走索引快，会不使用索引
  7. not in 不走索引，not exists走索引

📙索引失效：查询条件用or，却有条件没建索引。like查询以%开头。字符串作为索引不加引号。索引列参与计算。违背最左原则。MySQL觉得索引慢。not in语句。

五、日志

MySQL中的日志

MySQL中有六种日志，重做日志、回滚日志、二进制日志、错误日志、慢查询日志、一般查询日志

1. 重做日志redo log

存储引擎层生成的日志，为了保证数据可靠性。
根据事务的ACID特性，为了持久化，最简单的做法是每次事务提交的时候把涉及修改的数据页全部刷新到磁盘中。但会有严重的效率问题：

  1. InnoDB是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，把完整的页刷到磁盘太费资源
  2. 如果一个事务涉及多个数据页，并且这些数据页在物理上不连续，使用随机IO写入性能差

redo log 记录的是 “物理级别”上的页修改操作，比如页号xxx、偏移量xxx写入xxx数据。当MySQL写入数据时，先写redo log 然后redo log 根据某种方式持久化到磁盘，变成redo log file，用户数据则在buffer中，避免频繁IO操作。

2. 回滚日志

回滚日志，注意为了保证数据的原子性，保存了事务发生之前的一个版本，可以用于回滚，INSERT语句对应一条DELETE的undo log， UPDATE对应一个相反的UPDATE 的 undo log ，这样发生错误时就能回滚到事务之前的数据状态。同时undo log 也是MVCC（多版本并发控制）实现的关键

3. 二进制日志 bin log

记录了所有的数据DDL（数据定义语言）语句和DML（数据操纵语言）语句，不包括数据查询语句。语句以事件的形式保存，描述了数据的更改过程。用于灾难时的数据恢复，主从复制。

4. 错误日志

记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
是MySQL中最重要的日志之一，放数据库出现任何故障导致无法使用时，可以首先查看此日志

5. 查询日

记录客户端所有语句，而二进制日志不包含查询数据的语句

6. 慢查询日志

记录了所有执行时间超过参数设置值，并且扫描记录数不小于设置值的所有SQL语句的日志，默认情况下管理语句和不使用索引进行查询的语句并不会记录到慢查询日志中。

MySQL中如何保持数据不丢失

redo logo： 在MySQL中数据是先缓存在缓冲池，然后再以某种方式刷新到磁盘，如果宕机导致缓冲池中数据丢失，则读取磁盘上的redo log file 进行数据恢复。

由于文件系统对一次大数据页大多数情况下不是原子操作，如果服务器宕机了，可能值做了部分写入，如果突然断电，就是部分写问题。由于redo log 记录的是对页的物理修改，这个页已经发生损坏了，所以没法重做。
为了解决这个问题， InnoDB实现了double write buffer，简单来说就是写数据页之前，先把这个数据页写到一块独立的物理文件位置，然后再写到磁盘。这样如果数据页出现损坏，可以通过该页的副本还原该页，然后再redo log 重做。

redo log 记录物理级别的页修改操作，提高读写性能，在之后系统崩溃重启后可以把事务所做的修改都恢复出来。
如果对数据页进行修改的时候宕机了，会出现部分写问题，double write buffer可以解决这个问题.

MVCC就是在undo log中来实现多版本并发的，通过undo log 找回数据的历史版本。可以提供用户读，也可以用作回滚.

bin log 记录所有修改过程，可以用于数据恢复，也可以用cannel组件订阅bin log 日志做数据库主从复制.

错误日志记录错误，查询日志记录所有语句.

慢查询日志记录在 MySQL 中响应时间超过阀值的语句.

六、优化

数据库结构优化

  1. 字段很多的表分解成多个表——有些字段较多的表，如果某些字段的使用频率很低，可以将这些字段分离出来形成新表，因为当一个表的数据量很大时，会由于使用频率低的字段存在而变慢
  2. 增加中间表——对于需要经常联合查询的表，建立中间表，将需要联合查询的数据插入到中间表中
  3. 增加冗余字段——合理的冗余字段可以提高查询速度，表的规范化程度越高，表之间的关系越多，需要注意的是冗余字段在一个表中改了，其他表中也得更新

大表优化

  1. 优化SQL语句，加索引
  2. 限定数据范围——禁止不带任何限制数据范围的条件查询语句，比如查询用户订单时，控制在一个月的范围内
  3. 主从复制，读 / 写分离——数据库拆分，主库负责写、从库负责读
  4. 使用Reidis缓存
  5. 分库分表——比如用户的表中既有登录信息又有用户基本信息，可以把用户表拆分成两个单独的表，甚至可以放到单独的库进行分库
    可以使行数据变小，减少IO次数，简化表结构，易于维护。 缺点是主键会出现冗余

📙数据库结构优化： 字段多的拆分，经常联表查的加个中间表，增加冗余字段.

📙大表优化： SQL优化，加索引。 限定读数据范围。主从分离，读写分离。加入Redis缓存。分库分表.

思维导图原件：https://www.processon.com/embed/626e83a16376891e1c1ee8ff

如果发现有错误的地方，欢迎大家提出批评指正

💖致力于分享记录各种知识干货，关注我，让我们一起进步，互相学习，不断创作更优秀的文章。
💖💖希望大家能多多支持，你们的支持是我最大的动力！不要忘了三连哦 👍 💬 ⭐️
上篇：【Redis】基础概念及常见面试题
后期预告：Spring全家桶

网络标签：

相关问题