MySQL月末知识总结

2401_84149996

于 2024-05-03 13:18:01 发布

阅读量749

点赞数 19

分类专栏：程序员文章标签： mysql java 面试

本文链接：https://blog.csdn.net/2401_84149996/article/details/138414399

版权

程序员专栏收录该内容

198 篇文章 0 订阅

订阅专栏

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！

redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

前滚：

===

未完全提交的事务，即该事务已经被执行commit命令了，只是现在该事务修改所对应的脏数据块中只有一部分被写到磁盘上的数据文件中，还有一部分已经被置为提交标记的脏块还在内存上，如果此时数据库实例崩溃了，则当数据库实例恢复时，就需要用前滚（这个机制）来完成事务的完全提交，即将先前那部分已经被置为提交标记且还在内存上的脏块写入到磁盘上的数据文件中。

回滚：

===

未提交的事务，即该事务未被执行commit命令。但是此时，该事务修改的脏块中也有可能一部分脏块写入到数据文件中了。如果此时数据库实例崩溃了，则当数据库实例恢复时，就需要用回滚（这个机制）来将先前那部分已经写入到数据文件的脏块从数据文件上撤销掉。

redo log

========

redo log包括两部分：

一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；

二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

undo log

========

一般是逻辑日志，undo用来回滚行记录到某个版本。，根据每行记录进行记录。

undo log和redo log记录物理日志不一样，它是逻辑日志。

可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚

另外，undo log也会产生redo log，因为undo log也要实现持久性保护。

实质

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)

delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
update分为两种情况：update的列是否是主键列。
如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

恢复

在启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。

因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如二进制日志)要快很多。而且，innodb自身也做了一定程度的优化，让恢复速度变得更快。

与Redis事务区别

==========

mysql:

写方面：事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中
提交： 当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。
回滚：此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。

redis:

写：所有命令都会被序列化
提交：在事务提交过程，会按照顺序串行化执行队列中的命令
回滚：没有回滚。事务中任意命令执行失败，其余的命令仍会被执行

二、Redis事务

=========

Redis事务的概念：

Redis 事务的本质是一组命令的集合。事务支持一次执行多个命令，一个事务中所有命令都会被序列化。在事务执行过程，会按照顺序串行化执行队列中的命令，其他客户端提交的命令请求不会插入到事务执行命令序列中。

总结说：redis事务就是一次性、顺序性、排他性的执行一个队列中的一系列命令。

Redis事务没有隔离级别的概念：

批量操作在发送 EXEC 命令前被放入队列缓存，并不会被实际执行，也就不存在事务内的查询要看到事务里的更新，事务外查询不能看到。

Redis不保证原子性：

Redis中，单条命令是原子性执行的，但事务不保证原子性，且没有回滚。事务中任意命令执行失败，其余的命令仍会被执行。

Redis事务的三个阶段：

开始事务
命令入队
执行事务

三、MySql锁

========

MyISAM和MEMORY采用表级锁(table-level locking)

BDB采用页面锁(page-level locking)或表级锁，默认为页面锁

InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

3.1按照对数据操作的锁粒度来分

================

1 行级锁（加在索引上的锁）

==============

行级锁分为共享锁和排他锁

(1) 描述

行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁

(2) 特点

开销大，加锁慢，会出现死锁。发生锁冲突的概率最低，并发度也最高。

InnoDB有三种行锁的算法（排他锁）：

====================

1，Record Lock（记录锁）：单个行记录上的锁。这个也是我们日常认为的行锁。

2，Gap Lock（间隙锁）：间隙锁，锁定一个范围，但不包括记录本身（只不过它的锁粒度比记录锁的锁整行更大一些，他是锁住了某个范围内的多个行，包括根本不存在的数据）。

GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

该锁只会在隔离级别是RR或者以上的级别内存在。

间隙锁的目的是为了让其他事务无法在间隙中新增数据。

3，Next-Key Lock（临键锁）：它是记录锁和间隙锁的结合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁

record lock、gap lock、next-key lock，都是加在索引上的。假设有记录1,3,5,7，

则5上的记录锁会锁住5，

5上的gap lock会锁住(3,5)，

5上的next-key lock会锁住(3,5]。

2 表级锁

=====

(1) 描述

表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

(2) 特点

开销小，加锁快，不会出现死锁。发生锁冲突的概率最高，并发度也最低。

LOCK TABLE my_table_name READ; 用读锁锁表，会阻塞其他事务修改表数据。
LOCK TABLE my_table_name WRITE; 用写锁锁表，会阻塞其他事务读和写。

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，

在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，

因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

但是在InnoDB中如果需要表锁就需要显式地声明了

3 页级锁

=====

(1) 描述

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。

因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。

(2) 特点

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

3.2按照锁的共享策略来分

=============

共享锁和排他锁在MySQL中具体的实现就是读锁和写锁：

读锁（共享锁）：Shared Locks（S锁），针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁）：Exclusive Locks（X锁），当前写操作没有完成前，它会阻断其他写锁和读锁
IS锁：意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
IX锁：意向排他锁、Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，

以避免用遍历的方式来查看表中有没有上锁的记录。

注意：如果一个表中有多个行锁，他们都会给表加上意向锁，意向锁和意向锁之间是不会冲突的。

3.3从加锁策略上分

==========

乐观锁

===

乐观锁在操作数据时非常乐观，认为别人不会同时修改数据。

因此乐观锁不会上锁，只是在执行更新的时候判断一下在此期间别人是否修改了数据：如果别人修改了数据则放弃操作，否则执行操作。

乐观锁的实现方式主要有两种：CAS机制和版本号机制

=========================

CAS操作逻辑如下：

如果内存位置V的值等于预期的A值，则将该位置更新为新值B，否则不进行任何操作。

许多CAS的操作是自旋的：如果操作不成功，会一直重试，直到操作成功为止。

版本号机制：

的基本思路是在数据中增加一个字段version，表示该数据的版本号，每当数据被修改，版本号加1。

当某个线程查询数据时，将该数据的版本号一起查出来；
当该线程更新数据时，判断当前版本号与之前读取的版本号是否一致，如果一致才进行操作

悲观锁

===

悲观锁在操作数据时比较悲观，认为别人会同时修改数据。

因此操作数据时直接把数据锁住，直到操作完成后才会释放锁；上锁期间其他人不能修改数据。

3.4自增锁

======

自增锁（AUTO-INC锁）

自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。

通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。防止并发插入数据的时候自增id出现异常。

当一张表的某个字段是自增列时，innodb会在该索引的末位加一个排它锁。

**为了访问这个自增的数值，需要加一个表级锁，不过这个表级锁的持续时间只有当前sql，**而不是整个事务，即当前sql执行完，该表级锁就释放了。其他session无法在这个表级锁持有时插入任何记录。

四、MySQL InnoDB存储引擎

==================

InnoDB优势

========

1）支持事务

======

InnoDB 最重要的一点就是支持事务，可以说这是 InnoDB 成为 MySQL 中最流行的存储引擎的一个非常重要的原因

InnoDB 还实现了 SQL92 标准所定义的 4 个隔离级别

2）灾难恢复性好

========

commit、rollback、crash-recovery 来保障数据的安全

3）使用行级锁

=======

InnoDB 的行锁机制是通过索引来完成的，但毕竟在数据库中 99%的 SQL 语句都要使用索引来检索数据。行锁定机制也为 InnoDB 在承受高并发压力的环境下增强了不小的竞争力。

4）实现了缓冲处理

=========

InnoDB 提供了专门的缓存池，实现了缓冲管理，不仅能缓冲索引也能缓冲数据，常用的数据可以直接从内存中处理，比从磁盘获取数据处理速度要快。

当一条 SQL 执行的时候，如果是读操作，要查找的数据所在的数据页在内存中时，则将结果返回。否则会把对应的数据页加载到内存中，然后再返回结果。

同样对于写操作来说。如果要修改的行所在的数据页在内存中，则修改后返回对应的结果（当然还有后续操作）。如果不在的话，则会从磁盘里将该行所对应的数据页读到内存中再进行修改。

1.缓冲池的预读机制

==========

InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读

会产生两个问题

缓冲池污染问题

还有一种情况是当执行一条 SQL 语句时，如果扫描了大量数据或是进行了全表扫描，此时缓冲池中就会加载大量的数据页，从而将缓冲池中已存在的所有页替换出去，这种情况同样是不合理的。这就是缓冲池污染，并且还会导致 MySQL 性能急剧下降。

预读失效问题

被预先加载进缓冲池的页，并没有被访问到

2.缓冲刷新策略

========

通常来说，缓冲池是通过LRU（Latest Recent Used，最近最少使用）

5）文件大小不受限制

==========

InnoDB 的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分区）。

这与 MyISAM 表不同，比如在 MyISAM 表中每个表被保存在分离的文件中。

**InnoDB 表可以是任何尺寸，**即使在文件尺寸被限制为 2GB 的操作系统上。

6）支持外键

======

InnoDB 支持外键约束，检查外键、插入、更新和删除，以确保数据的完整性。

在存储表中数据时每张表的存储都按主键顺序存放，如果没有显式地在定义表时指定主键，InnoDB 会为每一行生成一个 6 字节的 ROWID ，并以此作为主键。

物理存储

====

1. 数据文件（表数据和索引数据）

==================

数据文件用来存放数据表中的数据和所有的索引数据，包括主键和其他普通索引。

InnoDB 存储的数据采用表空间（Tablepace）进行存放设计。表空间是用来存放 MySQL 系统相关信息的一个特殊共享表空间。

InnoDB 的表空间分为以下两种形式：

共享表空间，表数据和索引都存放在同一个表空间。默认的表空间文件就是上面所提到的 MySQL 初始化路径下的 ibdata1 文件。
独立表空间，每个表的数据和索引被存放在一个单独的 .ibd 文件中。

InnoDB 的表空间分为以下两种形式：

共享表空间，表数据和索引都存放在同一个表空间。默认的表空间文件就是上面所提到的 MySQL 初始化路径下的 ibdata1 文件。
独立表空间，每个表的数据和索引被存放在一个单独的 .ibd 文件中。

2. 日志文件

========

默认情况下，InnoDB 存储引擎的数据目录下会有两个名为 ib_logfile0 和 ib_logfile1 的文件。在 MySQL 官方手册中将其称为 InnoDB 存储引擎的重做日志文件（redo log file）。

每个 InnoDB 存储引擎至少有 1 个重做日志文件组（group），

每个文件组下至少有 2 个重做日志文件，如默认的 ib_logfile0 和 ib_logfile1。

五、MySql相关小知识

============

1.自增主键用完了该怎么办

=============

旦自增id达到最大值，此时数据继续插入是会报一个主键冲突异常如下所示

//Duplicate entry ‘4294967295’ for key ‘PRIMARY’

解决方法：将Int类型改为BigInt类型

1.1线上怎么修改列的数据类型的

================

方式一:使用mysql5.6+提供的在线修改功能

对于修改数据类型这种操作，是不支持并发的DML操作！也就是说，如果你直接使用ALTER这样的语句在线修改表数据结构，会导致这张表无法进行更新类操作(DELETE、UPDATE、DELETE)。因此，直接ALTER是不行滴！

方式二:借助第三方工具

1、pt-online-schema-change，简称pt-osc - 2、GitHub正式宣布以开源的方式发布的工具，名为gh-ost

如果你的表里有触发器和外键，这两个工具是不行

方式三:改从库表结构，然后主从切换

mysql架构一般是读写分离架构，从机是用来读的。我们直接在从库上进行表结构修改，不会阻塞从库的读操作。改完之后，进行主从切换即可。

可能会有数据丢失的情况

但是：

一般达不到最大值，我们就分库分表了，所以不曾遇见过！"

2.char和varchar的区别

=================

char类型的长度是固定的，varchar的长度是可变的。这就表示，存储字符串’abc’，使用char(10)，表示存储的字符将占10个字节，如果不足10字节，将使用空格占位，所以检索CHAR值时需删除尾随空格使用varchar2(10),，则表示只占3个字节，10是最大值，当存储的字符小于10时，按照实际的长储。
2.char类型的效率比varchar的效率稍高

3.set字段类型

=========

SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。

指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。

SET最多可以有64个不同的成员。

当创建表时，SET成员值的尾部空格将自动被删除。

4.BLOB 和 TEXT 有什么区别？

====================

TEXT与BLOB的主要差别就是BLOB保存二进制数据，TEXT保存字符数据。。

主要差别

TEXT与BLOB的主要差别就是BLOB保存二进制数据，TEXT保存字符数据。

目前几乎所有博客内容里的图片都不是以二进制存储在数据库的，而是把图片上传到服务器然后正文里使用

标签引用，这样的博客就可以使用TEXT类型。而BLOB就可以把图片换算成二进制保存到数据库中。

5.MySQL数据库预计运维三年,怎么优化？

======================

1、设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。

2、选择合适的表字段数据类型和存储引擎，适当的添加索引。

3、MySQL 库主从读写分离。

4、找规律分表，减少单表中的数据量提高查询速度。

5、添加缓存机制，比如 memcached，apc 等。

6、不经常改动的页面，生成静态页面。

7、书写高效率的 SQL。比如 SELECT ***** FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE

6.什么是存储过程？用什么来调用？

=================

存储过程是一个预编译的 SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。

如果某次操作需要执行多次 SQL，使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用存储过程

7.什么是触发器，可以用来做什么

================

触发器是与 MySQL 数据表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合

触发器的这种特性可以协助应用在数据库端确保数据的完整性。

六、MySQL索引底层实现原理

===============

索引是数据结构。

数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的。

计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等

但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

6.1B树

=====

B树事实上是一种平衡的多叉查找树，也就是说最多可以开m个叉（m>=2）

二、五阶B树

MySQL月末知识汇总

B树的查询过程和二叉排序树比较类似，从根节点依次比较每个结点，因为每个节点中的关键字和左右子树都是有序的，所以只要比较节点中的关键字，或者沿着指针就能很快地找到指定的关键字，如果查找失败，则会返回叶子节点，即空指针。

6.2Plus版 — B+树

==============

作为B树的加强版，B+树与B树的差异在于

所有的关键字全部存储在叶子节点上，且叶子节点本身根据关键字自小而大顺序连接。
非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字。
有n棵子树的节点含有n个关键字（也有认为是n-1个关键字）。

查找：

===

B+树的查找过程，与B树类似，只不过查找时，如果在非叶子节点上的关键字等于给定值，并不终止，而是继续沿着指针直到叶子节点位置。因此在B+树，不管查找成功与否，每次查找都是走了一条从根到叶子节点的路径。

特性

所有关键字都存储在叶子节上，且链表中的关键字恰好是有序的。
不可能非叶子节点命中返回。
非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层。
更适合文件索引系统。

6.3为什么使用B树（B+树）

===============

结局：总结+分享

看完美团、字节、腾讯这三家的一二三面试问题，是不是感觉问的特别多，可能咱们真的又得开启面试造火箭、工作拧螺丝的模式去准备下一次的面试了。

开篇有提及我可是足足背下了Java互联网工程师面试1000题，多少还是有点用的呢，换汤不换药，不管面试官怎么问你，抓住本质即可！能读到此处的都是真爱

Java互联网工程师面试1000题

而且从上面三家来看，算法与数据结构是必备不可少的呀，因此我建议大家可以去刷刷这本左程云大佬著作的《程序员代码面试指南 IT名企算法与数据结构题目最优解》，里面近200道真实出现过的经典代码面试题。

程序员代码面试指南–IT名企算法与数据结构题目最优解

其余像设计模式，建议可以看看下面这4份PDF（已经整理）

更多的Java面试学习笔记如下，关于面试这一块，我额外细分出Java基础-中级-高级开发的面试+解析，以及调优笔记等等等。。。

以上所提及的全部Java面试学习的PDF及笔记，如若皆是你所需要的，那么都可发送给你！

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！
的路径。**

特性

所有关键字都存储在叶子节上，且链表中的关键字恰好是有序的。
不可能非叶子节点命中返回。
非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层。
更适合文件索引系统。

6.3为什么使用B树（B+树）

===============

结局：总结+分享

Java互联网工程师面试1000题

[外链图片转存中…(img-nuNng6eg-1714713469927)]

程序员代码面试指南–IT名企算法与数据结构题目最优解

[外链图片转存中…(img-we3oYHgQ-1714713469928)]

其余像设计模式，建议可以看看下面这4份PDF（已经整理）

[外链图片转存中…(img-IZOIvaQo-1714713469928)]

更多的Java面试学习笔记如下，关于面试这一块，我额外细分出Java基础-中级-高级开发的面试+解析，以及调优笔记等等等。。。

[外链图片转存中…(img-8F7Xmi77-1714713469928)]

以上所提及的全部Java面试学习的PDF及笔记，如若皆是你所需要的，那么都可发送给你！

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！

2401_84149996

关注

19
点赞
踩
25

收藏

觉得还不错? 一键收藏
0
评论
MySQL月末知识总结

看完美团、字节、腾讯这三家的一二三面试问题，是不是感觉问的特别多，可能咱们真的又得开启面试造火箭、工作拧螺丝的模式去准备下一次的面试了。开篇有提及我可是足足背下了Java互联网工程师面试1000题，多少还是有点用的呢，换汤不换药，不管面试官怎么问你，抓住本质即可！能读到此处的都是真爱Java互联网工程师面试1000题。
复制链接

扫一扫