mysql进阶（锁相关，mvcc , 聚簇索引和非聚簇索引，in和exists）
参考链接：https://github.com/hongwen1993/all/blob/master/database/lock.md
1、
2、锁介绍（锁用来控制不同的事务并发执行的时候能够操作对应的数据行。）
2.1、基于锁的粒度分类：行级锁（innodb）、表级锁（innodb、myIsam）、页级锁（innodb）、记录锁、间隙锁（又称gap）、临键索（又称Next-key lock）
2.2、基于锁的状态分类：意向共享锁、意向排他锁
2.3、基于锁的属性分类：共享锁、排他锁
2.4、innodb加锁是给索引加锁
3、当前读、快照读。
3.1、当前读：顾名思义就是读取当前最新的数据，并且对读取的数据加锁，阻止其他事务同时修改相同记录，避免出现安全问题
使用场景：
3.1.1、update、delete、insert
3.1.2、select ... lock in share mode (主动加共享锁)
3.1.3、select ... for update (主动排他锁)
3.2、快照读：读取的历史数据，意味着innoDB使用多版本控制在某个时间点查询提供数据库的快照 （又称read view）
3.2.1、快照读的实现方式通过多版本控制MVCC机制实现。
3.2.2、innoDB在隔离级别READ COMMITTED和REPEATABLE READ上使用。
3.2.3、READ COMMITED 每次执行select都会生成一个快照（read view）
3.2.4、REPEATABLE READ开启事务后，第一次执行select才会生成快照，而不是事务一开始就生成快照。
4、幻读：如果所有的读取都是快照读，那么不会有幻读问题，如果有快照读也有当前读才会产生幻读问题。
5、MVCC - 多版本并发控制 - 一行数据可能存在多个不同的数据版本
5.1、mysql每张表都有三个默认字段，隐藏id ，事务id，回滚指针（指向上一个对应的历史版本数据）
5.2、说明：innodb存储引擎每次在进行数据插入的时候，数据必须要跟一个索引列绑定在一起，这个索引列有一个选择顺序：如果表有主键，选择主键，如果没有主键，选择唯一键，如果没有唯一键，选择自动生成一个6字节的rowid。
5.3、read view：读视图，当进行快照读的时候会生成一个事物id列表，来保存不同的信息，通过这些信息来做可见性判断
5.3.1、隔离级别READ COMMITTED:每次快照读的时候生成新的readview，隔离级别REPEATABLE READ：第一次执行快照读的时候生成readview，并且之后都是这个版本 , 而针对 READ UNCOMMITED隔离级别的事务来说，由于可以读取到未提交事务修改的记录，所以直接读取事务的最新版本就ok了。
5.3.2、list：生成readview的时候，活跃的id。up_limit_id: 当前活跃id的最小值。lower_limit_id: 尚未分配的下一个事物id
5.3.3、readview的执行规则：
6、聚簇索引：数据在进行插入的时候必须要跟某一个索隐列存储在一起，此时的索引列叫做聚簇索引，其他的索引存储的是聚簇索引的key值，在进行数据查找的时候，先从其他索引中找到key，再通过key去聚簇索引中找数据，这个过程叫回表。
6.1、innodb中既有聚簇索引也有非聚簇索引，myIsam只有非聚簇索引
7、in和exists的优缺点，说到in和exists就不可避免的需要说小表驱动大表原则
7.1 、MySQL Hash Join
且在8.0.18中，还需要一个对等的条件（table1.a=table2.a），才能满足hash join。
在8.0.20中，取消了对等条件的约束，可以全面支持non-equi-join，Semijoin，Antijoin，Left outer join/Right outer join。
hash join的原理概括为：选择占用空间较小的表t1（不一定是行数）作为驱动表，计算其join字段的hash值，在内存中build一个hash table，将t1的join字段的hash值存放至hash table。然后对被驱动表t2的join字段计算hash值，并与内存中的hash table进行查找匹配。
7.2、小表驱动大表原则（外部循环数量要小于内部循环数量）
for (int i = 0; i < 5; i++) { for (int j = 0; j < 100; j++) { } }
7.3、in用法（当查询tab_b表数量小于tab_a表数量则满足小表驱动大表原则，优先使用in），in会把外表和内表做hash连接，in内的子查询结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询效率会受到一定会影响，特别是对于返回结果比较大的子查询。
select * from tab_a where id in (select id from tab_b) 等价于=> for select id_a from tab_b for select * from tab_a where tab_a.id=tab_b.id
7.4、exists用法（当查询tab_a表数量小于tab_b表数量则满足小表驱动大表原则，优先使用exists），exists是对外表作loop循环loop循环再对内表进行查询。
select * from tab_a where id exists (select 1 from tab_b where tab_b.id=tab_a.id) 等价于=> for select * from tab_a for select 1 from tab_b where tab_b.id=tab_a.id
7.5、多表联合查询（left join,right join,inner join,union,union all）,1.相对于子查询连接查询需要创建临时表，但联表不需要查询数据，因此只需要在新表中做一次查询即可，2.表关联是可以利用两个表的索引的，这样查询效率更高，如果是子查询至少第二次查询是没办法使用索引的（MySQL 8.0.18之前），建议少使用子查询，多用联表查询。
8、explain 执行计划key_len详解
key_len表示使用的索引长度，key_len可以衡量索引的好坏，key_len越小 索引效果越好，那么key_len的长度是如何计算的？
常见的列类型长度计算：
9、有时候索引失效，不知道什么原因，可以使用trace工具查看mysql最终是否选择索引。
9.1、开启执行计划分析
‐‐ 开启trace set session optimizer_trace="enabled=on",end_markers_in_json=on;
9.2、查看sql执行计划
-- 解析sql计划 explain select * from origion_goods_uploaded where seller = '张三'; -- 查看执行计划 select * from information_schema.OPTIMIZER_TRACE;
9.3、执行计划trace字段分析
浙公网安备 33010602011771号