有关MYSQL的22年最新部分面试题汇总

数据库的三范式是什么？

1. 第一范式：列不可再分 。

2. 第二范式：行可以唯一区分，主键约束 。

3. 第三范式：表的非主属性不能依赖与 其他表的非主属性 外键约束 。

且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式 建立第一第二范式上。

MYISAM、Innodb的区别有哪些？

1. InnoDB支持事务，MyISAM不支持。

2. InnoDB支持外键，而MyISAM不支持。

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。而MyISAM是非聚集索引，数据文件是分离的， 索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用 一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高。

什么是事务？

多条sql语句，要么全部成功，要么全部失败。

事务的特性（ACID）？

1. 原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功， 整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始 状态。

2. 一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。 如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。

3. 隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰 .

4. 持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

索引是什么？

索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书 前面的目录，能加快数据库的查询速度。

常见的索引有哪些？

1. 普通索引：主要以B+树和哈希索引为主，任务是加快对数据的访问速度，常用于查询和排序的条件，值可以为空并没有唯一性的限制。

2. 唯一性索引：与普通索引类似，不同的是唯一性索引，索引列的值必须是唯一的，但可以为空。

3. 主键索引：主键索引是一种特殊的唯一性索引，在定义主键是自动创建，是创建在主键上的索引，所有属性列唯一而且不能为空。

4. 全文索引：MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。

InnoDB中常见的索引？

1. B+树索引

2. 聚簇索引

3. 非聚簇索引

4. 联合索引

5. 全文索引

6. 哈希索引

索引有什么优缺点？

优点：

1. 提高查询数据的速度，降低数据库的IO成本。

2. 降低数据排序的成本，降低CPU的消耗，索引已经将数据排序好

缺点：

1. 占用多余存储空间

2. 降低数据更新时候的速度，更新数据也需要去更新索引的数据

什么是聚簇索引？

叶子节点存放的是该行完整的行记录，这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。

• 聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。

• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

什么是非聚簇索引？

叶子节点上存放的只有索引列和主键数据，查询的列不在索引列中需要回表进行查询。

什么是前缀索引？

语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。

索引原则？

1. 索引不是越多越好。

2. 不要对经常变动的数据加索引。

3. 小数据量的表不需要加索引。

4. 索引一般加在常用来查询的字段上。

为什么索引是使用B+树？

1. 在innodb存储引擎中一个b+树的节点是 一页(16k)，而b+树是把数据都存放在叶子节点中的，那么在固定大小的容量中 B+树的非叶子节点中就可以存放更多的索引列数据，也就意味着B+树的非叶子节点存储的数据的范围就会更大，那么树的层次就会更少，IO次数也就会更少；

2. b+树的叶子节点维护了一个双向链表，它更有利于范围查询

3. b+树中的叶子节点和非叶子节点的数据都是分开存储的，那么进行全表扫描的时候，就可以不用再扫描非叶子节点的数据了，并且这是一个顺序读取数据的过程（顺序读比随机读的速度要快很多很多），扫描的速度也会大大提高；

为什么InnoDB的表一定要建一个主键？

InnoDB的表默认使用B+树来维护整张表的数据的，如果没有建立，Mysql会自己去找表中所有的列，看有没有一列是所有数据不重复的。有则按照这一列来维护一颗B+树来做索引，没有则Mysql自己维护一个隐藏的自增的列来做索引列从而维护B+树。所以如果不建立主键的话会增加MySQL的工作量，性能会有所下降。

为什么推荐使用自增整形主键作为索引？

一般工作中使用的存储引擎都为innodb，而innodb底层的数据结构为B+树，B+树是存放的数据是有序的

1. 如果是自增的话，每次添加只需要添加到末尾即可，不是递增的话，可能会涉及到页分裂的情况

2. 使用字符串的话，每次插入还需要先去计算字符串的大小，而且整形的的大小比较比字符串要高

B+树和B树的区别?

1. B+树的非叶子节点不存放数据，而B树的所有节点上都有数据，但每个节点的大小是固定的16KB，所以B+树的非叶子节点可以存放更多的索引元素，从而降低整个树的高度，减少查询时的IO次数

2. B+树的叶子节点维护了一个双向指针，能提高范围查询的速度，因为不需要每一次都从根节点开始查询

什么叫做最左前缀原则？

当我们用联合索引查询的时候，必须遵循最左前缀原则才会走索引，因为联合索引在维护B+树的时候，首先会比较第一个字段，如果第一个字段不相同，就直接按照第一个字段排序，如果相同，则按照下一个字段来进行比较，以此来维护B+树，所以在查询的时候不能跳过第一个索引来进行查询，跳过第一个字段之后第二个字段不是有序的，所以会不会走索引，这个就叫最左前缀原则。

MySQL索引失效的几种情况？

1. like查询以%开头，因为会导致查询出来的结果无序。

2. 类型转换，列计算也会可能会让索引失效，因为结果可能是无序的，也可能是有序的。

3. 在一些查询的语句中，MySQL认为走全表扫描比索引更加快也会导致索引失效。

4. 如果条件中有or并且or连接的字段中有列没有索引，那么即使其中有条件带索引也不会使用索引。

5. 复合索引不满足最左原则就不能使用全部索引。

常见的索引优化手段有哪些？

1. 尽可能的使用联合索引而不是索引的组合；

2. 创建索引尽量让辅助索引进行索引覆盖 而不是回表；

3. 在可以使用主键id的表中，尽量使用自增主键id，这样可以避免页分裂；

4. 查询的时候尽量不要使用select * ，这样可以避免大量的回表；

5. 尽量少使用子查询，能使用外连接就使用外连接，这样可以避免产生笛卡尔集；

6. 能使用短索引就是用短索引，这样可以在非叶子节点存储更多的索引列降低树的层高，并且减少空间的开销；

drop、delete与truncate的区别

1. delete和truncate只删除表的数据不删除表的结构 速度,一般来说: drop> truncate >delete

2. delete 语句是dml,这个操作会放到rollback segement中,事务提交之后才生效; 如果有相应的trigger,执行 的时候将被触发.

3. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

什么是视图？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是 有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易， 相比多表查询。

什么是内联接、左外联接、右外联接？

• 内联接（Inner Join）：匹配2张表中相关联的记录。

• 左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记 录，右表中未匹配到的字段用NULL表示。

• 右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记 录，左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join 的左右位置关系。

MySQL的隔离级别有哪些，分别有什么问题？

• 读未提交：脏读（第一个事务读到了另外的事务没有提交的数据）

• 读已提交：不可重复读（在第一个事务中的两次读数据之间，由于第二个事务 的修改导致第一个事务两次读取的数据可能不太一样）

• 可重复读：幻读（它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会 发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。）

• 序列化：效率低

大表如何优化？

1. 限定数据的范围 务必禁止不带任何限制数据范围条件的查询语句。

2. 读/写分离

3. 垂直分区：指数据表列的拆分，把一张列比较多的表拆分为多张表

4. 垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外， 垂直分区可以简化表的结构，易于维护。

5. 垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层 进行Join来解决。此外，垂直分区会让事务变得更加复杂。

6. 保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达 到了分布式的目的。 水平拆分可以支撑非常大的数据量。

数据库分片的两种常见方案？

1. 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网 的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

2. 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现 在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

分库分表之后,id 主键如何处理？

1. UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯 一的名字的标示比如文件的名字。

2. 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式 生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。

3. 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系 统更加复杂，可用性降低，编码更加复杂，增加了系统成本。

4. Twitter的雪花算法

5. 美团的Leaf分布式ID生成系统

说说在 MySQL 中一条查询 SQL 是如何执行的？

1. 获取连接：获取MySQL 中的连接器。

2. 查询缓存：key为SQL语句，value 为查询结果，如果查到就直接返回。不建议使用次缓存， 在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。

3. 分析器：分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。

4. 优化器：索引选择或者是表的连接顺序。

5. 执行器：使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都 等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取 到这个表的最后一行，最后返回。

MySQL 中 varchar 与 char 的区别？

1. varchar 与 char 的区别，char 是一种固定长度的类型，varchar 则是一种可变长度的类型。

2. 对效率要求高用 char，对空间使用要求高用 varchar。

varchar(30) 中的 30 代表的涵义？

varchar(30) 中 30 的涵义最多存放 30 个字符。varchar(30) 和 (130) 存储 hello 所占空间一 样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度 （memory 引擎也一样）。

count(*)、count(主键id)、count(字段)和count(1)那种效率更高？

• count(主键id)：InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

• count(1)：InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

• 单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

• count(字段)：

• 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；

• 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

• 也就是前面的第一条原则，server层要什么字段，InnoDB就返回什么字段。

• count()：并不会把全部字段取出来，而是专门做了优化，不取值。count()肯定不是null，按行累加。

所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count()，所以我建议你，尽量使用count()。

什么是 MVCC？

MVCC即多版本并发控制，指的就是在使用读已提交，可重复读的隔离级别中的事务在执行普通的SEELCT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写，写-读操作并发执行，从而提高系统性能。

详细地址：www.cnblogs.com/nevererror/…

数据库并发场景有哪些问题？

• 读-读：不存在任何问题，也不需要并发控制

• 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读

• 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC 可以为数据库解决什么问题？

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数 据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

MVCC的实现原理？

通过InnoDB表中的影藏字段、undoLog，版本链，ReadView来实现的，而MVCC+锁则实现了事务的隔离性，一致性则由redolog和undolog保证

MySQL 数据库的锁？

1. 共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。

2. 排它锁：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用 户读取正在写入的资源。

3. 表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。

4. 行锁：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现， 否则会自动锁全表，那么就不是行锁了）。

说什么是锁升级？

1. MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。

2. 当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。

MySQL中InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update; for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id不是索引键那么InnoDB将 完成表锁

什么是间隙锁？

间隙锁是在索引记录之间的间隙上的锁，或者在查询的第一条索引记录之前或最后一条索引记录之后的间隙上的锁。间隙锁通过锁住一个范围，如果其他事务想要在这个范围内插入该范围不存在的数据时就会被阻塞。

详细地址：blog.youkuaiyun.com/fofcn/artic…

binlog和redolog 的区别？

1. BinLog 是 MySQL 本身就拥有的，不管使用何种存储引擎，BinLog 都存在，而Redo Log 是 InnoDB 存储引擎特有的，只有 InnoDB 存储引擎才会输出Redo Log。

2. Bin Log 是一种逻辑日志，记录的是对数据库的所有修改操作，而 Redo Log 是一种物理日志， 记录的是每个数据页的修改。

3. BinLog 是追加写入，写完一个日志文件再写下一个日志文件，不会覆盖使用，而Redo Log 是循环写入，日志空间的大小是固定的，会覆盖使用。

4. Bin Log 一般用于主从复制和数据恢复，并且不具备崩溃自动恢复的能力，而 Redo Log 是在服务器发生故障后重启 MySQL，用于恢复事务已提交但未写入数据表的数据。

为什么需要两阶段提交？

如果没有两阶段提交，那么 binlog 和 redolog 的提交，无非就是两种形式：

1. 先写 binlog 再写 redolog。

2. 假设我们要向表中插入一条记录 R，如果是先写 binlog 再写 redolog，那么假设 binlog 写完后崩溃了，此时 redolog 还没写。那么重启恢复的时候就会出问题：binlog 中已经有 R 的记录了，当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时候，就会同步到 R 这条记录；但是 redolog 中没有关于 R 的记录，所以崩溃恢复之后，插入 R 记录的这个事务是无效的，即数据库中没有该行记录，这就造成了数据不一致。

3. 先写 redolog 再写 binlog。

4. 相反，假设我们要向表中插入一条记录 R，如果是先写 redolog 再写 binlog，那么假设 redolog 写完后崩溃了，此时 binlog 还没写。那么重启恢复的时候也会出问题：redolog 中已经有 R 的记录了，所以崩溃恢复之后，插入 R 记录的这个事务是有效的，通过该记录将数据恢复到数据库中；但是 binlog 中还没有关于 R 的记录，所以当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时候，就不会同步到 R 这条记录，这就造成了数据不一致。

那么按照前面说的两阶段提交就能解决问题吗？

我们来看如下三种情况：

情况一： 一阶段提交之后崩溃了，即写入 redo log，处于 prepare 状态 的时候崩溃了，此时：

由于 binlog 还没写，redo log 处于 prepare 状态还没提交，所以崩溃恢复的时候，这个事务会回滚，此时 binlog 还没写，所以也不会传到备库。

情况二： 假设写完 binlog 之后崩溃了，此时：

redolog 中的日志是不完整的，处于 prepare 状态，还没有提交，那么恢复的时候，首先检查 binlog 中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。

情况三： 假设 redolog 处于 commit 状态的时候崩溃了，那么重启后的处理方案同情况二。

由此可见，两阶段提交能够确保数据的一致性。

mysql中in和exists区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop 循环，每次loop循环再对内表进行查询。

一直大家都认为exists比in语句的效率要高，这种说法其实是不 准确的。这个是要区分环境的。

1. 如果查询的两个表大小相当，那么用in和exists差别不大。

2. 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

3. not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而 not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

int(20)中20的涵义

是指显示字符的长度。20表示 大显示宽度为20，但仍占4字节存储，存储范围不变； 不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示。

MySQL的复制原理以及流程

原理：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这 些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

流程：

1. master在每个事务更新数据完成之前，将该操作记录串行地写入到 binlog文件中。

2. salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程 终的目的是将这些事件写入到中继日志中。

3. SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持 一致。

Mysql深度分页问题怎么解决

1. 从产品角度来说，不允许用户随机跳页，并且把上一页的主键ID带过来作为分页参数

2. SELECT * FROM cps_user_order_detail d WHERE d.id > #{maxId} AND d.order_time>'2020-8-5 00:00:00' ORDER BY d.order_time LIMIT 6; 复制代码

3. 子查询使用ID去IN查询

4. select * from table where id in (select id from table limit 5)； 复制代码

5. Elastic Search搜索引擎优化（基于倒排索引,ES深度分页问题使用游标scroll解决）

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