网站导航
> 技术文档 > 【MySQL 数据库 篇】MySQL架构、SQL语句在数据库框架中的执行流程、数据库的三范式、Char 和 varchar 的区别、varchar（10）和varchar（20）的区别、索引、B+树_sql语句中varchar(10)

【MySQL 数据库 篇】MySQL架构、SQL语句在数据库框架中的执行流程、数据库的三范式、Char 和 varchar 的区别、varchar（10）和varchar（20）的区别、索引、B+树_sql语句中varchar(10)

网友: 技术文档 2025-09-03 22:35:25

目录

1. 请说下你对MySQL架构的了解？

2. 一条SQL语句在数据库框架中的执行流程？

3. 数据库的三范式是什么？

第一范式：

第二范式：

第三范式：

范式总结：

4. Char 和 varchar 的区别？

5. varchar（10）和varchar（20）的区别？

6. 谈谈你对索引的理解？

优点：

缺点：

建立索引的原则：

不适合建立索引的情况：

常见的索引类型

7. 索引的底层使用的是什么数据结构？

8. 谈谈对B+树的理解？

9.  为什么InnoDB存储引擎选用B+树而不是B树呢？

10. 谈谈你对聚簇索引的理解？

1. 请说下你对MySQL架构的了解？

Mysql的基本架构图：

大体来说，MySQL可以分为Server层和存储引擎两部分。

Server层包括：

连接器：负责和客户端建立连接，获取权限，管理连接

查询缓存：在一个查询语句中，会先到缓存中查看之前是否查询过这条语句（如果开启了查询缓存功能）：若存在则直接返回缓存的结果，优点是命中缓存时效率很高，缺点是缓存失效非常频繁，只要有对一个表的更新，该表所有的查询缓存都会被清空，MySQL8.0版本已经删除了查询缓存功能

分析器：对SQL语句进行词法分析和语法分析，判断语句是否合法

优化器：对SQL语句进行优化，选择索引

执行器：调用存储引擎接口返回结果

存储引擎层负责：数据的存储和提取，其架构是插件式的，支持InnoDB MyISAM等多个存储引擎。从MySQL 5.5.5版本开始默认的是InnoDB，但是在建表时可以通过engine=MyISAM来指定存储引擎。不同存储引擎数据的存取方式不同，支持的功能也不同。

2. 一条SQL语句在数据库框架中的执行流程？

  1. 连接器：通过连接器客户端与服务器建立连接

  2. 查询缓存：服务器拿到SQL，先去看是否该语句执行过，执行过会有缓存，命中该缓存直接返回给客户端，缓存失效非常频繁，只要有对一个表的更新，该表所有的查询缓存都会被清空，MySQL8.0 版本已删除了查询缓存功能

  3. 分析器：如果没有命中缓存，通过分析器开始对语句进行词法分析，解析关键词，再进行语法分析，确定语句符合语法规范

  4. 优化器：对语句进行优化，选择索引，确定语句的执行方案

  5. 执行器：判断是否对表有执行权限，如果有权限，就打开表继续执行，根据表的存储引擎定义，通过这个引擎提供的接口获取数据并返回给客户端

3. 数据库的三范式是什么？

范式是关系型数据库设计的一种规范，目标是消除冗余，建立结果合理的数据库，从而提升数据存储和使用的性能。

第一范式：

每列的原子性，表中的每一个字段都是不可分割的，同一列中不能有多个值。第一范式是对关系模式的基本要求，不满足第一范式的数据库不是关系型数据库。

注意：第一范式要根据实际需求来定。

例如：考虑地址字段（黑龙江省大庆市高新区）是否符合第一范式，如果经常访问地址中的城市部分就不符合第一范式需要对地址进行拆分。如果不会访问拆分的部分，就符合第一范式，拆分反而不利于查询完整的地址。

第二范式：

确保唯一性和依赖性，每个表都有主键，且其他字段完全依赖主键。

第二范式是在第一范式的基础上，要求表中的每一条数据可以被唯一区分，通常使用主键实现，其他所有字段都完全依赖主键。

其他字段依赖主键是指，其他每个字段都与主键完全相关，当确定主键的值时就能确定其他所有字段的值。也就是说一个表只能存一种数据，不可以把多种数据存在一个表中。

完全依赖是指，联合主键时，其他字段不可以只依赖主键时的某个字段，必须依赖联合主键中的每个字段。

例如：

这里有两个问题：联合主键（学生编号、课程编号），而学生姓名只依赖于学生编号，违背完全依赖规则，还有学生编号和课程编号属于两种数据，不可以把他俩存到一个表中。

第三范式：

在第二范式基础上，非主键字段必须直接依赖于主键，不能存在传递依赖。

例如：

这个班级名依赖于班级编号，而班级编号依赖于学生编号这个主键，传递依赖了。

范式总结：

1NF：每列原子性，字段不可分割

2NF：唯一性和依赖性，要有主键，且其他字段完全依赖主键

3NF：没有传递依赖

范式的优缺点：

优点：

  1. 重复数据很少或者没有

  2. 表更小，可以更好的放在内存里，执行操作更快

  3. 更新操作更快

  4. 可以更少使用group by和distinct

缺点：复杂一点的查询需要关联，可能使索引无效

4. Char 和 varchar 的区别？

char：

  • 固定长度

  • 最大255字符

  • 数据长度不足会在尾部填充空格，查询时会自动去掉空格

  • 效率高，占空间

比如：订阅char（10），当你输入“abc”三个字符的时候，他们占的空间还是10个字节，其他7个是空字节。

使用场景：存储密码的md5值，固定32字符长度的，使用char非常合适。

md5值是一种广泛使用的密码散列函数：特性：输入任意长度数据->输出固定128位（32字符）哈希值

例如：md5(\"hello\") = \"5d41402abc4b2a76b9719d911017c592\"

密码存储原理：不存储明文密码，只存储哈希值，用户登录时：对输入密码计算md5->与存储的md5比对

varchar：

  • 长度可变

  • 最大可设置65535字节

可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。所以，从空间上考虑varchar比较合适；从效率上考虑char比较合适，二者使用需要权衡。

5. varchar（10）和varchar（20）的区别？

varchar（10）中的10的涵义最多存放10个字符，varchar（10）和varchar（20）存储hello所占空间一样，但后者在排序时会消耗更多的内存，因为order by（排序指令）col（列名）采用 fixed_length计算col长度

注：fixed_length （固定长度）是MySQL内部排序时使用的一种计算方式，当varchar列进行排序时，MySQL会按照该列定义的最大长度（而不是实际长度）来分配内存空间。

6. 谈谈你对索引的理解？

index，是存储引擎用于快速找到数据的一种数据结构。

MySQL默认使用InnoDB存储引擎，该存储引擎最重要、使用最广泛的，除非有非常特别的原因需要使用其他存储引擎，否则优先考虑InnoDB。

索引的出现是为了提高数据的查询效率，就像书的目录一样。一本500页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会。同样，对于数据库的表而言，索引其实就是它的“目录”。

优点：

  • 减少服务器需要扫描的数据量

  • 帮助服务器避免排序和临时表（覆盖索引：索引包含了查询需要的所有列，数据库可以只读取索引而不访问表数据，就不需要创建包含完整记录的临时表了）

  • 索引可以将随机I/O变为顺序I/O，提高查询性能。

索引如何实现将随机I/O变为顺序I/O的？

没有索引的情况

当执行一个需要排序的查询时：

  1. 数据库必须读取所有符合条件的行（随机I/O，因为数据再磁盘上是分散存储的）

  2. 在内存或临时表中排序

  3. 返回结果

有索引的情况

  1. 数据库直接按索引顺序读取数据（索引本身是顺序存储的）

  2. 不需要额外排序

  3. 由于索引指向的数据物理位置可能仍然分散，但现代数据库有优化；会将随机访问批量处理，减少磁头移动

缺点：

  1. 从空间角度考虑，建立索引需要占用物理空间

  2. 从时间角度考虑，创建和维护索引都需要花费时间，这个时间随着数据量的增加而增加；例如对数据进行增删查改的时候都需要维护索引。

建立索引的原则：

  1. 在最频繁使用的、用以缩小查询范围的字段上建立索引；

  2. 在频繁使用的、需要排序的字段上建立索引。

不适合建立索引的情况：

  1. 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引；

  2. 对于一些特殊的数据类型，不宜建立索引，比如：文本字段（text）等。

常见的索引类型

  1. 哈希索引：基于哈希表实现，查找非常块，但不支持范围查找和排序操作，也不支持部分索引列的查找，只支持等值比较的查询。如果哈希冲突很多的话，索引的维护代价会很高。因此，哈希索引只适用某些特定的场合。在InnoDB中，支持的哈希索引是自适应的，不能人为创建。

  2. 全文索引：用于全文搜索的索引类型（倒排索引），可以执行关键字搜索。全文索引有很多限制，例如当数据量很大，内存无法装载全部索引时，搜索速度可能会非常慢。全文索引的维护成本也很大。MySQL支持全文索引，InnoDB从1.2版本开始支持全文索引。

  3. B+树索引：B+树索引就是传统意义上的索引，是目前关系型数据库中查找最为常用和最为有效的索引。B+树索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。B+树索引是顺序组织存储的，所以很适合查找范围数据。B+树索引分为聚簇索引（主键索引）和非聚簇索引（二级索引）。

7. 索引的底层使用的是什么数据结构？

MySQL中常用的是Hash和B+树索引

Hash索引：基于哈希表实现，非常快，但是不支持范围查找和排序，在MySQL中支持的哈希索引是自适应的，不能人为创建。

B+树索引：B+树索引就是传统意义上的索引，是目前关系型数据库中查找最为常用和最为有效的索引。B+树索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。B+树索引是顺序组织存储的，所以很适合查找范围数据。B+树索引分为聚簇索引（主键索引）和非聚簇索引（二级索引）。

8. 谈谈对B+树的理解？

  1. B+树是基于B树和叶子节点顺序访问指针（指的是所有叶子结点之间通过指针按顺序连接起来，形成一个有序链表的结构）进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

  2. 在B+树中，一个节点中的key从左到右非递减排列，如果某个指针的左右相邻key分别是key i和key i+1，且不为null，则该指针指向节点的所有key大于等于keyi且小于等于keyi+1

  3. 进行查找操作时，首先在根节点进行二分查找，找到一个key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的data。

  4. 插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

9.  为什么InnoDB存储引擎选用B+树而不是B树呢？

  1. B+树减少了IO次数：由于索引文件很大因此索引文件存储在磁盘上，B+树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机I/O读取次数相对就减少了。

  2. B+树查询效率更稳定：由于数据只存在在叶子结点上，所以查找效率固定为O（logn），所以B+树的查询效率相比B树更加稳定。

  3. B+树更加适合范围查找：B+树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+树的效率更高。

10. 谈谈你对聚簇索引的理解？

聚簇索引，一种数据存储方式，将数据放在索引的叶子页，索引和数据在同一个B+树上。因为无法同时把数据放在两个地方，所以一个表只能有一个聚簇索引。

聚簇索引VS普通索引：

聚簇索引：叶子节点存储的是完整的行数据（不仅仅是主键，而是该行的所有字段）。数据和索引绑定在一起，因此通过主键查询时可以直接获取完整数据，无需回表。

普通索引：叶子节点存储的是主键值（不是完整数据）。查询时需先通过普通索引找到主键，再通过主键回表到聚簇索引获取完整数据（多一次查找）。

为什么一个表只能有一个聚簇索引？

聚簇索引的叶子节点直接存储行数据，而数据行在磁盘上只能按一种顺序物理存储（例如按主键排序）。如果允许存在多个聚簇索引，同一行数据就需要按不同顺序存储多次，导致数据冗余和一致性维护问题。

InnoDB的默认行为：

在InnoDB中，这个索引是主键，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，也没有的话InnoDB会隐式定义一个主键作为聚簇索引。

总结：

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引排序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

聚簇索引和非聚簇索引的区别？

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

网络标签：

相关问题