MySQL-性能分析备忘录

4次阅读

共计 5770 个字符，预计需要花费 15 分钟才能阅读完成。

[TOC]

最后修改时间: 2019 年 10 月 21 日 15:08:59

救急

show full processlist

查看当前线程处理情况, 确认当前有哪些语句在执行, 执行情况如何.

特别注意执行时间长的, 如果确定有问题, 那么可以使用 kill {id} 干掉该连接

show full processlist 等价于以下语句

select id, db, user, host, command, time, state, info
from information_schema.processlist
order by time desc;

注意:

  • show processlist时, root 账号可以看到所有账号的连接, 如果是普通账号则只能看到自己的连接.
  • 如果显示长度太长看的很乱, 可以使用 \G, 即 show full processlist\G , 来将显示结果纵向输出, 方便查看.

扩展.

\g 等价于分号

\G 是将显示的表格以纵向输出, 方便查看.

Slow Query Log 慢查询日志

慢查询日志用于记录执行时间超过指定阀值的 SQL 命令.

确认开启情况

mysql> show variables like 'slow_query_log%';
+---------------------+------------------------------------------------+
| Variable_name       | Value                                          |
+---------------------+------------------------------------------------+
| slow_query_log      | ON                                             |
| slow_query_log_file | C:\laragon\data\mysql\DESKTOP-C1GGBS1-slow.log |
+---------------------+------------------------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

开启方式

配置文件

############### 慢查询日志 ################
# 打开慢查询日志
slow_query_log=1
# 日志记录位置
log_output=file
# 慢查询日志记录文件
slow_query_log_file=/var/run/mysqld/mysqld-slow.log
# 慢查询时间阀值
long_query_time=10

命令方式(mysqld 实例重启后失效)

-- 必须全局开启慢查询日志记录
set global slow_query_log=1;

-- 设置慢查询时间阀值
set global long_query_time=1;

如果想要分析一些语句的执行, 则可以考虑将当前 Session 的慢查询时间阀值设为 0 

set long_query_time=0;

注意

当数据库被拖垮时(高负载), 任何简单的语句都可能执行超时, 此时的慢查询日志能提供的帮助就有限了.

慢查询日志分析工具

mysqldumpslow

在实际生产环境中，如果要手工分析日志，查找、分析 SQL，显然是个体力活，MySQL 提供了日志分析工具 mysqldumpslow

# 分析慢日志
mysqldumpslow -a -n 50 -s c /var/run/mysqld/mysqld-slow.log

# 参数说明
--verbose    版本
--debug      调试
--help       帮助
 
-v           版本
-d           调试模式
-s ORDER     排序方式, 默认是 'at'
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time 平均锁定时间
              ar: average rows sent 平均返回记录数
              at: average query time 平均查询时间
               c: count 访问计数
               l: lock time 锁定时间
               r: rows sent 返回记录
               t: query time 查询时间
-r           反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前 N 条
-a           不要将 SQL 中数字转换成 N，字符串转换成 S。don't abstract all numbers to N and strings to'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配；grep: only consider stmts that include this string
-h HOSTNAME  mysql 机器名或者 IP；hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间；don't subtract lock time from total time

示例

得到返回记录集最多的 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

SQL Explain

对于慢查询日志中执行慢的语句分析其 SQL 语句的执行计划

EXPLAIN 可以帮助了解:

  • 数据表的读取顺序
  • SELECT 子句的类型
  • 数据表的访问类型
  • 可使用的索引 possible_keys
  • 实际使用的索引 key
  • 使用的索引长度 ken_len
  • 上一个表的连接匹配条件
  • 被优化器查询的行的数量
  • 额外的信息(如使用使用外部排序, 是否使用临时表)

举例

EXPLAIN 结果列分析

<u>字段 id</u>

SQL 执行顺序是根据

  • id 从大到小执行
  • id 相同时按照顺序从上往下执行.

<u>字段 select_type(查询类型)</u>

查询类型 说明
SIMPLE 简单查询
不包含 UNION 查询或子查询
PRIMARY 最外层查询
查询中若 包含任何复杂的子部分，最外层查询则被标记为 PRIMARY
SUBQUERY 子查询
在 SELECT 或 WHERE 中包含了子查询
DEPENDENT SUBQUERY !!! 子查询, 但依赖于外层查询的结果
注意确认, 避免大表驱动小表
DERIVED 子查询
在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)
UNION 联合
UNION 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION
UNION RESULT 使用联合的结果
从 UNION 表获取结果的 SELECT

关于 UNION, 网上有写以下这段, 但我个人不理解

UNION 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION：若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为：DERIVED

<u>字段 table(数据表)</u>

访问的数据表

<u>字段 partitions(分区)</u>

匹配的分区

<u>字段 type(访问方式)</u>

查询时的访问方式, 性能：all < index < range < index_merge < ref < eq_ref < system/const

一般来说至少需要保证访问方式是 range, 最好是 ref 级别.

访问方式 说明
ALL 全表扫描，对于数据表从头到尾找一遍
select * from tb1;
特别的：如果有 limit 限制，则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了 limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
因为非主键索引树比较小, 所以会比 ALL 更快
RANGE 对索引列进行范围查找
通常是在索引树上快速定位到某一索引项, 再向左 / 右遍历.
INDEX_MERGE 合并索引，使用多个单列索引搜索, 最后结果取交集或并集
比如使用了 UNION 且单独用到了两个索引.
REF 使用索引快速定位 (根据索引查找一个或多个值), 该索引是 普通索引 或 唯一索引的部分前缀
EQ_REF 使用主键索引或唯一索引快速定位
通常出现在多表的 join 查询, 连接时使用 primary key 或 unique 索引(都只能匹配到一行记录)
CONST 通过主键或唯一索引精确查找到一行
常量
表最多有一个匹配行(主键或唯一索引), 因为仅有一行, 在这行的列值可被优化器剩余部分认为是常数,const 表很快, 因为它们只读取一次
SYSTEM 系统
表仅有一行, 这是 const 联接类型的一个特例, 可以忽略这种情况。

Q. ALL 和 INDEX 的区别

A. 两个都是全索引扫描, 不同的是 ALL 是对主键索引扫描, INDEX 是对非主键索引扫描.

这里要理解, 所谓的全表扫描指的是对主键索引扫描.

Q. EQ_REF 和 CONST 的区别

A. 相同点都是使用主键 / 唯一索引精确查找到行记录. 不同点在于:

  • CONST 查询条件通常是 索引列 = 具体常量值
  • EQ_REF 通常是在多表关联查询时作为连接条件使用.

<u>字段 possible_keys(候选索引)</u>

<u>字段 key(实际使用的索引)</u>

如果是合并索引(INDEX_MERGE), 则此处可能存在超过 1 个的 key

<u>字段 key_len(使用索引的实际长度)</u>

该字段可以评估组合索引是否完全被使用或仅仅是最左前缀被用到.

该字段显示的值为索引字段的最大可能长度, 并非实际使用长度.(即 key_len 是根据表定义计算, 而非表内检索)

计算规则

字段类型 计算方式
字符串 char(n) n 字节长度
varchar(n) 若是 utf8 编码, 则是 n3 + 2 字节
如果是 utf8mb4 编码, 则是 4 n + 2 字节.
数值 tinyint 1 字节
smallint 2 字节
mediumint 3 字节
int 4 字节
bigint 8 字节
时间 date 3 字节
timestamp 4 字节
datetime 8 字节

如果对应索引字段允许为 null, 则还要额外消耗 1 个字节来存储 NULL.

<u>字段 ref</u>

表示索引的查找条件, 可能是常量(const) 或 联合查询中另一张表的某个字段.

<u>字段 row(扫描行数)</u>

估算的需要扫描的行数, 注意是估算的.

在某些情况下若索引统计信息偏差较大, 则此处的预估扫描行数也会过大, 导致影响查询计划的选择.

可以使用 SHOW INDEX FROM 表名 来查看索引统计信息

可以使用 ANYLYSIS TABLE 表名 来重新统计索引信息.

<u>字段 filtered</u>

<u>字段 Extra(额外信息)</u>

该列包含 MySQL 解决查询的详细信息

值 说明
Using filesort mysql 无法依靠索引直接获取有序记录, 而是对结果进行额外排序.
mysql 有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成
explain 不会告诉你 mysql 将使用哪一种文件排序
也不会告诉你排序会在内存里还是磁盘上完成。
Using index 使用覆盖索引，以避免访问表。不要把覆盖索引和 index 访问类型弄混了。
Using index condition 索引下推优化, 5.6 新增特性
Using temporary 意味着 mysql 在对查询结果排序时会使用一个临时表
Using where 使用了 where 过滤
这意味着 mysql 服务器将在存储引擎检索行后再进行过滤
许多 where 条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验
因此不是所有带 where 子句的查询都会显示“Using where”。
有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
Range checked for each record(index map: N) 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N 是显示在 possible_keys 列中索引的位图，并且是冗余的。
using join buffer 在表联结时, 使用了连接缓存

Profiling

写在最前: SHOW PROFILE 命令 将被弃用, 注意, 仅仅是 SHOW PROFILE命令弃用.

替代方案是从 information_schema 中的 profiling 数据表进行查看, 可参数链接:

  • https://www.cnblogs.com/sideo…
  • https://www.cnblogs.com/Jiang…

SHOW PROFILE 命令方式(旧)

查看语句执行的时间在各个步骤的开销

show profile 分析 SQL 性能工具(检测数据存在于临时表中)

  • 在会话级别开启 profile SET profiling=1;
  • 发送 sql

  • 查看 profile 的资源开销结果

    • show profiles 查看所有的分析结果(会有一个数量上限)
    • show profile 查看最后一条执行语句的分析结果
    • show profile for query <id> 查看指定执行语句的详细分析结果
    • show profile cpu, block io for query <id> 查看详细信息, 且包含 cpu, block.io 执行时间
  • 关闭 profile

这一部分更详细的可以参考如:

  • https://www.cnblogs.com/ggjuc…

information_schema .profiling

待用到时补充.

正文完
mysql 性能分析
发表至：无分类
2019-10-21
 0

站内搜索

-「」