导读:存储引擎和Mysql服务层出现索引信息不一致错误提示
错误日志:
- [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.old
- root@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: 0
- root@localhost*5.5.48-log[test] >\! mv employees.frm.old employees.frm
- root@localhost*5.5.48-log[test] >\! chown mysql.mysql employees.frm
- root@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 MySQL
- 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)
可以看到语句是走不到索引的,语句分析和优化是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: 0
- root@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: employees
- Create 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=utf8mb4
- 1 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 MySQL
- 160508 17:44:01 [ERROR] Innodb could not find key n:o 1 with name idx_first_name from dict cache for table test/employees
- 160508 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
参考文档:
https://www.percona.com/blog/2011/11/29/innodb-vs-mysql-index-counts/
https://www.percona.com/doc/percona-server/5.1/management/innodb_fast_index_creation.html