作者:风过无痕链接:https://www.cnblogs.com/tangyanbo/p/4462734.html场景用的数据库是mysql5.6,下面简单的介绍下场景课程表create table Course(c_id int PRIMARY KEY,name varchar(10))数据100条学生表:create table Student(id int PRIMARY KEY,name varchar(10))数据70000条学生成绩表SCCREATE table SC(sc_id int PRIMARY KEY,s_id int,c_id int,score int)数据70w条查询目的:查找语文考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为什么这么慢?先来查看下查询计划:EXPLAINselect 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条件的字段
………………………………