---
官方版本測試到8.0.21依舊存在這個問題，Percona已經修復了，修復如下
https://www.percona.com/blog/2018/03/27/analyze-table-is-no-longer-a-blocking-operation/
能力有限簡單記錄。
---

公眾號 MySQL學習

###二、analyze觸發了什麼

analyze table 除了更新我們的統計資料，實際上最後做了一個操作如下（棧）：
```
#0  TABLE_SHARE::clear_version (this=0x7ffeec00ee38) at /opt/mysql/mysql-8.0.21/sql/table.cc:518
#1  0x0000000003680a4d in <lambda>, std::unique_ptr<table_share table_share_deleter="">, std::hash<:__cxx11::basic_string> >, std::equal_to<:__cxx11::basic_string> >, Malloc_allocator<:pair std::__cxx11::basic_string="">, std::unique_ptr<table_share table_share_deleter=""> > > >::iterator)>::operator()(std::unordered_map<:__cxx11::basic_string std::char_traits="">, std::allocator<char> >, std::unique_ptr<table_share table_share_deleter="">, std::hash<:__cxx11::basic_string std::char_traits="">, std::allocator<char> > >, std::equal_to<:__cxx11::basic_string std::char_traits="">, std::allocator<char> > >, Malloc_allocator<:pair std::char_traits="">, std::allocator<char> > const, std::unique_ptr<table_share table_share_deleter=""> > > >::iterator) const (__closure=0x7fff600ce8a0, 
    my_it=...) at /opt/mysql/mysql-8.0.21/sql/sql_base.cc:9831
#2  0x0000000003680d0c in tdc_remove_table (thd=0x7ffed8094550, remove_type=TDC_RT_REMOVE_UNUSED, db=0x7ffed80c4a08 "test", table_name=0x7ffed80c4a20 "e01", has_lock=false)
    at /opt/mysql/mysql-8.0.21/sql/sql_base.cc:9844
#3  0x0000000003d890a9 in mysql_admin_table (thd=0x7ffed8094550, tables=0x7ffed80c4cb8, check_opt=0x7ffed80ba370, operator_name=0x61c06e7 "analyze", lock_type=TL_READ_NO_INSERT, 
    open_for_modify=true, repair_table_use_frm=false, extra_open_options=0, prepare_func=0x0, operator_func=
    (int (handler::*)(handler * const, THD *, HA_CHECK_OPT *)) 0x3accc64 <:ha_analyze ha_check_opt="">, check_view=0, alter_info=0x7ffed80c4b28, 
    need_to_acquire_shared_backup_lock=true) at /opt/mysql/mysql-8.0.21/sql/sql_admin.cc:1296
#4  0x0000000003d89e77 in Sql_cmd_analyze_table::execute (this=0x7ffed80c5270, thd=0x7ffed8094550) at /opt/mysql/mysql-8.0.21/sql/sql_admin.cc:1549
#5  0x00000000037501d9 in mysql_execute_command (thd=0x7ffed8094550, first_level=true) at /opt/mysql/mysql-8.0.21/sql/sql_parse.cc:4573

```
大概看一下做了什麼，如下：
```
    if (share->ref_count() > 0) {
      /*
        Set share's version to zero in order to ensure that it gets
        automatically deleted once it is no longer referenced.

Note that code in TABLE_SHARE::wait_for_old_version() assumes
        that marking share as old and removal of its unused tables
        and of the share itself from TDC happens atomically under
        protection of LOCK_open, or, putting it another way, that
        TDC does not contain old shares which don't have any tables
        used.
      */
      if (remove_type != TDC_RT_REMOVE_NOT_OWN_KEEP_SHARE)
        share->clear_version();
      table_cache_manager.free_table(thd, remove_type, share);
    }
```
這裡判斷了是否當前table share正在使用，如果正在使用（很顯然我們這個table share是不能直接釋放的，因為有select一直持有它）那麼將share版本的設定為0（share->clear_version()，實際上這個版本由全域性變數refresh_version初始化），目的在於下次如果有使用表定義的時候需要重新開啟table share。然後釋放了當前沒有使用的table cache（型別TDC_RT_REMOVE_UNUSED），如下：

```
TDC_RT_REMOVE_UNUSED  - remove all unused TABLE
                                                instances (if there are no
                                                used instances will also
                                                remove TABLE_SHARE).

```

###三、再次訪問表堵塞
當再次訪問表的時候（open_table），會去判斷如下是否有老的table share存在，如果存在則需要等待釋放：
```
share_found:
  if (!(flags & MYSQL_OPEN_IGNORE_FLUSH)) {
    if (share->has_old_version()) {  
      /*
        We already have an MDL lock. But we have encountered an old
        version of table in the table definition cache which is possible
        when someone changes the table version directly in the cache
        without acquiring a metadata lock (e.g. this can happen during
        "rolling" FLUSH TABLE(S)).
        Release our reference to share, wait until old version of
        share goes away and then try to get new version of table share.
      */
      release_table_share(share);
...
      wait_result =
          tdc_wait_for_old_version(thd, table_list->db, table_list->table_name,
                                   ot_ctx->get_timeout(), deadlock_weight);
```

首先如果存在判斷是否存在的老版本，判斷是透過table share的版本和當前全域性版本refresh_version進行比對，前面我們知道這裡table share的版本已經設定為0，因此這裡必然進入release_table_share環節，然後等待持有者的釋放（案例視窗1的select查詢），然後再次獲取table share。等待棧如下：
```
(gdb) bt
#0  tdc_wait_for_old_version (thd=0x7ffed8094550, db=0x7ffed802e5c0 "test", table_name=0x7ffed802e5d8 "e01", wait_timeout=31536000, deadlock_weight=0)
    at /opt/mysql/mysql-8.0.21/sql/sql_base.cc:2705
#1  0x0000000003671d7e in open_table (thd=0x7ffed8094550, table_list=0x7ffed802e7c8, ot_ctx=0x7fff600cf1a0) at /opt/mysql/mysql-8.0.21/sql/sql_base.cc:3280
#2  0x0000000003675c36 in open_and_process_table (thd=0x7ffed8094550, lex=0x7ffed80b9fe0, tables=0x7ffed802e7c8, counter=0x7ffed80ba038, prelocking_strategy=0x7fff600cf2b8, 
    has_prelocking_list=false, ot_ctx=0x7fff600cf1a0) at /opt/mysql/mysql-8.0.21/sql/sql_base.cc:5000
#3  0x0000000003677102 in open_tables (thd=0x7ffed8094550, start=0x7fff600cf260, counter=0x7ffed80ba038, flags=0, prelocking_strategy=0x7fff600cf2b8)
    at /opt/mysql/mysql-8.0.21/sql/sql_base.cc:5664
#4  0x0000000003678993 in open_tables_for_query (thd=0x7ffed8094550, tables=0x7ffed802e7c8, flags=0) at /opt/mysql/mysql-8.0.21/sql/sql_base.cc:6515
```
進入waiting for table flush狀態
```
  wait_status = mdl_context->m_wait.timed_wait(thd, abstime, true,
                                               &stage_waiting_for_table_flush);
```

###四、Percona修改一處

Percona在上文中已經提到問題如下：
![image.png](https://upload-images.jianshu.io/upload_images/7398834-37df65a5773e9965.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

一個關鍵的修改點如下
官方版本：
![image.png](https://upload-images.jianshu.io/upload_images/7398834-d8fc1b223318b07a.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

Percona版本：
![image.png](https://upload-images.jianshu.io/upload_images/7398834-b70a40af49b6f393.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

如此修改後analyze不會進入tdc_remove_table函式，那麼table share的版本不會設定為0。因此如果使用官方版本小心本問題。</:ha_analyze></table_share></char></:pair></char></:__cxx11::basic_string></char></:__cxx11::basic_string></table_share></char></:__cxx11::basic_string></table_share></:pair></:__cxx11::basic_string></:__cxx11::basic_string></table_share></lambda>

MySQL:Analyze table導致'waiting for table flush'

相關文章