慢查询日志中记录到有查询异常慢的问题,可能是因为未创建索引或索引失效;也可能是系统中有其他东西消耗了资源,也可能是某种类型的锁或者争用阻塞了查询的进度。
一、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 <'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;
:设置记录查询超过多长时间的SQLset 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