Posted by:
努力记

努力记,通过记录,计划,让自己的努力可以触碰彩虹!

4,686

Mysql:慢查询分析

  • 2013-05-17
  • 暂无评论

Mysql慢查询:Mysql的压力除了数据上的负载,和大并发的需求外,很多情况下都是因为表索引的建立与执行的SQL没有起到因有的作用

Mysql自身提供对SQL的记录,通过mysql参数:

  • slow_query_log 设置为ON,开启慢查询日志记录
  • slow_query_log_file 设置日志的存储路径
  • long_query_time 设置多少时间的查询算慢查询
  • log_slow_queries 旧版本的参数,已经不用,修改slow_query_log该变量也会被开启
  • slow_launch_time 设置查过多少时间的线程算慢查询,Slow_launch_threads计数加1,Slow_queries是慢查询的次数,这2个是环境状态

获取日志文件后,需要通过mysql的慢查询分析功能执行得到统计和排序结果

该工具是用perl语言写的脚本,在mysql程序包中的scripts目录下
ROOT/scripts/mysqldumpslow.pl,需要通过perl解释器去执行该脚本
Perl ROOT/scripts/mysqldumpslow.pl param log.log

  • -s 排序方式  c(记录次数) t(锁表时间) l(查询时间) r(返回的记录数) 前面加a倒序显示
  • -t top n的意思,即返回前面多少条数据
  • -g 可以设置一个正则匹配模式,大小写不敏感
  • -r 倒序排列
  • -h 查询的主机名或IP地址
  • -l 总查询时间中不减去锁定表的时间

根据需求设定条件

Mysql中提供了对一条查询语句的分析sql ,在sql前加上explain ,结果集中是对该查询所涉及到的表及索引和数据长度进行分析

结果集中的字段有

  1. Id:SQL执行的顺序,从大到小执行
  2. Select_type:SIMPLE(单表查询),PRIMARY: SUBQUERY:DEPENDENT SUBQUERY(子查询),UNION:UNION RESULT(连表查询),DERIVED(虚拟表:由查询生成的表)
  3. Table:该查询是关于哪张表的,当表是虚拟表,会显示的是<derived+ID>,由那句查询得出的虚拟表
  4. Type:显示了查询具体使用的链接类别,从好到差:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    通常能在该字段确定性能问题

    • system:如果查询的表只有一行会使用system,是const的一个特例
    • Const:通常对主键的查询会使用该链接类别
    • Eq_ref:引用表中的一行数据,查询使用表的一个索引的所有部分并且索引是唯一或者是主键,用=操作符的索引比较
    • Ref:引用满足条件的所有数据,查询使用表的一个索引的所有部分,用=或者< = >操作符的索引比较
    • Fulltext:使用的是fulltext的索引格式的字段,其余和ref类别相同
    • Ref_or_null:在查询中搜索包含null值的行
    • Index_merge:查询中使用到了多个索引,key包含多个索引,key_length包含所使用索引的最长关键元素
    • Unique_subquery:在使用子查询完成eq_ref类型的查询
    • Index_subquery:在使用子查询完成ref类型的查询
    • Range:使用一个索引来选择一定范围的行,key显示使用了什么索引,key_len包含所使用索引的最长关键元素,ref为null
    • Index:无法实现最优索引查询,但因为查询中不用查找数据,所以是会查找索引表,因为索引表相对数据表小,所以比all快
    • All:会进行完整的表扫描,是最慢的实现类型
  5. Possible_keys:指出mysql能使用哪些索引在该表中找到数据,如果该列为null则该查询不会使用索引,并且如果一次查询有多个对同表的查询,该列都会显示相同
  6. Key:显示实际使用的索引,如果没有使用索引,则该列为null。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
  7. Key_len:显示mysql使用索引所查询的长度,在不损失精确性的情况下,越短越好
  8. Ref:显示使用哪列和key一起从表中选择行,通常为该查询所涉及到的排序字段
  9. Row:显示mysql在执行查询时所必须查询的行数
  10. Extra:额外的查询信息
    • Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
    • Not exists: mysql优化了 left join ,一旦找到了 left join 匹配的行,便不再进行搜索了。
    • Rang checked for each:没有找到能优化的索引
    • Using filesort:mysql执行了需要文件排序的查询,需要优化该查询
    • Using temporary:查询中使用了零时表,最好能在应用中避免
    • Where used:使用了where条件语句查询,如果type为index或all,通常都会显示该信息,应对该查询优化

以上是分析工具的使用及介绍,但具体到分析工作则需要好好思考优化方向,特别是有些查询有可能需要进行程序功能的变动,如果可以应在开发中规避



back up ↑

无觅相关文章插件,快速提升流量