01.MySQL慢查询

1.1 什么是mysql慢查询

  • MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。
  • 运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。
  • long_query_time的默认值为10，意思是记录运行10秒以上的语句。

1.2 查找那些语句慢

1
2
3
4
5
6
7
8
9
10
11
# 比如，得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

# 得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

# 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

1.3 explain分析慢查询原因

  • 使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

  • EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。

  • explain的执行效果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> explain select * from subject where id = 1 \G
******************************************************
           id: 1                # 查询中每个SELECT子句的标识符； 数字越大优先级越高；联合查询中可有多个 ID。
  select_type: SIMPLE           # 查询的类型          ；SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）、DERIVED（派生表/子查询派生结果）
        table: user             # 表示访问那个表
   partitions: NULL
         type: const            # 连接的类型         ；关键字段，system > const > eq_ref > range > index > ALL 。 ALL表示全表扫描，性能最差
possible_keys: PRIMARY          # 可能使用的索引。    ；为空表示没有合适的索引
          key: PRIMARY           # 使用了主键索引，如果为null代表没有使用索引
      key_len: 4                # 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
          ref: const
         rows: 1                # 预计读取的行数 ； 越少越好，说明索引命中率越高； 
     filtered: 100.00           # 查询的表行占表的百分比； 10表示只留下10%的行
        Extra: NULL             # 执行过程的额外信息；  - Using index（覆盖索引）、Using where(用了where条件)、Using Temporary（使用临时表，可能影响性能）、Using filesort（使用文件排序，可能影响性能）
                                
                          
******************************************************
  • 慢查询应该关注项
    1. type = ALL
    • 全表扫描，说明没有使用索引
    • 优化方向： 增加索引或者修改查询条件
    1. key = NULL
      • 没有实际使用索引
      • 优化方向：检查possible_keys，可能是索引设计不合理或者写法阻碍使用索引。
    2. rows 数值很大
      • 说明扫描的行数哦很多，预估成本很高
      • 优化方向：是否可以通过索引减少扫描的数据量？
    3. Extra包含Using filesort 或者 Using temporary
      • 说明使用了磁盘排序或者临时表，这通常是性能瓶颈
      • 优化方向： 尽量通过索引避免这类操作，比如 ORDER BY 字段加索引。

__END__