MySQL 8.0.46 ベースに見ていく。
まずは俺の好きな sql/sql_yacc.yy からダイブ。 CHECK TABLE の定義はこのへん。
9627 check_table_stmt:
9628 CHECK_SYM table_or_tables table_list opt_mi_check_types
9629 {
9630 $$= NEW_PTN PT_check_table_stmt(YYMEM_ROOT, $3,
9631 $4.flags, $4.sql_flags);
9632 }
9633 ;
9634
9635 opt_mi_check_types:
9636 %empty { $$.flags = T_MEDIUM; $$.sql_flags= 0; }
9637 | mi_check_types
9638 ;
9639
9640 mi_check_types:
9641 mi_check_type
9642 | mi_check_type mi_check_types
9643 {
9644 $$.flags= $1.flags | $2.flags;
9645 $$.sql_flags= $1.sql_flags | $2.sql_flags;
9646 }
9647 ;
9648
9649 mi_check_type:
9650 QUICK
9651 { $$.flags= T_QUICK; $$.sql_flags= 0; }
9652 | FAST_SYM
9653 { $$.flags= T_FAST; $$.sql_flags= 0; }
9654 | MEDIUM_SYM
9655 { $$.flags= T_MEDIUM; $$.sql_flags= 0; }
9656 | EXTENDED_SYM
9657 { $$.flags= T_EXTEND; $$.sql_flags= 0; }
9658 | CHANGED
9659 { $$.flags= T_CHECK_ONLY_CHANGED; $$.sql_flags= 0; }
9660 | FOR_SYM UPGRADE_SYM
9661 { $$.flags= 0; $$.sql_flags= TT_FOR_UPGRADE; }
9662 ;
フラグの定数名が分かるくらいなのでさっさと PT_check_table_stmt に飛ぶ。
3211 Sql_cmd *PT_check_table_stmt::make_cmd(THD *thd) {
3212 thd->lex->sql_command = SQLCOM_CHECK;
3213
3214 LEX *const lex = thd->lex;
3215 Query_block *const select = lex->current_query_block();
3216
3217 if (lex->sphead) {
3218 my_error(ER_SP_BADSTATEMENT, MYF(0), "CHECK");
3219 return nullptr;
3220 }
3221
3222 lex->check_opt.flags |= m_flags;
3223 lex->check_opt.sql_flags |= m_sql_flags;
3224 if (select->add_tables(thd, m_table_list, TL_OPTION_UPDATING, TL_UNLOCK,
3225 MDL_SHARED_READ))
3226 return nullptr;
3227
3228 thd->lex->alter_info = &m_alter_info;
3229 return new (thd->mem_root) Sql_cmd_check_table(&m_alter_info);
3230 }
Sql_cmd_check_table に飛ぶ。
1755 bool Sql_cmd_check_table::execute(THD *thd) {
1756 Table_ref *first_table = thd->lex->query_block->get_table_list();
1757 thr_lock_type lock_type = TL_READ_NO_INSERT;
1758 bool res = true;
1759 DBUG_TRACE;
1760
1761 if (check_table_access(thd, SELECT_ACL, first_table, true, UINT_MAX, false))
1762 goto error; /* purecov: inspected */
1763 thd->enable_slow_log = opt_log_slow_admin_statements;
1764
1765 res = mysql_admin_table(thd, first_table, &thd->lex->check_opt, "check",
1766 lock_type, false, false, HA_OPEN_FOR_REPAIR, nullptr,
1767 &handler::ha_check, 1, m_alter_info, true);
1768
1769 thd->lex->query_block->m_table_list.first = first_table;
1770 thd->lex->query_tables = first_table;
1771
1772 error:
1773 return res;
1774 }
mysql_admin_table に飛ぶ。この時、10番目の引数が handler::ha_check になっているので
693 /*
694 RETURN VALUES
695 false Message sent to net (admin operation went ok)
696 true Message should be sent by caller
697 (admin operation or network communication failed)
698 */
699 static bool mysql_admin_table(
700 THD *thd, Table_ref *tables, HA_CHECK_OPT *check_opt,
701 const char *operator_name, thr_lock_type lock_type, bool open_for_modify,
702 bool repair_table_use_frm, uint extra_open_options,
703 int (*prepare_func)(THD *, Table_ref *, HA_CHECK_OPT *),
704 int (handler::*operator_func)(THD *, HA_CHECK_OPT *), int check_view,
705 Alter_info *alter_info, bool need_to_acquire_shared_backup_lock) {
案の定 mysql_admin_table 側では int (handler::*operator_func)(THD *, HA_CHECK_OPT *) として受け取られていて
1107 if (check_opt && (check_opt->sql_flags & TT_FOR_UPGRADE) != 0) {
1108 if (table->table->s->tmp_table) {
1109 result_code = HA_ADMIN_OK;
1110 } else {
1111 dd::String_type snam = dd::make_string_type(table->table->s->db);
1112 dd::String_type tnam =
1113 dd::make_string_type(table->table->s->table_name);
1114
1115 Check_result cr = check_for_upgrade(thd, snam, tnam, [&]() {
1116 DBUG_PRINT("admin", ("calling operator_func '%s'", operator_name));
1117 return (table->table->file->*operator_func)(thd, check_opt);
1118 });
1119
1120 result_code = cr.second;
1121 if (cr.first) {
1122 goto err;
1123 }
1124 }
1125 }
1126 // Some other admin COMMAND
1127 else {
1128 DBUG_PRINT("admin", ("calling operator_func '%s'", operator_name));
1129 result_code = (table->table->file->*operator_func)(thd, check_opt);
1130 }
1131 DBUG_PRINT("admin", ("operator_func returned: %d", result_code));
まあ結局 handler::ha_check を叩くことになる。
4680 int handler::ha_check(THD *thd, HA_CHECK_OPT *check_opt) {
4681 int error;
4682 assert(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK);
4683
4684 if ((table->s->mysql_version >= MYSQL_VERSION_ID) &&
4685 (check_opt->sql_flags & TT_FOR_UPGRADE))
4686 return 0;
4687
4688 if (table->s->mysql_version < MYSQL_VERSION_ID) {
4689 // Check for old temporal format if avoid_temporal_upgrade is disabled.
4690 mysql_mutex_lock(&LOCK_global_system_variables);
4691 const bool check_temporal_upgrade = !avoid_temporal_upgrade;
4692 mysql_mutex_unlock(&LOCK_global_system_variables);
4693
4694 if ((error = check_table_for_old_types(table, check_temporal_upgrade)))
4695 return error;
4696 error = ha_check_for_upgrade(check_opt);
4697 if (error && (error != HA_ADMIN_NEEDS_CHECK)) return error;
4698 if (!error && (check_opt->sql_flags & TT_FOR_UPGRADE)) return 0;
4699 }
4700 return check(thd, check_opt);
4701 }
これは結局ストレージエンジン層の check メソッドを呼ぶので
$ grep 'int check(' storage/ -r
storage/archive/ha_archive.h: int check(THD *thd, HA_CHECK_OPT *check_opt) override;
storage/csv/ha_tina.h: int check(THD *thd, HA_CHECK_OPT *check_opt) override;
storage/innobase/handler/ha_innodb.h: int check(THD *thd, HA_CHECK_OPT *check_opt) override;
storage/innobase/handler/ha_innopart.h: int check(THD *thd, HA_CHECK_OPT *check_opt) override;
storage/myisam/ha_myisam.h: int check(THD *thd, HA_CHECK_OPT *check_opt) override;
storage/myisammrg/ha_myisammrg.h: int check(THD *thd, HA_CHECK_OPT *check_opt) override;
storage/temptable/include/temptable/handler.h: int check(THD *, HA_CHECK_OPT *) override;
こう分かれていて、まあ興味があるのはInnoDBなので storage/innobase/handler/ha_innodb.h から辿っていく。
18152 /** Tries to check that an InnoDB table is not corrupted. If corruption is
18153 noticed, prints to stderr information about it. In case of corruption
18154 may also assert a failure and crash the server.
18155 @return HA_ADMIN_CORRUPT or HA_ADMIN_OK */
18156
18157 int ha_innobase::check(THD *thd, /*!< in: user thread handle */
18158 HA_CHECK_OPT *check_opt) /*!< in: check options */
18159 {
一応ここまで check_opt は引き回され続けてはいる。
18218 for (index = m_prebuilt->table->first_index(); index != nullptr;
18219 index = index->next()) {
18220 /* If this is an index being created or dropped, skip */
18221 if (!index->is_committed()) {
18222 continue;
18223 }
このループから、フラグと言うか修飾子というかによらずセカンダリインデックスも範囲なんだってことはわかった。
18225 if (!(check_opt->flags & T_QUICK) && !index->is_corrupted()) {
18226 /* Enlarge the fatal lock wait timeout during
18227 CHECK TABLE. */
18228 srv_fatal_semaphore_wait_extend.fetch_add(1);
18229
18230 bool valid = btr_validate_index(index, m_prebuilt->trx, false);
18231
18232 /* Restore the fatal lock wait timeout after
18233 CHECK TABLE. */
18234 srv_fatal_semaphore_wait_extend.fetch_sub(1);
18235
18236 if (!valid) {
18237 is_ok = false;
18238
18239 push_warning_printf(thd, Sql_condition::SL_WARNING, ER_NOT_KEYFILE,
18240 "InnoDB: The B-tree of"
18241 " index %s is corrupted.",
18242 index->name());
18243 continue;
18244 }
18245 }
CHECK TABLE .. QUICK でない場合は btr_validate_index に入る。
18281 size_t max_threads = thd_parallel_read_threads(m_prebuilt->trx->mysql_thd);
18282
18283 /* Scan this index. */
18284 if (dict_index_is_spatial(index)) {
18285 ret = row_count_rtree_recs(m_prebuilt, &n_rows, &n_dups);
18286 if ((check_opt->flags & T_EXTEND) && (ret == DB_SUCCESS) &&
18287 !(n_rows < n_rows_in_table || n_dups < n_rows - n_rows_in_table)) {
18288 /* For CHECK TABLE EXTENDED; we also want to make sure that MBR stored
18289 in SPATIAL Index is matching the MBR of geometry stored in Clustered
18290 record. */
18291 m_prebuilt->need_to_access_clustered = true;
18292 n_rows = 0;
18293 n_dups = 0;
18294 ret = row_count_rtree_recs(m_prebuilt, &n_rows, &n_dups);
18295 }
18296 } else {
18297 ret = row_scan_index_for_mysql(m_prebuilt, index, max_threads, true,
18298 &n_rows);
18299 }
パラレルでやるっぽいことと、空間インデックスは CHECK TABLE .. EXTEND の時だけ舐めることが分かった。
18309 if (ret == DB_INTERRUPTED || thd_killed(m_user_thd)) {
18310 /* Do not report error since this could happen
18311 during shutdown */
18312 break;
18313 }
18314 if (ret != DB_SUCCESS) {
18315 /* Assume some kind of corruption. */
18316 push_warning_printf(thd, Sql_condition::SL_WARNING, ER_NOT_KEYFILE,
18317 "InnoDB: The B-tree of"
18318 " index %s is corrupted.",
18319 index->name());
18320 is_ok = false;
18321 dict_set_corrupted(index);
18322 }
18323
18324 if (index == m_prebuilt->table->first_index()) {
18325 n_rows_in_table = n_rows;
18326 } else if (!(index->type & DICT_FTS) && (n_rows != n_rows_in_table) &&
18327 (!index->is_multi_value()) &&
18328 (!dict_index_is_spatial(index) || (n_rows < n_rows_in_table) ||
18329 (n_dups < n_rows - n_rows_in_table))) {
18330 push_warning_printf(thd, Sql_condition::SL_WARNING, ER_NOT_KEYFILE,
18331 "InnoDB: Index '%-.200s' contains %lu"
18332 " entries, should be %lu.",
18333 index->name(), (ulong)n_rows, (ulong)n_rows_in_table);
18334 is_ok = false;
18335 dict_set_corrupted(index);
18336 }
18337 }
row_scan_index_for_mysql の結果でメッセージなど出し分けつつ、18337行目がインデックスのforループの終端。あとは結果を返すだけ。
ということで結論。
- データ行(クラスタインデックス) だけじゃなくてセカンダリキーもちゃんと見る
- 空間インデックスがある時は
CHECK TABLE .. EXTEND で空間インデックスも見る
CHECK TABLE .. QUICK の時にかっ飛ばす処理は btr_validate_index だけだけどこれが結構速くなる上に全部のインデックスを読むには読むので、地味にバッファプールをあっためられる（ SELECT /*+ INDEX(t PRIMARY) */ COUNT(*) FROM t をインデックスごとにループさせるのとそんな時間変わらない )
