我了解的MYISAM引擎之七 check、analyze、optimize、repair 與myisamchk

語句	參數	作用	引擎	備注
CHECK TABLE	FOR UPGRADE	檢查更新後的不相容項	MYISAM，INNODB，VIEW	a）選項可以疊加使用,從上往下，校驗的内容逐漸增多； b）check table會自動flush table； c)   check  table如果在innodb上發現了非2級索引的塊損壞，會導緻mysqld程序崩潰https://bugs.mysql.com/bug.php?id=10132 d)   對于innodb，check table 可以并行讀取cluster index，并行讀取值為min( innodb_parallel_read_threads setting ,the number of index subtree) e)   對于MYISAM,check table會更新統計資訊，如果沒有指定QUICK或者MEDIUM或者EXTEND，預設選項為MEDIUM f)    驗證delete連接配接的邏輯：MYI檔案中的檔案從37位元組開始的8個位元組記錄了此表中被标記删除的記錄，接下來的8個位元組記錄了MYD檔案中被标記删除的記錄占用的空間，再接下來的8個位元組記錄了MYD檔案中最後一個删除記錄的偏移位址，通過這個位址在MYD可以找到最後一個被删除的記錄。同時，每一個被删除的記錄儲存了上一個被删除和下一個被删除的記錄的雙向連結清單、目前被删除記錄占用空間大小三個資料，删除校驗的目的就是從連結清單的頭開始一個一個檢查對應的前後連結的有效性，以及将所有删除記錄記錄的删除空間累加，直到連結清單尾部，最終此空間累積和大小需要等于MYI檔案中記錄的大小。 對于dynamic格式的myisam表，每一個被删除的記錄中繼資料資訊在記錄開始的20個位元組中，其中，第一個位元組為标記位，00表示标記删除，接下來的3位元組表示此标記删除記錄占用空間大小，接下來的8位元組記錄了下一個标記删除記錄的偏移位址，最後的8位元組記錄了上一個标記删除記錄的偏移位址； 對于fix格式的myisam表，由于行長度固定（MYI檔案頭261位元組開始的4個位元組記錄了行長度pack_reclength），是以MYD中記錄的尋址是通過行号*每行記錄所占空間大小來确定偏移位址的。删除連結清單的前後指針均指行号，單行删除空間即為行大小，累積删除空間等于删除行數*每行所占空間。MYI檔案第285位元組記錄了rec_reflength值，也既行号所占位元組數，此記錄從2-8個位元組不等，由參數myisam_data_pointer_size控制，當然這個值的範圍是2-7，少了一個位元組。 g) EXTENDED與MEDIUM相比，多了掃描全部行的所有索引key的步驟； h）MEDIUM與CHANGED相比，僅去掉了CHANGED的前置條件，檢查内容一緻； j）QUICK的不掃描行展現在check方法中的判斷，如果沒有指定（顯示或隐式）QUICK的話，會調用mi_check.cc的check_data_link方法，此方法中會讀取整個MYD檔案，并校驗資料、計算校驗和。這也是QUICK與CHANGED、MEDIUM、EXTENDED比起來少了的地方； k）FOR UPGRADE在8.0版本上是自動檢查項了，無論做什麼檢查，都會順帶進行。 l）對于dynamic行格式，如果沒有指定QUICK, MEDIUM, or EXTENDED 選項，則預設執行medium檢查；對于static行格式，如沒有指定任何選項，則預設執行medium檢查，CHANGED與QUICK的檢查項相同； m）從這一段代碼可以看出，特定情況下，fast到這裡就結束傳回了: 下面這兩端代碼均出自ha_myisam.cc檔案的int ha_myisam::check(THD *thd, HA_CHECK_OPT *check_opt)方法   if (!mi_is_crashed(file) &&       (((param.testflag & T_CHECK_ONLY_CHANGED) &&         !(share->state.changed &           (STATE_CHANGED | STATE_CRASHED | STATE_CRASHED_ON_REPAIR)) &&         share->state.open_count == 0) ||        ((param.testflag & T_FAST) &&         (share->state.open_count == (uint)(share->global_changed ? 1 : 0)))))     return HA_ADMIN_ALREADY_DONE; 而後面還會有下面這些檢查。這些也是fast與CHANGED/MEDIUM/EXTENDED等相比缺少的檢查： error = chk_status(&param, file);  // Not fatal   error = chk_size(&param, file);   if (!error) error |= chk_del(&param, file, param.testflag);   if (!error) error = chk_key(&param, file);   if (!error) {     if ((!(param.testflag & T_QUICK) &&          ((share->options &            (HA_OPTION_PACK_RECORD | HA_OPTION_COMPRESS_RECORD)) ||           (param.testflag & (T_EXTEND | T_MEDIUM)))) ||         mi_is_crashed(file)) {       uint old_testflag = param.testflag;       param.testflag |= T_MEDIUM;       if (!(error = init_io_cache(                 &param.read_cache, file->dfile, my_default_record_cache_size,                 READ_CACHE, share->pack.header_length, true, MYF(MY_WME)))) {         error = chk_data_link(&param, file, param.testflag & T_EXTEND);         end_io_cache(&(param.read_cache));       }       param.testflag = old_testflag;     }   } h）MEDIUM與EXTENDED主要的不同在mi_check.cc中的int chk_data_link(MI_CHECK *param, MI_INFO *info, int extend)方法的第1151行代碼 if (extend) {               int search_result =                   (keyinfo->flag & HA_SPATIAL)                       ? rtree_find_first(info, key, info->lastkey, key_length,                                          MBR_EQUAL | MBR_DATA)                       : _mi_search(info, keyinfo, info->lastkey, key_length,                                    SEARCH_SAME, info->s->state.key_root[key]);               if (search_result) {                 mi_check_print_error(param,                                      "Record at: %10s  "                                      "Can't find key for index: %2d",                                      llstr(start_recpos, llbuff), key + 1);                 if (error++ > MAXERR || !(param->testflag & T_VERBOSE))                   goto err2;               }             } else               key_checksum[key] +=                   mi_byte_checksum((uchar *)info->lastkey, key_length);
QUICK	不掃描行	MYISAM,  INNODB
FAST	僅檢查沒有正常close的表	MYISAM,  VIEW
CHANGED	僅檢查自上一次check後有變化的表 和沒有正常close的表	MYISAM,  VIEW
MEDIUM	根據delete_link掃描MYD資料行行以驗證被标記為deleted的連結是否有效， 會分别計算資料行中的key值和索引檔案中的key的校驗和，并進行比較是否相同。 預設選項。	MYISAM,  VIEW
EXTENDED	将MEDIUM的計算校驗和驗證資料的方式，換成了掃描所有索引key全部行記錄的方式。	MYISAM,  VIEW
checksum	QUICK	當表屬性CHECKSUM=1時，傳回已儲存的checnsum值 如果指定quick，但是checksum不為1，那麼傳回NULL	MYISAM,	a) 用于計算整表的校驗和資料，用來對資料遷移前後的表資料完整性進行校驗 b) 由于校驗和算法問題，實際不同的表内容可能得出相同的校驗和，但是不同的校驗和，表内容一定不同
EXTENDED	實時的一行一行的讀取并計算校驗和	MYISAM,  INNODB
analyze	收集表統計資訊，	收集統計資訊，統計資訊結果收集後存儲在information_schema.tables、information_schema.statistics、information_schema.COLUMN_STATISTICS(列直方圖)、information_schema.STATISTICS、information_schema.INNODB_TABLESTATS
optimize	重建表。重新組織表資料。釋放表檔案空間。	正常支援：MYISAM，INNODB，NDB	1，對于MYISAM引擎：消除碎片，排序索引block，更新統計資訊； 2，對于innodb：釋放多餘空間。大量删除資料後，将磁盤空間釋放回作業系統。預設使用online ddl ，alter table .. Force. 如果啟用skip-new或者 old_alter_table選項，則使用table  copy方法。 3，不支援空間索引。
skip-new啟用後可以支援其他存儲引擎。實際轉換為alter table。
repair table	QUICK	隻repaired索引資料。支援分區表；	MYISAM、CSV、ARCHIVE	upgrade更新表，修複損壞的索引，表資料， 2，NO_WRITE_TO_BINLOG、LOCAL不記錄repair table到binglog中。注意與repair table對源表的修改内容不會同步到從庫做區分； 3，USE_FRM,因為MYI檔案header儲存了MYISAM表的大量中繼資料，不使用MYI檔案頭，會導緻依賴此部分資訊的資料丢失，具體如下：      a）自增列最大值丢失；      b）delete_link丢失，導緻MYD檔案中已标記為删除的空間不能重用；      c）MYI檔案頭首4個位元組标志了檔案版本，不使用這個資訊，導緻repair table不會進行upgrade檢查更新；      d）MYI檔案中标記了表是否是compress格式，是以對于壓縮格式的表，會解析檔案失敗。（但是compress格式的表都是隻讀的，會用到repair的機會渺茫）      e）如果使用了與表檔案不同版本的repair，會報錯。 4，如果設定了avoid_temporal_upgrade 系統變量，則repair不會upgrade表。 5，可通過設定 myisam_max_sort_file_size 、key_buffer_size、myisam_sort_buffer_size、read_buffer_size等系統變量值提高repair效率。
EXTENDED	repair索引和資料。支援分區表；
USE_FRM	repair索引和資料。但是忽略MYI檔案header。僅使用資料字典存儲的表中繼資料。	MYISAM

此外，MYISAMCHK工具內建了check，analyze，optimize，repair功能于一身，還多了其他功能：

1，按照特定索引順序排序對應的MYD記錄，以增強按照特定索引排序的順序讀性能。-R, --sort-records=#

2，解壓myisampack壓縮的檔案。-u, --unpack

3，設定自增列的值。-A, --set-auto-increment[=value]。自增列的值儲存在MYI檔案頭中。

4，發現特定block中的記錄。-b,  --block-search=#

5，當MYD滿了之後（達到建表時指定的最大檔案大小），可以通過重建的方式增大最大大小。-D, --data-file-length=#

6，repair時，僅修改特定索引。-k, --keys-used=#

7，repair時，可自動備份。-B, --backup

myisamchk的全部選項可通過myisamchk --help檢視。具體使用時，可設定如下記憶體參數加快性能，最好設定tmp目錄指向其他目錄，因為預設的tmpdir使用的是linux作業系統的記憶體檔案系統，相當于完全記憶體操作。可能造成主機記憶體不足。

tmpdir                            (No default value)

key-buffer-size                   520192

read-buffer-size                  262136

write-buffer-size                 262136

sort-buffer-size                  2097144

myisam-sort-buffer-size           2097144

sort-key-blocks                   16

需要注意的是，myisamchk在檢查帶有MYI字尾的檔案時，會有報錯myisamchk: error: 140 when opening MyISAM-table 。這是個曆史悠久的BUG。https://bugs.mysql.com/bug.php?id=87729

可以這樣搞一下：

ls *.MYI | xargs basename -s .MYI|xargs  -I{}  /usr/local/mysql/mysql-5.7.27-el7-x86_64/bin/myisamchk -c -s -s