告警日志出现"which is different from the number of indexes 4 defined in the MySQL"报错

问题描述：告警日志出现"which is different from the number of indexes 4 defined in the
MySQL"报错，如下所示：

数据库：MySQL 5.7.21

1、告警日志

########################################
Error Detail ########################################

230921 21:30:00 [ERROR] Table test/test01 contains 5 indexes inside InnoDB, which
is different from the number of indexes 4 defined in the MySQL

230921 21:30:00 [ERROR] Table
test/test02 contains 5 indexes inside InnoDB, which
is different from the number of indexes 4 defined in the MySQL

 

说明：出现告警前3分钟有向各表添加索引.

2、异常原因

存储引擎和MySQL服务层中索引信息不一致.

3、测试过程

(root@localhost) [fruitsDB]
17:51:29 4> create table fruitsbak like fruits;

Query OK, 0 rows affected
(0.01 sec)

 

(root@localhost) [fruitsDB]
17:52:11 4> select count(*) from fruitsbak;

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (0.01 sec)

 

(root@localhost) [fruitsDB]
17:54:00 4> \! cp /mysql/data/fruitsDB/fruitsbak.frm
/mysql/data/fruitsDB/fruitsbak.frm.old

(root@localhost) [fruitsDB]
17:55:13 4> alter table fruitsbak add index idx_s_id(s_id);

Query OK, 0 rows affected
(0.01 sec)

Records: 0  Duplicates: 0 
Warnings: 0

 

--创建索引后备份.frm文件

(root@localhost) [fruitsDB]
18:09:31 4> \! cp /mysql/data/fruitsDB/fruitsbak.frm
/mysql/data/fruitsDB/fruitsbak.frm.old02

 

[mysql@mysql-leo-master fruitsDB]$
ls -ltr       

total 260

-rw-r-----. 1 mysql mysql     67 Aug 26 12:46 db.opt

-rw-r-----. 1 mysql mysql   8704 Sep 16 22:58 fruits.frm

-rw-r-----. 1 mysql mysql  98304 Sep 16 23:00 fruits.ibd

-rw-r-----. 1 mysql mysql   8704 Sep 23 18:09 fruitsbak.frm.old

-rw-r-----. 1 mysql mysql   8704 Sep 23 18:09 fruitsbak.frm

-rw-r-----. 1 mysql mysql
114688 Sep 23 18:09 fruitsbak.ibd

-rw-r-----. 1 mysql mysql   8704 Sep 23 18:10 fruitsbak.frm.old02

 

(root@localhost) [fruitsDB]
17:57:31 4> \! mv /mysql/data/fruitsDB/fruitsbak.frm.old
/mysql/data/fruitsDB/fruitsbak.frm

[mysql@mysql-leo-master
fruitsDB]$ ls -ltr

total 248

-rw-r-----. 1 mysql mysql     67 Aug 26 12:46 db.opt

-rw-r-----. 1 mysql mysql   8704 Sep 16 22:58 fruits.frm

-rw-r-----. 1 mysql mysql  98304 Sep 16 23:00 fruits.ibd

-rw-r-----. 1 mysql mysql   8704 Sep 23 18:09 fruitsbak.frm

-rw-r-----. 1 mysql mysql
114688 Sep 23 18:09 fruitsbak.ibd

-rw-r-----. 1 mysql mysql   8704 Sep 23 18:10 fruitsbak.frm.old02

 

(root@localhost) [fruitsDB]
17:58:11 4> flush tables;

Query OK, 0 rows affected
(0.01 sec)

 

(root@localhost) [fruitsDB]
17:59:42 4> select s_id from fruitsbak where s_id like '10%' limit 1;

Empty set (0.00 sec)

 

查看此时错误日志：

2023-09-23T10:00:55.371486Z 6
[Note] Access denied for user 'root'@'localhost' (using password: YES)

2023-09-23T10:01:04.119197Z 7
[ERROR] InnoDB: Table fruitsDB/fruitsbak contains 2 indexes inside InnoDB,
which is different from the number of indexes 1 defined in MySQL

2023-09-23T10:03:08.833201Z 4
[ERROR] InnoDB: Table fruitsDB/fruitsbak contains 2 indexes inside InnoDB,
which is different from the number of indexes 1 defined in MySQL

 

说明：此时成功模拟出异常现象.

 

(root@localhost) [fruitsDB]
18:03:08 4> explain select s_id from fruitsbak where s_id like '10%' limit
1;

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type |
table     | partitions | type |
possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | fruitsbak | NULL       | ALL 
| NULL          | NULL | NULL    | NULL |   
1 |   100.00 | Using where |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00
sec)

 

说明：此时select语句不会使用索引.

 

--接着恢复创建索引后的frm文件

(root@localhost) [fruitsDB]
18:12:17 4> \! mv /mysql/data/fruitsDB/fruitsbak.frm.old02
/mysql/data/fruitsDB/fruitsbak.frm

(root@localhost) [fruitsDB]
18:13:55 4> flush tables;

Query OK, 0 rows affected
(0.00 sec)

 

(root@localhost) [fruitsDB]
18:13:59 4> explain select s_id from fruitsbak where s_id like '10%' limit
1;

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

| id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

| 
1 | SIMPLE      | fruitsbak |
NULL       | index | idx_s_id      | idx_s_id | 4       | NULL |    1 |  
100.00 | Using where; Using index |

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

1 row in set, 1 warning (0.00
sec)

 

说明：如上所示，恢复创建索引之后的frm文件，select语句会使用到索引.

(root@localhost) [fruitsDB]
18:14:08 4> alter table fruitsbak engine=innodb;

Query OK, 0 rows affected
(0.01 sec)

Records: 0  Duplicates: 0 
Warnings: 0

 

(root@localhost) [fruitsDB]
18:15:38 4> flush tables;

Query OK, 0 rows affected
(0.01 sec)

 

(root@localhost) [fruitsDB]
18:15:46 4> explain select s_id from fruitsbak where s_id like '10%' limit
1;

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

| id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

| 
1 | SIMPLE      | fruitsbak |
NULL       | index | idx_s_id      | idx_s_id | 4       | NULL |    1 |  
100.00 | Using where; Using index |

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

1 row in set, 1 warning (0.00
sec)

 

(root@localhost) [fruitsDB]
18:16:30 4> flush tables;

Query OK, 0 rows affected
(0.00 sec)

 

(root@localhost) [fruitsDB]
18:17:42 4> show create table fruitsbak \G

*************************** 1.
row ***************************

       Table: fruitsbak

Create Table: CREATE TABLE
`fruitsbak` (

  `f_id` char(10) NOT NULL,

  `s_id` int(11) NOT NULL,

  `f_name` char(255) NOT NULL,

  `f_price` decimal(8,2) NOT NULL,

  `f_attribute` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`f_id`),

  KEY `idx_s_id` (`s_id`)

) ENGINE=InnoDB DEFAULT
CHARSET=utf8

1 row in set (0.00 sec)

 

说明：alter table fruitsbak engine=innodb基于frm的定义重建表.

 

参考文档：

https://www.percona.com/blog/2011/11/29/innodb-vs-mysql-index-counts/

https://blog.csdn.net/weixin_39789857/article/details/113939652