数据库基础-牛客

MySQL 的存储引擎有哪些？它们之间有什么区别？默认使用哪个？ #

MySQL的存储引擎是用于存储、处理和数据保护的核心组件。

InnoDB
- 特点：支持事物、行级锁、外键约束
- 优点：数据完整性高，适合高并发读写，崩溃后自动恢复
- 缺点：需要权衡其性能和资源消耗
- 应用场景：需要事务支持的应用
MyISAM
- 特点：不支持事务，使用表级锁，访问速度快
- 优点：读操作性能高，占用空间小
- 缺点：数据完整性差，奔溃后恢复困难
- 应用场景：读多写少的场景

SQLite 与 InnoDB 数据库引擎的主要区别 #

SQLite 和 InnoDB 都是流行的数据库引擎，但它们在设计理念、架构和应用场景上有显著差异：

1. 基本架构差异 #

特性	SQLite	InnoDB
架构类型	嵌入式、无服务器	客户端-服务器(作为MySQL存储引擎)
进程模型	库形式直接链接到应用程序	作为MySQL服务器的一部分运行
部署方式	单文件数据库	多文件存储系统

2. 并发处理能力 #

特性	SQLite	InnoDB
锁机制	数据库级锁(写独占)	行级锁
并发写	同一时间只允许一个写操作	支持多事务并发写入
隔离级别	默认SERIALIZABLE	支持多种隔离级别

3. 存储与性能 #

特性	SQLite	InnoDB
存储格式	单个磁盘文件	表空间文件(ibd)系统
索引类型	B-tree	B+tree
缓存	页面缓存	缓冲池(更复杂的内存管理)
全文搜索	需要FTS扩展	内置FULLTEXT索引支持

4. 功能特性对比 #

特性	SQLite	InnoDB
外键	需要显式启用	默认支持
ACID	完全支持	完全支持
复制	不支持	通过MySQL主从复制支持
分区	不支持	支持表分区

SQLite的B-tree与InnoDB的B+tree区别 #

SQLite和InnoDB虽然都使用基于树结构的存储引擎，但它们在实现上有显著差异：

核心数据结构差异 #

特性	SQLite的B-tree	InnoDB的B+tree
基本结构	纯B-tree结构	B+tree变种(所有数据存储在叶子节点)
节点结构	每个节点都包含键和数据	内部节点只存键，数据全在叶子节点
叶子节点链接	叶子节点没有相互链接	叶子节点通过指针双向链接形成有序链表
数据存储位置	数据可存在于任何节点	数据仅存在于叶子节点

性能特点对比 #

特性	SQLite的B-tree	InnoDB的B+tree
点查询性能	平均O(log n)	平均O(log n)，但更稳定
范围查询性能	需要多次树遍历	通过叶子节点链表高效扫描
插入/删除效率	节点分裂/合并频率较高	更平衡的结构，操作更稳定
空间利用率	通常较低(约50-70%)	更高(通常75%以上)

实现细节差异 #

SQLite的B-tree：
- 使用称为"B*-tree"的变体
- 页面大小固定(默认4KB)
- 支持表使用B-tree，索引使用B-tree
- 没有预读优化
InnoDB的B+tree：
- 高度优化的B+tree实现
- 支持可变页面大小(默认16KB)
- 表数据以聚簇索引形式存储
- 具有预读和缓存优化

InnoDB 是如何存储数据的？ #

InnoDB通过表空间、页和行的结构化方式存储数据，将数据保存在磁盘上的数据文件中，采用聚簇索引来组织数据行，支持事务外键和行级锁，从而实现高效的的数据检索和管理。

表空间
- InnoDB使用表空间来存储所有数据，包括表、索引和事务日志
- 表空间可以是共享的（系统表空间）或独立的（每一个表一个.ibd文件）
页
- 数据在表空间中以页为单位存储，每页大小通常为16KB
- 页是InnoDB的最小存储单位，包含行数据、索引等。
行
- 行是数据存储的基本存储单位，每行存储在页中
- InnoDB支持行级锁，运行高并发访问
索引
- InnoDB使用B+树来加速数据检索
- 主索引（聚簇索引）将数据存储在叶子节点，辅助索引存储指向主键的指针。
事务日志
- 用于记录事务操作，支持奔溃恢复。

图示说明 #

+-------------------+
|  表空间            |
| +--------------+  |
| | 页 (16KB)     | |
| | +----------+  | |
| | | 行        | | |
| | | 行        | | |
| | +----------+ | |
| +--------------+ |
+-------------------+

表空间：存储所有数据。
页：最小存储单位。
行：基本数据单元。

通过这种结构化的存储方式，InnoDB 能够高效地管理和检索数据，支持事务和并发访问。

MySQL 一行记录是怎么存储的？ #

一行数据存储在数据页中，包含行头信息、实际数据和可变长度字段的偏移量，具体结构因存储引擎而异。

页
- 数据以页为单位存储，每页通常16KB
- 页中包含多行记录
行记录结构
- 行头信息：存储行的元数据，如删除标记，下一行指针等
- 固定长度字段：存储定长数据，如整数、定长字符串
- 可变长度字段列表：存储变长数据的偏移量
- NULL值列表：记录哪些字段为NULL
行溢出
- 如果行数据过大，可能会导致行溢出，数据存储在溢出页中
压缩
- InnoDB 支持行压缩，减少存储空间

详细描述一条 SQL 在 MySQL 中的执行过程 #

一条sql在mysql中的执行过程包括解析器解析sql语句，优化器生成执行计划，存储引擎执行计划并返回结果，最后由mysql服务层处理和返回客户端。

连接管理
- 客户端通过连接器与MySQL服务器建立连接
查询解析
- SQL语句被发送到解析器，进行语法和语义检查
- 生成解析树
查询优化
- 优化器对解析树进行优化，选择最优执行计划
- 考虑索引，表连接顺序等
执行计划生成
- 根据优化结果生成执行计划
执行引擎
- 执行引擎根据执行计划访问存储引擎，获取数据
结果返回
- 将结果集返回给客户端
缓存
- 查询结果可能会被缓存，以提高后续相同查询的速度

MySQL 的查询优化器如何选择执行计划？ #

通过分析sql语句的多种执行路径，结合表的统计信息、索引的可用性、查询条件等因素。评估每种路径的成本，选择代价最低的执行计划。优化器会考虑使用索引、表连接顺序、排序和分组等操作，以提高查询效率。最终优化器生成的执行计划将传递给执行器执行。

语法分析与语义检查

查询优化器首先解析SQL语句，将其转换为内部的逻辑查询树，并进行语义检查，确保查询的语法和逻辑是正确的。
成本估算

优化器会根据表的统计信息（如行数、索引分布、列的基数等）来估算不同执行计划的成本。这些统计信息是通过ANALYZE TABLE或自动更新机制获取的。成本估算包括I/O成本、CPU成本和网络成本等。
生成可能的执行计划

优化器会生成多种可能的执行计划，包括不同的表连接顺序、索引使用方式、连接算法（如嵌套循环连接，哈希连接等）和子查询处理方式。
选择最优计划

优化器通过比较不同执行计划的成本，选择成本最低的计划作为最终执行计划。优化器会考虑多种因素，如索引的选择，表的大小，过滤条件的效率等。
动态调整

在某些情况下，优化器还会根据运行时信息（如缓存命中率，实时数据分布等）动态调整计划，进一步优化查询性能。

SQL 中 select、from、join、where、group by、having、order by、limit 的执行顺序？ #

from，选择查询的表，join连接关联表，where，根据条件过滤表数据，group by，对数据进行分组，having，对分组进行过滤，select选择查询的列，order by，对数据排序，limit限制返回数据大小

SQL 的执行顺序通常为：from->join->where->group by->having->select->order by->limit。

MySQL 中的数据排序（ORDER BY）是如何实现的？ #

使用排序算法对结果集进行排序，可能设计文件排序和内存排序

索引排序

如果查询的order by字段上有合适的索引，mysql可以直接利用索引的有序性来返回排序结果，而无需额外的排序操作。这种方式效率最高，因为它避免了额外的内存或磁盘排序开销。
文件排序

当无法使用索引进行排序时，mysql会使用文件排序算法。如果数据量较小，排序会在内存中完成；如果数据量较大，超过了系统配置的内存限制，则会将部分数据写入磁盘临时文件，然后进行外部排序。文件排序可能会涉及多次磁盘I/O，因此性能相对较低。
优先队列排序

对于某些特定的查询，如带有limit的查询，mysql可能会使用优先队列（也称为堆排序）来优化排序过程。优先队列排序可以在数据到达时动态维护一个有序队列，从而减少排序开销。

排序算法

mysql使用快速排序或合并排序等高效算法

多列排序

支持按多列排序，先按第一列排序，再按第二列排序

排序方向

支持生序和降序排序

如何实现数据库不停服迁移？ #

关键步骤包括：使用主从复制或数据同步工具复制数据，采用双写机制确保数据一致性，逐步切换读写流量

并监控系统稳定性，进行数据校验，准备回滚机制以应对问题，以及实时监控和优化系统性能。

数据复制

使用主从复制或数据同步工具，将数据从源数据库复制到目标数据库
双写机制

在迁移期间，应用程序同时写入源数据库和目标数据库，确保数据一致性。
流量切换

逐步将读写流量从源数据库切换到目标数据库，监控系统稳定性
数据校验

在迁移完成后，进行数据校验，确保源数据库和目标数据的数据一致性
回滚机制

准备好回滚方案，以应对可能出现的问题
监控和优化

在迁移过程中，实时监控系统性能，及时调整策略

UNSIGNED 属性有什么用？ #

unsigned 属性用于指定整数类型字段不允许存储负数，从而将可用的存储空间全部用于表示非负数，增加了字段的正数范围

扩展正向范围

unsigned讲整数类型的范围从负数扩展到正数
节省存储空间

在不需要负值的情况下，使用unsigned可以更高效地利用存储空间
数据完整性

确保数据不会出现负值
性能优化

在某些情况下提高计算性能，因为不用处理负数

MySQL 中 int(11) 的 11 表示什么？ #

表示显示宽度，影响数据在某些工具中的显示格式，但不影响存储范围或实际存储大小

显示宽度
- int(11)中的11指定了显示时的最小字符宽度
- 仅在使用zerofill时有效，自动填充前导0
存储大小

int类型的存储大小固定为4字节，与显示宽度无关
zerofill
- 使用zerofill时，数字会被填充前导0以达到指定宽度
实际应用

显示宽度通常用于格式化输出，不影响数据存储和计算

CHAR 与 VARCHAR 有何区别？ #

char是固定长度的字符串，适合存储定长数据，而varchar是可变长度的字符串类型，适合存储长度不固定的数据，varchar会根据实际数据长度分配存储空间。

存储方式
- char固定长度，空白填充到指定长度
- varchar可变长度，实际存储长度加上一个字节用于记录长度
长度限制
- char最大长度为255字节
- varchar最大长度为65535字节，受限于行大小
性能
- char适合存储定长数据，访问速度快
- varchar适合存储变长数据，节省空间

VARCHAR(100) 与 VARCHAR(10) 的区别？ #

区别在于它们允许存储定最大字符数不同，前者最多存储100个字符，而后者最多存储10个字符，影响字段的存储容量和数据长度限制

最大长度
- VARCHAR(100)：最多可存储 100 个字符
- VARCHAR(10)：最多可存储 10 个字符
存储空间

实际存储空间取决于字符串的实际长度，可以加上一个或两个字节用于记录长度
灵活性
- VARCHAR(100) 提供更大的灵活性，适合存储长度不确定的字符串。
- VARCHAR(10) 适合存储长度较短且固定的字符串。
性能
- 较大的VARCHAR可能导致更多的内存分配，但在实际应用中影响较小。

DECIMAL 与 FLOAT/DOUBLE 的区别？ #

decimal是用于存储精确小数据的定点数类型，适合财务计算，而float和double用于存储近似小数的浮点数类型，适合科学计算，float和double在存储时可能会有精度损失

精度
- DECIMAL：精确存储，适合需要高精度的场景，如财务计算。
- FLOAT/DOUBLE：近似存储，可能有舍入误差。
存储方式
- DECIMAL：以字符串形式存储，精度固定。
- FLOAT/DOUBLE：以二进制浮点数存储，精度随数值变化。
性能
- DECIMAL：计算速度较慢，因需处理精确数值。
- FLOAT/DOUBLE：计算速度快，适合科学计算。

DATETIME 与 TIMESTAMP 的区别？ #

datetime存储绝对时间，与时区无关，而timestamp存储相对时间，受时区影响，timestamp通常用于记录事件发生的时间点

存储范围
- date time：范围从1000-01-01 00:00:00到9999-12-31 23:59:59。
- TIMESTAMP：范围从1970-01-01 00:00:01UTC 到2038-01-19 03:14:07UTC。
时区处理
DATETIME：不受时区影响，存储的是绝对时间。
TIMESTAMP：受时区影响，存储的是相对时间，自动转换为当前时区。
存储大小
- DATETIME：占用 8 字节。
- TIMESTAMP：占用 4 字节。
自动更新
- TIMESTAMP：可以设置为自动更新为当前时间。
- DATETIME：需要手动更新。

NULL 与 ’’ 有什么区别？ #

null表示未知或缺失的值，意味着没有数据，而‘’是一个空字符串，表示已知但为空的值，占用存储空间，可以与其他字符串比较。

Boolean 类型在 MySQL 中如何表示？ #

在 MySQL 中，Boolean 类型通常用TINYINT(1)表示，其中0代表false，1代表true。

存储占1字节

为什么不推荐使用 TEXT 和 BLOB？ #

不推荐使用text和blob因为它们会导致性能下降，索引和查询效率低，且占用较多存储空间，影响数据库的整体性能

性能问题

处理大数据时，text和blob会导致查询速度变慢
索引限制

不能直接对text和blob列创建完整索引，只能索引前缀
存储管理

大数据可能导致表空间膨胀，影响存储管理
备份和恢复

备份和恢复需要更多时间和资源
应用场景

适合存储大文本或二进制数据，但不适合频繁查询和更新

在 MySQL 中存储金额应使用什么数据类型？ #

在 MySQL 中存储金额应使用DECIMAL数据类型，因为它能够精确存储小数，避免浮点数类型可能导致的精度损失。

存储精度

decimal以字符串形式存储，提供精确的定点数表示
避免浮点数误差

使用float和double可能导致舍入误差，不适合财务计算
灵活性

可以指定精度和小数位数，如decimal（10，2）表示最多10位数字，其中两位小数
性能

虽然decimal的计算速度略慢于浮点数，但在财务应用中，精度优先于速度
应用场景

是用于所有需要精确数值的场景

MySQL 如何存储 IP 地址？ #

MySQL 存储 IP 地址可以使用VARCHAR、INT或VARBINARY类型，具体选择取决于存储需求和查询性能。

使用VARCHAR
- 适合存储 IPv4 和 IPv6 地址，格式直观。
- 优点：易读易写，支持直接存储点分十进制格式。
- 缺点：占用空间较大，查询性能较低。
使用INT
- 适合存储 IPv4 地址，将其转换为整数。
- 优点：占用空间小（4 字节），查询性能高。
- 缺点：仅支持 IPv4，不支持 IPv6。
使用VARBINARY
- 适合存储 IPv6 地址，将其转换为二进制格式。
- 优点：支持 IPv4 和 IPv6，占用空间合理。
- 缺点：不易读写，需要转换。

什么是数据库视图？ #

数据库视图是基于一个或多个表的查询结果创建的虚拟表，用于简化复杂查询，提高数据安全性和提供数据抽象，基于SQL查询定义。

虚拟表

视图不存储数据，只存储查询逻辑
数据简化

通过视图，可以简化复杂查询，提供更易理解的数据表示
安全性

视图可以限制用户访问特定数据，增强数据安全性
可更新性

某些视图是可更新的，允许通过视图修改基础表的数据
性能

视图的性能取决于底层查询的复杂性，可能影响查询速度

什么是数据库游标？ #

数据库游标是一种数据库对象，用于逐行处理查询结果集，允许在结果集中进行遍历、检索、更新和删除操作，适合需要逐行处理数据场景。

指针机制

游标是一个指针，指向查询结果集中的某一行
逐行处理

允许在结果集中逐行移动，进行逐行处理
使用场景

适用于需要逐行处理数据的复杂操作，比如批量更新或计算
性能考虑

游标操作通常比批量操作慢，需要谨慎使用
游标类型

支持不同类型的游标，如只进游标、可滚动游标等

为什么不建议直接存储大对象（图片 / 音频 / 视频）？ #

主流数据库大多采用行存储结构页大小默认为16kb 大对象一般为MB级别远超存储限制造成行溢出数据库索引（如 B + 树）主要针对结构化数据（如数字、字符串）设计，对二进制大对象无法建立有效索引，导致：无法通过内容快速检索（如按图片颜色、视频关键词查询），需全表扫描。即使使用 UUID 等标识索引，也无法避免大对象本身的 IO 开销。大对象的读取 / 写入属于随机 IO（需频繁跳转不同数据页或溢出页），而数据库的优势在于顺序 IO（如批量查询结构化数据）。大对象传输占用大量带宽和数据库连接资源

数据库的三大范式是什么？ #

第一范式（1NF）：确保每列的值都是不可分割的原子值。
第二范式（2NF）：在满足第一范式的基础上，确保每个非主属性完全依赖于主键。
第三范式（3NF）：在满足第二范式的基础上，确保每个非主属性不传递依赖于主键。