Mysql-由浅入深论行锁

对于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执行的时候并没有走索引，所以就变成了表锁。