跳至主要內容

MySQL

GGBOND2025/5/21大约 10 分钟

一、基础知识

说一下你对MySQL的理解？

什么是 MySQL？

  • MySQL 是目前最流行的关系型数据库管理系统（RDBMS）

关系数据库 vs 非关系数据库

特性关系型数据库（MySQL）非关系型数据库（NoSQL）
数据存储表结构（行和列），预定义模式文档、键值对、图、列族等灵活结构
事务支持强一致性（ACID特性）最终一致性（CAP定理）
查询语言SQL（标准化）各有特色（MongoDB-JS、Redis-命令等）
扩展性垂直扩展（增加硬件性能）水平扩展（分布式，加机器）
适用场景复杂关系查询、银行交易、ERP系统高并发读写、大数据处理、快速迭代
数据结构结构化、半结构化数据非结构化、多样化数据
示例MySQL、PostgreSQL、OracleMongoDB、Redis、Cassandra

为什么大家都用它？

  • 数据一致性：事务保证数据准确性
  • 查询能力强：SQL语句支持复杂关联查询
  • 生态成熟：工具丰富，社区支持完善
  • 成本低：开源免费

MySQL 的事务四大特性（ACID）是什么？

  • 原子性（Atomicity）：事务要么全成功要么全失败。
  • 一致性（Consistency）：执行前后数据保持一致。
  • 隔离性（Isolation）：事务间互不干扰。
  • 持久性（Durability）：事务提交后数据永久保存。

Mysql的索引有哪些类型？什么时候使用？

按数据结构分类：

  • B+Tree索引：InnoDB默认，支持范围查询和排序
  • Hash索引：Memory引擎，等值查询极快，不支持范围查询
  • 全文索引：FULLTEXT，用于文本搜索（MySQL 5.6+支持InnoDB）

按逻辑特性分类：

  • 主键索引：唯一标识，不允许NULL，一张表只能有一个
  • 唯一索引：列值唯一，允许NULL，可有多个
  • 普通索引：无约束，提高查询速度
  • 联合索引：多列组合，遵循最左前缀原则

使用场景：

  • 频繁作为 WHERE、JOIN、ORDER BY 条件的字段
  • 避免在频繁更新、低选择性列上建索引

Mysql索引详解

说一下b+树的索引结构，Mysql为什么选用b+树？

B+树是什么？

B+树是一种多路平衡查找树，专门为磁盘存储优化设计。想象成一棵倒着生长的树，数据都存储在最底层的"叶子"上。

  • 多路结构：每个节点可存储多个关键字，减少树高度
  • 平衡特性：所有叶子节点在同一层，保证查询效率稳定
  • 有序排列：节点内关键字有序，叶子节点间有指针连接

B树与B+树的区别

对比维度B树B+树
数据存放位置所有节点都存数据只有叶子节点存完整数据
叶子节点关系各自独立，无连接用指针连成有序链表
查询稳定性不稳定，可能在中间找到稳定，必须到最底层
范围查询效率需要来回跳转，效率低顺着链表走，效率高
节点利用率存数据+指针，关键字少只存指针，关键字更多

MySQL选择B+树的原因

  1. 磁盘I/O优化：B+树更矮胖，减少磁盘I/O次数
  2. 范围查询高效：叶子节点链表结构支持快速范围扫描
  3. 查询性能稳定：所有查询路径长度相同，性能可预测
  4. 内存利用高效：非叶子节点可容纳更多索引项，减少内存占用

生活化比喻：
B树像传统图书馆，每层书架都有完整书籍；B+树像现代图书馆，目录层只有索引卡片，实际书籍全在一楼，且按编号连续排列。

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

常见存储引擎：

存储引擎核心特点适用场景生活化比喻
InnoDB支持事务、行级锁、外键高并发业务、电商订单、银行转账现代化图书馆 - 借书还要登记，还书要确认，多人同时借阅不冲突
MyISAM不支持事务，表级锁读多写少、新闻网站、博客传统书店 - 大家可以随便看，但只能一个人付钱（写操作会锁整张表）
Memory存储在内存中，速度极快临时数据、会话管理、缓存便签纸 - 写得快记得快，但停电就没了
Archive压缩存储，只支持插入查询日志记录、历史数据归档保险箱 - 安全压缩保存，但取出来麻烦
CSV以CSV文件存储数据导入导出纸质表格 - 容易被其他软件读取，但功能简单

如何指定存储引擎？、

CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB;

默认存储引擎：InnoDB（MySQL 5.5 之后）

InnoDB 是如何存储数据的？

  1. 页（Page）为最小存储单位

    • 默认页大小 16KB。
    • 数据存储在页中，页之间通过双向链表连接。

  2. 行（Row）存储

    • InnoDB 是 面向行的存储引擎。
    • 每一行数据存储在页中，行溢出时会用溢出页。

  3. 表空间（Tablespace）

    • InnoDB 把数据存储在表空间中（共享表空间 ibdata 或者独立 .ibd 文件）。

  4. 索引组织表（Clustered Index）

    • InnoDB 的表数据按 主键顺序存储，表数据和主键索引存储在同一棵 B+Tree 中。
    • 叶子节点存储整行数据。
    • 如果没有主键，会选择唯一索引或自动生成一个隐藏主键。

  5. 辅助索引（二级索引）

    • 二级索引叶子节点存储的是 主键值，再通过主键索引找到整行数据（二次查找）。

总结：
InnoDB 使用 页（16KB）作为最小存储单位，数据以 聚簇索引（B+Tree） 形式存储在表空间中，主键索引存整行数据，二级索引存主键值。

二、SQL语句

如何优化 SQL 性能？

  • 使用合适索引，避免全表扫描；
  • 避免 SELECT *，只查必要字段；
  • 使用 EXPLAIN 查看执行计划；
  • 避免子查询，尽量用 JOIN；
  • 控制返回行数，分页查询要加 LIMIT。

OVER 与 GROUP BY 的区别？

一、核心区别

特性GROUP BYOVER（窗口函数）
作用方式对数据分组后聚合，每组只保留一行结果对每一行数据进行计算，不减少行数
返回结果行数通常比原表少，按组聚合与原表行数相同，每行追加计算结果
适用场景仅关心每组的聚合结果，如“每个部门的总人数”需保留明细数据并附带分析值，如“每人所在部门总人数”
列限制SELECT中只能出现GROUP BY字段或聚合函数SELECT中可使用任意字段

二、举例说明

1. GROUP BY 示例：统计每个部门总工资

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
  • 每个部门输出一行，展示该部门工资总和。
  • 结果行数 = 部门数。

GROUP BY 的聚合逻辑：

当你使用GROUP BY player_id时，SQL 会将每个玩家的所有记录合并为一个分组。

对于event_date列，你没有指定聚合函数（如 MIN、MAX），因此数据库会从分组中随机选择一个值作为结果，而不是按照日期排序后取第一个。

2. OVER 示例：在每行展示“该员工所在部门的总工资”

SELECT employee_name, department_id,
       SUM(salary) OVER (PARTITION BY department_id) AS dept_total_salary
FROM employees;
  • 每行保留员工信息，并显示他所在部门的总工资。
  • 结果行数 = 原始员工数。

sql语句关键字顺序？何其执行时的顺序？

回答：

语句关键字顺序:
SELECT [DISTINCT] select_list
FROM table_expression
[WHERE condition]
[GROUP BY grouping_expression]
[HAVING group_condition]
[WINDOW window_definition]
[ORDER BY sort_expression]
[LIMIT | OFFSET row_count]

执行时的顺序:
-- 1. FROM 和 JOIN
FROM Employees e
JOIN Salaries s ON e.employee_id = s.employee_id
-- 2. WHERE
WHERE e.hire_date > '2020 - 01 - 01'
-- 3. GROUP BY
GROUP BY e.department_id
-- 4. HAVING
HAVING AVG(s.salary) > 50000
-- 5. SELECT
SELECT e.department_id, AVG(s.salary) AS avg_salary
-- 6. ORDER BY
ORDER BY avg_salary DESC;

一条 SQL 在 MySQL 中的执行过程?

回答：

  1. 连接器

    • 客户端先通过连接器与 MySQL 建立连接（TCP）。
    • 如果有连接池，会从池中获取。
    • 建立连接后进行权限认证（账号、密码、权限）。

  2. 查询缓存（MySQL 8.0 已移除）

    • 在 5.7 及之前版本，MySQL 会先检查缓存是否有相同 SQL 的结果。
    • 有则直接返回，没有则进入解析阶段。

  3. 解析器

    • 对 SQL 语句进行词法分析、语法分析。
    • 检查 SQL 语法是否正确，解析出 SQL 的语义，生成解析树。

  4. 优化器

    • 对解析树进行优化，选择合适的执行计划。
    • 比如：选择哪个索引、使用全表扫描还是索引扫描、确定表连接顺序。

  5. 执行器

    • 根据优化器生成的执行计划，调用存储引擎接口执行 SQL。
    • 判断用户是否有权限操作相关表和字段。

  6. 存储引擎

    • InnoDB、MyISAM 等存储引擎真正执行数据的读写操作。
    • InnoDB 会用到 Buffer Pool、redo log、undo log 等机制保证事务和持久性。

  7. 返回结果

    • 存储引擎将结果返回给执行器 → 执行器返回给客户端。

👉 总结：
客户端 → 连接器 → 查询缓存 → 解析器 → 优化器 → 执行器 → 存储引擎 → 返回结果?

三、线程池

为什么数据库连接很消耗资源？

  1. 建立连接开销大：需要进行网络通信（TCP/SSL握手）、身份认证、资源分配。
  2. 连接占用资源：数据库端要维护会话信息、内存、线程/进程句柄。
  3. 连接保持有成本：需要检测连接状态，空闲连接也会消耗内存和线程。
  4. 连接关闭有开销：释放资源需要时间，频繁创建/销毁连接影响性能。

因此，数据库连接属于重量级资源，通常通过连接池来复用以降低开销。

什么是数据库连接池？使用过哪些？

回答：
连接池通过复用连接降低连接开销，提高性能。常见连接池：

  • HikariCP（推荐，轻量高效）
  • Druid（阿里开源，功能全面）
  • C3P0（较老，配置复杂）

为什么连接池可以降低开销？

  1. 避免频繁建立/关闭连接：

    • 连接池在启动时就提前创建一定数量的连接并放入池中。
    • 应用请求数据库时直接复用已有连接，减少频繁的网络通信、身份认证、资源分配开销。

  2. 复用连接资源：

    • 同一个连接可以被多个请求依次使用。
    • 避免了数据库端维护过多短生命周期连接导致的资源浪费。

  3. 统一管理连接数量：

    • 可以设置最大连接数，防止过多连接耗尽数据库资源。
    • 空闲时可以回收或保活，避免资源闲置或失效。

连接池是如何实现的？

  1. 初始化：启动时预先创建一定数量的数据库连接（minIdle / initialSize）。

  2. 获取连接：应用请求时，从池中取出可用连接，而不是重新建立。

  3. 使用连接：应用执行 SQL 操作。

  4. 归还连接：操作完成后，将连接放回池中，供下次复用，而不是关闭。

  5. 池管理机制：

    • 最大连接数：限制并发连接上限，保护数据库。
    • 空闲检测：定期检测连接是否可用，不可用则移除。
    • 连接保活：执行心跳 SQL（如 SELECT 1）保持连接可用。
    • 等待队列：当连接用尽时，新请求进入队列等待，避免直接报错。

常见实现：HikariCP、Druid、C3P0、DBCP。

Copyright © 2025 GGBOND