mysql 思维导图模板_ProcessOn思维导图、流程图

锁

行锁

当A和B更新同一行数据，那么B必须等A 先执行完，否则会阻塞

在实际开发中如果，在一个事物执行多条语句，要将update较少的sql 放在后面，这样减少锁的时间

行锁是索引实现锁记录的，如果update条件没有索引，那么会锁整个表

死锁

设置了死锁超时，设置过大影响请求，设置过小会频繁中断 innodb_lock_wait_timeout

死锁检查innodb_deadlock_detect=on

每次被堵住的线程，都会判断是不是自己的加入导致了死锁，时间复杂度是O(n).假设有100个并发线程同时更新同一行，那么死锁检查是100万这个量级，虽然没有发生死锁，也会耗费大量的cpu，因此会看见cpu利用率高，但是每秒执行不力几个事物

关掉死锁检查，如果出现错误业务重试

解决方法，减少并发，控制业务的访问量，控制修改同一行的访问量为10个线程或者更少

例如

并不是死锁检测都要扫描所有事实，比如 B-A, D-C ,E-D那么E判断D、C是否会形成死锁，不检测B和A

表锁

lock tables … read/write

释放

主动unlock tables

client断开会自动释放

例如

lock tables t1 read, t2 write

其他线程写t1,读t2都会被阻塞

在unlock tables前当前线程只能读t1 和读写t2，不可以写t1

MDL锁

metadata lock

当访问一个表的时候会自动加上，试想当一个select 表时候，表结构被改动了，这个不可以的

当对表做crud的时候，会添加毒read lock，当对表结构修改的时候添加 write lock

读写，写写之间是互斥的，如果当一个表添加字段，一个线程添加字段，另一个线程也添加字段，则第二个线程必须等第一个执行完才能继续执行

例如

给一个小表添加字段，导致整个数据库挂了

session A B C D

A 读 B读没问题 C是alter  D是读，AB 不会被lock，C会被lock D也会被lock因为c还没有执行完，那么后续如果这张table频繁读写，则都会被lock，因为c被lock d也被lock，那么连接池好近，客服端崩溃

如何有效的添加给频繁的小标添加字段

MariaDB

ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...

如果访问不频繁要避免长事物，如果长事物要避免做dml，等不频繁的时候在做

问题

online ddl

1. 拿MDL写锁 2. 降级成MDL读锁 3. 真正做DDL 4. 升级成MDL写锁 5. 释放MDL锁

1.2.3.5过程不互斥，4的过程比较耗时，这期间表可以正常读写

全局锁

Flush tables with read lock（FTWRL）

当执行全局锁的时候，整个数据库处于只读状态，所有写，和建表语句等都是阻塞的

一般情况不会使用

测试可以使用事物写两个sql语句，开启A事物，不提交，开启B事物提交，看B是否阻塞，在commit A看B是否执行

mysql 抖动一下

分支主题

事物

隔离级别

读已提交read committed

读未提交read uncommitted

可重复读repeatable read

串行

日志

redo log

bing log

undo log

索引

普通索引和唯一索引

查询过程

普通索引

当通过条件查询到满足条件的数据，还需要寻找下一条记录，直到找到第一个不满足条件的数据结束

唯一索引

只查找到满足第一条的数据

性能影响

微乎其微

mysql查找记录并不是一行一行查找的，是按页(每个页16kb)，当普通索引查找下一条记录时候,往往在同一个页里，也就是在内存里，所以很快，如果不在同一个页里需要，载入新的页这个有点耗性能

插入过程

change buffer

当更新一个数据页时候，如果页在内存，就直接更新，如果没，在不影响数据一致性的情况下，innodb会将这些更新数据操作缓存在change buffer中，这样就不需要在磁盘读取数据页了，下次查询需要访问整个数据页时候，，将数据页读取内存，执行change buffer中与整个数据页有关的操作

change buffer在内存中copy，也会被写入磁盘

当访问数据页时候，change buffer有数据操作merge保存，后台也有线程定期执行merge，当数据库正常shutdown也会执行merge

如果能够将跟新数据先记录在change buffer，减少读磁盘，语句执行速度快，而且读数据需要占用buffer pool，所以这种方式可以避免占用内存，提升内存使用率

什么时候使用

插入（4,400）

情况

数据页在内存中

唯一索引:找到3,5之间的位置，判断到没有冲突，插入这个值，语句执行结束

普通索引

找到3，5之间的位置，插入这个值，语句结束

数据页不在内存中

唯一索引

读取数据页，判断没有冲突，插入数据，语句执行结束

普通索引

将记录在change buffer，语句执行结束

将数据载入内存和随机读非常耗性能，changge buffer可以减少磁盘访问量

dba反馈某个业务内存命中率突然冲99%降低到了75%，整个系统处于阻塞状态，更新语句全部堵住。原因是业务有大量insert操作，dba在前一天将其中的某个普通索引改成了唯一索引

使用场景

change buffer 只限于普通索引的情况下，change buffer记录越多，收益就越大，对应读多写少的业务非常适用，但是如果更新完数据，马上读取这个数据，这是会触发merge ，这样就增加了change buffer的维护代价

如果是历史数据，可以将唯一索引改为普通索引，然后将change buffer尽量开大，这个确保历史数据的插入速度

redo log

insert into t(id,k) values(id1,k1),(id2,k2)

k1 在内存直接更新内存，

k2不在内存直接更新change buffer

写入redo log 整个过程只更新了两次内存，写了一次磁盘log，还是顺序写入

读取插入条件数据，k1如果在内存直接返回,k2不在内存读取page页，然后merge change buffer生成一个正确的版本返回结果

对比

redo log主要节省的是随写磁盘IO消耗（转成顺序写），change buffer 主要节省的是随机读磁盘IO消耗

问题

count(*)

实现方式

MyISAM

把表的总行数存储在磁盘上，当count(*)效率高，但是该一旦涉及where 就不起作用了

Innodb

需要将数据一行一行的读出来在累计

为什么不将count(*)存储在磁盘

一旦涉及where 都需要重新计算

MVCC innodb返回多少行是不确定的

例如：三个会话 A B C 表里元数据为10000

A开启事物然后查询count(*)

10000

B开启事物 insert

10002

C先insert一条

10001

小结

MyISAM count(*)快，但是不支持事物

show table status -table_rows快但实际不准确，因为这个数据是在采样里预估的

innodb count(*)准确，但是会遍历全表，性能会有问题

不同的count 方法

count(*)

取出行直接加

count(主键)

count(字段)

需要取出字段判断null值

count(1)

效率

count(字段) < count(主键) < count(1) <= count(*)

查询一行也慢

主键查询慢，索引查询慢

show processlist

可以查看到当前的sql 状态

原因

可能有MDL写锁

设置performance_schema=on（有损的，性能会损失10%）

sys.schema_table_lock_waits 可以查看当前阻塞进程

等待flush

select * from t where id=1 lock in share mode

如果刚好有事物执行update where id=1  那么会等待

select * from t where c=50000 limit 1

c上没有索引，每次扫描5万行，虽然单次可能很快，但是如果数据量上涨的化，查询时间是线性上涨的

事物查询

一条记录跟新频率太多

整条在事物里 select * from table where id=1 ,select * from table where lock shard mode 

线程b做100万次更新

得出的结果是 lock 的记录快，因为lock是获取的当前读。直接定位到最后一条记录，而第一条需要按照undo log 日志回滚100万次才可以获取到当前结果

事物 select * from where c=5 for update c没有索引

在RC模式下，锁全表 delete 阻塞  update阻塞   insert 不会被阻塞

在RR模式下，锁全表 del，update， insert 都会被阻塞，为什么insert 会被阻塞，因为在RR模式下对应没有索引的操作。索引上的记录都会被加上gap锁