MySQL · 捉蟲動態 · 刪除索引導致表無法打開 · 數據庫內核月報

## 問題背景最近線上遇到一個問題，用戶重啟實例后發現有張表打不開了，經調研后發現是用戶之前的霸蠻操作導致的，下面給出復現步驟： ~~~ create table t1 (id int not null primary key, name varchar(100) not null) engine=innodb; create table t2 (id int not null primary key, fid int not null, name varchar(100) not null, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)) engine=innodb; insert into t1 values(1, 'test 1'),(2, 'test 2'),(3,'test 3'); insert into t2 values(1, 1, 'test 1'),(2, 1, 'test 2'),(3, 2, 'test 3'); show create table t2; show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `fid` int(11) NOT NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`), KEY `fk_fid` (`fid`), CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 霸蠻的刪掉外鍵約束用的索引 set foreign_key_checks=off; alter table t2 drop index `fk_fid`; -- 重啟mysqld前表t2正常 set foreign_key_checks=on; show create table t2; *************************** 1\. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `fid` int(11) NOT NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 重啟mysqld show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | t2 | +----------------+ show create table t2; ERROR 1146 (42S02): Table 'test.t2' doesn't exist ~~~ 重啟后t2表打不開了。 ## 問題分析在MySQL中，為了能夠快速的檢查外鍵約束，需要子表和父表對應的列上都要有索引，如果對應字段上沒有索引, 子表在建立外鍵約束的時候，會自動加上。 > MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. 子表上的外鍵索引正常情況是刪不掉，如果試圖去刪除的話會報錯： ~~~ ERROR 1553 (HY000): Cannot drop index 'fk_fid': needed in a foreign key constrain ~~~ 刪索引時的外鍵檢查邏輯在?`innobase_check_foreign_key_index()`函數中，感興趣的同學可以自己看下，調用棧如下： ~~~ #0 innobase_check_foreign_key_index #1 ha_innobase::prepare_inplace_alter_table #2 handler::ha_prepare_inplace_alter_table #3 mysql_inplace_alter_table #4 mysql_alter_table #5 Sql_cmd_alter_table::execute #6 mysql_execute_command #7 mysql_parse #8 dispatch_command #9 do_command #10 do_handle_one_connection #11 handle_one_connection #12 pfs_spawn_thread #13 start_thread #14 clone ~~~ 但是如果用戶設置了?`set foreign_key_checks=off`?后，刪除索引時就不會進入?`innobase_check_foreign_key_index()`?，所以索引能被刪掉。在上面的復現步驟中還可以看到，如果不重啟的話，表是能正常打開的，即使是做了flush tables，表也能正常打開，這又是為什么呢？我們知道MySQL有個table_open_cache，這是server層對打開表的緩存，flush tables會把這個cache清掉；InnoDB層的字典系統對打開的表也有一個緩存，基于這個緩存又構建了2個鏈表`dict_sys->table_LRU`?和?`dict_sys->table_non_LRU`，位于前者中的表緩存會被系統用LRU算法清理掉，后者不會，清理由InnoDB后臺線程做，flush tables 不會觸發清理。并且如果InnoDB表有外鍵的話，緩存是被放在`dict_sys->table_non_LRU`上的，所以不會被后臺線程清理掉，因此成功打開一次后就不會出問題了。關于含外鍵表緩存被放在?`dict_sys->table_non_LRU`上，具體的調用棧如下： ~~~ #0 dict_table_move_from_lru_to_non_lru #1 dict_foreign_add_to_cache #2 dict_load_foreign #3 dict_load_foreigns #4 dict_load_table #5 dict_table_open_on_name #6 ha_innobase::open #7 handler::ha_open #8 open_table_from_share #9 open_table #10 open_and_process_table #11 open_tables #12 open_tables #13 mysqld_show_create #14 mysql_execute_command #15 mysql_parse #16 dispatch_command #17 do_command #18 do_handle_one_connection #19 handle_one_connection #20 pfs_spawn_thread #21 start_thread #22 clone ~~~ 關于這點[官方文檔](https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_definition_cache)也有描述： > InnoDB system table instances and parent and child table instances with foreign key relationships are not placed on the LRU list and are not subject to eviction from memory. ## 問題解決通過上面的分析可以看出之所以表打不開，是因為用戶霸蠻的刪除了外鍵依賴的索引，但是能讓用戶通過SQL就把表弄的不能訪問，也算是數據庫系統的bug，也早有人給官方提了bug，詳見[bug#68148](https://bugs.mysql.com/bug.php?id=68148)、[bug#70260](http://bugs.mysql.com/bug.php?id=70260)、[bug#74659](https://bugs.mysql.com/bug.php?id=74659)。 MySQL 5.6.12 對此進行了修復，如果遇到這種情況表打不開的話，只需要先?`set foreign_key_checks=off`，然后表就可以打開了，這時把缺失的索引再加上就可以了，修復詳情見[patch](https://github.com/mysql/mysql-server/commit/b21b9953a710e8dfdd133eba942b1767fc0f5acd)。上面的修復是在出問題后，其實最好是能從源頭上杜絕，不讓用戶刪除外鍵約束需要的索引，因為這本身就是錯誤的操作，因此MySQL 5.7.5又出了一個[patch](https://github.com/mysql/mysql-server/commit/c1ed0eafcf3de23635e18f6d1f306c8fe97fa287)，讓用戶無法刪除外鍵約束的索引，即使用?`set foreign_key_checks=off`?關閉掉外鍵約束檢查。