我把SQL執行效率提高了10000000倍！[ 『INNER JOIN+索引』 取代 IN 語法 ]

資料來源:https://mp.weixin.qq.com/s?__biz=MzI4NDY5Mjc1Mg==&mid=2247488333&idx=1&sn=c5257e42d95bf442e0affeb65184d85d&chksm=ebf6cb32dc81422446a90e1dfe5532b05fdbf644ee1648e4adb98ba8ddf63063d985149e2ae9&scene=0&xtrack=1&key=41cacdd5e50db66b8a0890912e816dd3e4f84dae9f4819fd03ad643ac92ddc3e0e529ff08c1eb9b10551bd2798a7c5c64eecd994e928e516b448fb4c1707d4479e80b1fe4e8e172073db5952dedb5742&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10&version=62060833&lang=zh_TW&pass_ticket=Au7k0APLoI8cgzR94VtN9Dd9%2Ffn3p5K6MsO%2BtZuPMGH2RBBRay1R3Iq0XxsdxWRA

課程表：[數據100條]

create  table  Course( 

c_id  int  PRIMARY  KEY , 

name  varchar ( 10 ) 

)

學生表:[數據70000條]

create  table  Student( 

id  int  PRIMARY  KEY , 

name  varchar ( 10 ) 

)

學生成績表SC[數據70w條]

CREATE  table  SC( 

    sc_id  int  PRIMARY  KEY , 

    s_id  int , 

    c_id  int , 

    score  int

 )

 
查詢目的：查找語文考100分的考生 [執行時間：30248.271s]
 

 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 )

 
 
先給sc表的c_id和score建個索引

CREATE index sc_c_id_index on SC(c_id);

CREATE index sc_score_index on SC(score);

再次執行上述查詢語句，時間為: 1.054s

優化語法:

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