mysql
2019-09-05 18:56:00 24 举报AI智能生成
mysql 基础知识
作者其他创作
大纲/内容
锁
行锁
当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 ...
ALTER TABLE tbl_name WAIT N add column ...
如果访问不频繁要避免长事物，如果长事物要避免 做dml，等不频繁的时候在做
问题
online ddl
1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放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锁
0 条评论
下一页
