1、准备
说明：数据库是基于mysql5.5，以下操作都是基于此版本。
1.1、表格的创建
课程表
CREATE TABLE `demo_course` (
`id` INT(11) NOT NULL COMMENT '唯一标示',
`name` VARCHAR(30) NULL DEFAULT NULL COMMENT '名称',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
学生表
CREATE TABLE `demo_student` (
`id` INT(11) NOT NULL COMMENT '唯一标示',
`name` VARCHAR(30) NULL DEFAULT NULL COMMENT '名称',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
学生成绩表
CREATE TABLE `demo_score` (
`id` INT(11) NOT NULL COMMENT '唯一标识',
`cour_id` INT(11) NULL DEFAULT NULL COMMENT '课程id',
`stud_id` INT(11) NULL DEFAULT NULL COMMENT '学生id',
`score` FLOAT NULL DEFAULT NULL COMMENT '得分',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
1.2、数据初始化
初始化课程表数据（100）
drop procedure if exists insert_course; /* 如果存在存储过程demo_course，则删除 */
delimiter ;;
create procedure insert_course() /* 创建存储过程demo_course */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=100)do /* 对满足i<=100的值进行while循环 */
insert into demo_course(id,name) values(i, concat('课程',i)); /* 写入表demo_course中id、name两个字段，值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
end;;
delimiter ; /* 创建批量写入1000条数据到表t1的存储过程demo_course */
call insert_course(); /* 运行存储过程demo_course */
初始化学生表数据（7000）
drop procedure if exists insert_student; /* 如果存在存储过程insert_student，则删除 */
delimiter ;;
create procedure insert_student() /* 创建存储过程insert_student */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=7000)do /* 对满足i<=7000的值进行while循环 */
insert into demo_student(id,name) values(i, concat('学生',i)); /* 写入表demo_student中id、name两个字段，值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
end;;
delimiter ; /* 创建批量写入1000条数据到表t1的存储过程insert_student */
call insert_student(); /* 运行存储过程insert_student */
初始化成绩表（700000）
private String url = "jdbc:mysql://localhost:3306/core?rewriteBatchedStatements=true";
private String user = "root";
private String password = "root";
@Test
public void Test() {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO demo_score(id,cour_id,stud_id,score) VALUES(?,?,?,?)";
pstm = conn.prepareStatement(sql);
conn.setAutoCommit(false);
Long startTime = System.currentTimeMillis();
Random rand = new Random();
int a=0;
int totalNum=1;
for (int i = 1; i <= 100; i++) {
pstm.setInt(2, i);
for (int j = 1; j <= 7000; j++) {
pstm.setInt(1, totalNum);
pstm.setInt(3, j);
totalNum++;
a = rand.nextInt(100);
pstm.setString(4, a+"");
pstm.addBatch();
}
}
pstm.executeBatch();
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println("OK,用时：" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
if (pstm != null) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
2、查询
2.1、普通查询
查询：课程1 成绩为90的所有学生
select *
from demo_student
where id in
(
select stud_id
from demo_score
where score=90 and cour_id=1
);
执行时间：29246.259 sec，
2.2、优化索引
当发现慢的sql的时候，第一步要查看下mysql的执行计划，用explain
explain
select *
from demo_student
where id in
(
select stud_id
from demo_score
where score=90 and cour_id=1
);
查看结果：
发现没有索引，type是ALL，首先我们对表建议索引，建立索引的字段时where条件的字段
ALTER TABLE `demo_score`
ADD INDEX `cour_id` (`cour_id`);
ALTER TABLE `demo_score`
ADD INDEX `score` (`score`);
再次执行上面的查询sql，发现执行时间：18.643 sec，查询时间已经缩短了很多，索引会确实是很大程度的提高查询效率。但是18秒依旧是时间太长。
猜想：sql的执行顺序是不是应该先执行子查询：
select stud_id
from demo_score
where score=90 and cour_id=1;
执行时间：0.016 sec
返回的结果：一系列的id
然后把结果返回给主查询，执行主查询：
select *
from demo_student
where id in ('90','228','250','370','394','531','544','686','692','727','819','873','974','1043','1183','1185','1235','1473','1527','1746','1832','2062','2224','2614','2676','2737','2751','2824','2850','2972','3112','3154','3279','3340','3473','3586','3618','3655','3684','3731','3743','3860','3929','3963','4055','4085','4188','4478','4597','4659','4682','4744','4999','5056','5075','5257','5402','5418','5494','5571','5801','5933','5938','6002','6327','6462','6480','6578','6584','6596','6641','6816','6901');
执行时间：0.000 sec
发现如果是先子查询然后再子查询，时间应该远远小于18s才对
2.3、优化sql
我们再看下执行计划
显然type还有一个是ALL，id为主键，但是并没有使用索引。那么这个时候就要查看MYSQL优化后的查询语句
现有explain extended，然后再执行show warnings;
explain extended
select *
from demo_student
where id in
(
select stud_id
from demo_score
where score=90 and cour_id=1
);
show warnings;
仔细查看Message里面的内容，如下：
SELECT
`core`.`demo_student`.`id` AS `id`,
`core`.`demo_student`.`name` AS `name`
FROM
`core`.`demo_student`
WHERE
< in_optimizer > (
`core`.`demo_student`.`id` ,< EXISTS > (
SELECT
1
FROM
`core`.`demo_score`
WHERE
(
(
`core`.`demo_score`.`cour_id` = 1
)
AND (
`core`.`demo_score`.`score` = 90
)
AND (
< CACHE > (`core`.`demo_student`.`id`) = `core`.`demo_score`.`stud_id`
)
)
)
)
发现MYSQL将查询语句优化成了exists子语句，并出现了从属子查询DEPENDENT SUBQUERY，mysql是先执行了外查询，在执行了子查询，这样就循环的次数为7000*73=511000次
改成连接查询
select *
from demo_student s
inner join
demo_score sc on s.id=sc.stud_id
where sc.score=90 and sc.cour_id=1;
执行时间：0.000 sec
发现已经非常快了。我们再看一下执行计划
发现都用到了索引，在看下mysql优化后的sql语句：
SELECT
`core`.`s`.`id` AS `id`,
`core`.`s`.`name` AS `name`,
`core`.`sc`.`id` AS `id`,
`core`.`sc`.`cour_id` AS `cour_id`,
`core`.`sc`.`stud_id` AS `stud_id`,
`core`.`sc`.`score` AS `score`
FROM
`core`.`demo_student` `s`
JOIN `core`.`demo_score` `sc`
WHERE
(
(
`core`.`s`.`id` = `core`.`sc`.`stud_id`
)
AND (`core`.`sc`.`cour_id` = 1)
AND (`core`.`sc`.`score` = 90)
)
附录1：mysql执行计划介绍
使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。
explain执行计划包含的信息
其中重要的字段为：id，type、key、rows、Extra
id
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序 三种情况： 1、id相同：执行顺序由上至下
EXPLAIN
SELECT *
FROM t1,t2,t3
WHERE t1.id=t2.id AND t3.id=t1.id AND t1.name='t1-1'
2、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
EXPLAIN
SELECT *
FROM t1
where id =(select id from t2 where id=(select t3.id from t3 where t3.name='t3-1'));
3、id有相同有不同（两种情况同时存在）：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
EXPLAIN
SELECT t1.*
FROM (select t3.id from t3 where t3.name='t3-1') t,t1
where t1.id =t.id;
select_type
查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询
1、SIMPLE：简单的select查询，查询中不包含子查询或者union 2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary 3、SUBQUERY：在select 或 where列表中包含了子查询 4、DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里 5、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived 6、UNION RESULT：从union表获取结果的select
7、DEPENDENT SUBQUERY：子查询中的SELECT， 取决于外面的查询。常见于in
EXPLAIN
SELECT t1.*
FROM t1
where t1.id in (
select t3.id from t3 where t3.name='t3-1'
);
它意味着两步：
1、Mysql根据select * from t1；得到一个数据集temp1，其数据量是上图中的rows（1311）
2、上面的数据集temp1的每一条记录，都将与子查询SQL组成新的查询语句select * from t1 where t1.id in ('1',..)
等于说子查询执行了1311次。。。即使这两部都用到索引，也是会慢
DEPENDENT SUBQUERY的优化策略通常是用 与临时表联表查询，
EXPLAIN
SELECT t1.*
FROM t1, (
select t3.id from t3 where t3.name='t3-1'
) t
where t1.id =t.id;
type
访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index> ALL
一般来说，好的sql查询至少达到range级别，最好能达到ref
1、system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
2、const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const
EXPLAIN
SELECT t1.*
FROM t1
where t1.id =1
3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
EXPLAIN
SELECT *
FROM t2,t1
where t1.id = t2.id;
注意：ALL全表扫描的表记录最少的表如t2表
4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体 .name已添加索引
EXPLAIN
SELECT *
FROM t3
where t3.name='t3-1';
5、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引
EXPLAIN
SELECT *
FROM t3
where t3.id between 1 and 10;
6、index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
EXPLAIN
SELECT id
FROM t1
7、ALL：Full Table Scan，遍历全表以找到匹配的行
EXPLAIN
SELECT *
FROM t1;
possible_keys
查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用
key
实际使用的索引，如果为NULL，则没有使用索引。 查询中如果使用了覆盖索引，则该索引仅出现在key列表中
show index from t3
EXPLAIN
SELECT id,name
FROM t3
key_len
表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的
ref
显示索引的那一列被使用了，如果可能，是一个常量const。
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra
不适合在其他字段中显示，但是十分重要的额外信息
1、Using filesort ： mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”
很多场景都是索引是一个字段，order by 排序的字段与索引字段不一致，导致的Using fileSort;
#此时可以给排序字段和where条件字段，添加为组合索引，同时保证索引查询的数据不超过总量的15%，避免fileSort
show index from t1;
explain
select *
from t1
where t1.name='t1-1'
order by t1.address;
explain
select *
from t1
where t1.name='t1-1'
order by t1.email,t1.address;
2、Using temporary： 使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by
3、Using index： 表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高 如果同时出现Using where，表明索引被用来执行索引键值的查找（参考上图） 如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作
覆盖索引（Covering Index）：也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。 注意： a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select * b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能
4、Using where ： 使用了where过滤
5、Using join buffer ： 使用了链接缓存
6、Impossible WHERE： where子句的值总是false，不能用来获取任何元祖
7、select tables optimized away： 在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化
8、distinct： 优化distinct操作，在找到第一个匹配的元祖后即停止找同样值得动作
综合案例
explain
select d1.name,
(select id from t3) d2
from (select id,name from t1 where address=1) d1
union
select id ,name from t2;
执行顺序 1（id = 4）、【select id, name from t2】：select_type 为union，说明id=4的select是union里面的第二个select。
2（id = 3）、【select id, name from t1 where address = ‘1’】：因为是在from语句中包含的子查询所以被标记为DERIVED（衍生），where address = ‘1’ 通过复合索引idx_name_email_address就能检索到，所以type为index。
3（id = 2）、【select id from t3】：因为是在select中包含的子查询所以被标记为SUBQUERY。
4（id = 1）、【select d1.name, … d2 from … d1】：select_type为PRIMARY表示该查询为最外层查询，table列被标记为 “derived3”表示查询结果来自于一个衍生表（id = 3 的select结果）。
5（id = NULL）、【 … union … 】：代表从union的临时表中读取行的阶段，table列的 “union 1, 4”表示用id=1 和 id=4 的select结果进行union操作。
附录2：Mysql的执行顺序
我们看一下mysql的执行顺序：
发现mysql执行sql的顺序从 From 开始，以下是执行的顺序流程
1、FROM table1 inner join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1
2、JOIN table2 所以先是确定表，再确定关联条件
3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2
4、WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3
5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4
6、HAVING 对分组后的记录进行聚合 产生中间表Temp5
7、SELECT 对中间表Temp5进行列筛选，产生中间表 Temp6
8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7
9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8
10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9
我们现在把SQL调整为：
select *
from (select * from demo_score sc where sc.score=90 and sc.cour_id=1)t
inner join
demo_student s on s.id=t.stud_id;
执行时间：0.000 sec
为了验证执行的顺序，我们先把demo_score表的索引去掉再执行；
select *
from demo_student s
inner join
demo_score sc on s.id=sc.stud_id
where sc.score=90 and sc.cour_id=1;
执行时间：0.296 sec
select *
from (select * from demo_score sc where sc.score=90 and sc.cour_id=1)t
inner join
demo_student s on s.id=t.stud_id;
执行时间：0.249 sec
发现时间还是有一点优化
