防止 MySQL 索引損壞

我的 MySQL 數據庫經常出現問題，其中一個索引特別是已損壞。最初我大約每 2-3 週看到一次腐敗事件，而這週我已經看到它發生了兩次。損壞也只發生在一個索引上，數據本身似乎很好。

有問題的複合索引跨越一個有 5M 行的表上的兩個 BigInt 外鍵 ID 列。我對所有表都使用 InnoDB，並在 Amazon RDS 上執行 MySQL 5.6.23。我查看是否可以在 mysql-error 中找到任何內容，但沒有在其中找到任何條目。

為了驗證索引是否損壞，我執行以下命令EXPLAIN：

mysql> explain SELECT * FROM  student INNER JOIN school ON school.id = student.school_id WHERE student.student_status_id IN (3, 4, 7) ORDER BY student.id desc LIMIT 0, 25;

+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys                                                   | key                       | key_len | ref                   | rows | Extra                           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
|  1 | SIMPLE      | school     | ALL  | PRIMARY                                                         | NULL                      | NULL    | NULL                  | 3690 | Using temporary; Using filesort |
|  1 | SIMPLE      | student    | ref  | student_status_id_idx,school_id_idx,schoolStatusIndex_idx       | schoolStatusIndex_idx     | 8       | school.id             |  178 | Using index condition           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+————————————————+

當它“固定”時，行大約是 48/1 而不是 3690/178

要修復它，它需要以下 3 個步驟的某種組合：

1. 更改表學生刪除鍵schoolStatusIndex_idx；更改表學生添加鍵schoolStatusIndex_idx（school_id，student_status_id）；
2. 改變表學校引擎=innodb；（重建所有索引）
3. 更改表學生引擎=innodb；

不過，這讓我很困惑。什麼可能導致這種索引損壞以及如何防止它繼續發生？

SHOW CREATE TABLE這是for student、student_status和的修剪輸出school：

CREATE TABLE `student_status` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 `code` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


CREATE TABLE `student` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `school_id` bigint(20) NOT NULL,
 `phone` varchar(10) NOT NULL,
 `student_status_id` bigint(20) NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `student_status_id_idx` (`student_status_id`),
 KEY `school_id_idx` (`school_id`),
 KEY `schoolStatusIndex_idx` (`school_id`,`student_status_id`),
 CONSTRAINT `student_student_status_id_student_status_id` FOREIGN KEY (`student_status_id`) REFERENCES `student_status` (`id`),
 CONSTRAINT `student_school_id_school_id` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=2582686 DEFAULT CHARSET=latin1;


CREATE TABLE `school` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 `description` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=4350 DEFAULT CHARSET=latin1;

還有一些可能有用的額外輸出：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5753536512 |
+-------------------------+------------+

我懷疑 MySQL 查詢優化器偶爾會選擇次優路徑。我不確定為什麼它有時會使用索引，有時不使用，也不知道為什麼它似乎與相關索引有關。

我認為查詢本身不能被索引完全覆蓋（它包括一個ORDER BY我沒有覆蓋的）。我對查詢進行了更改ORDER BY student.created_at並添加了一個索引，(created_at, school_id, student_status_id)現在一切看起來都很好。

引用自：https://dba.stackexchange.com/questions/131336