MySQL慢查询分析工具

慢查询日志中记录到有查询异常慢的问题，可能是因为未创建索引或索引失效；也可能是系统中有其他东西消耗了资源，也可能是某种类型的锁或者争用阻塞了查询的进度。

一、EXPLAIN

1.1. explain命令的介绍

explain 命令可以查看SQL语句的执行计划，当explain 与SQL语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了他将如何处理语句，包括有关如何联接表的信息。

1.2. explain能够做什么？

1. 分析表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被查询

1.3. explain的使用

explain的使用很简单，只需要在SQL语句上面加上explain命令即可，除select 语句外，explain还能够分享 insert 、update 和 delete 语句。

1.4. explain各列字段解释

执行后出来的信息有10列，分别是id、select_type、table、type、partitions、possible_keys、key、key_len、ref、rows、filtered、Extra ，接来下逐一解释sql

explain SELECT fi_code fiCode,rf_code rfCode,amount FROM rf_refund_record WHERE loan_type='WITHE_BAR' AND add_time >= '2019-12-01 00:18:09.0' AND add_time <'2020-05-18 22:16:37.066' and business_code NOT IN (SELECT business_code FROM sys_business_config WHERE service_line IN ('CASH','DRIVER_CARRIAGE'))

 

 

1.4.1. id

SQL执行顺序的标识，SQL从大到小的执行

① id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大优先级越高，越先执行

② 如果是子查询，id 的序号会递增，id值越大优先级越高，越先被执行

③ id 为NULL最后执行

1.4.2. select_type 表示查询类型，包括简单查询、复杂查询、子查询等：

类型	释义
SIMPLE	简单的SELECT查询，查询中不包含子查询或UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
SUBQUERY	在SELECT或WHERE中包含了子查询
DERIVED	在FROM中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED
UNION RESULT	从UNION表获取结果的SELECT

1.4.3. table

可能是实际的表名 select * from table

可能是表别名 select * from table as t

可能是 derived  如from 来自子查询的派生表

可能是null 直接计算得结果，不用走表，例如 select 1+2

1.4.4. partitions

由查询匹配记录的分区。对于非分区表，值为NULL

1.4.5. type

表示关联类型或访问类型，即MySQL决定如何查找表中的行，对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

类型	释义
system、const	const表示查询使用了主键索引（primary key）或唯一索引，system是表只有一行记录（等于系统表）时的type，是 const 类型的特例
eq_ref	在连接查询时，如果被驱动表是通过主键或者唯一，索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是 eq_ref
ref	相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
ref_or_null	对普通二级索引进行等值查询，该索引列也可以为NULL值时
index_merge	使用不同的索引查询并将结果合并
range	使用索引查询范围结果，通常出现在 in, between ,> ,<, >= 等操作中。
index	查询语句对一个索引树进行了全量扫描
ALL	全表扫描，MySQL会遍历所有行去查找结果，这种类型是效率最差的类型，必须进行索引优化

1.4.6. possible_keys

指出MySQL能使用那个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

1.4.7. Key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

1.4.8. key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好 

1.4.9. ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

1.4.10. rows

 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

1.4.11. filtered

通过过滤条件之后对比总数的百分比，condition filter / total  * 100 = filtered 值
该列表示将被表条件过滤的表行估计的百分比。
该值越大，性能越好！

1.4.12. Extra表示额外信息：

类型	释义
Using index	表示mysql 将使用覆盖索引，避免回表
Using where Using index	无法直接通过索引查找来查询到符合条件的数据，一般是使用索引前导列进行范围查询或通过索引的非前导列查询
Using index condition	查询列的某一部分无法直接使用索引，一般是WHERE 条件列是索引前导列且是范围查询导致的
NULL	WHERE条件是索引前导列，但查询列至少有一个未与条件列在同一个索引树上，必须通过回表查询
Using where	表示mysql 服务器将在存储引擎检索行后再进行过滤
Using filesort	会对结果使用一个外部索引排序，而不是按索引次序从表里读取行
Using filesort	当SQL中使用ORDER BY关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序
Index merges	对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)，一般出现AND和OR查询

 sql

explain SELECT a.* FROM ls_disburse_bill_2019 a inner join ls_carriage_loan_project b using(loan_proj_no) where a.add_time >= '2019-12-01 00:18' and a.add_time < '2020-01-01 00:00:00' AND b.order_status = 'SUCCESS';

explain select * FROM db_loan.acc_sub_account_flow WHERE trade_type != '8' AND is_delete = 0 AND account_id =(SELECT account_id FROM db_loan.acc_sub_account WHERE credit_code='WHITE_BAR' AND cust_id in (SELECT cust_id FROM db_loan_core.uc_user_base_info where uid = '1_1154249'))

总结：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作。

二、show index table

show index table 查看表索引信息

下面来说一下每个列的含义

2.1.Table
表的名称，这里就是 acc_sub_account_flow

2.2.Non_unique
表示是否唯一索引，如果唯一，则为0 ；不唯一，则为1。

2.3.Key_name
索引的名称。

2.4.Seq_in_index
索引中的列序列号，从1开始，如果是组合索引，则表示每列在索引定义中的顺序。

2.5.Column_name
列名称。

2.6.Collation
列以什么方式存储在索引中。在MySQL中，升序显示值‘A’（升序）；若显示为NULL，则表示无分类。

2.7.Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

2.8.Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

2.9.Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。

2.10.Null
用于显示索引列中是否包含NULL。若列含有NULL，该列的值为YES；若没有，则该列的值为NO。

2.11.Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

2.12.Comment
索引的备注。

三、DESC

DESC  table  查看表结构的详细信息

3.1. Field

字段表示的是列名 　
3.2. Type

字段表示的是列的数据类型
3.3. Null

字段表示这个列是否能取空值
3.14 Key

在mysql中key 和index 是一样的意思，这个Key列可能会看到有如下的值：PRI(主键)、MUL(普通的b-tree索引)、UNI(唯一索引)
3.5. Default

列的默认值 Extra :其它信息

四、慢查询开关配置

想要优化SQL，先得找出效率低下的SQL，而慢查询日志是有力的工具。

首先，通过一条语句查看当前数据库慢查询日志的信息：

SHOW VARIABLES LIKE '%slow_query_log%';

• slow_query_log：慢查询开启状态，ON为开启，OFF为关闭
• slow_query_log_file：慢查询日志存放的位置

查询到慢查询日志的状态后，可以使用命令进行修改（这种方式修改，Mysql服务器重启后会失效）：

• set global slow_query_log=on;：打开慢查询日志
• set global long_query_time=1;：设置记录查询超过多长时间的SQL
• set global slow_query_log_file='/tmp/slow_query.log';：设置mysql慢查询日志路径，此路径需要有写权限
• set global log_queries_not_using_indexes=ON;：设置没有使用索引的SQL记录下来

如果想要设置永久生效，我们可以修改配置文件my.cnf（可以通过find命令查找，一般是/etc/my.cnf），找到[mysqld]，写入：

# 设置慢查询开启状态
slow_query_log =1
# 慢查询日志存放的位置
slow_query_log_file=/application/mysql/data/localhost-slow.log
# 询超过多少秒才记录   默认10秒 修改为1秒
long_query_time = 1