MySQL不为外键创建索引 [英] MySQL not creating indexes for foreign key

问题描述

我读到InnoDB自动为外键创建索引.

I read that InnoDB automatically creates indexes for Foreign-key.

MySQL Workbench是否会自动为外键创建索引?/a> MySQL是否自动索引外键列? https://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

Does MySQL Workbench automatically create indexes for foreign keys? Does MySQL index foreign key columns automatically? https://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

但是我的某些外键在表中没有索引.检查pharmaceutical_id外键字段.它没有索引.

But some of my foreign keys do not have the index in the table. Check pharmaceutical_id foreign-key field. It does not have an index.

| pharmaceuticals_pharmaceuticalcode | CREATE TABLE `pharmaceuticals_pharmaceuticalcode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code_id` int(11) NOT NULL,
  `pharmaceutical_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pharmaceuticals_pharmaceuticalco_pharmaceutical_id_5ae1e77e_uniq` (`pharmaceutical_id`,`code_id`),
  KEY `pharmaceuticals_phar_code_id_a7de9505_fk_human_api` (`code_id`),
  CONSTRAINT `pharmaceuticals_phar_code_id_a7de9505_fk_human_api` FOREIGN KEY (`code_id`) REFERENCES `human_api_code` (`id`),
  CONSTRAINT `pharmaceuticals_phar_pharmaceutical_id_04c18462_fk_pharmaceu` FOREIGN KEY (`pharmaceutical_id`) REFERENCES `pharmaceuticals_pharmaceutical` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=202770 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

我在pharmaceutical_id和code_id上添加了唯一键约束，这可能导致未为pharmaceutical_id创建单独的索引，因为MySQL以B-Tree方式管理这些索引，唯一键的索引可以是用于它.

I have added unique-together constraint on pharmaceutical_id and code_id which may caused the not creation of separate index for pharmaceutical_id because MySQL manage these index in B-Tree fashion and index of unique-together key can be used for it.

在 https://dev.mysql.com/doc/refman/5.6/zh-CN/create-table-foreign-keys.html

InnoDB允许外键引用任何索引列或一组列.但是，在引用表中，必须有一个索引，其中引用列是相同顺序的第一列.还应考虑InnoDB添加到索引中的隐藏列(请参见第14.6.2.1节聚集索引和二级索引").

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 14.6.2.1, "Clustered and Secondary Indexes").

但是，如果以上几点是正确的，那么为什么在下表模式中有member_id的索引?因为patients_membercard_b5c3e75b索引 patients_membercard_member_id_661ac31abca894ae_uniq

But If the above point is true then why there is an index for member_id in the below table schema? Because patients_membercard_b5c3e75b index is redundant after patients_membercard_member_id_661ac31abca894ae_uniq

| patients_membercard | CREATE TABLE `patients_membercard` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `patients_membercard_member_id_661ac31abca894ae_uniq` (`member_id`,`name`),
  KEY `patients_membercard_b5c3e75b` (`member_id`),
  CONSTRAINT `patients_member_member_id_459e0d6970a32170_fk_patients_member_id` FOREIGN KEY (`member_id`) REFERENCES `patients_member` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1655520 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

PS:在上表中，架构是由Django创建的，尚未在数据库上执行任何手动操作.

PS: Above table, schema is created by Django and no manual operation has been performed on DB.

推荐答案

您的外键确实具有索引.

Your foreign keys do have indexes.

MySQL在很多情况下都将KEY和INDEX当作同义词.

MySQL treats the words KEY and INDEX are synonyms in many contexts.

请参见在MySQL中使用INDEX与KEY有什么区别?

重新更新问题.

我认为Django负责定义额外的KEY patients_membercard_b5c3e75b (member_id).它不会自动创建.也就是说，如果我在没有该KEY定义的情况下测试您的CREATE TABLE语句，则FOREIGN KEY不需要它.外国密钥可以使用唯一密钥的左列member_id.

I think Django is responsible for defining the extra KEY patients_membercard_b5c3e75b (member_id). It is not created automatically. That is, if I test your CREATE TABLE statement without that KEY definition, the FOREIGN KEY doesn't need it. The FOREIGN KEY can use the left column member_id of the UNIQUE KEY.

我猜想创建Django的开发人员无法处理这种情况.他们假定每个FOREIGN KEY都需要自己的索引，并且他们更喜欢确保可以控制索引名称.因此，它们在CREATE TABLE语句中显式生成索引的定义.

I am guessing that the developers who created Django didn't handle this case. They assume that every FOREIGN KEY needs its own index, and they prefer to make sure that they can control the index name. So they generate a definition of an index explicitly in the CREATE TABLE statement.

这篇关于MySQL不为外键创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！