网站建设s,蓝色手机网站模板,wordpress域名邮箱,网站app开发公司Yan-英杰的主页
悟已往之不谏 知来者之可追
C程序员#xff0c;2024届电子信息研究生 目录
1、三大范式
2、DML 语句和 DDL 语句区别
3、主键和外键的区别
4、drop、delete、truncate 区别
5、基础架构
6、MyISAM 和 InnoDB 有什么区别#xff1f;
7、推荐自增id作为… Yan-英杰的主页
悟已往之不谏 知来者之可追
C程序员2024届电子信息研究生 目录
1、三大范式
2、DML 语句和 DDL 语句区别
3、主键和外键的区别
4、drop、delete、truncate 区别
5、基础架构
6、MyISAM 和 InnoDB 有什么区别
7、推荐自增id作为主键问题
8、为什么 MySQL 的自增主键不连续
9、redo log 是做什么的?
10、redo log 的刷盘时机
11、redo log 是怎么记录日志的
12、什么是 binlog
13、binlog 记录格式
14、binlog 写入机制
15、redolog 和 binlog 的区别是什么
16、两阶段提交
17、什么是 undo log.
18、什么是 relaylog
19、索引
20、Hash 索引
21、B树和B 树
22、主键索引
23、二级索引
24、聚簇索引与非聚簇索引
25、回表
26、覆盖索引和联合索引
27、最左前缀匹配原则
28、索引下推
29、隐式转换
30、普通索引和唯一索引该怎么选择?
31、避免索引失效
32、建立索引的规则
33、事务极其特性
34、并发事务带来的问题
35、事务的隔离级别
36、MVCC
37、Mysql 中的锁
38、查询语句执行过程
39、更新语句执行过程
40、sql 优化
41、主从同步数据
42、主从延迟要怎么解决
43、为什么不要使用长事务
44、什么是自适应哈希
45、什么是脏读、幻读和不可重复度
46、数据库锁的作用以及有哪些锁
47、隔离级别和锁的关系
48、InnoDB 中的锁算法
49、存储过程 1、什么是存储过程 2、存储过程和函数的区别
50、MySQL 中有哪些常见日志
51、主从复制
1、什么是主从复制
2、主从复制的作用
3、主从复制的架构
52、主从复制的实现原理
53、什么是异步复制和半同步
54、主从中常见问题以及解决 1、三大范式
1NF(第一范式)属性对应于表中的字段不能再被分割也就是这个字段只能是一个值不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 也就是说关系型数据库中创建的表一定满足第一范式。
2NF(第二范式)2NF 要求数据库表中的每个实例或行必须可以被惟一地区分2NF 在 1NF 的基础上增加了一个列这个列称为主键非主属性都依赖于主键。
3NF(第三范式)3NF 在 2NF 的基础之上要求每列都和主键列直接相关而不是间接相关即不存在其他表的非主键信息。
在开发过程中并不一定要满足三大范式有时候为了提高查询效率可以在表中冗余其他表的字段。
2、DML 语句和 DDL 语句区别 DML 是数据库操作语言Data Manipulation Language的缩写是指对数据库中表记录的操作主要包括表记录的插入、更新、删除和查询是开发人员日常使用最频繁的操作。 DDL Data Definition Language是数据定义语言的缩写简单来说就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作而不涉及到表的定义、结构的修改更不会涉及到其他对象。DDL 语句更多的被数据库管理员DBA所使用一般的开发人员很少使用。
3、主键和外键的区别 主键用于唯一标识一行数据不能有重复不允许为空且一个表只能有一个主键 外键用来和其他表建立联系外键是另一表的主键外键是可以有重复的可以是空值。一个表可以有多个外键
4、drop、delete、truncate 区别
1用法不同 drop(丢弃数据): drop table 表名 直接将表结构都删除掉在删除表的时候使用。 truncate (清空数据) : truncate table 表名 只删除表中的数据再插入数据的时候自增长 id 又从 1 开始在清空表中数据的时候使用。 delete删除数据 : delete from 表名 where 列名值删除某一行的数据如果不加 where 子句和truncate table 表名作用类似。
2属于不同的数据库语言 truncate 和 drop 属于 DDL(数据定义语言)语句操作立即生效原数据不放到 rollback segment 中不能回滚操作不触发 trigger。 delete 语句是 DML (数据库操作语言)语句这个操作会放到 rollback segment 中事务提交之后才生效。
3执行速度不同 delete命令执行的时候会产生数据库的binlog日志而日志记录是需要消耗时间的但是也有个好处方便数据回滚恢复。 truncate命令执行的时候不会产生数据库日志因此比delete要快。除此之外还会把表的自增值重置和索引恢复到初始大小等。 drop命令会把表占用的空间全部释放掉。
一般来说drop truncate delete
5、基础架构
下图是 MySQL 的一个简要架构图从下图你可以很清晰的看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的。 连接器 身份认证和权限相关(登录 MySQL 的时候)。 查询缓存 执行查询语句的时候会先查询缓存MySQL 8.0 版本后移除因为这个功能不太实用。 分析器 没有命中缓存的话SQL 语句就会经过分析器分析器说白了就是要先看你的 SQL 语句要干嘛再检查你的 SQL 语句语法是否正确。 优化器 按照 MySQL 认为最优的方案去执行。 执行器 执行语句然后从存储引擎返回数据。执行语句之前会先判断是否有权限如果没有权限的话就会报错。 插件式存储引擎主要负责数据的存储和读取采用的是插件式架构支持 InnoDB、MyISAM、Memory 等多种存储引擎。
6、MyISAM 和 InnoDB 有什么区别
MySQL 5.5 之前MyISAM 引擎是 MySQL 的默认存储引擎MySQL 5.5 版本之后InnoDB 是 MySQL 的默认存储引擎。
1是否支持行级锁
MyISAM 只有表级锁而 InnoDB 支持行级锁和表级锁默认为行级锁。
2是否支持事务
MyISAM 不提供事务支持InnoDB 提供事务支持实现了 SQL 标准定义的四个隔离级别具有提交和回滚事务的能力。
InnoDB 默认使用的 REPEATABLE-READ可重读隔离级别是可以解决幻读问题发生的基于 MVCC 和 Next-Key Lock。
3是否支持外键
MyISAM 不支持而 InnoDB 支持。
4是否支持数据库异常崩溃后的安全恢复
MyISAM 不支持而 InnoDB 支持。使用 InnoDB 的数据库在异常崩溃后数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。
5是否支持 MVCC
MyISAM 不支持而 InnoDB 支持。
6索引实现
虽然 MyISAM 引擎和 InnoDB 引擎都是使用 BTree 作为索引结构但是两者的实现方式不太一样。 InnoDB 引擎中其数据文件本身就是索引文件。其表数据文件本身就是按 BTree 组织的一个索引结构树的叶子节点 data 域保存了完整的数据记录。 MyISAM 索引文件和数据文件是分离的索引保存的是数据文件的指针。
7性能差别
InnoDB 的性能比 MyISAM 更强大不管是在读写混合模式下还是只读模式下随着 CPU 核数的增加InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发它的处理能力跟核数没关系。 InnoDB 和 MyISAM 性能对比
7、推荐自增id作为主键问题 普通索引的 B 树上存放的是主键索引的值如果该值较大会「导致普通索引的存储空间较大」 使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以直接「按照顺序插入」不用刻意维护 页分裂容易维护当插入数据的当前页快满时会发生页分裂的现象如果主键索引不为自增 id那么数据就可能从页的中间插入页的数据会频繁的变动「导致页分裂维护成本较高」
8、为什么 MySQL 的自增主键不连续 在MySQL 5.7及之前的版本自增值保存在内存里并没有持久化 唯一键冲突插入数据时先将自增主键1然后插入数据时唯一键冲突插入数据失败但是未将自增主键改回 事务回滚和唯一键冲突类似回滚操作时自增值也不回退事实上这么做的主要原因是为了提高性能。
9、redo log 是做什么的?
redo log重做日志是InnoDB存储引擎独有的它让MySQL拥有了崩溃恢复能力。
比如 MySQL 实例挂了或宕机了重启时InnoDB存储引擎会使用redo log恢复数据保证数据的持久性与完整性。
更新表数据的时候如果发现 Buffer Pool 里存在要更新的数据就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存redo log buffer里接着刷盘到 redo log 文件里。
10、redo log 的刷盘时机 红色部分为 redo log buffer 属于内存 黄色部分为 page cache 此时已经写入磁盘了但是未进行持久化 绿色部分是硬盘已经完成持久化
InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数它支持三种策略 设置为0的时候表示每次事务提交时不进行刷盘操作只是保留在 redo log buffer中mysql 崩溃会丢失1s的数据 设置为1的时候表示每次事务提交时都将进行刷盘操作默认值持久化到磁盘 设置为2的时候表示每次事务提交时都只把redo log buffer内容写入page cacheOS宕机会丢失1s的数据因为未进行持久化
innodb_flush_log_at_trx_commit 参数默认为 1 也就是说当事务提交时会调用 fsync(同步操作) 对 redo log 进行刷盘。
另外 InnoDB 存储引擎有一个后台线程每隔1秒就会把 redo log buffer 中的内容写到文件系统缓存page cache然后调用 fsync 刷盘。
redo log buffer占用的空间即将达到 innodb_log_buffer_size 一半的时候后台线程会主动刷盘。
11、redo log 是怎么记录日志的
硬盘上存储的 redo log 日志文件不只一个而是以一个日志文件组的形式出现的每个的redo日志文件大小都是一样的。
比如可以配置为一组4个文件每个文件的大小是 1GB整个 redo log 日志文件组可以记录4G的内容。
它采用的是环形数组形式从头开始写写到末尾又回到头循环写如下图所示。 编辑
所以如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中那么就会发生「内存抖动」现象从肉眼的角度来观察会发现 mysql 会宕机一会儿此时就是正在刷盘了。
12、什么是 binlog
binlog 是归档日志属于 Server 层的日志是一个二进制格式的文件记录内容是语句的原始逻辑类似于“给 ID2 这一行的 c 字段加 1”。
不管用什么存储引擎只要发生了表数据更新都会产生 binlog 日志。它的主要作用就是数据备份、主从复制。
binlog会记录所有涉及更新数据的逻辑操作属于逻辑日志并且是顺序写。
13、binlog 记录格式
binlog 日志有三种格式可以通过binlog_format参数指定。 statement 记录的内容是SQL语句原文存在数据一致性问题 row记录包含操作的具体数据能保证同步数据的一致性 mixed记录的内容是前两者的混合MySQL会判断这条SQL语句是否可能引起数据不一致如果是就用row格式否则就用statement格式。
14、binlog 写入机制
事务执行过程中先把日志写到binlog cache事务提交的时候再把binlog cache写到binlog文件中。
因为一个事务的binlog不能被拆开无论这个事务多大也要确保一次性写入所以系统会给每个线程分配一个块内存作为binlog cache。
我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小如果存储内容超过了这个参数就要暂存到磁盘Swap。
binlog 也提供了 sync_binlog 参数来控制写入 page cache 和磁盘的时机 0每次提交事务都只写入到文件系统的 page cache由系统自行判断什么时候执行fsync机器宕机page cache里面的 binlog 会丢失。 1每次提交事务都会执行fsync就如同 redo log 日志刷盘流程 一样。 N(N1)每次提交事务都写入到文件系统的 page cache但累积N个事务后才fsync。如果机器宕机会丢失最近N个事务的binlog日志。
15、redolog 和 binlog 的区别是什么 redolog 是 Innodb 独有的日志而 binlog 是 server 层的所有的存储引擎都有使用到 redolog 记录了具体的数值对某个页做了什么修改binlog 记录的操作内容 binlog 大小达到上限或者 flush log 会生成一个新的文件而 redolog 有固定大小只能循环利用 binlog 日志没有 crash-safe 的能力只能用于归档而 redo log 有 crash-safe 能力 redo log 在事务执行过程中可以不断写入刷盘设置为1后台线程1s执行一次或者 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候而 binlog 只有在提交事务时才写入文件缓存系统
16、两阶段提交
假设执行 sql 过程中写完 redo log 日志后binlog 日志写期间发生了异常会出现什么情况呢
由于 binlog 没写完就异常这时候 binlog 里面没有对应的修改记录。因此之后用 binlog 日志恢复数据时就会少这一次更新最终数据不一致。
为了解决两份日志之间的逻辑一致问题InnoDB 存储引擎使用两阶段提交方案。
将 redo log 的写入拆成了两个步骤 prepare 和 commit这就是两阶段提交。使用两阶段提交后写入 binlog 时发生异常也不会有影响因为 MySQL 根据 redo log日志恢复数据时发现 redo log 还处于 prepare 阶段并且没有对应 binlog 日志就会回滚该事务。
再看一个场景redo log 设置 commit 阶段发生异常那会不会回滚事务呢
并不会回滚事务虽然 redo log 是处于 prepare 阶段但是能通过事务id找到对应的 binlog 日志所以 MySQL 认为是完整的就会提交事务恢复数据。
17、什么是 undo log.
我们知道如果想要保证事务的原子性就需要在异常发生时对已经执行的操作INSERT、DELETE、UPDATE进行回滚在 MySQL 中恢复机制是通过回滚日志undo log 实现的所有事务进行的修改都会先记录到这个回滚日志中然后再执行相关的操作。
每次对记录进行改动都会记录一条 undo log每条 undo log 也都有一个DB_ROLL_PTR属性可以将这些 undo log 都连起来串成一个链表形成版本链。
版本链的头节点就是当前记录最新的值。 编辑
18、什么是 relaylog
relaylog 是中继日志在主从同步的时候使用到它是一个中介临时的日志文件用于存储从 master 节点同步过来的 binlog 日志内容。 编辑
master 主节点的 binlog 传到 slave 从节点后被写入 relay log 里从节点的 slave sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。
从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器从而使从服务器和主服务器的数据保持一致。
19、索引
索引其实是一种数据结构能够帮助我们快速的检索数据库中的数据。
索引的作用就相当于书的目录。打个比方: 我们在查字典的时候如果没有目录那我们就只能一页一页的去找我们需要查的那个字速度很慢。如果有目录了我们只需要先去目录里查找字的位置然后直接翻到那一页就行了。
20、Hash 索引
哈希表是键值对的集合通过键(key)即可快速取出对应的值(value)因此哈希表可以快速检索数据接近 O1。
但是哈希算法有个 Hash 冲突问题也就是说多个不同的 key 最后得到的 index 相同。通常情况下我们常用的解决办法是 链地址法。
链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过JDK1.8 以后 HashMap 为了减少链表过长的时候搜索时间过长引入了红黑树。
为了减少 Hash 冲突的发生一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。
既然哈希表这么快为什么 MySQL 没有使用其作为索引的数据结构呢 主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询那 Hash 索引可就不行了并且每次 IO 只能取一个。
21、B树和B 树 B 树的所有节点既存放键(key) 也存放数据(data)而 B树只有叶子节点存放 key 和 data其他内节点只存放 key。 B 树的叶子节点都是独立的B树的叶子节点有一条引用链指向与它相邻的叶子节点。 B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找可能还没有到达叶子节点检索就结束了。而 B树的检索效率就很稳定了任何查找都是从根节点到叶子节点的过程叶子节点的顺序检索很明显。
22、主键索引
数据表的主键列使用的就是主键索引一种特殊的唯一索引。
在 MySQL 的 InnoDB 的表中当没有显示的指定表的主键时InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段如果有则选择该字段为默认的主键否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
23、二级索引
二级索引又称为辅助索引是因为二级索引的叶子节点存储的数据是主键。也就是说通过二级索引可以定位主键的位置。
唯一索引普通索引前缀索引等索引属于二级索引。 唯一索引(Unique Key) 唯一索引也是一种约束。索引列的值必须唯一但允许有空值如果是组合索引则列值的组合必须唯一。一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性而不是为了查询效率。 普通索引(Index) 普通索引的唯一作用就是为了快速查询数据一张表允许创建多个普通索引并允许数据重复和 NULL。 前缀索引(Prefix) 前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引相比普通索引建立的数据更小 因为只取前几个字符。 组合索引指多个字段上创建的索引只有在查询条件中使用了创建索引时的第一个字段索引才会被使用。使用组合索引时遵循最左前缀集合后文介绍 全文索引(Full Text) 全文索引主要是为了检索大文本数据中的关键字的信息是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引5.6 之后 InnoDB 也支持了全文索引。 MySQL 中的全文索引有两个变量最小搜索长度和最大搜索长度对于长度小于最小搜索长度和大于最大搜索长度的词语都不会被索引。 24、聚簇索引与非聚簇索引
聚簇索引即索引结构和数据一起存放的索引并不是一种单独的索引类型。InnoDB 的主键索引的叶子节点中存放的就是数据行所以它属于聚簇索引。
在 MySQL 中InnoDB 引擎的表的 .ibd 文件就包含了该表的索引和数据对于 InnoDB 引擎表来说该表的索引(B树)的每个非叶子节点存储索引叶子节点存储索引和索引对应的数据。
非聚簇索引即索引结构和数据分开存放的索引并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎不管主键还是非主键使用的都是非聚簇索引。
辅助索引是我们人为创建的索引它的叶子节点中存放的是主键当我们通过辅助索引查找到主键之后再通过查找的主键去回表查找主键索引。
25、回表
回表就是先通过数据库索引扫描出该索引树中数据所在的行取到主键 id再通过主键 id 取出主键索引数中的数据即基于非主键索引的查询需要多扫描一棵索引树。
26、覆盖索引和联合索引
如果一个索引包含或者说覆盖所有需要查询的字段的值我们就称之为“覆盖索引”。指的是通过索引就能查询到我们所需要的数据而不需要根据索引再去查询数据表中的数据 回表这样就减少了数据库的 io 操作提高查询效率。
使用表中的多个字段创建索引就是联合索引也叫组合索引或复合索引。
27、最左前缀匹配原则
最左前缀匹配原则指的是在使用联合索引时MySQL 会根据联合索引中的字段顺序从左到右依次到查询条件中去匹配如果查询条件中存在与联合索引中最左侧字段相匹配的字段则就会使用该字段过滤一批数据直至联合索引中全部字段匹配完成或者在执行过程中遇到范围查询如 、、between 和 以%开头的like查询 等条件才会停止匹配。
所以我们在使用联合索引时可以将区分度高的字段放在最左边这也可以过滤更多数据。
28、索引下推
索引下推Index Condition Pushdown 是 MySQL 5.6 版本中提供的一项索引优化功能可以在非聚簇索引遍历过程中对索引中包含的字段先做判断过滤掉不符合条件的记录减少回表次数。
29、隐式转换
当操作符与不同类型的操作数一起使用时会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如MySQL 会根据需要自动将字符串转换为数字反之亦然。以下规则描述了比较操作的转换方式 两个参数至少有一个是NULL时比较的结果也是NULL特殊的情况是使用对两个NULL做比较时会返回1这两种情况都不需要做类型转换 两个参数都是字符串会按照字符串来比较不做类型转换 两个参数都是整数按照整数来比较不做类型转换 十六进制的值和非数字做比较时会被当做二进制串 有一个参数是TIMESTAMP或DATETIME并且另外一个参数是常量常量会被转换为timestamp 有一个参数是decimal类型如果另外一个参数是decimal或者整数会将整数转换为decimal后进行比较如果另外一个参数是浮点数则会把decimal转换为浮点数进行比较 所有其他情况下两个参数都会被转换为浮点数再进行比较
30、普通索引和唯一索引该怎么选择? 查询 当普通索引为条件时查询到数据会一直扫描直到扫完整张表 当唯一索引为查询条件时查到该数据会直接返回不会继续扫表 更新 普通索引会直接将操作更新到 change buffer 中然后结束 唯一索引需要判断数据是否冲突
所以唯一索引更加适合查询的场景普通索引更适合插入的场景。
31、避免索引失效
索引失效也是慢查询的主要原因之一常见的导致索引失效的情况有下面这些 使用 SELECT * 进行查询; 创建了组合索引但查询条件未准守最左匹配原则; 在索引列上进行计算、函数、类型转换等操作; 以 % 开头的 LIKE 查询比如 like %abc; 查询条件中使用 or且 or 的前后条件中有一个列没有索引涉及的索引都不会被使用到 match() 函数中的指定的列必须与全文索引中指定的列完全相同否则会报错无法使用全文索引。 全文索引时要注意搜索长度会导致索引失效
32、建立索引的规则 不为 NULL 的字段 索引字段的数据应该尽量不为 NULL因为对于数据为 NULL 的字段数据库较难优化。如果字段频繁被查询但又避免不了为 NULL建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。 被频繁查询的字段 我们创建索引的字段应该是查询操作非常频繁的字段。 被作为条件查询的字段 被作为 WHERE 条件查询的字段应该被考虑建立索引。 频繁需要排序的字段 索引已经排序这样查询可以利用索引的排序加快排序查询时间。 被经常频繁用于连接的字段 经常用于连接的字段可能是一些外键列对于外键列并不一定要建立外键只是说该列涉及到表与表的关系。对于频繁被连接查询的字段可以考虑建立索引提高多表连接查询的效率。 被频繁更新的字段应该慎重建立索引 尽可能的考虑建立联合索引而不是单列索引 考虑在字符串类型的字段上使用前缀索引代替普通索引 删除长期未使用的索引
33、事务极其特性
一个事情由n个单元组成这n个单元在执行过程中要么同时成功要么同时失败这就把n个单元放在了一个事务之中。举个简单的例子在不考虑试题正确与否的前提下一张试卷由多个题目构成当你答完题交给老师的时候是将一整张试卷交给老师而不是将每道题单独交给老师在这里试卷就可以理解成一个事务。
事务的特性 A原子性Atomicity原子性是指事务是一个不可分割的工作单位事务中的操作要么都发生要么都不发生。 C一致性Consistency在一个事务中事务前后数据的完整性必须保持一致。 I隔离性Isolation存在于多个事务中事务的隔离性是指多个用户并发访问数据库时一个用户的事务不能被其它用户的事务所干扰多个并发事务之间数据要相互隔离。 D持久性Durability持久性是指一个事务一旦被提交它对数据库中数据的改变就是永久性的接下来即使数据库发生故障也不应该对其有任何影响。
34、并发事务带来的问题 脏读Dirty readB事务读取到了A事务尚未提交的数据 丢失修改Lost to modify在一个事务读取一个数据时另外一个事务也访问了该数据那么在第一个事务中修改了这个数据后第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失因此称为丢失修改。 不可重复读Unrepeatable readB事务读到了A事务已经提交的数据即B事务在A事务提交之前和提交之后读取到的数据内容不一致AB事务操作的是同一条数据 幻读/虚读B事务读到了A事务已经提交的数据即A事务执行插入操作B事务在A事务前后读到的数据数量不一致。
35、事务的隔离级别
为了解决以上隔离性引发的并发问题数据库提供了事务的隔离机制。 read uncommitted读未提交: 一个事务还没提交时它做的变更就能被别的事务看到读取尚未提交的数据哪个问题都不能解决 read committed读已提交一个事务提交之后它做的变更才会被其他事务看到读取已经提交的数据可以解决脏读 ---- oracle默认的 repeatable read可重复读一个事务执行过程中看到的数据总是跟这个事务在启动时看到的数据是一致的可以解决脏读和不可重复读 ---mysql默认的 serializable串行化顾名思义是对于同一行记录“写”会加“写锁”“读”会加“读锁”。当出现读写锁冲突的时候后访问的事务必须等前一个事务执行完成才能继续执行。可以解决脏读、不可重复读和虚读---相当于锁表。
虽然 serializable 级别可以解决所有的数据库并发问题但是它会在读取的每一行数据上都加锁这就可能导致大量的超时和锁竞争问题从而导致效率下降。所以我们在实际应用中也很少使用 serializable只有在非常需要确保数据的一致性而且可以接受没有并发的情况下才考虑采用该级别。
36、MVCC
锁的粒度过大会导致性能的下降 MySQL 的 InnoDB 引擎下存在一种性能更优越的 MVCC 方法。
MVCC 是 Multi-Version Concurremt Control 的简称意思是基于多版本的并发控制协议通过版本号避免同一数据在不同事务间的竞争。它主要是为了提高数据库的并发读写性能不用加锁就能让多个事务并发读写。
MVCC 的实现依赖于隐藏列、Undo log、 Read View 。
从上面对 SQL 标准定义了四个隔离级别的介绍可以看出标准的 SQL 隔离级别定义里REPEATABLE-READ(可重复读)是不可以防止幻读的。
但是 InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的主要有下面两种情况 快照读 由 MVCC 机制来保证不出现幻读。 当前读 使用 Next-Key Lock临键锁 进行加锁来保证不出现幻读Next-Key Lock 是行锁Record Lock和间隙锁Gap Lock的结合行锁只能锁住已经存在的行为了避免插入新行需要依赖间隙锁。 InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE 隔离级别。 37、Mysql 中的锁
锁如果是从操作类型上分的话可以分为读锁、写锁这里说的读写锁的概念与我们Java中的是相似的可以理解为是共享锁以及独占锁。从粒度上分可以分为行锁、页锁、表锁平常中我们使用最多的是行锁与表锁这里面说的主要指的是锁的作用范围的一个大小锁作用范围的大小也会直接影响并发程度。行锁的并发程度是最高的但是他的加锁成本大常见于Innodb的引擎上表锁的加锁成本低但是锁定的范围也大并发度最低常见于MySIAM引擎上根据读的特性——共享的MySIAM是跟适合于偏向查询的场景。
我们知道锁以及事务级别实际上都是为了解决并发场景的事务级别的理解可以借助于redo、undo日志那么他们之间与锁又是有什么样的关联呢跟人理解是锁机制主要是做的一个粗粒度上的控制但是数据的读写因为存储结构的存在他并不是一下子就成功的这就造成了那几个脏读、脏写、不可重复读、幻读的问题而这些问题的解决又是借助于MVVC机制实现的。
38、查询语句执行过程
select * from tb_student s where s.age18 and s.name 张三 ; 先检查该语句是否有权限如果没有权限直接返回错误信息如果有权限在 MySQL8.0 版本以前会先查询缓存以这条 SQL 语句为 key 在内存中查询是否有结果如果有直接缓存如果没有执行下一步。 通过分析器进行词法分析提取 SQL 语句的关键元素比如提取上面这个语句是查询 select提取需要查询的表名为 tb_student需要查询所有的列查询条件是这个表的 id1。然后判断这个 SQL 语句是否有语法错误比如关键词是否正确等等如果检查没问题就执行下一步。 接下来就是优化器进行确定执行方案上面的 SQL 语句可以有两种执行方案 a.先查询学生表中姓名为“张三”的学生然后判断是否年龄是 18。 b.先找出学生中年龄 18 岁的学生然后再查询姓名为“张三”的学生。那么优化器根据自己的优化算法进行选择执行效率最好的一个方案优化器认为有时候不一定最好。那么确认了执行计划后就准备开始执行了。 进行权限校验如果没有权限就会返回错误信息如果有权限就会调用数据库引擎接口返回引擎的执行结果。
查询语句的执行流程如下权限校验如果命中缓存---查询缓存---分析器---优化器---权限校验---执行器---引擎
39、更新语句执行过程
update tb_student A set A.age19 where A.name 张三 ; 这条语句基本上也会沿着上一个查询的流程走只不过执行更新的时候要记录日志这就会引入日志模块了MySQL 自带的日志模块是 binlog归档日志 所有的存储引擎都可以使用我们常用的 InnoDB 引擎还自带了一个日志模块 redo log重做日志我们就以 InnoDB 模式下来探讨这个语句的执行流程。 先查询到张三这一条数据如果有缓存也是会用到缓存。 然后拿到查询的语句把 age 改为 19然后调用引擎 API 接口写入这一行数据InnoDB 引擎把数据保存在内存中同时记录 redo log此时 redo log 进入 prepare 状态然后告诉执行器执行完成了随时可以提交。 执行器收到通知后记录 binlog然后调用引擎接口提交 redo log 为提交状态。 更新完成。 更新语句执行流程如下分析器----权限校验----执行器---引擎---redo log(prepare 状态)--- binlog ---redo log(commit状态) 40、sql 优化 应尽量避免全表扫描首先应考虑在 where 及 order by 涉及的列上建立索引 应尽量避免在 where 子句中使用以下语句否则将导致引擎放弃使用索引而进行全表扫描 对字段进行 null 值判断 使用!或 or 来连接条件使用union all代替 in 和 not in 也要慎用 不要使用模糊查询可用全文索引 减少表达式操作 函数操作 任何地方都不要使用 select * from t 用具体的字段列表代替“* ”不要返回用不到的任何字段 一个表的索引数最好不要超过6个若太多则应考虑一些不常使用到的列上建的索引是否有必要 很多时候用 exists 代替 in 是一个好的选择 尽量减少多表联合查询 分页优化 正确使用索引 41、主从同步数据 编辑 master 主库将此次更新的事件类型写入到主库的 binlog 文件中 master 创建 log dump 线程通知 slave 需要更新数据 slave 向 master 节点发送请求将该 binlog 文件内容存到本地的 relaylog 中 slave 开启 sql 线程读取 relaylog 中的内容将其中的内容在本地重新执行一遍完成主从数据同步 同步策略 全同步复制主库强制同步日志到从库等全部从库执行完才返回客户端性能差 半同步复制主库收到至少一个从库确认就认为操作成功从库写入日志成功返回 ack 确认 42、主从延迟要怎么解决 MySQL 5.6 版本以后提供了一种并行复制的方式通过将 SQL 线程转换为多个 work 线程来进行重放 提高机器配置(王道) 在业务初期就选择合适的分库、分表策略避免单表单库过大带来额外的复制压力 避免长事务 避免让数据库进行各种大量运算 对于一些对延迟很敏感的业务直接使用主库读 43、为什么不要使用长事务 并发情况下数据库连接池容易被撑爆 容易造成大量的阻塞和锁超时长事务还占用锁资源也可能拖垮整个库 执行时间长容易造成主从延迟 回滚所需要的时间比较长事务越长整个时间段内的事务也就越多 undolog 日志越来越大长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据所以这个事务提交之前数据库里面它可能用到的回滚记录都必须保留这就会导致大量占用存储空间。 44、什么是自适应哈希 InnoDB 会监控对表上各索引页的查询执行情况如发现建立哈希索引可以提升速度则建立哈希索引这是过程不需要用户干预。默认开启 45、什么是脏读、幻读和不可重复度 脏读一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据然后 B 回滚操作那么 A 读取到的数据是脏数据。 不可重复读一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据事务 B 在事务 A 多次读取的过程中对数据作了更新并提交导致事务 A 多次读取同一数据时结果 不一致。 幻读一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级但是系统管理员 B 就在这个时候插入了一条具体分数的记录当系统管理员 A 改结束后发现还有一条记录没有改过来就好像发生了幻觉一样这就叫幻读。 46、数据库锁的作用以及有哪些锁 当数据库有并发事务的时候可能会产生数据的不一致这时候需要一些机制来保证访问的次序锁机制就是这样的一个机制。即锁的作用是解决并发问题。 从锁的粒度划分可以将锁分为表锁、行锁以及页锁。 行级锁是锁定粒度最细的一种锁表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小但加锁的开销也最大。 行级锁开销大加锁慢且会出现死锁。但锁定粒度最小发生锁冲突的概率最低并发度也最高。 表级锁是粒度最大的一种锁表示对当前操作的整张表加锁它实现简单资源消耗较少被大部分MySQL引擎支持。 页级锁是粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多行级冲突少但速度慢。所以取了折中的页级一次锁定相邻的一组记录。 开销和加锁时间界于表锁和行锁之间会出现死锁。锁定粒度界于表锁和行锁之间并发度一般。
从使用性质划分可以分为共享锁、排它锁以及更新锁。 共享锁Share LockS 锁又称读锁用于所有的只读数据操作。 S 锁并非独占允许多个并发事务对同一资源加锁但加 S 锁的同时不允许加 X 锁即资源不能被修改。S 锁通常读取结束后立即释放无需等待事务结束。 排他锁Exclusive LockX 锁又称写锁表示对数据进行写操作。 X 锁仅允许一个事务对同一资源加锁且直到事务结束才释放其他任何事务必须等到 X 锁被释放才能对该页进行访问。
使用 select * from table_name for update; 语句产生 X 锁。 更新锁U 锁用来预定要对资源施加 X 锁允许其他事务读但不允许再施加 U 锁或 X 锁。 当被读取的页将要被更新时则升级为 X 锁U 锁一直到事务结束时才能被释放。故 U 锁用来避免使用共享锁造成的死锁现象。
从主观上划分又可以分为乐观锁和悲观锁。 乐观锁Optimistic Lock顾名思义从主观上认定资源是不会被修改的所以不加锁读取数据仅当更新时用版本号机制等确认资源是否被修改。 乐观锁适用于多读的应用类型可以系统提高吞吐量。 悲观锁Pessimistic Lock正如其名具有强烈的独占和排它特性每次读取数据时都会认为会被其它事务修改所以每次操作都需要加上锁。 47、隔离级别和锁的关系 1在 Read Uncommitted 级别下读取数据不需要加共享锁这样就不会跟被修改的数据上的排他锁冲突 2在 Read Committed 级别下读操作需要加共享锁但是在语句执行完以后释放共享锁 3在 Repeatable Read 级别下读操作需要加共享锁但是在事务提交之前并不释放共享锁也就是必须等待事务执行完毕以后才释放共享锁 4在 SERIALIZABLE 级别下限制性最强因为该级别锁定整个范围的键并一直持有锁直到事务完成。 48、InnoDB 中的锁算法 Record lock单个行记录上的锁 Gap lock间隙锁锁定一个范围不包括记录本身 Next-key lockrecord gap 锁定一个范围包含记录本身 49、存储过程 1、什么是存储过程 存储过程是一个预编译的SQL语句优点是允许模块化的设计就是说只需要创建一次以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL使用存储过程比单纯SQL语句执行要快。 2、存储过程和函数的区别 1返回值的区别函数有1个返回值而存储过程是通过参数返回的可以有多个或者没有。 2调用的区别函数可以在查询语句中直接调用而存储过程必须单独调用。 50、MySQL 中有哪些常见日志 重做日志redo log物理日志 作用是确保事务的持久性。 redo 日志记录事务执行后的状态用来恢复未写入 data file 的已提交事务数据。 回滚日志undo log逻辑日志 作用是保证数据的原子性。 保存了事务发生之前的数据的一个版本可以用于回滚同时可以提供多版本并发控制下的读MVCC也即非锁定读。
二进制日志binlog逻辑日志
常用于主从同步或数据同步中也可用于数据库基于时间点的还原。
错误日志errorlog
记录着 MySQL 启动和停止以及服务器在运行过程中发生的错误的相关信息。在默认情况下系统记录错误日志的功能是关闭的错误信息被输出到标准错误输出。
普通查询日志general query log
记录了服务器接收到的每一个命令无论命令语句是否正确因此会带来不小开销所以也是默认关闭的。
慢查询日志slow query log
记录执行时间过长和没有使用索引的查询语句默认 10s同时只会记录执行成功的语句。
中继日志relay log
在从节点中存储接收到的 binlog 日志内容用于主从同步。 51、主从复制
1、什么是主从复制
主从复制是用来建立一个与主数据库完全一样的数据库环境即从数据库。主数据库一般是准实时的业务数据库。
2、主从复制的作用 读写分离使数据库能支撑更大的并发。高可用做数据的热备作为后备数据库主数据库服务器故障后可切换到从数据库继续工作避免数据丢失。
3、主从复制的架构 一主一从或一主多从 在主库的请求压力非常大时可通过配置一主多从复制架构实现读写分离把大量对实时性要求不是很高的请求通过负载均衡分发到多个从库上去读取数据降低主库的读取压力。而且在主库出现宕机时可将一个从库切换为主库继续提供服务。 主主复制 双主复制架构适用于需要进行主从切换的场景。 两个数据库互为主从当主库宕机恢复后由于它还是原来从库现在主库的从机所以它还是会复制新的主库上的数据。那么无论主库的角色怎么切换原来的主库都不会脱离复制环境。 多主一从5.7 开始支持联级复制 因为每个从库在主库上都会有一个独立的 Binlog Dump 线程来推送 binlog 日志所以随着从库数量的增加主库的 IO 压力和网络压力也会随之增加这时联级复制架构应运而生。 联级复制架构只是在一主多从的基础上再主库和各个从库之间增加了一个二级主库 Master2这个二级主库仅仅用来将一级主库推送给它的 Binlog 日志再推送给各个从库以此来减轻一级主库的推送压力。 52、主从复制的实现原理 数据库有个 binlog 二进制文件记录了数据可执行的所有 SQL 语句。主从同步的目标就是把主数据库的 binlog 文件中的 SQL 语句复制到从数据库让其在从数据的 relaylog 文件中再执行一次这些 SQL 语句即可。 具体实现需要三个线程 binlog 输出线程每当有从库连接到主库的时候主库都会创建一个线程然后发送 binlog内 容到从库。 在从库里当复制开始的时候从库就会创建两个线程进行处理 从库 IO 线程当 START SLAVE 语句在从库开始执行之后从库创建一个 IO 线程该线程连接到主库并请求主库发送 binlog 里面的更新记录到从库上。从库 IO 线程读取主库的 binlog 输出线程发送的更新并拷贝这些更新到本地文件其中包括 relaylog 文件。 从库 SQL 线程从库创建一个 SQL 线程这个线程读取从库 IO 线程写到 relaylog 的更新事件并执行。 53、什么是异步复制和半同步 MySQL 的主从复制有两种复制方式分别是异步复制和半同步复制异步复制 MySQL 默认的主从复制方式就是异步复制因为 Master 根本不考虑数据是否达到了 Slave或 Slave 是否成功执行。 如过需要实现完全同步方式即 Master 需要等待一个或所有 Slave 执行成功后才响应成功那集群效率可想而知。故 MySQL 5.6 之后出现了一种折中的方式——半同步。 一主一从一主多从情况下Master 节点只要确认至少有一个 Slave 接受到了事务即可向发起请求的客户端返回执行成功的操作。同时 Master 是不需要等待 Slave 成功执行完这个事务Slave 节点接受到这个事务并成功写入到本地 relay 日志中就算成功。 另外在半同步复制时如果主库的一个事务提交成功了在推送到从库的过程当中从库宕机了或网络故障导致从库并没有接收到这个事务的Binlog此时主库会等待一段时间这个时间由rpl_semi_sync_master_timeout的毫秒数决定如果这个时间过后还无法推送到从库那 MySQL 会自动从半同步复制切换为异步复制当从库恢复正常连接到主库后主库又会自动切换回半同步复制。 半同步复制的“半”体现在虽然主从库的Binlog是同步的但主库不会等待从库执行完Relay-log后才返回而是确认从库接收到Binlog达到主从Binlog同步的目的后就返回了所以从库的数据对于主库来说还是有延时的这个延时就是从库执行Relay-log的时间。所以只能称为半同步。 54、主从中常见问题以及解决 问题 1主库宕机后数据可能丢失。 2从库只有一个sql Thread主库写压力大复制很可能延时。 解决 1半同步复制确保事务提交后 binlog 至少传输到一个从库 解决数据丢失的问题。 2并行复制从库多线程apply binlog解决从库复制延迟的问题。 图书赞助商 iToday打开信息的新时代。作为一家创新的IT数字媒体平台iToday致力于为用户提供最新、最全面的IT资讯和内容。里面包含了各界热门新闻、娱乐分享、财经报刊、前沿科技等诸多内容。我们的团队由一群热爱创作的开发者和分享的专业编程知识爱好者组成他们精选并整理出真实可信的信息确保您获得独特、有价值的阅读体验。随时随地尽在iToday与世界保持连接开启您的信息新旅程 IT今日热榜 一站式资讯平台IT今日热榜汇聚各类IT热榜虎嗅、知乎、36氪、京东图书销售、晚点、全天候科技、极客公园、GitHub、掘金、CSDN、哔哩哔哩、51CTO、博客园、GitChat、开发者头条、思否、LeetCode、人人都是产品经理、牛客网、看准、拉勾、Boss直聘http://itoday.top/ 文末送书 图书介绍 《MySQL入门到精通》是一本全面而实用的MySQL数据库学习指南。无论您是初学者还是有一定经验的开发人员这本书都将帮助您掌握MySQL的核心概念和技术。从基础的数据库设计到高级的性能优化从SQL查询语句到存储过程和触发器的应用本书提供了丰富的示例和实践案例帮助您深入理解MySQL的各个方面。与此同时您还将学习如何保障数据的安全性和可靠性以及如何优化数据库性能提升应用的效率。无论您是想系统学习MySQL知识还是寻找解决具体问题的实用指南本书都会成为您不可或缺的学习和工作伙伴。
