数据库-Sql性能分析（慢查询、EXPLAIN详解）

更多相关内容可查看

SQL性能监控

全局监控：通过全局监控，可以了解整个数据库集群或服务器的性能状况。这有助于确定是否存在性能瓶颈或问题区域。
定点监控：在确定了全局性能问题后，可以进行定点监控，专注于具体的查询或事务。这有助于识别造成性能下降的具体原因，如慢查询或磁盘问题

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以查看服务器状态信息
可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW  GLOBAL STATUS LIKE  'Com_______';

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

那么通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。 那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。

systemctl restart mysqld

然后，再次查看开关情况，慢查询日志就已经打开了。

windows下临时配置慢查询 :

show variables like '%slow_query_log%';

set global slow_query_log = 1 ;

set global slow_query_log_file='D:\\Users\\Desktop\\log\\mysql-slow.log';

set  long_query_time = 1 ;

show variables like '%long_query_time%';

select count(*) from item ;

测试：

1. 执行如下SQL语句 ：

select * from item; -- 这条SQL执行效率比较高, 执行耗时 0.00sec

select count(*) from item ; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时 13.35sec

2. 检查慢查询日志 ：
   最终我们发现，在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的SQL是不会记录的。
   
   那这样，通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化

explain

找到了哪些SQL语句执行速度比较慢, 那么我们该如何进行优化呢 ? 首先我们需要知道这条SQL为什么慢 , 找到慢的原因

TIPS : 任何一个问题的解决思路, 都是先找到问题, 再针对性解决问题

可以通过SQL语句执行计划EXPLAIN关键字或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法:

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN   SELECT   字段列表   FROM   表名   WHERE  条件 ;

Explain 执行计划中各个字段的含义:

字段	含义
id	select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、
UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有使用索引。
key_len	表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

id

每个 SELECT语句都会⾃动分配的⼀个唯⼀标识符. 表示查询中操作表的顺序，有三种情况：

• id相同：执⾏顺序由上到下
• id不同：如果是⼦查询，id号会⾃增，id越⼤，优先级越⾼。
• id相同和不同同时存在

id列为null的就表示这是⼀个结果集，不需要使⽤它来进⾏查询。
例如 :

explain select (select name from tb_user) from tb_user ;

select_type

查询类型，主要⽤于区别普通查询、联合查询(union、union all)、⼦查询等复杂查询。

explain select * from tb_user

explain select (select name from tb_user) from tb_user ;

explain select * from tb_user where id = (select max(id) from tb_user);

explain select * from tb_user where sex='man' union select * from tb_user where sex='2';

explain select * from (select * from tb_user where sex='man') b;

table

显示的查询表名

• 如果查询使⽤了别名，那么这⾥显示的是别名
• 如果不涉及对数据表的操作，那么这显示为null
• 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执⾏计划中的id，表示结果来⾃于 这个查询产⽣
• 如果是尖括号括起来的，与<unionM,N> 类似，也是⼀个临时表，表示这个结果来⾃于union查询 的id为M,N的结果集。

type

索引的使用类型 , 性能由好到差依次为 : system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL除了all之外，其他的type都可以使⽤到索引，除了index_merge之外，其他的type只可以⽤到⼀个索引
TIPS : 如果想让一条SQL的效率高, 那么最起码要使用到range级别

explain select * from (select * from tb_user where id=1) a;

explain select * from tb_user where name = 'Bob';

explain select a.id from tb_user a left join tb_dep b on a.deptId=b.id;

explain select * from tb_user where name = 'Bob';

explain select * from tb_user where name like 'B%';

explain select name  from tb_user;

explain select * from tb_user;

possible_keys

此次查询中可能选⽤的索引，⼀个或多个

key

查询真正使⽤到的索引，select_type为index_merge时，这⾥可能出现两个以上的索引，其他的 select_type这⾥只会出现⼀个。

key_len

⽤于处理查询的索引⻓度，如果是单列索引，那就整个索引⻓度算进去，如果是多列索引，那么查询不⼀定都能使⽤到所有的列
key_len只计算where条件⽤到的索引⻓度，⽽排序和分组就算⽤到了索引，也不会计算到 key_len中

ref

如果是使⽤的常数等值查询，这⾥会显示const 如果是连接查询，被驱动表的执⾏计划这⾥会显示驱动表的关联字段
如果是条件使⽤了表达式或者函数，或者条件列发⽣了内部隐式转换，这⾥可能显示为func

rows

这⾥是执⾏计划中估算的扫描⾏数，不是精确值

Extra

这个列包含不适合在其他列中显示单⼗分重要的额外的信息，这个列可以显示的信息⾮常多，有⼏⼗ 种，常⽤的有

1. using temporary : 表示使⽤了临时表存储中间结果
2. using filesort : 排序时⽆法使⽤到索引时，就会出现这个。常⻅于order by和group by语句中

explain select * from tb_addr order by address;

3. using index : 查询时不需要回表查询，直接通过索引就可以获取查询的数据

explain select name,age,sex from tb_user where name like '%B';

• 表示相应的SELECT查询中使⽤到了覆盖索引（Covering Index），避免访问表的数据⾏，效率不错！
• 如果同时出现Using Where ，说明索引被⽤来执⾏查找索引键值
• 如果没有同时出现Using Where ，表明索引⽤来读取数据⽽⾮执⾏查找动作

4. using where : 表示存储引擎返回的记录并不是所有的都满⾜查询条件，需要在server层进⾏过滤

注：Oracle的EXPLAIN PLAN其实本质上跟Mysql的explain差距不大

本篇小结

有小伙伴对其他数据库内容感兴趣，可以通过以下链接进行查看
数据库-索引（基础篇）
数据库-索引结构（B-Tree，B+Tree，Hash，二叉树）
数据库-索引语法（增删查）
数据库-索引分类（主键索引、唯一索引、普通索引、全文索引）
数据库-索引使用（验证索引效率、单列索引与联合索引、最左前缀法则）
索引失效情况
数据库-Mysql锁详解（全局锁、表级锁、行级锁）
数据库-事物原理刨析(高级详解)