数据库相关问题3

题目 01- 请你说一说 MySQL 的锁机制

分类

从锁的粒度来分。分为全局锁，表锁，和行锁。
从锁的功能分，分为读锁（共享锁），写锁（排他锁）

1.全局锁 锁库

整库只读，一般用于整库备份
加锁 Flush tables with read lock;

2.表锁 锁表

2.1 表读锁 lock table 表名 read;
当前事务可select，不可delete update insert，其他事务可以select。
2.2 表写锁 lock table 表名write;
当前事务select,insert,delete,update都可以，其他事务都不可以。
（加锁的影响有两方面，一个是对当前事务的影响，另一个是对其他事务的影响，其他事务的权限一定会小于等于当前事务。）
2.3 元数据锁，不需要显式指定，Mysql默认支持
select,insert,delete,update加读锁
Crate，alter表结构加写锁

3.行锁 锁行

存储引擎层实现，innodb存储引擎的行锁锁的是索引，如果没有索引，行锁会退化成表锁。从这个角度也可以体现索引的优点。
innodb 行级锁

1. 记录锁，锁索引树上的一个节点。
2. 间隙锁，锁索引节点的前后，开区间。间隙锁可以解决幻读问题，因此RC隔离级别没有间隙锁。
3. 临键，锁索引的前后，左开右闭区间
4. 插入意向锁，间隙锁的一种，insert时产生

Innodb级别的表锁：意向锁
当前事务去update/delete某行记录时，会加行级写锁和表级意向写锁。
加行级写锁，其他事务不可读该行。加意向写锁，其他事务可以去读其他行

4.读锁

当前事务可select，不可delete update insert，其他事务可select
select * from t where id =1 lock in share mode;
（INNODB引擎，RC,RR这两种隔离级别下，普通select语句（快照读）是不会加读锁的，串行化时会加读锁）

5. 写锁

当前事务可以select,insert,delete,update，其他事务都不可以
select *from t where id =1 lock for update;

死锁

死锁出现的标志是某条sql语句无限等待某个锁，注意这里的无限等待指的是一个对业务场景不可忍受的时间。实际上mysql的死锁检测机制会在一段时间后放弃获取锁并返回超时。
死锁出现的原因是循环等待，事务A持有锁1等待锁2，事务B持有锁2等待锁1。

排查

SHOW ENGINE INNODB STATUS;

实例

delete from tt where uid = 666的锁定情况 ;

1. uid是主键，在主键索引上加行级写锁（记录锁）。
2. uid是非主键唯一索引，在uid和主键索引上都加行级写锁（记录锁）。
3. uid是非唯一索引，RC隔离级别，在uid和主键索引的若干条记录上都加行级写锁（记录锁）。RR级别及以上，都要满足可重复读，因此会多出一个uid上的间隙锁。
4. uid不是索引，RC隔离级别，会先在全表加行级写锁（记录锁），然后不满足的条件释放锁，满足的保留。RR隔离级别及以上，类似上一条，也会多出间隙锁。

题目 02- 请你说一说 MySQL 的 SQL 优化

SQL优化一般要通过执行计划explain来分析现有语句的状态，然后做出判断。
explian执行计划得出的计划比较重要的参数是type，
type=ref代表，通过使用非唯一索引避免了全表扫描

tyep=index代表要查询的列在索引中，但查找的过程还是走了全表扫描