慢查询日志中记录到有查询异常慢的问题，可能是因为未创建索引或索引失效；也可能是系统中有其他东西消耗了资源，也可能是某种类型的锁或者争用阻塞了查询的进度。
一、EXPLAIN
1.1. explain命令的介绍
explain 命令可以查看SQL语句的执行计划，当explain 与SQL语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了他将如何处理语句，包括有关如何联接表的信息。
1.2. explain能够做什么？
分析表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被查询
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
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
查询语句只扫描一次索引树即获得了目标数据，效率很高，一般是通过索引列查询主键或查询与索引列建有联合索引的列
Using where Using index
无法直接通过索引查找来查询到符合条件的数据，一般是使用索引前导列进行范围查询或通过索引的非前导列查询
Using index condition
查询列的某一部分无法直接使用索引，一般是WHERE 条件列是索引前导列且是范围查询导致的
NULL
WHERE条件是索引前导列，但查询列至少有一个未与条件列在同一个索引树上，必须通过回表查询
Using where
WHERE条件列上无索引(既没有单独索引，也没有联合索引)，而与查询列无关
Using filesort
MySQL需要创建一张内部临时表来处理查询，通常在许多执行包括DISTINCT、GROUP BY、ORDER BY等子句查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能会寻求建立内部临时表来执行查询
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
扫一扫
1413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
