MySQL面试题(2022)

MySQL（数据库）

1、主键、外键有什么区别？

1.主键是唯一标识一条记录，不能有重复的，而外键是另一表的主键，可以重复。

2.主键不允许为空，而外键可以。

3.主键是用来保证数据完整性，而外键是用来和其他表建立联系的。

4.主键只有一个，外键可以有多个。

2、怎么理解三范式和反范式？

第一范式：强调原子性，列不可再分

第二范式：强调唯一性，要有主键，其他列必须完全依赖于主键，不能部分依赖

第三范式：不可传递依赖，任何非主键列只能依赖于主键，不能依赖于其他列

基于三范式不适合对高性能的要求，因而出现了反范式。

反范式：是一种对范式化设计的数据库的性能优化策略，通过在表中增加冗余或重复的数据来提高数据库的读取性能。
简括就是增加了一点冗余以空间换取时间。
 

3、范式和反范式的优缺点？

1、范式化 （时间换空间）

优点：

范式化的表减少了数据冗余，数据表更新操作快、占用存储空间少。

缺点：

 查询时需要对多个表进行关联，查询性能降低。 更难进行索引优化

2 、反范式化（空间换时间）

反范式的过程就是通过冗余数据来提高查询性能，但冗余数据会牺牲数据一致性

优点：

可以减少表关联可以更好进行索引优化

缺点：

存在大量冗余数据数据维护成本更高（一个地方修改了，所有冗余字段也要对应修改）

4、什么是事务？

事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部不成功。

举例：
  例如：A——B 转帐，对应于如下两条 sql 语句

  update account set money=money-100 where name=‘a’;

  update account set money=money+100 where name=‘b’;

  要保证这两句代码在任何情况下，要么一起成功要么一起失败

5、事务有哪几个特性？

原子性：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log

持久性：保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log

隔离性：保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）

一致性：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障

6、什么是脏读、幻读、不可重复读？

脏读

1、在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据。
2、由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据。
这种读取到另一个事务未提交的数据的现象就是脏读(Dirty Read)。

幻读

事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中事务A新增了数据，
导致事务B后一次读取到前一次查询没有看到的行。

幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新。

不可重复读

事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。

这种在同一个事务中，前后两次读取的数据不一致的现象就是不可重复读(Nonrepeatable Read)。

7、MySQL 有哪些事务隔离级别？

8、MySQL 默认的事务隔离级别是？

数据库默认隔离级别:  mysql ---可重复读,oracle,sql server ---读已提交

9、什么是索引？

对数据库中一列或多列的值进行排序的一种结构

10、索引有什么用？

使用索引可以快速访问数据库表中特定信息（加速检索表中的数据）

11、索引为什么能提高查询效率？

数据存储在磁盘（ SSD 跟 CPU 性能也不在一个量级），而磁盘处理数据很慢；
提高磁盘性能主要通过减少 I/O 次数，以及单次 I/O 有效数据量；
索引通过多阶（一个节点保存多个数据，指向多个子节点）使树的结构更矮胖，从而减少 I/O 次数；
索引通过 B+ 树，把业务数据与索引数据分离，来提高单次 I/O 有效数据量，从而减少 I/O 次数；
索引通过树数据的有序和「二分查找」（多阶树可以假设为多分查找），大大缩小查询范围；
索引针对的是单个字段或部分字段，数据量本身比一条记录的数据量要少的多，这样即使通过扫描的方式查询索引也比扫描数据库表本身快的多；

12、索引的设计有哪些原则?

选择唯一性索引

为频繁查询的字段建立索引

尽量为ORDER BY 和 GROUP BY 后面的字段建立索引

限制索引的数目

尽量使用数据量少的索引
	如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比
	对 CHAR(10) 类型的字段需要的时间要多。

数据量小的表最好不要使用索引
	由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

尽量使用前缀来索引
    如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，
    进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

删除不再使用或者很少使用的索引
    表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。
    应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

13、什么情况下应不建或少建索引？

表记录太少
经常插入、删除、修改的表
数据重复且分布平均的表字段
经常和主字段一块查询但主字段索引值比较多的表字段

14、MySQL 索引的种类有哪些？

    普通索引
    唯一索引
    主键索引
    组合索引
    全文索引

15、MySQL 索引最左匹配原则怎么理解？

当⼀个SQL想要利⽤索引时，就⼀定要提供该索引所对应的字段中最左边的字段，也就是排在最前⾯的字段，
⽐如针对a,b,c三个字段建⽴了⼀个联合索引，那么在写⼀个sql时就⼀定要提供a字段的条件，
这样才能⽤到联合索引，这是由于在建⽴a,b,c三个字段的联合索引时，底层的B+树是按照a,b,c三个字段从左往右去⽐较⼤⼩进⾏排序的，
所以如果想要利⽤B+树进⾏快速查找也得符合这个规则

16、MySQL 数据库引擎怎么选择？

 1) MyISAM
在 MySQL 5.1 版本及之前的版本，MyISAM 是默认的存储引擎。
 
MyISAM 存储引擎不支持事务和外键，所以访问速度比较快。如果应用主要以读取和写入为主，
只有少量的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择 MyISAM 存储引擎是非常适合的。
 
MyISAM 是在 Web 数据仓储和其他应用环境下最常使用的存储引擎之一。
2) InnoDB
MySQL 5.5 版本之后默认的事务型引擎修改为 InnoDB。
 
InnoDB 存储引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，
所以比 MyISAM 存储引擎占用更多的磁盘空间。

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，
数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。
 
InnoDB 存储引擎除了可以有效地降低由于删除和更新导致的锁定，
还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，
InnoDB 都是合适的选择。 

17、MySQL 默认数据库引擎是什么？

MySQL默认的是MyISAM数据引擎

18、MySQL 引擎 MyISAM 和 InnoDB 的区别？

在MySQL 5.5.5版本之前默认存储引擎是 MyISAM，之后默认存储引擎是 InnoDB。
InnoDB支持事务，MyISAM不支持。
InnoDB支持外键，MyISAM不支持。
InnoDB在MySQL 5.6之前不支持全文索引，MyISAM支持。
清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
InnoDB支持行锁和表锁，而MyISAM只支持表锁。
InnoDB一张表对应两个文件，分别是.frm、.ibd；MyISAM一张表对应三个文件，分别是.frm、.MYD、.MYI。

19、char 和 varchar 的区别？

1、最大长度：

char最大长度是255字符，varchar最大长度是65535个字节。

2、定长：

char是定长的，不足的部分用隐藏空格填充，varchar是不定长的。

3、空间使用：

char会浪费空间，varchar会更加节省空间。

4、查找效率：

char查找效率会很高，varchar查找效率会更低。

5、尾部空格：

char插入时可省略，varchar插入时不会省略，查找时省略。

20、MySQL 的 drop、delete、truncate区别？

相同点
    都可以删除整张表中的数据
不同点
    删除的范围：drop（删除表中所有数据及表结构）>truncate（删除表中所有数据）>=delete（删除表中所有数据或部分数据）
    查询条件：delete可以使用查询条件进行表中数据删除，drop和truncate不可以
    命令类型：delete属于DML，drop和truncate属于DDL
    数据能否恢复：delete删除的数据可以恢复，但是drop和truncate删除的数据不能恢复
    执行效率：drop>truncate>delete

21、MySQL 怎么实现分页查询？

1. LIMIT用法

LIMIT出现在查询语句的最后，用两个参数来限制取出的数据。其中第一个参数代表偏移量：offset（可选参数），第二个参数代表取出的数据条数：rows。

/* 查询第1-10条数据 */
SELECT * FROM Student LIMIT 0,10;
/* 查询第11-20条数据 */
SELECT * FROM Student LIMIT 10 OFFSET 10;

22、MySQL 的高可用方案有哪些？

23、如何分析一条 SQL 语句的执行计划和性能？

 mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

可以看到，explain 会返回约 10 个字段，不同版本返回的字段有些许差异，每个字段都代表着具体的意义，这篇文章我不打算把每个字段都详细的介绍一遍，东西比较多，怕你也不容易记住，不如先把几个重要的字段好好理解了。

其中 type、key、rows、Extra 这几个字段我认为是比较重要的，我们接下来通过具体的实例来帮你更好的理解这几个字段的含义。

首先有必要简单介绍下这几个字段的字面意思。

type 表示 mysql 访问数据的方式，常见的有全表扫描（all）、遍历索引（index）、区间查询（range）、常量或等值查询（ref、eq_ref）、主键等值查询（const）、当表中只有一条记录时（system）。下面是效率从最好到最差的一个排序。

system > const > eq_ref > ref > range > index > all

key 表示查询过程实际会用到的索引名称。

rows 表示查询过程中可能需要扫描的行数，这个数据不一定准确，是mysql 抽样统计的一个数据。

Extra 表示一些额外的信息，通常会显示是否使用了索引，是否需要排序，是否会用到临时表等。

24、MySQL 查询优化有哪些方法？

1.检查是否⾛了索引，如果没有则优化SQL利⽤索引
2.检查所利⽤的索引，是否是最优索引
3.检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
4.检查表中数据是否过多，是否应该进⾏分库分表了
5.检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

25、MySQL 为什么不建议默认 null 值？

使用了NULL值的列将会使索引失效

26、MySQL 为什么尽量选择最小数据类型？

在MySQL数据类型设置方面，尽量采用更小的数据类型，因为它们占用的存储空间更小，通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL。

27、怎么理解数据库中的乐观锁和悲观锁？

悲观锁

顾名思义，就是对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，
别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。

悲观锁的实现，通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select … for update来实现悲观锁。

乐观锁

顾名思义，就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。

乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。
给表加一个版本号或时间戳的字段，读取数据时，将版本号一同读出，数据更新时，将版本号加1。

28、MySQL 中的 MVCC 是指什么？

MVCC是多版本并发控制。MVCC在Mysql InnoDB中实现主要是为了提高数据库并发性能，
用户更好的方式去处理文件读写冲突，做到即使有读写冲突时，也能做到不加锁，进行非阻塞并发读。

29、MySQL InnoDB 的 MVCC 实现机制？

30、MySQL 中的 MVCC 支持哪些事务隔离级别？

MVCC只在READ COMMITED和REPEATABLE READ 两个隔离级别下工作

31、MySQL 支持哪三种级别的锁？

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

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

32、MySQL InnoDB 支持什么锁？

InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁。

33、MySQL 中的表锁有哪些？

34、MySQL 中的行锁有哪些？

35、MySQL 中的意向锁有什么用？

意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

36、MySQL 中的意向锁的分类？

    意向共享锁
    意向排它锁

37、MySQL 中的意向锁是表锁还是行锁？

表锁

38、MySQL 中的自增锁有什么用？

自增锁很明显是用于自增类型的操作，自增锁是表级锁，自增锁的作用是为了保证数据库的主键是自动递增的。
其实这个锁主要就是用于拥有自增主键的数据表的插入操作，两个事务先后执行插入操作，第二个事务的插入操作则会被阻塞，
因为需要保证主键是递增操作。

39、MySQL 行锁是锁的是什么？

    行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁
    除了直接在主键索引加锁，我们还可以通过辅助索引找到相应主键索引后再加锁

40、MySQL 行锁实现的几种算法？

    Record Lock：单个记录上的锁
    Gap Lock： 间隙锁，锁定一个范围，但不包含记录本身
    Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身

41、MySQL 什么情况会发生死锁？

两个(或以上)的Session加锁的顺序不一致。

42、MySQL 死锁怎么排查？

43、MySQL 如何解决死锁？

等待，直到超时（innodb_lock_wait_timeout=50s），自动回滚事务。

●　发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）。

由于性能原因，一般都是使用死锁检测来进行处理死锁。

死锁检测

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

回滚

检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

44、MySQL 如何避免死锁？

1、以固定的顺序访问表和行；
2、大事务更倾向于死锁，如果业务允许，将大事务拆小；
3、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率；
4、降低隔离级别；
5、为表添加合理的索引。

45、MySQL 和 Oracle的区别？

1、Oracle是大型数据库，而MySQL是中小型数据库。但是MySQL是开源的，但是Oracle是收费的，而且比较贵。

2、Oracle的内存占有量非常大，而mysql非常小

3、MySQL支持主键自增长，指定主键为auto increment，插入时会自动增长。Oracle主键一般使用序列。

4、MySQL字符串可以使用双引号包起来，而Oracle只可以单引号

5、MySQL分页用limit关键字，而Oracle使用rownum字段表明位置，而且只能使用小于，不能使用大于。

6、Oracle在处理长字符串的时候,长度是小于等于4000个字节,如果要插入更长的字符串,考虑用CLOB类型,插入修改记录前要做进行修改和 长度的判断,如果为空,如果长度超出返回操作处理.（CLOB类型是内置类型，它一般都作为某一行中的一列,有些数据库也有别名）

7、MySQL中0、1判断真假，Oracle中true false

8、MySQL中命令默认commit,但是Oracle需要手动提交

9、MySQL在windows环境下大小写不敏感 在unix,linux环境下区分大小写，Oracle不区分

46、MySQL 日志 undo 和 redo 的区别？

redo重做是为了恢复。
比如系统崩溃了，机器宕机了，磁盘坏了。这时候重启机器之后需要将恢复数据，就需要用redo log
undo撤销是为了撤销操作
最简单的就是数据库事务，需要rollback的时候就要用到undo log

redo是物理层面上的，记录的是物理磁盘数据页的变化，而不是逻辑上的某条记录的内容变化。
undo是逻辑层面上的，比如一条数据库表记录，我们记录他的某个版本的变化。(MVCC)

47、什么是表分区？

48、表分区有什么好处？

49、表分区与分表的区别？

50、MySQL 支持的分区类型有哪些？

1、RANGE分区将多行分配给分区。

2、LIST区域根据列值匹配离散值集中的某个值进行选择。

3、HASH分区包括MySQL中有效、产生非负整数值的表现。

根据用户定义的表现式回归值进行选择的分区，该表现式的使用将插入表中的这些行列值进行计算。

4、KEY区域只支持计算一列或多列。

MySQL服务器提供自己的哈希函数。必须有一列或多列包含整数值。

51、MySQL 分区表有哪些限制因素？

52、MySQL 为什么要分库分表？

单表操作数据量有最优值，mysql为1000万左右；

可以减轻数据库的压力，不用所有线程都查同一个数据库；

数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，
库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大；
另外，由于无法进行分布式式部署，而一台服务器的资源(CPU、磁盘、内存、IO等)是有限的，
最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。

53、MySQL 分库分表怎么做？

1、水平分库

2、水平分表

3、垂直分库

4、垂直分表

54、MySQL 分库分表工具有哪些？

    sharding-sphere：jar，前身是sharding-jdbc；
    TDDL：jar，Taobao Distribute Data Layer；
    Mycat：中间件。

55、MySQL 分库分表会产生哪些问题？

1 事务一致性问题

由于分库分表后把数据分布在不同库甚至不同的服务器，数据库原有的单库的数据库事务就会失效，
不同的数据库实例完成同一个事物可能会失败从而带来分布式事务问题。

2 跨节点关联查询

在垂直分库后比如商品信息和店铺信息不在一个数据库甚至不在一个服务器这时就无法进行联表的查询。
可以将原来的关联查询分为两次查询，第一次查询的结果集中找出关联数据id，
然后根据id发起第二次请求得到关联数据，最后将获得到的数据进行拼装。

3 跨节点分页、排序函数
跨节点多库进行查询时，limit分页、order by排序问题就变得比较的复杂。
需要先在不同的分片节点中将数据排序并返回，然后将不同分片返回的结果集数据进行汇总和再次排序。

4.主键避重

在分库分表的环境中，由于表中数据同时存在不同数据库中，主键值平时使用的主键自增长将会失去效果，
某个数据库生成的ID无法保证全局唯一的主键。因此需要单独的设计全局的主键，以避免跨库主键重复问题。

5 公共表问题

公共表就是业务中经常用的一些存储公共属性的表，也就是我们常说的参数表、数据字典表，
当我们进行分库分表后根据不同的业务拆分了不同的库表，但是公共属性因为数据量和变化都不是很大所以没必要进行再次的拆分。
我们可以每个数据都保存一份这样的公共表的数据，对所有的公共表的更新等操作进行统一的同时的发送到所有的分库分片中进行修改。
这样公共的表维护起来都是比较麻烦的。由于分库分表之后，数据被分散在了不同的数据库或者不同的服务器中，
因此对数据的操作也无法通过常规的方式来完成，并且他还带来了一系列的问题。为了解决这些问题，
所以我们会引入一些市面上常用的中间件来解决这些问题，比如shareing-jdbc。

56、MySQL 批量插入，如何不插入重复数据？

1、insert ignore into

当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。
所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。例如：

INSERT IGNORE INTO user (name) VALUES ('telami')

这种方法很简便，但是有一种可能，就是插入不是因为重复数据报错，而是因为其他原因报错的，也同样被忽略了～

2、on duplicate key update

当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。

例如，为了实现name重复的数据插入不报错，可使用一下语句：

INSERT INTO user (name) VALUES (‘telami’) ON duplicate KEY UPDATE id = id

这种方法有个前提条件，就是，需要插入的约束，需要是主键或者唯一约束（在你的业务中那个要作为唯一的判断就将那个字段设置为唯一约束也就是unique key）。

3、insert … select … where not exist

根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：

INSERT INTO user (name) SELECT ‘telami’ FROM dual WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1)

这种方法其实就是使用了mysql的一个临时表的方式，但是里面使用到了子查询，效率也会有一点点影响，如果能使用上面的就不使用这个。

4、replace into

如果存在primary or unique相同的记录，则先删除掉。再插入新记录。

REPLACE INTO user SELECT 1, ‘telami’ FROM books

这种方法就是不管原来有没有相同的记录，都会先删除掉然后再插入。

57. 自增id与UUID的区别？

自增id

优点

• 数字类型，占用空间小，写入速度相对快
• 数据库自动增量排序，对检索有利，读速度快
• 系统编码过程中，可以不指定id，数据库自增

缺点

因自动增长，当手动插入的时候会出现麻烦，主键冲突的情况会有发生
分表之后控制不好会出现主键重复现象
新老数据合并，要是新数据主键也是数字类型，想新老数据区分会出现一些冲突
uuid
优点

全局唯一性，不用担心重复的现象，对数据的拆分、合并比较友好
缺点

字符串类型，占用空间比较大，读写速度慢，而且索引会随着数据量的增加越来越难用

作者：云边一片海
链接：https://www.jianshu.com/p/262409233443
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。