• 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 SELECTWHERE中包含了子查询
    DERIVED FROM中包含的子查询被标记为DERIVEDMySQL会递归执行这些子查询,把结果放在临时表里
    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
  • 相关阅读:
    哈尔滨理工大学第七届程序设计竞赛决赛(网络赛-高年级组)I
    哈尔滨理工大学第七届程序设计竞赛决赛(网络赛-高年级组)F
    哈尔滨理工大学第七届程序设计竞赛决赛(网络赛-高年级组)H
    哈尔滨理工大学第七届程序设计竞赛决赛(网络赛-高年级组)E
    哈尔滨理工大学第七届程序设计竞赛决赛(网络赛-高年级组)G
    哈尔滨理工大学第七届程序设计竞赛决赛(网络赛-高年级组)C
    哈尔滨理工大学第七届程序设计竞赛决赛(网络赛-高年级组)D
    哈尔滨理工大学第七届程序设计竞赛决赛(网络赛-高年级组)A
    哈尔滨理工大学第七届程序设计竞赛决赛(网络赛-高年级组)B
    HDU 6187 Destroy Walls
  • 原文地址:https://www.cnblogs.com/unknows/p/14349042.html
Copyright © 2020-2023  润新知