时间戳

首页 > 解决方案 > mysql 在添加新索引时有时会删除旧索引？

mysql - mysql 在添加新索引时有时会删除旧索引？

问题描述

TL;博士

我们已经在虚拟机上看到，添加 UNIQUE 索引会导致自动删除较旧的非 UNIQUE 索引。

我无法在任何其他机器上重现这个（还没有？），如果我这样做：

mysql -e 'create db2'
mysqldump db1 | mysql db2

在将新索引添加到 db1 之前，然后尝试在 db2 上添加索引，旧索引不会从 db2 中删除。时髦！

我们有一个带有 db1 的机器的快照，并且可以在现有的 db1 数据库中重现它......

有人知道发生了什么吗？我们有脚本来自动添加/删除索引，但开始失败是因为在那台机器上，旧索引不再存在。

当转储/恢复周期导致问题消失时，就很难理解、重现问题并将问题简化为一个简单的示例。

细节

在添加新索引（来自SHOW CREATE TABLE）之前，有问题的表看起来像这样。注意monitoredTableRowID关键：

CREATE TABLE `cfgAttributeInstances` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `attributeID` int(10) unsigned DEFAULT NULL,
  `nodeID` int(10) unsigned DEFAULT NULL,
  `groupID` int(10) unsigned DEFAULT NULL,
  `statisticID` int(10) unsigned DEFAULT NULL,
  `nodeStatisticID` int(10) unsigned DEFAULT NULL,
  `serviceID` int(10) unsigned DEFAULT NULL,
  `nodeServiceID` int(10) unsigned DEFAULT NULL,
  `nodeComponentID` int(10) unsigned DEFAULT NULL,
  `syslogFilterID` int(10) unsigned DEFAULT NULL,
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `nodeID` (`nodeID`,`attributeID`),
  UNIQUE KEY `groupID` (`groupID`,`attributeID`),
  UNIQUE KEY `statisticID` (`statisticID`,`attributeID`),
  UNIQUE KEY `nodeStatisticID` (`nodeStatisticID`,`attributeID`),
  UNIQUE KEY `serviceID` (`serviceID`,`attributeID`),
  UNIQUE KEY `nodeServiceID` (`nodeServiceID`,`attributeID`),
  KEY `attributeID` (`attributeID`),
  KEY `monitoredTableRowID` (`nodeComponentID`),
  KEY `syslogFilterID` (`syslogFilterID`),
  CONSTRAINT `cfgAttributeInstances_ibfk_1` FOREIGN KEY (`attributeID`) REFERENCES `cfgAttributes` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_2` FOREIGN KEY (`nodeID`) REFERENCES `cfgNodes` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_3` FOREIGN KEY (`groupID`) REFERENCES `cfgGroups` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_4` FOREIGN KEY (`statisticID`) REFERENCES `cfgStatistics` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_5` FOREIGN KEY (`nodeStatisticID`) REFERENCES `cfgNodeStatistics` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_6` FOREIGN KEY (`serviceID`) REFERENCES `cfgServices` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_7` FOREIGN KEY (`nodeServiceID`) REFERENCES `cfgNodeServices` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_8` FOREIGN KEY (`nodeComponentID`) REFERENCES `cfgNodeComponents` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_9` FOREIGN KEY (`syslogFilterID`) REFERENCES `cfgSyslogFilters` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='CapMon Attribute instances';

我添加了这个索引：

ALTER TABLE cfgAttributeInstances ADD
    UNIQUE new_nodeComponentID (nodeComponentID, attributeID)

添加该索引后看起来像这样：

CREATE TABLE `cfgAttributeInstances` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `attributeID` int(10) unsigned DEFAULT NULL,
  `nodeID` int(10) unsigned DEFAULT NULL,
  `groupID` int(10) unsigned DEFAULT NULL,
  `statisticID` int(10) unsigned DEFAULT NULL,
  `nodeStatisticID` int(10) unsigned DEFAULT NULL,
  `serviceID` int(10) unsigned DEFAULT NULL,
  `nodeServiceID` int(10) unsigned DEFAULT NULL,
  `nodeComponentID` int(10) unsigned DEFAULT NULL,
  `syslogFilterID` int(10) unsigned DEFAULT NULL,
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `nodeID` (`nodeID`,`attributeID`),
  UNIQUE KEY `groupID` (`groupID`,`attributeID`),
  UNIQUE KEY `statisticID` (`statisticID`,`attributeID`),
  UNIQUE KEY `nodeStatisticID` (`nodeStatisticID`,`attributeID`),
  UNIQUE KEY `serviceID` (`serviceID`,`attributeID`),
  UNIQUE KEY `nodeServiceID` (`nodeServiceID`,`attributeID`),
  UNIQUE KEY `new_nodeComponentID` (`nodeComponentID`,`attributeID`),
  KEY `attributeID` (`attributeID`),
  KEY `syslogFilterID` (`syslogFilterID`),
  CONSTRAINT `cfgAttributeInstances_ibfk_1` FOREIGN KEY (`attributeID`) REFERENCES `cfgAttributes` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_2` FOREIGN KEY (`nodeID`) REFERENCES `cfgNodes` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_3` FOREIGN KEY (`groupID`) REFERENCES `cfgGroups` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_4` FOREIGN KEY (`statisticID`) REFERENCES `cfgStatistics` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_5` FOREIGN KEY (`nodeStatisticID`) REFERENCES `cfgNodeStatistics` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_6` FOREIGN KEY (`serviceID`) REFERENCES `cfgServices` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_7` FOREIGN KEY (`nodeServiceID`) REFERENCES `cfgNodeServices` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_8` FOREIGN KEY (`nodeComponentID`) REFERENCES `cfgNodeComponents` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `cfgAttributeInstances_ibfk_9` FOREIGN KEY (`syslogFilterID`) REFERENCES `cfgSyslogFilters` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='CapMon Attribute instances';

但是，嘿！monitoredTableRowID指数去哪儿了？

同样，我无法重现这一点。如果我执行mysqldump > dump.db/mysql < dump.db循环，monitoredTableRowID则不会消失...

关于发生了什么的任何想法？

标签： mysql

解决方案

复合键 withnodeComponentID导致非复合键 withnodeComponentID在它所使用的外键引用的上下文中是冗余的。MySQL 自动删除冗余隐式索引。该行为取决于列索引排序，因为删除的索引将始终是定义的复合键中的第一列。[原文如此]

这些问题是由添加外键时创建的自动隐式索引特别引起的CONSTRAINT，并且显式定义的索引SHOW CREATE TABLE也由mysqldump.

如果子表上已经有可以支持外键的显式定义的索引，则忽略该index_name值。否则，MySQL会隐式创建外键索引... [原文如此]

MySQL 需要外键和引用键上的索引，以便外键检查可以快速且不需要表扫描。在引用表中，必须有一个索引，其中外键列按相同顺序列为第一列。如果引用表不存在，则会在引用表上自动创建 此类 索引。如果您创建另一个可用于强制外键约束的索引，则此索引可能会在稍后被静默删除。index_name，如果给定，则如前所述使用。[原文如此]

重现问题

隐式键示例db-fiddle

注意 -INDEX FK_BAR_FOO (foo_id)没有明确定义。

DROP TABLE IF EXISTS `BAR`;
DROP TABLE IF EXISTS `FOO`;

CREATE TABLE `FOO` (
    `id` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB
;

CREATE TABLE `BAR` (
    `id` INT(11) NOT NULL,
    `foo_id` INT(11) NOT NULL,
    `b` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `FK_BAR_FOO` FOREIGN KEY (`foo_id`) REFERENCES `FOO` (`id`) ON DELETE CASCADE
)
ENGINE=InnoDB
;

SHOW CREATE TABLE `BAR`;

结果

INDEX FK_BAR_FOO (foo_id)在 中显式定义SHOW CREATE TABLE，但由 MySQL 隐式创建。

CREATE TABLE `BAR` (
    `id` INT(11) NOT NULL,
    `foo_id` INT(11) NOT NULL,
    `b` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_BAR_FOO` (`foo_id`),
    CONSTRAINT `FK_BAR_FOO` FOREIGN KEY (`foo_id`) REFERENCES `FOO` (`id`) ON DELETE CASCADE
)
ENGINE=InnoDB
;

复合键示例：db-fiddle

ALTER TABLE `BAR`
ADD UNIQUE INDEX `foo_id_b` (`foo_id`, `b`);

SHOW CREATE TABLE `BAR`;

结果

foo_id索引由于冗余而被删除。
由于foo_id是组合键中的第一列，因此删除了隐式 foo_id索引。FK_BAR_FOO

CREATE TABLE `BAR` (
    `id` INT(11) NOT NULL,
    `foo_id` INT(11) NOT NULL,
    `b` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `foo_id_b` (`foo_id`, `b`),
    CONSTRAINT `FK_BAR_FOO` FOREIGN KEY (`foo_id`) REFERENCES `FOO` (`id`) ON DELETE CASCADE
)
ENGINE=InnoDB
;

显式键示例：db-fiddle

如果表是使用满足外键的显式索引创建的，MySQL 会忽略冗余索引CONSTRAINT。

DROP TABLE IF EXISTS `BAR`;
DROP TABLE IF EXISTS `FOO`;

CREATE TABLE `FOO` (
    `id` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB
;

CREATE TABLE `BAR` (
    `id` INT(11) NOT NULL,
    `foo_id` INT(11) NOT NULL,
    `b` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_BAR_FOO` (`foo_id`),
    CONSTRAINT `FK_BAR_FOO` FOREIGN KEY (`foo_id`) REFERENCES `FOO` (`id`) ON DELETE CASCADE
)
ENGINE=InnoDB
;

ALTER TABLE `BAR`
ADD UNIQUE INDEX `foo_id_b` (`foo_id`, `b`);

SHOW CREATE TABLE `BAR`;

结果

创建复合键索引 forfoo_id, b和 index on foo_id。

CREATE TABLE `BAR` (
    `id` INT(11) NOT NULL,
    `foo_id` INT(11) NOT NULL,
    `b` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `foo_id_b` (`foo_id`, `b`),
    INDEX `FK_BAR_FOO` (`foo_id`),
    CONSTRAINT `FK_BAR_FOO` FOREIGN KEY (`foo_id`) REFERENCES `FOO` (`id`) ON DELETE CASCADE
)
ENGINE=InnoDB
;

解决方案

在您的脚本中，在尝试删除/添加索引之前验证索引（不存在）。

IF NOT EXISTS(
    SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS  
    WHERE TABLE_NAME = 'cfgAttributeInstances'
    AND INDEX_NAME = 'new_nodeComponentID' 
    AND INDEX_SCHEMA = 'DbName') 
THEN
    ALTER TABLE `cfgAttributeInstances`
    ADD UNIQUE INDEX `new_nodeComponentID` (`nodeComponentID`,`attributeID`);
END IF;

IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS  
    WHERE TABLE_NAME = 'cfgAttributeInstances'
    AND INDEX_NAME = 'nodeComponentID' 
    AND INDEX_SCHEMA = 'DbName') 
THEN
   ALTER TABLE `cfgAttributeInstances` 
   DROP INDEX `nodeComponentID`;
END IF;

推荐阅读