存储引擎和Mysql服务层出现索引信息不一致错误提示

最新推荐文章于 2022-10-16 21:18:06 发布

weixin_34090562

最新推荐文章于 2022-10-16 21:18:06 发布

阅读量202

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/anthonyyau/blog/674476

版权

2019独角兽企业重金招聘Python工程师标准>>>

错误日志：

[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