sql优化案例

说明一下sql的优化过程

场景介绍

数据库 mysql 5.6
场景说明

课程表:

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

课程表插入数据，20条:

insert into `forapp`.`course` (name) values ('语文'),('数学'),('英语'), ('物理'), ('化学'), ('生物'), ('机械'), ('电子技术'), ('芯片工艺'),('芯片装备制造'),('园艺'), ('哲学'), ('美文赏析'), ('莎士比亚戏剧'), ('量子力学'), ('微积分'), ('高等数学'), ('线性代数'), ('人工智能'), ('统计学');

学生表：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=69360 DEFAULT CHARSET=utf8;

学生表数据，7.1万条:

DROP PROCEDURE if EXISTS student_add;
create PROCEDURE student_add(num int)

BEGIN
    DECLARE i int DEFAULT 0;
    START TRANSACTION;
    while i < num DO

    INSERT INTO `forapp`.`student`(name) VALUES (CONCAT('ycy_',i));
    SET i = i + 1;
    end WHILE;
    COMMIT;
END;

CALL student_add(71000);

学生成绩表sc

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sc_id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(11) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`sc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

成绩表数据，78万条:
学生ID 分布: 1-71000 之间 (CEIL(RAND()71000)
课程ID分布：1-20之间 CEIL(RAND()20)
考生分数分布: 60-150 之间 FLOOR(60 + RAND()*91)

drop PROCEDURE IF EXISTS sc_add;
CREATE PROCEDURE sc_add(num int)

BEGIN
    DECLARE i int DEFAULT 0;
    START TRANSACTION;
    WHILE i < num DO
    
    INSERT INTO `forapp`.`sc` (s_id, c_id, score) VALUES
    (CEIL(RAND()*71000), CEIL(RAND()*20), FLOOR(60 + RAND()*91));
    set i = i+1;
    END WHILE;
    COMMIT;
END;
CALL sc_add(780000);

查询目的：查找语文考100分的学生。
查询语句

select s.id, s.name from student as s where s.id in (select s_id from sc where c_id = 1 and score = 100)

执行时间：0.85s ~ 1.2s之间，属于比较慢的sql了。

我们来查看下查询计划：

EXPLAIN
select s.id, s.name from student as s where s.id in (select s_id from sc where c_id = 1 and score = 100)

结果如下

explain分析计划.PNG

type类型介绍

现在我们来说明一下结果里的type类型。
type类型取值如下（自左向右，sql性能从最差到最好）:

|All | index | range | ref | eq_ref | const, system | null |

All（全表扫描）
MYSQL从头到尾扫描整张表查找行。
index（索引）
根据索引来读取数据，如果索引已经包含了查询数据，只需要扫描索引树，否则执行全表扫描和All类似。
range（范围）
以范围的形式扫描索引，如where条件中使用>,<,>=,<=,in。
ref（引用）
非唯一索引性访问。
eq_ref（等值引用）
使用有唯一性索引查找（主键或唯一性索引）。
const（常量连接）
在整个查询过程中，这个表最多只会有一条匹配的行，比如主键id=1就肯定只有一行。只需要读取一次表数据便能取得所有结果，且表数据在分解执行计划时读取。

Extra 列介绍

Extra 名如其意，表示附加信息。效率由高到低的取值为：

| Using index | Using where | Using filesort | Using temporary|

Using index
表明使用索引，如果只有 Using index ，说明他没有查询到数据表，只用索引表就完成了查询，这个叫覆盖索引。如果同时出现 Using where，代表使用索引来查询记录，也是可以用到索引的，但是需要查询数据表。

Using where
表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，就会出现 Using where。如果 type 是 All 或者 index，而没有出现该信息，则有可能执行了错误的查询：即返回了所有数据。

**Using filesort **
filesort 是MYSQL所实现的一种排序策略，通常在使用排序语句ORDER BY的时候，会出现该信息。

Using temporary
表示为了得到结果，使用了临时表，这通常是出现在多表联合查询，结果排序的场合。

如果EXPLAIN出现后面两个信息（Using filesort，Using temporary），而rows又比较大，通常意味着你需要调整查询语句，或者需要添加索引，总之要尽量消除这两个消息。

优化分析

接着前面的“场景介绍”。
看EXPLAIN的结果，我们发现对sc表进行查询时type类型为ALL，首先想到创建索引。

给sc表c_id字段和score字段创建一个联合索引。

create index sc_cid_score_index on sc(c_id, score)

优化后执行时间为0.062s - 0.102s。
还能继续优化吗？因为使用了子查询，还有进一步优化的空间。
下面是创建联合索引后的执行计划：

c_id和score添加联合索引后执行计划.PNG

我们通过 extended 查看查询计划内部过程(在命令行执行)：

EXPLAIN EXTENDED
select s.id, s.name from student as s where s.id in (select s_id from sc where c_id = 1 and score = 100);

使用show warnings 查看MYSQL是如何优化的:

sql优化.PNG

MYSQL建议使用join。

按照直观的理解，mysql应该先执行子查询：

select s_id from sc where c_id = 1 and score = 100

耗时0.01s速度很快
然后再执行外层查询:

 select s.id, s.name from student as s where s.id in (39129,13767,62895,15638);

0.001s

如果按这种顺序执行速度就很快了。而Mysql是先执行外层查询，再执行内层查询，然后将sql优化成了exists子句，这样就要循环 71000 * 451 次。

那么改用连接查询呢？

SELECT distinct s.* FROM student s 
INNER JOIN sc on sc.s_id = s.id
where sc.c_id = 1 and sc.score = 100;

这时我们发现已经又快了，0.01s。通过explain执行查询计划，发现type类型是 ref和eq_ref。
我们为了分析连接查询的情况，将上一步创建的索引删除。

 drop index sc_cid_score_index on sc;

执行连接语句，结果耗时1.28s。

Join的Explain分析.PNG

猜想是不是在s_id建立个索引。

create index sc_sid_index on sc(s_id);
show index from sc;

耗时3.5s，时间竟然长了！是什么原因？查看查询计划。

貌似先做的连接查询，再进行的where条件过滤。

Note  | 1003 | /* select#1 */ select `forapp`.`s`.`id` AS `id`,`forapp`.`s`.`name` AS `name` from `forapp`.`student` `s` join `forapp`.`sc` where ((`forapp`.`sc`.`s_id` = `forapp`.`s`.`id`) and (`forapp`.`sc`.`score` = 100) and (`forapp`.`sc`.`c_id` = 1))

回到前面的执行计划

join查询计划2.PNG

这里是先做的where条过滤，再做连表，执行计划还不是固定的，那么我们先看下标准的sql执行顺序：

(8) SELECT (9) DISTINCT<select list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2)                      ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY<order_by_list>
(11) LIMIT <limit_number>

正常情况下是先join然后再进行where过滤，但是我们这里的情况，如果先join，将会有78w条数据发送join操作，因此先执行where过滤是明智的选择。

现在为了排除mysql的查询优化，我自己写一条优化后的sql

select distinct  s.* from (select * from sc where sc.c_id=1 and sc.score=100) t inner join student s on t.s_id = s.id;

1.19s，和没有建s_id索引的时间差不多，查看执行计划：

join分析3.PNG

先提取sc再连表，这样的效率高多了，现在的问题是提取sc的时候出现了全表扫描。那么现在可以明确需要建立的相关索引。

create index sc_cid_score_index on sc(c_id, score);

现在就相当快了，0.001s。快了1000多倍！

join分析4.PNG

我们看到，先提取sc，再连表，都用到了索引。

那么再来执行下sql

select distinct s.* from student s 
inner join sc on sc.s_id = s.id
where sc.c_id = 1 and sc.score=100;

耗时 0.001s。
执行计划:

join分析6.PNG

这是mysql进行了查询语句的优化，先执行了where过滤，再执行连接操作，再执行了连接操作，且都用到了索引。

sc表数据更多的情况

我们来造一些数据，使sc表的数据增加到300万，分数更离散。
考生分数分布: 1-200 之间 FLOOR(60 + RAND()*91)

drop PROCEDURE IF EXISTS sc_add;
CREATE PROCEDURE sc_add(num int)

BEGIN
    DECLARE i int DEFAULT 0;
    START TRANSACTION;
    WHILE i < num DO
    
    INSERT INTO `forapp`.`sc` (s_id, c_id, score) VALUES
    (CEIL(RAND()*71000), CEIL(RAND()*20), 
 CEIL(RAND()*200));
    set i = i+1;
    END WHILE;
    COMMIT;
END;

循环调用存储过程，每次插入1万表数据

add_sc(10000)

或者使用java的jdbc造数据，速度更快。
执行sql：

select DISTINCT s.* from student s  
inner join sc on sc.s_id = s.id 
where sc.c_id = 1 and sc.score=100;

耗时 0.03s，有点慢。
查看执行计划:

join分析7.PNG

这里我们看到从sc表中筛选数据时，用到了联合索引 sc_cid_score_index。联合索引的效率要比两个字段分别建索引的效率要高。
根据c_id=1检索的结果是 158565；根据score=100检索的结果是 12269条。如果是两个字段单独建索引，mysql会有一个 intersect操作，即两个索引同时检索的结果再求并集。显然性能会比联合索引低一些。

从另外一个角度看，该表的数据是317w，实际生产中可能会更多，就索引存储而言都是不小的空间开销。随着数据量 的增加，索引就不能全部加载到内存，而是要从磁盘中去读取，这样索引的个数越多，读磁盘的开销就越大。

这里我们还看到使用了临时表--Using temporary。这个是耗时的操作，mysql在使用distinct，ordre by， group by 数据量大的情况下会产生 Using temporary; Using filesort 效果。这个暂时想不到怎么优化，希望了解的朋友能留言告知一下哈。

总结：

1. mysql嵌套子查询的效率确实比较低，可以将子查询优化成连接查询；
2. 连接表时，可以先用where条件对表进行过滤，然后做表连接（虽然mysql会对连接语句进行优化）；
3. 建立合适的索引，必要时建立多列联合索引；
4. 使用distinct，group by，order by 数据量大的情况下容易产生 sing temporary; Using filesort，这是需要进行优化的；
5. 学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要。
6. mysql实际执行查询操作时，不是按照直观的从头到尾来执行的。

索引优化

优先使用联合索引，使用联合索引效率会更高，尤其是在数据量较大，单个列区分度不高的情况下。

最左前缀

多列索引有最左前缀特性，如在a，b，c字段上创建了联合索引：

create index test_a_b_c_index on test(a,b,c);

那么where条件，where a=1 和 where a=1 and b=1 和 where a=1 and b=1 and c=1都能有效利用索引。

索引覆盖

就是查询的列都建立了索引，这样在获取结果集的时候不用再去磁盘获取其它列的数据，直接返回索引数据即可。

排序

在排序字段上建立索引会大幅度提高sql效率，因为消除了Using temporary; Using filesort 现象。

常用的sql调优经验

1. 列类型尽量定义成数值类型，且长度尽可能短，如主键和外键，类型字段等；
2. 建立必要的单列索引；
3. 根据需要建立多列联合索引；
4. 索引不是越多越好不能滥用；单张表索引数要控制在5个以内，最好不超过3个；
5. 根据业务场景建立覆盖索引只查询业务所需字段，这样会极大提高查询效率；
6. where 条件字段上需要建立索引；
7. 排序字段需要建立索引；
8. 分组字段上需要建立索引；
9. where 条件上不要使用运算函数，以免索引失效。