5.MySQL面试题

云笺AI
130 阅读22分钟

1. 数据库三范式是什么？

  1. 第一范式（1NF）：

每一列都是不可分割的原子数据项

  1. 第二范式（2NF）:

确保表中的每列都和主键相关。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

  1. 第三范式（3NF）:

确保每列都和主键列直接相关，而不是间接相关。

2. MySQL的存储引擎有哪些？

  1. InnoDB存储引擎

优点：提供了良好的事务处理、崩溃修复能力和并发控制。

缺点：读写效率较差，占用的数据空间相对较大。

  1. MyISAM存储引擎

优点：占用空间小，处理速度快。

缺点：是不支持事务的完整性和并发性。

  1. MEMORY存储引擎

优点：数据快速访问和处理。因为直接在内存操作数据

缺点：一旦发生异常，重启或关闭机器，数据都会丢失。

使用场景：

  1. InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势
  2. MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。
  3. MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。

3. innodb和myisam的区别

  1. innodb支持事务，而myisam不支持事务。
  2. innodb支持外键，而myisam不支持外键。
  3. innodb默认表锁，使用索引检索条件时是行锁，而myisam是表锁（每次更新增加删除都会锁住表）。
  4. innodb和myisam的索引都是基于b+树，但他们具体实现不一样，innodb的b+树的叶子节点是存放数据的，myisam的b+树的叶子节点是存放指针的。
  5. innodb是聚簇索引，必须要有主键，一定会基于主键查询，但是辅助索引就会查询两次（辅助索引叶子节点存的是数据的主键），myisam是非聚簇索引，索引和数据是分离的，索引里保存的是数据地址的指针，主键索引和辅助索引是分开的。
  6. innodb不存储表的行数，所以select count( * )的时候会全表查询，而myisam会存放表的行数，select count(*）的时候会查的很快。

4. 事务的四大特征是什么？

  1. 原子性(atomicity)：多个数据库操作合并成组成一个事务，事务类似一个原子操作不可再分。
  2. 一致性(consistency)：事务中多个操作要么同时成功，要么同时失败，执行结果保持一致。
  3. 隔离性(isolation)：在并发的场景下，存在多个事务对相同数据操作，事务之间相互隔离，各自执行是不会相互影响。
  4. 持久性(durability)：事务提交成功以后，对数据库的修改是永久的(已经持久化到磁盘)。

5. 事务的隔离级别？

  1. 读未提交(Read-Uncommited) : 顾名思义A在事务执行过程中能够读取到B事务还未提交时，B事务中的执行操作(脏读)。属于最低的事务隔离级别。
  2. 读已提交(Read-Commited)：顾名思义A事务在执行过程中读取到的是B事务已经提交的内容，因为只能读取到其他事务提交的内容，因此解决了脏读。但是如果在A事务修改数据提交成功前后分别读取数据会存在不一致的情况(不可重复读)，是比读未提交更高的隔离级别
  3. 可重复读(repeatable-read)：repeatable-read是MySQL默认隔离级别。可重复读的含义在于强调同一个事务A读取同一个条数据的结果，肯定是一致的，保证数据在A事务中读取多次结果一致。但是当B事务插入了新的数据，此时我们再对数据进行更新或者插入时，发现和第一次读取到的数据不一致，产生幻读。 是比读已提交更高的隔离级别
  4. 可串行化(Serializable)：最高的隔离级别，要求事务都串行执行，因此执行的效率最低，但是可靠性也是最好的，不会出现上述的脏读，不可重复读，以及幻读。

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

  • 脏读

在数据库技术中,脏数据在临时更新（ 脏读）中产生。事务A更新了某个数据项X，但是由于某种原因，事务A出现了问题，于是要把A回滚。但是在回滚之前，另一个事务B读取了数据项X的值(A更新后)，A回滚了事务，数据项恢复了原值。事务B读取的就是数据项X的就是一个“临时”的值，就是脏数据。

通俗的讲，当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

  • 不可重复读

是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。

这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

  • 幻读

指当事务不是独立执行时发生的一种现象，例如 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。

5.2 不可重复读和幻读得区别

  • 不可重复读重点在于数据是否被改变了。在一个事务中对同一条记录进行查询，第一次读取到的数据和第二次读取到的数据不一致，这便是可重复读。引起不可重复读的原因在于另一个事务进行了UPDATE或者是DELETE操作。

  • 幻读重点在于数据是否存在。原本不存在的数据却真实的存在了，这便是幻读。在同一个事务中，第一次读取到结果集和第二次读取到的结果集不同。（对比上面的例子，当B事务INSERT以后，A事务中再进行插入，此次插入相当于一次隐式查询）。引起幻读的原因在于另一个事务进行了INSERT操作。

6. 你详细了解过MVCC吗？它是怎么工作的？

  1. MVCC是什么?

MVCC是为了解决事务操作中多线程并发安全问题的无锁并发控制技术，它的全称是Multi-Version Concurrency Control，多版本并发控制，简称MVCC。

  1. 事务操作中多线程并发安全问题有哪些？

读读并发：这种情况不会产生并发问题，也不需要并发控制

读写并发：这种情况可能会造成事务隔离型问题，还可能会出现脏读、幻读和不可重复度的问题

写写并发：会出现数据更新丢失的问题

  1. 怎么解决的?

MVCC相当于是为每个修改保存⼀个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。它是通过数据库记录中的隐式字段Undo日志、Read View来实现的。

  1. MVCC解决了什么问题？

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，从而提高数据库的并发读写的处理能力。

能实现读一致性，从而解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决数据更新丢失的问题。

采用乐观锁或者悲观锁用来解决写和写的冲突，从而最大程度地去提高数据库的并发性能。

7. 你知道执行一条查询语句的流程吗？

  1. 客户端发送查询语句给服务器。
  2. 服务器首先进行用户名和密码的验证以及权限的校验。
  3. 然后会检查缓存中是否存在该查询，若存在，返回缓存中存在的结果。若是不存在就进行下一步。注意：Mysql 8就把缓存这块给砍掉了。
  4. 接着进行语法和词法的分析，对SQl的解析、语法检测和预处理，再由优化器生成对应的执行计划。
  5. Mysql的执行器根据优化器生成的执行计划执行，调用存储引擎的接口进行查询。服务器将查询的结果返回客户端。

8. mysql的 redo log / undo log / bin log 的区别？

mysql的 redo log/undo log/bin log 的区别

  1. redo log 是事务开始之后就产生，并且redo log 落到磁盘并不是随着事务的提交才写入的。而是在事务的执行过程中，就开始写入，而且redo log是为了保证事务的持久性，在mysql中，如果发生系统性故障，比如系统直接宕机，重启的时候，就会根据redo log 就行重做，从而达到事务持久性概念。

  2. undo log，他是事务开始之前，根据当前版本的数据生成一个undolog，undo log可以保存事务发生前数据的一个版本，能够用于回滚，同时也会提供更多版本并发控制下的读，也就是mvcc

  3. bin log ，bin log是事务提交时，一次性将事务中所有的sql按照一定的格式记录到bin log中，而bin log更多的是用于复制，在主从复制当中，从库要利用主库上的bin log进行传播，实现主从同步，还可以用于数据库的基于时间点的还原，很多同步数据得中间件都是基于binlog去实现的。

9. MySQL的索引的底层实现？为什么不用有序数组、hash、二叉树或者B树实现索引？

Mysql的索引是一种加快查询速度的数据结构，索引就好比书的目录一样能够快速的定位你要查找的位置。

Mysql的索引底层是使用B+树的数据结构进行实现，结构如下图所示： img

  1. hash是以key-value的形式进行存储，适合于等值查询的场景，查询的时间复杂度为O(1)，因为hash储存并不是有序的，所以对于范围查询就可能要遍历所有数据进行查询，而且不同值的计算还会出现hash冲突，所以hash并不适合于做Mysql的索引。

  2. 有序数组在等值查询和范围查询性能都是非常好的，那为什么又不用有序数组作为索引呢？因为对于数组而言作为索引更新的成本太高，新增数据要把后面的数据都往后移一位，所以也不采用有序数组作为索引的底层实现。

  3. 最后二叉树，主要是因为二叉树只有二叉，一个节点存储的数据量非常有限，需要频繁的随机IO读写磁盘，若是数据量大的情况下二叉的树高太高，严重影响性能，所以也不采用二叉树进行实现。

  4. B树的所有节点既存放键key也存放数据data，而B+树只有叶子节点存放key和data，非叶子节点只存放key。B树的叶子节点都是独立的，而B+树的叶子节点都有一条引用链指向它相近的节点。B树的检索过程是相当于对范围内的每个节点的关键词做二分查找法，可能还没到叶子节点检索就结束了，而B+树的检索效率就很稳定，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

10. 说说 InnoDB的聚簇索引（主键索引）、非聚簇索引和辅助索引

  1. 聚簇索引（主键索引）

按照每张表的主键构建一棵B+树，叶节点中存放表的整行数据，叶节点成为数据页，每个数据页之间通过一个双向链表进行连接。数据页存放每行的所有记录，非数据页（非叶节点）存放键值和指向数据页的偏移量。一张表只能有一个聚集索引（因为只有一个主键PRIMARY KEY）

  1. 辅助索引

按照每张表创建的索引列（非主键列的其他列，被搜索的列）创建一棵B+树，叶子节点并不包含行记录的所有数据，只包含键值和书签，书签用来告诉InnoDB存储引擎在哪里可以找到行数据，一张表可以有可以有多个辅助索引。要先在辅助索引中找到键值，再根据键值去聚集索引中找到整行数据。 例：执行一次查询就是一次IO，比如 辅助索引树高度为3，聚集索引树高度为2，则通过辅助索引查询数据时就要进行3+2次逻辑IO最终得到一个数据页

  1. 非聚簇索引

数据储存于索引分开，叶节点指向了对应的数据行。

那么InnoDB有了聚簇索引，为什么还要有二级索引呢？

聚簇索引的叶子节点存储了一行完整的数据，而二级索引只存储了主键值，相比于聚簇索引，占用的空间要少。当我们需要为表建立多个索引时，如果都是聚簇索引，那将占用大量内存空间，所以InnoDB中主键所建立的是聚簇索引，而唯一索引、普通索引、前缀索引等都是二级索引。

11. 怎么查看索引是否生效？什么情况下索引会失效呢？

  1. 查看索引是否起作用可以使用explain关键字，查询后的语句中的key字段，若是使用了索引，该字段会展示索引的名字。

  2. 索引失效七字口诀：

模 型 数 空 运 最 快

  • 模：模糊查询的意思。like的模糊查询以%开头，索引失效。比如：
SELECT * FROM `user` WHERE `name` LIKE '%zhangsan';
  • 型：代表数据类型。类型错误，如字段类型为varchar，where条件用number，索引也会失效。比如：

height为varchar类型导致索引失效。

SELECT * FROM `user` WHERE height= 180;
  • 数：是函数的意思。对索引的字段使用内部函数，索引也会失效。这种情况下应该建立基于函数的索引。比如：

create_time字段设置索引，那就无法使用函数，否则索引失效。

SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';
  • 空：是Null的意思。索引不存储空值，如果不限制索引列是not null，数据库会认为索引列有可能存在空值，所以不会按照索引进行计算。比如：
SELECT * FROM `user` WHERE address IS NULL不走索引。

SELECT * FROM `user` WHERE address IS NOT NULL;走索引。

建议大家这设计字段的时候，如果没有必要的要求必须为NULL，那么最好给个默认值空字符串，这可以解决很多后续的麻烦（切记）。

  • 运：是运算的意思。对索引列进行（+，-，*，/，!, ！=, <>）等运算，会导致索引失效。比如：
SELECT * FROM `user` WHERE age - 1 = 20;

  • 最：是最左原则。在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。

  • 快：全表扫描更快的意思。如果数据库预计使用全表扫描要比使用索引快，则不使用索引。

12. 索引的分类有哪些？

  1. 单列索引

一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

  1. 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。
  2. 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
  3. 主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

  1. 组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀原则。

  1. 全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。

13. 什么是回表？回表是怎么产生的呢？

InnoDB引擎的主键索引存储的是行数据，二级索引的叶子结点存储的是索引数据以及对应的主键

比如有一个表（id（pk），name（key），age），id为主键，name为二级索引，这时，我们使用

select * 
或者
select name,age

时，由于age没有建立索引，所以根据二级索引name找到数据的id，再根据id查找到完整数据。

14. 怎么解决回表的问题？

可以通过索引覆盖来解决回表问题

索引覆盖是一种避免回表查询的优化策略。具体的做法是将要查询的数据作为索引建立普通索引（可以是单列索引，也可以是一个索引语句定义所有要查询的列，即联合索引），由于辅助索引的叶子节点保存的有当前索引列的数据，所以就可以直接返回索引中的数据，不需要再通过集聚索引去定位行记录，避免了回表的情况发生。

15. 什么是最左前缀原则？

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。 例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

16.什么是索引下推？

Mysql5.6之前是没有索引下推这个功能，后面为了提高性能，避免不必要的回表5.6之后就有了索引下推优化的功能。索引下推会对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

17. 主键使用自增ID还是UUID?能说说原因吗？

自增ID和UUID作为主键的考虑主要有两方面，一个是性能另一个就是存储的空间大小，一般没有特定的业务要求都不推荐使用UUID作为主键。

因为使用UUID作为主键插入并不能保证插入是有序的，有可能会涉及数据的挪动，也有可能触发数据页的分裂，因为一个数据页的大小就是16KB，这样插入数据的成本就会比较高。

而自增ID作为主键的话插入数据都是追加操作，不会有数据的移动以及数据页的分裂，性能会比较好。

另一方面就是存储空间，自增主键一般整形只要4个字节，长整形才占8字节的大小空间，而使用UUID作为主键存储空间需要16字节的大小，会占用更多的磁盘，在二级索引中也会存出一份主键索引，这样多占用消耗的空间就是两倍，性能低，所以不推荐使用。

18. 能说一说Mysql的主从复制吗？

  1. 读写分离

实现MySQL读写分离的前提是我们已经将MySQL主从复制配置完毕，读写分离实现方式：（1）配置多数据源。（2）使用mysql的proxy中间件代理工具。

  1. 主从复制的原理

MySQL的主从复制和读写分离两者有着紧密的联系，首先要部署主从复制，只有主从复制完成了才能在此基础上进行数据的读写分离。 img

  1. 读写分离的原理

读写分离就是只在主服务器上写，只在从服务器上读。基本原理是让主数据库处理事务性查询，而从服务器处理select查询。数据库复制被用来把事务性查询导致的变更同步到从数据库中。

19. 你平时是怎么进行SQL优化的？

  1. 代码优化
  2. sql语句的优化
  3. 合理使用索引
  4. 避免索引失效
  5. 分库分表
  6. 选择合适的存储引擎

20.能说一说分库分表吗？怎么分？

首先为什么要分表？

  1. 如果一个表的每条记录的内容很大，那么就需要更多的IO操作，如果字段值比较大，而使用频率相对比较低，可以将大字段移到另一张表中，当查询不查大字段的时候，这样就减少了I/O操作

  2. 如果表的数据量非常非常大，那么查询就变的比较慢；也就是表的数据量影响查询的性能。

  3. 表中的数据本来就有独立性，例如分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，而另外一些数据不常用。

  4. 分表技术有(水平分割和垂直分割)

垂直分割：

垂直分割是指数据表列的拆分，把一张列比较多的表拆分为多张表。垂直分割一般用于拆分大字段和访问频率低的字段，分离冷热数据。

垂直分割比较常见：例如博客系统中的文章表，比如文章tbl_articles (id, titile, summary, content, user_id, create_time)，因为文章中的内容content会比较长，放在tbl_articles中会严重影响表的查询速度，所以将内容放到tbl_articles_detail(article_id, content)，像文章列表只需要查询tbl_articles中的字段即可。

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

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

水平分割：

水平拆分是指数据表行数据的拆分，表的行数超过500万行或者单表容量超过10GB时，查询就会变慢，这时可以把一张的表的数据拆成多张表来存放。水平分表尽可能使每张表的数据量相当，比较均匀。

水平拆分会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点。

因为只要索引关键字不大，则在索引用于查询时，表中增加2-3倍数据量，查询时也就增加读一个索引层的磁盘次数，所以水平拆分要考虑数据量的增长速度，根据实际情况决定是否需要对表进行水平拆分。

水平分割最重要的是找到分割的标准，不同的表应根据业务找出不同的标准

  1. 用户表可以根据用户的手机号段进行分割如user183、user150、user153、user189等，每个号段就是一张表。
  2. 用户表也可以根据用户的id进行分割，加入分3张表user0,user1,user2，如果用户的id%3=0就查询user0表，如果用户的id%3=1就查询user1表。
  3. 对于订单表可以按照订单的时间进行分表。

分库分表技术：

现在市面上主要的分库分表技术有mycat和sharding-jdbc

avatar
文章
阅读
粉丝