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<br>
client断开会自动释放
例如
lock tables t1 read, t2 write<br>
其他线程 写t1,读t2都会被阻塞
在unlock tables前 当前线程只能读t1 和 读写t2，不可以写t1
MDL锁
metadata lock<br>
当访问一个表的时候会自动加上，试想当一个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<br>
ALTER TABLE tbl_name NOWAIT add column ...<br>ALTER TABLE tbl_name WAIT N add column ...
如果访问不频繁要避免长事物，如果长事物要避免 做dml，等不频繁的时候在做
问题
online ddl
1. 拿MDL写锁<br>2. 降级成MDL读锁<br>3. 真正做DDL<br>4. 升级成MDL写锁<br>5. 释放MDL锁
1.2.3.5过程不互斥，4的过程比较耗时，这期间表可以正常读写
全局锁
Flush tables with read lock（FTWRL）
当执行全局锁的时候，整个数据库处于只读状态，所有写，和建表语句等都是阻塞的
一般情况不会使用
测试可以使用事物写两个sql语句，开启A事物，不提交，开启B事物 提交，看B是否阻塞，在commit A看B是否执行
mysql 抖动一下
分支主题
事物
隔离级别
读已提交read committed<br>
读未提交read uncommitted<br>
可重复读repeatable read<br>
串行
日志
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%）<br>
sys.schema_table_lock_waits 可以查看当前阻塞进程<br>
等待flush
select * from t where id=1 lock in share mode<br><br>
如果刚好有事物执行update where id=1 那么会等待
select * from t where c=50000 limit 1<br>
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 条评论
下一页
