一次非常有意思的 SQL 优化经历

create table Course(

c_id int PRIMARY KEY,

name varchar(10)

)

create table Student(

id int PRIMARY KEY,name varchar(10)
)

CREATE table SC(
    sc_id int PRIMARY KEY,
    s_id int,
    c_id int,
    score int )

select s. from Student s  where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

EXPLAIN select s. from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

CREATE index sc_c_id_index on SC(c_id);

CREATE index sc_score_index on SC(score);

SELECT    `YSB`.`s`.`s_id` AS `s_id`, 
   `YSB`.`s`.`name` AS `name`FROM    `YSB`.`Student` `s` WHERE     < in_optimizer > (
        `YSB`.`s`.`s_id` ,< EXISTS > (
            SELECT                 1             FROM                 `YSB`.`SC` `sc`
            WHERE                (
                    (`YSB`.`sc`.`c_id` = 0)
                    AND (`YSB`.`sc`.`score` = 100)
                    AND (
                        < CACHE > (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`
                    )
                )
        )
    )

select s_id from SC sc where sc.c_id = 0 and sc.score = 100

select s. from Student s where s.s_id in(7,29,5000)

SELECT s. from 

Student s INNER JOIN SC sc on sc.s_id = s.s_id where sc.c_id=0 and sc.score=100

SELECT     `YSB`.`s`.`s_id` AS `s_id`,
    `YSB`.`s`.`name` AS `name`FROM     `YSB`.`Student` `s`JOIN `YSB`.`SC` `sc` WHERE     (
        (
            `YSB`.`sc`.`s_id` = `YSB`.`s`.`s_id`
        )
        AND (`YSB`.`sc`.`score` = 100)
        AND (`YSB`.`sc`.`c_id` = 0)
    )