MySQL的Searching rows for update状态是怎样的
这篇文章主要介绍“MySQL的Searching rows for update状态是怎样的”，在日常操作中，相信很多人在MySQL的Searching rows for update状态是怎样的问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL的Searching rows for update状态是怎样的”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
1、限制条件一般不能是唯一键和主键，也不能是全表，代码如下：
if(used_index!=MAX_KEY)//不能是唯一键(主键)和全表{//Checkifwearemodifyingakeythatweareusedtosearchwith: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_rowstmp_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);gotoexit_without_my_ok;}while(!(error=info.read_record(&info))&&!thd->killed){thd->inc_examined_row_count(1);//扫描增加boolskip_record=FALSE;if(qep_tab.skip_record(thd,&skip_record))...4、测试总结：
mysql>showcreatetabletest0820;+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|test0820|CREATETABLE`test0820`(`id`int(11)NOTNULL,`name`varchar(20)DEFAULTNULL,`name1`varchar(20)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`idx_u_test`(`name1`),KEY`name`(`name`))ENGINE=InnoDBDEFAULTCHARSET=utf8|+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)
主键ID更新不触发
唯一键idx_u_test更新不触发
普通索引name更新触发
如果update执行计划出现Using temporary 则会使用stage_searching_rows_for_update。
mysql>descupdatetest0820setname1='7'wherename1='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|Usingwhere|+----+-------------+----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+1rowinset(2.58sec)mysql>descupdatetest0820setname='7'wherename='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|Usingwhere;Usingtemporary|+----+-------------+----------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+1rowinset(1.91sec)mysql>descupdatetest0820setid=2whereid=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|Usingwhere|+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+1rowinset(2.30sec)5、stage_searching_rows_for_update状态扫描数据已经加锁，因此很容易测试这种情况
栈帧：
#00x00007ffff7bd368cinpthread_cond_wait@@GLIBC_2.3.2()from/lib64/libpthread.so.0#10x0000000001b2f921inos_event::wait(this=0x7ffee0e418e8)at/mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/include/os0event.h:156#20x0000000001b2f269inos_event::wait_low(this=0x7ffee0e418e8,reset_sig_count=1)at/mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/os/os0event.cc:131#30x0000000001b2f692inos_event_wait_low(event=0x7ffee0e418e8,reset_sig_count=0)at/mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/os/os0event.cc:328#40x0000000001af0c4binlock_wait_suspend_thread(thr=0x7ffee0e42ed0)at/mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0wait.cc:387#50x0000000001bb6de8inrow_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#60x0000000001bf9ed6inrow_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#70x0000000001a53113inha_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#80x0000000000f933c2inhandler::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#90x0000000000f83dacinhandler::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#100x0000000000f8e844inhandler::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#110x0000000000f8c775inhandler::multi_range_read_next(this=0x7ffee0952030,range_info=0x7fffec5ec370)at/mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:6817#120x0000000000f8d68dinDsMrr_impl::dsmrr_next(this=0x7ffee09524a0,range_info=0x7fffec5ec370)at/mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:7204#130x0000000001a6689ainha_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#140x00000000017bdbd8inQUICK_RANGE_SELECT::get_next(this=0x7ffee0e40250)at/mysqldata/percona-server-locks-detail-5.7.22/sql/opt_range.cc:11237#150x00000000014e27f5inrr_quick(info=0x7fffec5ec870)at/mysqldata/percona-server-locks-detail-5.7.22/sql/records.cc:399#160x000000000168c103inmysql_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#170x0000000001692e40inSql_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#180x000000000169338dinSql_cmd_update::execute(this=0x7ffee0006bc0,thd=0x7ffee0000c00)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_update.cc:3023#190x00000000015cc801inmysql_execute_command(thd=0x7ffee0000c00,first_level=true)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3756#200x00000000015d2fdeinmysql_parse(thd=0x7ffee0000c00,parser_state=0x7fffec5ee600)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#210x00000000015c6b72indispatch_command(thd=0x7ffee0000c00,com_data=0x7fffec5eed70,command=COM_QUERY)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#220x00000000015c58ffindo_command(thd=0x7ffee0000c00)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#230x000000000170e578inhandle_connection(arg=0x6795460)at/mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#240x0000000001945538inpfs_spawn_thread(arg=0x6947660)at/mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#250x00007ffff7bcfaa1instart_thread()from/lib64/libpthread.so.0#260x00007ffff6b37c4dinclone()from/lib64/libc.so.6T1 T2 BEGIN;
delete from test0820;
update test0820 set name=’100’ where name=’90’
显示如下：
mysql>showprocesslist;+----+-----------------+-----------+---------+---------+------+---------------------------+------------------------------------------------+-----------+---------------+|Id|User|Host|db|Command|Time|State|Info|Rows_sent|Rows_examined|+----+-----------------+-----------+---------+---------+------+---------------------------+------------------------------------------------+-----------+---------------+|1|event_scheduler|localhost|NULL|Daemon|4771|Waitingonemptyqueue|NULL|0|0||3|root|localhost|testmts|Query|28|Searchingrowsforupdate|updatetest0820setname='100'wherename='90'|0|0||7|root|localhost|testmts|Query|0|starting|showprocesslist|0|0|+----+-----------------+-----------+---------+---------+------+---------------------------+------------------------------------------------+-----------+---------------+3rowsinset(0.01sec)
到此，关于“MySQL的Searching rows for update状态是怎样的”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
