隨筆MySQL：Searching rows for update狀態解析
歡迎關注我的《深入理解MySQL主從原理 32講 》，如下：
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)
6、疑問：
- 其他還有一些特殊情況，包含哪些？
- update執行計劃出現Using temporary是在哪裡做的？
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2654278/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 隨筆：MySQL 查詢事務狀態欄位說明MySql
- MySQL 隨筆MySql
- 隨遇而安——狀態模式模式
- Redo active狀態解析
- Last_SQL_Error: Could not execute Update_rows eventASTSQLError
- MySQL Galera Cluster全解析 Part 3 狀態快照傳輸(SST)MySql
- 沉浸式狀態列解析
- mysql新手入門隨筆MySql
- MySQL 8.0 Reference Manual（讀書筆記67節--Phantom Rows）MySql筆記
- React hooks 狀態管理方案解析ReactHook
- java狀態模式例項解析Java模式
- mysql 鎖狀態的一些狀態資訊記錄MySql
- mysql新手入門隨筆2MySql
- mysql新手入門隨筆4MySql
- LINUX netstat連線狀態解析及TCP狀態轉換LinuxTCP
- ijkplayer 播放器狀態解析播放器
- MySQL 分析伺服器狀態MySql伺服器
- FineUIMvc隨筆（1）動態建立表格列UIMVC
- Redo內部解析-Multi Rows Insert （八）
- React學習筆記-State(狀態)React筆記
- MySQL執行緒狀態詳解MySql執行緒
- MySQL MHA 執行狀態監控MySql
- 獲取Mysql的狀態、變數MySql變數
- Mysql複製執行緒狀態MySql執行緒
- 檢測mysql狀態的指令碼MySql指令碼
- MySQL執行狀態監控(pt-mysql-summary)MySql
- 隨筆：MySQL 普通3表join順序MySql
- 靜態路由介紹（雲端計算隨筆）路由
- MySQL InnoDB update流程MySql
- mysql update join優化update in查詢效率MySql優化
- 智慧變色T恤 顏色隨運動狀態變
- 7. 監控MySQL主從狀態MySql
- Galera Cluster for MySQL監控狀態說明MySql
- 【MYSQL】state狀態converting HEAP to MyISAMMySql
- mysql較為重要的狀態變數MySql變數
- Myisam-儲存引擎-動態格式-DELETED ROWS儲存引擎delete
- Myisam-儲存引擎-靜態格式-DELETED ROWS儲存引擎delete
- 深度學習論文翻譯解析（十九）：Searching for MobileNetV3深度學習