name字段没有索引,但是会提示走PRIMARY索引,但是也确实锁表了,有大佬能解释一下MySQL的逻辑吗?
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL DEFAULT '',
`mobile` char(11) NOT NULL DEFAULT '',
`rank` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `mobile` (`mobile`),
KEY `rank` (`rank`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
explain update users set rank=rank-1 where name="zhangsan"
mysql> explain update users set rank=rank-1 where name="zhangsan";
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | UPDATE | users | NULL | index | NULL | PRIMARY | 4 | NULL | 7 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)
mysql> explain select * from users where name="zhangsan";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)
mysql>
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。
在MySQL中,EXPLAIN
的结果显示了查询优化器如何执行SQL语句的计划。这里有几个关键点需要理解:
索引选择:
UPDATE
语句 explain update users set rank=rank-1 where name="zhangsan"
,虽然name
字段没有索引,但MySQL选择了使用PRIMARY KEY
(即id
)作为执行计划中的key
。这是因为更新操作通常需要锁定行以避免并发修改问题,而主键是最适合进行行级锁定的索引。即使查询是基于name
字段,优化器可能决定先通过扫描表找到匹配的name
值对应的id
,然后利用这些id
来高效地定位和更新行,这过程中实际上涉及到了两次查找:先全表扫描找到ID,再根据ID更新,因此它显示为index
类型(全索引扫描)。锁表现象:
REPEATABLE READ
),可能会对整个表进行锁定,以防止其他事务并发修改数据,导致所谓的“锁表”现象。尽管在这个例子中使用了PRIMARY KEY
进行更新,但如果表数据量大且更新条件不频繁,也可能出现较明显的锁竞争或性能影响。查询与更新的区别:
SELECT
语句 explain select * from users where name="zhangsan"
,因为name
字段没有索引,所以优化器选择了ALL
作为访问类型,这意味着它将执行全表扫描来查找匹配的行。这与更新操作不同,更新操作可能尝试利用现有索引来减少锁定范围或提高效率。综上所述,MySQL在处理这类查询时,会根据多种因素(如索引可用性、数据分布、查询成本等)来决定最优的执行计划。尽管name
字段没有索引,但在更新操作中,优化器可能利用主键索引来辅助锁定和更新过程,但这并不意味着完全避免了锁表行为,尤其是在高并发或大数据量场景下。对于频繁按name
查询或更新的情况,考虑添加针对name
的索引可以显著提升性能并减少锁的范围。