MySQL的Searchingrowsforupdate状态是怎样的
这篇文章主要介绍“MySQL的Searching rows for update状态是怎样的”，在日常操作中，相信很多人在MySQL的Searching rows for update状态是怎样的问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL的Searching rows for update状态是怎样的”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
成都创新互联公司坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站建设、成都网站设计、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的吴中网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！
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
| T1 | T2 |
|---|---|
| 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)
到此，关于“MySQL的Searching rows for update状态是怎样的”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注创新互联网站，小编会继续努力为大家带来更多实用的文章！
分享题目：MySQL的Searchingrowsforupdate状态是怎样的
文章链接：http://ybzwz.com/article/jshhjs.html
