使用阿里云DMS的数据库实验室
云飞扬的个人博客
CREATE TABLE `courses` (
`cid` int(20) NOT NULL COMMENT '课程ID',
`name` varchar(32) NOT NULL COMMENT '课程名',
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
随机生成20条数据
CREATE TABLE `students` (
`sid` int(11) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
随机生成5W条数据
CREATE TABLE `sc` (
`sc_id` int(11) NOT NULL,
`sid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`sc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
生成数据100w条
1号课程考100分的学生
SELECT s.* FROM `students` s WHERE s.`sid` IN
(SELECT `sid` from `sc` sc WHERE sc.`cid` = 1 and sc.`score` = 100);
220ms
19ms
SELECT s.*
FROM `students` s
INNER JOIN `sc` sc on sc.`sid`= s.`sid`
where sc.`cid`= 1
and sc.`score`= 100;
52ms
6ms
ALTER TABLE `yfytest`.`sc` ADD INDEX `idx_score_cid_sid` (`score`, `cid`, `sid`)
2ms