MySQL底层原理二之锁机制

叶子节点是有序的，在往里面存储数据的时候，假设这个数据是int类型，在业务允许的范围之内且允许自增的情况下都要自增

为什么需要自增

因为有序和分裂，

• 叶分裂的过程

假设这个叶子节点，只能存4条记录，存满了之后，还想往里面插入一条14的记录，因为叶子节点一定要保证有序，14在13和15之间，这个节点已经满了怎么办，把一个节点分裂成2个节点

然后把14放入进去，

分裂了之后，要保证上层节点有指针指向它，可能需要增加一个key值，上层节点若也满了，它也需要进行分裂。

往叶子节点里面插入一条数据，它会影响上面不同层数节点的变化，这就是页分裂的过程。

除了分裂，还有一个叶合并，比如1个节点1KB，另一个节点3KB，2个节点需要合并成一个节点4KB。

叶分裂、叶合并管理都是比较麻烦的。

如果是自增的话，直接后面追加就可以了，前面的不用管，就没有叶分裂、叶合并问题了。

但是会涉及到另外一个问题：索引的维护。

插入大量数据的时候，会导致性能变低，很多时间都浪费在索引的维护上面了。

在实际删除一条记录的时候，不是把磁盘里的数据真的删除了，而是给一个删除的标记，当有新数据来了之后可以往里面补全的。

mysql锁

• 乐观锁/悲观锁

  cas就是乐观锁，加一个version字段，通过版本标识；悲观锁是mysql自带的排他锁

• 按照锁粒度划分

  

按照锁定粒度分为表锁和行锁，意向锁和自增锁都是表锁，间隙锁、临键锁、记录锁全部都属于行锁。一般来说，聊锁的时候，都是聊锁的粒度问题。

• 按照锁的方式划分

  

  只有共享锁之间是兼容的，其他都不兼容。

数据库里面经常会发生锁的问题,怎么去排查锁?

锁一般情况下是不需要人为加的，人为加的话，常用的基本只有两种：

• select for update
• select lock in share mode

锁的作用

为了满足事务隔离性，解决对应的一个并发问题，最重要保证数据的一致性，所以聊锁的时候，必不可少的要牵涉到事务，innodb存储引擎支持事务，myisam不支持事务。

查看当前innodb存储引擎的状态

show engine innodb status \G;

这里有一个事务模块

• 开启输出锁信息

set global innodb_status_output_locks=1;

• 设置手动提交事务

• 开启一个事务并做一个查询操作

  

  现在就一个事务，查询下事务的状态

TRANSACTION 143222, ACTIVE 125 sec，这个事务活跃了125秒。

2 lock struct(s), heap size 1136, 3 row lock(s) 两个锁结构，3个行锁。

TABLE LOCK table test .rental  trx id 143222 lock mode IX 这是一个表锁，rental这个表被这个事务 143222锁住了， IX表示意向锁。

RECORD LOCKS space id 7753 page no 3 n bits 72 index PRIMARY of table test .rental  trx id 143222 lock_mode X 这是一个行锁。如果这个表没有主键，这里就会显示GEN_CLUST_INDEX，这是一个聚簇索引，

聚簇索引clustered index的创建

• 如果定义了 primary key，MySQL则会使用该primary key作为clustered index；
• 如果没有定义 primary key，MySQL会使用第一个字段不为空并且是 unique index作为clustered index；
• 如果db table既没有primary key，又没有满足条件的unique key，MySQL则会创建一个名为 GEN_CLUST_INDEX的clustered index。

表示当前这个事务在这个表的聚簇索引上加了一个X锁，3-72之间也表明这是一个间隙锁，锁定是某个范围。

共有2个锁结构，一个是表锁，一个是行锁以及三个行锁的情况。

监控到这些信息之后，就可以对当前的锁做一个基本的判断了。

关于这几种类型的锁，最关键的不是把这些概念搞明白，而是要了解每种锁所对应的情况。

如果遇到死锁怎么去排查？

死锁还是这么排查，在这个事务模块里面会看到deadlock，这里演示下死锁的产生过程，

• 开启事务1

  

• 再开启一个窗口，开启事务2

• 开启了之后，查看当前锁的情况

  

此时还没有锁，

• 在事务1里面加一个锁

  

• 事务2里面也加一个锁

  

两个事务分别都查询出来了

• 在第一个事务里面查看状态

  

  可以看到两个事务的情况，这两个事务分别都加了行锁，

事务1加了X行锁，不是间隙锁，

事务2也加了行锁，

• 在第一个事务里面查询20，查不到

因为事务2中已经加了这个锁了，所以事务1，只能等待事务2释放行锁，

查看锁状态，可以看到事务1里面有一个等待状态的行锁

• 第二个事务查10

显示死锁状态 （Deadlock）了。

• 总结上述过程

  

  1、事务1对10的这条记录加锁

  2、事务2对20的这条记录加锁

  3、事务1对20的这条记录加锁，它能够加上锁的前提，是需要事务2释放了20这条记录的行锁，事务1才能加上锁，所以事务1需要等待事务2释放了20这条记录的行锁

  4、事务2对10的这条记录加锁，同理，事务2也需要等待事务1释放了10这条记录上的行锁，但此时事务2检测到死锁状态，事务2抛出异常，事务2被回滚，事务2就会释放对记录20上的锁，事务1得以对记录20加锁，

  5、所以事务1查询20的这条记录查询出来了

再出现事务记录就可以看到检测到死锁的描述了

事务2为了避免或解除死锁问题，rollback了

线上再出现锁竞争或死锁排查问题就可以协查了。

show profiles

显示当前这个sql语句在执行每个步骤的时候花了多久。开启 、权限验证、打开表、初始化、系统锁，把每个操作的时间做一个显示，当前sql语句执行的一个具体状况，但这些东西在beta版本可能被淘汰掉。

https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
复制代码

使用performance_schema替换show profiles

show databases;可以看到 performance_schema这个数据库，

切换到这个数据库，可以看到里面有87张表，存储的都是最基本的监控信息。

select digest_text,avg_timer_wait from events_statements_summary_by_digest order by count_star;

这里可以看到所有的sql的执行过程，performance_schema会进行实际数据的统计工作，帮你去做监控，但要开启的话就需要占用资源，当你需要监控某些mysql性能参数的时候建议开启。

sql调优的实例

这是一张城市信息表，

里面有19800条数据，按这个列完整的建索引的话，会占用很多空间，如何进行优化？

• 看下每个城市大概有多少个重复值

  

重复的很多，如果用全局索引创建的话，效率一定很低

取城市名称的前7位字符创建索引，而不是用整串字符去创建索引，既可以提高检索效率，又不会浪费太多空间

alter table citydemo add key(city(7));