MySQL
2025-06-21 15:24:57 0 举报AI智能生成
个人使用
作者其他创作
大纲/内容
架构
连接器
主要用于管理客户端连接和权限认证
查询缓存
查询后会将结果放入，但是8.0后删除，因为比较鸡肋
每一次更新都会删除查询缓存中的缓存数据，所以命中效率并不高
分析器
词法分析和语法分析
优化器
优化sql的执行过程和方式
执行器
调用存储引擎接口执行sql，同时也会校验表操作权限
存储引擎
MyIsam、innodb、memory、archive等
索引
数据结构
hash索引
通过一个hash方法获取hash索引值
查找效率高但是存在hash冲突问题
不支持范围查询
二叉树
树形结构
节点左子树数据小于当前节点，右子树数据大于当前节点
二分查找，效率高
顺序插入导致树退化为一个链表，反而影响效率
红黑树
一个平衡二叉树
节点插入时，会通过算法平衡树的高度
数据量大时，树高很高，导致检索效率也不高
b tree
一个节点相当于一个链表，可以存放多行数据
所有节点都可以存放数据，每个节点的值都不相同
非叶子结点存放下层数据指针
检索效率不稳定，不适合范围检索
从左到右依次递增
b+ tree
是b tree 的优化
所有非叶子结点不存放具体数据，只存放下层数据指针
节点之间使用双向指针关联
检索效率稳定，适合范围检索
聚簇索引
通过主键id生成的包含数据表全部数据的索引文件
升序排列
非聚簇索引
使用普通索引(二级索引)为依据生成的索引文件
存放的只用索引以及主键id
获取全部数据需要回表
稀疏索引
稀疏索引不是每行数据都创建索引项，而是间隔一定数据行建立索引，是某些存储引擎(如MyISAM)使用的一种索引策略。
一些问题
为什么选用B+Tree作为索引？
数据全部在底层，MySQL在检索时在固定页大小的情况下(16KB一页)能获取更多的索引数据，从而能检索出更多符合或不符合需求的数据索引，有效减少IO次数
更稳定的检索效率(所有数据都要在最底层进行获取)
通过节点之间双向指针的优化，范围查询效率更高
为什么推荐使用自增整形id
使用非整形数据，对于插入时排序的效率会降低，MySQL需要为我们操作
使用非自增id时，索引建立可能是无序的，会导致插入时因为某些节点数据已满此时在中间插入数据，会为了平衡树而发生树的分裂，导致插入更新效率变低
如果完全自增id，在分页时也可以进行优化，即：limit 10000,5 改为 id > 10000 limit 5
为什么非聚簇索引叶子结点不存放全部数据
多分数据的存放，会导致数据一致性问题
减少数据磁盘占用
explain
是什么？
是MySQL的SQL执行计划
便于我们针对于SQL的执行过程有一个更明确的了解
方便我们针对于执行计划，有针对性的对SQL进行优化
两个扩展
explain extrend
会为我们展示一些额外的执行计划参数
同时使用 show warnings 可以查询优化后的sql语句
explain partitions
如果查询SQL包含分区，可以通过这个方式查看分区执行计划信息
结果参数
id
执行的顺序，id越大的先执行，相同的从上到下依次执行
select_type
查询类型
simple：简单查询
primary：复杂查询
subquery：子查询
derived：派生/衍生查询
table
查询的表
type
检索类型
system：系统检索
const：唯一索引检索
eq_ref：主键关联
range：唯一键范围查询
index：使用普通索引扫描，没有使用查询条件，覆盖索引
all
possible_key
可能使用的索引
key
实际使用的索引
key_len
使用到的索引的字节长度，可能需要一个字节记录字段是否为null
ref
关联的字段？
extra
扩展信息
using index：使用覆盖索引
using file sort：使用文件排序
using where：没用到索引的条件查询
using index dondition：查询的列不被索引完全覆盖（用到联合索引的部分）
using temporary：用到临时表
索引优化
索引失效情况
不遵守最左前缀匹配法则
对查询列使用函数操作
in or 可能不走索引（数据量过大时）
like 使用不当会导致不走索引
is null 或者 is not null 导致不走索引
!= 导致索引失效
条件查询类型不匹配导致失效
索引优化
使用最左前缀匹配规则
尽量使用覆盖索引而不是回表
全值匹配：使用到全部联合索引列效率更高
一些知识
force 强制使用索引
扫描行数少不一定就快
实战
索引下推
允许存储引擎在索引层面就使用where条件进行过滤掉不符合条件的结果列，避免先回表数据再进行结果筛选
索引跳跃扫描
联合索引会有
在(a, b, c)在a的区分度不高的情况下，MySQL8.0会把只查询 b ， c的sql语句改为：固定值1/2/3/4，b，c。这种方式来使用索引.
trace 工具
用于深入跟踪MySQL对于sql执行的优化
可以看到MySQL对于sql使用索引的权衡
order by 与 group by
where+order by 可以使用联合索引，所以遵守最左前缀匹配
8.0之前 group by 会隐式执行一次order by
尽量使用覆盖索引排序
范围查询会导致索引失效
分页查询优化
连续自增的id
可以使用 id > 10000 limit 5 替代 limit 10000,5
非连续的自增id
使用覆盖索引
select * from table join on (select id from table order by name limit 10000, 5) on in.id = out.id
上述sql 可以使用到覆盖索引先排序id查数据，而不是直接在table的全部数据内进行排序
join 关联优化
使用inner join 时，MySQL使用小表作为驱动表
使用 left join 时，把驱动表放在left左边
使用 right join 时，把驱动表放在right 右边
关联字段尽量增加索引
MySQL使用inner join 关联时，如果误指定驱动表，可以使用straight_join 强制指定
索引设计思路
代码写完之后在进行索引建立
优先根据业务情况建立联合索引而不是大量创建单值索引
对于长字符类型的字段创建索引可以使用前20字符进行索引建立
查询尽量使用覆盖索引
排序/分组与where 冲突时，优先选择where
基于慢SQL 优化
小基数列不加索引
filesort
是什么？
文件排序
在MySQL对数据进行排序时，如果没有使用索引进行排序，就会用到filesort
单路排序
一次性加载所有的文件字段，在 sort buffer 中进行排序
双路排序
只加载需要排序的字段和主键id，在sort buffer中排序后回表取出select 字段
max_length_for_sort_data
如果 SELECT字段 的大小小于这个参数，则使用单路排序，反之使用多路排序
连接算法
NLJ 嵌套链接循环算法
一次一行循环的从嵌套表中获取数据，再去被驱动表中进行关联查询。
只有使用索引时，默认使用这种算法
索引定位数据时间复杂度是1，所以关联被驱动表(1w行)次数 1w+驱动表行数
BLJ基于块的嵌套连接算法
把驱动表的多行数据载入join_buffer后，逐行扫描被驱动表进行关联。
join_buffer 默认是256kb，如果放不下表数据，会分段存放
事务
ACID
原子性
对于事务内的操作是原子的，要么全部成功要么全部失败
隔离性
不同事务之间数据的操作是隔离的
一致性
对于数据的状态，在修改前后时一致的，同时必须遵守相应的逻辑与业务规则
持久性
事务提交之后，数据的修改是永久的
事务隔离级别
读未提交
读取未提交事务的数据
导致脏读，脏写，不可重复读和幻读
读已提交
只允许读取已提交事务的数据
导致脏写、不可重复读，幻读
可重复读
事务内，数据的多次读取，数据是一致的
幻读、脏写
MySQL默认事务隔离级别
串行化
串行化执行，所有事务阻塞等待其他事务的执行完毕
innodb引擎事务持久性的保证
通过redolog和binlog实现
在安全性下，所有事务提交都必须将redolog 和binlog落盘后，才能提交
大事务的影响
容易出现大量阻塞，导致锁超时或死锁
容易导致连接被占满
undolog日志膨胀
事务提交或回滚效率变低
主从复制延迟
事务优化
大事务拆分小事务
查询放在事务外执行
涉及锁操作尽量放在事务最后执行
设置超时
应用侧保证一致性
锁
区分
性能
悲观锁
乐观锁
数据锁粒度
行锁
页锁
表锁
操作类型区分
读锁
写锁
意向锁
意向共享锁
意向排它锁
I锁，针对于表加锁，属于行锁优化，避免加表锁时逐行检索是否有行锁
读锁
S锁，共享锁
不同事务可以读取数据，但是不能修改数据
写锁
X锁，排它锁
其他事务不能读写数据
表锁
行锁
页锁
BDB引擎独有
间隙锁
就是对两个数据行索引之间的间隙加锁
临建锁
行锁+间隙锁
行锁升级
前提：不使用索引加锁
MySQL会把遍历过的索引记录和间隙都加锁，从而导致行锁升级
Myisam和Innodb锁区别
Myisam只有表锁，Innodb有表锁和行锁
Myisam加锁性能高，Innodb加行锁时性能较低
Myisam查询也会加表锁，但是Innodb除非串行化下，不会加锁
虽然Innodb加锁效率低，但是行锁的实现反而增加了数据操作的效率
锁优化
加锁都走索引，避免锁升级
尽可能减少检索范围
MVCC机制
介绍
多版本并发控制机制
主要通过read review (一致性视图)、undolog和版本链实现
所有的读操作都是快照读，所有的写操作都是当前写
详解
undolog
回滚日志
版本链
一行数据被多行数据修改后，Innodb会把这些事务的undolog通过 trx_id（事务id）和 pool_pointer（当前事务的回滚日志）串联起来形成的一个历史记录版本链
read review
数据一致性视图
开始事务，在第一次执行查询时，生成一个当前全部未提交事务的一致性视图，在事务提交前永远不会变化。同时记录内最小事务id和最大事务id
可重复读下，只有第一次查询生成
读已提交下，每次查询都会生成
可见性规则
已提交事务可看，黄色区间需判断当前行事务id是否在一致性视图内。本身trx_id也可见
Innodb引擎底层原理
各类日志
redo log
用于事务提交后，数据还未落盘时，MySQL宕机重启时的数据恢复
持久化的保证
undo log
用于事务回滚时的数据回滚操作
通过段的方式管理，每个回滚段记录1024个segement，每个事务对应一个segment
MySQL8.0升级最大支持128个回滚段
如果是修改，还需MVCC不在使用才能删除
bin log
归档日志
用于主从复制和增量数据恢复
在服务重启、执行flushlogs、日志文件大于max_binlog_size时会重新生成
日志格式
statement，基于语句的复制
row，基于行的复制
MIX，当有函数时，使用row，没有时，使用statement
磁盘写入机制sync_binlog
0, 只写入page cache
1，每次提交都写入磁盘
N，每次事务写入pagecache，N个事务累计写一次磁盘
错误日志
MySQL启停日志
通用查询日志
记录所有查询sql，很耗费性能
更新数据的流程
1. 更新buffer pool 数据，如果没有，此时从磁盘加载需更新数据当前整到 buffer pool中
2. 记录当前更新数据的 undolog
3. 写入redolog buffer后，顺序写入 redolog 中，准备提交数据
4. 写入bin log，写入成功后，给redo log 打一个提交标识表示事务提交成功
5. 异步io线程在空闲时会把buffer pool中变更数据刷盘
为什么先写入buffer pool 而不是直接落盘
效率
为什么用redolog 和 undolog 而不能只用一个
作用不同
效率不同
出现时机不同
redo log 是什么样的日志？
实际上是一个固定大小的文件
数据的写入从第一个文件第一行开始，直到最后一个文件的最后一行，写满后再从头开始
如果没有写入空间，此时会执行落盘，擦除掉已经落盘记录
Innodb_flush_log_at_trx_commit
主要是用于控制事务在提交时，是否使用redo log和 os 的page cache
0，不写入redolog
1，必须先写入page cache后，写入redo log
2，只写入page cache，不写入redolog
数据的恢复
如果是误删除，可以通过binlog恢复
如果是整个库删除，需要使用备份数据恢复，再使用binlog做增量恢复
buffer
buffer pool
一般设置为内存的 60%到70%
优先更新buffer pool数据，等一段时间后异步刷盘
redo log pool
主要用于提升redolog 写入效率
使用顺序写的方式写入磁盘
doublewrite buffer
为了解决mysql一页大小16kb和系统一页大小4Kb之间差异导致数据可能落盘失败而存在的。
恢复时InnoDB可以检查双写缓冲区中的页是否完整，从而决定是否重新落盘
8.0新增特性
索引隐藏
groupby 不再默认排序
索引可以倒序
函数索引
窗口函数
默认Innodb表，除非手动指定Myisam
可以设置bufferpool自适应
DDL语言也支持事务
自增id持久化
innodb 新增跳过锁等待
默认字符集变更为utf8mb4
undolog不在使用表空间而是独立
binlog日志过期精确到秒
参数修改持久化
元数据存储变化
死锁检查控制
WAL
预写式日志
循环顺序写
0 条评论
下一页
