話不多說(shuō)赫段，直接上手呀打！
場(chǎng)景
用的數(shù)據(jù)庫(kù)是mysql5.6，下面簡(jiǎn)單的介紹下場(chǎng)景
課程表
create table Course(
c_id int PRIMARY KEY,
name varchar(10)
)
數(shù)據(jù)100條
學(xué)生表:
create table Student(
id int PRIMARY KEY,
name varchar(10)
)
數(shù)據(jù)70000條
學(xué)生成績(jī)表SC
CREATE table SC(
sc_id int PRIMARY KEY,
s_id int,
c_id int,
score int
)
數(shù)據(jù)70w條
查詢目的：
查找語(yǔ)文考100分的考生
查詢語(yǔ)句：
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 )
執(zhí)行時(shí)間：30248.271s
為什么這么慢糯笙？先來(lái)查看下查詢計(jì)劃：
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 )
發(fā)現(xiàn)沒(méi)有用到索引贬丛，type全是ALL，那么首先想到的就是建立一個(gè)索引给涕，建立索引的字段當(dāng)然是在where條件的字段豺憔。
先給sc表的c_id和score建個(gè)索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再次執(zhí)行上述查詢語(yǔ)句，時(shí)間為: 1.054s
快了3w多倍够庙，大大縮短了查詢時(shí)間恭应，看來(lái)索引能極大程度的提高查詢效率，看來(lái)建索引很有必要耘眨，很多時(shí)候都忘記建索引了昼榛，數(shù)據(jù)量小的的時(shí)候壓根沒(méi)感覺(jué)，這優(yōu)化感覺(jué)挺爽剔难。
但是1s的時(shí)間還是太長(zhǎng)了胆屿，還能進(jìn)行優(yōu)化嗎，仔細(xì)看執(zhí)行計(jì)劃：
查看優(yōu)化后的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`
)
)
)
)
補(bǔ)充：這里有網(wǎng)友問(wèn)怎么查看優(yōu)化后的語(yǔ)句
方法如下：
在命令窗口執(zhí)行
有type=all
按照筆者之前的想法偶宫，該sql的執(zhí)行的順序應(yīng)該是先執(zhí)行子查詢
select s_id from SC sc where sc.c_id = 0 and sc.score = 100
耗時(shí)：0.001s
得到如下結(jié)果：
然后再執(zhí)行
select s.* from Student s where s.s_id in(7,29,5000)
耗時(shí)：0.001s
這樣就是相當(dāng)快了啊非迹，Mysql竟然不是先執(zhí)行里層的查詢，而是將sql優(yōu)化成了exists子句读宙，并出現(xiàn)了EPENDENT SUBQUERY彻秆，
mysql是先執(zhí)行外層查詢，再執(zhí)行里層的查詢结闸，這樣就要循環(huán)70007*11=770077次唇兑。
那么改用連接查詢呢？
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
這里為了重新分析連接查詢的情況桦锄，先暫時(shí)刪除索引sc_c_id_index扎附，sc_score_index
執(zhí)行時(shí)間是：0.057s
效率有所提高，看看執(zhí)行計(jì)劃：
這里有連表的情況出現(xiàn)结耀，我猜想是不是要給sc表的s_id建立個(gè)索引
CREATE index sc_s_id_index on SC(s_id);
show index from SC
在執(zhí)行連接查詢
時(shí)間: 1.076s留夜，竟然時(shí)間還變長(zhǎng)了，什么原因图甜？查看執(zhí)行計(jì)劃：
優(yōu)化后的查詢語(yǔ)句為：
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)
)
貌似是先做的連接查詢碍粥，再執(zhí)行的where過(guò)濾
回到前面的執(zhí)行計(jì)劃：
這里是先做的where過(guò)濾，再做連表黑毅，執(zhí)行計(jì)劃還不是固定的嚼摩，那么我們先看下標(biāo)準(zhǔn)的sql執(zhí)行順序：
正常情況下是先join再where過(guò)濾，但是我們這里的情況，如果先join枕面，將會(huì)有70w條數(shù)據(jù)發(fā)送join做操愿卒，因此先執(zhí)行where
過(guò)濾是明智方案，現(xiàn)在為了排除mysql的查詢優(yōu)化潮秘，我自己寫(xiě)一條優(yōu)化后的sql
SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id
即先執(zhí)行sc表的過(guò)濾琼开，再進(jìn)行表連接，執(zhí)行時(shí)間為：0.054s
和之前沒(méi)有建s_id索引的時(shí)間差不多
查看執(zhí)行計(jì)劃：
先提取sc再連表枕荞，這樣效率就高多了柜候，現(xiàn)在的問(wèn)題是提取sc的時(shí)候出現(xiàn)了掃描表，那么現(xiàn)在可以明確需要建立相關(guān)索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再執(zhí)行查詢：
SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id
執(zhí)行時(shí)間為：0.001s买猖，這個(gè)時(shí)間相當(dāng)靠譜改橘，快了50倍
執(zhí)行計(jì)劃：
我們會(huì)看到滋尉，先提取sc玉控，再連表，都用到了索引狮惜。
那么再來(lái)執(zhí)行下sql
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
執(zhí)行時(shí)間0.001s
執(zhí)行計(jì)劃：
這里是mysql進(jìn)行了查詢語(yǔ)句優(yōu)化高诺，先執(zhí)行了where過(guò)濾，再執(zhí)行連接操作碾篡，且都用到了索引虱而。
總結(jié)：
1.mysql嵌套子查詢效率確實(shí)比較低
2.可以將其優(yōu)化成連接查詢
3.建立合適的索引
4.學(xué)會(huì)分析sql執(zhí)行計(jì)劃，mysql會(huì)對(duì)sql進(jìn)行優(yōu)化开泽，所以分析執(zhí)行計(jì)劃很重要
本人免費(fèi)整理了Java高級(jí)資料牡拇，涵蓋了Java、Redis穆律、MongoDB惠呼、MySQL、Zookeeper峦耘、Spring Cloud剔蹋、Dubbo高并發(fā)分布式等教程，一共30G辅髓，需要自己領(lǐng)取泣崩。
傳送門(mén)：https://mp.weixin.qq.com/s/igMojff-bbmQ6irCGO3mqA
