MySQL慢查詢?nèi)罩局械腖ock_time由來解析

更新時(shí)間：2023年06月02日 11:48:39 作者：愛可生開源社區(qū)

這篇文章主要為大家介紹了慢查詢?nèi)罩局蠰ock_time的由來解析，以及Lock_time?包含哪些鎖等待時(shí)間、以及是怎么計(jì)算得到的，有需要的朋友可以借鑒參考下，希望能夠有所幫助

慢查詢?nèi)罩?/h2>
經(jīng)常關(guān)注慢查詢?nèi)罩镜淖x者，和 Lock_time 應(yīng)該算是老相識(shí)了，大家對(duì)這位老相識(shí)了解有多少呢？

研究 Lock_time 之前，我對(duì)它的了解，僅限于它表示鎖等待時(shí)間。至于它包含哪些鎖等待時(shí)間、怎么計(jì)算得到的，我并不清楚。

所以，我一直有個(gè)困惑：為什么有些 SQL 執(zhí)行時(shí)間很長(zhǎng)，Lock_time 卻很?。ɡ纾?.001 秒）?

今天我們就一起來看看，Lock_time 包含哪些鎖等待時(shí)間、以及是怎么計(jì)算得到的？

本文基于 MySQL 8.0.32 源碼，存儲(chǔ)引擎為 InnoDB。

1. 整體介紹

Lock_time 由兩部分相加得到：

表鎖等待時(shí)間，如果 SQL 中包含多個(gè)表，則是多個(gè)表鎖等待時(shí)間之和。
行鎖等待時(shí)間，如果 SQL 執(zhí)行過程中需要對(duì)多條記錄加鎖，則是多個(gè)行鎖等待時(shí)間之和。

對(duì) InnoDB 來說，DML、DQL 對(duì)記錄進(jìn)行增刪改查操作時(shí)，如需加鎖，都是加行級(jí)別的共享鎖、排他鎖，而不加表級(jí)別的共享鎖、排他鎖。

共享鎖又稱作 S 鎖，排他鎖又稱作 X 鎖。

那么，InnoDB 有表級(jí)別的共享鎖、排他鎖嗎？

別說，還真有！

不過，不常有！

只有執(zhí)行 LOCK TABLES ... [READ | WRITE]，并且系統(tǒng)變量 innodb_table_locks = 1、auto_commit = 0，InnoDB 才會(huì)加表級(jí)別的共享鎖、排他鎖。

從代碼注釋和官方文檔對(duì) innodb_table_locks 的介紹來看，執(zhí)行存儲(chǔ)過程和觸發(fā)器時(shí)，InnoDB 也可能會(huì)加表級(jí)別的共享鎖、排他鎖，我們就不展開介紹了。

如果 InnoDB 加了表級(jí)別的共享鎖、排他鎖，Lock_time 包含表鎖等待時(shí)間，我們比較好理解。

如果我們執(zhí)行 DML、DQL，InnoDB 沒有加表級(jí)別的共享鎖、排他鎖，Lock_time 里還包含表鎖等待時(shí)間嗎？

這個(gè)問題，就得看用什么標(biāo)準(zhǔn)了：

嚴(yán)格來說，Lock_time 就不包含表鎖等待時(shí)間了。
不嚴(yán)格來說，Lock_time 還是包含表鎖等待時(shí)間的（InnoDB 采用了這個(gè)標(biāo)準(zhǔn)）。

接下來，我們通過源碼，進(jìn)入表鎖、行鎖等待時(shí)間的實(shí)現(xiàn)邏輯，來一睹芳容。

2. 表鎖等待時(shí)間

我們先來看一下表鎖等待時(shí)間實(shí)現(xiàn)邏輯的堆棧：

|?>?mysql_execute_command(THD*,?bool)?sql/sql_parse.cc:4688
|?+?>?Sql_cmd_dml::execute(THD*)?sql/sql_select.cc:574
|?+?-?>?lock_tables(...)?sql/sql_base.cc:6899
|?+?-?x?>?mysql_lock_tables(...)?sql/lock.cc:337
|?+?-?x?=?>?lock_external(THD*,?TABLE**,?unsigned?int)?sql/lock.cc:393
|?+?-?x?=?|?>?handler::ha_external_lock(THD*,?int)?sql/handler.cc:7841
|?+?-?x?=?|?+?>?ha_innobase::external_lock(THD*,?int)?storage/innobase/handler/ha_innodb.cc:18869

Sql_cmd_dml::execute () 調(diào)用 lock_tables () 對(duì)多個(gè)表加鎖。

//?sql/sql_base.cc
bool?lock_tables(THD?*thd,?Table_ref?*tables,?uint?count,?uint?flags)?{
??...
??if?(!thd->locked_tables_mode)?{
????...
????if?(!(thd->lock?=
??????????mysql_lock_tables(thd,?start,?(uint)(ptr?-?start),?flags)))
??????return?true;
????...
??}
??...
}

lock_tables () 調(diào)用 mysql_lock_tables () 對(duì)多個(gè)表加鎖。

//?sql/lock.cc
MYSQL_LOCK?*mysql_lock_tables(THD?*thd,?TABLE?**tables,?size_t?count,
??????????????????????????????uint?flags)?{
??...
??//?記錄開始時(shí)間
??ulonglong?lock_start_usec?=?my_micro_time();
??...
??if?(sql_lock->table_count?&&
??????lock_external(thd,?sql_lock->table,?sql_lock->table_count))?{
????/*?Clear?the?lock?type?of?all?lock?data?to?avoid?reusage.?*/
????reset_lock_data_and_free(&sql_lock);
????goto?end;
??}
??...
??//?lock_external()?執(zhí)行完成之后
??//?當(dāng)前時(shí)間減去開始時(shí)間
??//?就是表鎖等待時(shí)間
??ulonglong?lock_end_usec?=?my_micro_time();
??thd->inc_lock_usec(lock_end_usec?-?lock_start_usec);
??...
}

mysql_lock_tables () 調(diào)用 lock_external () 之前，先把當(dāng)前時(shí)間記錄下來，作為表鎖等待的開始時(shí)間。

然后調(diào)用 lock_external () 對(duì)多個(gè)表加鎖。

最后，調(diào)用 thd->inc_lock_usec () 把表鎖等待時(shí)間累加到 server 層線程對(duì)象（thd）的 m_lock_usec 屬性中。

//?sql/lock.cc
static?int?lock_external(THD?*thd,?TABLE?**tables,?uint?count)?{
??...
??//?循環(huán)?SQL?中的表
??for?(i?=?1;?i?<=?count;?i++,?tables++)?{
????assert((*tables)->reginfo.lock_type?>=?TL_READ);
????//?默認(rèn)鎖類型為寫鎖
????//?對(duì)應(yīng)到?InnoDB?的鎖類型就是排他鎖（X）
????lock_type?=?F_WRLCK;?/*?Lock?exclusive?*/
????//?如果以只讀方式打開表的數(shù)據(jù)文件（.ibd）或者
????//?lock_type?大于等于?TL_READ(2)?并且
????//?lock_type?小于等于?TL_READ_NO_INSERT(5)
????//?則說明是只讀操作，加讀鎖
????//?對(duì)應(yīng)到?InnoDB?的鎖類型就是共享鎖（S）
????if?((*tables)->db_stat?&?HA_READ_ONLY?||
????????((*tables)->reginfo.lock_type?>=?TL_READ?&&
?????????(*tables)->reginfo.lock_type?<=?TL_READ_NO_INSERT))
??????lock_type?=?F_RDLCK;
????if?((error?=?(*tables)->file->ha_external_lock(thd,?lock_type)))?{
??????//?ha_external_lock()?返回非?0?值
??????//?說明執(zhí)行?ha_external_lock()?方法出現(xiàn)了錯(cuò)誤
??????//?這里處理善后工作
??????...
??????return?error;
????}?else?{
??????(*tables)->db_stat?&=?~HA_BLOCK_LOCK;
??????(*tables)->current_lock?=?lock_type;
????}
??}
??return?0;
}

lock_external () 會(huì)迭代 SQL 中的表，每迭代一個(gè)表，都調(diào)用 ha_external_lock () 對(duì)表進(jìn)行加鎖。

//?sql/handler.cc
int?handler::ha_external_lock(THD?*thd,?int?lock_type)?{
??...
??MYSQL_TABLE_LOCK_WAIT(PSI_TABLE_EXTERNAL_LOCK,?lock_type,
????{?error?=?external_lock(thd,?lock_type);?})
??...
}

handler::ha_external_lock () 調(diào)用表對(duì)應(yīng)存儲(chǔ)引擎的 external_lock () 方法。

對(duì) InnoDB 來說，調(diào)用的是 ha_innobase::external_lock()，這個(gè)方法的代碼比較多，算是個(gè)大雜燴，可以分為三類：

加表級(jí)別的共享鎖、排他鎖。
把當(dāng)前迭代表所屬表空間的臟頁(yè)，同步刷新到磁盤。
一些初始化邏輯（執(zhí)行快，花費(fèi)時(shí)間極少）。

ha_innobase::external_lock () 的執(zhí)行時(shí)間會(huì)計(jì)入表鎖等待時(shí)間，因?yàn)槠渲锌赡馨剿⑴K頁(yè)操作、執(zhí)行一些初始化邏輯花費(fèi)的時(shí)間，所以，表鎖等待時(shí)間并不純粹。

對(duì)需要加表鎖的 SQL 來說，表鎖等待時(shí)間包含兩部分：

加表級(jí)別的共享鎖、排他鎖的等待時(shí)間。
執(zhí)行一些初始化邏輯花費(fèi)的時(shí)間。

如果是 FLUSH TABLES ... WITH READ LOCK 語(yǔ)句，表鎖等待時(shí)間還包含：把其中涉及的表所屬表空間的臟頁(yè)同步刷新到磁盤所花費(fèi)的時(shí)間。

對(duì)不需要加表鎖的 SQL 來說，表鎖等待時(shí)間就是執(zhí)行 ha_innobase::external_lock () 中一些初始化邏輯花費(fèi)的時(shí)間。

我們來看看 ha_innobase::external_lock () 主要包含哪些代碼邏輯，對(duì)這部分細(xì)節(jié)不感興趣的讀者，可以跳過這個(gè)小節(jié)。

這個(gè)小節(jié)的代碼都來自于 ha_innobase::external_lock()，文件路徑 storage/innobase/handler/ha_innodb.cc。

update_thd(thd);

以上代碼，創(chuàng)建 InnoDB 的事務(wù)對(duì)象（trx_t），保存到 server 層的用戶線程對(duì)象（thd）中。

//?lock_type?==?F_WRLCK，意味著需要加寫鎖
//?這里用于表示需要記錄?binlog
if?(lock_type?==?F_WRLCK?&amp;&amp;
????//?表示不支持?STATEMENT?格式的?binlog
????//?table_flags()?方法會(huì)判斷事務(wù)隔離級(jí)別
????!(table_flags()?&amp;?HA_BINLOG_STMT_CAPABLE)?&amp;&amp;
????//?系統(tǒng)變量?binlog_format?=?STATEMENT
????//?表示用戶需要記錄?STATEMENT?格式的?binlog
????thd_binlog_format(thd)?==?BINLOG_FORMAT_STMT?&amp;&amp;
????//?表示需要為當(dāng)前連接指定的數(shù)據(jù)庫(kù)記錄?binlog
????//?use?&lt;db&gt;?或者連接數(shù)據(jù)庫(kù)時(shí)指定了數(shù)據(jù)庫(kù)
????thd_binlog_filter_ok(thd)?&amp;&amp;
????//?表示當(dāng)前執(zhí)行的?SQL?會(huì)產(chǎn)生?ROW?格式的?binlog
????thd_sqlcom_can_generate_row_events(thd))?{
??bool?skip?=?false;
??...
??if?(!skip)?{
????...
????return?HA_ERR_LOGGING_IMPOSSIBLE;
??}
}

上面代碼的判斷條件有點(diǎn)多，我們用一句話來概括一下代碼邏輯：

事務(wù)隔離級(jí)別為 READ_UNCOMMITTED、READ_COMMITTED 時(shí)，如果 SQL 會(huì)產(chǎn)生 ROW 格式的 binlog，而用戶設(shè)置系統(tǒng)變量 binlog_format 的值為 STATEMENT，要求記錄 STATEMENT 格式的 binlog，ha_innobase::external_lock () 會(huì)返回 HA_ERR_LOGGING_IMPOSSIBLE，因?yàn)?MySQL 無(wú)法處理這樣矛盾的場(chǎng)景。

if?(lock_type?==?F_WRLCK)?{
??/*?If?this?is?a?SELECT,?then?it?is?in?UPDATE?TABLE?...
??or?SELECT?...?FOR?UPDATE?*/
??m_prebuilt-&gt;select_lock_type?=?LOCK_X;
??m_stored_select_lock_type?=?LOCK_X;
}

InnoDB 讀取記錄時(shí)，會(huì)根據(jù) m_prebuilt->select_lock_type 的值確定是否加行鎖、加共享鎖還是排他鎖。

lock_type 等于 F_WRLCK，表示 server 層要求加寫鎖，對(duì)應(yīng)到 InnoDB 的鎖類型，就是排他鎖，設(shè)置加鎖類型為 LOCK_X。

if?(lock_type?==?F_RDLCK)?{
??...
??//?如果當(dāng)前表是數(shù)據(jù)字典表
??//?或者被標(biāo)識(shí)為不需要加鎖（no_read_locking?=?true)
??//?設(shè)置加鎖類型為?LOCK_NONE
??if?(m_prebuilt->table->is_dd_table?||?m_prebuilt->no_read_locking)?{
????m_prebuilt->select_lock_type?=?LOCK_NONE;
????m_stored_select_lock_type?=?LOCK_NONE;
??//?如果事務(wù)隔離級(jí)別是可串行化
??}?else?if?(trx->isolation_level?==?TRX_ISO_SERIALIZABLE?&&
????//?并且當(dāng)前?SQL?還沒有確定加鎖類型
????m_prebuilt->select_lock_type?==?LOCK_NONE?&&
????//?并且當(dāng)前事務(wù)需要手動(dòng)提交
????thd_test_options(thd,?OPTION_NOT_AUTOCOMMIT?|?OPTION_BEGIN))?{
????//?設(shè)置加鎖類型為共享鎖
????m_prebuilt->select_lock_type?=?LOCK_S;
????m_stored_select_lock_type?=?LOCK_S;
??}?else?{
????//?Retain?value?set?earlier?for?example?via?store_lock()
????//?which?is?LOCK_S?or?LOCK_NONE
????ut_ad(m_prebuilt->select_lock_type?==?LOCK_S?||
??????????m_prebuilt->select_lock_type?==?LOCK_NONE);
??}
}

lock_type 等于 F_RDLCK，表示 server 層要求加讀鎖，對(duì)應(yīng)到 InnoDB 的鎖類型，就是共享鎖，分兩種情況設(shè)置 InnoDB 的加鎖類型：

對(duì)于 ACL 表，m_prebuilt->no_read_locking 會(huì)被設(shè)置為 true，表示讀取記錄時(shí)不加鎖。
如果事務(wù)隔離級(jí)別是可串行化，并且當(dāng)前事務(wù)需要手動(dòng)執(zhí)行 COMMIT 語(yǔ)句提交，以及還沒有確定讀取該表記錄時(shí)加什么類型的行鎖，設(shè)置 InnoDB 加鎖類型為共享鎖。

ACL 表用于訪問權(quán)限控制，包含如下這些表：

user
db
tables_priv
columns_priv
procs_priv
proxies_priv
role_edges
default_roles
global_grants
password_history

switch?(m_prebuilt->table->quiesce)?{
??case?QUIESCE_START:
????/*?Check?for?FLUSH?TABLE?t?WITH?READ?LOCK;?*/
????if?(!srv_read_only_mode?&&?sql_command?==?SQLCOM_FLUSH?&&
????????lock_type?==?F_RDLCK)?{
??????...
??????row_quiesce_table_start(m_prebuilt->table,?trx);
??????...
????}
????break;
??...
}

只有執(zhí)行 FLUSH TABLES ... WITH READ LOCK 語(yǔ)句時(shí)，才會(huì)命中代碼中的 case 分支。

row_quiesce_table_start () 會(huì)調(diào)用 buf_LRU_flush_or_remove_pages()，并把當(dāng)前加表鎖的表所屬表空間對(duì)象傳給該方法，表示把該表空間的臟頁(yè)刷新到磁盤。

3. 行鎖等待時(shí)間

我們先來看看對(duì)一條記錄加行鎖的等待時(shí)間是怎么計(jì)算的。

InnoDB 讀取一條記錄時(shí)，如需加行鎖，會(huì)調(diào)用 sel_set_rec_lock () 進(jìn)行加鎖。

如果其它事務(wù)持有該記錄的行鎖，sel_set_rec_lock() 會(huì)返回 DB_LOCK_WAIT，row_search_mvcc() 調(diào)用 row_mysql_handle_errors () 處理鎖等待邏輯。

row_mysql_handle_errors () 調(diào)用 lock_wait_suspend_thread()，行鎖等待邏輯由這個(gè)方法實(shí)現(xiàn)。

//?storage/innobase/lock/lock0wait.cc
void?lock_wait_suspend_thread(que_thr_t?*thr)?{
??srv_slot_t?*slot;
??trx_t?*trx;
??//?聲明變量，用于保存行鎖等待的開始時(shí)間
??std::chrono::steady_clock::time_point?start_time;
??...
??if?(thr->lock_state?==?QUE_THR_LOCK_ROW)?{
????srv_stats.n_lock_wait_count.inc();
????srv_stats.n_lock_wait_current_count.inc();
????//?設(shè)置行鎖等待的開始時(shí)間
????start_time?=?std::chrono::steady_clock::now();
??}
??...
??//?等待行鎖
??os_event_wait(slot->event);
??...
??//?運(yùn)行到這里，有兩種情況：
??//?1.?鎖等待超時(shí)
??//?2.?已經(jīng)獲取到行鎖
??if?(thr->lock_state?==?QUE_THR_LOCK_ROW)?{
????//?用當(dāng)前時(shí)間減去行鎖等待的開始時(shí)間
????//?就是一條記錄的行鎖等待時(shí)間
????const?auto?diff_time?=?std::chrono::steady_clock::now()?-?start_time;
????...
????/*?Record?the?lock?wait?time?for?this?thread?*/
????//?累加線程的行鎖等待時(shí)間
????//?保存到?mysql_thd?線程中
????//?mysql_thd?是?server?層的線程
????thd_set_lock_wait_time(trx->mysql_thd,?diff_time);
????...
??}
??...
}

從上面代碼可以看到，計(jì)算一條記錄的行鎖等待時(shí)間，邏輯比較簡(jiǎn)單：
先保存當(dāng)前行鎖等待的開始時(shí)間，獲取到行鎖或等待行鎖超時(shí)之后，再用當(dāng)前時(shí)間減去開始時(shí)間，就得到了一條記錄的行鎖等待時(shí)間。

4. 累計(jì)時(shí)間

一滴水的夢(mèng)想是終有一天能夠匯入大海。

表鎖、行鎖等待時(shí)間的歸宿是累加起來，最終成為 lock_time，這個(gè)過程是通過調(diào)用 thd_set_lock_wait_time() 實(shí)現(xiàn)的。

//?storage/innobase/handler/ha_innodb.cc
void?thd_set_lock_wait_time(THD?*thd,
????????????????????????????std::chrono::steady_clock::duration?value)?{
??if?(thd)?{
????thd_storage_lock_wait(
????????thd,
????????std::chrono::duration_cast<std::chrono::microseconds>(value).count());
??}
}

thd_set_lock_wait_time () 調(diào)用 thd_storage_lock_wait() 累加表鎖、行鎖等待時(shí)間。

//?sql/sql_thd_api.cc
void?thd_storage_lock_wait(MYSQL_THD?thd,?long?long?value)?{
??thd-&gt;inc_lock_usec(value);
}

真正干活的是 THD::inc_lock_usec () 方法。

//?sql/sql_class.cc
void?THD::inc_lock_usec(ulonglong?lock_usec)?{
??m_lock_usec?+=?lock_usec;
??MYSQL_SET_STATEMENT_LOCK_TIME(m_statement_psi,?m_lock_usec);
}

server 層每獲取到一個(gè)表鎖，都會(huì)調(diào)用 thd_set_lock_wait_time()，累加表鎖等待時(shí)間。

最終會(huì)調(diào)用 THD::inc_lock_usec () 把表鎖等待時(shí)間累加到 server 層的線程對(duì)象 thd 的 m_lock_usec 屬性中。

InnoDB 每獲取到一條記錄的行鎖，或者行鎖等待超時(shí)，都會(huì)調(diào)用 thd_set_lock_wait_time()，累加行鎖等待時(shí)間。

最終會(huì)調(diào)用 THD::inc_lock_usec () 把行鎖等待時(shí)間累加到 server 層的線程對(duì)象 thd 的 m_lock_usec 屬性中。

5. lock_time

SQL 執(zhí)行完成之后，dispatch_command () 調(diào)用 log_slow_statement () 記錄慢查詢到文件中。

log_slow_statement () 也不是真正干活的，經(jīng)過多級(jí)，最終調(diào)用 Query_logger::slow_log_write () 記錄慢查詢到文件中。

//?sql/log.cc
bool?Query_logger::slow_log_write(THD?*thd,?const?char?*query,
??????????????????????????????????size_t?query_length,?bool?aggregate,
??????????????????????????????????ulonglong?lock_usec,?ulonglong?exec_usec)?{
??...
??if?(aggregate)?{
????query_utime?=?exec_usec;
????lock_utime?=?lock_usec;
??}?else?if?(thd->start_utime)?{
????query_utime?=?(current_utime?-?thd->start_utime);
????lock_utime?=?thd->get_lock_usec();
??}?else?{
????query_utime?=?0;
????lock_utime?=?0;
??}
??...
??bool?error?=?false;
??for?(Log_event_handler?**current_handler?=?slow_log_handler_list;
???????*current_handler;)?{
????error?|=?(*current_handler++)->log_slow(
???????????????thd,?current_utime,
???????????????(thd->start_time.tv_sec?*?1000000ULL)?+
???????????????thd->start_time.tv_usec,
???????????????user_host_buff,?user_host_len,?query_utime,
???????????????lock_utime,?is_command,?query,?query_length);
??}
??...
}

Query_logger::slow_log_write () 被調(diào)用時(shí)，參數(shù) aggregate 的值都是 false，上面代碼不會(huì)進(jìn)入 if (aggregate) 分支。

if (thd->start_utime) 分支，lock_utime = thd->get_lock_usec ()，從當(dāng)前線程對(duì)象（thd）中獲取之前累加的表鎖、行鎖等待時(shí)間。

然后，調(diào)用 log_slow () 記錄慢查詢到文件中。

//?sql/log.cc
bool?Log_to_file_event_handler::log_slow(
????THD?*thd,?ulonglong?current_utime,?ulonglong?query_start_utime,
????const?char?*user_host,?size_t?user_host_len,?ulonglong?query_utime,
????ulonglong?lock_utime,?bool?is_command,?const?char?*sql_text,
????size_t?sql_text_len)?{
??if?(!mysql_slow_log.is_open())?return?false;
??Silence_log_table_errors?error_handler;
??thd->push_internal_handler(&error_handler);
??bool?retval?=?mysql_slow_log.write_slow(
??????thd,?current_utime,?query_start_utime,?user_host,?user_host_len,
??????query_utime,?lock_utime,?is_command,?sql_text,?sql_text_len);
??thd->pop_internal_handler();
??return?retval;
}

Log_to_file_event_handler::log_slow () 最終調(diào)用 mysql_slow_log.write_slow () 記錄慢查詢到文件中。

//?sql/log.cc
bool?File_query_log::write_slow(...)?{
??...
??if?(!thd->copy_status_var_ptr)?{
????if?(my_b_printf(&log_file,
????????"#?Query_time:?%s??Lock_time:?%s"
????????"?Rows_sent:?%lu??Rows_examined:?%lu\n",
????????query_time_buff,?lock_time_buff,
????????(ulong)thd->get_sent_row_count(),
????????(ulong)thd->get_examined_row_count())?==?(uint)-1)
??????goto?err;?/*?purecov:?inspected?*/
??}
??...
}

經(jīng)?？绰樵?nèi)罩镜淖x者，想必對(duì)這 2 行會(huì)非常熟悉：