当前位置：首页 > news >正文

MySQL中的部分问题（1）

news 2025/6/6 7:17:00

回滚

回滚是数据库事务中的一个操作，指在事务执行过程中，如果出现错误或用户主动放弃操作，就将数据库状态还原到事务开始之前的状态。
简单理解：
- 就像“撤回”一段操作，把修改的内容全部撤销，恢复到之前的样子。
- 用途：保证事务的原子性（要么全部成功，要么全部不改变）
举例：

假设有一笔转账操作：

A转账100元给B
如果在中途出错，回滚意味着系统会撤销之前的转账，把账户余额恢复到转账之前的状态。
实现机制：
- 依赖于Undo Log（撤销日志），可以逆向恢复数据。

回表

回表在数据库优化中指：
- 查询时只用到索引，但索引中的字段不能完全满足查询条件或显示需要，所以还需要访问表中的数据行，从索引“回到”主数据表（也叫“主表”）读取完整数据。
简单理解：
- “索引覆盖查询”：数据都在索引中就能满足（不用回表）
- 回表：索引不能满足所有字段的需求，还需再从表中读取数据

总结

回表发生在索引不能满足所有查询字段时
是“从索引跳回”数据表的动作
可以影响查询性能（多一次IO操作）

索引优化

索引基本类型

类型	特点	使用场景
B-Tree 索引	默认类型，支持等值和范围查询	常用索引类型
哈希索引	快速等值查找，不支持范围	内存存储引擎 MEMORY
Fulltext	全文索引，全文搜索	文字搜索场景
索引覆盖	查询的字段都在索引中，无需访问数据行	加快查询

InnoDB默认用B-Tree索引。

优化原则

1. 只为常用查询建立索引
- 只在常用的WHERE条件、JOIN关系、ORDER BY、GROUP BY字段上建立索引。
2. 避免过多索引
- 索引越多，写操作越慢。
- 保持索引简洁有效。
3. 设计合理的索引顺序
- 联合索引（多列索引）中，最具筛选能力的列放前面。
4. 选择合适的索引类型
- 根据查询特点，选择B-Tree或全文索引。

优化技巧

1. 使用EXPLAIN分析查询
- 查看查询执行计划，识别是否用到索引。

explain select ...;

2.建立合适的索引
例：
- 单列索引：create index idx_name on table_name(name);
- 复合索引：create index idx_multi on table_name(col1, col2);
3. 索引覆盖查询
- 查询只涉及索引中的字段，无需回表读取数据。

select id, name from users where age > 20;
--- 如果index包含id和name，则无需访问表数据

1. 使用前缀索引（对长字符串）
- 建立索引时，只索引字段前缀，提高效率。

create index idx_prefix on table(col(10)); --索引前10个字符

1. 去除不必要的索引
- 定期用show index查看索引使用情况。
- 删除未使用或冗余索引。

drop index index_name on table_name;

注意事项

索引不要过度依赖：索引提升查询，但会影响写入性能。
避免在低选择性列上建索引：如性别（一般只有两类），效果有限。
索引的列应为经常用作过滤条件：WHERE、JOIN、GROUP BY中的列。

总结

技巧	作用	例子/说明
查询前用`EXPLAIN`	查执行计划	查看是否用到索引
建立复合索引	提升多条件查询性能	`(col1, col2)`
索引覆盖	让查询只用索引	SELECT只涉及索引字段
只建必需索引	避免冗余	去除未用索引
使用索引前缀	长字段索引优化	`col(10)`

MySQL框架

Server层包括：
- 连接器：负责和客户端建立连接，获取权限，管理连接
- 查询缓存：在一个查询语句中，会先到缓存中查看之前是否查询过这条语句（如果开启了查询缓存功能）：若存在则直接返回缓存的结果，优点是命中缓存时效率很高，缺点是缓存失效非常频繁，只要有对一个表的更新，该表所有的查询缓存都会被清空，MySQL 8.0 版本已删除了查询缓存功能（存放在Server的内存中（不是存放在存储引擎中））
- 分析器：对 SQL 语句进行词法分析和语法分析，判断语句是否合法
- 优化器：对SQL语句进行优化，选择索引
- 执行器：调用存储引擎接口，返回结果
- 二进制日志：用于复制、同步和点时间恢复。管理Binlog的写入。
  - Binlog（Binary Log），是MySQL用来记录所有对数据库进行更改操作（写操作）的事件日志文件，包括：
  - 插入（INSERT）
  - 更新（UPDATE）
  - 删除（DELETE）
  - 结构变更（比如创建表、添加索引等）、

Binlog是MySQL用来记录所有写操作的二进制日志文件

存储引擎层：负责数据的存储和提取，其架构是插件式的，支持 InnoDB MyISAM 等多个存储引擎。从MySQL 5.5.5 版本开始默认的是 InnoDB，但是在建表时可以通过 engine = MyISAM 来指定存储引擎。不同存储引擎数据的存取方式不同，支持的功能也不同。
- InnoDB缓冲池：存放数据和索引页，加快访问速度(这是InnoDB存储引擎特有的机制)。当存储引擎接到请求后，先在缓冲池中查找，减少磁盘IO。
- 键缓存：针对MyISAM存储的索引
- 日志管理和持久化
  - 重做日志：事务确保“持久性”的关键，保证在崩溃后恢复数据。（存放在磁盘上，一般是以一组文件（如ib_logfile0、ib_logfile1等）存放）
    - 当事务提交后，变更首先写入重做日志，然后才写入数据文件。
  - 撤销日志：撤销日志记录的是事务进行中的变更前的旧值。事务的回滚支持。（存放在磁盘上，与数据文件一样，存放在InnoDB的系统表空间或专门的撤销表空间中）

特点	Server层	存储引擎层
作用	系统调度和客户请求的管理	数据的实际存储和存取
责任	解析SQL，优化查询，事务管理，连接控制	数据存储结构设计、索引存取、崩溃恢复
独立性	相对独立，可以切换存储引擎（如InnoDB转MyISAM）	插件式，可换支持不同存储引擎

工作流程

客户端发起SQL请求
Server层解析SQL，将请求调度给存储引擎
存储引擎执行数据操作（如查找、插入、更新）
存储引擎返回结果给Server层
Server层返回结果给客户端

在这个流程中，Server层就像中枢调度员，而存储引擎则专注于“存储和检索任务”。

不同的存储引擎

一、InnoDB

1. 特点
- 支持事务：ACID特性，完整支持提交、回滚、崩溃恢复
- 支持行级锁：高并发性能优异
- 支持外键：数据完整性保证
- 支持崩溃恢复：通过 redo log 和 undo log
- 默认存储引擎：MySQL 5.5之后
1. 适用场景
- 需要事务支持的应用（金融、订单系统）
- 高并发写操作
- 数据完整性、崩溃恢复关键场景

MyISAM

1. 特点
- 不支持事务：只支持简单的表锁（表级锁）
- 查询速度快：适合读操作多的场景
- 存储结构简单，索引和数据存放在不同文件
- 不支持外键，容易出现数据不一致
- 支持全文索引
1. 适用场景
- 只读或写少、读多的应用
- 需要全文搜索（MyISAM的全文索引）
- 临时表或不关键数据

Memory（HEAP）

1. 特点
- 数据存放在内存中，实现高速访问
- 数据在MySQL重启后会丢失
- 通常用作临时表或会话缓存
- 支持哈希索引和BTREE索引
1. 适用场景
- 临时存储、缓存、会话变量
- 需要超高速访问的数据

CSV引擎

1. 特点
- 将数据存储到逗号分隔值（CSV）文件
  - 表中的数据不是存放在数据库内部的二进制文件，而是以文本文件的形式保存，每一行对应一条记录，字段之间用逗号（,）隔开。
  - 这种文件很容易用文本编辑器查看和编辑，也方便导入导出。
- 适合数据导入导出，交互操作
- 不支持索引，性能有限
- 仅适合存储简单或导入数据
1. 适用场景
- 数据交换、批量导入/导出
- 兼容其他系统

其他存储引擎（简要）

Aria：MyISAM的后续，支持事务，部分功能
NDB（Cluster Storage Engine）：MariaDB中的分布式集群存储引擎
Federated：支持分布式数据库，连接远程MySQL
Archive：只支持插入和压缩，适合存档

总结对比

引擎	事务	锁机制	外键	索引支持	主要特点	典型应用场景
InnoDB	是	行级锁	支持	B-Tree、全文	高性能、事务、崩溃恢复	关键业务、事务处理
MyISAM	否	表级锁	否	B-Tree、全文	快速读写，无崩溃恢复	读多写少、全文索引
Memory	否	哈希、BT-树	不支持	哈希、BT-树	内存存储，速度超快，但数据易丢失	临时数据、缓存
CSV	否	无	不支持	无	简单导入导出，性能较差	数据交换、导入导出