MySQL底层原理

1.常见索引数据结构

二叉树
- 最低效
- 容易出现深度过大的问题，从而导致大量IO，性能低
红黑树（二叉树pro）
- 相比与二叉树有自平衡特性：即会通过自身的算法降低二叉树在极端情况的深度
- 红黑树本质上就是一种特殊的二叉树
- 即使可以降低二叉树的深度，但在实际中深度仍较大，性能还是比较低
B树
- 所有节点都存数据（且不止一个数据）
- 非叶子结点存key+data
- 叶子结点间不一定相连
- 性能比红黑树要好，但不适用于范围查询，因其本身无序
B+树
- MySQL的默认索引方式
- 性能优于B树，高度相对更低
- 非叶子结点只存key，只有叶子结点存data，且叶子结点之间通过链表相连
- 范围查询高效

2.如果一个表没有主键索引还会创建B+树吗？

InnoDB 的数据是按聚簇索引顺序存储的。当你没定义主键时，InnoDB 仍然必须有一个聚簇索引，会生成一个隐藏的row_id作为唯一的主键id，该id不可被修改、查询、使用。

3.Hash索引

Hash索引会对索引列的值进行Hash计算，然后填入对应的槽

当字段的Hash值出现重复时（大量数据时，即使是不同的数据也有极小概率出现相同的hash值），也就是Hash冲突，就会形成一个链表

4.聚簇索引与非聚簇索引

按物理存储分类：InnoDB的存储方式是聚簇索引，MyISAM的存储方式是非聚簇索引

InnoDB这种索引和数据存储在一起的就是聚簇索引。InnoDB 中主键索引是聚簇索引，普通索引是非聚簇索引。

InnoDB存储文件后缀
- .frm:Frame->表结构
- .ibd:Innodb data表索引+数据
MyISAM存储文件后缀
- .frm:Frame->表结构
- .MYD:MyISAM Data->表数据
- .MYI:MyISAM Index->表索引

5.InnoDB的二级索引是非聚簇索引

除主键以外的索引，存的都是二级索引

在InnoDB存储引擎中，叶子结点存的是：索引列+主键值。
二级索引不直接存储整行数据，只存主键值

回表

如果想通过二级索引查询其他字段：

先通过二级索引找到主键
再回到聚簇索引差整行数据
这个过程就是回表

覆盖索引

“覆盖索引”不是一种索引类型，而是一种查询现象
当查询的所有字段都已经包含在索引中时，就不需要进行回表，直接在索引树中就能拿到结果。这就叫覆盖索引

例如：在二级索引中查找主键字段，因为二级索引直接存储主键值，就不需要回表查询，即为覆盖索引

6.索引下推

索引下推：简称ICP。是在MySQL5.6中针对扫描二级索引的一项优化改进。用来在范围查询时减少回表的次数。ICP适用于InnoDB和MYISAM

ALTER TABLE `test`.`user` ADD INDEX (`name`,`age`)

不使用索引下推实现：

SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;

场景

当进行范围查询时，不使用索引下推就会对所有数据都进行回表。有几条数据就会回几次表

索引下推：在你查询的范围里一次性回表。而不是几条数据回几次表

7.联合索引

最左前缀原则

以最左边的为起点的任何连续的索引都能匹配上。
如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了
当创建(a,b,c)复合索引时，想要索引生效的话，只能使用a和ab、ac和abc三种组合

最左前缀原则下索引失效的原因：因为索引生效的前提是有序，如果不符合最左前缀原则，没有第一个字段有序的前提，那就不是有序的，索引不生效

联合索引的优势

1.减少开销

建一个联合索引(a,b,c)，实际上相当于建了(a),(a,b),(a,b,c)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大减少开销！

2.覆盖索引

对于索引(a,b,c),如果有如下sql的

select a,b,c from table where a='xxx' and b='xx';

那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别是随机io是DBA主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一

3.效率高

索引列多时，联合索引的效率比许多个单列索引高很多。可以参考索引下推

8.索引的优缺点

优点

提高检索效率
降低排序成本，索引对应的字段是会有一个自动排序功能的，默认是升序asc

1, 3, 5, 10   ← 升序 ASC
10, 5, 3, 1   ← 降序 DESC

缺点

创建索引和维护索引需要耗费时间，这种时间随数据量的增加而增加
会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护
索引会占用磁盘物理空间，数据量越大，占用空间越大（因现时代磁盘空间越来越大，可忽略）

9.索引的使用场景

适合：
1. 较频繁的作为查询条件的字段应该创建索引
不适合：
1. 字段值的唯一性太差不适合单独作为索引
2. 更新非常频繁的字段不适合
3. 不会出现在where举句中的字段不适合

10.索引失效

①不符合最左前缀原则

②应做到不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

③范围查询会使后面字段无序，造成部分索引失效

范围之后全失效

EXPLAIN SELECT * FROM employees WHERE name='张三' AND age=18 AND position='beijing';
EXPLAIN SELECT * FROM employees WHERE name='张三' AND age=18 AND position='beijing';

注：当第一个字段就使用范围查询时，MySQL默认的方式是使用全盘扫描，可以用FORCE INDEX来指定让MySQL使用联合索引

EXPLAIN SELECT * FROM employees FORCE INDEX (idx_name_age_position) WHERE name>'张三' AND age=18 AND position='beijing'

④尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少`select *`语句——覆盖索引不写星

⑤不等、空值还有or，会使索引失效要少用

MySQL在使用不等于(!=或者<>),not in,not exists的时候无法使用索引会导致全表扫描
<小于、>大于、<=、>=这些，MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

EXPLAIN SELECT * FROM employees FORCE INDEX (idx_name_age_position) WHERE name != '张三';

EXPLAIN SELECT * FROM employees WHERE name is null;

EXPLAIN SELECT * FROM employees FORCE INDEX (idx_name_age_position) WHERE name = '张三' or name = '李四';

⑥Like 百分写最右

EXPLAIN SELECT * FROM employees WHERE name like '%三'

这样写会导致索引的排序失效，从而导致转向全盘扫描。不应执行此操作

EXPLAIN SELECT * FROM employees WHERE name like '张%'

问题：解决like '%字符串%'索引不被使用的方法？

使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name LIKE '%Lei%';

如果不能使用覆盖索引，则可能需要借助搜索引擎

11.范围查询优化

给年龄添加单值索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE;

EXPLAIN SELECT * FROM employees WHERE age >= 1 and age <= 2000;

没走索引的原因：MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引。

优化方法：可以将大的范围拆分成多个小范围

explain select * from employees where age >= 1 and age <= 1000;
explain select * from employees where age >= 1001 and age <= 2000;

然后在代码中合并查询结果

还原最初索引状态

ALTER TABLE `employees` DROP INDEX `idx_age`;

使用Limit

提高查询效率：一个查询返回成千上万的数据行，不仅占用了大量的系统资源，也会占用更多的网络带宽，影响查询效率。使用limit可以限制返回行数，减轻系统负担，提高效率。
避免过度提取数据：对于大型数据库系统，从数据库中提取大量的数据可能会导致系统崩溃。使用limit可以限制提取的数据量。
优化分页查询：分页查询需要查询所有的数据才能进行分页处理，这会浪费大量的系统资源和时间。使用LIMIT优化分页查询可以只查询需要的数据行，缩短查询时间，减少资源的浪费
简化查询结果：有时我们只需要一小部分数据来得出决策，而不是整个数据集。使用LIMIT可以使结果集更加精简和易于阅读和理解

百万级表LIMIT翻页越往后越慢怎么办？
以下面这条查询语句为例，解释为什么offset偏大之后limit查找会变慢

select * from table_name limit 10000,10

获取一万条数据后面的十条数据

这句SQL的执行逻辑是

从数据表中读取第N条数据添加到数据集中
重复第一步直到N=10000+10
根据offset跑起前面10000条数据
返回剩余的10条数据

所以说读取前面一万条数据的过程都是无效的

第一次优化
根据数据库这种查找的特性，就有了一种想当然的方法，利用自增索引（假设为id）

select * from table_name where (id >= 10000) limit 10

确实能达到优化的目的，但是限制太多，在实际开发中显然是不会用到的

第二次优化
说起数据库查询优化，第一时间想到的就是索引，所以便有了第二次优化：先查找出需要数据的索引列（假设为id），再通过索引列查找出需要的数据。

select * from table_name where id in (select id from table_name where (user = xx) limit 10000,10) b using (id)

select * from table_name where (user = xxx) limit 10000,10

相比较的结果是（500w条数据）：第一条花费平均耗时约为第二条的1/3左右
同样是较大的offset，第一条的查询更为复杂，为什么性能反而得到了提升？
基本原理就是：

子查询只用到了索引列，没有取实际的数据，所以不涉及磁盘IO，即使是比较大的offset查询速度也不会太差
利用子查询的方式，把原来的基于user的搜索转化为基于主键(id)的搜索，主查询因为已经获得了精准的索引值，所以查询过程也相对较快

第三次优化
在数据量大的时候in操作的效率就不怎么样了，我们需要把in操作替换掉，使用inner join就是一个不错的选择

union all代替union

UNION ALL 的作用是：把多个 SELECT 查询的结果“直接拼接在一起”，并且不去重。

假设有两个查询：

SELECT name FROM student_2023
UNION ALL
SELECT name FROM student_2024;

执行结果是:

先取第一个查询的结果
再把第二个查询的结果追加到后面
不会删除重复行

关键字	是否去重	性能	使用场景
`UNION`	✅ 去重	较慢	需要唯一结果
`UNION ALL`	❌ 不去重	更快	不关心重复、追求性能
那么`union all`与`union`如果业务数据容许出现重复的记录，我们更推荐使用`union all`，因为`union`去重数据需要遍历、排序和比较，它更耗时，更消耗cpu资源，但是数据结果最完整。

Join表不宜过多

系统负载增加：join操作需要大量的计算，因此会导致系统负载增加
维护难度加大：在一个连接了多个表的查询中，如果修改其中一个表的结构或内容，就可能会需要同时修改其他表的结构或内容。就增加了耦合性，不利于维护。
因此在数据库设计时，应该尽量减少join操作的使用频率，简化表之间的关系
除上述优化之外，通常在建表还需要注意以下内容：
1. 控制索引数量
2. 选择合理的字段类型

总结

SQL优化是提高数据库性能的重要方法，在实际开发中我们的SQL要尽量遵守以下几点原则，避免留下技术债：

减少数据扫描
返回更少数据
减少交互次数
减少服务器cpu及内存开销

事务

一组操作要么全部成功，要么全部失败，目的是为了保证数据的最终一致性

事务的ACID四大特性

原子性（Atomicity）：当前事务的操作要么同时成功，要么同时失败。原子性由undo log日志来保证
一致性（Consistency）：使用事务的最终目的，即从结果的角度来看，两端同时达成某一操作。由业务代码正确逻辑保证。
隔离性（Isolation）：在事务并发执行时，他们内部的操作不能互相干扰
持久性（Durability）：一旦提交了事务，它对数据库的改变就应该是永久性的。持久性由redo log日志来保证

undo 日志->原子性：

在事务回滚的时候需要的东西。当你将5 updata 成3时(5->3)，undo日志就会记录3->5，在需要回滚的时候，就会从undo日志里读取。以此来保证原子性。

MySQL的原子性是通过undo日志来实现的

事务隔离性

InnoDB引擎中，定义了四种隔离级别供我们使用，级别越高事务隔离性越好。但性能就越低，而隔离性是由MySQL的各种锁以及MVCC机制来实现的

read uncommit（读未提交）：脏读——能读到修改了，但是还没有提交的数据
read commit（读已提交）：不可重复读——只能读到已提交的数据。但有个缺点是数据可能实时变化，不稳定。适合对并发要求较高的场景，如软件公司
repeatable read（可重复读）：脏写——能读到在读取的时候的快照版本的数据，相比于读已提交更加稳定。缺点是可能在写入数据时，数据库内真实的数据已更新，但代码里还在旧的数据的基础上进行修改。就会产生覆盖冲突，即脏写。适合对并发要求不高，对时间一致性敏感的场景，如电商公司的报表，要求各个数据处在同一时间节点下
serializable（串行）：读和写在同一时间只能执行其中一种操作，这样就避免了以上的所有并发问题。

串行化的实现原理

读锁（共享锁、S锁）：select ... lock in share mode;
读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改
写锁（排它锁、X锁）：select ... for update;
写锁是排它的，会阻塞其他的写锁和读锁，update、delete、insert都会加写锁

可重复读实现原理

MVCC多版本并发控制，就可以做到读写不阻塞，且避免了类似脏读这样的问题，主要通过undo日志链来实现

read commit(读已提交)：语句级快照
repeatable read(可重复读)，事务级快照

MVCC是通过copy on write实现的，即每次修改时都会先复制原数据，然后在复制出的副本上修改，同时roll_pointer回滚指针指向原数据。

也就是说，每次修改，都不会删除曾经的数据，而是产生一份新的备份。在可重复读的模式下，读到目标数据后，会与该数据产生绑定关系。无论该数据被其他数据如何修改，都只是产生新的副本。而可重复读依靠这份绑定关系，仍可继续读到这份曾经的历史版本

读已提交:每次都读最新的、有commited的数据

可重复读（脏写）的解决方案

方案1：乐观锁

可以用乐观锁来避免脏写问题，具体实现:
在记录里加上一个version（版本号）字段，值初始为1 。每次数据更新版本号都同步自动+1

这样做在并发时，其它事务更新数据就会使版本号自动改变迭代。而我们在Java代码中，更新时加上版本号限制，如果版本号已经更新，就会找不到数据。也就不会覆盖数据，避免了脏写的问题

update account set balance = 1500 where id = 1 and version = 1;

and version = 1;起到了限制版本号的作用，防止脏写

因为这里会直接失败，所以乐观锁一般与while循环搭配
如果更新失败，则返回循环头部，重新获取最新的版本，重新更新一次。

方案2：在数据库里的数据基础上直接进行修改

即将:

update account set balance = 1500 where id = 1;

改为

update account set balance = blance + 500 where id = 1;

这样会在修改时，自动从数据库中获取最新的数据，并在最新数据的基础上进行更新

查询业务需要使用事务吗？

在可重复读隔离级别下需要使用事务，否则如果在你查询后，数据库内的数据更新了，但你已经查询的数据没有更新，就会造成数据与实际不一致的情况。所以在可重复读的隔离级别下，查询业务也需要使用事务。需要理解可重复读的本质

并发时避免写“大事务”。大事务的影响

并发情况下，数据库容易被撑爆
锁定太多的数据，造成大量的阻塞和锁超时
执行时间长，容易造成主从延迟
回滚所需要的时间比较长
undo log膨胀
容易造成死锁

长事务的优化

将查询等数据准备操作放到事务外
事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久
事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
更新等涉及加锁的操作尽可能放在事务靠后的位置
能异步处理的尽量异步处理
应用侧（业务代码）保证数据一致性，非事务执行

redo 日志的作用——持久性

redo log 会 记录所有对数据页的修改操作。

MySQL 修改数据时 不会立刻把数据写到磁盘的数据页，原因是：

磁盘随机写 非常慢
频繁写磁盘性能会很差
所以 InnoDB 采用：
先写内存 → 再异步写磁盘

客户端修改数据
     ↓
修改 Buffer Pool（内存中的数据页）
     ↓
记录 redo log
     ↓
事务提交
     ↓
后台线程慢慢把数据刷到磁盘

这里就产生一个问题：

如果 MySQL 在数据还没写入磁盘时崩溃怎么办？

如果没有日志：

👉 这次更新就丢了

当mysql崩溃重启时：

读取 redo log
↓
重新执行这些修改
↓
恢复数据

注：
1.redo日志记录的是磁盘发生的物理读写，而不是sql语句
2.在数据写入磁盘之前，redo日志必须先写入磁盘
3.redo日志是顺序写，数据页是随机写。顺序写磁盘速度可以快10~100倍
4.redo日志让 MySQL 可以用“内存修改 + 顺序写日志”代替“随机写磁盘”。

MySQL底层结构

Client
   ↓
Server Layer（SQL层）
   ↓
Storage Engine Layer（存储引擎层）
   ↓
File System

Server层

模块	作用
连接管理	管理客户端连接
SQL解析	解析SQL语句
优化器	生成执行计划
查询缓存（旧版本）	缓存查询结果
权限管理	验证用户权限
事务协调	协调binlog和存储引擎

1 连接器（Connection）

负责：

TCP 连接
用户认证
连接线程管理

例如客户端连接数据库：

mysql -uroot -p

连接器会进行：

身份认证
创建会话
分配线程

2 解析器（Parser）

解析器的作用：

将 SQL 转换为语法树（AST）

例如：

SELECT name FROM user WHERE id=1

解析后的逻辑结构：

SELECT  
├ table=user  
├ column=name  
└ condition=id=1

如果 SQL 语法错误，解析阶段就会报错。

3 优化器（Optimizer）

优化器决定：

SQL 应该如何执行效率最高

例如：

SELECT * FROM user WHERE age=20

优化器会决定：

是否使用索引
使用哪个索引
join 顺序
扫描方式

优化结果称为：

执行计划（Execution Plan）

4 执行器（Executor）

执行器负责：

真正执行 SQL 并调用存储引擎

例如调用存储引擎接口：

handler->index_read()

存储引擎层

存储引擎负责：

数据的真正存储和读取

常见存储引擎：

引擎	特点
InnoDB	默认引擎，支持事务
MyISAM	旧引擎，不支持事务
Memory	数据存储在内存
每个存储引擎都实现统一接口：

handler API

Server 层通过这些接口访问数据。

InnoDB内部结构

核心组件：

InnoDB  
├ Buffer Pool  
├ Redo Log  
├ Undo Log  
├ Double Write  
├ Change Buffer  
└ Data File

1 Buffer Pool（核心组件）

Buffer Pool 是：

数据库的内存缓存

数据访问流程：

磁盘数据页  
↓  
加载到 Buffer Pool  
↓  
SQL 在内存中操作

优点：

减少磁盘 IO
提高数据库性能

2 Redo Log （InnoDB特有的）

Redo Log 的作用：

保证事务持久性和崩溃恢复

流程：

修改数据页  
↓  
记录 redo log  
↓  
事务提交

如果数据库崩溃：

重启时读取 redo log
重新执行修改
恢复数据

3 Undo Log

Undo Log 主要作用：

事务回滚
实现 MVCC（多版本并发控制）

例如执行：

UPDATE user SET age=20 WHERE id=1

修改前的数据会写入 undo log。

如果事务回滚，就可以恢复旧数据。

4 Double Write

Double Write 机制用于解决：

部分页写入问题（Partial Page Write）

原因：

磁盘写入时可能只写了一半数据。

解决方案：

先写 DoubleWrite Buffer  
再写真实数据页

保证数据页完整。

5 数据文件

数据最终存储在：

.ibd 文件

MySQL 8 默认：

每个表一个独立表空间文件

既然磁盘顺序写效率那么高，为什么设计磁盘时要采用磁盘随机写？

可以这样简要总结：

顺序写磁盘确实比随机写快，因为机械硬盘随机写需要频繁移动磁头（寻道），而顺序写几乎不需要移动。
但现实的数据访问是 随机的（例如数据库更新不同记录），所以存储系统必须支持 随机读写。
如果只使用顺序写（像日志那样不断追加），读取某条数据时可能需要扫描大量日志，读性能会非常差。
因此现代系统通常采用折中方案：顺序写日志 + 随机访问数据结构（如 B+Tree），既保证写入性能，又保证读取效率。

posted @ 2026-07-02 18:12 畅畅c 阅读(2) 评论(0) 收藏举报

刷新页面返回顶部

畅畅c