MySQL知识点整理
2021-09-23 22:40:18 12 举报AI智能生成
MySQL知识点整理
作者其他创作
大纲/内容
支持
事务
回滚，崩溃恢复，多版本并发控制
表锁，行锁
锁
数据和索引一起
表结构定义
.frm
数据和索引
独享表空间
.ibd
共享表空间
ibdata
数据文件
读写
场景
扫表
count
5.5后默认
innoDB
不支持
只表锁，不支持行锁
表结构和数据分离
表数据
.myd
索引树
.myi
写多
专门存储的地方
Myisam
innoDB与Myisam区别
连接器
连接池
系统管理和控制工具
查询缓存
查询优化器
解析器
sql接口
redo log buffer
redo log file
Buffer Pool(默认128M)
系统文件
存储引擎
架构
redo file成功，则commit成功
当事务提交时，先写重做日志(Rede Log)，然后再异步择机将脏页(Buffer pool)写入磁盘
系统重启时，内存未落盘的账页脏页(Buffer pool)丢失，可以从redo log file恢复
预写日志 Write Ahead Log
只有落盘redo file才能事务提交成功
就是是每次提交事务，必须调用操作系统Fsync，redo log buffer 写入rede file
提交强制日志Force Log at Commit
可从共享表空间恢复
当因系统原因导致丢失
buffer pool 的页写入数据文件时，页先复制到双写区(double write buffer)，写入时同时写共享表空间和数据文件。
默认2M
完全检查点，当数据库正常关闭，全脏页落盘
sharp checkpoint
模糊检查点
每秒或每十秒，异步落盘
Master Thread Checkpoint
读取LRU列表，落盘
FLUSH_LRU_LIST Checkpoint
redo日志快满了，落盘
75% 异步落盘
90% 同步落盘
Async/Sync Flush Checkpoint
脏页太多（占75%）落盘
Dirty Page too much
时机
fuzzy checkpoint
脏页落盘时机策略
检查点 CheckPoint
双写 Double Write
数据可靠性
error log
错误日志
更新日志
事务提交后才记录
bin log
二进制日志
general query log
通用查询日志
slow_query__log=on
long_query_time=3
查询阈值
slow query log
慢查询日志
崩溃时的恢复数据
innodb引擎buffer pool写入时，先写入redo log，当落盘成功后，清空redo log。 当宕机落盘失败，从redo log恢复
脏页可靠性处理
循环写入
ib_logfile0
ib_logfile1
文件
默认8M
innodb_log_buffer_size
配置
大小
innodb_flush_log_at_trx_commit
表示事务提交，写入redo buffer里， 后每隔固定时间落盘
0
默认
表示事务提交，必须进行一次fsync
1
表示事务提交，写入系统缓存， 后系统每隔固定时间落盘
2
redo log buffer落到redo file的策略
redo log
重做日志
对事物的影响进行撤销，和多版本并发控制
多版本链表串联
数据结构
undo log 页存的回滚段
行
rowid
表示最近修改的事务ID
事务号
db_trx_id
指向undo log的回滚段
回滚指针
db_roll-ptr
行记录隐藏3个字段
insert只对本身可见
不需要purge操作
rollback后，直接删除
insert undo log
updeta或delete产生
需要purge删除
提交后，不能直接删除
update undo log
分类
undo log
回滚日志
relay log
中继日志
日志文件
元数据（表结构）
数据字典
双写buffer
insert buffer
回滚段 rollback segments
undo空间
存的东西
ibdata1
系统表空间文件
独立表空间
各个表
用户表空间文件
日志
多叉
平衡
B
非叶子节点不存数据，存索引
主干索引
叶子指针相连形成链表
叶子链表
B+
16k假设1行1k
16k=16384字节
16384/14=1170
非叶子节点，主键ID+指针 8+6=14字节
1170*16*1170= 21902400
2.19千万
高度3层
可存多少数据
16k
文件系统最小块 4k
最小存储单元页
聚簇索引
叶子节点存储主键ID
索引需要回表查
非聚簇索引
https://juejin.cn/post/6844904177106157576#heading-1
参考
select_type
查询类型
system
const
eq-ref
命中主键或唯一索引
ref
命中非唯一索引
range
范围
index
全表，索引树读取
all
全表，文件读取
type
类型
无需回表
using index
覆盖索引
尽量用索引捞数据，非索引捞数据，将会全部取出后加锁
Using index condition
索引下推
using where
未使用索引
using temporary
使用零时表
using filesort
使用文件排序
using join buffer
关联字段无索引
extra
id
简单
simple
复杂查询
PRIMARY
select或where中包含子查询
SUBQUERY
from包含子查询
DERIVED
含union
UNION
union重临时表中读取数据
UNION RESULT
table
分区
partitions
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
不需要磁盘IO
数据只有一行
命中主键索引或唯一索引
查询时命中主键或唯一索引
eq_ref
使用非唯一索引
额外搜索含null的值
ref_or_null
使用了两个以上索引，合并索引优化
index_merge
唯一索引用于子查询
unique_subquery
非唯一索引用户子查询
index_subquery
使用索引选择行，where中使用bettween and < > In等
读全表，便利索引树读取
全表，从硬盘读取
All
possible_keys
key
key_len
const，func，null，字段名
常量值查询
字段名
关联查询
func
查询使用表达式
mysql需要扫描的行数
rows
过滤后剩下满足条件的百分比
filtered
使用覆盖索引
Using index
未找到可用索引，通过where条件过滤获取数据
Using where
查询后使用临时表，一般是排序或分组
Using temporary
无法用索引来排序，order By字段没有索引
Using filesort
关联字段没有用到索引
Using join buffer
表示不太正确的where
Impossible where
没有from子句
No tables used
Extra
explain
explain是如何解析sql的
解析
最左前缀要遵守
全值匹配我最爱
范围之后全失效
索引列上少计算
覆盖索引不写星
like百分写最右
索引失效要少用
不等空值还有OR
索引失效和优化
https://www.cnblogs.com/leefreeman/p/8315844.html
数据
索引
版本号
时间戳
程序时间
乐观锁
锁住后不能修改数据，因为update数据时自动加行写锁，与此表读锁互斥
其他会话能读
当前会话能读，不能写
共享读锁，表示表只能读
read lock
锁住后当前会话能修改数据，因为update数据时自动加行写锁
但不能加读锁和写锁
其他会话能读，不能写
当前会话能读和写
加写锁后，不能加读和写锁
write lock
当前会话不能访问非锁定表
锁定后
手动加
表锁
自动加
CURD加读锁
DDL加写锁
元数据锁（表结构）
加读或写锁，其他会话都不能修改表或元数据
show open tables
看哪些表被锁了
每表锁一次+1
table_locks_immediate:产生表级锁定的次数
每表锁等待一次+1
table_locks_waited:出现表级锁定争用而发生的等待次数
show status like 'table%'
分析锁定
分析
SQL layer层
共享读锁 IS
排他写锁 IX
行加共享读锁时，必须先获的该表的IS锁
行加排他写锁时，必须先获得该表的IX锁
提前，意向，再行锁前必须满足的条件
innoDB层，意向锁
使用主键索引锁定一行，会产生记录锁
一行
Record Locks
记录锁
使用非主键索引锁定一行，会产生间隙锁，锁定这行上下之间的记录
记录前，中，后的行
Gap locks
间隙锁
记录锁+间隙锁
Next-Key锁
行锁只有通过索引加锁实现，如果没有索引将退化为表锁
锁定范围
注意如果没用索引，行锁将升级为表锁
select .... lock in share mode
其他会话不可修改
可以读
允许当前事务读一行，阻止其他事务获取排他锁
共享读锁 S锁
select .... for update
允许当前事务更新数据，阻止其他事务获取排共享读和排他写锁
排他写锁 X锁
锁的分类
当前正在等待锁定的数量
Innodb_row_lock_current_waits
系统启动到现在锁定总时间
Innodb_row_lock_time
每次等待平均时间
Innodb_row_lock_time_avg
最长一次等待时间
Innodb_row_lock_time_max
每行锁等待一次+1
等待此时
Innodb_row_lock_waits
show status like 'innodb_row_lock%'
行锁（innoDB）
悲观锁
detele form t1 where id=10
有没有索引
id是不是主键
当前隔离级别
锁的前提
聚簇索引加记录X锁
ID为主键
锁唯一索引上加记录X锁，回表，聚簇索引上加记录X锁
ID为唯一索引
反查索引对应的多条主键ID，加记录X锁
只是回表后锁多条相同的
类似唯一索引
ID为非唯一索引
所有行全加记录X锁
全表扫描
ID无索引
无锁
当查不出记录
RC
同上
出现间隙锁GAP
可以防止幻读
唯一索引上加间隙X锁，回表后，聚簇索引上加间隙X锁
所有行全加间隙X锁
会有间隙锁
RR
只要有sql就锁，而且无索引就表锁
Serializable
index key
如果主键索引是范围查询的话加间隙X锁
加记录X锁
先找主键条件
index Filter
加间隙X锁
后找其他索引条件
table Filter
全部加X锁
在处理无索引的条件
sql加锁分析
加锁分析
系统会检测到死锁，同时后一个事务直接释放锁
资源的锁定顺序
注意程序的逻辑
保持事务的轻量
提高运行速度
尽快提交事务
避免
show processlist
查询进程
查询到相对应的进程，然后 kill id
show OPEN TABLES where In_use > 0;
查看是否锁表
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
innodb_trx ## 当前运行的所有事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
innodb_locks ## 当前出现的锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
innodb_lock_waits ## 锁等待的对应关系
information_schema
查看当前事务，当前锁
开启innodb_lock_monitor后使用更详细
可看锁的具体日志
show engine innodb status
查看引擎状态
mysqladmin -S /tmp/mysql3306.sock debug
课看到线程ID
error日志会看到
使用日志debug
总体锁的竞争状况
排查
死锁
原子
一致
mvcc实现
隔离
持久
ACID
丢失更新
读到其他事务未提交数据
脏读
读到其他事务已提交事务
不可重复读
读到其他事务新增的数据
幻读
并发问题
读未提交
Read uncommitted
读已提交
Read committed
可重复读
innoDB的RR可以解决幻读，因Next-Key锁
Repeatable read
串行化
所有的查询操作会加锁select
隔离级别
存储回滚段，也就是历史版本的数据结构
觉得当前事务可看到的事务链数据
read view
依赖undo log和read view
多版本读
读历史记录
不加锁
简单select操作
快照读
读最新记录
加锁
插入，更新，删除操作
当前读
读的分类
如果最新记录锁定
RC可读到已提交的记录
RR可读到事务开始时的记录
在RR和Rc读的历史记录
一致性非锁定读
事务开始到提交的过程中，都会保存事务到trx_sys的事务链条上
每个事务开始，将当前活跃事务拷贝到read View
当RR时
每个语句开始，将当前活跃事务拷贝到read View
当RC时
事务链表
表示此事务只能看到
事务开启时当前所有事务的集合，存了最大和最小事务ID
MVCC
配置开启
mysqldumpslow分析
诊断一条SQL的硬件性能瓶颈
set profiling=1;
开启
运行sql
show profiles;\t
查看
show profile for query 1
具体分析
profile分析
性能分析
innodb_buffer_pool_sizt=750M
修改my.cnf
默认128M
越少说明需要扩大
空闲页
Innodb_buffer_pool_pages_free
show status like 'innodb_buffer_pool_pages_%'
使用情况查询
扩大buffer pool
子主题 2
将数据保存在内存中，尽量从内存读取数据
利用mysql缓存
内存预热
innodb_log_file_size设置成 buffer_pool*0/25
redo log大 落盘次数少
redo log写入策略配置
关闭 查询日志，慢查询日志，开bin_log
降低磁盘写入次数
ssd
提高磁盘读写
服务器层优化
针对统计分析，或者实时性不高的需求
设计中间表
减少关联查询，创建合理冗余字段
针对字段太多的表
针对不经常被使用或数据较多的字段
拆表
表都要有一个int 主键
表设计层优化
组合索引（最左前缀）
非选择行不加锁
不回表
索引覆盖
where
分组统计
两边排序
on
索引优化
不要用 *
大分页，用ID查询分页
limit优化
不用count(*)
不用mysql内置函数，不会建立查询缓存
Sql层优化
优化
分库分表
MySQL
收藏
收藏
0 条评论
回复 删除
下一页
