By

文章目录
  1. 认识与开始
    1. 认识慢查询
      1. 开始检测慢查询
      2. 开始分析慢查询
    2. 简单的方式评估查询性能

认识与开始

认识慢查询

MySQL的查询执行流程

对于一个业务查询来说， 性能问题的直观反映是响应时间，对于一个任务来说分析的主要方法就是拆分任务为子任务，找到慢的子任务。
对于一个慢查询来说，在不关注服务器当前的CPU、网络等因素的前提下，响应时间高主要分析以下几个方面:

  1. 是不是请求了过多的数据
  2. 查询是否扫描了额外不相关的数据

开始检测慢查询

MySQL较新的版本已经内置提取慢查询的功能：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#long_query_time用于定义慢查询的响应时间阀值，单位秒，
mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
#查看是否打开查询日志记录与日志文件
mysql> SHOW VARIABLES LIKE 'slow%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_launch_time    | 2                             |
| slow_query_log      | OFF                            |
| slow_query_log_file | /var/lib/mysql/node1-slow.log |
+---------------------+-------------------------------+
3 rows in set (0.01 sec)
#打开记录开关
SET GLOBAL slow_query_log='ON'
#测试
SELECT SLEEP(10)
#查看日志文件
cat /var/lib/mysql/node1-slow.log
usr/sbin/mysqld, Version: 5.6.21 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 170110 15:12:48
# User@Host: root[root] @  [127.0.0.1]  Id: 98122
# Query_time: 10.317485  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use db_1;
SET timestamp=1484032368;
select sleep(10);
#记录开启成功
#mysqldumpslow 命令方便的查看慢查询
mysqldumpslow -s c -t 10 /tmp/slow-log
#-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序；
#-t 返回记录数
#-g "exp" 按照参数字符串对查询进行过滤

开始分析慢查询

官方提供Explain命令用以分析MySQL对于SELECT的执行计划。

1
Explain select * from xxx

返回结果包含内容：

  • id: 查询编号

  • select_type: 查询的具体类型，详见点击

  • table: 该查询所引用的表

  • partitions: 该查询匹配到的分区

  • type: 表示这个表是如何组织到查询中或表是如何进行关联的

    • system: 该表只有一行数据
    • const: 最多只有一行匹配的数据，在指定使用主键或唯一索引时候，该表的数据则作为常量使用

    • eq_ref: 对于每个来自于前面的表的行组合，从该表中读取一行。

      select * from t_order a,t_order_ext b where a.order_id=b.order_id;

    • ref: 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。

      select * from t_order a,t_order_ext b where a.order_id=b.order_id and a.order_id=100;

    • fulltext: 查询使用了一个全文索引

    • ref_or_null: 类似ref，但是MySQL特意为可以为空的字段进行了空值的查询（字段尽量不为空）

      select * from t_order where user_id=100 or user_id is null;

    • index_merge: 表示使用了索引合并优化方法

    • unique_subquery: 该类型替换了类似以下IN子查询的 eq_ref:

      value IN (SELECT primary_key FROM single_table WHERE some_expr)

    • index_subquery: 该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

      value IN (SELECT key_column FROM single_table WHERE some_expr)

    • range: 只检索给定范围的行，用一个索引来选择行
    • index: 类似ALL,除了索引树被扫描了：
      • i：索引覆盖了整个查询，这样的话只扫描索引树就可以返回结果而不需要扫描数据，此时Extra 显示Using index.
      • ii: 读取了索引的内容用以进行全表扫描，此时Extra没有Uses index

    • ALL: 完整的扫描全表，最慢的联接类型，尽可能的避免

      select * from xxx

  • possible_keys: 可能使用到的索引，不一定真正使用到，但如果是NULL则代表无索引可用，需要优化

  • key: MySQL决定在查询中真正使用的索引，有可能不存在于possible_keys中，原因是此时发现possible_keys中的索引无助于更快的查询，但是所查询的列却完全包含在其他索引中

  • key_len: 决定使用索引的长度

  • ref: 那些列或者常量需要和使用到的索引进行比较

  • rows: MySQL认为的需要完成查询必须检查的行数

  • filtered: 表示在该查询条件下能够过滤掉数据行数的百分比（实际行数=rows*filtered/100） 5.7.3之前版本需要使用EXTENDED参数才显示，

  • Extra: 显示关于此查询额外的信息

    • Child of ‘table’ pushed join@1: 只在集群模式下存在
    • const row not found: 查询的表为空
    • Deleting all rows: 对于DELETE语句使用，表示MySQL使用一种快速删除所有数据的方式
    • Distinct: MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行
    • FirstMatch(tbl_name): 一种semi-join简化策略用在了表上
    • Full scan on NULL key: 子查询优化退化策略，此时优化器无法使用索引查找方式
    • Impossible HAVING: HAVING语句总是FALSE，无法选择任何数据
    • Impossible WHERE: WHERE语句总是FALSE，无法选择任何数据
    • Impossible WHERE noticed after reading const tables: MySQL读取了所有的system或者const表，发现where条件永远FALSE
    • LooseScan(m..n): semi-join使用到松散扫描策略
    • No matching min/max row: select min/max类型的语句没有找到匹配的行
    • no matching row in const table: 联表查询中存在空表或没有满足针对唯一索引条件的行
    • No matching rows after partition pruning: DELETE或UPDATE,对分区进行剪枝后没有发现要删除或更新的行
    • No tables used: 查询没有FROM条件或者存在FROM DUAL

    • Not exists: MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。

      SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
      此例中，t2的id是主键不可空，所以针对t1当中的每一行记录，MySQL只查t2一次即返回

    • Plan isn’t ready yet: 使用 EXPLAIN FOR CONNECTION 时候，查询计划还没有结束时
    • Range checked for each record(index map: N): MySQL没有发现好的索引，但是发现查询的列在之前的表中已知时有一些索引可用，比没有索引可用要快，N标识可能用到的索引，16进制的掩码（0x19 =>11001标识索引编号1、4、5可能会用到）
    • Scanned N databases: 略，关于INFORMATION_SCHEMA表
    • Select tables optimized away: 优化器确定只有一行需要返回，为了产生这一行数据需要读取确定行数的数据
    • Skip_open_table, Open_frm_only, Open_full_table: 略，关于INFORMATION_SCHEMA表
    • Start temporary, End temporary: 表示在semi-join优化中使用了去重策略(Duplicate Weedout)
    • unique row not found: 对于SELECT … FROM tbl_name，没有行满足关于unique index或者primary key条件的行
    • Using filesort: 此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。
    • Using index: 只需要查找索引树就可以获取所有的行，而不需要做额外的查找
    • Using index condition: 需要读取索引进一步判断是否需要读取表所有的行
    • Using index for group-by: group by查询中使用索引即可查找到结果
    • sing join buffer (Block Nested Loop), Using join buffer (Batched Key Access): 联表查询需要读入前面表作为缓存一部分进行优化查询
    • Using MRR: Multi-Range Read优化策略
    • Using sort_union(…), Using union(…), Using intersect(…): 标识索引归并是如何进行的
    • Using temporary: 使用了中间表
    • Using where: 当有where子句时
    • Using where with pushed condition: 略
    • Zero limit: 存在LIMIT 0 限制无法选择任何数据

在执行EXPLAIN EXTENDED后紧接着执行SHOW WARNINGS可输出进过优化器优化处理的SQL提示语句，并不一定可以执行

1
2
3
4
5
6
7
8
9
10
11
12
13
EXPLAIN EXTENDED
SELECT
        *
FROM
        activitypin_info
JOIN activityinfo
ON
        activity_id = activityinfo.id
WHERE
        activity_id  < 1000
        AND order_id > 1000;
SHOW WARNINGS

得到的优化SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/* select#1 */
select
        `db1`.`aa`.`id` AS `id`
from
        `db1`.`activitypin_info` `aa`
join `db1`.`activityinfo`
where
        (
                (
                        `db1`.`aa`.`activity_id` = `db1`.`activityinfo`.`id`
                )
                and
                (
                        `db1`.`activityinfo`.`id` < 1000
                )
                and
                (
                        `db1`.`aa`.`order_id` > 1000
                )
        )

在生成的结果中可能会有一些特殊的标记，见链接点击查看

简单的方式评估查询性能

查询行数与寻道数的关系：

$$N_{seek}=\frac{\log(行数)}{\log(索引大小/2*3/(索引长度 + 数据指针大小))}$$
  • 索引大小: 一般是1024 bytes
  • 数据指针: 一般是4 bytes
  • 索引长度: 500000行一般3bytes

索引存储空间问题：

1
空间=行数*(3+4)*3/2(MB)
文章目录
  1. 认识与开始
    1. 认识慢查询
      1. 开始检测慢查询
      2. 开始分析慢查询
    2. 简单的方式评估查询性能