MySQL面试题

一、MySQL基础

1.什么是关系型数据库？

就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。

常见的关系型数据库

MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite）

2.什么是 SQL？

SQL 是一种结构化查询语言(Structured Query Language)，专门用来与数据库打交道，目的是提供一种从数据库中读写数据的简单有效的方法。

二、MySQL 字段类型

MySQL 字段类型可以简单分为三大类：

数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

1.整数类型的 UNSIGNED 属性有什么用？

可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。

2.CHAR 和 VARCHAR 的区别是什么？

两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

3.VARCHAR(100)和 VARCHAR(10)的区别是什么？

VARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符;

二者存储相同的字符串，所占用磁盘的存储空间其实是一样的;

不过，VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。

4.DECIMAL 和 FLOAT/DOUBLE 的区别是什么

DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

DECIMAL 用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。

在 Java 中，MySQL 的 DECIMAL 类型对应的是 Java 类 java.math.BigDecimal

5.为什么不推荐使用 TEXT 和 BLOB？

TEXT 类型类似于 CHAR（0-255 字节）和 VARCHAR（0-65,535 字节），但可以存储0-65,535 字节的字符串，即长文本数据，例如博客内容。

BLOB 类型主要用于存储二进制大对象（0-65KB），例如图片、音视频等文件。

这两种类型具有一些缺点和限制，例如：

不能有默认值。
在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表（《高性能 MySQL》书中有提到）。
检索效率较低。
不能直接创建索引，需要指定前缀长度。
可能会消耗大量的网络和 IO 带宽。
可能导致表上的 DML 操作变慢。

6.DATETIME 和 TIMESTAMP 的区别是什么？

DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。

TIMESTAMP 只需要使用 4-7 个字节的存储空间，但是 DATETIME 需要耗费 2-8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。

DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
Timestamp：1970-01-01 00:00:01[.000000] ～ 2038-01-19 03:14:07[.999999]

什么是时间戳？

从「1970-1-1 00:00:00 +0:00」这个基准时间开始以秒计时，，用整数表示距离这个基准时间的时长；

优点是只用一个整数就能表示时间，且不需要考虑时区，缺点是不够直观，可读性差；

7.NULL 和 '' 的区别是什么？

NULL 跟 ''(空字符串)是两个完全不一样的值，区别如下：

NULL 代表一个不确定的值,就算是两个 NULL,它俩也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。
''的长度是 0，是不占用空间的，而NULL 是需要占用空间的。
NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

为什么 MySQL 不建议使用 NULL 作为列默认值

①使用count()、max()、min()等聚合函数时会忽略NULL值，可能导致结果不符合我们的期望值；

②使用distinct、group by、order by进行排序、去重、分组时，NULL值也会被加入结果，可能会造成干扰；

③NULL值本身还需要占用一个字节；

推荐使用0或者""来代替NULL值；

8.Boolean 类型如何表示？

MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1，分别对应 false 或 true；

三、MySQL 基础架构

1.一条SQL语句在MySQL中的执行过程

①查询语句

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

Ⅰ. 权限校验（&查询缓存）：先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，缓存命中则直接返回结果，缓存未命中则执行下一步；

Ⅱ. 分析器：进行词法分析，提取 SQL 语句的关键元素（要查询的列、表名、查询条件等）；然后进行语法分析，判断这个 SQL 语句是否有语法错误，若有错则报错，没有错误则执行下一步；

Ⅲ. 优化器：优化器根据自己的优化算法进行选择执行效率最好的一个方案；

Ⅳ. 执行器&存储引擎：执行器操作存储引擎，按照执行方案执行SQL语句，并调用引擎接口，返回执行结果；

②更新语句（包括增删改）

update tb_student A set A.age='19' where A.name=' 张三 ';

Ⅰ. 分析器：进行词法分析，提取 SQL 语句的关键元素（要查询的列、表名、查询条件等）；然后进行语法分析，判断这个 SQL 语句是否有语法错误，若有错则报错，没有错误则执行下一步；

Ⅱ. 权限校验：检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，则执行下一步；

Ⅲ. 执行器&存储引擎：执行器操作存储引擎，执行SQL语句；

Ⅳ. redo log(prepare 状态)：InnoDB 引擎把更新的数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交；

Ⅴ. binlog&redo log(commit 状态)：执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态；

2.MySQL 一行记录是怎么存储的？

①MySQL 的数据存放在哪个文件？

一张数据库表的数据是保存在「表名字.ibd 」的文件里的，这个文件也称为独占表空间文件；

②表空间文件的结构是怎么样的？

表空间由段（segment）、区（extent）、页（page）、行（row）组成：

行：数据库表中的记录都是按行进行存放的；

页：InnoDB的数据读取是以页为单位的，每页默认大小为16KB；

区：在表中数据量大的时候，为某个索引分配空间就以区为单位了，每个区默认大小为1MB，一个区中有64个连续页；

段：表空间分为多个段，每个段包含多个区；

③InnoDB行格式有哪些？

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

Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了；

Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact；

Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式

④COMPACT 行格式长什么样？

一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分；

记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息；

Ⅰ.变长字段长度列表：逆序存放记录中所有变长字段的真实数据占用的字节数；

——定长字段或NULL值在此字段值不做记录，若没有变长字段则去掉此字段；

——每一列的长度用整字节表示，若单列最大字节数<=255KB，则用1KB表示每一列的长度，若单列最大字节数>255KB，则用2KB表示每一列的长度；

Ⅱ.NULL值列表：如果记录中存在NULL值，则在NULL值列表中用一个二进制位来表示一个列，0表示非NULL，1表示是NULL，逆序排列（NULL值列表长度以字节为单位，不足的高位补0）；

——当每个字段都定义为NOT NULL时，行格式中就会去掉NULL值列表，这样可以至少省下1KB的空间；

——变长字段长度列表、NULL值列表逆序存放的原因：使得位置靠前的记录的真实数据和数据对应的字段长度列表、NULL值列表这些信息可以更接近，更有可能位于同一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率；

Ⅲ：记录头信息：记录头中包含很多信息，比如有delete_mask字段表示此条记录是否已被逻辑删除，next_record字段记录下一条记录的地址（指向的是下一条记录的「记录头信息」和「真实数据」之间的位置）；

记录真实数据部分除了我们定义的字段列1、列2、...，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer；

Ⅰ.row_id：如果建表时指定了主键或是唯一约束列，则不会有此字段，否则会添加此字段用于指定某列作为主键，占用6KB；

Ⅱ.trx_id：记录此条记录有由哪个事务生成，占用6KB；

Ⅲ.roll_ptr：记录上一版本的指针，占用7KB；

④varchar(n) 中 n 最大取值为多少？

首先，MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏字段和记录头信息）占用的字节长度加起来不能超过 65535 个字节；

这里我们只考虑单字段记录的情况，由于不包括隐藏字段和记录头信息，故只考虑变长字段长度列表和NULL值列表所占空间即可，

因为单列最大字节数>255KB，故变长字段长度列表占2KB，允许该列为NULL的话，再加上1KB的NULL值列表，

故在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532；

⑤行溢出后，MySQL 是怎么处理的？

对于Compact行格式：当发生行溢出时，在记录的真实数据处会存放该列的一部分数据，将剩余的数据放在溢出页中，并在真实数据除用20KB存放溢出页的地址；

对于Compressed 和 Dynamic 这两种格式：记录的真实数据处只会用20KB存放溢出页的地址，全部数据都放在溢出页中；

四、MySQL存储引擎

1.MySQL 支持哪些存储引擎？默认使用哪个？

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

2.MySQL 存储引擎架构了解吗？

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，存储引擎是基于表的，而不是数据库，可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要；

3.MyISAM 和 InnoDB 有什么区别？

InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。
MyISAM 不支持外键，而 InnoDB 支持。
MyISAM 不支持 MVCC，而 InnoDB 支持。
虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。
MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。
InnoDB 的读写性能比 MyISAM 更强大。

4.MyISAM 和 InnoDB 如何选择？

一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择 MyISAM 也是一个不错的选择。

但是一般情况下，我们都是需要考虑到这些问题的。

五、MySQL索引

1.InnoDB中索引的底层数据结构是什么？为什么选用B+树？

①相比于Hash表，B+树多支持了范围匹配和排序的功能；

②相比于二叉树，B+树的层数更少，搜索效率更高；

③相比于B树，B+树中所有数据都存放在叶子节点中，非叶子结点只起索引数据的作用，使得一页中存储的键值和指针都更多，相同数据量下消耗的空间更小；

2.聚簇索引和非聚簇索引

聚簇索引就是索引结构和数据一起存放的索引，主键索引就是聚簇索引，特点是叶子节点中存储的索引和对应的数据；

非聚簇索引就是索引结构和数据分开存放的索引，二级索引就是非聚簇索引，特点是叶子节点中只存储了主键，还需要根据主键再回表查数据；

3.键值为NULL的记录是怎么在索引的B+树中存放的？

首先确定一个事实，主键必定是非空的，所以主键索引/聚簇索引中不可能出现NULL值，NULL值只有可能出现在二级索引中；

其次，在二级索引中，NULL值被视为列中最小的值，放在B+树叶子节点链表中的最左侧；

4.索引的优缺点？何时适合使用索引，何时不适合？

优点：

最大的好处是大大提高查询速度

缺点：

需要占用物理空间；

创建索引和维护索引要耗费时间，会降低表的增删改的效率；

何时适合使用：

字段有唯一性限制的，或是重复度较低的；

经常用于 WHERE 查询条件的字段；

经常用于 GROUP BY 和 ORDER BY 的字段；

何时不适合使用：

字段中存在大量重复数据，例如性别；

表数据太少，或是不经常用于WHERE、GROUP BY、ORDER BY查询的字段；

需要经常更新的字段；

六、MySQL事务

1.数据库的ACID特性

原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成；——通过undo log实现

一致性（Consistency）：事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态；（例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的）——通过A、I、D实现

隔离性（Isolation）：并发访问数据库时，一个事务不会被其他事务所干扰，每个事务之间数据空间是独立的；——通过MVCC或锁机制实现

持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是永久的，即使数据库发生故障也不应该对其有任何影响；——通过redo log实现

——其中A、I、D是手段，C是目的；

2.并发事务可能会引发哪些问题？

①脏读

一个事务读到了另一个未提交事务修改过的数据，就称为脏读；

②不可重复读

在一个事务内多次读取同一个数据，前后读到的结果不同，就称为不可重复读；

③幻读

在一个事务内多次使用同样的查询条件得到的记录条数不一样，就称为幻读；

④丢弃修改

两个写事务T1 T2同时对A=0进行递增操作，结果T2覆盖T1，导致最终结果是1 而不是2，事务被覆盖；

不可重复读和幻读的区别？

不可重复读强调内容修改，幻读强调记录增加或减少；

将两种分为两个概念主要是解决不可重复读和幻读的手段不同；

3.并发事务的控制方式

①锁

——详见第七章

②MVCC

什么是MVCC？

MVCC（Multi-Version Concurrency Control）多版本并发控制是一种用于在多个并发事务同时读写数据库时保证数据库的隔离性和一致性的机制，

核心思想时为每条数据记录维护多个版本的数据，当一个事务要对数据库中的数据进行修改时，MVCC会为该事务创建一个数据快照，而不是直接修改实际的数据行；

MVCC有哪些功能？

Ⅰ.在可重复读隔离级别下解决不可重复读问题和部分解决幻读问题；

InnoDB如何实现MVCC？

通过事务的Read View和数据行隐藏字段中的TRX_ID来判断数据的可见性，如不可见，则通过数据行隐藏字段中的ROLL_PTR找到undo log中的历史版本，

在同一事务中，用户只能看到该事务创建的Read View之前已提交的修改和该事务本身做出的修改；

4.事务的隔离级别有哪些？

①读未提交（Read Uncommitted）:允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

②读已提交（Read Committed）：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

③可重复读（Repeatable Read）：一个事务对同一字段的多次读取结果都是一致的，可以阻止脏读和不可重复读，但幻读仍有可能发生。——MySQL 默认支持的隔离级别

④串行化（Serializable）：所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

5.在可重复读隔离级别下，有哪些解决幻读的方案？

针对快照读（普通select语句），是通过MVCC方式解决了幻读，因为在可重复读隔离级别下，快照读总是读取本事务开始时的行数据版本，中途其他事务即使插入了数据也不会产生影响；

针对当前读（select ... for update语句），是通过next-key lock（临键锁，也即记录锁+间隙锁）解决了幻读，因为在一个事务执行select .. for update时，其他事务向相同区间插入记录的操作会被阻塞；

在可重复读隔离级别下，可以完全解决幻读问题吗？

不可以，比如在两次快照读间添加一条更新语句，同时另一个事务在此期间插入记录，则两次快照读的记录数不一样，

或是先执行快照读，再执行当前读，同时另一个事务在此期间插入记录，则前后读取的记录数也不一样；

解决办法是尽量先使用当前读对查询范围进行上锁，这样就可以最大可能避免幻读；

记录锁+间隙锁可以防止删除操作而导致的幻读吗？

可以，加锁之后删除和插入/修改操作都是会被阻塞的；

七、MySQL锁

——当事务执行 commit 后，事务过程中生成的锁都会被释放

1.表级锁和行级锁

表级锁：MySQL中除了全局锁以外锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。

不过，触发锁冲突的概率最高，高并发下效率极低，MyISAM 和 InnoDB 引擎都支持表级锁。

——尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能

行级锁：MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。

但加锁的开销也最大，加锁慢，会出现死锁。只有 InnoDB 引擎支持表级锁。

2.行级锁具体有哪几类？（行级锁是InnoDB引擎独有）

①Record Lock记录锁

用于锁住一条记录，禁止其它事务对该条记录进行更新、删除操作；

有共享锁（S锁）和互斥锁（X锁）之分，共享锁只与共享锁兼容，互斥锁不与任何锁兼容；

②Gap Lock间隙锁

只存在于可重复读隔离级别，用于锁住一个范围禁止插入数据，防止幻读现象的发生；

间隙锁没有共享锁和互斥锁之分，所有间隙锁都是兼容的；

③Next-Key Lock临键锁

用于锁住一个范围和记录本身，相当于记录锁+间隙锁的组合，既能防止该记录被修改或删除，又可以防止数据插入范围内；

④插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁，如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止，

在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁；

3.何时会加行级锁，具体加什么锁？

①什么SQL语句会加行级锁？

普通的 select 语句是不会对记录加锁的（除了串行化隔离级别），因为它属于快照读，是通过 MVCC（多版本并发控制）实现的；

在select语句后加lock in share mode/for share、for update就可以加共享锁、独占锁了（必须是在事务内执行才会加锁，同时事务提交之后就会释放）；

update、delete语句都会给记录加行级锁，且都是独占锁；

②查询语句具体加哪一种行级锁？（delete、update本质上也是先进行查询，在对查询到的记录进行下一步操作）

首先需要说明的是，行级锁加锁的对象是索引，加锁的基本单位是next-key lock，但在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁；

可以通过 select * from performance_schema.data_locks\G; 这条语句，查看事务执行 SQL 过程中加了什么锁。

Ⅰ.唯一索引等值查询

若查询记录存在，则给该条记录加上独占的记录锁（X型record lock）；

select * from user where id = 1 for update;

若查询记录不存在，则给该条记录所在的最小区间加上独占的间隙锁（X型 gap lock）；

select * from user where id = 2 for update;

Ⅱ.唯一索引范围查询

在保证查询范围内记录不被修改的最小范围内，添加独占间隙锁/临键锁；

select * from user where id > 15 for update;

select * from user where id >= 15 for update;

select * from user where id < 6 for update;

Ⅲ.非唯一索引等值查询

若查询记录存在，则给该条记录对应的主键索引加上独占的记录锁，同时给二级索引加上最小范围的临键锁和间隙锁（为了避免二级索引值相同，但主键索引值相同的记录插入造成幻读）；

select * from user where age = 22 for update;

若记录不存在，则给二级索引加上最小范围内的间隙锁即可；

select * from user where age = 25 for update;

Ⅳ.非唯一索引范围查询

给二级索引加上最小范围的临键锁，给主键索引加上记录锁；

select * from user where age >= 22  for update;

Ⅴ.无索引查询

扫描全表，同时将表中的每条记录都加锁（和表锁效果差不多，但是不是表锁）；

③插入语句具体加哪一种行级锁？

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。

只有在特殊情况下，才会将隐式锁转换为显示锁：

Ⅰ.记录之间加有间隙锁

此时会生成一个插入意向锁，然后锁的状态设置为等待状态，插入语句进入阻塞状态；——防止出现幻读

Ⅱ.插入的记录和已有记录间存在唯一键冲突

此时会报错，同时，若是主键索引发生冲突，则会给发生冲突的记录的主键索引加上S型记录锁；

若是唯一二级索引发生冲突，则会给发生冲突的记录的二级索引加上S型next-key锁；

Ⅲ.两个事务执行过程中，执行了相同的 insert 语句的场景（发生了唯一二级索引冲突）

第一条insert语句会给发生冲突的二级索引加上X型记录锁，

第二条insert语句试图给发生冲突的记录的二级索引加上S型next-key锁，但和X型记录锁不兼容，故被阻塞；

4.共享锁和互斥锁

共享锁：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取；

互斥锁：又称写锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取；

共享锁只与共享锁是兼容的，互斥锁与任何锁都不兼容；

5.什么是意向锁？

意向锁是一种表级锁，用于快速判断表里是否有记录被加锁，进而判断是否可以对该表使用表锁；

具体用法：

在表中的记录加上共享锁/独占锁之前，会先在表级别加上一个意向共享/独占锁（引擎自动完成，无需用户手动维护），

而当想要给表加上独占表锁时，会先检查该表是否存在意向共享/独占锁，若存在则不能加独占表锁，

当想要给表加上共享表锁时，会先检查该表是否存在意向独占锁，若存在则不能加共享表锁，

——意向共享、互斥锁之间互相兼容，与行锁兼容，

——意向共享锁与共享表锁兼容，与互斥表锁不兼容；

——意向互斥锁与共享、互斥表锁均不兼容；

6.当前读和快照读的区别？

①快照读

就是普通的select语句（未上锁的那种），快照即记录的历史版本，每行记录可能存在多个版本，

在读已提交、可重复读隔离级别下使用快照读，其中在读已提交隔离级别下，快照读总是读取被锁定行的最新数据版本；

而在可重复读隔离级别下，快照读总是读取本事务开始时的行数据版本；

②当前读

就是增删改和上锁的select语句，当前读就是给行记录加上共享或互斥锁之后，再进行操作；

7.发生死锁的情况

time 1：事务A执行更新操作，为(20, 30)区间加上间隙锁；

time 2：事务B执行更新操作，为(20, 30)区间加上间隙锁，间隙锁是相互兼容的；

time 3：事务A向(20, 30)区间执行插入操作，由于事务B为该区间上了间隙锁，故事务A阻塞，并生成插入意向锁，等待事务B释放间隙锁；

time 4：事务B向(20, 30)区间执行插入操作，由于事务A为该区间上了间隙锁，故事务B阻塞，并生成插入意向锁，等待事务A释放间隙锁；

满足互斥、持有等待、不可强占、循环等待四个条件，发生死锁；

8.数据库层面如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

①设置事务等待锁的超时时间。

当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

②开启主动死锁检测。

主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

八、MySQL日志

1.undo log回滚日志

①什么是undo log？

undo log 是一种用于撤销回退的日志。

在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚；

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id，通过 trx_id 可以知道该记录是被哪个事务修改的，通过 roll_pointer 指针可以将多个版本的 undo log 串成一个链表；

②undo log的两大作用

①实现事务回滚，保证事务的原子性；

②和ReadView一起实现MVCC多版本并发控制；

（MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录）

2.redo log重做日志

①什么是redo log？

redo log 是物理日志，记录了某个数据页做了什么修改，保证了事务的持久性，主要用于掉电等故障恢复；

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘；

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态；

②redo log 和 undo log 区别在哪？

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务；

③redo log什么时候刷盘？

MySQL 正常关闭时；

redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时；

InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘；

每次事务提交时，具体策略由innodb_flush_log_at_trx_commit 参数控制：

Ⅰ.参数为0，每次事务提交时，redo log 仍留在 redo log buffer 中，后台线程每隔1s调用write()将redo log写到操作系统的page cache中，再调用fsync()持久化到磁盘；

——参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;

Ⅱ.参数为1，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘；

——参数为 1 的策略，MySQL 进程的崩溃或是操作系统的崩溃都不会导致数据的丢失;

Ⅲ.参数为2每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到操作系统的page cache中，后台线程每隔1s调用fsync()持久化到磁盘；

——参数为 2的策略，MySQL 进程的崩溃不会导致上一秒钟所有事务数据的丢失，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失；

数据安全性：参数 1 > 参数 2 > 参数 0，写入性能：参数 0 > 参数 2> 参数 1；

④redo log 文件写满了怎么办？

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置；

如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作；

3.binlog归档日志

①什么是binlog？

binlog是 Server 层生成的日志，主要用于数据备份和主从复制；

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

②redo log 和 binlog 有什么区别？

1、适用对象不同：

binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
redo log 是 Innodb 存储引擎实现的日志；

2、文件格式不同：

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：
- STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
- ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
- MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

3、写入方式不同：

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4、用途不同：

binlog 用于备份恢复、主从复制；
redo log 用于掉电等故障恢复。

5、写入磁盘时机不同：

binlog 只有在事务提交时才会写入磁盘；
redo log 不论采用哪种刷盘策略，后台线程每隔1s都会将 redo log buffer 持久化到磁盘；

③如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

④MySQL主从复制是如何实现的？

MySQL 集群的主从复制过程梳理成 3 个阶段：

写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
回放 Binlog：回放 binlog，并更新存储引擎中的数据。

⑤binlog 什么时候刷盘？

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中，

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

4.两阶段提交

①为什么需要两阶段提交？

因为 redo log 影响主库的数据，binlog 影响从库的数据，因此在持久化 redo log 和 binlog 这两份日志的时候，如果出现半成功（一个成功另一个失败）的状态，就会造成主从环境的数据不一致性，

为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决；

②两阶段提交的过程是怎样的？

就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，

对于处于 prepare 阶段的 redo log，

如果redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务；

如果redolog 和 binlog 都已经完成了刷盘，则提交事务；

5.MySQL 磁盘 I/O 很高，有什么优化的方法？

事务在提交的时候，需要将 binlog 和 redo log 持久化到磁盘，那么如果出现 MySQL 磁盘 I/O 很高的现象，我们可以通过控制以下参数，来 “延迟” binlog 和 redo log 刷盘的时机，从而降低磁盘 I/O 的频率：

设置组提交的两个参数： binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，延迟 binlog 刷盘的时机，从而减少 binlog 的刷盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但即使 MySQL 进程中途挂了，也没有丢失数据的风险，因为 binlog 早被写入到 page cache 了，只要系统没有宕机，缓存在 page cache 里的 binlog 就会被持久化到磁盘。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000），表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢 N 个事务的 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢数据；

九、MySQL性能优化

参考文章：https://top.interviewguide.cn/issue/496

0.如何排查一条慢SQL？可以从哪些方面入手？

①发现慢SQL：开启mysql的慢日志查询，设置好时间阈值，对慢查询命令进行捕获；

②分析慢SQL：通过explain的指令去查这个sql的执行计划；

③优化慢SQL：可以从以下五个角度去进行思考优化：SQL优化、资源占用、业务改造、数据减少、源头替换。

1.SQL优化

参考文章：https://www.cnblogs.com/tangyanbo/p/4462734.html

①索引优化

Ⅰ.索引缺失

对于经常用于where查询条件、group by、order by，或是多表连接中的字段需要建立索引，必要时建立联合索引；

Ⅱ.联合索引字段顺序

建立联合索引时，区分度最高的在最左边，尽早过滤掉大部分数据；

存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置，避免索引失效；

Ⅲ.避免索引失效

索引失效的情况：

a.索引可选择性(区分度)差

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大扫描的记录数越少，所以尽量选择区分度高的列作为索引；

唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0（实际为0.00003，考虑精度可认为=0）。一般对于区分度大于0.1的查询字段都要建立索引。

如果字段的可选择性非常差，使用索引比全表扫描还慢。因为要先跑一遍索引，然后根据没有消除几个记录的索引再回表跑差不多大半个的全表，结果还不如直接跑全表。

对于查询来讲，最好的索引就是唯一性，一次即可定位，对于重复数据很多的列不适合建立索引，因为过滤后数据量仍然会很大，先走索引在走表，所以很慢。

b.避免类型隐式转换

索引字段的数据类型和查询的数据类型一定要匹配上

如查看该字段是int类型，但是查询条件值是字符串： sql SELECT * FROM t WHERE c = 'aa'，会导致SQL不走索引，而导致全表扫描。
通过在explain语句后增加extended即explain extended 'sql语句'，再执行show warnings查看是否存在隐式转换以及哪个字段存在隐式转换。

c.最左匹配原则

在使用联合索引时，查询应当从索引的最左列开始，并且不能跳过其中的列，否则会造成后面的字段索引失效；

d.范围查询>、<、>=、<=、between、like可能导致索引失效

在使用联合索引时，如果出现范围查询（>、<），则范围查询右侧的列索引失效；

使用>=、<=、between、like前缀匹配则不会使范围查询右侧的索引失效；

（原理解析参考文章：https://mp.weixin.qq.com/s/8qemhRg5MgXs1So5YCv0fQ?poc_token=HJ_vEmWjM6Fus-dXC9y5-1FTThuFkqV6AyjdKEHk

因为联合索引的B+树是先按左侧的字段排序，在左侧字段相同的情况下再按右侧字段排序，换言之，右侧字段只有在左侧字段相等的情况下才是有序的；

因此，对于>、<：因为不存在相同的左侧字段，故右侧的列完全无序，因而无法使用索引；

而对于>=、<=、between、like前缀匹配：因为都还存在一部分相同的左侧字段，因而这部分是可以利用索引的，虽然后面左侧字段不相同时就需要挨个扫描了）

e.!=、not in、IS NULL、IS NOT NUL可能导致索引失效

这取决于当前索引是否是一个覆盖索引，以及如果不是的话满足条件的记录数量占所有记录数量的比值，

若这是一个覆盖索引，无需回表查询，则会选择使用索引；

若不是，但满足条件的记录占比很小，回表查询的次数不多，则也会选择使用索引；

若不是，且占比很大，则说明需要进行回表查询的次数非常多，那还不如全表扫描；

f.在索引列上进行计算、函数等操作

因为索引中只对原字段进行了排序，经过计算之后的字段无法使用索引；

g.模糊查询like后缀匹配

h.查询条件中使用 or，且 or 的前后条件中有一个列没有索引，那么所有的索引都不会被使用到

总结：建立索引的原则为——遵循最左前缀原则、避免在查询条件中使用函数和表达式、避免隐式转换、尽量使用覆盖索引

（覆盖索引：返回的结果集中只包含索引叶子节点中的字段，无需回表查询）

②优化SQL语句

Ⅰ.尽量避免嵌套子查询（因为子查询产生的表是虚表，无法创建索引），而应使用连接查询，例如not in/not exists可以用left join on xxx is null来替代；

Ⅱ..分页写法（深翻页）——减少筛选数据条数，或是减少回表查询次数

对于常见的分页语句：

select * from t1 where order by grade limit 30000,10

当偏移量很小时，查询速度很快，但是随着 offset 变大时，查询速度会越来越慢，因为要查询第30001到30010条的这10条数据，需要遍历全部前30010条数据；

且因为order by的字段是grade，而最终select的是所有字段，因此会对这30010条数据都要到聚簇索引/主键索引中进行一次回表查询；

解决办法一般有两种：

如果已知上一页的最大/小id，则可以由上一页的最大/小id推算此页id的范围，可以加一个查询条件，直接从第30001或30010条开始查起：

select * from t1 where grade >= 300000 order by grade limit 10

如果不知道附近的页的id范围，那就从减少回表查询次数入手，先利用grade索引遍历30001条数据，找出按序第30001条数据的grade，

然后再用grade索引找出后面10条数据的grade，最后对这十条数据进行回表查询；

select * from t1 where grade > (select grade from t1 order by grade limit 300000, 1) limit 10

Ⅲ.最大最小写法——不要用max/min函数，用group by+limit 1

MySQL提供了max()和min()用于获取最大最小值，但是优化得不是很好，建议使用limit语句替代，

例如：

select max(id) from t1 where text1 = 'dd'

可以改成：

select id from t1 where text1 = 'dd' order by text1 DESC limit 1

Ⅳ.order by排序问题——加索引即可

MySQL进行排序是一个成本比较高的操作：全字段排序会在sort_buffer中建立临时表进行排序，基于rowid排序不仅需要建立临时表，还会涉及回表操作；

在需要排序时会在explain的Extra字段中出现Using filesort；

建议给order by字段加上索引：

如果order by后面的字段是单个索引，需要order by 条件要与where中条件一致，否则order by不会利用索引进行排序
如果order by 最后的字段是组合索引的一部分，需要把放在索引组合顺序的最后

Ⅴ.group by临时表问题——加索引即可

内存占用：group by语句由于可能会建立内部临时表，用于保存和统计中间结果。首先会使用内存临时表，但是内存临时表的大小是有限制的，由参数 tmp_table_size 控制，当超过此限制时会把内存临时表转成磁盘临时表。因此内部临时表的存在会影响内存和磁盘的空间，且需要构造的是一个带唯一索引的表，执行代价都是比较高的。因此需要尽量避免内部临时表的建立
额外排序：group by column默认会根据column排序，因此还会触发排序开销问题；

可以通过看explain 的Extra结果里有没有 Using temporary 和 Using filesort，来判断是否建立了临时表、是否进行了默认排序；

建议给group by字段加上索引：

通过索引建立，只需要顺序扫描到数据结束，就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序

Ⅵ.in & exists——EXISTS由外向内执行，IN由内向外执行，原则都是小表驱动大表，故主查询少用EXISTS，子查询少用IN

in执行流程：查询子查询的表且内外表有关联时，先执行内层表的子查询，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选，得到结果集。所以相对内表比较小的时候，in的速度较快

exists执行流程：指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的，匹配上就将结果放入结果集中

建议：

遵循小表驱动大表：*exists是以外层表为驱动表、IN是先执行内层表的***子查询。（其实思想就是先对表进行过滤缩小数据量，然后再连接表）

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in；

反之如果外层的主查询记录较少，子查询中的表大且又有索引时使用exists

Ⅶ.not in & not exists——使用not exists代替not in

not in使用的是全表扫描没有用到索引；而not exists在子查询依然能用到表上的索引

建议：

使用not exists代替not in

Ⅷ.查询记录是否存在——不要用select count(*)，改用limit 1

在很多时候开发人员判断某一条件对应的记录是否存在时会采用select count(*)，但是这样子会导致扫描所有符合条件的数据

建议：

改用limit 1，这样子数据库查询到一条符合条件的记录就会返回，不需要再继续查找还有多少条记录;

2.资源占用

锁资源等待：在读写很热的表上，通常会发生锁资源争夺，从而导致慢查询的情况
- 谨慎使用for update
- 增删改尽量使用到索引
- 降低并发，避免对同一条数据进行反复修改
网络波动：往客户端发送数据时发生网络波动导致的慢查询
硬件配置：CPU利用率高，磁盘IO经常满载，导致慢查询

在高并发、高流量下，数据库所在机器的负载load过高也会导致SQL整体执行时间过长，这时可能需要从机器和实例的分配，分布式部署，分库分表，读写分离等角度进行优化

——锁资源争夺、CPU利用率过高和磁盘IO经常满载都会导致慢查询，对于锁资源争夺，应当谨慎使用当前读，对于后者，可以通过提高硬件配置来缓解；

3.业务改造

是不是真的需要全部查出来，还是取其中的top N就能够满足需求了
查询条件过多的情况下，能否前端页面提示限制过多的查询条件的使用
针对实时导出的数据，涉及到实时查DB导出大量数据时，限制导出数据量 or 走T+1的离线导出是不是也是可以的
现在业务上需要做数据搜索，使用了 LIKE “%关键词%” 做全模糊查询，从而导致了慢SQL。是不是可以让业务方妥协下，最右模糊匹配，这样就可以利用上索引了

——比如尽量通过前端的限制来减少不必要的查询条件，尽量避免全模糊查询，改为前缀匹配以用上索引；

4.数据减少

SQL本身的性能已经到达极限了，但是耗时仍然很长，可能由于数据量或索引数据都比较大了。因此需要从数据量级减少的角度去处理

使用分库分表。由于单表的数据量过大，例如达到千万级别的数据了，需要使用分库分表技术拆分后减轻单库单表的单点压力
定时清理终态数据。针对已经状态为终态的业务单据或明显信息，可以使用idb历史数据清理的方式配置定时自动清理。如针对我们的仓储库存操作明细为完结状态的数据，我们只保留最近1天的数据在db中，其他直接删除，减少db查询压力
统计类查询可以单独维护汇总数据表。参考数据仓库中的数据分层设计，基于明细数据，抽出一张指标汇总表，或7天/15天等的视图数据进行预计算。此类汇总表数据量级相比明细表下降很多，从而避免直接根据大量明细查询聚合造成慢sql

——单表数据量过大例如达到千万级别，也会造成慢查询，可以通过分库分表和定期处理无效数据来减轻单库单表的压力；

5.源头替换

Mysql并不是任何的查询场景都是适合的，如需要支持全模糊搜索时，全模糊的like是无法走到索引的。同时结合数据本身的生命周期，对于热点数据，可以考虑存储到缓存解决。因此针对不适合mysql数据源的情况，我们需要替代新的存储介质

有like的全模糊的查询，比如基于文本内容去查订单信息，需要接搜索引擎解决
有热点数据的查询，考虑是否要接缓存解决
针对复杂条件的海量数据查询，可以考虑切换到OLAP(Online Analytical Processing)，可以考虑接Hybrid DB或ADB通道
有些场景Mysql不适用，需要用K-V的数据库，HBASE等列式存储的存储引擎

——有些查询场景不适合mysql，此时可以替换为其它存储介质，例如Mysql进行全模糊查询是无法使用索引的效率非常低，此时可以使用搜索引擎，再比如热点数据查询频率非常高，可以考虑使用缓存；

posted @ 2023-09-25 17:41 Avava_Ava 阅读(17) 评论(0) 编辑收藏举报

刷新页面返回顶部

AvavaAva

MySQL面试题

一、MySQL基础

1.什么是关系型数据库？

2.什么是 SQL？

二、MySQL 字段类型

1.整数类型的 UNSIGNED 属性有什么用？

2.CHAR 和 VARCHAR 的区别是什么？

3.VARCHAR(100)和 VARCHAR(10)的区别是什么？

4.DECIMAL 和 FLOAT/DOUBLE 的区别是什么

5.为什么不推荐使用 TEXT 和 BLOB？

6.DATETIME 和 TIMESTAMP 的区别是什么？

7.NULL 和 '' 的区别是什么？

8.Boolean 类型如何表示？

三、MySQL 基础架构

1.一条SQL语句在MySQL中的执行过程

①查询语句

②更新语句（包括增删改）

2.MySQL 一行记录是怎么存储的？

①MySQL 的数据存放在哪个文件？

②表空间文件的结构是怎么样的？

③InnoDB行格式有哪些？

④COMPACT 行格式长什么样？

④varchar(n) 中 n 最大取值为多少？

⑤行溢出后，MySQL 是怎么处理的？

四、MySQL存储引擎

1.MySQL 支持哪些存储引擎？默认使用哪个？

2.MySQL 存储引擎架构了解吗？

3.MyISAM 和 InnoDB 有什么区别？

4.MyISAM 和 InnoDB 如何选择？

五、MySQL索引

1.InnoDB中索引的底层数据结构是什么？为什么选用B+树？

2.聚簇索引和非聚簇索引

3.键值为NULL的记录是怎么在索引的B+树中存放的？

4.索引的优缺点？何时适合使用索引，何时不适合？

六、MySQL事务

1.数据库的ACID特性

2.并发事务可能会引发哪些问题？

①脏读

②不可重复读

③幻读

④丢弃修改

3.并发事务的控制方式

①锁

②MVCC

4.事务的隔离级别有哪些？

5.在可重复读隔离级别下，有哪些解决幻读的方案？

七、MySQL锁

1.表级锁和行级锁

2.行级锁具体有哪几类？（行级锁是InnoDB引擎独有）

①Record Lock记录锁

②Gap Lock间隙锁

③Next-Key Lock临键锁

④插入意向锁

3.何时会加行级锁，具体加什么锁？

①什么SQL语句会加行级锁？

②查询语句具体加哪一种行级锁？（delete、update本质上也是先进行查询，在对查询到的记录进行下一步操作）

③插入语句具体加哪一种行级锁？

4.共享锁和互斥锁

5.什么是意向锁？

6.当前读和快照读的区别？

①快照读

②当前读

7.发生死锁的情况

8.数据库层面如何避免死锁？

八、MySQL日志

1.undo log回滚日志

①什么是undo log？

②undo log的两大作用

2.redo log重做日志

①什么是redo log？

②redo log 和 undo log 区别在哪？

③redo log什么时候刷盘？

④redo log 文件写满了怎么办？

3.binlog归档日志

①什么是binlog？

②redo log 和 binlog 有什么区别？

③如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

④MySQL主从复制是如何实现的？

⑤binlog 什么时候刷盘？