MySQL · 捉虫动态 · 删除索引导致表无法打开

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

问题背景

最近线上遇到一个问题,用户重启实例后发现有张表打不开了,经调研后发现是用户之前的霸蛮操作导致的,下面给出复现步骤:

create table t1 (id int not null primary key, name varchar(100) not null) engine=innodb;
create table t2 (id int not null primary key, fid int not null, name varchar(100) not null, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)) engine=innodb;
insert into t1 values(1, 'test 1'),(2, 'test 2'),(3,'test 3');
insert into t2 values(1, 1, 'test 1'),(2, 1, 'test 2'),(3, 2, 'test 3');
show create table t2;
show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`fid` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_fid` (`fid`),
CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-- 霸蛮的删掉外键约束用的索引
set foreign_key_checks=off;
alter table t2 drop index `fk_fid`;


-- 重启mysqld前表t2正常
set foreign_key_checks=on;
show create table t2;
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`fid` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-- 重启mysqld
show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
show create table t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
AI 代码解读

重启后t2表打不开了。

问题分析

在MySQL中,为了能够快速的检查外键约束,需要子表和父表对应的列上都要有索引,如果对应字段上没有索引, 子表在建立外键约束的时候,会自动加上。

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

子表上的外键索引正常情况是删不掉,如果试图去删除的话会报错:

ERROR 1553 (HY000): Cannot drop index 'fk_fid': needed in a foreign key constrain
AI 代码解读

删索引时的外键检查逻辑在 innobase_check_foreign_key_index()函数中,感兴趣的同学可以自己看下,调用栈如下:

#0  innobase_check_foreign_key_index
#1  ha_innobase::prepare_inplace_alter_table
#2  handler::ha_prepare_inplace_alter_table
#3  mysql_inplace_alter_table
#4  mysql_alter_table
#5  Sql_cmd_alter_table::execute
#6  mysql_execute_command
#7  mysql_parse
#8  dispatch_command
#9  do_command
#10 do_handle_one_connection
#11 handle_one_connection
#12 pfs_spawn_thread
#13 start_thread
#14 clone
AI 代码解读

但是如果用户设置了 set foreign_key_checks=off 后,删除索引时就不会进入 innobase_check_foreign_key_index() ,所以索引能被删掉。

在上面的复现步骤中还可以看到,如果不重启的话,表是能正常打开的,即使是做了flush tables,表也能正常打开,这又是为什么呢?

我们知道MySQL有个table_open_cache,这是server层对打开表的缓存,flush tables会把这个cache清掉;InnoDB层的字典系统对打开的表也有一个缓存,基于这个缓存又构建了2个链表dict_sys->table_LRU 和 dict_sys->table_non_LRU,位于前者中的表缓存会被系统用LRU算法清理掉,后者不会,清理由InnoDB后台线程做,flush tables 不会触发清理。并且如果InnoDB表有外键的话,缓存是被放在dict_sys->table_non_LRU上的,所以不会被后台线程清理掉,因此成功打开一次后就不会出问题了。关于含外键表缓存被放在 dict_sys->table_non_LRU 上,具体的调用栈如下:

#0  dict_table_move_from_lru_to_non_lru
#1  dict_foreign_add_to_cache
#2  dict_load_foreign
#3  dict_load_foreigns
#4  dict_load_table
#5  dict_table_open_on_name
#6  ha_innobase::open
#7  handler::ha_open
#8  open_table_from_share
#9  open_table
#10 open_and_process_table
#11 open_tables
#12 open_tables
#13 mysqld_show_create
#14 mysql_execute_command
#15 mysql_parse
#16 dispatch_command
#17 do_command
#18 do_handle_one_connection
#19 handle_one_connection
#20 pfs_spawn_thread
#21 start_thread
#22 clone
AI 代码解读

关于这点官方文档也有描述:

InnoDB system table instances and parent and child table instances with foreign key relationships are not placed on the LRU list and are not subject to eviction from memory.

问题解决

通过上面的分析可以看出之所以表打不开,是因为用户霸蛮的删除了外键依赖的索引,但是能让用户通过SQL就把表弄的不能访问,也算是数据库系统的bug,也早有人给官方提了bug,详见bug#68148bug#70260bug#74659

MySQL 5.6.12 对此进行了修复,如果遇到这种情况表打不开的话,只需要先 set foreign_key_checks=off,然后表就可以打开了,这时把缺失的索引再加上就可以了,修复详情见patch

上面的修复是在出问题后,其实最好是能从源头上杜绝,不让用户删除外键约束需要的索引,因为这本身就是错误的操作,因此MySQL 5.7.5又出了一个patch,让用户无法删除外键约束的索引,即使用 set foreign_key_checks=off 关闭掉外键约束检查。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
db匠
+关注
目录
打赏
0
0
0
0
9495
分享
相关文章
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
244 66
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
177 9
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
81 22
 MySQL秘籍之索引与查询优化实战指南
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
69 10
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
79 18
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
60 8
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
75 7
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
102 5
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
144 7

相关产品

  • 云数据库 RDS MySQL 版
  • AI助理

    你好,我是AI助理

    可以解答问题、推荐解决方案等