从例子可以看到当tmp_table_size=262144时，查询结果不对，而tmp_table_size=1610241024时查询结果是正确的。

分析

查询结果跟tmp_table_size有关，而tmp_table_size是控制查询时生成的临时表是MEMORY还是MyISAM类型的。临时表超过tmp_table_size会生成MyISAM类型的。 另外，查询计划中可以看到MATERIALIZED，MATERIALIZED优化是将子查询的结果存储在临时表中，避免多次执行子查询。 而5.6存在一个问题是，MATERIALIZED对MyISAM临时表中存在unique constraint的情况支持不友好。此问题直到5.7才解决，参见

  3. if (keyinfo->key_length >= table->file->max_key_length() ||
  4.     keyinfo->user_defined_key_parts > table->file->max_key_parts() ||
  5.     share->uniques)
  6. {
  7.   /* Can't create a key; Make a unique constraint instead of a key */
  8.   share->keys=    0;
  9.   share->uniques= 1;
  10.   using_unique_constraint=1;
  11.   memset(&uniquedef, 0, sizeof(uniquedef));
  12.   uniquedef.keysegs=keyinfo->user_defined_key_parts;
  13.   uniquedef.seg=seg;
  14.   uniquedef.null_are_equal=1;
  16.   /* Create extra column for hash value */
  17.   memset(*recinfo, 0, sizeof(**recinfo));
  18.   (*recinfo)->length=MI_UNIQUE_HASH_LENGTH;
  19.   (*recinfo)++;
  20.   share->reclength+=MI_UNIQUE_HASH_LENGTH;

而本例中，c1字段varchar(512)是utf8字符集 512*3 > 1000 超过最大key大小，正好进入上述代码逻辑。

子查询先物化到此临时表，然后后续查询从临时表读数据，然而这里对unique constraint的读取操作(ha_myisam::index_read_map)还不支持

  1. ha_myisam::index_read_map
  2. handler::ha_index_read_map 
  4. sub_select
  5. evaluate_join_record 
  6. sub_select
  7. do_select
  8. JOIN::exec
  9. mysql_execute_select
  10. mysql_select 
  11. handle_select

虽然5.7修复了此问题，但改动较大，同时还存在部分bug. 5.6采取了折衷的修复方法，当MyISAM临时表存在unique contraint时，则不采用MATERIALIZED优化，从而避免了产生MyISAM临时表。 修复方法参见bugfix

BUG 2 表不存在

现象

从备份集中恢复的实例中查询发现表不存在，但实际frm,idb文件都存在

  1. select * from t2;
  2. Table 'test.t2' doesn't exist

从备份的源实例中查看，貌似一切正常

  1. show create table t2;
  2. CREATE TABLE `t2` (
  3.   `c1` int(11) NOT NULL,
  4.   `c2` int(11) DEFAULT NULL,
  5.   PRIMARY KEY (`c1`),
  6.   CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  9. show create table t1;
  10. t1      CREATE TABLE `t1` (
  11.   `c1` int(11) NOT NULL,
  13.   PRIMARY KEY (`c1`)
  14. ) ENGINE=InnoDB DEFAULT CHARSET=latin1

首先，怀疑是备份集的问题。因此，重新备份后再测试，依然是表不存在。 排除备份集的问题后，通过调试源码的方法来找原因

上述堆栈是表在load过程中构建foreign信息。查找t2表列c2的foreign key时失败了。

  1. select * from information_schema.INNODB_SYS_TABLES t,information_schema.INNODB_SYS_indexes i,information_schema.INNODB_SYS_fields f where t.name='test/t2' and t.table_id=i.table_id and i.index_id=f.index_id;
  2. +----------+---------+------+--------+-------+-------------+------------+---------------+----------+---------+----------+------+----------+---------+-------+----------+------+-----+
  3. | TABLE_ID | NAME    | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | INDEX_ID | NAME | POS |
  4. +----------+---------+------+--------+-------+-------------+------------+---------------+----------+---------+----------+------+----------+---------+-------+----------+------+-----+
  5. |       21 | test/t2 |    1 |      5 |     7 | Antelope    | Compact    |             0 |       23 | PRIMARY |       21 |    3 |        1 |       3 |     7 |       23 | c1   |   0 |
  6. +----------+---------+------+--------+-------+-------------+------------+---------------+----------+---------+----------+------+----------+---------+-------+----------+------+-----+
  8. select * from information_schema.INNODB_SYS_FOREIGN f,information_schema.INNODB_SYS_FOREIGN_COLS fc where f.for_name='test/t2' and f.id=fc.id;
  9. +----------------+----------+----------+--------+------+----------------+--------------+--------------+-----+
  10. | ID             | FOR_NAME | REF_NAME | N_COLS | TYPE | ID             | FOR_COL_NAME | REF_COL_NAME | POS |
  11. +----------------+----------+----------+--------+------+----------------+--------------+--------------+-----+
  12. | test/t2_ibfk_1 | test/t2  | test/t1  |      1 |    0 | test/t2_ibfk_1 | c2           | c1           |   0 |
  13. +----------------+----------+----------+--------+------+----------------+--------------+--------------+-----+

场景恢复

为什么外键在t2上的索引会不存在呢，是参数FOREIGN_KEY_CHECKS捣的鬼，我们看看如下例子

  1. create table t1(c1 int primary key, c2 int) engine=innodb;
  2. create table t2(c1 int primary key, c2 int , key idx1(c2), foreign  key (c2) references t1(c1)) engine=innodb;
  3. set FOREIGN_KEY_CHECKS=1;
  4. alter table t2 drop key idx1;
  5. ERROR 1553 (HY000): Cannot drop index 'c2': needed in a foreign key constraint
  7. set FOREIGN_KEY_CHECKS=0;

删除后表还是可以正常访问的。但一旦表定义踢出缓存或数据库重启，重新加载数据字典信息时，就会出现前面堆栈中的找不到外键索引的问题，从而导致表不存在的错误。

这个错误是非常严重的，会导致用户无法访问数据。

一旦出现此种情况，需要修改代码绕过加载外键数据字典信息的错误，才能恢复出数据，比较麻烦。

而对于我们源实例的场景算比较幸运，t2的表定义还在内存中，这时只需要把idx1重新建回去即可。再重新备份就可以生成有效的备份集了。

修复方法是FOREIGN_KEY_CHECKS=0时不允许删除外键所在索引。