0%

慢查询

发表于 分类于

文章字数：1166,阅读全文大约需要4分钟

定义及作用

配置

  1. 位置
  • windows下配置文件为my.ini
  • linux下配置文件位置为my.cnf
  1. 开启配置
    1
    2
    3
    slow_query_log = 1 # 0关闭 1开启
    slow_query_log_file = slow.log # 慢文件存放位置(安装目录date文件夹下)
    long_query_time = 5 # 查询多久以上的算是慢

排查

1
2
Query_time: xxx # 执行时间
Lock_time: xxx # 锁定时间

分析工具

  • mysqldumpslow
    MySQL自带的慢查询文件解析工具，自带分析。位于bin下。
    1
    2
    3
    mysqldumpslow -s at -t 5 /usr/local/data/slow.log
    -s at # 算出平均时间
    -t 5 # top5

常见优化

  1. 系统层面入手(排查sql导致cpu占用过高)
    1
    top -H # 查出性能占用最大的pid
  2. mysql参数优化
    1
    2
    3
    4
    5
    # 独立表空间
    Using filesort

    # 设置缓存空间(连表之类的操作使用的缓存空间)
    set sort_buffer_size = 256*1024*1024
  3. sql优化
  • 子查询变成关联查询
  1. 反范式设计优化
  • 针对范式化设计而言
  • 为了性能和读取效率考虑，适当对数据库设计范式进行违反
  • 允许适当冗余，以空间换时间(减少连表)
  1. 索引优化
  • explain查看索引使用情况
  • 根据使用情况重写建立索引(创建时范围索引放最后)

执行计划

1
explain sql
  • key 是否使用了索引，使用了什么索引
  • key_len 是否充分使用了索引

varchar(50)的索引计算

  1. 字符类型 varchar +2 char+0
  2. 字符集 utf8(3) 一个字符的长度
  3. 本身长度 50
  4. 是否null null(+ 1) not null(+ 0)
1
2
50 * 3
varchar(50) * 一个字符3个字节 + 0(not null) + 2(varchar类型)

可以通过索引长度计算出使用了几个索引

Explain字段详解

1
2
3
4
5
6
7
mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
row in set (0.03 sec)

1. id

  • 执行顺序的标识，id大的优先执行

2. select_type

  • SIMPLE简单的查询，不使用UNION或子查询等
  • PRIMARY查询中包含任何复杂字部分，最外层查询会被标记为PRIMARY
  • UNION中的后一个语句标记为UNION
  • DEPENDENT UNION，UNION中的后一个语句，取决于外面的查询
  • UNION RESULT是UNION语句的结果
  • SUBQUERY子查询中的第一个SELECT
  • DEPENDENT SUBQUERY，子查询中的第一个SELECT，取决于外面的查询
  • DERIVED派生表的SELECT，FROM子句的子查询
  • UNCACHEABLE SUBQUERY一个子查询的结果不能被缓存，必须重新评估外链接的第一行

3. table

  • 这一行的数据是关于那张表的，可能是tableNamex，x代表执行的步骤

4. type

  • 表示查找到这一行的方式，性能从低到高
  1. ALL遍历全表找到匹配的行
  2. index遍历全部索引树找到匹配的行
  3. range只检索给定范围的行，使用一个索引来选择行
  4. ref表的连接匹配条件，即那些列或常量被用于查找索引上的值
  5. eq_ref类似ref区别在于是使用的所有是唯一索引，即多表中使用primary key或者unique key作为关联条件
  6. const、system，当MySQL对查询某部分进行优化，并转换成一个常量时，使用这些类型访问。例如主键置于where中，MySQL就能把改查询转换成一个常量，system是const的特例，当查询的表只有一行情况下是system。
  7. NULL执行时不用访问表或索引，例如从索引列选取最小值，可以直接通过索引查找完成。

5. possible_keys

  • 指出MySQL能使用那个索引在表中找到记录，查询涉及到的字段若存在索引，则索引会被列出，但不一定会被利用到。

6.key

  • MySQL决定使用的键

7. key_len

  • 表示索引中使用的字节数，可通过改列计算查询中使用的索引的长度。

8. ref

  • 列与索引的比较，表示上述表的连接匹配条件，即那些列或常量被用于查找索引列的值

9. rows

  • 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况估算找到所需记录所需要读的行数

10. Extra

  • 包含MySQL解决查询的详细信息
  1. Using where，不用读取表中的所有信息，通过索引就能获取所需数据