问题描述
mysql innodb删除大主表数据过慢,主表nms_managed_resource,下面有个各种不同的几十张字表用外键关联到nms_managed_resource的resourceId,子表的外键带有级联删除
问题出现的环境背景及自己尝试过哪些方法
set foreign_key_checks=0; //关闭外键检查
DELETE from NMS_MANAGED_RESOURCE where resourcetype=’Stor_Lun’ and parentresourceid>0 limit 1;
删除一条数据都很慢
相关代码
// 请把代码文本粘贴到下方(请勿用图片代替代码)
CREATE TABLE
nms_managed_resource
(
resourceId
bigint(20) NOT NULL AUTO_INCREMENT,
resourceName
varchar(255) DEFAULT NULL,
ipAddress
varchar(255) DEFAULT NULL,
resourceCategory
varchar(255) DEFAULT NULL,
resourceType
varchar(255) DEFAULT NULL,
resourceVersion
varchar(255) DEFAULT NULL,
pollingPeroid
int(11) DEFAULT NULL,
managedstatus
bit(1) DEFAULT NULL,
disableAlarmOnNonWorkingDay
bit(1) DEFAULT NULL,
resourceAlias
varchar(255) DEFAULT NULL,
createTime
varchar(255) DEFAULT NULL,
parentResourceId
bigint(20) DEFAULT NULL,
resourceavailability
int(11) DEFAULT NULL,
probeName
varchar(255) DEFAULT NULL,
pollingExpression
varchar(255) DEFAULT ‘5’,
timeUnit
int(11) DEFAULT ‘0’,
hourPoint
int(11) DEFAULT ‘0’,
weekPoint
int(11) DEFAULT ‘1’,
monthPoint
int(11) DEFAULT ‘1’,
accProtocol
int(11) DEFAULT ‘0’,
groupId
bigint(20) DEFAULT ‘1’,
vmid
varchar(255) DEFAULT NULL,
clusterservername
varchar(255) DEFAULT NULL,
clusternodename
varchar(255) DEFAULT NULL,
originResourceId
bigint(20) DEFAULT ‘0’,
synchronizeFlag
bit(1) DEFAULT b’0′,
companyId
bigint(20) DEFAULT ‘1’,
businessSystem
varchar(255) DEFAULT NULL,
PRIMARY KEY (
resourceId
),
KEY
index_parentResourceId
(
parentResourceId
),
KEY
index_resourceavailability
(
resourceavailability
),
KEY
index_on_ipAddress
(
ipAddress
),
KEY
resource_name
(
resourceName
),
KEY
resource_address
(
ipAddress
),
KEY
resource_category
(
resourceCategory
),
KEY
resource_type
(
resourceType
),
KEY
resource_alias
(
resourceAlias
),
KEY
FKB528B3AE1CIUYUI4956
(
groupId
),
KEY
nms_managedresource_originresource
(
originResourceId
),
KEY
nms_managedresource_probe
(
probeName
),
CONSTRAINT
FKB528B3AE1CIUYUI4956
FOREIGN KEY (
groupId
) REFERENCES
nms_group
(
id
)
) ENGINE=InnoDB AUTO_INCREMENT=173126673 DEFAULT CHARSET=utf8
你期待的结果是什么?实际看到的错误信息又是什么?
大家有没有什么好的方法?