作者徐晨亮，MySQL DBA，知数堂学员。热衷于数据库优化，自动化运维及数据库周边工具开发，对MySQL源码有一定的兴趣。

一、背景说明

最近有位朋友咨询说为何如此多线程处于Searching rows for update，当时看到这个状态的第一反应就是锁，这里暂且抛开锁不谈，谈一谈为何出现 Searchingrowsforupdate

二、实验环境：

  1. root@mysqldb 10:15:  [xucl]> show create table test1\G
  2. *************************** 1. row ***************************
  3.        Table: test1
  4. Create Table: CREATE TABLE `test1` (
  5.   `a` int(11) NOT NULL,
  6.   `b` varchar(20) DEFAULT NULL,
  7.   PRIMARY KEY (`a`),
  8.   KEY `b` (`b`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  10. 1 row in set (0.00 sec)

  12. root@mysqldb 10:15:  [xucl]> select * from test1;
  13. +---+------+
  14. | a | b    |
  15. +---+------+
  16. | 2 | b    |
  17. | 3 | c    |
  18. | 1 | ccc  |
  19. +---+------+
  20. 3 rows in set (0.00 sec)
大概出现的状态如下所示
Searching rows for update状态初探_java
三、初探过程
简单做了pstack，其中id=2的线程堆栈如下
Searching rows for update状态初探_java_02
从堆栈很明显可以看出，该线程处于锁等待的状态，但为何处于 Searchingrowsforupdate并没有给出明确暗示，可以看到调用的顺序是
  1. mysql_parse
  2. mysqlexecutecommand
  3. Sqlcmdupdate::execute_command
  4. mysql_update
  5. ...
  6. hainnobase::indexread
  7. ...
  8. lockwaitsuspend_thread
  9. ...
废话不多说，咱们直接从 mysql_update切入，该入口函数位置在 sql_update.cc
  1. bool mysql_update(THD *thd,
  2.                   List<Item> &fields,
  3.                   List<Item> &values,
  4.                   ha_rows limit,
  5.                   enum enum_duplicates handle_duplicates,
  6.                   ha_rows *found_return, ha_rows *updated_return)
  7. {
  8. ...
  9. if (used_key_is_modified || order)
  10. {
  11.  /*
  12.           When we get here, we have one of the following options:
  13.           A. used_index == MAX_KEY
  14.           This means we should use full table scan, and start it with
  15.           init_read_record call
  16.           B. used_index != MAX_KEY
  17.           B.1 quick select is used, start the scan with init_read_record
  18.           B.2 quick select is not used, this is full index scan (with LIMIT)
  19.           Full index scan must be started with init_read_record_idx
  20.         */

  22.         if (used_index == MAX_KEY || (qep_tab.quick()))
  23.           error= init_read_record(&info, thd, NULL, &qep_tab, 0, 1, FALSE);
  24.         else
  25.           error= init_read_record_idx(&info, thd, table, 1, used_index, reverse);

  27.         if (error)
  28.           goto exit_without_my_ok;

  30.         THD_STAGE_INFO(thd, stage_searching_rows_for_update);
  31.         ha_rows tmp_limit= limit;
  32. }
  33. ...
debug结果如下
Searching rows for update状态初探_java_03
这里判断条件为 used_index是否等于 MAX_KEY，其中 MAX_KEY为常量， used_index的定义如下：
used_index= get_index_for_order(order, &qep_tab, limit, &need_sort, &reverse);
这里的判断就比较复杂了，本人水平有限，暂时未深入理解优化器的部分，也不展开说明，源码位置在 sql_select.cc，有兴趣的同学可以深入研究一下
函数iskeyused定义如下：
  1. bool is_key_used(TABLE *table, uint idx, const MY_BITMAP *fields)
  2. {
  3.   bitmap_clear_all(&table->tmp_set); //清空tmp_set位图
  4.   table->mark_columns_used_by_index_no_reset(idx, &table->tmp_set); //这里设置位图
  5.   const bool overlapping= bitmap_is_overlapping(&table->tmp_set, fields); //比较索引位置和修改位置是否重合

  7.   // Clear tmp_set so it can be used elsewhere
  8.   bitmap_clear_all(&table->tmp_set);
  9.   if (overlapping)
  10.     return 1; //如果重合返回1
  11. ...
Searching rows for update状态初探_java_04
看到debug的结果变量， used_key_is_modified为true，那么进入如下判断
Searching rows for update状态初探_java_05
然后就进入 stage_searching_rows_for_update状态，也就是我们一开始在show processlist中看到的状态
Searching rows for update状态初探_java_06
而如果我们修改的是其他字段，那么进入的状态便是 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);处打上断点，然后更新
Searching rows for update状态初探_java_07
实验结果符合预期
其他测试结果：
case结果
update主键直接进入 stage_updating
update唯一索引直接进入 stage_updating
update普通二级索引+limit进入 stage_searching_rows_for_update，完成后进入stage_updating
四、总结
最后总结一下：
  1. Searchingrowsforupdate状态出现的要求比较严格，当进行数据更新时，如果更新的字段为当前执行计划用到的索引，并且该索引属于普通二级索引（不能是主键也不能是唯一索引），那么就会出现 Searchingrowsforupdate状态，正因为出现了锁等待，所以能看到这种状态
  2. 如果不是，那么直接进入 Updating状态，这个状态也就是我们经常看到的状态
  3. 出现如上两种状态并且持续时间长，并不是造成数据库性能下降的根本原因，而应该考虑其他原因，如本案例中的锁等待问题
问题点：由于本人对优化器部分研究并不深入，并未列出全部情况，待后续慢慢补充，也欢迎各位读者补充
-The End-
本公众号长期关注于数据库技术以及性能优化，故障案例分析，数据库运维技术知识分享，个人成长和自我管理等主题，欢迎扫码关注。
Searching rows for update状态初探_java_08