mysql记录

近期二刷了极客时间的MySQL实战45讲，对部分知识点进行简单的记录

检测delete不删除磁盘表数据
1，为了方便直接win安装了mysql；
2，通过SHOW VARIABLES LIKE 'datadir';命令找到数据所在文件夹
3，通过存储函数为test库创建20000条数据

CREATE PROCEDURE proc()
BEGIN
declare var int;
set var=0;
while var <= 10000 do
insert into term values(var,'');
set var = var + 1;
end while;
END;

data

4，此时执行delete语句，delete from term where 1=1；文件大小并没有发生改变
5，重现执行步骤三，执行truncate语句，truncate term；ibd文件只剩下96KB

因此批量删除数据时建议用truncate，不仅释放磁盘空间，而且row格式的binlog日志中也是这么一条语句；
缺点，删除无法进行数据恢复；

MDL锁
用于解决或者保证DDL操作与DML操作之间的一致性;
DML操作需要MDL读锁,DDL操作需要MDL写锁；
读写互斥，读读不互斥；

MDL.png
如图开启两个事务通过show processlist命令可以看见MDL锁
MDL.png

MySQL 5.6 版本开始引入的 Online DDL;具体理解就是在更改表结构的时候可以同时进行DML操作；
那还是否有MDL写锁？
当然，只是写锁时间缩短；ALTER语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。

死锁产生与处理
死锁出现的原因是资源的相互等待(例子来源于mysql实战45讲第7,21章)

首先表只事务的隔离级别是可重复读（拥有间隙锁）
CREATE TABLE t (
id int(11) NOT NULL,
c int(11) DEFAULT NULL,
d int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY c (c)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

Deadlock

当执行sessionA,B后可发现出现了死锁
1，session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
2，session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；
3，然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

期间还需注意sessionB block了为什么还能添加间隙锁？
由于加锁的步骤是分两步的，先是间隙锁，后是行锁。间隙锁加成功，而update语句的行锁被block了

当出现死锁以后，有两种策略：
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事 务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

innodb_lock_wait_timeout在InnoDB中，innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现 死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于 在线服务来说，这个等待时间往往是无法接受的。
但是，我们又不可能直接把这个时间设置成一个很小的值，比如1s。这样当出现死锁的时候，确 实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会 出现很多误伤。
所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect的默认值本身就是on。主动死锁检测在发生死锁的时候，是能够快速发 现并进行处理的，但是它也是有额外负担的。
每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级 的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到 CPU利用率很高，但是每秒却执行不了几个事务

Deadlock.png