MySQL锁机制和优化

按锁粒度
全局锁：对整个Database进行加锁，由MySQL的SQL Layer层实现
全局锁是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句、DDL语句和已经更新操作的事务进行提交语句都将被阻塞。
用途：全库的逻辑备份

flush tables with read lock; ## 加全局锁
unlock tables; ### 释放全局锁

表级锁：对某个Table进行加锁，由MySQL的SQL Layer层实现
表级锁又分为表读锁、表写锁、元数据锁、自增锁

lock table 表名称 read(write)， 表名称2 read(write), 其他。。。 ### 格式
lock table test read ### 为表test添加读锁
lock table test write ### 为表test添加写锁
show open tables ### 查看表锁情况
unlock tables ### 释放表锁

元数据锁不需要显示指定，在访问一个表的时候被自动加上，锁的作用是保证读写的正确性；当一个表做增删改查操作的时候，加元数据读锁；当要对表结构进行变更操作的时候，加元数据写锁。
元数据读锁是共享的，互不阻塞，因此可以有多个线程同时对一张表加读锁，保证数据在读取的过程中不会被其他线程修改
元数据写锁是排他的，一个写锁会阻塞其他的写锁和读锁，用来保证变更表结构操作的安全性

自增锁是一种特殊的表级锁，发生在涉及AUTO_INCREMENT列的事务性插入操作时产生

行级锁：对某个Row的索引进行加锁，也可以对索引之间的间隙进行加锁，由存储引擎实现(InnoDB)
按锁定范围
记录锁：锁定索引中的一条记录
劲劲锁住索引记录的一行，在单条索引记录上加锁。记录锁锁住的永远是索引，而非记录本身，即使该表上没有任务显示索引，那么innodb会在后台创建一个隐藏的聚簇索引，那么锁住的就是隐藏的聚簇索引

间隙锁：锁住索引记录中间的值或锁住第一个索引记录前面的值或最后一个索引记录后面的值
劲劲锁住一个索引区间(不包含双端节点)，间隙锁可以防止幻读，保证索引间隙不会被插入数据

临键锁：是索引记录上的记录锁和索引记录之前的间隙锁的组合
相当于记录锁+间隙锁(左开右闭区间，(6,8] ) ，当查询索引中含有唯一属性的时候，临键锁会进行优化，降级为记录锁

插入意向锁：做Insert操作时添加的对记录id的锁
是一种间隙锁，不是意向锁，在Insert操作产生；在多事务同时写入不同的数据到同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待，插入意向锁不会阻止任何锁，对于插入的记录会持有一个记录锁

按功能
行读锁：允许一个事务去读一行，阻止其他事务更新目标行数据，同时阻止其他事务对其加写锁，但不阻止其他事务加读锁
行写锁：允许获得排他锁的事务更新数据，阻止其他事务获取或修改数据，同时阻止其他事务加读锁和写锁

### 对于update、delete、insert语句，innodb会自动给涉及的数据集加写锁
### 对于普通select语句，innodb不会加任何锁
select * from test where id = 4 lock in share mode; ### 添加行读锁
select * from test where id = 4 for update;  ### 添加行写锁

按锁功能
共享锁(S锁、读锁)：加了读锁的记录，允许其他事务在加读锁，方式为 select … lock in share mode
排他锁(X锁、写锁)：加了写锁的记录，不允许其他事务在加读锁或写锁，方式为 select … for update

死锁
死锁指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误

查询最近一次死锁日志

SHOW ENGINE INNODB STATUS;

如何避免死锁
MySQL默认会主动探知死锁，并回滚一个影响最小的事务。等另外一个事务执行完成之后，在重新执行该事务
1.注意程序的逻辑：程序逻辑的顺序交叠，最常见的是交差更新
2.保持事务的轻量：越是轻的事务，占用越少的锁资源，这样发生死锁几率越小
3.提高运行速度：避免使用子查询，尽量使用主键等
4.尽量快提交事务，减少持有锁时间

一条delete语句(delete from tt where uid = 666 )的加锁分析
RC的隔离级别
如果uid为主键，那么就是在uid=666这条记录加锁
如果uid为唯一索引，会先uid=666的记录后，然后唯一索引上的uid=666加上写锁，同时会回表找到主键索引项加写锁
如果uid为普通索引，那么满足所有查询条件的记录都会被加锁，同时这些记录的主键索引项的记录也会被加锁
如果uid没有索引，会把聚簇索引上所有记录都被加上写锁，既不是表锁，也不是在满足条件的记录加锁

RR的隔离级别
如果uid为主键，那么就是在uid=666这条记录加锁
如果uid为唯一索引，会先uid=666的记录后，然后唯一索引上的uid=666加上写锁，同时会回表找到主键索引项加写锁
如果uid为普通索引，会通过uid索引定位到第一条满足条件的记录，在该记录上加写锁，加GAP上的间隙锁，然后加主键索引上记录加写锁，然后返回;继续读取下一条记录，重复进行，直到进行到第一条不满足条件的记录，此时不需要对该记录加写锁，但仍然后需要加间隙锁，最后返回结束
如果uid没有索引，会进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上主键索引内的所有间隙，杜绝所有的并发的其他变更操作

SQL优化
索引优化
表记录很少的情况不需要创建索引
一个表的索引个数不能过多 ：浪费空间，变更操作变慢
频繁更新的字段不建议作为索引
区分度低的字段不建议建索引
在innodb存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段
不建议用无序的值作为索引
尽量创建组合索引，而不是单列索引

LIMIT优化
如果预计查询语句的结果是一条，最好使用limit 1，可以停止全表扫描
如果limit偏移量很大，可以先使用id > offset ，然后limit后面只写rows

select * from (select * from test where id > 100000 and id < 100050 order by id) as t limit 0, 20

子查询优化
可以使用连接查询(join) 代替子查询，连接查询时不需要建立临时表，其速度比子查询快

其他查询优化
小表驱动大表：建议使用left join时，以小表关联大表，因为使用join的话，第一张表示必须全扫描，用小表可以减少扫描次数，而且join的关联字段最好建立索引且字段类型一致
避免全表扫描
避免MySQL放弃索引
where条件中尽量不要使用not in语句，建议使用not exists

执行计划

1.table : explain语句输出的每条记录都对应着某个单表的访问方法，该记录的table列代表着该表的表名

2.id: 查询语句中每出现一个SELECT关键字，Mysql就会为它分配一个唯一的id值

3.select_type （物化表示 缓存或者临时表）

4.partitions : 和分区表有关，一般情况下我们的查询语句的执行计划partitions都为null

5.type : 执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法/访问类型，其中的type列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标；
结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
出现比较多的是
system > const > eq_ref > ref > range > index > all
一般来说，得保证查询至少达到range级别，最好是ref



6.possible_key 与 key ： possible_key列表示在某个查询语句中，对某个表执行单查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引

7.key_len ： 表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度

8.ref : 当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery其中之一时,ref列展示的就是与索引列做等值匹配的是谁

9.rows : 如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描得索引记录行数

10.filtered : 查询优化器预测有多少条记录满，其余的搜索条件

11.extra : 是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句