面试题之MySQL，后端程序员必备技能，到底有多少知识点？

萤小宇Tyrone
187 阅读19分钟

0、前言

MySQL面试内容.png

MySQL是后端程序员的必备技能，面试中MySQL也是必问的一块知识点，MySQL很复杂，今天主要是在面试的角度，对MySQL一些重点知识点做一个简单的总结，我总结的这些问题，基本包含了MySQL面试的重点，希望给在准备面试的同学们一些帮助哦~

关系型数据库

关系型数据库是一种基于关系理论的数据库模型，它将数据组织成表格的形式，每张表格由一组列组成，每列代表一个属性，每行代表一个实体。

总结一下，简单来说，关系型数据库就是数据以表格的形式存储的数据库。面试中可能会让我们举例：

主要代表有这几种数据库：SQL Server，Oracle，Mysql，PostgreSQL。

非关系型和关系型的区别

非关系型数据库指NoSQL，主要代表：MongoDB，Redis。

非关系型数据库将数据组织成文档、键值对或图形的形式，每个文档、键值对或图形都可以包含多个属性和值。非关系型数据库使用NoSQL语句进行查询，可以方便地进行复杂的查询和数据分析，但是查询语句比SQL语句更加复杂。NoSQL适合存储较为简单的数据。有一些不能够持久化数据，所以需要和关系型数据库结合。

总之：非关系型数据库和关系型数据库的主要区别在于数据的组织方式和查询方式，还有应用场景。

1、MySQL的数据类型

MySQL中支持三种数据类型：数值型、字符串型、日期和时间型。

数值型：常用整型、浮点型。根据不同大小的数值：TINYINT，INT 和 BIGINT。浮点型：FLOAT 和 DOUBLE。

字符串型：最常用：CHAR，VARCHAR。

日期和时间型：YEAR、TIME、DATE、DATETIME 。常用作修改时间。

CHAR 和 VARCHAR 的区别

MySQL中的CHAR和VARCHAR是两种常用的字符串数据类型，它们的主要区别在于存储方式和长度限制。

CHAR是固定长度，VARCHAR长度可变

  • CHAR是一种固定长度的字符串类型，每个字符占用一个固定的存储空间，长度从1到255个字符。如果存储的字符串长度小于指定的长度，则会在末尾填充空格。

  • VARCHAR是一种可变长度的字符串类型，每个字符占用的存储空间不固定，长度从1到65535个字符。如果存储的字符串长度小于指定的长度，则不会在末尾填充空格。

2、MySQL的存储引擎

MySQL支持哪些存储引擎

MySQL支持多种存储引擎，不同的存储引擎具有不同的特点和适用场景。以下是MySQL支持的一些存储引擎：

  • InnoDB：MySQL默认的存储引擎，支持事务、行级锁、外键等特性，适用于需要高并发和事务处理的场景。
  • MyISAM：不支持事务和行级锁定，适用于读取频繁、写入不频繁的应用场景。
  • Memory：存储在内存中的存储引擎，适用于需要快速读取的场景。
  • CSV：基于CSV文件的存储引擎，适用于需要处理大量CSV数据的场景。

MyISAM 和 InnoDB有哪些区别

  • MyISAM不提供事务支持，InnoDB支持事务。
  • InnoDB支持行级锁，适用于需要高并发和事务处理的场景；MyISAM不支持行级锁，只支持表级锁，适用于读操作比较频繁的场景。
  • InnoDB支持外键，MyISAM不支持外键。
  • InnoDB索引采用B+树索引结构，B+树的叶子节点包含所有的数据页，其数据文件本身就是索引文件。MyISAM的索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

MyISAM 和 InnoDB 该如何选择

如果需要进行高并发和事务处理，可以选择InnoDB存储引擎；如果需要进行快速读取和处理大量数据，可以选择MyISAM存储引擎。

在我们的工作中，一般都是选用MySQL默认的InnoDB引擎。MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)。

3、MySQL事务

事务是数据库操作的基本单位，它是一组原子性的操作，这些操作要么全部执行，要么全部不执行。

事务的四个特性

事务具有四个特性，通常被称为ACID特性，分别是：

  1. 原子性（Atomicity）：事务是一个原子操作，要么全部执行成功，要么全部回滚。如果事务执行过程中发生了错误，所有已经执行的操作都将被撤销，数据库状态回滚到事务开始之前的状态。
  2. 一致性（Consistency）：事务执行前后，数据库的状态保持一致，不会出现数据不一致的情况。指事务的执行不会破坏数据库的完整性约束，所谓的完整性约束包括数据关系的完整性和业务逻辑的完整性
  3. 隔离性（Isolation）：事务的执行应该相互隔离，每个事务的执行应该与其他事务的执行相互独立。事务之间相互独立，不会相互影响，不会出现数据冲突的情况。
  4. 持久性（Durability）：事务执行完成后，其结果会永久保存在数据库中，不会因为系统故障而丢失。指的是事务对数据库的操作会被持久化到磁盘中，即使数据库系统崩溃或重启，也能够恢复到事务执行成功后的状态。

事务隔离级别

事务的隔离级别是指事务之间相互独立，不会相互影响，不会出现数据冲突的情况。

MySQL支持四种隔离级别，分别是：

  1. 读未提交（READ UNCOMMITTED）：事务可以读取其他事务未提交的数据，可能会出现数据不一致的情况。可能会导致脏读、幻读或不可重复读。
  2. 读已提交（READ COMMITTED）：事务只能读取其他事务已提交的数据。但是会导致幻读或不可重复读。
  3. 可重复读（REPEATABLE READ）：对同一字段的多次读取结果都是一致的。但是幻读仍有可能发生。这是MySQL默认隔离级别。

image-20230915180455594.png

  1. 串行化（SERIALIZABLE）：事务之间相互独立，不会相互影响。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。完全解决脏读、幻读或不可重复读。

脏读、幻读、不可重复读都是什么问题？

这三种问题都是由于事务之间的数据冲突而导致的数据不一致的情况。

  1. 脏读：指一个事务中访问到了另外一个事务未提交的数据。这种情况下，如果第一个事务回滚，那么第二个事务访问到的数据就会变成脏数据，导致数据不一致。
  2. 不可重复读：指前后读取的数据不一致；事务中的数据可能会被其他事务修改，导致数据不一致。
  3. 幻读：前后读取的记录数量不一致。其他事务新增、删除了第一个事务结果中的数据，导致数据的数量不一致。

严重程度

image-20230915175609155.png

4、MySQL索引

索引对 SQL 的性能提升非常明显，是一个性价比较高的 SQL 优化手段。

索引的优缺点

优点：

  1. 加快查询速度：索引可以加快数据库查询的速度，因为它可以快速定位数据，避免全表扫描。
  2. 提高性能：索引可以提高数据库的性能，因为它可以减少磁盘I/O操作，提高查询效率。
  3. 优化排序：索引可以优化排序操作，因为它可以按照索引字段进行排序，避免全表扫描。

​ 最重要的一点是可以大大减少检索的数据量，避免全表扫描。

缺点：

  1. 占用存储空间：索引会占用存储空间，因为它需要存储索引数据和索引结构。
  2. 增加写操作：索引会增加写操作的时间，因为它需要更新索引数据和索引结构。
  3. 维护成本高：索引需要定期维护，因为它需要更新索引数据和索引结构，以保证索引的正确性。

​ 总之，使用索引会耗费存储空间，增删改也会更复杂一些。

如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

为什么使用B+树结构

首先来看一下B+树的结构：

B+树结构.png

我们可以看到B+树结构的特点有：

  • 保证树的高度相对较小。
  • B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样，非叶子节点不存储data，只存储索引，可以放更多的索引。
  • 叶子节点包含所有索引字段，且数据索引从左到右递增排列
  • 叶子节点用双向指针连接，提高区间访问的性能

因此它作为索引的结构有以下优点：

  • B+树可以支持范围查询，因为它可以按照索引字段的值范围进行查询。
  • B+树的结构，决定了即使是千万级数据表，仍可快速查找到元素。
  • B+树非叶子节点只存储索引，这样一个节点能存放更多索引，存放相同数量的索引，B+树的高度会更小，查询时IO次数更少。

为什么不用Hash索引

首先要知道Hash索引的工作方式是怎么样的。哈希表使用key-value的方法，通过key可以迅速找到value。哈希表的所有 key 都会经过哈希函数计算,然后再存放数据。当出现Hash 冲突 问题时，通常采用链地址法，将哈希冲突数据存放在链表中。因此Hash索引的问题有：

  1. 不支持范围查询：hash索引只能支持等值查询，因为hash索引的key不是连续的，它通过哈希函数均匀分布。
  2. 不支持排序：hash索引只能按照索引字段的值进行排序，不支持自定义排序，因此无法满足某些查询需求。
  3. Hash冲突问题：当使用拉链法解决hash冲突时，要进行线性查找，这会增加查询的时间复杂度。
  4. 不支持高效更新：hash索引的更新效率较低，因为它需要重新计算hash值，重新分配内存空间，因此无法满足某些高效更新的需求。

B 树和 B+树的区别

  • B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，非叶子节点只起到索引的作用。
  • B+树叶子节点包含了全部的数据，同时由从左到右，从小到大的顺序通过指针连接在一起。
  • B+树的查询效率较高，可以通过指针快速定位数据。而B树的查询效率较低，因为它需要遍历整个树来定位数据。
  • B树的空间利用率较高，可以减少IO次数，磁盘读写代价更低

聚簇索引和非聚簇索引

聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

  • 聚簇索引叶子节点存储的是数据，查到了叶子节点就可以之间拿到数据。（主键索引是聚簇索引）
  • 非聚簇索引，数据和索引分开存储，叶子节点中存储的是数据行的地址，如果要取数据则要再进行一次查询拿到全部数据。（除了主键，其他索引都是非聚簇索引）。

在MyISAM引擎中，主键索引也是非聚簇索引，因此每次查数据都需要回表。

哪些列适合创建索引

  • 频繁作为WHERE查询条件的字段
  • 在经常需要根据范围进行搜索的列上
  • 对分组或者排序的字段，即经常GROUP BY和ORDER BY的列
  • 需要去重的字段。即需要使用DISTINCT的列
  • 区分度高的列适合作为索引
    • 可以使用公式select count(distinct a)/count(*)from t1计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。
    • 因此性别列不适合作为索引，因为只有两种，区分度太低。

什么是最左匹配原则

MySQL的联合索引遵循最左匹配原则。联合索引是有顺序的。例如（a，b，c）索引：

  • 相当于建立了索引a、ab、abc三个索引
select * from test where a=1 and b=1 and c =1;    #走abc索引
select * from test where a=1 and b=1;		#走ab索引
select * from test where a=1;			#走a索引
select * from test where a=1 and c =1;		#走a索引，c要全表查询
select * from test where b=1 and c =1;		#不走索引

EXPLAIN关键字

explain例1.png

explain例2.png

首先要明确：EXPLAIN关键字被用作分析SQL语句的性能，可以查看查看SQL的执行过程。

说一下比较关键的列：

  • type：连接使用了哪种类别,有无使用索引,常用的类型有：system, const, eq_ref, ref, range, index, ALL（从左到右，性能越来越差）
  • key：表示MySQL实际选择的索引
  • Extra：包含MySQL解决查询的详细信息
    • Using where。MySQL服务器将在存储引擎检索行后再进行过滤。
    • Uing index。所需要的信息直接从索引就可以拿到
    • Using filesort。无法利用索引完成的排序

5、MySQL锁机制

MySQL的锁机制是一种控制并发访问的机制，它可以避免多个事务之间的数据冲突，保证数据的一致性。

表级锁、行级锁和页级锁

  • 表级锁：MySQL的表级锁是最基本的锁机制，它可以实现对整张表的加锁，以控制多个事务之间的并发访问。表级锁的优点是简单易用，缺点是容易出现死锁，性能较差。

  • 行级锁：MySQL的行级锁是一种针对行级别的锁机制，它可以实现对某一行的加锁，以控制多个事务之间的并发访问。行级锁可以分为共享锁和排他锁两种类型，通过使用 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 语句来获取行级锁。

  • 页级锁：MySQL的页级锁是锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

共享锁和排他锁

  • 共享锁：又称读锁。其他事务可以读取数据，但不能对该数据进行修改，直到所有的共享锁被释放。如果事务对某行数据加上共享锁之后，可进行读写操作；其他事务可以对该数据加共享锁，但不能加排他锁，且只能读数据，不能修改数据。
  • 排他锁：又称写锁。如果事务对数据加上排他锁之后，则其他事务不能对该数据加任何的锁。获取排他锁的事务既能读取数据，也能修改数据。

总结，加共享锁其他事务可以获取共享锁进行读操作，但是无法进行写操作。而加排他锁后，其他事务无法获取，只有获取排他锁的事务才可以修改数据。

举个例子：

加排他锁可以使用select ...for update 语句

加共享锁可以使用select ... lock in share mode语句。

所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据

MySQL中的死锁

这里的死锁和操作系统中的死锁类似。指的是两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，相互等待对方释放资源，从而导致所有事务都无法继续执行的情况。

那就先说一下产生进程间死锁要满足的四个必要条件：

  • 互斥。一个资源每次只能被一个进程使用。
  • 不可剥夺。进程已获得的资源，在没有使用完之前，不能强行剥夺。只能自行释放。
  • 请求与保持。一个进程因请求资源而阻塞时，对已获得的资源保持不放。也就是说在等待资源的同时不会释放自己已经持有的资源。
  • 循环等待。多个进程互相持有对方所需的资源、互相等待。

数据库中的事务也类似。MySQL出现死锁的几个要素:

  • 两个或者两个以上事务。
  • 锁资源同时只能被同一个事务持有或者不兼容（对应互斥）
  • 每个事务都已经持有锁并且申请新的锁（对应请求与保持）
  • 事务之间因为持有锁和申请锁导致了循环等待（对应循环等待）

快照读和当前读

  1. 快照读：快照读是一种读取模式，它可以读取某一时刻的数据快照。很可能是历史版本，不用加锁。
  2. 当前读：当前读是一种读取模式，它可以读取当前版本的数据。读取的是记录的最新版本，并且返回的数据记录会加上锁，保证其他事务不能并发的修改数据记录。

快照读就是单纯的 SELECT 语句

当前读就是给行记录加 X 锁或 S 锁。常见SQL语句如下：

SELECT...FOR UPDATE   #排他锁
SELECT...FOR SHARE    #共享锁
......
  • 快照读性能好，但是不能保证数据的一致性。
  • 当前读要加锁，保证数据一致性但是对性能会有一定的损耗。

6、MySQL日志

MySQL中比较重要的日志：二进制日志 bin log，重做日志redo log和 回滚日志undo log。

二进制日志 bin log

bin log日志是MySQL的二进制日志，以二进制的形式记录了对于数据库的变更操作。不包括查询操作。bin log日志的作用包括：MySQL的备份恢复和MySQL的主从复制。

  • 备份恢复：通过使用 MySQL binlog工具来使恢复数据
  • 主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的

重做日志redo log

redo log 记录了数据页上的改动，事务中修改了的数据，将会备份存储。发生数据库服务器宕机、或者脏页未写入磁盘，可以通过redo log恢复。是Innodb存储引擎独有的日志。

具体来说就是只记录事务对数据页做了哪些修改

redo log 是物理日志，记录的是在某个表做了什么修改，用于 MySQL 异常重启时数据恢复，所以恢复速度比 bin log 更快。

总结一下bin log和redo log的区别：

  • bin log日志用于记录所有的数据修改操作。是在事务提交之后记录的，因此它可以用于数据备份、恢复、复制以及数据审计等方面。binlog日志是逻辑日志，记录的是数据库操作语句，因此可以跨平台使用，不受数据库引擎的影响。

  • redo log用于记录MySQL的物理日志，主要用于保证数据库的ACID事务特性。redolog记录的是InnoDB存储引擎中的数据修改操作，记录的是操作之前和之后的物理状态，用于在系统崩溃时恢复数据库到事务之前的状态。redolog是在事务提交之前记录的，因此可以保证在崩溃时数据的一致性和完整性。redolog日志是物理日志，记录的是数据块的修改，只能用于同种数据库引擎的恢复。

二者的场景不同：bin log日志是为了数据备份、复制、恢复。redo log是为了保证事务特性。

回滚日志undo log

要保证事务的原子性，需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的。

undo log有两个作用，事务回滚和MVCC。

  • 它跟redo log重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，这样发生错误时才可以回滚。

7、MySQL性能优化

索引失效场景

  • 未遵守最左匹配原则。
  • like左边包含%
  • 使用OR关键字
  • order by使用不当
  • 索引使用了函数
  • not in和not exists
  • 使用select *

慢查询日志

查看慢查询日志是否开启

SHOW VARIABLES LIKE  '%slow_query_log%';

慢查询日志.png

  1. 发现性能问题。慢查询日志记录了执行时间较长的SQL语句。
  2. 优化查询语句。慢查询日志记录SQL语句的执行时间、扫描的行数等信息。
  3. 监控数据库健康状况。慢查询日志记录所有执行的SQL语句的执行时间、扫描的行数等信息。

读写分离

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。

主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。一般情况下，都会选择一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。

8、小结

本文只是对MySQL的知识点做了一个简单的梳理，针对一些部分，之后我会做更详细的分享。

avatar
文章
阅读
粉丝