问题背景
最近线上遇到一个问题，用户重启实例后发现有张表打不开了，经调研后发现是用户之前的霸蛮操作导致的，下面给出复现步骤：
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中，为了能够快速的检查外键约束，需要子表和父表对应的列上都要有索引，如果对应字段上没有索引, 子表在建立外键约束的时候，会自动加上。
子表上的外键索引正常情况是删不掉，如果试图去删除的话会报错：
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
关于这点官方文档也有描述：
问题解决
通过上面的分析可以看出之所以表打不开，是因为用户霸蛮的删除了外键依赖的索引，但是能让用户通过SQL就把表弄的不能访问，也算是数据库系统的bug，也早有人给官方提了bug，详见bug#68148、bug#70260、bug#74659。
MySQL 5.6.12 对此进行了修复，如果遇到这种情况表打不开的话，只需要先 set foreign_key_checks=off，然后表就可以打开了，这时把缺失的索引再加上就可以了，修复详情见patch。
上面的修复是在出问题后，其实最好是能从源头上杜绝，不让用户删除外键约束需要的索引，因为这本身就是错误的操作，因此MySQL 5.7.5又出了一个patch，让用户无法删除外键约束的索引，即使用 set foreign_key_checks=off 关闭掉外键约束检查。
