sql优化案例

作者: 横渡 | 来源:发表于2019-05-31 14:48 被阅读0次

说明一下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 效果。这个暂时想不到怎么优化，希望了解的朋友能留言告知一下哈。

总结：

mysql嵌套子查询的效率确实比较低，可以将子查询优化成连接查询；
连接表时，可以先用where条件对表进行过滤，然后做表连接（虽然mysql会对连接语句进行优化）；
建立合适的索引，必要时建立多列联合索引；
使用distinct，group by，order by 数据量大的情况下容易产生 sing temporary; Using filesort，这是需要进行优化的；
学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要。
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调优经验

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

sql优化案例

场景介绍

type类型介绍

Extra 列介绍

优化分析

sc表数据更多的情况

索引优化

最左前缀

索引覆盖

排序

常用的sql调优经验

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读