错误日志：

[ERROR] Table vip_cube/imp_sup_dm_sup_brand_name_goods_online_half_hm contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MySQL

存储引擎和Mysql服务层出现索引统计信息不一致，是否进行了DDL操作(创建了索引？出现这个错误，新创建的索引是否能使用？)

• 问题重现：

root@localhost*5.5.48-log[test] >create table employees like employees.employees;Query OK, 0 rows affected (0.21 sec)root@localhost*5.5.48-log[test] > \! cp employees.frm employees.frm.oldroot@localhost*5.5.48-log[test] >alter table employees add index idx_first_name(first_name);Query OK, 0 rows affected (0.64 sec)Records: 0 Duplicates: 0 Warnings: 0root@localhost*5.5.48-log[test] >\! mv employees.frm.old employees.frmroot@localhost*5.5.48-log[test] >\! chown mysql.mysql employees.frmroot@localhost*5.5.48-log[test] >flush tables;root@localhost*5.5.48-log[test] >select first_name from employees where first_name like 'a%' limit 1;Empty set (0.00 sec)

查看错误日志：

[ERROR] Table test/employees contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MySQLroot@localhost*5.5.48-log[test] >explain select first_name from employees where first_name like 'a%' limit 1;+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 1 | Using where |+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)

可以看到语句是走不到索引的，语句分析和优化是mysql server完成？

• 然后恢复创建索引后的frm文件：

root@localhost*5.5.48-log[test] > mv employees.frm.2 employees.frm – `employees.frm.2之前备份了`root@localhost*5.5.48-log[test] >flush tables;Query OK, 0 rows affected (0.02 sec)root@localhost*5.5.48-log[test] >explain select first_name from employees where first_name like 'a%' limit 1;+----+-------------+-----------+-------+----------------+----------------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+-------+----------------+----------------+---------+------+------+--------------------------+| 1 | SIMPLE | employees | index | idx_first_name | idx_first_name | 58 | NULL | 1 | Using where; Using index |+----+-------------+-----------+-------+----------------+----------------+---------+------+------+--------------------------+1 row in set (0.00 sec)root@localhost*5.5.48-log[test] >alter table employees engine=innodb;Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0root@localhost*5.5.48-log[test] >flush tables;Query OK, 0 rows affected (0.04 sec)root@localhost*5.5.48-log[test] >explain select first_name from employees where first_name like 'a%' limit 1;+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 1 | Using where |+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)

然后恢复有索引的frm文件

root@localhost*5.5.48-log[test] >flush tables;Query OK, 0 rows affected (0.04 sec)root@localhost*5.5.48-log[test] >show create table employees\G;*************************** 1. row ***************************Table: employeesCreate Table: CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` enum('M','F') NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`),KEY `idx_first_name` (`first_name`) – `索引是可以看到，查frm文件`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)ERROR:No query specified

但是日志会报如下错，说明存储引擎的索引已经在执行alter table employees engine=innodb;删除(重建表基于frm定义) ：

160508 17:44:01 [ERROR] Table test/employees contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MySQL160508 17:44:01 [ERROR] Innodb could not find key n:o 1 with name idx_first_name from dict cache for table test/employees160508 17:44:01 [ERROR] Table test/employees contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

• 参考文档：