1. 首页
  2. IT资讯

随笔MySQL:Searching rows for update状态解析

欢迎关注我的《深入理解MySQL主从原理 32讲 》,如下:

如果图片不能显示可查看下面链接:
https://www.jianshu.com/p/d636215d767f

1、限制条件

一般不能是唯一键和主键,也不能是全表,代码如下:

  if (used_index != MAX_KEY) //不能是唯一键(主键) 和 全表     { // Check if we are modifying a key that we are used to search with:      used_key_is_modified= is_key_used(table, used_index, table->write_set);//通过写位图write_set 进行确认 查询的条件和修改的条件相同    }  

2、进入状态

进入stage_searching_rows_for_update状态

  THD_STAGE_INFO(thd, stage_searching_rows_for_update);          ha_rows tmp_limit= limit;          IO_CACHE *tempfile= (IO_CACHE*) my_malloc(key_memory_TABLE_sort_io_cache,                                                    sizeof(IO_CACHE),                                                    MYF(MY_FAE | MY_ZEROFILL));  

3、临时文件使用

创建MY开头的临时文件,在tmp目录下,扫描行加入到临时文件中,供后面实际的update操作使用,会进入实际的update操作会进入stage_updating状态,如下:

         if (open_cached_file(tempfile, mysql_tmpdir,TEMP_PREFIX,                               DISK_BUFFER_SIZE, MYF(MY_WME)))//打开一个MY临时文件          {            my_free(tempfile);            goto exit_without_my_ok;          }          while (!(error=info.read_record(&info)) && !thd->killed)          {            thd->inc_examined_row_count(1);//扫描增加             bool skip_record= FALSE;            if (qep_tab.skip_record(thd, &skip_record))  ...  

4、测试总结:

  mysql> show create table test0820;  +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | Table    | Create Table                                                                                                                                                                                                                                    |  +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | test0820 | CREATE TABLE `test0820` (    `id` int(11) NOT NULL,    `name` varchar(20) DEFAULT NULL,    `name1` varchar(20) DEFAULT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `idx_u_test` (`name1`),    KEY `name` (`name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)  
  • 主键ID更新不触发
  • 唯一键idx_u_test更新不触发
  • 普通索引name更新触发

如果update执行计划出现Using temporary 则会使用stage_searching_rows_for_update。

  mysql> desc   update test0820 set name1='7' where name1='5';  +----+-------------+----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+  | id | select_type | table    | partitions | type  | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |  +----+-------------+----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+  |  1 | UPDATE      | test0820 | NULL       | range | idx_u_test    | idx_u_test | 63      | const |    1 |   100.00 | Using where |  +----+-------------+----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+  1 row in set (2.58 sec)  mysql> desc   update test0820 set name='7' where name='5';  +----+-------------+----------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+  | id | select_type | table    | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra                        |  +----+-------------+----------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+  |  1 | UPDATE      | test0820 | NULL       | range | name          | name | 63      | const |    1 |   100.00 | Using where; Using temporary |  +----+-------------+----------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+  1 row in set (1.91 sec)  mysql> desc   update test0820 set id=2  where id=1;  +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+  | id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |  +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+  |  1 | UPDATE      | test0820 | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |  +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+  1 row in set (2.30 sec)  

5、stage_searching_rows_for_update状态扫描数据已经加锁,因此很容易测试这种情况

栈帧:

  #0  0x00007ffff7bd368c in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0  #1  0x0000000001b2f921 in os_event::wait (this=0x7ffee0e418e8) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/include/os0event.h:156  #2  0x0000000001b2f269 in os_event::wait_low (this=0x7ffee0e418e8, reset_sig_count=1)      at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/os/os0event.cc:131  #3  0x0000000001b2f692 in os_event_wait_low (event=0x7ffee0e418e8, reset_sig_count=0)      at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/os/os0event.cc:328  #4  0x0000000001af0c4b in lock_wait_suspend_thread (thr=0x7ffee0e42ed0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0wait.cc:387  #5  0x0000000001bb6de8 in row_mysql_handle_errors (new_err=0x7fffec5eb7bc, trx=0x7fffd7804080, thr=0x7ffee0e42ed0, savept=0x0)      at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:1312  #6  0x0000000001bf9ed6 in row_search_mvcc (buf=0x7ffee097fb40 "377", mode=PAGE_CUR_GE, prebuilt=0x7ffee0e42730, match_mode=1, direction=0)      at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:6318  #7  0x0000000001a53113 in ha_innobase::index_read (this=0x7ffee0952030, buf=0x7ffee097fb40 "377", key_ptr=0x7ffee0a2f6d0 "", key_len=63, find_flag=HA_READ_KEY_EXACT)      at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9536  #8  0x0000000000f933c2 in handler::index_read_map (this=0x7ffee0952030, buf=0x7ffee097fb40 "377", key=0x7ffee0a2f6d0 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)      at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.h:2942  #9  0x0000000000f83dac in handler::ha_index_read_map (this=0x7ffee0952030, buf=0x7ffee097fb40 "377", key=0x7ffee0a2f6d0 "", keypart_map=1,       find_flag=HA_READ_KEY_EXACT) at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:3248  #10 0x0000000000f8e844 in handler::read_range_first (this=0x7ffee0952030, start_key=0x7ffee0952118, end_key=0x7ffee0952138, eq_range_arg=true, sorted=true)      at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:7750  #11 0x0000000000f8c775 in handler::multi_range_read_next (this=0x7ffee0952030, range_info=0x7fffec5ec370)      at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:6817  #12 0x0000000000f8d68d in DsMrr_impl::dsmrr_next (this=0x7ffee09524a0, range_info=0x7fffec5ec370) at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:7204  #13 0x0000000001a6689a in ha_innobase::multi_range_read_next (this=0x7ffee0952030, range_info=0x7fffec5ec370)      at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:22211  #14 0x00000000017bdbd8 in QUICK_RANGE_SELECT::get_next (this=0x7ffee0e40250) at /mysqldata/percona-server-locks-detail-5.7.22/sql/opt_range.cc:11237  #15 0x00000000014e27f5 in rr_quick (info=0x7fffec5ec870) at /mysqldata/percona-server-locks-detail-5.7.22/sql/records.cc:399  #16 0x000000000168c103 in mysql_update (thd=0x7ffee0000c00, fields=..., values=..., limit=18446744073709551615, handle_duplicates=DUP_ERROR,       found_return=0x7fffec5ecbd8, updated_return=0x7fffec5ecbd0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_update.cc:691  #17 0x0000000001692e40 in Sql_cmd_update::try_single_table_update (this=0x7ffee0006bc0, thd=0x7ffee0000c00, switch_to_multitable=0x7fffec5ecc7f)      at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_update.cc:2896  #18 0x000000000169338d in Sql_cmd_update::execute (this=0x7ffee0006bc0, thd=0x7ffee0000c00) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_update.cc:3023  #19 0x00000000015cc801 in mysql_execute_command (thd=0x7ffee0000c00, first_level=true) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3756  #20 0x00000000015d2fde in mysql_parse (thd=0x7ffee0000c00, parser_state=0x7fffec5ee600) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901  #21 0x00000000015c6b72 in dispatch_command (thd=0x7ffee0000c00, com_data=0x7fffec5eed70, command=COM_QUERY)      at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490  #22 0x00000000015c58ff in do_command (thd=0x7ffee0000c00) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021  #23 0x000000000170e578 in handle_connection (arg=0x6795460) at /mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312  #24 0x0000000001945538 in pfs_spawn_thread (arg=0x6947660) at /mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190  #25 0x00007ffff7bcfaa1 in start_thread () from /lib64/libpthread.so.0  #26 0x00007ffff6b37c4d in clone () from /lib64/libc.so.6  
T1T2
BEGIN;
delete from test0820;
update test0820 set name=’100’ where name=’90’

显示如下:

    mysql> show processlist;  +----+-----------------+-----------+---------+---------+------+---------------------------+------------------------------------------------+-----------+---------------+  | Id | User            | Host      | db      | Command | Time | State                     | Info                                           | Rows_sent | Rows_examined |  +----+-----------------+-----------+---------+---------+------+---------------------------+------------------------------------------------+-----------+---------------+  |  1 | event_scheduler | localhost | NULL    | Daemon  | 4771 | Waiting on empty queue    | NULL                                           |         0 |             0 |  |  3 | root            | localhost | testmts | Query   |   28 | Searching rows for update | update test0820 set name='100' where name='90' |         0 |             0 |  |  7 | root            | localhost | testmts | Query   |    0 | starting                  | show processlist                               |         0 |             0 |  +----+-----------------+-----------+---------+---------+------+---------------------------+------------------------------------------------+-----------+---------------+  3 rows in set (0.01 sec)  

6、疑问:

  • 其他还有一些特殊情况,包含哪些?
  • update执行计划出现Using temporary是在哪里做的?

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-2654278/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183932.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code