网站站外优化怎么做,搜索引擎优化方法与技巧,女生适合做seo吗,河南省建设厅厅长最近在复习mysql的知识点#xff0c;像索引、优化、主从复制这些很容易就激活了脑海里尘封的知识#xff0c;但是在mysql锁的这一块真的是忘的一干二净#xff0c;一点映像都没有#xff0c;感觉也有点太难理解了#xff0c;但是还是想把这块给啃下来#xff0c;于是想通… 最近在复习mysql的知识点像索引、优化、主从复制这些很容易就激活了脑海里尘封的知识但是在mysql锁的这一块真的是忘的一干二净一点映像都没有感觉也有点太难理解了但是还是想把这块给啃下来于是想通过这篇文章把复盘过程中所有的知识点给记录下来特别是实操部分 文章目录 一、mysql的锁介绍共享锁排他锁记录锁(Record Locks)间隙锁临键锁 二、加锁情况实操1. 表无显式主键和索引数据准备不加任何查询条件的查询添加条件查询 2. 表有显式主键无索引数据准备不带任何条件的查询通过id查询 3. 表无显式主键有索引数据准备不带任何条件的查询带普通索引ID的查询带唯一索引的id查询 4.表有显式主键和普通索引数据准备不带任何条件的查询where条件是普通索引字段where条件是主键字段where条件同时包含普通索引字段和主键索引字段 5.表有显式主键和唯一索引数据准备不带条件查询where条件是唯一索引字段where条件是主键where条件是主键和唯一索引 总结 一、mysql的锁介绍
mysql里有一堆锁但是会存在一些概念上的交叉所以这里只列举出常用的或者说面试中常问的锁进行介绍如下
共享锁
共享锁又叫读锁S锁加了共享锁的数据还可以被其他事务加共享锁但是不能被其他事务加排他锁主要用于保证数据在读取过程中的一致性。可通过lock in share mode显示的给sql加上共享锁
select * from t lock in share mode;排他锁
排他锁又叫写锁X锁加了排他锁的数据不能被其他事务再加排他锁或者共享锁直到该排他锁被释放可以通过for update显示的给sql加排他锁当然实际工作中不会使用for updatemysql在执行update、insert、delete等DML操作的时候会自动加排他锁
select * from t for update;记录锁(Record Locks)
记录锁也可以理解成行锁作用在索引上只锁某一行记录
间隙锁
间隙锁锁定的是索引记录锁之间的间隙或者是索引最小值之前后者最大值之后的间隙只在RR级别以上被使用只阻止其他事务插入到间隙中他们不阻止其他事务在同一个间隙上获得间隙锁
临键锁
临键锁记录锁间隙锁
二、加锁情况实操 约定 关闭事务自动提交我们需要在事务里观察加锁情况set autocommit0使用show engine innodb status\G观察锁的情况查看mysql关于锁日志的输出是否打开(默认关闭)show variables like ‘innodb_status_output%’;打开开关set global innodb_status_output_lockson; set global innodb_status_outputon;只演示mysql的默认隔离界别RR下锁的情况RC隔离级别大部分加的都是行锁mysql的表一定会有聚簇索引的有主键使用主键作为聚簇索引没主键使用唯一且非空列作为聚簇索引否则使用自己维护的rowid作为聚簇索引mysql的锁都是加在索引上的 1. 表无显式主键和索引
数据准备
create table t(id int default null,name char(20) default null);
insert into t values(10,10),(20,20),(30,30);不加任何查询条件的查询
sql
begin;
select * from t for update;
show engine innodb status\G图示 分析 表t没有主键没有唯一非空列所以会创建一条聚簇索引查询的是全表没有限制任何条件所以加的是聚簇索引的临键锁由于聚簇索引是rowid所以范围这里我们看不懂由于没有主键和索引所以加上条件的查询和不加条件是一样的这里就不再重复展示了
添加条件查询
sql
begin;
select * from t where id 10 for update;
show engine innodb status\G图示同上分析 可以发现从结果上课与不加条件加锁情况是一致的都是加的聚簇索引的临键锁限制了条件和全表查询一样是因为mysql锁是加在索引上的并不是加在数据10上的不加的话理论上在插入一条id10的记录也是可以的这一就出现幻读了
2. 表有显式主键无索引
数据准备
create table t2(id int primary key not null,name char(20) default null);
insert into t2 values(10,10),(20,20),(30,30);不带任何条件的查询
sql
begin;
select * from t2 for update;
show engine innodb status\G图示 分析 结果与1中一样区别在于这里的id是主键聚簇索引就是id了在图里就能显示的看到临建锁的范围了虽然是16进制的转换后就是表里对应的102030
通过id查询
sql
begin;
select * from t2 where id 10 for update;
show engine innodb status\G图示 分析 可以看到此情况只加了id的记录锁因为id就是聚簇索引锁住这一行记录就可以了
3. 表无显式主键有索引
数据准备
create table t3(id int default null,name char(20) default null);
create index idx_id on t3(id);
insert into t3 values(10,10),(20,20),(30,30);不带任何条件的查询
与1.2类似加的都是聚簇索引的临键锁
带普通索引ID的查询
sql
begin;
select * from t3 where id 10 for update;
show engine innodb status\G图示 分析 如图会加聚簇索引的记录所id的临建锁id的间隙锁锁的范围如上图由此可见我们此时可以插入30之后的数据不能插入10之前的数据如下图
带唯一索引的id查询
sql
create table t4(id int default null,name char(20) default null);
create unique index idx_id on t4(id);
insert into t4 values(10,10),(20,20),(30,30);
begin;
select * from t4 where id 10 for update;
show engine innodb status\G图示 分析 因为是唯一索引且可以为空所以mysql还会创建聚簇索引所以此时加的是唯一索引id的记录锁聚簇索引
4.表有显式主键和普通索引
数据准备
create table t5(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into t5 values(10,10),(20,20),(30,30);不带任何条件的查询
sql
begin;
select * from t5 for update;
show engine innodb status\G图示 分析 主键id的记录锁(锁表里的每行记录)普通索引name的临键锁
where条件是普通索引字段
sql
begin;
select * from t5 where name10 for update;
show engine innodb status\G图示 分析 主键id的记录锁(锁id10一条记录)、普通索引name的临键锁范围(-∞,10]普通索引name的间隙锁(10,20)
where条件是主键字段
sql
begin;
select * from t5 where id10 for update;
show engine innodb status\G图示 分析 查询条件是主键的话就简单了直接加id的记录锁即可
where条件同时包含普通索引字段和主键索引字段
sql
begin;
select * from t5 where id10 and name10 for update;
show engine innodb status\G分析 与单查主键一致都只是上主键id的记录锁
5.表有显式主键和唯一索引
数据准备
create table t6(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into t6 values(10,10),(20,20),(30,30);不带条件查询
sql
begin;
select * from t6 for update;
show engine innodb status\G图示 分析 与普通索引类似会加主键的记录锁锁定表里的每条记录然后加唯一索引的临键锁范围是(-∞,10],(10,20],(20,30],(30,10)
where条件是唯一索引字段
sql
begin;
select * from t6 where name10 for update;
show engine innodb status\G图示 分析 两个行锁分别是主键索引的行锁和唯一索引的行锁
where条件是主键
sql
begin;
select * from t6 where id10 for update;
show engine innodb status\G分析 只会加主键的行锁
where条件是主键和唯一索引
sql
begin;
select * from t6 where id10 and name10 for update;
show engine innodb status\G分析 与只加主键一致还是只会加主键的行锁一般涉及到主键的查询都会只锁主键的行锁 总结
至此mysql加锁的分析就结束了其实挺绕的实际总是与自己想的不一致但是要秉持一个原则就是mysql的锁除了保证并发事务的情况下对数据的安全读写外还会用来解决幻读问题有时候从幻读的角度出发那么也就理解了为什么会这样加锁了
