數據庫用的是 MySQL 5.6,先交代一下表結構。
文/風過無痕-唐
課程表 Course:
create table Course(
c_id int PRIMARY KEY,
name varchar(10)
)
數據量 100 條。
學生表 Student:
create table Student(
id int PRIMARY KEY,name varchar(10)
)
數據量 7 萬條。
學生成績表 SC:
CREATE table SC(
sc_id int PRIMARY KEY,
s_id int,
c_id int,
score int
)
數據量 70 萬條。
查詢目標:找出語文考了 100 分的考生。
最初的查詢語句:
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 )
執行耗時:30248.271s。
當時就懵了——怎麼能慢到這個程度?先看執行計劃:
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 )

問題一眼可見:索引用不上,type 全是 ALL,走的都是全表掃描。既然如此,第一反應就是建索引,字段自然選 where 條件裡的那幾個。
先給 SC 表的 c_id 和 score 各建一個索引:
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再跑一次同樣的語句,耗時 1.054s。
快了兩三萬倍,效果立竿見影。可見索引對查詢效率的影響有多大——平時最容易忘的偏偏就是它。數據量小的時候毫無感覺,一旦上量,這種優化帶來的暢快實在明顯。
但 1 秒仍然偏長,還能再擠一擠嗎?回頭細看執行計劃:

再看優化器改写後的 SQL:
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`
)