数据库基础知识

微笑天堂猫

已于 2024-04-09 15:31:09 修改

阅读量33

点赞数 1

文章标签： mysql 面试数据库

于 2023-07-14 17:52:42 首次发布

本文链接：https://blog.csdn.net/qq_52235571/article/details/131729073

版权

数据库

视图

为什么使用视图

1.重用sql语句
2.简化复杂的sql操作，在编写查询后，可以方便地重用它而不必知道他的基本查询细节。
3.使用表的组成部分而不是整个表。
4.保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5.更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

视图的规则和限制
1.与表一样，视图必须唯一命名；
2.可以创建任意多的视图；
3.为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
4.视图可以嵌套，可以利用从其他视图中检索数据的查询来构造一个视图。
5.Order by 可以在视图中使用，但如果从该视图检索数据select中也是含有order by，那么该视图的order by 将被覆盖。
6.视图不能索引，也不能有关联的触发器或默认值
7.视图可以和表一起使用

索引

索引是帮助数据库快速获取数据的一种数据结构，类似于字典中的目录。

Mysql底层用B+树存储索引不支持hash

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

索引覆盖

索引覆盖（Index Covering）是指通过在索引中包含所有查询语句中所需的列，可以避免对表中的数据进行额外的访问，从而提高查询效率。(避免了回表操作)

例如，对于一个查询语句：

SELECT col1, col2, col3 FROM table WHERE col1 = x AND col2 = y

如果在table表中建立了一个索引，包含col1、col2和col3三列，那么MySQL可以通过索引定位到符合条件的数据，并在索引中提取col1、col2和col3列的值，无需对表中的数据进行额外的访问。这种方式就叫做索引覆盖。

索引覆盖能够显著提高查询效率，因此在建立索引时应尽量考虑包含查询语句中所需的所有列。

聚簇索引（主键索引）

聚簇索引将数据存储在索引树的叶子节点上。
聚簇索引可以减少一次查询，因为查询索引树的同时就能获取到数据。
聚簇索引的缺点是，对数据进行修改或删除操作时需要更新索引树，会增加系统的开销。
聚簇索引通常用于数据库系统中，主要用于提高查询效率。

非聚簇索引（又称二级索引 / 辅助索引）

非聚簇索引不将数据存储在索引树的叶子节点上，而是存储在数据页中。
非聚簇索引在查询数据时需要两次查询，一次查询索引树，获取数据页的地址，再通过数据页的地址查询数据（通常情况下来说是的，但如果索引覆盖的话实际上是不用回表的）。
非聚簇索引的优点是，对数据进行修改或删除操作时不需要更新索引树，减少了系统的开销。
非聚簇索引通常用于数据库系统中，主要用于提高数据更新和删除操作的效率。

非聚集索引和聚集索引的主要区别在于非聚集索引（辅助索引）的叶子节点不存储表中的数据，而是存储的是该列的主键，想要查询数据还需要根据主键再次进行聚集索引种进行查找。这种再根据聚集索引查找数据的过程，我们称之为回表。
聚簇索引要比非聚簇索引查询效率高很多，特别是范围查询的时候。

非聚簇索引为什么不存数据地址值而存储主键？

我们知道在MyISAM引擎中是没有聚簇索引，都是存的辅助索引。但是和InnoDB不同的是存储的，它是存储索引值和数据地址，而我们InnoDB中存储的是主键ID。

我们要记住知道一个点，数据是会不断变动的，那么它的一个地址也是会跟着不断变动，如果直接存储地址，下次找到的数据可能就不是原来的数据了。如果要解决这个问题的话，成本是非常高的。每次数据变动都需要进行调整。

创建索引的优点缺点是什么？

✅ 优点

索引能够提高数据检索的效率，降低数据库的IO成本。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性，创建唯一索引
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
加速两个表之间的连接，一般是在外键上创建索引

❌ 缺点

需要占用物理空间,建立的索引越多需要的空间越大
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

什么情况索引失效?

这里回答基于最新MySQL8版本，MySQL8失效的以前版本也失效，MySQL8不失效的，以前可能会失效。

使用like并且是左边带%, 右边可以带会走索引（但是并不绝对）
隐式类型转换，索引字段与条件或关联字段的类型不一致。（比如你的字段是int，你用字符串方式去查询会导致索引失效）。
在where条件里面对索引列使用运算或者使用函数。
使用OR且存在非索引列
在where条件中两列做比较会导致索引失效
使用IN可能不会走索引（MySQL环境变量eq_range_index_dive_limit的值对IN语法有很大影响，该参数表示使用索引情况下IN中参数的最大数量。MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10，之后的版本默认值为200。我们拿MySQL8.0.19举例，eq_range_index_dive_limit=200表示当IN (…)中的值 >200个时，该查询一定不会走索引。<=200则可能用到索引。）
使用非主键范围条件查询时，部分情况索引失效。
使用order by可能会导致索引失效
is null is not null ≠ 可能会导致索引失效

最左匹配原则的原理

MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先：
如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；
如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；

有字段为NULL 索引是否会失效？

首先讲答案不一定。即使我们使用is null 或者is not null 它其实都是会走索引的。那为什么会有这样的言论呢？这里首先就得来讲讲NULL值是怎么在记录中存储的，又是怎么在B+树中存储的呢。

那么在InnoDB中分为聚簇索引和非聚簇索引两种，聚簇索引本身是不允许记录为空的，所以可以不不用考虑，那么就剩下非聚簇索引也就是我们的辅助索引。

那既然IS NULL、IS NOT NULL、!=这些条件都可能使用到索引，那到底什么时候索引，什么时候采用全表扫描呢？

首先我们得知道两个东西，第一个在InnoDB引擎是如何存储NULL值的，第二个问题是索引是如何存储NULL值的，这样我们才能从根上理解NULL在什么场景走索引，在什么场景不走索引。

1⃣️ 在InnoDB引擎是如何存储NULL值的？

InnoDB引擎通过使用一个特殊的值来表示null，这个值通常被称为"null bitmap"。null bitmap是一个二进制位序列，用来标记表中每一个列是否为null。当null bitmap中对应的位为1时，表示对应的列为null；当null bitmap中对应的位为0时，表示对应的列不为null。在实际存储时，InnoDB引擎会将null bitmap作为行记录的一部分，存储在行记录的开头，这样可以在读取行记录时快速判断每个列是否为null。

从头开始说理解起来会比较容易，理解了独占表空间文件就更容易理解行格式了，接着往下看：

当我们创建表的时候默认会创建一个*.idb 文件，这个文件又称为独占表空间文件，它是由段、区、页、行组成。InnoDB存储引擎独占表空间大致如下图；

Segment(表空间) 是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

数据段 存放 B + 树的叶子节点的区的集合
索引段 存放 B + 树的非叶子节点的区的集合
回滚段 存放的是回滚数据的区的集合， MVCC就是利用了回滚段实现了多版本查询数据

Extent(区) 在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

（我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。）

Page(页) 记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的，数据页的结构这里我就不讲细说了，总之知道表中的记录存储在「数据页」里面就行。

Row(行) 数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

重点来了！！！

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。

Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了，那就不展开详讲了。
MySQL 5.0 之后引入了 Compact 行记录存储方式，由于 Redundant 不是一种紧凑的行格式，而采用更为紧凑的Compact ，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

那么我们来看看Compact里面长什么样，先混个脸熟。

这里简单介绍一下，Compact行格式其他内容后面单独出一个章节介绍。

NULL值列表（本问题介绍重点）
- 表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。
- 二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL。另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。
- 当然NULL 值列表也不是必须的。当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以节省 1 字节的空间（NULL 值列表占用 1 字节空间）。
- 「NULL 值列表」的空间不是固定 1 字节的。当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

2⃣️ 索引是如何存储NULL值的？

我们知道InnoDB引擎中按照物理存储的不同分为聚簇索引和非聚簇索引，聚簇索引也就是主键索引，那么是不允许为空的。那就不再我们本问题的讨论范围，我们重点来看看非聚簇索引，非聚簇索引是允许值为空的。

在InnoDB中非聚簇索引是通过B+树的方式进行存储的

从图中可以看出，对于s1表的二级索引idx_key1来说，值为NULL的二级索引记录都被放在了B+树的最左边，这是因为设计InnoDB的大叔有这样的规定：

We define the SQL null to be the smallest possible value of a field.

也就是说他们把SQL中的NULL值认为是列中最小的值。在通过二级索引idx_key1对应的B+树快速定位到叶子节点中符合条件的最左边的那条记录后，也就是本例中id值为521的那条记录之后，就可以顺着每条记录都有的next_record属性沿着由记录组成的单向链表去获取记录了，直到某条记录的key1列不为NULL。

3⃣️ 我们了解了上面的两个问题之后，我们就可以来看看，使不使用索引的依据是什么了

实际上来说我们用is null is not null ≠ 这些条件都是能走索引的，那什么时候走索引什么时候走全表扫描呢？

总结起来就是两个字：成本！！！

如何去度量成本计算使用某个索引执行查询的成本就非常复杂了，展开讲这个话题就停不下来了，后面考虑单独列一个篇幅去讲。

这里总结性讲讲：第一个，读取二级索引记录的成本，第二，将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录操作所付出的成本。

要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本（举一个极端的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录执行一遍回表操作，自然不如直接扫描聚簇索引来的快）

所以MySQL优化器在真正执行查询之前，对于每个可能使用到的索引来说，都会预先计算一下需要扫描的二级索引记录的数量，比方说对于下边这个查询：

SELECT * FROM s1 WHERE key1 IS NULL;

优化器会分析出此查询只需要查找key1值为NULL的记录，然后访问一下二级索引idx_key1，看一下值为NULL的记录有多少（如果符合条件的二级索引记录数量较少，那么统计结果是精确的，如果太多的话，会采用一定的手段计算一个模糊的值，当然算法也比较麻烦，我们就不展开说了），这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。当然，对于某些查询，比方说WHERE子句中有IN条件，并且IN条件中包含许多参数的话，比方说这样：

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

这样的话需要统计的key1值所在的区间就太多了，这样就不能采用index dive的方式去真正的访问二级索引idx_key1，而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条（很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多）。

反正不论采用index dive还是依据统计数据估算，最终要得到一个需要扫描的二级索引记录条数，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询。

理解了这个也就好理解为什么在WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件仍然可以使用索引，本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。

大家可以看到，MySQL中决定使不使用某个索引执行查询的依据很简单：就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件。大家以后也多多辟谣吧，没那么复杂，只是一个成本而已。

为什么LIKE以%开头索引会失效？

首先看看B+树是如何查找数据的：

查找数据时，MySQL会从根节点开始，按照从左到右的顺序比较查询条件和节点中的键值。如果查询条件小于节点中的键值，则跳到该节点的左子节点继续查找；如果查询条件大于节点中的键值，则跳到该节点的右子节点继续查找；如果查询条件等于节点中的键值，则继续查找该节点的下一个节点。

比如说我有下面这条SQL：

select * from `user` where nickname like '%冥';

如果数据库中存在南冥 北冥 西冥 东冥 ，那么在B+树中搜索的效率和全表扫描还有什么区别呢？

我走聚簇索引全表扫描还不用回表。

最后在扩展讲一个点，其实不一定会导致索引失效。举个例子：

create table `user`(
  id int primary key auto_increment,
  name varchar(20),
  index idx_name(name),
);

// 那么这种情况是会走索引的。
select id,name from `user` where name like '%冥';

为什么说上面的例子会走索引呢？

首先我们需要查询的id name 这两个字段是不是都在我们的辅助索引中，叶子节点是不是存的索引值和主键值，所以我们只要查辅助索引就可以直接拿到我们的需要的结果了，那么这个叫做索引覆盖。我们观察执行计划会发现它的查询级别是index ，其实也是全表遍历了辅助索引。

第二个问题来了，那为什么就要走辅助索引而不是走全表扫描呢？

因为辅助索引中记录的东西比主键索引少了很多，只有索引值和主键值，但是主键索引中就包含了，其他值、事物ID、MVCC的回流指针等等。再加上索引覆盖不用回表，优化器就认为直接遍历辅助索引的效率高于主键索引。

InnoDB的索引和MyISAM的索引有什么区别？

首先InnoDB和MyISAM都是使用的B+树实现的，但是InnoDB使用的是聚簇索引而MyISAM使用的是非聚簇索引

因为叶子结点将索引和数据放在一起，就决定了聚簇索引的唯一性，一张表里面只能有一个聚簇索引。
InnoDB引擎默认将主键设置为聚簇索引，但如果没有设置主键，那么InnoDB将会选择非空的唯一索引作为代替，如果没有这样的索引，InnoDB将会定一个隐式主键作为聚簇索引。
因为聚簇索引特殊的物理结构所决定，叶子结点将索引和数据存放在一起，在获取数据的速度上是比非聚簇索引快的。
聚簇索引数据的存储是有序的，在进行排序查找和范围查找的速度也是非常快的。
⚠️ 也正因为有序性，在数据插入时按照主键的顺序插入是最快的，否则就会出现页分裂等问题，严重影响性能。对于InnoDB我们一般采用自增作为主键ID。
第二个问题主键最好不要进行更新，修改主键的代价非常大，为了保持有序性会导致更新的行移动，一般来说我们通常设置为主键不可更新。

联合索引，组合索引，复合索引

我们在索引回顾的时候和大家对索引做了一个分类对吧，按照字段个数来分的话，就分为了单列索引和组合索引对吧。那么他们之间的特点是什么呢？我们来看

单列索引 一个索引只包含了一个列，一个表里面可以有多个单列索引，但是这不叫组合索引。
组合索引（联合索引 & 复合索引）一个索引包含多个列。

看上去感觉这组合索引并没有太大作用是吧，我一个列已经有一个索引了，我还要这组合索引干嘛？

真相往往不那么简单，首先我们得承认我们的业务千变万化，我们的查询语句条件肯定是非常多的。

高效率 如果说只有单列索引，那就会涉及多次二级索引树查找，再加上回表，性能相对于联合索引来说是比较低的。
减少开销 我们要记得创建索引是存在空间开销的，对于大数据量的表，使用联合索引会降低空间开销。
索引覆盖 如果组合索引索引值已经满足了我们的查询条件，那么就不会进行回表，直接返回。
这里涉及到了一个重点，叫做最左前缀，简单理解就是只会从最左边开始组合，组合索引的第一个字段必须出现在查询组句中，还不能跳跃，只有这样才能让索引生效，比如说我查询条件里面有组合索引里面的第二个字段，那么也是不会走组合索引的。举个例子

// 假设给username，age创建了组合索引

// 这两种情况是会走索引的
select username,age from user where username = '张三' and age = 18;
select * from user where username = '张三';

// 这种是不会走索引的
select * from user where age = 18;
select * from user where city = '北京' and age = 18;

复合索引创建时字段顺序不一样使用效果一样吗？

// 假设给username，age创建了组合索引

// 这两种情况是会走索引的
select username,age from user where username = '张三' and age = 18;
select * from user where username = '张三';

// 这种是不会走索引的
select * from user where age = 18;
select * from user where city = '北京' and age = 18;

使用Order By时能否通过索引排序？

我们知道在很多场景下会导致索引失效，比如说没有遵循B+树的最左匹配原则，但是也有一些情况是遵循了最左匹配原则但是还是没有走索引，这里我们使用order by进行排序的时候就有不走索引的情况，那么带大家来分析一下

drop table if exists `user`;
drop table if exists `user_example`;
create table `user`(
    `id` int primary key comment '主键ID',
    `card_id` int comment '身份证',
    `nickname` varchar(10) comment '昵称',
    `age` int not null comment '年龄',
    key  `card_id` (`card_id`)
) engine=InnoDB default charset=utf8mb4;

// 这里我们明明对card_id建好了单列索引，那为什么不走索引呢？
select * from `user` order by card_id

如果索引覆盖是可以走索引的
如果带上索引条件是可以走索引的

通过索引排序内部流程

explain select nickname,card_id,age from user order by card_id;

我们在了解mysql底层是怎么排序的之前，我们先来了解一下一个概念 sort buffer .

首先mysql会为每一个线程都分配一个固定大小的sort buffer 用于排序。它是一个具有逻辑概念的内存区域，我们可以通过sort_buffer_size参数来控制，默认值是256kb 。

// 输入查看最，小可以设置为 32K，最大可以设置为 4G。
show variables like 'sort_buffer_size';

由于sort buffer 大小是一个固定的，但是我们待排序的数据量它不是，所以根据它们之间的一个差值呢，就分为了内部排序和外部排序

当待排序的数据量小于等于sort buffer 时，那我们的sort buffer就能够容纳，MySQL就可以直接在内存里面排序就行了，内部排序使用的排序算法是快排
当待排序的数据量大于sort buffer 时，那我们的sort buffer 就不够用了对吧。这个时候MySQL就得要借助外部文件来进行排序了。将待排序数据拆成多个小文件，对各个小文件进行排序，最后再汇总成一个有序的文件，外部排序使用的算法时归并排序

row_id排序

和大家说一个这个参数max_length_for_sort_data ，在我们MySQL中专门控制用户排序的行数据长度参数。默认是4096，也就是说如果超过了这个长度MySQL就会自动升级成row_id算法。

// 默认max_length_for_sort_data的大小为4096字节
show variables like 'max_length_for_sort_data';

row_id排序的思想就是把不需要的数据不放到sort_buffer中，让sort_buffer中只存放需要排序的字段。

举个例子：

explain select nickname,card_id,age from user order by card_id;

我们前面说到了sort buffer，在sort buffer里面进行排序的数据是我们select的全部字段，所以当我们查询的字段越多，那么sort buffer能容纳的数据量也就越小。而通过row_id排序就只会存放row_id 字段和排序相关的字段。其余的字段等排序完成之后通过主键ID进行回表拿。

group by 分组和 order by 在索引使用上有什么不同吗？

没什么太大的差异group by实际是先进行排序，再进行分组。所以遵循order by的索引机制。

B±树

B-树

B+Tree
在 BTree 的基本上，对 BTree 进行了优化：只有叶子节点才会存储键值 - 数据，非叶子节点只存储键值和子节点的地址；叶子节点之间使用双向指针进行连接，形成一个双向有序链表，便于范围查找。最小页为16k。

B+树的优势：
1.I/O代价更低 B+树非叶子节点不存放data只存放索引，从而使树的高度降低，磁盘IO次数更少。

2.查询效率稳定。B+树由于所有data都放在叶子节点中，因此每次查询都要走完整的根节点到叶子节点的路径，所有查询的路径长度相同，查询效率更加稳定。

3.更有利于范围查询。B+树叶子节点之间有指针，注意是双向的指针，更利于范围查询。

B+树退化的极端情况
当每次插入的元素都是二叉查找树中最大的元素,二叉查找树就会退化成了一条链表,查找数据的时间复杂度变成了 O(n)

事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。
事务的四大特性
1.原子性要么全部执行，要么全部不执行
2.一致性事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性和原子性密切相关
3.持久性事务一旦提交，它对数据库中数据的操作是永久的
4.隔离性事务与事务之间数据要相互隔离，事务之间互不干扰

**-事务的原子性、隔离性由锁机制实现 **
-事务的持久性和一致性由事务的 redo 日志和undo 日志来保证

redo log

Redo log的主要作用是用于数据库的崩溃恢复
Redo log 是重做日志，提供再写入操作，实现事务的持久性；

Redo log可以简单分为以下两个部分：
保存在内存中重做日志的缓冲 (redo log buffer),是易失的
保存在硬盘中重做日志文件 (redo log file)，是持久的

redo工作流程
InnoDB 的更新操作采用的是 Write Ahead Log (预先日志持久化)策略，即先写日志，再写入磁盘。
当一条记录更新时，redo流程大致如下在内存更新数据后，会把更新后的记录写入到 redo log buffer 中。
第一步：InnoDB 会先把记录从硬盘读入内存
第二部：修改数据的内存拷贝
第三步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
第四步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
第五步：定期将内存中修改的数据刷新到磁盘中(注意注意注意，不是从redo log file刷入磁盘，而是从内存刷入磁盘，redo log file只在崩溃恢复数据时才用)，如果数据库崩溃，则依据redo log buffer、redo log file进行重做，恢复数据,这才是redo log file的价值所在

mysql的事务隔离级别

默认：可重复读
oracle:默认读已提交
1.读未提交（READ-UNCOMMITTED）
最低的隔离级别，允许读取尚未提交的数据变更，可能造成脏读、不可重复读、幻读。
2.读已提交（READ-COMMITTED）
允许读取并发事务已经提交的数据，可以避免脏读，但是可能造成不可重复、幻读。
3.可重复读（REPEATABLE-READ）
对同一字段多次读取的结果都是一致的，除非本身事务修改，可以避免脏读和不可重复读，但是可能造成幻读。

在MYSQL的RR隔离级别下，也解决了幻读的问题，MVCC（一致性快照），一个是间隙锁。

4.可串行化（SERIALIZABLE）
最高的隔离级别，完全服从ACID的隔离级别，所有的事务依次执行，可以避免脏读、不可重复读、幻读。

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

锁

根据粒度和性质划分

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VBR0YdqE-1689328318598)(https://cdn.nlark.com/yuque/0/2023/png/26667770/1683374161146-800d4ee2-f17b-428a-a3ab-e7357ed135d5.png#averageHue=%23ededed&clientId=u697494e7-939a-4&from=paste&height=121&id=u25bcf4a8&originHeight=181&originWidth=542&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=65754&status=done&style=none&taskId=ufd83bba0-4e44-4132-87ff-e6f43695c5e&title=&width=361.3333333333333)]

**行级锁的使用有什么注意事项？ **
InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行
UPDATE 、 DELETE 语句时，如果 WHERE 条件中字段没有命中唯⼀索引或者索引失效的话，就
会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，⼀定要多多
注意！！！
不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。

锁升级
InnoDB 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。原因是 InnoDB 是将 primary key index和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondary index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失效，行锁自然就会升级为表锁。

在不使用索引的情况下加锁

运行结果：

在不给 age 字段加索引的情况下进行排它锁的加锁操作，可以看到尽管加锁的数据是不同的，但是事务2在加锁时出现了锁等待现象。说明此时事务1从行级锁升级为表锁，导致事务2在给 age = 15 的数据加锁时出现了锁等待现象。

在使用普通索引的情况进行加锁
alter table user add index idx_age(age); --给age字段加个索引：

运行结果：

在加了索引之后，再一次进行以上操作。可以看到，user 表不再进行表锁，那是因为行锁是建立在索引字段的基础上，如果行锁锁定的列表锁索引列则会升级为表锁。

范围性查询测试

运行结果：

当我们使用range查询时，需要加行锁的数据过多，mysql会认为行锁开销过大，就会升级为表锁。

总结：
行锁是建立在索引的基础上。
普通索引的数据重复率过高导致索引失效，行锁升级为表锁。

SQL优化手段

1、查询语句中不要使用select *
2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代
3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，
union all会更好)
5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表
扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有
null值，然后这样查询： select id from t where num=0

常见的存储引擎

MySQL中常用的四种存储引擎分别是： MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默认的存储引擎为InnoDB。
InnoDB存储引擎
InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。
优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。
缺点：占用的数据空间相对较大。
适用场景：需要事务支持，并且有较高的并发读写频率。

MyISAM存储引擎
数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。
.MYD是MyISAM的 内容文件
.MYI是MyISAM的 索引文件
.frm是所有引擎都有的是 表结构文件
优点：访问速度快。
缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。
适用场景：对事务完整性没有要求；表的数据都会只读的。

MEMORY存储引擎
MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。
MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。
优点：访问速度较快。
缺点：

哈希索引数据不是按照索引值顺序存储，无法用于排序。
不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
只支持等值比较，不支持范围查询。
当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

MyISAM和InnoDB的区别？

InnoDB 支持事务，MyISAM 不支持
InnoDB 支持外键，而 MyISAM 不支持
InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；
MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；
InnoDB 不保存表的具体行数，MyISAM 用一个变量保存了整个表的行数。
MyISAM 采用表级锁(table-level locking)；InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁。

MVCC

多版本并发控制（MVCC=Multi-Version Concurrency Control）
宏观：MySQL的InnoDB存储引擎下RC、RR且快照读下基于MVCC做数据的多版本并发控制

MVCC 的实现原理
MVCC的实现原理主要是依赖每一行记录中两个隐藏字段，undo log，ReadView

undo log版本链

undo log可以理解成回滚日志,它存储的是老版本数据。在表记录修改之前，会先把原始数据拷贝到undo log里，如果事务回滚，即可以通过undo log来还原数据。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S0xMtzMs-1689328318599)(https://cdn.nlark.com/yuque/0/2023/png/26667770/1683882056492-84adb86e-837f-47f3-874d-722ca7f0b59e.png#averageHue=%23ededec&clientId=uad752387-d626-4&from=paste&height=267&id=ua8cac2eb&originHeight=401&originWidth=838&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=212647&status=done&style=none&taskId=ufd35c8bb-98fa-4a09-8f83-69f8e229bd1&title=&width=558.6666666666666)]
1、事务回滚时，保证一致性
2、如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本(用于MVCC快照读)。

ReadView

ReadView 保存了当前事务开启时所有活跃的事务列表。
用作可见性判断，判断当前事务是否有资格访问该行数据
ReadView是如何保证可见性判断的呢？我们先看看 ReadView 的几个重要属性

trx_ids: 当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。(重点注意:这里的trx_ids中的活跃事务，不包括当前事务自己和已提交的事务，这点非常重要)
up_limit_id: 目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。
low_limit_id: 活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id。
creator_trx_id: 表示生成该 ReadView 的事务的事务id

访问某条记录的时候如何判断该记录是否可见,具体规则如下：

何时创建ReadView？
只有在select的时候才会创建ReadView。但在不同的隔离级别是有区别的:
在RC隔离级别下，是每个select都会创建最新的ReadView；
而在RR隔离级别下，则是当事务中的第一个select请求才创建ReadView(下面会详细举例说明)。
insert/update/delete操作不会创建ReadView。但是这些操作在事务开启(begin)且其未提交的时候，那么它的事务ID,会存在在其它存在查询事务的ReadView记录中，也就是trx_ids中。

大表查询慢怎么优化？

某个表有近千万数据，查询比较慢，如何优化？
当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
索引优化，SQL优化。最左匹配原则等，参考：https://topjavaer.cn/database/mysql.html#%E4%BB%80%E4%B9%88%E6%98%AF%E8%A6%86%E7%9B%96%E7%B4%A2%E5%BC%95
建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能
利用缓存。利用Redis等缓存热点数据，提高查询效率
限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内
读写分离。经典的数据库拆分方案，主库负责写，从库负责读
通过分库分表的方式进行优化，主要有垂直拆分和水平拆分
合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDERBY命令上涉及的列建立索引

数据异构到es
冷热数据分离。几个月之前不常用的数据放到冷库中，最新的数据比较新的数据放到热库中
升级数据库类型，换一种能兼容MySQL的数据库（OceanBase、tidb）

count(1)、count(*)和count(字段名)的区别

嗯，先说说count(1) and count(字段名)的区别。
两者的主要区别是

count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
count(字段名) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。

接下来看看三者之间的区别。
执行效果上：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(字段名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：