跳至主要內容

MySQL

GGBOND2025/5/21大约 18 分钟

一、基础知识

说一下你对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 条件的字段
  • 避免在频繁更新、低选择性列上建索引

怎么知道查询有没有走索引？

  • EXPLAIN命令：在SQL语句前添加EXPLAIN，查看执行计划
    • type列：ref、range、index、all（all表示全表扫描，未使用索引）
    • key列：显示实际使用的索引
    • possible_keys列：可能使用的索引

索引什么时候会失效？

常见的索引失效场景：

  1. 违反最左前缀原则：联合索引未使用最左列
  2. 使用了函数或计算：WHERE条件中对索引列进行函数操作
  3. 类型转换：索引列与查询值类型不匹配
  4. 使用!=、<>、NOT IN：否定条件可能导致全表扫描
  5. 使用OR连接：OR两边至少有一个列没有索引
  6. LIKE以%开头：模糊查询%开头无法使用索引
  7. 查询条件包含IS NULL/IS NOT NULL：可能导致索引失效
  8. 索引列参与运算：如WHERE a+1=10

大量数据应该怎么样查询？

处理大量数据查询的优化策略：

  1. 分页查询：使用LIMIT和合理的ORDER BY，避免OFFSET过大
  2. 索引优化：为查询条件、排序字段建立合适索引
  3. 分表分库：按时间、地域等维度拆分数据
  4. 读写分离：主库写，从库读，分担查询压力
  5. 缓存优化：使用Redis等缓存热点数据
  6. 减少返回字段：只查询需要的字段，避免SELECT *
  7. 批量处理：将大量查询拆分为小批量操作
  8. 使用覆盖索引：查询只需要索引列，无需回表

覆盖索引是指一个索引包含了查询所需的所有列，查询可以直接从索引中获取所有数据，无需再回表查询数据行。

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） 形式存储在表空间中，主键索引存整行数据，二级索引存主键值。

MySQL为啥不推荐用text字段？

不推荐使用的原因

  1. 检索慢：Text字段存储在溢出页，需要额外磁盘I/O
  2. 占空间大：占用大量存储空间，影响缓存效率
  3. 索引支持有限：只能索引前N个字符（N通过索引前缀长度设置），不能用于ORDER BY、GROUP BY
CREATE INDEX idx_content ON articles(content(100));
  1. 影响查询性能：可能导致全表扫描，增加网络传输开销
  2. 维护成本高：表优化、备份和恢复速度更慢

替代方案

  1. 使用VARCHAR替代：文本长度在65535字节以内时优先使用
  2. 拆分表：将Text字段单独存储到关联表中
  3. 存储文件路径：超大数据存储在文件系统，数据库只保存路径
  4. 使用文档数据库：非结构化文本考虑使用MongoDB等

适用场景

Text字段并非完全不能用，在以下场景可以考虑：

  • 存储确实需要的长文本数据
  • 数据查询频率低
  • 不需要在Text字段上建立复杂索引
  • 数据量不大

为什么MySQL用B+树，MongoDB用B树?

数据库索引结构选择原因
MySQLB+树适合范围查询，叶子节点形成有序链表，查询性能稳定
MongoDBB树适合文档存储，数据直接存储在节点中，支持更快的单键查询

MySQL选择B+树的原因：

  • 范围查询高效：叶子节点通过指针连成有序链表，支持快速范围扫描
  • 查询性能稳定：所有查询都需要遍历到叶子节点，路径长度相同
  • 磁盘I/O优化：非叶子节点只存储索引，单个节点可容纳更多索引项，减少I/O次数
  • 适合关系型数据：关系型数据常涉及范围查询、排序等操作

MongoDB选择B树的原因：

  • 文档存储特点：文档数据较大，B树节点可以直接存储完整文档
  • 单键查询更快：无需像B+树那样必须遍历到叶子节点，在中间节点即可找到数据
  • 支持更多数据类型：B树对复杂数据类型的支持更灵活
  • 适合NoSQL场景：NoSQL更多是键值查询，较少复杂的范围查询

MySQL热点数据更新会带来哪些问题,如何优化?

核心问题：单行锁竞争

  • 行级锁竞争严重：大量请求同时更新同一行，线程排队，响应变慢
  • 事务等待与超时：更新同一行会互相等待，容易出现 lock wait timeout
  • 数据库吞吐下降：热点集中在一行，导致整体 QPS 被"单行锁"限制
  • 死锁风险增加：高并发下容易形成循环等待
  • 应用端响应变慢：大量线程阻塞等待数据库锁，导致服务雪崩风险

缓存加剧问题

  • 缓存频繁失效：热点数据频繁更新导致缓存不断失效
  • 缓存击穿：单个热点key更新导致缓存失效，大量并发请求击穿数据库
  • 回源压力剧增：缓存失效后大量请求直接访问数据库，进一步加重热点行压力
  • 恶性循环：数据库压力增大导致响应变慢，更多请求超时，系统雪崩风险提高

解决方法：

  • 分库分表：将热点数据分散到多个表或库，降低单行锁竞争
  • 缓存优化：使用缓存预热、多级缓存、本地缓存减少数据库访问
  • 消息队列：将更新操作异步化，通过消息队列削峰填谷
  • 乐观锁：使用版本号替代悲观锁，减少锁竞争
  • 热点分离：将热点数据单独处理，使用专门的存储方案
  • 读写分离：将读操作分流到从库，减轻主库压力

如果要存IP地址，用什么数据类型比较好?

推荐数据类型：使用INT UNSIGNED（无符号整数）存储IP地址是最佳选择。

原因分析：

  • 节省空间：IPv4地址用字符串存储需要15字节，转为INT UNSIGNED只需4字节，节省73%的存储空间
  • 查询效率高：整数类型的比较和索引效率远高于字符串类型，支持范围查询
  • 便于计算：可以直接进行IP段的计算和比较，支持CIDR查询

实现方法：

  • 存储：使用INET_ATON()函数将IP字符串转换为整数
  • 读取：使用INET_NTOA()函数将整数转换为IP字符串
-- 存储IP地址
INSERT INTO users (ip_address) VALUES (INET_ATON('192.168.1.1'));

-- 查询IP地址
SELECT INET_NTOA(ip_address) FROM users WHERE id = 1;

-- 范围查询（查询192.168.1.0/24网段）
SELECT * FROM users WHERE ip_address BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');

IPv6支持：对于IPv6地址，可以使用VARBINARY(16)或BINARY(16)类型存储，配合INET6_ATON()和INET6_NTOA()函数进行转换。

假设数据库成为了性能瓶颈点，动态数据查询如何提升效率？

优化查询语句：

  • 使用索引：为查询条件、排序字段创建合适的索引
  • 避免SELECT *：只查询必要的字段
  • 优化JOIN操作：减少JOIN表数量，使用合适的JOIN顺序
  • 避免子查询：尽量用JOIN替代子查询
  • 使用LIMIT：控制返回行数，避免一次性返回大量数据

数据库层面优化：

  • 读写分离：将读请求分流到从库，降低主库压力
  • 分库分表：将大表拆分为多个小表，分散查询压力
  • 分区表：根据时间、范围等维度对表进行分区
  • 物化视图：将复杂查询结果预计算并存储

缓存优化：

  • 应用层缓存：使用Redis、Memcached等缓存热点数据
  • 查询缓存：启用MySQL查询缓存（注意：MySQL 8.0已移除）
  • 结果缓存：缓存频繁查询的结果，定期刷新

架构优化：

  • 引入搜索引擎：使用Elasticsearch等搜索引擎处理复杂查询
  • 数据冗余：在不影响一致性的前提下，适当冗余数据，减少JOIN操作
  • 异步处理：将复杂查询异步化，通过消息队列处理

硬件优化：

  • 增加内存：扩大InnoDB Buffer Pool，减少磁盘I/O
  • 使用SSD：提升磁盘I/O性能
  • 优化服务器配置：调整MySQL参数，如innodb_buffer_pool_size、innodb_log_file_size等

如何在生产环境不停服情况下进行数据迁移从原来的16张表迁移到64张表中?

迁移策略：采用双写+灰度切换的方案，确保数据一致性和服务可用性。

具体步骤：

  1. 准备阶段：设计新的64表结构，开发数据迁移工具，搭建新表的测试环境
  2. 全量数据迁移：将16张表的历史数据全量迁移到64张表中，验证数据一致性
  3. 双写阶段：修改应用代码，同时向旧表和新表写入数据，开启增量数据同步
  4. 灰度切换阶段：逐步将读请求从旧表切换到新表，监控新表的性能和稳定性
  5. 完全切换阶段：将所有读请求切换到新表，停止向旧表写入数据，关闭双写机制
  6. 清理阶段：保留旧表一段时间，确认无问题后，删除旧表

二、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 © 2026 GGBOND