InnoDB的唯一键和索引
问题描述:
我用下面的创建语句:InnoDB的唯一键和索引
CREATE TABLE `subscr` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`media_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY (`media_id`, `user_id`),
CONSTRAINT FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE CASCADE,
CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB
为什么InnoDB的只是把一个指数USER_ID?我认为它需要所有外键引用的索引。
CREATE TABLE `subscr` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`media_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `media_id` (`media_id`,`user_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `subscriptions_media_ibfk_1` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE CASCADE,
CONSTRAINT `subscriptions_media_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB
答
media_id也具有索引,并且它是唯一的。这对于MySQL来说已经足够了。只为user_id创建了一个单独的索引,因为唯一索引中的user_id是第二个索引,因此对于外键是没用的。