如何分析sql性能

1、前言

提到sql性能分析，可能都会想到explain，它在mysql里被称为执行计划，也就是说可以通过该命令看出mysql在通过优化器分析之后如何执行sql。mysql的内置优化器十分强大，它能帮我们把sql再次优化，以最低的成本去执行。我们的项目中通常会添加慢sql打印的功能，这些慢sql就是需要我们去分析和优化的目标。

2、explain的使用

explain关键字使用很简单，只需要在目标sql前面加上该关键字然后执行即可，如下所示EXPLAIN SELECT * FROM user;

可以看到，返回了十几个字段，这里主要介绍几个重要字段type、key、rows、Extra。
type 表示 mysql 访问数据的方式，常见的有全表扫描（all）、遍历索引（index）、区间查询（range）、常量或等值查询（ref、eq_ref）、主键等值查询（const）、当表中只有一条记录时（system）。其效率由好到坏依次为

system > const > eq_ref > ref > range > index > all

key 表示查询过程实际会用到的索引名称
rows 表示查询过程中可能需要扫描的行数，这个数据不一定准确，是mysql 抽样统计的一个数据
Extra 表示一些额外的信息，通常会显示是否使用了索引，是否需要排序，是否会用到临时表等
对于上面那条sql，我并没有创建额外的索引，其type为all，说明走了全表扫描，这种全表扫描效率是很低的，是优化的重点。
现在分别给字段添加普通索引

alter table `user` add index name_index(name);
alter table `user` add index address_index(address);
alter table `user` add index age_index(age);

然后我们创建测试数据，这里用一个存储过程实现，以注释分割，依次执行

-- 修改mysql分隔符为双分号
delimiter ;;
-- 删除存储过程
drop procedure if exists makedata;;
-- 创建存储过程
create procedure makedata()
begindeclare i int;set i=1;while i<=100000 doinsert into user(`name`,`address`,`sex`,`age`) values(concat('小明',i),concat('北京',i),'男','25');set i=i+1;end while;
end;;
-- 修改分隔符为单分号
delimiter ;
-- 执行该存储过程
call makedata();

然后分析一条sqlEXPLAIN SELECT * FROM user where age>24;

可以看到，虽然key里面给出了索引名称，但是type类型依然是all，也就是说实际上我们的索引并未生效，这里就涉及一个回表查询了，所谓回表查询，就是我们根据普通索引查询到了索引字段和唯一主键索引字段，但是我们查询的字段超出了这两个，因此，还要去根据唯一主键索引去查询其他字段的数据。从这里也可以看出，mysql优化器在分析后认为还不如一开始就用全表查询（至少只查一次）。那么回表查询如何解决呢？最直接的方式就是只查询索引字段和唯一主键字段，如下

但是这样的话，有可能不满足我们的需求，所以最直接的方式就是实现索引覆盖。所谓的索引覆盖就是要查询的字段建立一个联合索引。
我们创建一个名字和年龄的索引字段alter table user add index name_age_index(name,age);
然后再去查询

3、总结

这篇文章主要记录了如何去分析一条sql的性能，然后引出了回表查询、索引、创建存储过程的相关知识，通过这些例子我们可以知道为什么不要去写select * 查询，当然，这里的内容还是远远不够的，需要我们多学习，多分析。