JAVA面试：mysql数据库

介绍一下MySQL架构

MySQL架构

连接层（Connection Layer）

提供客户端与服务器的连接服务，负责建立、管理和终止客户端与数据库服务器之间的连接请求。
负责授权认证，确保只有合法的用户才能访问数据库资源。
实现安全协议，包括SSL加密连接等功能。

服务层（Service Layer）

包括SQL接口、解析器、优化器和执行器等多个组件。
SQL接口接收客户端发送过来的SQL语句。
解析器对SQL语句进行词法分析和语法分析，理解SQL语句的意图。
优化器基于数据库表的统计信息，选择最优的执行计划，包括索引选择、表扫描顺序等。
执行器按照优化器产生的执行计划执行SQL语句，获取结果集，并返回给客户端。

存储引擎层（Storage Engine Layer）

存储引擎是MySQL的核心组件之一，负责数据的存储和读取操作。
MySQL支持多种存储引擎，如InnoDB、MyISAM、MEMORY等，每种引擎有不同的特性，如事务处理、行级锁定、全文索引等。
各存储引擎独立管理其底层的数据存储结构，并通过统一的API与服务层进行交互。

系统文件层（File System Layer）

存储引擎将数据以物理形式持久化到磁盘上的文件系统中。
数据文件、索引文件以及其他类型的文件（如日志文件）都位于此层。
例如，InnoDB存储引擎有自己的表空间文件、重做日志文件和系统表空间等。

介绍一下InnoDB存储引擎的结构

InnoDB存储引擎的结构

InnoDB 存储引擎是 MySQL 中一个非常重要的事务型存储引擎，其设计目标是提供高性能的同时保证事务处理的ACID特性。InnoDB 引擎的结构可以从内存结构和磁盘结构两个层面进行详细说明：

内存结构：

Buffer Pool（缓冲池）

缓冲池是InnoDB的核心组件之一，用于缓存数据页和索引页，减少磁盘I/O操作。当执行SQL查询时，首先尝试从缓冲池中获取所需页面，而不是直接从磁盘读取，显著提升了性能。

Change Buffer（变更缓冲区）

在非唯一二级索引修改操作中，对于尚未加载到缓冲池中的数据页，对应的更改会被记录在Change Buffer中，延迟对相应索引页的物理更新，等到这些页被访问时再合并更改。

Adaptive Hash Index（自适应哈希索引）

InnoDB能够根据查询模式动态地为经常使用的索引构建哈希索引，从而加速某些查询操作。AHI有助于快速定位缓冲池中的数据页。

Log Buffer（重做日志缓冲区）

InnoDB使用重做日志系统来保证事务的持久性。日志缓冲区存储着尚未写入磁盘的重做日志记录，包括对数据库所做的更改信息。

磁盘结构：

Tablespaces（表空间）

表空间是InnoDB逻辑上的最高层容器，可以包含多个数据文件（ibdata*或单独的xxx.ibd文件）。启用innodb_file_per_table选项后，每个表的数据和索引都会存储在一个独立的.ibd文件中。

Data Dictionary（数据字典）

数据字典在磁盘上保存有关InnoDB表及其索引的信息，是元数据存储的地方。

Doublewrite Buffer（双写缓冲区）

双写缓冲区用来防止突发故障导致的数据页损坏。新写入的数据首先被复制到这个区域，然后再写入对应的数据文件，确保即使在崩溃恢复期间也能找回未完全写入的数据。

Redo Log（重做日志）

重做日志文件用于记录事务对数据库所做的更改，以便在系统崩溃时能够通过回放日志恢复未提交的事务，实现事务的持久性。

Undo Logs（回滚日志）

回滚日志用于撤销事务操作，以支持事务的原子性和一致性。在事务回滚或并发事务间的多版本控制（MVCC）中发挥作用。

介绍一下MySQL索引结构

MySQL索引结构

MySQL索引的底层结构和运行原理是数据库性能优化的核心，其核心数据结构为B+树，结合存储引擎特性实现高效的数据检索。以下从底层结构、运行原理、存储引擎差异及优化策略等方面详细解析：

一、索引底层数据结构：B+树

B+树与B树对比

B+树特性：
- 多路平衡搜索树：非叶子节点仅存储索引（键值），不存储数据，每个节点可容纳更多键值，减少树高度。
- 叶子节点存储数据：所有数据记录存储在叶子节点，且通过双向链表连接，支持高效范围查询和排序。
- 非叶子节点结构：格式为 (n, A0, K1, A1, ..., Kn, An)，其中Ki为键值，Ai为子节点指针。
B树特性：
- 所有节点存储键值+数据，树高度较高，适合内存数据库；B+树更适合磁盘存储，因为非叶子节点不占数据空间。

B+树优势

磁盘I/O优化：3层B+树可支撑千万级数据，每次查询最多3次I/O。
范围查询高效：通过叶子节点链表直接遍历区间数据。
稳定性：所有查询路径长度相同，时间复杂度为 O(log_d N)。

二、存储引擎的索引实现

InnoDB（聚簇索引）

主键索引（聚簇索引）：
- 数据文件本身就是B+树结构，叶子节点存储完整数据行，键值为主键。
- 主键设计建议：使用自增整型，避免随机主键导致页分裂。
二级索引（非聚簇索引）：
- 叶子节点存储主键值而非数据地址，查询需回表（先查二级索引，再查聚簇索引）。
- 示例：若索引为 (name, age)，查询 name='John' 时，先定位主键，再回表获取数据。

MyISAM（非聚簇索引）

索引文件与数据文件分离，叶子节点存储数据记录地址。
主键与二级索引结构相同，仅键值唯一性约束不同。

三、索引运行原理

查询过程
从根节点开始：通过二分查找确定子节点指针。
逐层遍历：直到到达叶子节点。
数据获取：
- 聚簇索引：直接返回数据。
- 二级索引：获取主键后回表查询。
范围查询

利用叶子节点链表顺序遍历，如 WHERE age BETWEEN 20 AND 30。

联合索引

最左前缀原则：索引 (a, b, c) 仅支持 a、(a,b)、(a,b,c) 的查询。
覆盖索引：若查询字段均在索引中，无需回表（如 SELECT a, b）。

四、索引失效场景与优化

失效场景

隐式类型转换：如 WHERE username = 123（字符串与数字比较）。
函数操作索引列：如 WHERE UPPER(name) = 'JOHN'。
模糊查询前缀通配符：LIKE '%abc' 无法使用索引。

优化策略

控制索引数量：避免冗余索引，平衡查询与写入性能。
前缀索引：对长文本字段（如URL）创建前缀索引。
定期维护：分析表统计信息，优化索引分布。

五、其他索引类型

哈希索引：
- 适用于等值查询（如内存引擎Memory），但不支持范围查询。
全文索引：
- 针对文本字段分词检索（如InnoDB 5.6+）。

六、总结

B+树是MySQL索引的核心结构，通过多路平衡和叶子链表设计实现高效检索。
InnoDB的聚簇索引优化了主键查询，但二级索引需回表；MyISAM适合静态数据的非聚簇存储。
合理设计索引需结合查询模式、数据更新频率及存储引擎特性，避免过度索引或设计不当导致性能下降。

为什么不用hash B树二叉树而要用B+树

B+树

在MySQL中，B+树被广泛用作索引结构，而不是单纯的B树或二叉树。这是因为B+树在处理数据库查询时具有显著的优势，特别是在磁盘I/O和大规模数据集方面。以下是详细的分析：

1. B+树 vs B树

B树

结构：
- 每个节点包含多个键值对和指向子节点的指针。
- 叶子节点和非叶子节点都存储键值对。
特点：
- 高度平衡，所有叶子节点都在同一层。
- 支持范围查询和有序性。
缺点：
- 叶子节点不连续，无法利用数据局部性进行批量读取。
- 每次访问数据都需要从根节点到叶子节点逐层查找。

B+树

结构：
- 所有叶子节点都在同一层，并且通过指针链表相连。
- 非叶子节点仅存储键值对和指向子节点的指针，不存储实际数据。
- 所有实际数据存储在叶子节点中。
特点：
- 高度平衡，所有叶子节点都在同一层。
- 支持范围查询和有序性。
- 叶子节点连续存储，提高数据局部性和I/O效率。
优点：
- 数据局部性：叶子节点连续存储，一次磁盘读取可以加载多个相关的数据页，减少I/O操作次数。
- 高效的数据访问：支持顺序访问和批量读取，适合数据库中的大量数据检索。
- 自平衡特性：插入和删除操作后，通过分裂、合并和旋转等操作保持树的高度最小化，确保查询性能稳定。
- 高效的写操作：插入和删除操作主要发生在叶子节点附近，对其他部分的影响较小，减少了全局结构调整的需求。

2. B+树 vs 二叉树

二叉树

结构：
- 每个节点最多有两个子节点（左子节点和右子节点）。
- 节点同时存储键值对和指向子节点的指针。
特点：
- 不一定高度平衡，可能导致某些路径过长。
- 支持精确匹配和简单的范围查询。
缺点：
- 高度不平衡时，查询性能下降，最坏情况下退化为线性时间复杂度 $O(n)$。
- 数据不连续存储，不利于批量读取和顺序访问。
- 插入和删除操作可能导致树的高度变化较大，需要频繁调整以保持平衡。

B+树

结构：
- 每个节点可以包含多个键值对和指向子节点的指针。
- 叶子节点通过指针链表相连，形成一个有序的列表。
特点：
- 高度平衡，所有叶子节点都在同一层。
- 支持范围查询和有序性。
- 叶子节点连续存储，提高数据局部性和I/O效率。
优点：
- 数据局部性：叶子节点连续存储，一次磁盘读取可以加载多个相关的数据页，减少I/O操作次数。
- 高效的数据访问：支持顺序访问和批量读取，适合数据库中的大量数据检索。
- 自平衡特性：插入和删除操作后，通过分裂、合并和旋转等操作保持树的高度最小化，确保查询性能稳定。
- 高效的写操作：插入和删除操作主要发生在叶子节点附近，对其他部分的影响较小，减少了全局结构调整的需求。

3. 底层原理分析

磁盘I/O效率

B+树：
- 连续存储：叶子节点连续存储，一次磁盘读取可以加载多个相关的数据页，减少I/O操作次数。
- 批量读取：适合顺序访问和批量读取，提高了数据检索的效率。
二叉树：
- 随机访问：每个节点只包含两个子节点，数据不连续存储，每次访问数据都需要从根节点到叶子节点逐层查找，增加了I/O操作次数。
B树：
- 非叶子节点存储数据：虽然也具有一定的平衡性，但非叶子节点存储数据会导致不必要的I/O操作，不如B+树高效。

查询性能

B+树：
- 有序性：所有叶子节点按顺序排列，支持高效的范围查询和排序操作。
- 快速定位：通过有序性可以快速找到范围的起始点，并沿着叶子节点链表遍历整个范围。
二叉树：
- 不平衡风险：高高度不平衡时，查询性能下降，最坏情况下退化为线性时间复杂度 $O(n)$。
B树：
- 有序性：支持有序性，但非叶子节点存储数据导致额外的I/O开销。

写操作效率

B+树：
- 局部调整：插入和删除操作主要发生在叶子节点附近，对其他部分的影响较小，减少了全局结构调整的需求。
二叉树：
- 频繁调整：插入和删除操作可能导致树的高度变化较大，需要频繁调整以保持平衡。
B树：
- 局部调整：虽然也能保持平衡，但非叶子节点存储数据导致额外的I/O开销。

4. 总结

数据局部性：B+树的叶子节点连续存储，提高了数据的局部性，适合顺序访问和批量读取，减少了磁盘I/O操作次数。
高效的数据访问：B+树支持高效的范围查询和排序操作，适用于数据库中的大量数据检索。
自平衡特性：B+树在插入和删除操作后能够自动调整以保持平衡，确保查询性能稳定。
高效的写操作：B+树的插入和删除操作主要发生在叶子节点附近，对其他部分的影响较小，减少了全局结构调整的需求。

相比之下，二叉树和B树在处理大规模数据集时存在以下问题：

二叉树：容易出现不平衡，导致查询性能下降；数据不连续存储，增加了I/O操作次数。
B树：虽然具有平衡性，但非叶子节点存储数据导致额外的I/O开销，不如B+树高效。

因此，MySQL选择使用B+树作为索引结构，以优化磁盘I/O效率、查询性能和写操作效率。

MySQL遇到过的性能问题，做了哪些调优

MySQL调优

MySQL 性能问题与调优实践总结
在实际开发与运维中，MySQL 性能问题常表现为慢查询、高负载、锁竞争、I/O 瓶颈等。以下结合常见问题场景与调优手段，分维度梳理解决方案，并附关键实践案例。

一、索引优化

索引设计原则
- 覆盖索引：避免回表查询，例如查询仅需索引字段时，直接通过索引返回结果。
- 复合索引：遵循最左前缀匹配原则，如 (a, b, c) 索引适用于 WHERE a=1 AND b=2，但对 b=2 失效。
- 避免冗余索引：删除重复索引（如已有 (a,b) 时无需单独建 (a)）。
索引失效场景与优化
- 隐式类型转换：如 WHERE user_id = '100'（user_id 为 INT）导致索引失效，需统一类型。
- 函数操作列：WHERE YEAR(create_time)=2023 改为范围查询 create_time BETWEEN '2023-01-01' AND '2023-12-31'。
- 模糊查询前导%：LIKE '%keyword' 无法使用索引，需业务逻辑优化或全文索引。

二、SQL 查询优化

减少全表扫描
- 避免 SELECT *，仅查询必要字段。
- 使用 EXPLAIN 分析执行计划，关注 type（理想值为 ref/range）和 rows（扫描行数）。
分页与 JOIN 优化
- 深分页优化：将 LIMIT 100000, 10 改为基于游标的分页（如记录上一页最后一条 ID）。
- JOIN 策略：优先使用 INNER JOIN，确保关联字段有索引，小表驱动大表。
复杂查询拆解
- 将多条件查询拆分为多个简单查询，或使用临时表存储中间结果。

三、数据库配置调优

关键参数调整
- innodb_buffer_pool_size：设置为物理内存的 70%~80%（如 16GB 内存设为 12G）。
- 连接与日志参数：
  - max_connections：根据并发量调整（如设为 500）。
  - innodb_log_file_size：增大日志文件大小（如 1G）提升事务写入效率。
查询缓存与锁策略
- 禁用查询缓存（MySQL 5.7+ 默认关闭）：减少锁竞争开销。
- 优化事务：避免长事务，减少锁等待时间。

四、架构与硬件优化

分库分表与读写分离
- 水平分表：按哈希或范围拆分（如 user_id % 4）。
- 主从复制：主库处理写操作，从库处理读操作，通过 ProxySQL 实现路由。
硬件升级
- 使用 SSD 替代 HDD 提升 I/O 性能。
- 增加内存容量以扩大缓冲池。

五、监控与诊断工具

慢查询日志分析
- 开启慢查询（long_query_time=1），使用 mysqldumpslow 定位高频慢 SQL。
性能监控
- 通过 SHOW PROCESSLIST 检查阻塞查询。
- 利用 Performance Schema 监控锁等待与 I/O 瓶颈。

六、实战案例
案例1：订单表查询性能低下

问题：SELECT * FROM orders WHERE user_id=100 全表扫描。
优化：
- 创建覆盖索引 (user_id, order_date)。
- 改为 SELECT order_id, user_id FROM orders WHERE user_id=100。
效果：查询时间从 500ms 降至 10ms。
案例2：分页加载卡顿
问题：LIMIT 100000, 10 导致扫描百万行。
优化：改用游标分页 WHERE id > last_id LIMIT 10。
效果：CPU 使用率下降 40%，吞吐量提升 3 倍。

总结
MySQL 调优需结合索引设计、SQL 优化、参数配置、架构扩展四层策略，核心目标是减少磁盘 I/O 和锁竞争。建议定期通过慢查询日志与监控工具定位瓶颈，逐步迭代优化。对于高并发场景，可结合缓存（如 Redis）与异步处理进一步提升性能。

讲讲mysql mvcc

MySQL mvcc

MySQL MVCC 详解：原理、实现与应用场景
一、MVCC 的核心概念与作用
MVCC（Multi-Version Concurrency Control，多版本并发控制）是数据库管理系统中用于提高并发性能的核心技术，通过维护数据的多个版本，实现非阻塞读写，解决传统锁机制下的读写冲突问题。其核心目标是：

提升并发性能：读操作无需等待写操作，写操作也不阻塞读操作。
隔离性保障：在读已提交（RC）和可重复读（RR）隔离级别下，避免脏读、不可重复读等问题。

二、MVCC 的实现原理
MVCC 的实现依赖于以下三大核心组件：

1. 隐藏字段

InnoDB 为每行记录额外存储三个隐藏字段（若表无主键，还会生成隐藏主键 DB_ROW_ID）：

DB_TRX_ID（6字节）：记录最后一次修改该行的事务 ID。
DB_ROLL_PTR（7字节）：回滚指针，指向该行的 undo log 版本链。
DB_ROW_ID（6字节）：单调递增的行标识符，仅在无主键时生成。

2. Undo Log 与版本链

Undo Log：记录数据修改前的旧值，用于回滚和构建多版本数据。
版本链：通过 DB_ROLL_PTR 将多次修改的行记录串联成链表，形成历史版本集合。例如：
```
事务A → 事务B → 事务C
```
每次更新生成新版本，旧版本通过 undo log 保存。

3. Read View（一致性视图）

Read View 是事务执行快照读时生成的“数据版本筛选规则”，包含以下关键字段：

m_ids：生成 Read View 时活跃（未提交）的事务 ID 列表。
min_trx_id：m_ids 中最小的事务 ID。
max_trxish_id：系统下一个待分配的事务 ID。
creator_trx_id：当前事务的 ID。
可见性判断规则：

若版本的 trx_id == creator_trx_id：可见（当前事务自身修改）。
若 trx_id < min_trx_id：可见（事务已提交）。
若 trx_id >= max_trx_id：不可见（事务在 Read View 生成后开启）。
若 min_trx_id ≤ trx_id < max_trx_id：
- 若 trx_id 在 m_ids 中：不可见（事务未提交）。
- 否则：可见（事务已提交）。

三、MVCC 与事务隔离级别的关联
MVCC 在读已提交（RC）和可重复读（RR）隔离级别下生效，但实现机制不同：

1. 读已提交（RC）

Read View 生成时机：每次查询生成新的 Read View。
效果：可见其他事务已提交的最新修改，解决脏读，但可能出现不可重复读。

2. 可重复读（RR）

Read View 生成时机：事务首次查询时生成，后续复用同一视图。
效果：保证事务内多次查询结果一致，解决不可重复读。通过Next-Key Lock（记录锁+间隙锁）进一步抑制幻读。

四、当前读与快照读
MVCC 支持两种读操作：

快照读（Snapshot Read）：
- 普通 SELECT 语句，基于 Read View 和 Undo Log 返回历史版本数据。
当前读（Current Read）：
- 带锁的读操作（如 SELECT FOR UPDATE、INSERT/UPDATE/DELETE），读取最新数据并加锁，用于写操作。

五、MVCC 的优缺点

优点：
- 高并发性能：读写操作无锁冲突。
- 隔离性保障：解决脏读、不可重复读。
缺点：
- 存储开销：需维护多版本数据，Undo Log 可能占用大量空间。
- 清理成本：Purge 线程需定期清理过期版本，可能影响性能。

六、MVCC 的典型应用场景

读多写少的 OLTP 系统：如电商商品浏览与库存更新。
高并发查询场景：报表生成、数据分析等非实时操作。

七、MVCC 无法解决的问题

幻读：需结合间隙锁（Gap Lock）和 Next-Key Lock 解决。
写写冲突：仍需依赖行锁或间隙锁实现互斥。

八、总结
MySQL 的 MVCC 通过隐藏字段、Undo Log 版本链和Read View 可见性规则，实现了高效的非阻塞并发控制。其设计在保障数据一致性的同时，显著提升了数据库吞吐量，成为现代高并发系统的核心技术之一。理解 MVCC 的原理与限制，是优化数据库性能与事务设计的关键。

mysql 可重复读怎么怎么保证的不会幻读(mvcc + 间隙锁)

幻读

幻读(不可重复读)在事务隔离级别【读未提交和读已提交】隔离级别下发生

读未提交

允许脏读、不可重复读以及幻读发生。在这个级别下，事务可以读取到其他未提交事务修改的数据，而且随着其他事务的插入操作，查询结果可能会发生变化，表现为幻读现象。

读已提交

只能读取到已经提交的数据，解决了脏读问题
出现幻读，A事务可查询到B事务commit后记录，两次查询结果发生变化。
A事务查询sql for update 显式加锁（行锁），B事务更新锁定记录，需等待A事务将锁释放，
A事务查询sql for update 对一般索引范围查询（不是间隙锁），B事务可插入记录commit后，A事务可查询到新提交记录，出现幻读

可重复读(mvcc + 间隙锁)

A事务不会读到B事务已提交的记录
在可重复读隔离级别下，UPDATE/DELETE的索引范围条件会触发间隙锁以保护事务的完整性，而INSERT操作本身不涉及间隙锁，但可能因其他事务的间隙锁被阻塞。

手动加间隙锁，范围查询时SELECT ... FOR UPDATE （X锁，排他锁）或 SELECT ... LOCK IN SHARE MODE (S锁，共享锁)

A事务查询sql for update 显式加锁，若为等值查询，加行锁，B事务等待锁释放
A事务查询sql for update 显式加锁，一般索引（字段a）（非唯一）范围查询（a>=1），加间隙锁，锁定字段a值范围（1至无穷大），B事务针对字段a（大于等于1范围的值）的插入、更新的写操作都需要等待A事务间隙锁锁释放

联合索引为什么需要最佳左前缀匹配

联合索引

主要原因在于索引的底层实现机制以及数据检索的效率优化：

索引组织结构：联合索引在MySQL的InnoDB存储引擎中是以B+树的形式存储的。在B+树中，一个节点包含多个键值对，其中键是由联合索引定义的多个字段组成的有序数组。这些键是按照联合索引定义的字段顺序依次排序的。节点存储联合索引的组合键值，叶子结点存储具体数据记录、主键等，
索引查找路径：
查询时，MySQL会首先使用联合索引的第一个字段来进行查找。由于B+树的有序性，基于第一个字段的值就能快速定位到对应的节点或叶子节点。
若查询条件包含联合索引的第二个字段，则可以根据第一个字段找到的范围继续沿着B+树向下查找第二个字段对应的值。
以此类推，对于每一个索引字段，都可以依据前面字段的值快速定位到下一个字段所在的子树，最终查询到记录、主键。
范围查询的影响：
当查询条件包含范围查询（如>、<、BETWEEN等）时，索引会停止在范围查询字段之后的索引字段上生效，即范围查询字段右边的所有字段都无法利用索引进行过滤。
这是因为范围查询打破了有序性，使得MySQL无法预知后面字段的具体分布情况，因而无法继续利用索引进行高效的检索。

3.1. 版本优化与例外情况
MySQL 5.6+ 引入了索引下推（ICP, Index Condition Pushdown），优化了范围查询后索引的使用：
场景：若范围查询后仍有等值条件，优化器可能将条件推送到索引层过滤数据。
例如，a=1 AND b>6 AND c=3 可能拆分为两部分：a=1 AND b=6 AND c=3 与 a=1 AND b>6 AND c=3，从而部分利用索引。
执行计划验证：通过 EXPLAIN 的 key_len 和 Extra 字段（如 Using index condition）可观察优化效果。
3.2. 特殊范围查询的处理
闭区间范围（>=、<=）：部分场景下可继续利用索引，因数据在B+树中仍保持有序性。
LIKE 查询：LIKE '前缀%' 可用索引，而 LIKE '%中缀%' 失效。
3.3. 实际应用建议
索引设计：
将范围查询字段放在联合索引的右侧（如 (a, b) 中 b 是范围查询字段）。
优先使用闭区间范围（BETWEEN、>=/<=）。
查询优化：
避免对索引列进行计算或函数操作（如 WHERE age+1=30）。
使用覆盖索引减少回表（查询字段均在索引中）。

最优化效果：
最左前缀匹配原则允许MySQL根据查询条件尽可能多地使用索引字段，从而最大化地利用索引加快查询速度，减少不必要的全表扫描或索引扫描。

综上所述，最左前缀匹配原则确保了MySQL能够在联合索引中高效地查找数据，并且考虑到索引结构的局限性和范围查询的特殊性，这种设计有效地平衡了索引大小与查询效率之间的关系。

数据库怎么实现乐观锁、怎么实现悲观锁

乐观锁和悲观锁是数据库中用于并发控制的两种不同机制，它们各自有不同的实现方式和适用场景。下面是对这两种锁的具体分析。

悲观锁

悲观锁（Pessimistic Locking） 假设最坏的情况会发生，即在同一时刻可能会有多个事务试图修改同一数据。因此，在一个事务开始处理某条记录时，就立即对该记录加锁，防止其他事务对其进行修改，直到该事务完成并释放锁。

实现方式

SELECT ... FOR UPDATE

这是最常见的悲观锁实现方式。通过 SELECT ... FOR UPDATE 语句，可以锁定查询返回的行，直到当前事务提交或回滚。

START TRANSACTION;

SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

-- 在这里进行数据更新操作
UPDATE table_name SET column_name = 'new_value' WHERE id = 1;

COMMIT;

SELECT ... LOCK IN SHARE MODE
- 如果只需要读取数据而不需要修改，可以使用 LOCK IN SHARE MODE 来获取共享锁。这样其他事务可以继续读取该数据，但不能写入。
```
START TRANSACTION;

SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;

-- 只读操作
...

COMMIT;
```

缺点

降低并发性能：由于每次操作都需要加锁，可能导致大量等待时间，尤其是在高并发环境下。
死锁风险：多个事务相互等待对方释放锁，容易导致死锁情况发生。

乐观锁

乐观锁（Optimistic Locking） 假设冲突很少发生，因此不会在事务一开始时对数据加锁。相反，它会在提交事务时检查数据是否被其他事务修改过。如果发现数据已被修改，则拒绝当前事务的提交，并可以选择重试或其他处理方式。

实现方式

版本号机制

为每个需要保护的数据行添加一个版本号字段。每次更新数据时，版本号都会递增。在提交事务前，会检查版本号是否与最初读取时相同。如果不相同，说明数据已经被其他事务修改过，当前事务应该放弃或重试。

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    data VARCHAR(255),
    version INT DEFAULT 0
);

-- 读取数据并记录版本号
SELECT id, data, version FROM table_name WHERE id = 1;

-- 更新数据时检查版本号
UPDATE table_name 
SET data = 'new_value', version = version + 1 
WHERE id = 1 AND version = 初始版本号;

时间戳机制

类似于版本号机制，可以使用时间戳来代替版本号。每次更新数据时，时间戳也会更新。提交事务前，检查时间戳是否与初始读取时相同。

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    data VARCHAR(255),
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 读取数据并记录时间戳
SELECT id, data, last_modified FROM table_name WHERE id = 1;

-- 更新数据时检查时间戳
UPDATE table_name 
SET data = 'new_value', last_modified = NOW() 
WHERE id = 1 AND last_modified = 初始时间戳;

优点

提高并发性能：由于不加锁，多个事务可以同时访问和修改数据，减少了等待时间。
减少死锁：没有显式的锁竞争，降低了死锁的风险。

缺点

不适合频繁冲突的场景：如果数据冲突频繁发生，乐观锁会导致大量的失败和重试，反而可能降低性能。
复杂性：实现相对复杂，需要额外的字段和逻辑来管理版本号或时间戳。

总结

悲观锁适用于冲突频繁、数据一致性要求高的场景，如银行转账等金融交易。
乐观锁适用于冲突较少、并发较高的场景，如博客文章评论系统等。

MySQL遇到过什么问题没？

查询时多表关联字段的字符集不一致（utf8、utf8mb4），导致查询变慢（索引无法使用），mysql实例CPU资源飙升100%。
字符集不一致时需要使用 convert 函数进行转换，从而导致索引失效。

数据库中插入100万条数据，怎么能快速插入

快速插入

在数据库中快速插入100万条数据需要结合多种优化策略，以下是基于不同场景和技术的综合方案：

一、通用优化策略

批量插入代替逐条插入
- 单条INSERT合并：将多条数据合并为一条INSERT INTO ... VALUES (...), (...), ...语句，减少网络往返和事务提交次数，每批建议500-1000条。
- 事务控制：关闭自动提交（SET autocommit=0），每批插入后手动提交事务，显著降低I/O开销。
禁用非必要约束与索引
- 插入前删除非主键索引，完成后再重建，避免插入时维护索引的开销。
- 暂时禁用外键约束检查，减少关联表的校验时间。
调整数据库配置
- 增大innodb_buffer_pool_size（InnoDB缓冲池）以减少磁盘I/O。
- 提升innodb_log_file_size和innodb_log_buffer_size优化日志写入。

二、具体技术方案

使用LOAD DATA INFILE（推荐）
- 适用场景：数据已存储在文件（如CSV）中。
- 优势：单条命令导入，速度可达每秒数十万条。
- 步骤：
  1. 数据写入CSV文件（字段以特定符号分隔）。
  2. 执行SQL：
```
LOAD DATA INFILE '/path/to/file.csv' 
INTO TABLE target_table 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
```
  3. 确保MySQL配置允许本地文件导入（local_infile=1）。
- 优势：避免直接操作磁盘表的开销，适合复杂数据生成场景。

编程语言批量操作

Java（JDBC）：

PreparedStatement pstmt = conn.prepareStatement("INSERT ...");
conn.setAutoCommit(false);
for (int i=0; i<data.size(); i++) {
    pstmt.setObject(...);
    pstmt.addBatch();
    if (i % 1000 == 0) { pstmt.executeBatch(); pstmt.clearBatch(); }
}
pstmt.executeBatch();
conn.commit();

优化参数：URL添加rewriteBatchedStatements=true启用MySQL批处理优化。

Python（PyMySQL）：
```
with connection.cursor() as cursor:
    cursor.executemany("INSERT ...", data_batch)
connection.commit()
```
- 建议：分批提交（每千条一次），结合LOAD DATA LOCAL INFILE进一步加速。

三、高级优化技巧

分库分表与并行插入
- 将数据拆分到多个表或分片，通过多线程并发插入，提升吞吐量。
预编译与参数化查询
- 使用PreparedStatement或参数化查询避免SQL解析重复开销。
硬件与存储优化
- 使用SSD替代机械硬盘，提升I/O性能。
- 增加服务器内存，减少磁盘交换。

四、性能对比参考

单条插入：约1.2万条/秒（MySQL）。
批量插入（每批1000条）：24万条/秒（MySQL）。
LOAD DATA INFILE：可达每秒数十万条，具体取决于文件大小和I/O性能。

mysql change buffer

change buffer

InnoDB存储引擎提供了Change Buffer（变更缓冲区）功能，用于提高对非唯一二级索引的写入性能。在高并发的写入场景下，尤其是大量插入、更新操作发生在非聚簇索引页不在内存（Buffer Pool）中的情况下，Change Buffer能显著降低磁盘I/O的压力。

Change Buffer的工作原理如下：

非唯一二级索引的更新：当对非唯一二级索引进行插入或删除操作时，如果目标页不在Buffer Pool中，InnoDB会选择将这个更改暂时缓存到Change Buffer中，而不是立即从磁盘读取数据页并更新。
合并操作：后续当相应索引页被读取到Buffer Pool中时，InnoDB会将Change Buffer中的变更与索引页进行合并，这个过程称为Merge操作。在合并过程中，InnoDB会执行必要的插入、删除或者页分裂操作。
提升写性能：通过这种方式，Change Buffer降低了大量的随机I/O，特别是在数据量大且索引页大部分不在内存中的情况下，极大地提升了写操作的性能。
需要注意的是，Change Buffer并不适用于唯一索引，因为对于唯一索引的插入操作，需要在插入前验证唯一性，而这一步骤必须立即访问索引页。此外，当数据库处于读密集型工作负载时，频繁的Merge操作可能会成为性能瓶颈，此时关闭Change Buffer可能会带来更好的性能表现。

mysql change buffer 为什么不支持唯一索引

MySQL的InnoDB存储引擎的Change Buffer（变更缓冲区）主要是用来提高对于非唯一二级索引更新操作的性能。之所以不支持唯一索引，是因为唯一索引在插入或更新时必须立即检查索引值的唯一性，这一检查操作要求数据页已经在内存中的Buffer Pool中，以便可以直接读取和验证。
以下是Change Buffer不支持唯一索引的原因：

唯一性检查：

对于唯一索引，任何插入或更新操作都需要先确认即将插入的值在索引中是否唯一，这就意味着必须立刻访问到对应的索引页，不可能将这类更新推迟到后续阶段处理。

同步性要求：

唯一索引的插入和更新操作需要确保事务的一致性，尤其是并发环境下的唯一性约束，这意味着在写入数据之前必须获取适当的锁并对唯一性进行验证，这是一个即时的过程，无法通过异步的方式完成。

避免冲突：

Change Buffer是为了减少随机I/O，通过将对尚未载入内存的数据页的更改暂存下来，等到以后需要访问这些页时再合并。而对于唯一索引，直接进行更改可以及时发现并解决潜在的唯一性冲突，避免后续合并时出现错误。
因此，由于上述原因，InnoDB的Change Buffer只适用于非唯一索引的更新，对于唯一索引，即使是二级索引，也不适用Change Buffer机制。

介绍一下mybatis

mybatis

MyBatis与MyBatis-Plus（简称MP）是Java生态中广泛应用的持久层框架，两者既有继承关系又有显著差异。以下从核心特性、功能对比、适用场景等维度进行详细介绍：

一、MyBatis：半自动ORM框架

核心特性

SQL与代码分离：通过XML或注解将SQL语句与Java代码解耦，便于维护。
动态SQL支持：提供、等标签，灵活拼接复杂查询条件。
结果集映射：自动将查询结果映射为Java对象，支持字段名与属性名的驼峰命名映射。
灵活性高：直接编写原生SQL，适合复杂查询和性能优化需求。
手动配置：需自行编写所有CRUD操作的SQL语句及Mapper接口。

典型使用场景

// Mapper接口
public interface UserMapper {
    User getUserById(Long id);
}
// XML配置（userMapper.xml）

    SELECT id, username FROM user WHERE id = #{id}

优缺点

优点：SQL可控性强，适合数据库深度优化；社区成熟，文档丰富。
缺点：开发效率低（需手动写SQL）；数据库移植性差（SQL依赖具体数据库语法）。

二、MyBatis-Plus：MyBatis的增强工具

核心特性
MyBatis-Plus在MyBatis基础上扩展了以下功能，完全兼容MyBatis原有功能：

自动化CRUD：
继承BaseMapper后，无需编写SQL即可实现单表增删改查。
```
public interface UserMapper extends BaseMapper {}
```

条件构造器：
通过QueryWrapper动态拼接查询条件，避免手动写SQL。

QueryWrapper wrapper = new QueryWrapper<>();
wrapper.eq("age", 20).like("name", "John");
List users = userMapper.selectList(wrapper);

分页插件：
物理分页实现，配置后即可使用selectPage方法。
代码生成器：
自动生成Entity、Mapper、Service等代码，减少样板化编码。
Lambda支持：
通过Lambda表达式避免字段写错问题。
```
wrapper.lambda().eq(User::getAge, 20);
```
全局拦截与安全：
阻断全表更新/删除操作，预防SQL注入。

扩展功能

主键策略：支持自增、UUID、分布式ID生成器（如Snowflake）。
AR模式：实体类继承Model后可直接操作数据库。
性能分析插件：输出SQL执行时间，辅助优化慢查询。

典型使用场景

// 插入数据
User user = new User();
user.setName("Tom");
userMapper.insert(user);
// 分页查询
Page page = new Page<>(1, 10);
IPage result = userMapper.selectPage(page, null);

优缺点

优点：开发效率高；内置企业级功能（分页、性能分析）；支持热加载XML。
缺点：过度依赖框架可能导致SQL可读性下降；复杂查询仍需手动编写SQL。

三、MyBatis与MyBatis-Plus对比

维度	MyBatis	MyBatis-Plus
开发效率	低（需手动写SQL）	高（自动生成CRUD）
SQL控制力	强	弱（简单场景自动处理）
适用场景	复杂SQL优化、数据库深度定制	标准化CRUD、快速开发
学习成本	低（仅需SQL和框架基础）	中等（需掌握新API）
侵入性	无	无（增强不改变原有配置）

四、如何选择？

选MyBatis：
项目需高度定制SQL（如复杂联表查询）、对SQL性能有极致要求。
选MyBatis-Plus：
标准化CRUD为主、需快速迭代（如管理系统开发）；希望减少重复代码。

五、总结

MyBatis是“工匠”，适合对SQL有极致控制需求的场景；
MyBatis-Plus是“自动化流水线”，通过封装提升效率，但需权衡灵活性。
两者可共存：用MP处理通用逻辑，MyBatis解决复杂查询，兼顾效率与性能。

mybatis与jpa的区别

MyBatis与JPA是Java领域两大主流持久层框架，它们在设计理念、使用场景和实现方式上有显著差异。以下从多个维度进行详细解析：

核心设计理念

MyBatis
属于半自动化ORM框架，核心是SQL中心。开发者需手动编写SQL语句，框架负责将结果集映射到Java对象。其灵活性高，适合对SQL有精细控制需求的场景。
- 示例：通过XML或注解定义SQL，如 @Select("SELECT * FROM users")。
JPA
是全自动ORM规范（如Hibernate是其实现），核心是对象中心。通过注解或XML配置实体类与数据库表的映射，自动生成SQL，简化数据库操作。
- 示例：@Entity 和 @Table 注解定义实体映射。

映射方式与抽象层次

维度	MyBatis	JPA
映射机制	手动编写SQL，需配置字段与对象的映射	自动映射，通过注解或XML定义ORM关系
抽象层次	低层次，直接操作SQL	高层次，面向对象查询（如JPQL）
数据库耦合	高（SQL依赖数据库特性）	低（支持跨数据库移植）

性能与灵活性

MyBatis
- 优势：SQL优化灵活，支持动态SQL（如 ``标签），适合复杂查询和高性能需求场景。
- 劣势：需手动管理SQL，维护成本高。
JPA
- 优势：内置缓存机制（一级/二级缓存）、懒加载等，适合标准化CRUD场景。
- 劣势：复杂查询可能生成低效SQL，需依赖Hibernate等实现的手动调优。

学习曲线与开发效率

MyBatis
对熟悉SQL的开发者友好，学习成本低，但需处理大量SQL文件和映射配置。
JPA
需掌握ORM概念（如实体状态、事务传播），学习曲线较陡，但可快速实现简单业务逻辑。

适用场景

推荐使用MyBatis：
- 需要复杂SQL优化或数据库特定功能（如存储过程）。
- 项目需求变化频繁，需灵活调整SQL。
推荐使用JPA：
- 业务模型稳定，以面向对象方式操作数据为主。
- 需要快速开发和跨数据库移植的项目。

扩展功能对比

功能	MyBatis	JPA
动态查询	支持XML/注解动态SQL	依赖JPQL或`Specification`接口
事务管理	依赖Spring等外部框架	内置事务管理，支持传播机制
日志系统	基础日志功能	丰富日志（SQL、缓存警告等）
批量操作	需手动优化（如`rewriteBatchedStatements`）	支持批量处理，但性能依赖实现

混合使用建议
在复杂项目中，可结合两者优势：

JPA处理标准化CRUD，MyBatis解决复杂查询或性能瓶颈。
注意事务管理和配置冲突（如Hibernate缓存与MyBatis手动SQL的协调）。

总结

MyBatis：灵活性与性能优先，适合高频优化场景。
JPA：开发效率与标准化优先，适合面向对象设计的业务系统。
目前采用MyBatisPlus做CRUD标准化方法,复杂sql使用xml文件定义sql

posted @ 2024-03-11 14:20 爪哇搬砖阅读(28) 评论(0) 收藏举报

刷新页面返回顶部

爪哇搬砖

Mr.Li

JAVA面试：mysql数据库

介绍一下MySQL架构

连接层（Connection Layer）

服务层（Service Layer）

存储引擎层（Storage Engine Layer）

系统文件层（File System Layer）

介绍一下InnoDB存储引擎的结构

内存结构：

Buffer Pool（缓冲池）

Change Buffer（变更缓冲区）

Adaptive Hash Index（自适应哈希索引）

Log Buffer（重做日志缓冲区）

磁盘结构：

Tablespaces（表空间）

Data Dictionary（数据字典）

Doublewrite Buffer（双写缓冲区）

Redo Log（重做日志）

Undo Logs（回滚日志）

介绍一下MySQL索引结构

MySQL索引的底层结构和运行原理是数据库性能优化的核心，其核心数据结构为B+树，结合存储引擎特性实现高效的数据检索。以下从底层结构、运行原理、存储引擎差异及优化策略等方面详细解析：

为什么不用hash B树 二叉树 而要用B+树

1. B+树 vs B树

B树

B+树

2. B+树 vs 二叉树

二叉树

B+树

3. 底层原理分析

磁盘I/O效率

查询性能

写操作效率

4. 总结

MySQL遇到过的性能问题，做了哪些调优

MySQL 性能问题与调优实践总结 在实际开发与运维中，MySQL 性能问题常表现为慢查询、高负载、锁竞争、I/O 瓶颈等。以下结合常见问题场景与调优手段，分维度梳理解决方案，并附关键实践案例。

讲讲mysql mvcc

1. 隐藏字段

2. Undo Log 与版本链

3. Read View（一致性视图）

1. 读已提交（RC）

2. 可重复读（RR）

mysql 可重复读怎么怎么保证的不会幻读(mvcc + 间隙锁)

读未提交

读已提交

可重复读(mvcc + 间隙锁)

联合索引为什么需要最佳左前缀匹配

数据库怎么实现乐观锁、怎么实现悲观锁

悲观锁

实现方式

缺点

乐观锁

实现方式

优点

缺点

总结

MySQL遇到过什么问题没？

数据库中插入100万条数据，怎么能快速插入

在数据库中快速插入100万条数据需要结合多种优化策略，以下是基于不同场景和技术的综合方案：

mysql change buffer

Change Buffer的工作原理如下：

mysql change buffer 为什么不支持唯一索引

唯一性检查：

同步性要求：

避免冲突：

介绍一下mybatis

MyBatis与MyBatis-Plus（简称MP）是Java生态中广泛应用的持久层框架，两者既有继承关系又有显著差异。以下从核心特性、功能对比、适用场景等维度进行详细介绍：

mybatis与jpa的区别

MyBatis与JPA是Java领域两大主流持久层框架，它们在设计理念、使用场景和实现方式上有显著差异。以下从多个维度进行详细解析：

公告

为什么不用hash B树二叉树而要用B+树

MySQL 性能问题与调优实践总结
在实际开发与运维中，MySQL 性能问题常表现为慢查询、高负载、锁竞争、I/O 瓶颈等。以下结合常见问题场景与调优手段，分维度梳理解决方案，并附关键实践案例。