首页  流程图  详情

Mysql知识点

2021-05-29 14:38:51   0  举报





Mysql知识点

mysql

作者其他创作

大纲/内容

通过free链表查找空闲位置插入

页

间隙锁

意向锁

slave

data5

buffer pool

ib_logfile1

索引原则

回表

向数据库发送请求 cli jdbc

fsync

memory

连接器

hash

data8

脏页

name

work2

replay 重放执行sql，随机写

node

io thread

每秒写入os

执行器

Mysql索引类型(innodb为例)

relay log

innodb

优化sql执行顺序，基于成本，基于规则两方面进行优化

特点：一个节点(只存储key)能存储的更多数据量更多了树的高度降低，支持范围查询

1.OLAP(存储key值，文件位置，文件偏移) eg: hive2.OLTP(存储key值，索引内容) eg: mysql

MTS(并行复制)

内存过多导致无法置换，lru链表最近最长未使用页会被淘汰，但是为了防止select * 操作导致所有页都被置换出去，还有额外机制保证(热数据，冷数据区域)

bin log日志写入

(RR)可重复读，读取v1版本数据

底层存储(innodb)

李四 => 王五

bin log

行锁

B树

排它锁（X）

原子性

bin logerro logretry log

依靠原子性，隔离性，持久性保证

data3

最新记录

防止表锁和行锁冲突

data6

写

索引优化小细节

读

BST(二叉搜索树)

coordinator

主键索引唯一索引联合索引普通索引

undolog

1.执行修改数据sql2.更新内存数据3.写入redo log （此时 prepare阶段）4.写入bin log 日志5.commit 6.更新完成

Mysql锁

依靠undo log的逻辑日志实现回滚功能

顺序读(网络)

B+树

存储引擎

data1

循环写

自增锁

relay log（中继日志）

AVL(二叉平衡树)

特点：非聚簇索引的叶子节点存储索引数据外还会存储聚簇索引的key联合索引和普通索引都是非聚簇索引(myisam叶子节点存储的是数据地址)

读取页

begin

客户端

sql写入操作

保障机制

key2

区别：1. myisam 支持表锁，innodb 支持行锁(作用在索引)和表锁2.myisam不支持事务， Innodb 支持事务3.索引类型myisam是非聚簇索引，innodb一定会有一个聚簇索引4.myisam不支持外键，innodb支持5.myisam支持全文索引，innodb 5.6版本后支持6.myisam适合大量查询的场景，innodbs适合大量插入与更新场景

data7

持久性

flush 链表

解决方案

顺序写

张三 => 李四

顺序读

.ibd

依靠redo log实现持久性

undo log

磁盘（Log files）

锁住指定行记录

Mysql服务

1.节点无序，查询效率慢O（n）2.不支持范围查询

update student set name ='张三' where name = '李四'

存储结构

特点：左节点一定小于根节点，右节点一定大于根节点，当所有节点都大于根节点，叶子节点层数不超过两倍，如果超过一层会自旋调整二叉树，根节点与叶子节点一定是黑色，同一条路径不允许存在连续红色节点1.节点有序，查询效率高O(nlogn)2.范围查询中序遍历

Mysql索引

隔离性

批量事务一起进行刷盘减少io操作

0x123

v1事务前

读写操作

ib_logfile0

使用hash索引

磁盘操作都会经过os buff

Mysql模型

特点：聚簇索引的叶子节点存储完整的行数据如果存在主键索引，则主键索引为聚簇索引如果不存在主键索引，存在唯一索引，则唯一索引是聚簇索引如果都不存在，数据库会生成rowId组成的索引作为聚簇索引，用户无感知

v3事务提交后

表结构文件:.frm索引文件: .MYI数据文件:.MYD

词法分析，语法分析

某个数据范围中不能够写入数据

0commit

回表最左匹配索引覆盖索引下推

log buffer

Mysql事务

work3

记录锁

红黑树

一致性

Log Buffer

commit溢写三种方式(1效率低，但是数据丢失可能性低)

1commit

不会出现索引变化自增调整

Mysql可用性

(RC)读已提交读取v3版本数据

不同存储引擎，数据存放不同位置，不同文件格式InnoDb: 磁盘Myisam: 磁盘memory:内存

组提交

1.机器设备原因2.网络原因3.大事务的执行4.TPS高，单线程进行重做

update student set name ='王五' where name = '李四'

OS Buffer

回滚指针

数据结构

rowID(主键)

张四

redo log(物理日志)undo log(逻辑日志)

实现方式

四大隔离级别

lru 链表

非聚簇索引

聚簇索引(myisam没有聚簇索引)

Buffer pool

data2

注意点：索引不是越多越好，也不是存储的字段越多越好，要根据具体业务进行设计

1.等值查询效率高2.不支持范围查询3.需要大量的内存空间

索引下推

sql thread

key1

保证数据不丢失(不是百分百)

hash二叉树BSTAVL红黑树B树B+树

分析器

2commit

redo log

data4

特点：一个节点能存储的数据量更多了树的高度降低

null

myisam

主从复制

共享锁（S）

特点：左节点一定小于根节点，右节点一定大于根节点，当所有节点都大于根节点，叶子节点层数不超过一层，如果超过一层会自旋调整二叉树1.节点有序，查询效率高O(nlogn)2.范围查询中序遍历

mysql server

age

事务提交

表结构文件：.frm索引与数据文件: .ibd

二叉树

内存

所有sql执行，要么全部成功，要么全部失败（undo log）

v2事务中

读写操作都会先到内存查询，如果内存查找不到对应数据，会到磁盘中查找数据并且将数据加载到内存中，每次IO操作的单位是页update:1.修改buffer pool内存数据（脏页）2.生成redo log -> log buffer3.redo log数据持久化(事务提交)4.返回修改成功

1.读未提交（脏读，不可重复读，幻读）2.读已提交(不可重复读，幻读)3.可重复读(幻读)4.串行化

临键锁(左开右闭) 解决幻读

key3

独立线程写入修改过的数据

最左匹配

表锁

应用场景：数据持久化存储

每次insert update 先更新内存，然后写入日志(redo log bin log)，服务宕机也可以通过磁盘的redo log进行恢复

free 链表

key4

历史记录

为什么要有索引

每秒调用fsync

1.使用索引列查询的时候尽量不要使用表达式eg: select a from table where a + 1= 52.尽量使用主键索引查询，减少回表3.使用前缀索引4.如果查询的字段是单键索引，or操作回走索引，如果查询的所有列都组成索引，那么or也回走索引，否则索引失效5.范围列可以用到索引>= <= between，但是使用范围列后面的列不会使用索引6.强制类型转换会全表扫描7.区分度不高的列不适合作索引

索引覆盖

优化器

事务ID

check point

data

work1

pre

如何设计索引

master

某个数据范围(包含首位节点)中不能够写入数据

sql的实际执行，与执行引擎交互

张三