I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen twice. The corruption also only happens on one index and the data itself seems fine.
The composite index in question spans two BigInt foreign key ID Columns on a table with 5M rows. I'm using InnoDB for all tables and running MySQL 5.6.23 on Amazon RDS. I looked to see if I could find anything in mysql-error but haven't found any entries in it.
To verify that the index corrupted, I run the following 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 |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+————————————————+
When it’s “fixed” the rows are roughly 48/1 instead of 3690/178
To fix it, it requires some combination of these 3 steps:
- alter table student drop KEY
schoolStatusIndex_idx
; alter table student add keyschoolStatusIndex_idx
(school_id
,student_status_id
); - alter table school engine=innodb; (rebuilds all indexes)
- alter table student engine=innodb;
Still, this has me baffled. What could be causing this kind of index corruption and how can I prevent it from happening moving forward?
Here's the trimmed output of SHOW CREATE TABLE
for student
, student_status
, and 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;
And some additional output that might be helpful:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 5753536512 |
+-------------------------+------------+
ANALYZE TABLE
would probably also "fix" it. This doesn't look like "corruption," merely inaccurate index statistics.SHOW CREATE TABLE
for both tables. Does theEXPLAIN
look different in other areas when it is 'corrupt'? Does the query run slower or faster when 'corrupt'? What version of MySQL are you running?EXPLAIN
only seems to change here. In a normal state, the query takes around 35 ms. When it's in the 'corrupt' state, the query takes an average 3500 ms or more. I'm on MySQL 5.6.23.SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
EXPLAIN FORMAT=JSON SELECT...;
. And try to catch both variants. Something funny may be going on with the "costs".