MySQL知识点整理思维导图模板_ProcessOn思维导图、流程图

innoDB与Myisam区别

innoDB

事务

支持

锁

表锁，行锁

回滚，崩溃恢复，多版本并发控制

数据文件

数据和索引一起

.frm

表结构定义

.ibd

独享表空间

数据和索引

ibdata

共享表空间

场景

读写

count

扫表

5.5后默认

Myisam

事务

不支持

锁

只表锁，不支持行锁

数据文件

表结构和数据分离

.frm

表结构定义

.myd

表数据

.myi

索引树

场景

写多

count

专门存储的地方

架构

连接器

系统管理和控制工具

连接池

sql接口

解析器

查询优化器

查询缓存

存储引擎

Buffer Pool(默认128M)

redo log buffer

redo log file

系统文件

数据可靠性

预写日志 Write Ahead Log

当事务提交时，先写重做日志(Rede Log)，然后再异步择机将脏页(Buffer pool)写入磁盘

redo file成功，则commit成功

系统重启时，内存未落盘的账页脏页(Buffer pool)丢失，可以从redo log file恢复

提交强制日志Force Log at Commit

只有落盘redo file才能事务提交成功

就是是每次提交事务，必须调用操作系统Fsync，redo log buffer 写入rede file

双写 Double Write

buffer pool 的页写入数据文件时，页先复制到双写区(double write buffer)，写入时同时写共享表空间和数据文件。

当因系统原因导致丢失

可从共享表空间恢复

默认2M

检查点 CheckPoint

脏页落盘时机策略

sharp checkpoint

完全检查点，当数据库正常关闭，全脏页落盘

fuzzy checkpoint

模糊检查点

时机

Master Thread Checkpoint

每秒或每十秒，异步落盘

FLUSH_LRU_LIST Checkpoint

读取LRU列表，落盘

Async/Sync Flush Checkpoint

redo日志快满了，落盘

75% 异步落盘

90% 同步落盘

Dirty Page too much

脏页太多（占75%）落盘

日志

日志文件

错误日志

error log

二进制日志

bin log

更新日志

事务提交后才记录

通用查询日志

general query log

慢查询日志

slow query log

slow_query__log=on

查询阈值

long_query_time=3

重做日志

redo log

脏页可靠性处理

崩溃时的恢复数据

innodb引擎buffer pool写入时，先写入redo log，当落盘成功后，清空redo log。当宕机落盘失败，从redo log恢复

文件

ib_logfile0

循环写入

ib_logfile1

大小

默认8M

配置

innodb_log_buffer_size

redo log buffer落到redo file的策略

innodb_flush_log_at_trx_commit

0

表示事务提交，写入redo buffer里，后每隔固定时间落盘

1

表示事务提交，必须进行一次fsync

默认

2

表示事务提交，写入系统缓存，后系统每隔固定时间落盘

回滚日志

undo log

对事物的影响进行撤销，和多版本并发控制

undo log 页存的回滚段

数据结构

多版本链表串联

行记录隐藏3个字段

rowid

行

db_trx_id

事务号

表示最近修改的事务ID

db_roll-ptr

回滚指针

指向undo log的回滚段

分类

insert undo log

insert只对本身可见

rollback后，直接删除

不需要purge操作

update undo log

updeta或delete产生

提交后，不能直接删除

需要purge删除

中继日志

relay log

系统表空间文件

ibdata1

共享表空间

存的东西

数据字典

元数据（表结构）

双写buffer

insert buffer

回滚段 rollback segments

undo空间

用户表空间文件

独立表空间

各个表

.frm

.ibd

索引

数据结构

B

多叉

平衡

B+

主干索引

非叶子节点不存数据，存索引

叶子链表

叶子指针相连形成链表

最小存储单元页

16k

可存多少数据

16k假设1行1k

非叶子节点，主键ID+指针 8+6=14字节

16k=16384字节

16384/14=1170

高度3层

1170*16*1170= 21902400

2.19千万

文件系统最小块 4k

分类

聚簇索引

非聚簇索引

叶子节点存储主键ID

索引需要回表查

解析

explain是如何解析sql的

参考

https://juejin.cn/post/6844904177106157576#heading-1

查询类型

select_type

类型

type

system

命中主键或唯一索引

const

eq-ref

命中非唯一索引

ref

范围

range

全表，索引树读取

index

全表，文件读取

all

extra

覆盖索引

using index

无需回表

索引下推

Using index condition

尽量用索引捞数据，非索引捞数据，将会全部取出后加锁

未使用索引

using where

使用零时表

using temporary

使用文件排序

using filesort

关联字段无索引

using join buffer

explain

id

select_type

simple

简单

PRIMARY

复杂查询

SUBQUERY

select或where中包含子查询

DERIVED

from包含子查询

UNION

含union

UNION RESULT

union重临时表中读取数据

table

partitions

分区

type

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system

数据只有一行

不需要磁盘IO

const

命中主键索引或唯一索引

eq_ref

查询时命中主键或唯一索引

ref

使用非唯一索引

ref_or_null

额外搜索含null的值

index_merge

使用了两个以上索引，合并索引优化

unique_subquery

唯一索引用于子查询

index_subquery

非唯一索引用户子查询

range

使用索引选择行，where中使用bettween and < > In等

index

读全表，便利索引树读取

All

全表，从硬盘读取

possible_keys

key

key_len

ref

const，func，null，字段名

常量值查询

const

关联查询

字段名

查询使用表达式

func

rows

mysql需要扫描的行数

filtered

过滤后剩下满足条件的百分比

Extra

Using index

使用覆盖索引

Using where

未找到可用索引，通过where条件过滤获取数据

Using temporary

查询后使用临时表，一般是排序或分组

Using filesort

无法用索引来排序，order By字段没有索引

Using join buffer

关联字段没有用到索引

Impossible where

表示不太正确的where

No tables used

没有from子句

索引下推

尽量用索引捞数据，非索引捞数据，将会全部取出后加锁

索引失效和优化

全值匹配我最爱

最左前缀要遵守

索引列上少计算

范围之后全失效

like百分写最右

覆盖索引不写星

不等空值还有OR

索引失效要少用

数据

https://www.cnblogs.com/leefreeman/p/8315844.html

锁

乐观锁

程序时间

版本号

时间戳

悲观锁

表锁

SQL layer层

表锁

手动加

read lock

共享读锁，表示表只能读

锁住后不能修改数据，因为update数据时自动加行写锁，与此表读锁互斥

当前会话能读，不能写

其他会话能读

write lock

加写锁后，不能加读和写锁

锁住后当前会话能修改数据，因为update数据时自动加行写锁

当前会话能读和写

其他会话能读，不能写

但不能加读锁和写锁

锁定后

当前会话不能访问非锁定表

元数据锁（表结构）

自动加

CURD加读锁

DDL加写锁

加读或写锁，其他会话都不能修改表或元数据

分析

看哪些表被锁了

show open tables

分析锁定

show status like 'table%'

table_locks_immediate:产生表级锁定的次数

每表锁一次+1

table_locks_waited:出现表级锁定争用而发生的等待次数

每表锁等待一次+1

innoDB层，意向锁

共享读锁 IS

排他写锁 IX

提前，意向，再行锁前必须满足的条件

行加共享读锁时，必须先获的该表的IS锁

行加排他写锁时，必须先获得该表的IX锁

行锁（innoDB）

锁定范围

记录锁

Record Locks

一行

使用主键索引锁定一行，会产生记录锁

间隙锁

Gap locks

记录前，中，后的行

使用非主键索引锁定一行，会产生间隙锁，锁定这行上下之间的记录

Next-Key锁

记录锁+间隙锁

行锁只有通过索引加锁实现，如果没有索引将退化为表锁

锁的分类

共享读锁 S锁

手动加

select .... lock in share mode

注意如果没用索引，行锁将升级为表锁

允许当前事务读一行，阻止其他事务获取排他锁

其他会话不可修改

可以读

排他写锁 X锁

自动加

DML(insert,update,delete)

手动加

select .... for update

允许当前事务更新数据，阻止其他事务获取排共享读和排他写锁

其他会话不可修改

可以读

分析

show status like 'innodb_row_lock%'

Innodb_row_lock_current_waits

当前正在等待锁定的数量

Innodb_row_lock_time

系统启动到现在锁定总时间

Innodb_row_lock_time_avg

每次等待平均时间

Innodb_row_lock_time_max

最长一次等待时间

Innodb_row_lock_waits

等待此时

每行锁等待一次+1

普通查询select sql语句是不加锁的（非序列化隔离级别）, DML自动加行锁排他写锁

加锁分析

detele form t1 where id=10

锁的前提

id是不是主键

有没有索引

当前隔离级别

RC

ID为主键

聚簇索引加记录X锁

ID为唯一索引

锁唯一索引上加记录X锁，回表，聚簇索引上加记录X锁

ID为非唯一索引

反查索引对应的多条主键ID，加记录X锁

类似唯一索引

只是回表后锁多条相同的

ID无索引

全表扫描

所有行全加记录X锁

当查不出记录

无锁

RR

ID为主键

同上

聚簇索引加记录X锁

ID为唯一索引

同上

锁唯一索引上加记录X锁，回表，聚簇索引上加记录X锁

ID为非唯一索引

出现间隙锁GAP

唯一索引上加间隙X锁，回表后，聚簇索引上加间隙X锁

可以防止幻读

ID无索引

全表扫描

所有行全加间隙X锁

当查不出记录

会有间隙锁

Serializable

只要有sql就锁，而且无索引就表锁

sql加锁分析

先找主键条件

index key

加记录X锁

如果主键索引是范围查询的话加间隙X锁

后找其他索引条件

index Filter

加间隙X锁

在处理无索引的条件

table Filter

全部加X锁

死锁

系统会检测到死锁，同时后一个事务直接释放锁

避免

注意程序的逻辑

资源的锁定顺序

保持事务的轻量

提高运行速度

尽快提交事务

排查

查询进程

show processlist

查询到相对应的进程，然后 kill id

查看是否锁表

show OPEN TABLES where In_use > 0;

查看当前事务，当前锁

information_schema

innodb_trx ## 当前运行的所有事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

innodb_locks ## 当前出现的锁

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

innodb_lock_waits ## 锁等待的对应关系

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

查看引擎状态

show engine innodb status

可看锁的具体日志

开启innodb_lock_monitor后使用更详细

使用日志debug

mysqladmin -S /tmp/mysql3306.sock debug

error日志会看到

课看到线程ID

总体锁的竞争状况

show status like 'table%'

show status like 'innodb_row_lock%'

事务

ACID

原子

redo log,undo log, force log at Commint实现

一致

隔离

mvcc实现

持久

并发问题

丢失更新

脏读

读到其他事务未提交数据

不可重复读

读到其他事务已提交事务

幻读

读到其他事务新增的数据

隔离级别

Read uncommitted

读未提交

Read committed

读已提交

RC

Repeatable read

可重复读

RR

innoDB的RR可以解决幻读，因Next-Key锁

Serializable

串行化

所有的查询操作会加锁select

MVCC

多版本读

依赖undo log和read view

undo log

存储回滚段，也就是历史版本的数据结构

read view

觉得当前事务可看到的事务链数据

读的分类

快照读

读历史记录

undo log

简单select操作

不加锁

当前读

读最新记录

插入，更新，删除操作

加锁

一致性非锁定读

如果最新记录锁定

在RR和Rc读的历史记录

RC可读到已提交的记录

RR可读到事务开始时的记录

read view

事务链表

事务开始到提交的过程中，都会保存事务到trx_sys的事务链条上

当RR时

每个事务开始，将当前活跃事务拷贝到read View

当RC时

每个语句开始，将当前活跃事务拷贝到read View

事务开启时当前所有事务的集合，存了最大和最小事务ID

表示此事务只能看到

优化

性能分析

慢查询日志

配置开启

mysqldumpslow分析

profile分析

诊断一条SQL的硬件性能瓶颈

开启

set profiling=1;

运行sql

查看

show profiles;

具体分析

show profile for query 1

服务器层优化

将数据保存在内存中，尽量从内存读取数据

扩大buffer pool

默认128M

修改my.cnf

innodb_buffer_pool_sizt=750M

使用情况查询

show status like 'innodb_buffer_pool_pages_%'

Innodb_buffer_pool_pages_free

空闲页

越少说明需要扩大

子主题 2

内存预热

利用mysql缓存

降低磁盘写入次数

redo log大落盘次数少

innodb_log_file_size设置成 buffer_pool*0/25

redo log写入策略配置

关闭查询日志，慢查询日志，开bin_log

提高磁盘读写

ssd

表设计层优化

设计中间表

针对统计分析，或者实时性不高的需求

减少关联查询，创建合理冗余字段

拆表

针对字段太多的表

针对不经常被使用或数据较多的字段

表都要有一个int 主键

Sql层优化

索引优化

where

组合索引（最左前缀）

索引下推

非选择行不加锁

索引覆盖

不回表

on

两边排序

分组统计

索引失效和优化

全值匹配我最爱

最左前缀要遵守

索引列上少计算

范围之后全失效

like百分写最右

覆盖索引不写星

不等空值还有OR

索引失效要少用

不要用 *

limit优化

大分页，用ID查询分页

不用count(*)

用count(id),走缓存

不用mysql内置函数，不会建立查询缓存

分库分表