导读
作者徐晨亮,MySQL DBA,知数堂学员。热衷于数据库优化,自动化运维及数据库周边工具开发,对MySQL源码有一定的兴趣。
一、背景说明
最近有位朋友咨询说为何如此多线程处于Searching rows for update,当时看到这个状态的第一反应就是锁,这里暂且抛开锁不谈,谈一谈为何出现Searching rows for update
二、实验环境:
root@mysqldb 10:15: [xucl]> show create table test1G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `a` int(11) NOT NULL, `b` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) root@mysqldb 10:15: [xucl]> select * from test1; +---+------+ | a | b | +---+------+ | 2 | b | | 3 | c | | 1 | ccc | +---+------+ 3 rows in set (0.00 sec)
大概出现的状态如下所示:
三、初探过程
简单做了pstack,其中id=2的线程堆栈如下:
从堆栈很明显可以看出,该线程处于锁等待的状态,但为何处于Searching rows for update
并没有给出明确暗示,可以看到调用的顺序是
-
mysql_parse
-
mysql_execute_command
-
Sql_cmd_update::execute_command
-
mysql_update
-
…
-
ha_innobase::index_read
-
…
-
lock_wait_suspend_thread
-
…
废话不多说,咱们直接从mysql_update
切入,该入口函数位置在sql_update.cc
bool mysql_update(THD *thd, List<Item> &fields, List<Item> &values, ha_rows limit, enum enum_duplicates handle_duplicates, ha_rows *found_return, ha_rows *updated_return) { ... if (used_key_is_modified || order) { /* When we get here, we have one of the following options: A. used_index == MAX_KEY This means we should use full table scan, and start it with init_read_record call B. used_index != MAX_KEY B.1 quick select is used, start the scan with init_read_record B.2 quick select is not used, this is full index scan (with LIMIT) Full index scan must be started with init_read_record_idx */ if (used_index == MAX_KEY || (qep_tab.quick())) error= init_read_record(&info, thd, NULL, &qep_tab, 0, 1, FALSE); else error= init_read_record_idx(&info, thd, table, 1, used_index, reverse); if (error) goto exit_without_my_ok; THD_STAGE_INFO(thd, stage_searching_rows_for_update); ha_rows tmp_limit= limit; } ...
debug结果如下:
这里判断条件为used_index
是否等于MAX_KEY
,其中MAX_KEY
为常量,used_index
的定义如下:
used_index= get_index_for_order(order, &qep_tab, limit, &need_sort, &reverse);
这里的判断就比较复杂了,本人水平有限,暂时未深入理解优化器的部分,也不展开说明,源码位置在sql_select.cc
,有兴趣的同学可以深入研究一下
函数is_key_used定义如下:
bool is_key_used(TABLE *table, uint idx, const MY_BITMAP *fields) { bitmap_clear_all(&table->tmp_set); //清空tmp_set位图 table->mark_columns_used_by_index_no_reset(idx, &table->tmp_set); //这里设置位图 const bool overlapping= bitmap_is_overlapping(&table->tmp_set, fields); //比较索引位置和修改位置是否重合 // Clear tmp_set so it can be used elsewhere bitmap_clear_all(&table->tmp_set); if (overlapping) return 1; //如果重合返回1 ...
看到debug的结果变量,used_key_is_modified
为true,那么进入如下判断
然后就进入stage_searching_rows_for_update
状态,也就是我们一开始在show processlist中看到的状态
而如果我们修改的是其他字段,那么进入的状态便是Updating
,对应的源码为
if (used_key_is_modified || order) { ... } ... thd->count_cuted_fields= CHECK_FIELD_WARN; thd->cuted_fields=0L; THD_STAGE_INFO(thd, stage_updating); ...
废话不多说,我们来测试验证一下THD_STAGE_INFO(thd, stage_updating);
处打上断点,然后更新数据
实验结果符合预期
其他测试结果:
case | 结果 |
---|---|
update主键 | 直接进入stage_updating |
update唯一索引 | 直接进入stage_updating |
update普通二级索引+limit | 进入stage_searching_rows_for_update,完成后进入stage_updating |
四、总结
最后总结一下:
-
Searching rows for update
状态出现的要求比较严格,当进行数据更新时,如果更新的字段为当前执行计划用到的索引,并且该索引属于普通二级索引(不能是主键也不能是唯一索引),那么就会出现Searching rows for update
状态,正因为出现了锁等待,所以能看到这种状态 -
如果不是,那么直接进入
Updating
状态,这个状态也就是我们经常看到的状态 -
出现如上两种状态并且持续时间长,并不是造成数据库性能下降的根本原因,而应该考虑其他原因,如本案例中的锁等待问题
作者寄语:由于本人对优化器部分研究并不深入,并未列出全部情况,待后续慢慢补充,也欢迎各位在留言区补充
END
扫码加入MySQL技术Q群
(群号:529671799)
需要了解更多数据库技术:Searching rows for update状态初探,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/818146.html