开发者社区 问答 正文

MySQL5.7中explain能正确解释update吗?

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> 

展开
收起
海阔天空94 2022-08-15 16:03:33 440 分享 版权
阿里云 AI 助理回答

在MySQL中,EXPLAIN的结果显示了查询优化器如何执行SQL语句的计划。这里有几个关键点需要理解:

  1. 索引选择

    • 对于UPDATE语句 explain update users set rank=rank-1 where name="zhangsan",虽然name字段没有索引,但MySQL选择了使用PRIMARY KEY(即id)作为执行计划中的key。这是因为更新操作通常需要锁定行以避免并发修改问题,而主键是最适合进行行级锁定的索引。即使查询是基于name字段,优化器可能决定先通过扫描表找到匹配的name值对应的id,然后利用这些id来高效地定位和更新行,这过程中实际上涉及到了两次查找:先全表扫描找到ID,再根据ID更新,因此它显示为index类型(全索引扫描)。
  2. 锁表现象

    • 当执行更新操作时,MySQL为了保证数据的一致性,可能会采取不同的锁策略。如果表没有合适的索引用于直接定位要更新的行,或者优化器判断全表扫描更优,那么在某些隔离级别下(如默认的REPEATABLE READ),可能会对整个表进行锁定,以防止其他事务并发修改数据,导致所谓的“锁表”现象。尽管在这个例子中使用了PRIMARY KEY进行更新,但如果表数据量大且更新条件不频繁,也可能出现较明显的锁竞争或性能影响。
  3. 查询与更新的区别

    • 对于SELECT语句 explain select * from users where name="zhangsan",因为name字段没有索引,所以优化器选择了ALL作为访问类型,这意味着它将执行全表扫描来查找匹配的行。这与更新操作不同,更新操作可能尝试利用现有索引来减少锁定范围或提高效率。

综上所述,MySQL在处理这类查询时,会根据多种因素(如索引可用性、数据分布、查询成本等)来决定最优的执行计划。尽管name字段没有索引,但在更新操作中,优化器可能利用主键索引来辅助锁定和更新过程,但这并不意味着完全避免了锁表行为,尤其是在高并发或大数据量场景下。对于频繁按name查询或更新的情况,考虑添加针对name的索引可以显著提升性能并减少锁的范围。

有帮助
无帮助
AI 助理回答生成答案可能存在不准确,仅供参考
0 条回答
写回答
取消 提交回答