对于mysql innodb的各种锁,这里不采用堆文字的讲解方式,我们来从现象到本质,先看现象,再来说明其中的道理。
我使用的是mysql 5.5,默认隔离别-可重复读。
建了一张表,如下图:
id
为主键,student_num
上有唯一索引,score
分数上有普通索引,name
和age
没有索引。
建表语句如下:
CREATE TABLE `student` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(100) NOT NULL,
`student_num` varchar(100) NOT NULL,
`age` int(10) NOT NULL COMMENT '年龄',
`score` int(10) NOT NULL COMMENT '分数',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_stu_num` (`student_num`) USING BTREE,
KEY `idx_score` (`score`) USING BTREE
) ENGINE=InnoDB
行锁
现象1:
如上图,左侧执行了一条更新语句,但是事物还未提交。
现在,如果执行红框中的sql,会出现锁等待,需要等待左侧的事物提交,否则,他会等待到超时。
这就是行锁。
行锁,锁的是索引键
现象2:
如上图,尽管查询的不是同一行,但是红框内sql执行的时候还是会出现锁等待,原因就是他们使用了相同的索引键。innodb在实现行锁的时候,并不是在记录行上加的锁,而是在索引上加的。
如下两条sql:
update student set score = 60 where id=1;
update student set score = 60 where id=1 and student_num="X001";
由于都使用了id=1
的主键索引键,所以会出现锁等待。
如果没有索引,变成表锁
现象3:
age
列没有索引,innodb会直接使用表锁(表锁即锁住所有行)。所以即使查询的不是相同的列,还是会锁等待。
即使有索引,也可能变成表锁
现象4:
如上图,student
表中加了一个性别字段sex
,并且sex
加了一个索引。然后表中一共有4条数据,3男1女,然而当执行红框中的sql还是会出现锁等待,why???
另外,如果你将3个男的记录中的任意两条删掉,再做上面的实验,你会惊喜的发现,不会等锁了。
原因在于innodb有个优化器,当你的索引区分度不大的时候,他会直接走全表扫描,根本不走索引。
所以上面的例子就是全表扫秒,导致成了表锁。当你删除,两条男的数据之后,sex
的区分度又好了,成了行锁。
优化器不使用索引选择全表扫描
比如一张order表中有联合索引(order_id, goods_id),在此例子上来说明这个问题是从两个方面来说:
查询字段在索引中 select order_id from order where order_id > 1000,
如果查看其执行计划的话,发现是用use index condition,走的是索引覆盖。查询字段不在索引中 select * from order where order_id > 1000,
此条语句查询的是该表所有字段,有一部分字段并未在此联合索引中,因此走联合索引查询会走两步,首先通过联合索引确定符合条件的主键id,然后利用这些主键id再去聚簇索引中去查询,然后得到所有记录,利用主键id在聚簇索引中查询记录的过程是无序的,在磁盘上就变成了离散读取的操作,假如当读取的记录很多时(一般是整个表的20%左右),这个时候优化器会选择直接使用聚簇索引,也就是扫全表,因为顺序读取要快于离散读取,这也就是为何一般不用区分度不大的字段单独做索引,注意是单独因为利用此字段查出来的数据会很多,有很大概率走全表扫描。
https://www.cnblogs.com/163yun/p/8892324.html
总而言之,加了索引,但是sql执行的时候并没有走索引,所以就变成了表锁。